Reply
Explorer
Posts: 23
Registered: ‎04-26-2016

Need to sqoop import data from sql server

I need to do a sqoop import from sql server, what are the steps.

 

I alread brought the jdbc sql server based jar file to the following folder:/var/lib/sqoop/

sqljdbc_4.0/enu/sqljdbc4.jar 

 

Need the syntax on how to properly sqoop the data off of sql server tables into hadoop.

 

Thanks a lot for the helpful info.

 

Posts: 352
Topics: 11
Kudos: 54
Solutions: 30
Registered: ‎09-02-2016

Re: Need to sqoop import data from sql server

@cplusplus1

 

Follow the below two steps (customize as needed)

 

##Step1:
Download the driver from microsoft.com and install it in your Sqoop lib path.

 

$ curl -L 'http://download.microsoft.com/download/0/2/A/02AAE597-3865-456C-AE7F-613F99F850A8/sqljdbc_4.0.2206.100_enu.tar.gz' | tar xz
$ Unzip it
$ sudo cp sqljdbc_4.0/enu/sqljdbc4.jar /var/lib/sqoop/

 

##Step2:
Run the sqoop script as follows

sqoop import --connect jdbc:sqlserver://ipaddress:1433 
--username kumar 
--password testpwd 
--query 'SELECT * FROM tablename' 
--target-dir dir_path 
--hive-table db.tablename 
--hive-import 
--hive-drop-import-delims

 


Thanks

Kumar

Explorer
Posts: 23
Registered: ‎04-26-2016

Re: Need to sqoop import data from sql server

Hi Kumar,

 

I have executed the following sqoop import from sql server into hive database.

 

when i ran the below i am getting some warning messages on unix putty. 

 

please consider removing target dir warning message: what does it mean and what is the best to reissue the sqoop import fixing those warning messages: thank you very much for the helpful info.

 

sqoop import --connect jdbc:sqlserver://svrsql29:1433 --username uname_ofmy_db --P --query 'SELECT * FROM data_analysis.dbo.cal_dim WHERE $CONDITIONS' --target-dir /user/hive/warehouse/test_database.db/sql_test_table2 --hive-import --hive-database test_database --hive-table sql_test_table2 --split-by date

in order to view on hue, must invalidate metadata;


17/01/19 12:49:58 WARN tool.BaseSqoopTool: It seems that you're doing hive import directly into default
17/01/19 12:49:58 WARN tool.BaseSqoopTool: hive warehouse directory which is not supported. Sqoop is
17/01/19 12:49:58 WARN tool.BaseSqoopTool: firstly importing data into separate directory and then
17/01/19 12:49:58 WARN tool.BaseSqoopTool: inserting data into hive. Please consider removing
17/01/19 12:49:58 WARN tool.BaseSqoopTool: --target-dir or --warehouse-dir into /user/hive/warehouse in
17/01/19 12:49:58 WARN tool.BaseSqoopTool: case that you will detect any issues.
17/01/19 12:49:58 INFO manager.SqlManager: Using default fetchSize of 1000

Posts: 352
Topics: 11
Kudos: 54
Solutions: 30
Registered: ‎09-02-2016

Re: Need to sqoop import data from sql server

@cplusplus1

 

You can ignore the warning as long as the data get imported, because As you know there some Mandatory & Recommended conditions are available in all the tool in general. Ignoring/mishandling the mandatory conditions will end up with error, mishandling recommended conditions will end up with warning. Your are in 2nd category now.

 

Still if you want to fix the warning pls follow the below steps

1. The sample script that i've given is customized as per my need... you have to modify as per your need

 

2. Go to the below link and search for "Import Control arguments", It has so many arguments, except few, most of them are optional. Remove unwanted arguments
https://sqoop.apache.org/docs/1.4.1-incubating/SqoopUserGuide.html


3. Try "--target-dir sql_test_table2" instead of "--target-dir /user/hive/warehouse/test_database.db/sql_test_table2"   (or) Remove "--target-dir" argument (not sure it is mandatory)

 

Thanks

Kumar

 

Explorer
Posts: 23
Registered: ‎04-26-2016

Re: Need to sqoop import data from sql server

[ Edited ]

Hi Kumar,

 

can you please tell me how i can do sqoop export: from hive database into sql server.

 

sqoop import --connect jdbc:sqlserver://svrsql3452:1433 --username uname_ofmy_db --P --query 'SELECT * FROM data_analysis.dbo.cal_dim WHERE $CONDITIONS' --target-dir /user/hive/warehouse/test_database.db/sql_test_table2 --hive-import --hive-database test_database --hive-table sql_test_table2 --split-by date

 

the above command worked fine with importing sql server data into hive database, want to try sqoop export.

 

 

hive datase: test_database

hive table to export: sql_test_table2

 

sql server databasename: data_analysis

sql server tableName: cal_dim

 

Thank you very much.

 

Highlighted
Posts: 352
Topics: 11
Kudos: 54
Solutions: 30
Registered: ‎09-02-2016

Re: Need to sqoop import data from sql server

@cplusplus1

 

To be frank, i never tried export to sql server before... but I believe if import works fine then you can manage export with some minor changes from the below link

 

https://sqoop.apache.org/docs/1.4.1-incubating/SqoopUserGuide.html#_literal_sqoop_export_literal

 

Pls try the same and let us see if something goes wrong!!

Announcements