How to COPY data into Redshift from S3 using a dynamic file path

0

I have several folders in S3 generated from logs in the format: s3://bucketname/2024/03/28/17 s3://bucketname/2024/03/29/18 s3://bucketname/2024/03/28/19 date part '2024/03/28/17' are generated in UTC year, month, date, and hour

I have tried to construct the S3 folder name using || and concat but Redshift doesn't support those, for example: COPY table From 's3://bucketname/2024/' || to_char(getdate(), 'MM') || '/' || to_char(getdate(), 'DD') || '/' || to_char(getdate(), 'HH') IAM_ROLE 'arnxxxx' DELIMITER '\t'

Is there anyway to construct a dynamic file path for COPY command based on dates?

asked 16 days ago255 views
1 Answer
1
Accepted Answer

The COPY command does not support the use of functions or expressions directly within the FROM clause. This means that you cannot use the CONCAT function directly in the FROM clause of the COPY command.

For you archive this desired functionality, you would need to programmatically construct the S3 file path in your client application or script before passing the complete path as a string to the COPY command. You might do this in a Python script:

import datetime
import boto3

# Get the current date and time
now = datetime.datetime.now()

# Construct the S3 file path
s3_path = 's3://bucketname/{}/{}/{}/{}'.format(
    now.year,
    str(now.month).zfill(2),
    str(now.day).zfill(2),
    str(now.hour).zfill(2)
)

# Example Redshift COPY command
copy_command = f"""
COPY table_name
FROM '{s3_path}'
IAM_ROLE 'arn:aws:iam::<account_id>:role/<role_name>'
DELIMITER '\\t';
"""

print(copy_command)
profile picture
EXPERT
answered 16 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions