Search code examples
postgresqljdbcsqooppsqlpgbouncer

Sqoop: how switch off Prepared Statements?

I use Sqoop 1.4.5-cdh5.4.2 and Postgresql.

If Sqoop connects directly to the database - all right. But need use Sqoop over pgbouncer, and I have problem with this.

In pgbouncer you can not do prepared statements transaction mode.

... connect command:

sqoop import \
    --connect "$db_name" \
    --username "$db_user" \
    --password "$db_pass" \
    --direct \
    --hive-import \
    --hive-table "$hive_schema.$t" \
    --hive-overwrite \
    --num-mappers 10 \
    --fetch-size 100000 \
    --split-by "object_id" \
    --target-dir "/user/$hive_schema/$t" \
    --table "$t"

... and error:

org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:791)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:815)
    at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:315)
    at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
    at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
    at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:126)
    at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:188)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:514)
    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)
Solution

  • SQOOP don't work with pgbouncer and transaction pool :(