Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Solved Go to solution

sqoop unable to import data from two tables

Labels:
avatar
csguna author-rank
Champion

Created on ‎11-15-2016 02:44 AM - edited ‎09-16-2022 03:47 AM

I am facing the sql error and sqoop error in two senarios . 

I performing this data for testing.

 

DB - mysql

Sqoop version : Sqoop version: 1.4.4-cdh5.0.0

 

My table citi

+------+------------+-----------+
| id   | country_id | city      |
+------+------------+-----------+
|   10 |        101 | omaha     |
|   11 |        102 | coloumbus |
|   12 |        103 | buff      |
+------+------------+-----------+

table country 

 

+------------+---------+
| country_id | country |
+------------+---------+
|        101 | us      |
|        102 | in      |
|        103 | nz      |
+------------+---------+

below is my sqoop import 

 

sqoop import \
> --connect jdbc:mysql://localhost/ms4 \
> --username xxx \
> --password yyy \
> --query 'SELECT citi.id, \
> country.name, \
> citi.city \
> FROM citi \
> JOIN country USING(country_id) \
> --num-mappers 1 \
> --target-dir cities

below is the error i am seeing . I dont find anything wrong with my --query to my knoweldge . 

 

16/11/15 05:27:02 INFO manager.SqlManager: Executing SQL statement: SELECT citi.id, \
country.name, \
citi.city \
FROM citi \
JOIN country USING(country_id) \
WHERE  (1 = 0) 
16/11/15 05:27:02 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\
country.name, \
citi.city \
FROM citi \
JOIN country USING(country_id) \
WHERE' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\
country.name, \
citi.city \
FROM citi \
JOIN country USING(country_id) \
WHERE' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2283)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:699)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:708)
	at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:243)
	at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:233)
	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:356)
	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
	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:222)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
16/11/15 05:27:02 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
	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:222)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:240)

As a work around or trying to see if the WHERE $CONDITIONS is problem or not 

i skip WHERE $CONDITIONS by forcing the sqoop to use one mapper 

 

sqoop import \
> --connect jdbc:mysql://localhost/movielens \
> --username training \
> --password training \
> --query 'SELECT citi.id, \
> country.name, \
> citi.city \
> FROM citi \
> JOIN country USING(country_id)' \
> --num-mappers 1 \
> --target-dir cities

I pretty sure we can force sqoop to avoid parallelism ,but it is complaining or throwing error. 

 

ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Query [SELECT citi.id, \
country.name, \
citi.city \
FROM citi \
JOIN country USING(country_id)] must contain '$CONDITIONS' in WHERE clause.
	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:352)
	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
	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:222)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231

I would greatlt appreciate for any kind of information or solution.

Thanks 

 

10,949 Views
0 Kudos
1 ACCEPTED SOLUTION

avatar
saranvisa author-rank
Champion

Created ‎11-16-2016 07:34 AM

You can break your sqoop steps using \ but you should not break your query. Also 'where $CONDITIONS' is mandatory and $CONDITIONS should be in upper case. Pls do the below correction and try again

 

Currection version (only few steps covered):
> --username xxx \
> --password yyy \
> --query 'SELECT citi.id, \
> country.name, \
> citi.city \
> FROM citi \
> JOIN country USING(country_id) \
> --num-mappers 1 \

Updated version (only few steps covered):
> --username xxx \
> --password yyy \
> --query 'SELECT citi.id, country.name, citi.city FROM citi JOIN country USING(country_id) where $CONDITIONS'  \
> --num-mappers 1 \

View solution in original post

10,928 Views
0 Kudos
5 REPLIES 5

avatar
saranvisa author-rank
Champion

Created ‎11-16-2016 07:34 AM

You can break your sqoop steps using \ but you should not break your query. Also 'where $CONDITIONS' is mandatory and $CONDITIONS should be in upper case. Pls do the below correction and try again

 

Currection version (only few steps covered):
> --username xxx \
> --password yyy \
> --query 'SELECT citi.id, \
> country.name, \
> citi.city \
> FROM citi \
> JOIN country USING(country_id) \
> --num-mappers 1 \

Updated version (only few steps covered):
> --username xxx \
> --password yyy \
> --query 'SELECT citi.id, country.name, citi.city FROM citi JOIN country USING(country_id) where $CONDITIONS'  \
> --num-mappers 1 \
10,929 Views
0 Kudos

avatar
csguna author-rank
Champion

Created ‎11-16-2016 08:20 AM

I will certainly try to write the query in single line as per you suggestion , but i am wondering why do we need a placeholder $CONDITIONS When we are forcing the sqoop to perform only one job by using -num-mappers 1 ?
10,919 Views
0 Kudos

avatar
saranvisa author-rank
Champion

Created ‎11-16-2016 08:33 AM

$CONDITIONS to be appended with query. it is sqoop syntax and not related to -num-mappers.

 

 

Ex: select col1, col2 from table1 where $CONDITIONS

it seems you are also missing --split-by columns... it is also recommanded/mandatory for import

 

Thanks

Kumar

 

 

10,916 Views
0 Kudos

avatar
csguna author-rank
Champion

Created ‎11-16-2016 07:07 PM

It worked . Thanks mate
10,904 Views
0 Kudos

avatar
author-rank Samia
New Contributor

Created ‎05-15-2018 10:53 AM

[cloudera@quickstart ~]$ ^C
[cloudera@quickstart ~]$ sqoop import --connect jdbc:mysql://192.168.186.128:3306/myfirsttutorial --username cloudera --password cloudera --table mytable --target-dir=/user/cloudera/myfirst-m
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/05/15 10:47:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
18/05/15 10:47:00 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/15 10:47:00 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/05/15 10:47:00 INFO tool.CodeGenTool: Beginning code generation
18/05/15 10:47:01 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Access denied for user 'cloudera'@'quickstart.cloudera' (using password: YES)
java.sql.SQLException: Access denied for user 'cloudera'@'quickstart.cloudera' (using password: YES)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:870)
 at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4332)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1258)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
 at java.sql.DriverManager.getConnection(DriverManager.java:571)
 at java.sql.DriverManager.getConnection(DriverManager.java:215)
 at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904)
 at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
 at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763)
 at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
 at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
 at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
 at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
 at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327)
 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)
18/05/15 10:47:01 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
 at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1663)
 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)

[cloudera@quickstart ~]$ ^C
[cloudera@quickstart ~]$

 

 

I have just started cloudera i am stuck here can anybody hep

7,732 Views
0 Kudos
Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements