Cannot connect to Redshift and PostgreSQL in same Job

Problem

While using Redshift and PostgreSQL components in a Job, the following error occurs:

Exception in component tPostgresqlInput_1 (test_redshift_postgresql)
java.sql.SQLException: [Amazon](500002) Error setting metadata information: {0}.
at com.amazon.redshift.api.PGDataTypeUtilities.createColumn(PGDataTypeUtilities.java:732)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:743)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:699)
at com.amazon.jdbc.common.SForwardResultSet.initializeResultSetColumns(Unknown Source)
at com.amazon.jdbc.jdbc41.S41ForwardResultSet.getMetaData(Unknown Source)
at com.amazon.jdbc.common.SForwardResultSet.initializeColumnNameMap(Unknown Source)
at com.amazon.jdbc.common.SStatement.executeQuery(Unknown Source)
at 
Caused by: com.amazon.support.exceptions.GeneralException: [Amazon](500002) Error setting metadata information: {0}.
[FATAL]: [Amazon](500002) Error setting metadata information: {0}.
java.sql.SQLException: [Amazon](500002) Error setting metadata information: {0}.
at com.amazon.redshift.api.PGDataTypeUtilities.createColumn(PGDataTypeUtilities.java:732)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:743)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:699)
at com.amazon.jdbc.common.SForwardResultSet.initializeResultSetColumns(Unknown Source)
at com.amazon.jdbc.jdbc41.S41ForwardResultSet.getMetaData(Unknown Source)
at com.amazon.jdbc.common.SForwardResultSet.initializeColumnNameMap(Unknown Source)
at com.amazon.jdbc.common.SStatement.executeQuery(Unknown Source)
... 9 more
Caused by: com.amazon.dsi.exceptions.NumericOverflowException
at com.amazon.dsi.dataengine.utilities.ColumnMetadata.setColumnLength(Unknown Source)
at com.amazon.redshift.api.PGDataTypeUtilities.createColumn(PGDataTypeUtilities.java:728)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:743)
at com.amazon.redshift.dataengine.PGResultSet.getSelectColumns(PGResultSet.java:699)
at com.amazon.jdbc.common.SForwardResultSet.initializeResultSetColumns(Unknown Source)
at com.amazon.jdbc.jdbc41.S41ForwardResultSet.getMetaData(Unknown Source)
at com.amazon.jdbc.common.SForwardResultSet.initializeColumnNameMap(Unknown Source)
at com.amazon.jdbc.common.SStatement.executeQuery(Unknown Source)

 

Solution

Explicitly cast the string literal/column alias in your SQL query to a varchar and try again.

 

For example, this query work as long as the Job has no connection to Redshift:

select 'test', count(*) as mycount from test;

 

This query works all the time:

select cast('test' as varchar), count(*) as mycount from test;
Version history
Revision #:
4 of 4
Last update:
‎09-29-2018 12:19 AM
Updated by:
 
Labels (3)