CODEWITHSUNDEEP
pythonamazon-s3amazon-redshiftboto3
Alfredo Suárez
Alfredo Suárez

Reputation: 1

Redshift from Boto3 - ERROR: syntax error at or near "'s3://xx-xx-xx/xx-xx-x/x-x-xx/xxx.csv'"

I'm having issues updating a Redshift table, and honestly I am not sure what I'm doing wrong. Is it even possible to update like this from S3? Im doing a JOIN on the Primary key which is a unique key, so that I can update only existing rows. I appreciate your help:

update "red"."shift"."table" 
set bid=s.bid, user_id=s.user_id, username=s.username, total_sum=s.total_sum,amount_currency=s.amount_currency,allowance_name=s.allowance_name,ledgerentrytype=s.ledgerentrytype,transaction_timestamp_group=cast(s.transaction_timestamp_group as timestamp),employer=s.employer,taxreportingstatus=s.taxreportingstatus
from 's3://xx-xx-xx/xx-xx-x/x-x-xx/xxx.csv' as s
join "red"."shift"."table" as t on s.bid=t.bid
iam_role 'arn:aws:iam::xxxxx:role/service-role/xxxx-xx-xx-xx```

Upvotes: 0

Views: 96

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

The FROM clause of the UPDATE statement needs to point to a table in Redshift not an S3 object. See https://docs.aws.amazon.com/redshift/latest/dg/r_UPDATE.html

Now you can define a table in Redshift and COPY the data from this S3 object into this table, then perform the UPDATE. Or, you can define an external table in Redshift that references this object and use this table in the UPDATE statement. Either way Redshift needs a defined table for the UPDATE statement.

Upvotes: 1

Related Questions