Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

How to Import table from SQL Server into Hive using sqoop?



Hi ,

Im trying to import table from SQL Server to Hive with the commands below:

sqoop import --connect "jdbc:sqlserver://;databaseName=dswFICO" --username sqoop--password sqoop --driver --table KNA1 --target-dir "/tmp/dbo-customer"

When i check on Hive View i cant see any KNA1 table created.



You are importing as file into a directory (/tmp/dbo-customer). You can check in HDFS to see the files there. You can either create a table from hive that points to this table OR use --hive-import and --create-hive-table options directly import into hive


Hi @Ravi Mutyala,

is there any disadvantage when importing a table from Sql Server directly to HIVE? what is the best way to do it?

Can you help me with this. thank you in advance

Super Collaborator

@Harold Allen Badilla

Use this command to import data directly to hive :

sqoop import --connect "jdbc:sqlserver://;databaseName=dswFICO" \ --username sqoop\ --password sqoop \ --driver \ --table KNA1 \ --split-by rowkey --hive-import -m 1

Expert Contributor
@Harold Allen Badilla

There is no disadvantage in importing a table from Sql Server directly to Hive.

In fact its a single command which internally does importing data into HDFS loaction(you can specify via --warehouse-dir) creates hive table schema and Loads the data to Hive table.

This create Hive table name/schema similar to the source database table.

sqoop import --connect "jdbc:sqlserver://;databaseName=dswFICO" \ --username sqoop\ --password sqoop \ --driver \ --table KNA1 \ --warehouse-dir <HDFS path> --hive-import

-> additionally you can specify --hive-overwrite if you want to overwrite any exiting data on the Hive table(if exists)

-> If you want to load data into a table(hive) of your choice you can use --create-hive-table --hive-table <table name>

Rising Star

If want to do one time import,use the following command,It will use hcatalog to create the table and import the data in ORC format

sqoop import --connect jdbc:sqlserver://;databaseName=dswFICO --driver --username sqoop --password sqoop --table KNA1 --hcatalog-database default --hcatalog-table KNA1 --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"