CODEWITHSUNDEEP
mysqletlsqoop
Carleto
Carleto

Reputation: 951

Use MySQL functions in Sqoop free-form query

I'm trying to execute a query like this

SELECT replace(name, '\0', '') FROM example

using the argument --query in sqoop

--query "select replace(name, '\\0', '')" from example e

but this approach is returning the error

ERROR tool.ImportTool: Imported Failed: No column by the name namefound while importing data; expecting one of [id, REPLACE_name___0_____]

Any idea of how to solve this ?

Upvotes: 0

Views: 184

Answers (1)

KiranM
KiranM

Reputation: 1323

Firstly, you need to enclose the SQL statement properly. Then you need to escape "\" character with "/".

Please check below worked for me:

sqoop import \
  --connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
  --username=retail_dba \
  --password=cloudera \
  --query "select replace(department_name, '/\0', '*') from departments  where 1=1 and \$CONDITIONS" \
  --split-by "department_name" \
  --target-dir "/km/op_sqoop/dept_names" \
  -m 1

eval also worked:

sqoop eval \
  --connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
  --username retail_dba \
  --password cloudera \
  --query "select department_id, department_name, replace(department_name, '/\0', '*') from departments"

I didn't get your error though. Hope it helps.

Upvotes: 1

Related Questions