Search code examples
copyamazon-redshift

AWS Redshift Copy Command Dynamic source file path

Is there a way to pass in current_date to copy command for the S3 path

for ex : Copy tablename from 's3/rootlocation/_current_date_/*.txt in AWS Redshift

Solution

  • John is correct, it's not possible to dynamically build the COPY statement. However, I found a way to work around this, using SQL only, with just a few more commands:

    create temporary table _path as 
    select (
        '{"entries":[{"url":"s3://bucket/customer' || 
        getdate()::date || 
        '.txt", "mandatory":true}]}'
    )::varchar(255)
;

unload ('select * from _path') to 's3://bucket/customer.manifest'
credentials '' parallel off
;

copy customer from 's3://bucket/customer.manifest000' credentials '' manifest;

    See http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html and http://docs.aws.amazon.com/redshift/latest/dg/loading-data-files-using-manifest.html.