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

[#1]

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.

[#2]

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

[#3]

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

[#4]

@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

[#5]

@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>

[#6]

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"