CODEWITHSUNDEEP
mysqlhadoopsqoop
user2774120
user2774120

Reputation: 147

I am getting Error while using sqoop import command on hadoop

Hello I am using below sqoop command which I think should work 

[cloudera@quickstart ~]$ sqoop import --connect jdbc:mysql://quickstart.cloudera:3306/retail_db --username root --password cloudera --query "select * from customers where customer_street like '%Plaza%' AND $CONDITIONS" --target-dir /user/cloudera/problem1/customers/textdata --fields-terminated-by '*' --lines-terminated-by '|' --as-textfile --columns "customer_id,customer_fname,customer_lname,customer_street" --split-by "customer_id"

But i am getting below error related to $conditions which i think implemented correctly.. Please help.. Thanks

Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 19/11/17 10:12:10 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0 19/11/17 10:12:10 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 19/11/17 10:12:10 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 19/11/17 10:12:10 INFO tool.CodeGenTool: Beginning code generation 19/11/17 10:12:10 ERROR tool.ImportTool: Import failed: java.io.IOException: Query [select * from customers where customer_street like '%Plaza%' AND ] must contain '$CONDITIONS' in WHERE clause. at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:332) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1858) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1657) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:494) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243) at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

Upvotes: 1

Views: 1139

Answers (2)

yammanuruarun
yammanuruarun

Reputation: 403

Refer to "7.2.3. Free-form Query Imports" in SqoopUserGuide

Sqoop --query option in the free form select query expects the $CONDITIONS and there are few things to be noted as in the sqoop user guide Notes.

  1. Based on the quotation used for query in --query option we need to use either $CONDITIONS or \$CONDITIONS as shown below. Since you used double quotes \$CONDITIONS must be used to avoid substitution.

Examples:

When using a single mapper, the entire selected data will be transferred sequentially by the one mapper.

 --query 'SELECT * FROM a WHERE $CONDITIONS' -m 1

If double-quotes are used need to add \ to avoid substitutions.

 --query "SELECT * FROM a WHERE \$CONDITIONS" -m 1

When using multiple mappers, selected data will be transferred parallelly by all the mappers after splitting based on the --split-by clause and substituting in place of $CONDITIONS. 

 --query 'SELECT * FROM a WHERE $CONDITIONS' -m 3 --split-by a.column
  1. Instead of using the --table, --columns and --where arguments, you can specify a SQL statement with the --query argument. So --columns should be removed from your command and the number of mappers should be passed using -m or --num-mappers.

Upvotes: 0

airliquide
airliquide

Reputation: 530

Hi the folowing should works 

 sqoop import --connect jdbc:mysql://quickstart.cloudera:3306/retail_db --username root --password cloudera --query "select * from customers where \$CONDITIONS AND customer_street like '%Plaza%'" --target-dir /user/cloudera/problem1/customers/textdata --fields-terminated-by '*' --lines-terminated-by '|' --as-textfile --columns "customer_id,customer_fname,customer_lname,customer_street" --split-by "customer_id"

Upvotes: 1

Related Questions