Support Questions

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

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

avatar

13705-hive.jpg

Hi ,

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

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

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

5 REPLIES 5

avatar
Guru

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

avatar

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

avatar
Super Collaborator

@Harold Allen Badilla

Use this command to import data directly to hive :

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

avatar
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://11.11.111.11;databaseName=dswFICO" \ --username sqoop\ --password sqoop \ --driver com.microsoft.sqlserver.jdbc.SQLServerDriver \ --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>

avatar
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://11.11.111.11;databaseName=dswFICO --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --username sqoop --password sqoop --table KNA1 --hcatalog-database default --hcatalog-table KNA1 --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"