Postgres to Sqoop to Hive Orc Table Using hcatalog

mcharbonneau — Wed, 26 Jul 2017 23:58:43 GMT

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

Re: Postgres to Sqoop to Hive Orc Table Using hcatalog

jsensharma — Thu, 27 Jul 2017 00:01:46 GMT

@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/

Re: Postgres to Sqoop to Hive Orc Table Using hcatalog

mcharbonneau — Thu, 27 Jul 2017 04:14:01 GMT

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

question Postgres to Sqoop to Hive Orc Table Using hcatalog in Archives of Support Questions (Read Only)

Postgres to Sqoop to Hive Orc Table Using hcatalog

Re: Postgres to Sqoop to Hive Orc Table Using hcatalog

Re: Postgres to Sqoop to Hive Orc Table Using hcatalog