Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Error executing statement: java.sql.SQ LException: No suitable driver found when running oozie sqoop action

Highlighted

Error executing statement: java.sql.SQ LException: No suitable driver found when running oozie sqoop action

New Contributor

yarn.txt

I am getting error when i run the command yarn logs -applicationId application_1532367457044_47155. I have attached yarn.txt file contains oozie job log. Kindly look into that.

Below output is the sample error of yarn logs saying driver issue when importing sqoop job in oozie. I gave oozie.libpath=${nameNode}/user/oozie/share/lib/lib_20180703052502/sqoop as libpath in job.properties.

I don't know where i am wrong , please help me out to find the solution.

For your kind info i have attached both workflow.xml and job.properties.

what should i change to get this work ??

Successfully loaded & initialized native-zlib library18/07/28 08:49:17 INFO compress.CodecPool: Got brand-new decompressor [.deflate]2018-07-28 07:11:35,988 [main] ERROR org.apache.sqoop.manager.SqlManager  - Error executing statement: java.sql.SQLException: No suitable driver found for 'jdbc:postgresql://35.189.238.202:5432/c00026'2018-07-28 07:11:36,012 [main] ERROR org.apache.sqoop.tool.ImportTool  - Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter18/07/28 08:49:17 INFO compress.CodecPool: Got brand-new decompressor [.deflate]

job.properties:

nameNode=hdfs://hostname:8020

jobTracker=resoursemanager-hostname:8050

master=yarn-cluster

user.name=oozie

queueName=QE

oozieExecutionFolder=oozietest/sqoop

oozie.use.system.libpath=true

oozie.libpath=${nameNode}/user/oozie/share/lib/lib_20180703052502/sqoop

oozie.wf.application.path=${nameNode}/user/cloud2tpn/${oozieExecutionFolder}



workflow.xml:


<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at
       http://www.apache.org/licenses/LICENSE-2.0
  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-wf">
    <start to="sqoop-node"/>
    <action name="sqoop-node">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <command>import --driver org.postgresql.Driver --connect 'jdbc:postgresql://35.189.238.202:5432/c00026' --username postgres --password psqltn147555 --target-dir hd
fs://s02-cv-bd1.c.project-ngp-clearvue-test.internal:8020/user/cloud2tpn/sqoop_job_oozie --table 'sqoop_job' -m 1 --check-column id --incremental append --last-value 0 --direct </command>
    <file>hdfs://s02-cv-bd1.c.project-ngp-clearvue-test.internal:8020//user/oozie/share/lib/lib_20180703052502/sqoop/postgresql-9.2-1002.jdbc4.jar</file>
        </sqoop>
        <ok to="end"/>
        <error to="fail"/>
    </action>
    <kill name="fail">
        <message>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>





3 REPLIES 3
Highlighted

Re: Error executing statement: java.sql.SQ LException: No suitable driver found when running oozie sqoop action

Master Collaborator

You need the postgres java jar file. Below are similar commands I have to add the jar to ambari-server. You may need to get the jar files to your oozie job and or nodes oozie job is executing on:

Add postgres connector:

cd /usr/share/java
sudo wget https://jdbc.postgresql.org/download/postgresql-42.2.2.jar
sudo ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-42.2.2.jar
sudo ambari-server restart

If this answer helps you please choose ACCEPT.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven @ DFHZ

Highlighted

Re: Error executing statement: java.sql.SQ LException: No suitable driver found when running oozie sqoop action

New Contributor

I tried restarting ambari server with JDBC driver as you mentioned and it's not working for me.

Anyway, thank you for your support.

when I see the errors in yarn logs with application id, repeatedly getting below error.

SqlManager-Error executing statement: java.sql.SQLException: No suitable driver found

looking for some other solution. Please help me

Highlighted

Re: Error executing statement: java.sql.SQ LException: No suitable driver found when running oozie sqoop action

New Contributor

@Steven Matison

I made it work by passing sqoop command as arguments in workflow.xml.

Please have a look at my workflow.xml and job.properties file.

This is working absolutely fine.

job.properties:

nameNode=hdfs://(namenode):8020
jobTracker=(resourcemanager):8050/8032
queueName=default
oozieExecutionFolder=oozietest/sqoop
oozie.use.system.libpath=true
oozie.wf.application.path=${nameNode}/user/${user.name}/${oozieExecutionFolder}



workflow.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at
       http://www.apache.org/licenses/LICENSE-2.0
  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-wf">
    <start to="sqoop-node"/>
    <action name="sqoop-node">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
           <!-- <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>!-->
<!--	  <arg>sudo -u bigdata2tpn</arg>!-->
       	  <arg>import</arg>
          <arg>--connect</arg>
          <arg>jdbc:postgresql://ipaddress:5432/c00026</arg>
          <arg>--username</arg>
          <arg>postgres</arg>
          <arg>--password</arg>
          <arg>password</arg>
          <arg>--target-dir</arg>
          <arg>hdfs://namenode:8020/user/bigdata2tpn/sqoop_job_oozie</arg>
          <arg>--table</arg>
          <arg>sqoop_job</arg>
          <arg>-m</arg>
          <arg>1</arg>
	  <arg>--check-column</arg>
          <arg>id</arg>
          <arg>--incremental</arg>
          <arg>append</arg>
          <arg>--last-value</arg>
          <arg>0</arg>
          <file>hdfs://namenode:8020/user/oozie/share/lib/lib_20180703052502/sqoop/postgresql-9.2-1002.jdbc4.jar</file>
	  <file>hdfs://namenode:8020/user/oozie/share/lib/lib_20180703052502/sqoop/sqoop-1.4.6.2.6.5.0-292.jar</file>
        </sqoop>
        <ok to="end"/>
        <error to="fail"/>
    </action>
    <kill name="fail">
        <message>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

Note:

sqoop commands should be passed within arguments otherwise job will not run. Additionaly you have to mention postgresql driver jar file path inside file tag in workflow.xml. The given configuration is more than enough to run oozie-sqoop job successfully.

Good to go !! :)

Don't have an account?
Coming from Hortonworks? Activate your account here