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.

Sqoop import - special characters

Solved Go to solution

Sqoop import - special characters

New Contributor

All,

Working on importing data from DB2 using sqoop import, it worked fine for the most part except one table, which seemed to have some special characters ( control-M = ^M ) in contents, hence while sqooping, these characters are treated as newline and hence everything after it will be on the next line in the imported files, which will affect all the records after one bad record.

I am unable to guess how to fix the imports? is there any easy way?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Sqoop import - special characters

Champion

@Freakabhi

 

If your business allows to replace Ctrl+M with something else then I would suggest two steps

 

1. Understand the regexp_replace function

ex: regexp_replace(col2,  '<the char that you want to replce>', '')

 

2. Use --query option instead of --table option in sqoop

 

So you have to use the following method to replace Ctrl+M with something else in your sqoop script

--query "select col1, regexp_replace(col2,  '<the char that you want to replce>', '') from db.table"

3 REPLIES 3

Re: Sqoop import - special characters

Champion

@Freakabhi

 

If your business allows to replace Ctrl+M with something else then I would suggest two steps

 

1. Understand the regexp_replace function

ex: regexp_replace(col2,  '<the char that you want to replce>', '')

 

2. Use --query option instead of --table option in sqoop

 

So you have to use the following method to replace Ctrl+M with something else in your sqoop script

--query "select col1, regexp_replace(col2,  '<the char that you want to replce>', '') from db.table"

Re: Sqoop import - special characters

Explorer

@saranvisa @Freakabhi 

 

I tried the same with Teradata but it throws an error 

 

[Teradata Database] [TeraJDBC 15.10.00.26] [Error 6706] [SQLState HY000] The string contains an untranslatable character.

 

Did i add the parameter right ?

 

PFB my sqoop command

 

sqoop import --connect jdbc:teradata://xx.xx.xx/DATABASE=xxx --username xxx -P --query "SELECT TOP 1000 regexp_replace(load_hash_key_val,'^M',''),regexp_replace(load_hash_data_val,'^M','') FROM db.tble_name  where 1=1 AND \$CONDITIONS" --target-dir /hadoop/test -m1

 

Any suggestions please ?

 

Thanks in advance.

Highlighted

Re: Sqoop import - special characters

New Contributor

Hi,

 

Can you let me know how to replace a set of special characters in the sqoop import query.

 

I need to replace the column value if it contains any of the special characters(|,",^,$,% etc) in it

 

 

Thank You

Vijay

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