Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Unloading data in Amazon Redshift
To unload data from database tables to a set of files in an Amazon S3 bucket, you can use the [UNLOAD](r_UNLOAD.md) command with a SELECT statement. You can unload text data in either delimited format or fixed-width format, regardless of the data format that was used to load it. You can also specify whether to create compressed GZIP files.
You can limit the access users have to your Amazon S3 bucket by using temporary security credentials.
**Topics**
+ [Unloading data to Amazon S3](t_Unloading_tables.md)
+ [Unloading encrypted data files](t_unloading_encrypted_files.md)
+ [Unloading data in delimited or fixed-width format](t_unloading_fixed_width_data.md)
+ [Reloading unloaded data](t_Reloading_unload_files.md)
# Unloading data to Amazon S3
Amazon Redshift splits the results of a select statement across a set of files, one or more files per node slice, to simplify parallel reloading of the data. Alternatively, you can specify that [UNLOAD](r_UNLOAD.md) should write the results serially to one or more files by adding the PARALLEL OFF option. You can limit the size of the files in Amazon S3 by specifying the MAXFILESIZE parameter. UNLOAD automatically encrypts data files using Amazon S3 server-side encryption (SSE-S3).
You can use any select statement in the UNLOAD command that Amazon Redshift supports, except for a select that uses a LIMIT clause in the outer select. For example, you can use a select statement that includes specific columns or that uses a where clause to join multiple tables. If your query contains quotation marks (enclosing literal values, for example), you need to escape them in the query text (\$1'). For more information, see the [SELECT](r_SELECT_synopsis.md) command reference. For more information about using a LIMIT clause, see the [Usage notes](r_UNLOAD.md#unload-usage-notes) for the UNLOAD command.
For example, the following UNLOAD command sends the contents of the VENUE table to the Amazon S3 bucket `s3://amzn-s3-demo-bucket/tickit/unload/`.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/unload/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
```
The file names created by the previous example include the prefix '`venue_`'.
```
venue_0000_part_00
venue_0001_part_00
venue_0002_part_00
venue_0003_part_00
```
By default, UNLOAD writes data in parallel to multiple files, according to the number of slices in the cluster. To write data to a single file, specify PARALLEL OFF. UNLOAD writes the data serially, sorted absolutely according to the ORDER BY clause, if one is used. The maximum size for a data file is 6.2 GB. If the data size is greater than the maximum, UNLOAD creates additional files, up to 6.2 GB each.
The following example writes the contents VENUE to a single file. Only one file is required because the file size is less than 6.2 GB.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/unload/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
parallel off;
```
**Note**
The UNLOAD command is designed to use parallel processing. We recommend leaving PARALLEL enabled for most cases, especially if the files will be used to load tables using a COPY command.
Assuming the total data size for VENUE is 5 GB, the following example writes the contents of VENUE to 50 files, each 100 MB in size.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/unload/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
parallel off
maxfilesize 100 mb;
```
If you include a prefix in the Amazon S3 path string, UNLOAD will use that prefix for the file names.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/unload/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
```
You can create a manifest file that lists the unload files by specifying the MANIFEST option in the UNLOAD command. The manifest is a text file in JSON format that explicitly lists the URL of each file that was written to Amazon S3.
The following example includes the manifest option.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
manifest;
```
The following example shows a manifest for four unload files.
```
{
"entries": [
{"url":"s3://amzn-s3-demo-bucket/tickit/venue_0000_part_00"},
{"url":"s3://amzn-s3-demo-bucket/tickit/venue_0001_part_00"},
{"url":"s3://amzn-s3-demo-bucket/tickit/venue_0002_part_00"},
{"url":"s3://amzn-s3-demo-bucket/tickit/venue_0003_part_00"}
]
}
```
The manifest file can be used to load the same files by using a COPY with the MANIFEST option. For more information, see [Using a manifest to specify data files](loading-data-files-using-manifest.md).
After you complete an UNLOAD operation, confirm that the data was unloaded correctly by navigating to the Amazon S3 bucket where UNLOAD wrote the files. You will see one or more numbered files per slice, starting with the number zero. If you specified the MANIFEST option, you will also see a file ending with '`manifest`'. For example:
```
amzn-s3-demo-bucket/tickit/venue_0000_part_00
amzn-s3-demo-bucket/tickit/venue_0001_part_00
amzn-s3-demo-bucket/tickit/venue_0002_part_00
amzn-s3-demo-bucket/tickit/venue_0003_part_00
amzn-s3-demo-bucket/tickit/venue_manifest
```
You can programmatically get a list of the files that were written to Amazon S3 by calling an Amazon S3 list operation after the UNLOAD completes. You can also query STL\$1UNLOAD\$1LOG.
The following query returns the pathname for files that were created by an UNLOAD. The [PG\$1LAST\$1QUERY\$1ID](PG_LAST_QUERY_ID.md) function returns the most recent query.
```
select query, substring(path,0,40) as path
from stl_unload_log
where query=2320
order by path;
query | path
-------+--------------------------------------
2320 | s3://amzn-s3-demo-bucket/venue0000_part_00
2320 | s3://amzn-s3-demo-bucket/venue0001_part_00
2320 | s3://amzn-s3-demo-bucket/venue0002_part_00
2320 | s3://amzn-s3-demo-bucket/venue0003_part_00
(4 rows)
```
If the amount of data is very large, Amazon Redshift might split the files into multiple parts per slice. For example:
```
venue_0000_part_00
venue_0000_part_01
venue_0000_part_02
venue_0001_part_00
venue_0001_part_01
venue_0001_part_02
...
```
The following UNLOAD command includes a quoted string in the select statement, so the quotation marks are escaped (`=\'OH\' '`).
```
unload ('select venuename, venuecity from venue where venuestate=\'OH\' ')
to 's3://amzn-s3-demo-bucket/tickit/venue/ '
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
```
By default, UNLOAD will fail rather than overwrite existing files in the destination bucket. To overwrite the existing files, including the manifest file, specify the ALLOWOVERWRITE option.
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/venue_pipe_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
manifest
allowoverwrite;
```
# Unloading encrypted data files
UNLOAD automatically creates files using Amazon S3 server-side encryption with AWS managed encryption keys (SSE-S3). You can also specify server-side encryption with an AWS Key Management Service key (SSE-KMS) or client-side encryption with a customer managed key. UNLOAD doesn't support Amazon S3 server-side encryption using a customer-supplied key. For more information, see [ Protecting data using server-side encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html).
To unload to Amazon S3 using server-side encryption with an AWS KMS key, use the KMS\$1KEY\$1ID parameter to provide the key ID as shown in the following example.
```
unload ('select venuename, venuecity from venue')
to 's3://amzn-s3-demo-bucket/encrypted/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
KMS_KEY_ID '1234abcd-12ab-34cd-56ef-1234567890ab'
encrypted;
```
If you want to provide your own encryption key, you can create client-side encrypted data files in Amazon S3 by using the UNLOAD command with the ENCRYPTED option. UNLOAD uses the same envelope encryption process that Amazon S3 client-side encryption uses. You can then use the COPY command with the ENCRYPTED option to load the encrypted files.
The process works like this:
1. You create a base64 encoded 256-bit AES key that you will use as your private encryption key, or *root symmetric key*.
1. You issue an UNLOAD command that includes your root symmetric key and the ENCRYPTED option.
1. UNLOAD generates a one-time-use symmetric key (called the *envelope symmetric key*) and an initialization vector (IV), which it uses to encrypt your data.
1. UNLOAD encrypts the envelope symmetric key using your root symmetric key.
1. UNLOAD then stores the encrypted data files in Amazon S3 and stores the encrypted envelope key and IV as object metadata with each file. The encrypted envelope key is stored as object metadata `x-amz-meta-x-amz-key` and the IV is stored as object metadata `x-amz-meta-x-amz-iv`.
For more information about the envelope encryption process, see the [Client-side data encryption with the AWS SDK for Java and Amazon S3](https://aws.amazon.com/articles/2850096021478074) article.
To unload encrypted data files, add the root key value to the credentials string and include the ENCRYPTED option. If you use the MANIFEST option, the manifest file is also encrypted.
```
unload ('select venuename, venuecity from venue')
to 's3://amzn-s3-demo-bucket/encrypted/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
master_symmetric_key ''
manifest
encrypted;
```
To unload encrypted data files that are GZIP compressed, include the GZIP option along with the root key value and the ENCRYPTED option.
```
unload ('select venuename, venuecity from venue')
to 's3://amzn-s3-demo-bucket/encrypted/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
master_symmetric_key ''
encrypted gzip;
```
To load the encrypted data files, add the MASTER\$1SYMMETRIC\$1KEY parameter with the same root key value and include the ENCRYPTED option.
```
copy venue from 's3://amzn-s3-demo-bucket/encrypted/venue_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
master_symmetric_key ''
encrypted;
```
# Unloading data in delimited or fixed-width format
You can unload data in delimited format or fixed-width format. The default output is pipe-delimited (using the '\$1' character).
The following example specifies a comma as the delimiter:
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/venue/comma'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter ',';
```
The resulting output files look like this:
```
20,Air Canada Centre,Toronto,ON,0
60,Rexall Place,Edmonton,AB,0
100,U.S. Cellular Field,Chicago,IL,40615
200,Al Hirschfeld Theatre,New York City,NY,0
240,San Jose Repertory Theatre,San Jose,CA,0
300,Kennedy Center Opera House,Washington,DC,0
...
```
To unload the same result set to a tab-delimited file, issue the following command:
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/venue/tab'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter as '\t';
```
Alternatively, you can use a FIXEDWIDTH specification. This specification consists of an identifier for each table column and the width of the column (number of characters). The UNLOAD command will fail rather than truncate data, so specify a width that is at least as long as the longest entry for that column. Unloading fixed-width data works similarly to unloading delimited data, except that the resulting output contains no delimiting characters. For example:
```
unload ('select * from venue')
to 's3://amzn-s3-demo-bucket/tickit/venue/fw'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
fixedwidth '0:3,1:100,2:30,3:2,4:6';
```
The fixed-width output looks like this:
```
20 Air Canada Centre Toronto ON0
60 Rexall Place Edmonton AB0
100U.S. Cellular Field Chicago IL40615
200Al Hirschfeld Theatre New York CityNY0
240San Jose Repertory TheatreSan Jose CA0
300Kennedy Center Opera HouseWashington DC0
```
For more details about FIXEDWIDTH specifications, see the [UNLOAD](r_UNLOAD.md) command.
# Reloading unloaded data
To reload the results of an unload operation, you can use a COPY command.
The following example shows a simple case in which the VENUE table is unloaded using a manifest file, truncated, and reloaded.
```
unload ('select * from venue order by venueid')
to 's3://amzn-s3-demo-bucket/tickit/venue/reload_'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
manifest
delimiter '|';
truncate venue;
copy venue
from 's3://amzn-s3-demo-bucket/tickit/venue/reload_manifest'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
manifest
delimiter '|';
```
After it is reloaded, the VENUE table looks like this:
```
select * from venue order by venueid limit 5;
venueid | venuename | venuecity | venuestate | venueseats
---------+---------------------------+-------------+------------+-----------
1 | Toyota Park | Bridgeview | IL | 0
2 | Columbus Crew Stadium | Columbus | OH | 0
3 | RFK Stadium | Washington | DC | 0
4 | CommunityAmerica Ballpark | Kansas City | KS | 0
5 | Gillette Stadium | Foxborough | MA | 68756
(5 rows)
```