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.

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

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

New Contributor

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
Highlighted

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

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

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

New Contributor

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

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

Expert Contributor

@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

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

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

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

New Contributor

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"