Exporting Data from hive to MSSQL(Windows Auth) using sqoop

[#1]

HI Team,

I'm trying to export data into MSSQL Server Windows authentication from hive ,

i placed JTDS Drivers in Sqoop library,

Can someone please help me on this.

Many Thanks in advance!

[#2]

@Jai C

Based on the following Jira, unfortunately, it seems like the ability to import directly into hive bucketed tables is not supported yet.

https://issues.apache.org/jira/browse/SQOOP-1889

So, you would have to import the data to an intermediate table and then insert into the bucketed table.

Please accept answer if this helped.

[#3]

HI @Sindhu ,

i'm trying to export from hive to MS-SQL(Windows Auth),

can you suggest me a solution for this.

[#4]

@Jai C

Below is the sample export command which works. In your case, could you share the error seen?

sqoop export --connect "jdbc:jtds:sqlserver://IE11WIN7:1433;useNTLMv2=true;domain=IE11WIN7;databaseName=default_db" --table "test_table_view" --hcatalog-database default --hcatalog-table t1 --columns col2,col3 --connection-manager org.apache.sqoop.manager.SQLServerManager --driver net.sourceforge.jtds.jdbc.Driver --username IEUser --password 'Passw0rd!' --update-mode allowinsert --verbose

[#5]

@Jai C

The error is not related to the Windows authentication, but is because of the Hive/Hcatalog table being a bucketed table which is not supported from Sqoop export. You can verify the same by running command from Hive Cli / Beeline:

show create table <database>.<table>;

The table definition would be like, keyword being 'clustered by':

hive> show create table default.test_bucket;
OK
CREATE TABLE `default.test_bucket`(
  `col1` int,
  `col2` string)
CLUSTERED BY ( 
  col1) 
INTO 3 BUCKETS
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://xxx.com:8020/apps/hive/warehouse/test_bucket'
TBLPROPERTIES (
  'numFiles'='13', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='8883', 
  'transactional'='true', 
  'transient_lastDdlTime'='1505206092')
Time taken: 0.786 seconds, Fetched: 21 row(s)

[#6]

@Jai C

As mentioned in the Sqoop Jira, export into Bucketed Hive table is not supported. To export into Hive table, recreate the table without 'clustered by'.

[#7]

@Jai C

It seems like Sqoop exported '0' records and the mapper failed. Check the application log for errors and share the complete error stack.

[#8]

HI @Sindhu ,

the following command work well and show list databases.

sqoop list-databases --connect 'jdbc:sqlserver://192.168.40.21:1433;useNTLMv2=true;databasename=DataDB' --connection-manager org.apache.sqoop.manager.SQLServerManager --username 'DataApp' --password 'BD@t@'

but when i run command by the following:

sqoop export --connect "jdbc:sqlserver://192.168.41.210:1433;useNTLMv2=true;domain=192.168.40.21;databaseName=DataDB" --table "TblBatch" --hcatalog-database default --hcatalog-table TblBatch --connection-manager org.apache.sqoop.manager.SQLServerManager --username DataApp --password 'BD@t@' --update-mode allowinsert --verbose

i have this error:

19/07/21 12:15:01 ERROR orm.CompilationManager: It seems as though you are running sqoop with a JRE.

19/07/21 12:15:01 ERROR orm.CompilationManager: Sqoop requires a JDK that can compile Java code.

19/07/21 12:15:01 ERROR orm.CompilationManager: Please install a JDK and set $JAVA_HOME to use it.

19/07/21 12:15:01 ERROR tool.ExportTool: Encountered IOException running export job:

java.io.IOException: Could not start Java compiler.

at org.apache.sqoop.orm.CompilationManager.compile(CompilationManager.java:196)

at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)

at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:77)

at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:113)

at org.apache.sqoop.Sqoop.run(Sqoop.java:150)

at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)

at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:186)

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:240)

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:249)

at org.apache.sqoop.Sqoop.main(Sqoop.java:258)