cancel
Showing results for 
Search instead for 
Did you mean: 

Hadoop / Sqoop - Export HDFS to HANA

Former Member
0 Kudos

Hi everybody,

i’m working now with hadoop and sap hana for around 3 month and i'm sticking heavily at the moment.
My Hadoop System is importing and converting logfiles via pig script and generates a result, which i finally want to export to sap hana appliance.

I wanted to use sqoop for this task, but at the moment, i’m wondering about the error message and the behavior of the system.
To get handy with sqoop, i generated a test file with some simple input like this:

1
3
5

On the other side, i generated a table in sap hana with a text field column.
I used this sqoop command to successfully export the hdfs test file into sap hana:

sqoop export –connect jdbc:sap://saphana.XXX.XXX:30115/ –driver com.sap.db.jdbc.Driver –table XXX.XXX–username XXX –password XXX –export-dir /user/tom/test

And it worked. I saw 1 3 and 5 on the sap hana side.
Then i tried to export much more data, but till in the simple format like:

1
3
5
7
9
1

And then i got some error messages, which dont have something to do with the change of my test file:

java.io.IOException: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ",": line 1 col 35 (at pos 35)

  at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:184)

  at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:649)

  at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:766)

  at org.apache.hadoop.mapred.MapTask.run(MapTask.java:363)

  at org.apache.hadoop.mapred.Child$4.run(Child.java:255)

  at java.security.AccessController.doPrivileged(Native Method)

  at javax.security.auth.Subject.doAs(Subject.java:396)

  at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1232)

  at org.apache.hadoop.mapred.Child.main(Child.java:249)

Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ",": line 1 col 35 (at pos 35)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:174)

  at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)

  at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1142)

  at com.sap.db.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1961)

  at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:981)

  at com.sap.db.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:253)

  at com.sap.db.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:212)

  at com.sap.db.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:123)

  at com.sap.db.jdbc.CallableStatementSapDBFinalize.<init>(CallableStatementSapDBFinalize.java:31)

  at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:1370)

  at com.sap.db.jdbc.trace.Connection.prepareStatement(Connection.java:413)

  at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.getPreparedStatement(ExportOutputFormat.java:141)

  at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.execUpdate(AsyncSqlRecordWriter.java:149)

  at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:181)

  ... 8 more

curiously sqoop exported the first two lines to sap hana and then crashes.

Could someone help me with this problem?

Suerte

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Felix,

It might be that the insert SQL SQOOP is creating is generating  multiple values

e.g.

INSERT INTO <TABLE> VALUES (<ROW1>), (<ROW2>), etc.

Which isn't support by HANA SQL.

Instead you may want SQOOP to generate multiple insert lines such as:

INSERT INTO <TABLE> VALUES (<ROW1>)

INSERT INTO <TABLE> VALUES (<ROW2>)

The multiple values feature enables other databases to load inserts faster. 

For HANA you should try setting     sqoop.export.records.per.statement=1

e.g.

sqoop export -D sqoop.export.records.per.statement=1 –connect jdbc:sap://saphana.XXX.XXX:30115/ –driver com.sap.db.jdbc.Driver –table XXX.XXX–username XXX –password XXX –export-dir /user/tom/test


I hope you have some luck with that. Let me know how you get on. 🙂


Former Member
0 Kudos

Thanks for the tip, this fixed the issue for me.

Former Member
0 Kudos

First, it's worth noting that I don't think this is a supported scenario, so your mileage may vary.

Can you debug the SQL the Sqoop generates and paste it here.

John