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.

Problems importing tables from SAP that have / characters in the table name

Re: Problems importing tables from SAP that have / characters in the table name

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.

Re: Problems importing tables from SAP that have / characters in the table name

Contributor

Any updates on this?

Re: Problems importing tables from SAP that have / characters in the table name

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.

Re: Problems importing tables from SAP that have / characters in the table name

Contributor

That still doesn't work.

Here's my script:

sqoop --options-file options.txt \

--table "SCHEMA\".\"/BI0/TCUSTOMER"

--hive-import

--hive-overwrite

--split-by CUSTOMER

--hive-database DATABASE

Produces this error:

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

Highlighted

Re: Problems importing tables from SAP that have / characters in the table name

New Contributor

Can you try it with backqoutes? Something like this - \`tablename\`

Re: Problems importing tables from SAP that have / characters in the table name

Contributor

I tried that and got this error:

ERROR manager.SqlManager: Error executing statement: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "AS": line 1 col 19 (at pos 19) com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "AS": line 1 col 19 (at pos 19)

Re: Problems importing tables from SAP that have / characters in the table name

Contributor

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:

  1. a table name can be something like 'MSG\TABLENAME' or even worse: '[SCHEMA]::database.[TABLENAME]'. Just make sure you put the complete tablename between escaped double quotes:

    eg: \"/SOMETING/TABLENAME\" or \"[SCHEMA]::database.[TABLENAME]\"

  2. we needed to add there where clause '\$CONDITIONS' even though we did a select * without any filters.
  3. when limiting the result with a where clause the values have to be between single quotes:

    eg. WHERE DDLANGUAGE='E'

  4. SAP columns can contain empty values called SPACE (not the same as NULL) (shown as a '?' in the webIDE). If you want to exclude them use the where clause <>'' (just two singlequotes following each other):

    WHERE DDLANGUAGE<>''

  5. When making your command more readible I had to keep one extra parameter after the --query parameter. When I moved the --hive-import to the next line the command would fail (I think due to the ending quotes of the query.

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

Don't have an account?
Coming from Hortonworks? Activate your account here