CODEWITHSUNDEEP
hadoopapache-sparkhivesqoop
Munesh
Munesh

Reputation: 1569

How to transfer data & metadata from Hive to RDBMS

There are more than 300 tables in my hive environment. I want to export all the tables from Hive to Oracle/MySql including metadata.

My Oracle database doesn't have any tables corresponding to these Hive tables.

Sqoop import from Oracle to Hive creates tables in Hive if the table doesn't exists.
But Sqoop export from Hive to Oracle doesn't create table if not exists and fails with an exception.

Is there any option in Sqoop to export metadata also? or
Is there any other Hadoop tool through which I can achieve this?

Thanks in advance

Upvotes: 0

Views: 1018

Answers (2)

Dev
Dev

Reputation: 13753

Sqoop can't copy metadata or create table in RDBMS on the basis of Hive table.

Table must be there in RDBMS to perform sqoop export.

Why is it so?

Mapping from RDBMS to Hive is easy because hive have only few datatypes(10-15). Mapping from multiple RDBMS datatypes to Hive datatype is easily achievable. But vice versa is not that easy. Typical RDBMS has 100s of datatypes (that too different in different RDBMS).

Also sqoop export is newly added feature. This feature may come in future.

Upvotes: 1

Joe Widen
Joe Widen

Reputation: 2448

The feature you're asking for isn't in Spark. I don't know of a current hadoop tool which can do what you're asking either unfortunately. A potential workaround is using the "show create table mytable" statement in Hive. It will return the create table statements. You can parse this manually or pragmatically via awk and get the create tables in a file, then run this file against your oracle db. From there, you can use sqoop to populate the tables.

It won't be fun.

Upvotes: 1

Related Questions