Sqoop job fails to record password after running for the first time problem fix

How to run sqoop job through oozie successfully

Sqoop jobs are used to create and save the import and export commands. It helps to automate the sqoop tasks and in re-execution of sqoop actions. I mostly find myself writing sqoop jobs when I need to write incremental-imports in Sqoop and to schedule and automate the sqoop tasks through Oozie (mostly). Oozie is an amazing workflow scheduler system that manages apache hadoop jobs.

Problem:

Sqoop jobs fails when running it through Oozie. It runs and completes successfully right after I create it i.e for the first time and the sqoop job starts to fail after this. What I mean is that the sqoop jobs that I create run and complete successfully for the first time and then fail every time thereafter.

Rather than jumping off to the solution directly, let me show you the not so obvious message I kept on getting through Oozie launcher logs:

Error executing statement: java.sql.SQLException: Access denied for user 'root'@'IP' (using password: NO)java.sql.SQLException: Access denied for user 'root'@'IP' (using password: NO)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.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:1846)at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1646)at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)at org.apache.sqoop.Sqoop.run(Sqoop.java:143)at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)at org.apache.sqoop.Sqoop.main(Sqoop.java:236)at org.apache.oozie.action.hadoop.SqoopMain.runSqoopJob(SqoopMain.java:197)at org.apache.oozie.action.hadoop.SqoopMain.run(SqoopMain.java:177)at org.apache.oozie.action.hadoop.LauncherMain.run(LauncherMain.java:49)at org.apache.oozie.action.hadoop.SqoopMain.main(SqoopMain.java:46)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.oozie.action.hadoop.LauncherMapper.map(LauncherMapper.java:236)at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453)at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runSubtask(LocalContainerLauncher.java:388)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runTask(LocalContainerLauncher.java:302)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.access$200(LocalContainerLauncher.java:187)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler$1.run(LocalContainerLauncher.java:230)at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)at java.util.concurrent.FutureTask.run(FutureTask.java:262)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)at java.lang.Thread.run(Thread.java:745)6483 [uber-SubtaskRunner] ERROR org.apache.sqoop.tool.ImportTool - Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriterat org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1652)at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)at org.apache.sqoop.Sqoop.run(Sqoop.java:143)at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)at org.apache.sqoop.Sqoop.main(Sqoop.java:236)at org.apache.oozie.action.hadoop.SqoopMain.runSqoopJob(SqoopMain.java:197)at org.apache.oozie.action.hadoop.SqoopMain.run(SqoopMain.java:177)at org.apache.oozie.action.hadoop.LauncherMain.run(LauncherMain.java:49)at org.apache.oozie.action.hadoop.SqoopMain.main(SqoopMain.java:46)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.oozie.action.hadoop.LauncherMapper.map(LauncherMapper.java:236)at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453)at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runSubtask(LocalContainerLauncher.java:388)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runTask(LocalContainerLauncher.java:302)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.access$200(LocalContainerLauncher.java:187)at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler$1.run(LocalContainerLauncher.java:230)at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)at java.util.concurrent.FutureTask.run(FutureTask.java:262)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)at java.lang.Thread.run(Thread.java:745)

Catch it right there!It says:
Access denied for user 'root'@'IP' (using password: NO)

using password:NO was the obvious hint for me. I was trying to import data from a MySQL database to HDFS and hive through a Sqoop job.

My typical Sqoop job looked like this:

sqoop job --meta-connect jdbc:hsqldb:hsql://FQDN:16000/sqoop --create sample_job -- import --driver com.mysql.jdbc.Driver --connect jdbc:mysql://IP/db?zeroDateTimeBehavior=convertToNull --username USERNAME --password 'PASSWORD' --table tableName --merge-key id --split-by id --hive-import --hive-overwrite --hive-database hiveDBNAME

I then tried executing this sqoop from Command line interface with the following command:

job --meta-connect jdbc:hsqldb:hsql://FQDN:16000/sqoop  --exec sample_job -- --warehouse-dir DIRNAME

and BAM!

I was prompted for password.

So, basically the sqoop

Sqoop job fails to record password

That’s EVIL! Ask my WHY?

Since there is no user interaction when we are running jobs through oozie(Of course that’s the first rule of automation) and it’s asking for password? EVIL! EVIL! EVIL!
So how do you provide sqoop jobs the password?

1. You need to edit sqoop-site.xml and lookout for a property named sqoop.metastore.client.record.password and set it to true.

It should look like this:
<property><name>sqoop.metastore.client.record.password</name><value>true</value><description>If true, allow saved passwords in the metastore.</description></property><property>

Note: This property is probably commented out by default and is set to false so do not forget to uncomment it after setting it to true in sqoop-site.xml

Note:2 If your are using cloudera distribution of hadoop, you can find and edit sqoop-site.xml using the command on linux but anyway just needed to point you to the location of sqoop-site.xml.
vi /opt/cloudera/parcels/CDH-5.7.0-1.cdh5.7.0.p0.45/etc/sqoop/conf.dist/sqoop-site.xmlNote 3: Anyway, you can find the location yourself using the command:
find / -name sqoop-site.xml

Well, I had set this to true already and then I was like :'(

Now, I found only 2 reasons for this could be happening:

1.

Sqoop is not recording password

that is passed to it through argument --password.
In my case it was and I know it because I could run half the sqoop jobs from oozie smoothly with no interruptions, so of course it was recording passwords.
To get myself even more sure, I executed this command to check if it should need password to run:

sqoop job --meta-connect jdbc:hsqldb:hsql://FQDN:16000/sqoop --show sample_job |grep db.require.password

It returned false

Alright! So, problem is not here.

2. I was executing the jobs that were created before I changed the property sqoop.metastore.client.record.password value to true in sqoop-site.xml. Jobs that were created when the property was set to false would not start recording the passwords if you change the value for the respective property in sqoop-site.xml

Note: I had all my jobs created after I had set the value to true in sqoop-site.xml

What do I do now?

Created and posted my first bug report on https://issues.apache.org/jira/browse/SQOOP-2940nd but no response YET!

Alrighty, I googled endlessly for 3 days, asked questions on forums and yet NO SOLUTION to my help.

Finally, after having killed my 3 days hopelessly, I tried this fix for the problem (Though I still wish SOMEONE out there had hinted me this)

SOLUTION:

In my oozie workflow where I have my sqoop job execution commands, I explicitly pass the password there.

So, this is what my command in Oozie workflow looks like for execution of sqoop jobs with recorded passwords and thus resulting in successfully completed sqoop jobs

job --meta-connect jdbc:hsqldb:hsql://FQDN:16000/sqoop  --exec sample_job -- --warehouse-dir DIRNAME --password YOURPASSWORD

This is only a workaround to the problem

Sqoop job fails to record password after running for the first time

posted a lot of times in different forums and I await a fix for this bug.

Phewwww! That fixed it and I danced in the glory of it. I found it only through hit and try and nothing else.

I though really wish there were forums dedicated for hadoop problems with more active user participation and support. It would really save a lot of time. Let’s see if I hear back anything on JIRA issue I created with a fix for the issue anytime soon but I hope this post saved you some time and could help.

P.S: Internet is a successfully thriving community from generous contributions of people from across th globe. You can help it thrive too. Please contribute. As a reader, you can be contributing with your valuable feedbacks.
Please drop by a comment or Share to let people know you were here. ๐Ÿ™‚ ๐Ÿ™‚

2 thoughts on “Sqoop job fails to record password after running for the first time problem fix

Leave a Reply

Your email address will not be published. Required fields are marked *