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.

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

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

New Contributor

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

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

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

Highlighted

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

New Contributor

HI @Sindhu ,

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

can you suggest me a solution for this.

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

@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

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

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

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

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

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

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

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

New Contributor

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)