CODEWITHSUNDEEP
mysqlhadoopsqoop
DarqMoth
DarqMoth

Reputation: 603

Sqoop: replace charatcters when importing from MySQL to Hive?

When importing data from MySQL to Hive I need to drop two characters +7 in phone number. The following request returns SQL error. What is the correct replace command should I use?

sqoop import --connect jdbc:mysql://server/db --username xxxx --password yyyy --query 'select name, last_name, email, second_name, Replace(personal_phone, '+7', ''), Replace(mobile, '+7', ''), Replace(phone, '+7', '') from user where $CONDITIONS' --target-dir /data/test -m 1 --null-string '\\N' --null-non-string '\\N' --hive-import --hive-table user_inf

Upvotes: 1

Views: 3539

Answers (2)

DarqMoth
DarqMoth

Reputation: 603

This one works for me:

sqoop import --connect jdbc:mysql://server/db --username xxxx --password yyyy --query "select name, last_name, email, second_name, Replace(phone, "+7", '') as phone, Replace(mobile, "+7", '') as mobile from test where \$CONDITIONS" --target-dir /data/test -m 1 --null-string '\\N' --null-non-string '\\N' --hive-import --hive-table info

Upvotes: 1

Neels
Neels

Reputation: 2543

The REPLACE command looks correct. The error might be because of the quotes since you have used single quotes in the beginning. I have changed it to Double quotes in the SQL query. It should work now:

sqoop import --connect jdbc:mysql://server/db --username xxxx --password yyyy --query "select name, last_name, email, second_name, Replace(personal_phone, '+7', ''), Replace(mobile, '+7', ''), Replace(phone, '+7', '') from user where $CONDITIONS" --target-dir /data/test -m 1 --null-string '\\N' --null-non-string '\\N' --hive-import --hive-table user_inf

Upvotes: 0

Related Questions