Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

sqoop import-all-tables to Hive/HCatalog

sqoop import-all-tables to Hive/HCatalog

Expert Contributor

HDP-2.4.2.0-258 installed using Ambari 2.2.2.0

To import a single table from SQL Server to HCatalog, the following command suffices :

sqoop import --connect 'jdbc:sqlserver://<IP>;database=FleetManagement' --username  --password  --table SettingAttribute -- --schema Administration  --hcatalog-database FleetManagement --hcatalog-table Administration_SettingAttribute_orc --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"

I'm unsure if this is supported Sqoop, I tried but the files only got imported to HDFS and NOT HCatalog/Hive :

sqoop import-all-tables --hcatalog-home /usr/hdp/current/hive-webhcat --hcatalog-database FleetManagement_Common --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile" --connect 'jdbc:sqlserver://<IP>;database=FleetManagement' --username --password -- --schema Common

I am eager to know if there exists a way to import all tables from an rdbms schema to Hive/HCatalog in one step ?

4 REPLIES 4

Re: sqoop import-all-tables to Hive/HCatalog

Expert Contributor

You should be able to use the --hive-import and --hive-database options as specified here to import all tables to hive:

https://issues.apache.org/jira/browse/SQOOP-912

However, I just tried the following command, and it only imported the first table:

sqoop import-all-tables --connect jdbc:mysql://<host-name>/foodmart --username bhagan --password xxxxxxxx --hive-database foodmart --hive-import

Re: sqoop import-all-tables to Hive/HCatalog

Rising Star

Try the below command. I was facing the same issue.

1. Clean up /user/hive/warehouse for any exiting copies of to be imported tables

2. Cleanup java files outdoor if any

3. run the below command

sqoop import-all-tables \

--connect jdbc:mysql://localhost:33066/dbname \

--username user \

--password password \

--m 1 \

--hive-database default \

--hive-import \

--hive-overwrite \

--create-hive-table \

--as-textfile \

--compression-codec=snappy \

--outdir java_out

Re: sqoop import-all-tables to Hive/HCatalog

New Contributor

If you are giving hive overwrite then no need to cleanup the warehouse....it will overwrite the data in existing table.

Re: sqoop import-all-tables to Hive/HCatalog

New Contributor

I used below code

sqoop import-all-tables \

--connect jdbc:mysql://<hostname>:3306/retail_db \

--username <username>\

--password <password> \

--hive-import \

--hive-database <database_name> \

--autoreset-to-one-mapper \

--hive-overwrite \

--create-hive-table;

,

I used below code

sqoop import-all-tables \

--connect jdbc:mysql://<hostname>:<port>/retail_db \

--username <username> \

--password <password> \

--hive-import \

--hive-database <dbname> \

--autoreset-to-one-mapper \

--hive-overwrite \

--create-hive-table

Don't have an account?
Coming from Hortonworks? Activate your account here