question Re: sqoop unable to import data from two tables in Archives of Support Questions (Read Only)

sqoop unable to import data from two tables

csguna — Fri, 16 Sep 2022 10:47:32 GMT

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

Re: sqoop unable to import data from two tables

saranvisa — Wed, 16 Nov 2016 15:34:35 GMT

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 \

Re: sqoop unable to import data from two tables

csguna — Wed, 16 Nov 2016 16:20:35 GMT

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 ?

Re: sqoop unable to import data from two tables

saranvisa — Wed, 16 Nov 2016 16:33:36 GMT

$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

Re: sqoop unable to import data from two tables

csguna — Thu, 17 Nov 2016 03:07:51 GMT

It worked . Thanks mate

Re: sqoop unable to import data from two tables

Samia — Tue, 15 May 2018 17:53:15 GMT

[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