Created on 03-17-2017 09:33 AM - edited 08-18-2019 04:45 AM
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.
Created 03-17-2017 09:52 AM
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
Created 03-20-2017 03:15 AM
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
Created 03-20-2017 03:42 AM
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
Created 03-22-2017 07:13 PM
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>
Created 03-23-2017 03:00 PM
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"