Support Questions

Find answers, ask questions, and share your expertise

Import all tables from MySQL to HIVE using SQOOP

Explorer

Hi

  Following command is the one which I tried to import all tables from MySQL to HIVE but it seems i am doing wrong somewhere. can anyone please help me to solve this?

 

sqoop import-all-tables --connect jdbc:mysql://localhost/forhive --username root  -P  --m 1 --hive-import --hive-database default --warehouse-dir /user/forhive --driver com.mysql.jdbc.Driver

 

Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
19/12/03 23:36:09 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
Enter password:
19/12/03 23:36:16 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
19/12/03 23:36:16 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
19/12/03 23:36:16 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
19/12/03 23:36:16 INFO manager.SqlManager: Using default fetchSize of 1000
19/12/03 23:36:16 ERROR manager.SqlManager: Error reading database metadata: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
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.ConnectionImpl.getInstance(ConnectionImpl.java:381)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
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.listTables(SqlManager.java:539)
at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:94)
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)
Could not retrieve tables list from server
19/12/03 23:36:16 ERROR tool.ImportAllTablesTool: manager.listTables() returned null

2 REPLIES 2

Mentor

@lalprasanth 

That's a permission issue but root should by default have access to all the MySQL databases. Can you run this snippet

On the MySQL host 

# mysql -uroot -p'%root_password%'

 

Then grant root the desired privileges

 

mysql >GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '%root_password%' WITH GRANT OPTION;

Then retry  the import

 

Guru
@lalprasanth

"localhost" will not work here, because MySQL is accessed remotely from NodeManager hosts, not from the host you ran Sqoop command. So you need to use the FQDN of the MySQL host here.

Please also make sure that MySQL is reachable from ALL NodeManager hosts and can use the same username and password to login from those hosts.

Cheers
Eric
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.