Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar

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!

7 REPLIES 7

avatar
@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.

avatar

HI @Sindhu ,

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

can you suggest me a solution for this.

avatar

@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

avatar
@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)

avatar

@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'.

avatar

@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.

avatar
Explorer

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)