Support Questions
Find answers, ask questions, and share your expertise

Error when i try to import a table from postgres to hive

When i try to execute a shell job with sqoop or sqoop job in workflow manager i have a error:

I am try to import tables from postgres to hive

2017-11-06 13:58:46,622  WARN ShellActionExecutor:523 - SERVER[ambari.4linux.com.br] USER[admin] GROUP[-] TOKEN[] APP[Fluxo_BigDatinha] JOB[0000003-171106133302301-oozie-oozi-W] ACTION[0000003-171106133302301-oozie-oozi-W@Verifica_Sujeira_HDFS] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.ShellMain], exit code [1]

My Shell Script:

sqoop import --connect jdbc:postgresql://10.0.0.78:5433/cr4prd --username ambari --password xxxxxxx --table tb_con_contatos --target-dir /user/admin/teste2

My Cluster Configuration:

42475-screenshot-2017-11-6-ambari-bigdatinha.png

42475-screenshot-2017-11-6-ambari-bigdatinha.png

I am running in /user/admin/sqoop and inside this directory have a paste with lib:

42476-screenshot-2017-11-6-ambari-bigdatinha1.png

1 ACCEPTED SOLUTION

Accepted Solutions

Super Mentor

@Guilherme Colla

There are not much logging in the provided WARN message to identify the root cause of the issue.

But few things we can check like the Postgres JDBC Driver is jar installed in Oozie sharelib or not? And Sqoop client is installed on all the nodes properly.

View solution in original post

17 REPLIES 17

Super Mentor

@Guilherme Colla

There are not much logging in the provided WARN message to identify the root cause of the issue.

But few things we can check like the Postgres JDBC Driver is jar installed in Oozie sharelib or not? And Sqoop client is installed on all the nodes properly.

View solution in original post

@Jay Kumar SenSharma

my sharelib is:

oozie admin -oozie http://ambari.4linux.com.br:11000/oozie -shareliblist[Available ShareLib]
hive
distcp
mapreduce-streaming
spark
oozie
hcatalog
hive2
sqoop
pig
spark_orig<br>

but i have the postgres.jar inside sqoop folder

42477-screenshot-2017-11-6-ambari-bigdatinha2.png

When i try to execute anyone command with user oozie i have thist error:

ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: org.postgresql.Driver
java.lang.RuntimeException: Could not load db driver class: org.postgresql.Driver
    at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:875)
    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: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)

But when i execute anyone command with root, the command finish with success.

Super Mentor

@Guilherme Colla

As mentioned earlier the symptoms indicates that you have not placed the Postgres SQL driver inside your OOzie sharedlib.

can you please try that. May be you can try putting the Postgresql driver jar inside the "/user/oozie/share/lib/lib*/sqoop" on hdfs.

Then check:

# hdfs fs -ls /user/oozie/share/lib/sqoop/

.

@Jay Kumar SenSharma

I have the postgres jar inside te /user/oozie/share/lib/*/sqoop

hdfs dfs -ls /user/oozie/share/lib/*/sqoop | grep sql
-rw-r--r--   3 root  hdfs       1208 2017-11-01 13:54 /user/oozie/share/lib/lib_20171030154340/sqoop/db.hsqldb.properties
-rw-r--r--   3 root  hdfs       1081 2017-11-01 13:55 /user/oozie/share/lib/lib_20171030154340/sqoop/db.hsqldb.script
-rw-r--r--   3 oozie hdfs     643727 2017-10-30 15:46 /user/oozie/share/lib/lib_20171030154340/sqoop/hsqldb-1.8.0.7.jar
-rw-r--r--   3 root  hdfs     883898 2017-11-01 12:56 /user/oozie/share/lib/lib_20171030154340/sqoop/mysql-connector-java.jar
-rw-r--r--   3 root  hdfs     579785 2017-10-31 15:40 /user/oozie/share/lib/lib_20171030154340/sqoop/postgresql-9.2-1002.jdbc4.jar
-rw-r--r--   3 root  hdfs     446067 2017-11-06 16:01 /user/oozie/share/lib/lib_20171030154340/sqoop/postgresql-jdbc.jar

Super Mentor

@Guilherme Colla

After putting that Jar in the "/user/oozie/share/lib/lib_20171030154340/sqoop/postgresql-9.2-1002.jdbc4.jar" location have you run the "sharelibupdate" command after putting the Jar there?

# oozie admin -oozie http://$HOSTNAME:11000/oozie -sharelibupdate 

.

@Jay Kumar SenSharma

yes, after i put jar i run the sharelibupdate

Super Mentor

@Guilherme Colla

Please check on your HDFS where your "workflow.xml" is present, create "lib" directory there and put the postgresql jdbc driver jar inside that directory (on HDFS).

.

Super Mentor

@Guilherme Colla

Can you also check if your "job.properties" has included the lib?

oozie.action.sharelib.for.sqoop=hive,hcatalog,sqoop

.