Support Questions
Find answers, ask questions, and share your expertise

how to Restore Oracle Dump file in hive database

how to directly restore oracle Dump file in HIVE database.

7 REPLIES 7

Master Collaborator

One way to achieve this would be to use Sqoop to transfer all tables in the Oracle database into Hive but this would need the data in a live Oracle DB and not in the dump file.

Thanks Deepesh, This is what we are doing as of now. But we want to directly restore oracle dump file into hive so that the we can save the efforts and space of oracle db.

Super Mentor

@Gaurav Jain

Import the DB dump into the Oracle DB and then import using Hive. There is no direct way to import the DB dump file into hive directly.

Even using sqoop i there is no offline way to import the Oracle or any other RDBMS database Data (inform of DB dump): https://sqoop.apache.org/docs/1.4.5/SqoopUserGuide.html#_connecting_to_a_database_server

Sqoop is designed to import tables from a database into HDFS. To do so, you must specify a connect string that describes how to connect to the database. The connect string is similar to a URL, and is communicated to Sqoop with the --connect argument.

I think this is for restoring dump file in Oracle DB. Will the same command work in Hive. i need to restore oracle dump file into Hive.

Super Mentor

@Gaurav Jain

No, Oracle DB dump file can not be directly imported to Hive. At leaset i am not aware of any such option.

But if you have the OracleDB running then you can use tools like "sqoop" to do so something as following:

# sqoop import \
–connect jdbc:oracle:thin:@OracleDBHostname:1521:Schema \
–username test \
–password test1 \
–query "select owner, object_name, object_id, object_type, to_char(created, ‘yyyy-mm-dd’) created_date from all_objects where \$CONDITIONS" \
-m 4 \
–split-by object_type \
–hive-import \
–target-dir ‘/tmp/sqoopimport’ \
–hive-table test_oracle.my_all_objects_sqoop 

.

Please see: https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive

And

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.4.3/bk_dataintegration/content/using_sqoop_to_m...

Super Mentor

@Gaurav Jain

The following link talks about the multiple methods of moving data into Hive. How you move the data into Hive depends on the source format of the data and the target data format that is required. Generally, ORC is the preferred target data format because of the performance enhancements that it provides. https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.4.3/bk_dataintegration/content/moving_data_into... One of them is "Operational SQL database" (ORC file format)

1. Use Sqoop to import the data from the SQL database into Hive.

2. Then use Hive to convert the data to the ORC file format.

New Contributor

Dumpspdf is providing Oracle Exam Questions for all exams with very cheap price, and exam passing guarantee and full money back guarantee also provide. So, don't waste time on different site today visit Dumpspdf and download exam dumps. and prepare it. and pass the exam at the first attempt.