Redshift drop/create/select query failing in Data Pipeline

1
votes

I'm trying to run a daily migration script in Redshift using Data Pipeline.
The script works as expected when I run it directly using SQL Workbench/J, but fails when triggered through Data Pipeline.

I have reproduced the problem with this simple code:

drop table if exists image_stg;
create table image_stg (like image_full); 
select * from image_stg;

When I run it in Data Pipeline, I get this error:

[Amazon](500310) Invalid operation: relation "image_stg" does not exist;

I also got this error once, for the exact same code, without changing anything:

[Amazon](500310) Invalid operation: Relation with OID 108425 does not exist.;

Here's a screenshot of the two error messages:

Sad screenshot

I've found this thread on the AWS forums, but it didn't help: Pipeline started failing on simple Redshift SqlActivity and temp table

What is causing this error? Is there a workaround?

2
sqlamazon-redshiftamazon-data-pipeline
Check your database connection in Data Pipeline, and try runnig the query with schema prefix (drop table if exists <schema>.image_stg - vtuhtan
Does SQL workbench have autocommit on? Can you reproduce the problem by turning it off? - Brian R Armstrong

2 Answers

1
votes

I've contacted Amazon, and it looks like a problem in Data Pipeline.
They did suggest a workaround that seems to work in my case: Change the JDBC connection string from jdbc:redshift://… to jdbc:postgresql://… .

0
votes

I had the same problem when creating a temporary table in Redshift via Pipeline but the workaround of changing the connection string from jdbc:redshift://… to jdbc:postgresql://… didn't work for me though. My last resort is to create the table as physical table and drop it after use - through Pipeline.