Created 06-07-2016 08:45 PM
Is there a way to make sqoop accept / characters in table and field names?
I have tried the following:
leaving the name as-is: --table /BI0/TCUSTOMER
wrapping in quotes: --table "/BI0/TCUSTOMER"
escaping each /: --table "\/BI0\/TCUSTOMER"
It produces this error each time for all three options above:
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: : sql syntax error: incorrect syntax near "\": line 1 col 17 (at pos 17)
Created 06-07-2016 08:51 PM
Have you tried using --query using a specific select statement to pull data?
Created 06-16-2016 07:22 PM
So I found that if i force a query using --query it will work, but if I let sqoop generate the query it does not.
Example --query "select * from \"BI0/TCUSTOMER\"" will work, but referring to the table in the same way by using the --table \"BI0/TCUSTOMER\" argument does not work. So the problem must be when sqoop generates the SQL query on its own, it is not syntactically correct.
If there was a way to see the query it was generating that would be very helpful to troubleshoot it, but as far as I know there is not.
Created 06-07-2016 08:59 PM
Yes, doing that gave the same error.
Created 06-07-2016 09:05 PM
Please use like this, it will resolve the issue.
Similar to bug https://issues.apache.org/jira/browse/SQOOP-1722
Created 06-07-2016 10:03 PM
I still believe this can be fix through different syntax since I don't have SAP installed with SAP driver so can't test it with multiple changes locally.
Please give another try with below syntax.
Created 06-08-2016 09:03 PM
Did you tried the last suggested syntax or are you able to resolve this issue?
Created 06-08-2016 09:13 PM
Yes I tried the above. That somewhat fixed my problem, it seems to accept the syntax now, but I believe it is now literally interpreting the quotes as part of the table name, so when it goes to SAP to find that table, it can't find it because the quotes aren't actually there in the SAP table name...
16/06/08 15:10:31 ERROR tool.ImportTool: Encountered IOException running import job: java.io.FileNotFoundException: /tmp/sqoop-hdfs/compile/d26f755016cd4c734711de4a2550ca46/SCHEMA."/BI0/TCUSTOMER".jar (No such file or directory)
Created 06-08-2016 09:16 PM
Ok then we may need to remove the initial double quote.
Created 06-08-2016 09:22 PM
Got the same error:
16/06/08 16:20:49 ERROR tool.ImportTool: Encountered IOException running import job: java.io.FileNotFoundException: /tmp/sqoop-hdfs/compile/2fb644a9751cabd5c187540154e2e696/SAPR3."/BI0/TCUSTOMER".jar (No such file or directory)
Created 06-08-2016 09:31 PM
So it seems that SAP JDBC error is gone but somehow sqoop is not resolving the double quotes. Will do search locally and get back to you.
Created 06-13-2016 06:21 PM
Any updates on this?
Created 06-14-2016 10:15 PM
Sorry didn't get cycle to work on this. I will try it in couple of days. Also lets wait if other experts comment on this.
Created 06-07-2016 09:32 PM
That still doesn't work.
Here's my script:
sqoop --options-file options.txt \
Produces this error:
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: : sql syntax error: incorrect syntax near "/": line 1 col 26 (at pos 26)
Created 06-09-2016 12:40 AM
Can you try it with backqoutes? Something like this - \`tablename\`
Created 06-09-2016 01:18 PM
I tried that and got this error:
ERROR manager.SqlManager: Error executing statement: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: : sql syntax error: incorrect syntax near "AS": line 1 col 19 (at pos 19) com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: : sql syntax error: incorrect syntax near "AS": line 1 col 19 (at pos 19)
Created 07-08-2016 11:38 AM
I can confirm what @Josh Persinger is saying. The only way to get tables with forward slashes ('/') (and actually colons too) in the tablename from SAP into hadoop hdfs/hive is by using the --query statement.
Some other things I found out when importing from SAP HANA:
eg: \"/SOMETING/TABLENAME\" or \"[SCHEMA]::database.[TABLENAME]\"
eg. WHERE DDLANGUAGE='E'
The result should look something like this:
sqoop import --connect "jdbc:sap://[SERVER]:30015/?currentschema=[SCHEMA]" \
--username [USERNAME] \
--password-file file:///[PATH]/.pwd_file \
--driver com.sap.db.jdbc.Driver \
--query "select * from \"[/TABLENAME/WITH/FORWARDSLASHES]\" WHERE DDLANGUAGE='E' and [COLUMNNAME]<>'' and \$CONDITIONS" --hive-import \
--hive-database [HIVEDATABASE] \
--hive-table [TABLENAME] \
--hive-delims-replacement ' ' \
--fields-terminated-by '|' \
--lines-terminated-by '\n' \
--hive-overwrite --num-mappers 1