01-13-2017 03:44 AM
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/
Need the syntax on how to properly sqoop the data off of sql server tables into hadoop.
Thanks a lot for the helpful info.
01-14-2017 01:05 PM
Follow the below two steps (customize as needed)
Download the driver from microsoft.com and install it in your Sqoop lib path.
$ curl -L 'http://download.microsoft.com/download/0/2/A/02AA
E597-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/
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
01-19-2017 10:05 AM
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_tab
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
01-19-2017 10:45 AM
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
3. Try "--target-dir sql_test_table2" instead of "--target-dir /user/hive/warehouse/test_database.db/sql_test_tab
01-19-2017 11:48 AM - edited 01-19-2017 11:49 AM
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_tab
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.
01-19-2017 11:59 AM
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
Pls try the same and let us see if something goes wrong!!