on 12-27-2013 3:33 AM
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
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. 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.