How to add dynamic filename to the file which is getting unloaded from redshift unload query with a partition by column

Question

```
CREATE temp table temp_table (
        id INT,
        name VARCHAR(50),
        age INT,
        city VARCHAR(50)
    );
INSERT INTO temp_table VALUES
        (1, 'John', 25, 'New York'),
        (2, 'Jane', 30, 'Los Angeles'),
        (3, 'Bob', 28, 'Chicago'),
        (4, 'Alice', 22, 'San Francisco');
UNLOAD ('SELECT * FROM temp_table')
TO 's3:///folder_1/'
IAM_ROLE ''
ALLOWOVERWRITE
PARALLEL OFF
DELIMITER as ','
ADDQUOTES
header
PARTITION BY (city);

```

The file name which is generated in each of the folders is 000
Is there a way to change the filename based on the columns for eg "newyork_000", "losangeles_000" directly in this unload query?

Answer

When using the UNLOAD command to extract data from Redshift with a dynamic file name based on a partition column, you can achieve this by using a combination of SQL and scripting. Here's a general outline of the approach:

Use SQL to generate the UNLOAD command dynamically with the desired file name based on the partition column. You can achieve this by constructing the UNLOAD command using string concatenation along with the partition column value.

Use scripting or programming language (such as Python, Bash, etc.) to execute the dynamically generated UNLOAD command. Within the script, you can use the appropriate logic to connect to Redshift and execute the dynamically generated UNLOAD command.

When executing the UNLOAD command, ensure that you're using the appropriate syntax for dynamic file naming. You may use variables or concatenation to dynamically generate the file name based on the partition column value.

Here's an example of how this might look in SQL and Python:

SQL: sql -- Generate the UNLOAD command dynamically SELECT 'UNLOAD (\'SELECT × FROM your_table WHERE partition_column = \'' || partition_column || '\' \') TO \'s3://your_bucket/' || partition_column || '_data.csv\' CREDENTIALS \'aws_access_key_id=your_access_key;aws_secret_access_key=your_secret_key\' DELIMITER \',\';' FROM (SELECT DISTINCT partition_column FROM your_table) AS partitions;

Python: ```python import psycopg2

Establish connection to Redshift
conn = psycopg2.connect( dbname='your_db', host='your_host', port='your_port', user='your_user', password='your_password' ) cur = conn.cursor()

Execute the dynamically generated UNLOAD command
cur.execute() ```

How to add dynamic filename to the file which is getting unloaded from redshift unload query with a partition by column

Relevant content