Table doesn't exist : Importing from Sqoop to HDFS


#1

Hi All,
I am trying to import a table from MySql (retail_export database) to HDFS.
each time I try, am getting an error that the table doesn’t exist in retail_export database, though it does.
I have dropped and recreated the table multiple times, but still its showing the same error.

KIndly find the code and error below:

sqoop import
–connect jdbc:mysql://ms.itversity.com:3306/retail_export
–username retail_user
–password itversity
–table products_aparna
–target-dir /user/aparna149/Exercise5/products_text
–fields-terminated-by ‘|’
–lines-terminated-by ‘\n’
–null-non-string -1
–null-string ‘NOT-AVAILABLE’
–where “product_id >=1 and product_id <= 1000”
–as-textfile
-m 3

Error is:
18/04/27 14:47:02 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘retail_export.products_aparna’ doesn’t exist
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘retail_export.products_aparna’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
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:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2326)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
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:328)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)
18/04/27 14:47: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:1659)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

Please help me solving this.

Thanks in advance,
Aparna


#2

use this command to search if your table exist.
sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query “show tables”
if exist do count to see the correct number.
sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query "select count(*) from products_aparna "


#3

There is no table with that name.
these are the tables with your name
sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query “show tables” | grep -i aparna
18/04/27 16:31:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
18/04/27 16:31:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/04/27 16:31:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
| aparna_daily_revenue |
| aparna_daily_revenue_stage |
| products_external_aparna |
| products_replica_aparna |
| result_aparna |

kindly create new table in the mysql database or use any of the above tables.


#4

Hi Raghu,
Thanks a lot for your reply.
I tried this as well n many other eval commands earlier, That’s where I am struggling as it doesn’t show the table while running sqoop, while in the same time if you go to MySQL and check in retail_export database, you can find it there .

On the other hand, in MySQL, products_replica_aparna doesn’t exist anymore as I already had dropped it earlier today, but strange, it’s showing when sqoop command is run.(as you did above)
MySQL and Sqoop are not in sync in my case.

Hope you get my issue and help me in fixing this.

Thanks
Aparna


#5

@AparnaSen
I logged into Mysql and used retail_export database. searched for table named products_aparna but i cannot find that. In the above thread you mentioned you deleted the table products_replica_aparna( Please check below regarding this table).
MySQL [(none)]> use retail_export;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [retail_export]> show tables ;

MySQL [retail_export]> select * from products_aparna limit 10;
ERROR 1146 (42S02): Table ‘retail_export.products_aparna’ doesn’t exist

MySQL [retail_export]> select * from products_replica_aparna limit 5;
±-----------±--------------------±----------------------------------------------±--------------------±--------------±--------------------------------------------------------------------------------------±---------------±------------------+
| product_id | product_category_id | product_name | product_description | product_price | product_image | products_grade | product_sentiment |
±-----------±--------------------±----------------------------------------------±--------------------±--------------±--------------------------------------------------------------------------------------±---------------±------------------+
| 1 | 2 | Quest Q64 10 FT. x 10 FT. Slant Leg Instant U | | 59.98 | http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy | NULL | NULL |
| 2 | 2 | Under Armour Men’s Highlight MC Football Clea | | 129.99 | http://images.acmesports.sports/Under+Armour+Men’s+Highlight+MC+Football+Cleat | NULL | NULL |
| 3 | 2 | Under Armour Men’s Renegade D Mid Football Cl | | 89.99 | http://images.acmesports.sports/Under+Armour+Men’s+Renegade+D+Mid+Football+Cleat | NULL | NULL |
| 4 | 2 | Under Armour Men’s Renegade D Mid Football Cl | | 89.99 | http://images.acmesports.sports/Under+Armour+Men’s+Renegade+D+Mid+Football+Cleat | NULL | NULL |
| 5 | 2 | Riddell Youth Revolution Speed Custom Footbal | | 199.99 | http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet | NULL | NULL |
±-----------±--------------------±----------------------------------------------±--------------------±--------------±--------------------------------------------------------------------------------------±---------------±------------------+

requesting you to create your desire named table(products_aparna) and try again.:smiley:
I think you deleted thie table(products_aparna)


#6

Hi Raghu,
Thanks for your time and effort.but the thing is when I login to MySQL, I can see products_aparna but not products_replica_aparna.
I checked creating other tables as well in same retail_export database but still not able to find them when I run sqoop command, when they actually exist in MySQL.

Please check below what is exactly showing in my MySQL and Sqoop.

mysql> use retail_export;
Database changed
mysql> show tables like ‘%aparna%’;
±-----------------------------------+
| Tables_in_retail_export (%aparna%) |
±-----------------------------------+
| dept_aparna |
| products_aparna |
| result_aparna |
±-----------------------------------+
3 rows in set (0.00 sec)

mysql> select count() from products_replica_aparna;
ERROR 1146 (42S02): Table ‘retail_export.products_replica_aparna’ doesn’t exist
mysql> select count(
) from products_aparna;
±---------+
| count(*) |
±---------+
| 1345 |
±---------+
1 row in set (0.00 sec)

mysql> select * from dept_aparna;
±--------------±----------------+
| department_id | department_name |
±--------------±----------------+
| 1 | Grocery |
| 2 | Fitness |
| 3 | Footwear |
| 4 | Apparel |
| 5 | Golf |
| 6 | Outdoors |
| 7 | Fan Shop |
±--------------±----------------+
7 rows in set (0.00 sec)

sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query “show tables” | grep -i aparna
18/04/28 01:18:04 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
18/04/28 01:18:04 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/04/28 01:18:05 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
| aparna_daily_revenue |
| aparna_daily_revenue_stage |
| products_external_aparna |
| products_replica_aparna |
| result_aparna |

sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query “desc products_aparna” Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/04/28 01:55:49 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
18/04/28 01:55:49 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/04/28 01:55:50 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/04/28 01:55:51 WARN tool.EvalSqlTool: SQL exception executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘retail_export.products_aparna’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
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:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1383)
at org.apache.sqoop.tool.EvalSqlTool.run(EvalSqlTool.java:68)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

[aparna149@gw03 ~]$ sqoop eval --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --query “desc dept_aparna”
Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/04/28 01:20:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
18/04/28 01:20:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/04/28 01:20:31 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/04/28 01:20:32 WARN tool.EvalSqlTool: SQL exception executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘retail_export.dept_aparna’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
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:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1383)
at org.apache.sqoop.tool.EvalSqlTool.run(EvalSqlTool.java:68)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

Hope now you can understand the issue.

Thanks
Aparna


#7

FYI

MySQL [retail_export]> show tables like ‘%aparna%’;
±-----------------------------------+
| Tables_in_retail_export (%aparna%) |
±-----------------------------------+
| aparna_daily_revenue |
| aparna_daily_revenue_stage |
| products_aparna_external |
| products_external_aparna |
| products_replica_aparna |
| result_aparna |
±-----------------------------------+
6 rows in set (0.00 sec)

MySQL [retail_export]> select count() from products_replica_aparna;
±---------+
| count(
) |
±---------+
| 1352 |
±---------+
1 row in set (0.00 sec)

is this what you are using to connect to mysql?
mysql -u retail_user -h ms.itversity.com -p


#9

Thanks a lot again Raghu for your time and effort you have given to solve my issue.
Really appreciate!
You guessed it correct. The entire issue was arrising because of different hosts I used for both, which I could not notice earlier.:unamused:

Thanks
Aparna


#10

Thats fine. No problem :smiley: