Postgres to Sqoop to Hive Orc Table Using hcatalog

[#1]

Hello all,

I have read all the previous posts on this subject but I am running into a error that I can't troubleshoot. I know @Ravi Mutyala has some great posts about this but it didn't work for me.

Here is the script I run:

sudo -u hdfs sqoop import
--connect jdbc:postgresql://xyz
--username xyz
--password xyz 
--table upstream_lead_vendors
--hcatalog-database default
--hcatalog-table test_sqoop_orc_2
--create-hcatalog-table 
--hcatalog-storage-stanza "stored as orcfile" -m 1
--driver org.postgresql.Driver

Here is the error the error msg:

Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/07/26 16:39:48 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
17/07/26 16:39:48 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/07/26 16:39:48 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.
17/07/26 16:39:48 INFO manager.SqlManager: Using default fetchSize of 1000
17/07/26 16:39:48 INFO tool.CodeGenTool: Beginning code generation
17/07/26 16:39:49 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: No suitable driver found for jdbc:postgresql://REMOVED FOR SECURITY
java.sql.SQLException: No suitable driver found for jdbc:postgresql://REMOVED FOR SECURITY
	at java.sql.DriverManager.getConnection(DriverManager.java:689)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)
	at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:744)
	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
	at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
	at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
	at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
	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.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)
17/07/26 16:39:49 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651)
	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.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)

Any help would be greatly appreciated!

Marc

[#2 · Accepted solution]

@Marc Charbonneau

The following error indicates that your JDBC URL is not correct. May be the Database Name is missing at the end of the host port of the DB.

java.sql.SQLException: No suitable driver found for jdbc:postgresql://REMOVED FOR SECURITY

Syntax:

jdbc:postgresql://db_host:port/databaseName   


Example:
jdbc:postgresql://erie1.example.com:5432/ambari

Example:

  sudo -u hdfs sqoop import --connect jdbc:postgresql://erie1.example.com:5432/ambari --username ambari --password bigdata --table hosts --hcatalog-database default  --hcatalog-table test_sqoop_orc_2 --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile" -m 1  --driver org.postgresql.Driver

.

Also please check if the Correct JDBC driver is present in the sqoop client classpath.

Example:

cp   -f   /PATH/TO/postgresql-9.3-1101-jdbc4.jar    /usr/hdp/current/sqoop-client/lib/

.

[#3]

@Jay SenSharma that did it, i forget the / between port and dbName! Thank you very much