Error while import all tables - SQOOP

#1

When i use import all tables with below query, i get error.

My query is as follows

sqoop import-all-tables
-m 12
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username retail_dba
–password itversity
–as-avrodatafile
–warehouse-dir=/user/pruthvikonda9/hive/warehouse/retail_stage.db

0 Likes

#2

@Pruthvi_Konda - Not sure what error you are facing. Try Dmapreduce.job.user.classpath.first=true option in import.

0 Likes

#3

How exactly do you want me to use ? Also, when i do the import all. It doesn’t copy all the tables with retail_db database. Only copies, department table and not the others.

Help is appreciated !

0 Likes

#4

@Pruthvi_Konda, for the all tables import, all the tables in the retail database needs to have primary keys. It might be that the next table after departments do not have a primary key, it is the reason it might be failing. Set the -m to 1 and run it

0 Likes

#5

nope. that’s not working too. I used below query

sqoop import-all-tables
-m 1
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username=retail_dba
–password=itversity
–as-avrodatafile
–warehouse-dir=/user/pruthvikonda9/hive/warehouse/retail_stage.db

0 Likes

#6

Also, when i try that, only the first table (actor) is getting copied and not anything else

0 Likes

#7

Can you post your error message.

0 Likes

#8

I received diff error now. I’m getting

“# There is insufficient memory for the Java Runtime Environment to continue.”

0 Likes

#9

to import table in hive don’t we need to use hive related commands like --hive -import
–create-hive-table

0 Likes

#10

May be there is no sufficient memory.
May be there might files, directories already exist in home path.

0 Likes

#11

I checked and there is no directory in that destination path.

I can only see actors table

0 Likes

#12

@Pruthvi_Konda

Please share the error log.

0 Likes

#13

16/12/13 01:30:35 INFO mapreduce.ImportJobBase: Retrieved 109 records.
16/12/13 01:30:35 INFO tool.CodeGenTool: Beginning code generation
16/12/13 01:30:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM current_dept_e mp AS t LIMIT 1
16/12/13 01:30:35 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: View
’retail_db.current_dept_emp’ references invalid table(s) or column(s) or function(s) or definer/inv
oker of view lack rights to use them
java.sql.SQLException: View ‘retail_db.current_dept_emp’ references invalid table(s) or column(s) o
r function(s) or definer/invoker of view lack rights to use them
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
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.ImportAllTablesTool.run(ImportAllTablesTool.java:111)
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)
16/12/13 01:30:35 ERROR tool.ImportAllTablesTool: Encountered IOException running import job: java.
io.IOException: No columns to generate for ClassWriter

0 Likes

#14

@Pruthvi_Konda -
You can change the warehouse directory path as per below instead of trying to store in Hive database.

sqoop import-all-tables -Dmapreduce.job.user.classpath.first=true
-m 1
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username retail_dba
–password itversity
–as-avrodatafile
–warehouse-dir=/user/pruthvikonda9/sqoop_import

@itversity - retail_db.current_dept_emp table don’t have access to import.

0 Likes

#15

Here is the error. You need to look into view if it is valid or not.

0 Likes

#16

I guess you are using Sakila Database, even I faced the same situation where I could only see Actors table. My problem got resolved after using -m 1 in the end of the sqoop statement. I had to use -m 1 because the very next table which is Actor_info does not have Primary Key in it. So, either use split-by or use -m 1.

But I can see you have tried using -m 1 and -m 12 (but in the beginning of the code). Lets try -m 1 in the end of the sqoop statement.

Good Luck!

0 Likes

#17

I understood the issue, people have created many tables, views etc in retail_db. I will clean up all the other tables than the main 6 tables and change the permissions for retail_dba.

0 Likes

#18

Yes @itversity we should change the permissions of the databases. For sqoop export, it is better to create another database and allow people to export the tables there. It is better to leave the retail_db clean.

0 Likes