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/). # UNLOAD | | | --- | | Client-side encryption for COPY and UNLOAD commands will no longer be open to new customers starting April 30, 2025. If you used client-side encryption with COPY and UNLOAD commands in the 12 months before April 30, 2025, you can continue to use client side encryption with COPY or UNLOAD commands until April 30, 2026. After April 30, 2026, you won't be able to use client-side encryption for COPY and UNLOAD. We recommend that you switch to using server-side encryption for COPY and UNLOAD as soon as possible. If you're already using server-side encryption for COPY and UNLOAD, there's no change and you can continue to use it without altering your queries. For more information on encryption for COPY and UNLOAD, see the ENCRYPTED parameter below. | Unloads the result of a query to one or more text, JSON, or Apache Parquet files on Amazon S3, using Amazon S3 server-side encryption (SSE-S3). You can also specify server-side encryption with an AWS Key Management Service key (SSE-KMS). By default, the format of the unloaded file is pipe-delimited ( `|` ) text. You can manage the size of files on Amazon S3, and by extension the number of files, by setting the MAXFILESIZE parameter. Ensure that the S3 IP ranges are added to your allow list. To learn more about the required S3 IP ranges, see [ Network isolation](https://docs.aws.amazon.com//redshift/latest/mgmt/security-network-isolation.html#network-isolation). You can unload the result of an Amazon Redshift query to your Amazon S3 data lake in Apache Parquet, an efficient open columnar storage format for analytics. Parquet format is up to 2x faster to unload and consumes up to 6x less storage in Amazon S3, compared with text formats. This enables you to save data transformation and enrichment you have done in Amazon S3 into your Amazon S3 data lake in an open format. You can then analyze your data with Redshift Spectrum and other AWS services such as Amazon Athena, Amazon EMR, and Amazon SageMaker AI. For more information and example scenarios about using the UNLOAD command, see [Unloading data in Amazon Redshift](c_unloading_data.md). ## Required privileges and permissions For the UNLOAD command to succeed, at least SELECT privilege on the data in the database is needed, along with permission to write to the Amazon S3 location. For information about permissions to access AWS resources for the UNLOAD command, see [Permissions to access other AWS Resources](copy-usage_notes-access-permissions.md). To apply least-privilege permissions, follow these recommendations to only grant permissions, as needed, to the user running the command. + The user must have SELECT privilege on the data. For information about how to limit database privileges, see [GRANT](r_GRANT.md). + The user needs permission to assume the IAM role to write to the Amazon S3 bucket in your AWS account. To restrict access for a database user to assume a role, see [Restricting access to IAM roles](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service-database-users.html) in the *Amazon Redshift Management Guide*. + The user needs access to the Amazon S3 bucket. To restrict permission using an Amazon S3 bucket policy, see [Bucket policies for Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucket-policies.html) in the *Amazon Simple Storage Service User Guide*. ## Syntax ``` UNLOAD ('select-statement') TO 's3://object-path/name-prefix' authorization [ option, ...] where authorization is IAM_ROLE { default | 'arn:aws:iam:::role/[,arn:aws:iam:::role/][,...]' } where option is | [ FORMAT [ AS ] ] CSV | PARQUET | JSON | PARTITION BY ( column_name [, ... ] ) [ INCLUDE ] | MANIFEST [ VERBOSE ] | HEADER | DELIMITER [ AS ] 'delimiter-char' | FIXEDWIDTH [ AS ] 'fixedwidth-spec' | ENCRYPTED [ AUTO ] | BZIP2 | GZIP | ZSTD | ADDQUOTES | NULL [ AS ] 'null-string' | ESCAPE | ALLOWOVERWRITE | CLEANPATH | PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] | MAXFILESIZE [AS] max-size [ MB | GB ] | ROWGROUPSIZE [AS] size [ MB | GB ] | REGION [AS] 'aws-region' } | EXTENSION 'extension-name' ``` ## Parameters ('*select-statement*') A SELECT query. The results of the query are unloaded. In most cases, it is worthwhile to unload data in sorted order by specifying an ORDER BY clause in the query. This approach saves the time required to sort the data when it is reloaded. The query must be enclosed in single quotation marks as shown following: ``` ('select * from venue order by venueid') ``` If your query contains quotation marks (for example to enclose literal values), put the literal between two sets of single quotation marks—you must also enclose the query between single quotation marks: ``` ('select * from venue where venuestate=''NV''') ``` TO 's3://*object-path/name-prefix*' The full path, including bucket name, to the location on Amazon S3 where Amazon Redshift writes the output file objects, including the manifest file if MANIFEST is specified. The object names are prefixed with *name-prefix*. If you use `PARTITION BY`, a forward slash (/) is automatically added to the end of the *name-prefix* value if needed. For added security, UNLOAD connects to Amazon S3 using an HTTPS connection. By default, UNLOAD writes one or more files per slice. UNLOAD appends a slice number and part number to the specified name prefix as follows: `/_part_`. If MANIFEST is specified, the manifest file is written as follows: `/manifest`. If PARALLEL is specified OFF, the data files are written as follows: `/`. UNLOAD automatically creates encrypted files using Amazon S3 server-side encryption (SSE), including the manifest file if MANIFEST is used. The COPY command automatically reads server-side encrypted files during the load operation. You can transparently download server-side encrypted files from your bucket using either the Amazon S3 console or API. For more information, see [Protecting Data Using Server-Side Encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html). REGION is required when the Amazon S3 bucket isn't in the same AWS Region as the Amazon Redshift database. *authorization* The UNLOAD command needs authorization to write data to Amazon S3. The UNLOAD command uses the same parameters the COPY command uses for authorization. For more information, see [Authorization parameters](copy-parameters-authorization.md) in the COPY command syntax reference. IAM\$1ROLE \$1 default \$1 'arn:aws:iam::**:role/**' Use the default keyword to have Amazon Redshift use the IAM role that is set as default and associated with the cluster when the UNLOAD command runs. Use the Amazon Resource Name (ARN) for an IAM role that your cluster uses for authentication and authorization. If you specify IAM\$1ROLE, you can't use ACCESS\$1KEY\$1ID and SECRET\$1ACCESS\$1KEY, SESSION\$1TOKEN, or CREDENTIALS. The IAM\$1ROLE can be chained. For more information, see [Chaining IAM roles](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html#authorizing-redshift-service-chaining-roles) in the *Amazon Redshift Management Guide*. [ FORMAT [AS] ] CSV \$1 PARQUET \$1 JSON Keywords to specify the unload format to override the default format. When CSV, unloads to a text file in CSV format using a comma ( , ) character as the default delimiter. If a field contains delimiters, double quotation marks, newline characters, or carriage returns, then the field in the unloaded file is enclosed in double quotation marks. A double quotation mark within a data field is escaped by an additional double quotation mark. When zero rows are unloaded, Amazon Redshift might write empty Amazon S3 objects. When PARQUET, unloads to a file in Apache Parquet version 1.0 format. By default, each row group is compressed using SNAPPY compression. For more information about Apache Parquet format, see [Parquet](https://parquet.apache.org/). When JSON, unloads to a JSON file with each line containing a JSON object, representing a full record in the query result. Amazon Redshift supports writing nested JSON when the query result contains SUPER columns. To create a valid JSON object, the name of each column in the query must be unique. In the JSON file, boolean values are unloaded as `t` or `f`, and NULL values are unloaded as `null`. When zero rows are unloaded, Amazon Redshift does not write Amazon S3 objects. The FORMAT and AS keywords are optional. You can't use CSV with ESCAPE, FIXEDWIDTH, or ADDQUOTES. You can't use PARQUET with DELIMITER, FIXEDWIDTH, ADDQUOTES, ESCAPE, NULL AS, HEADER, GZIP, BZIP2, or ZSTD. PARQUET with ENCRYPTED is only supported with server-side encryption with an AWS Key Management Service key (SSE-KMS). You can't use JSON with DELIMITER, HEADER, FIXEDWIDTH, ADDQUOTES, ESCAPE, or NULL AS. PARTITION BY ( *column\$1name* [, ... ] ) [INCLUDE] Specifies the partition keys for the unload operation. UNLOAD automatically partitions output files into partition folders based on the partition key values, following the Apache Hive convention. For example, a Parquet file that belongs to the partition year 2019 and the month September has the following prefix: `s3://amzn-s3-demo-bucket/my_prefix/year=2019/month=September/000.parquet`. The value for *column\$1name* must be a column in the query results being unloaded. If you specify PARTITION BY with the INCLUDE option, partition columns aren't removed from the unloaded files. Amazon Redshift doesn't support string literals in PARTITION BY clauses. MANIFEST [ VERBOSE ] Creates a manifest file that explicitly lists details for the data files that are created by the UNLOAD process. The manifest is a text file in JSON format that lists the URL of each file that was written to Amazon S3. If MANIFEST is specified with the VERBOSE option, the manifest includes the following details: + The column names and data types, and for CHAR, VARCHAR, or NUMERIC data types, dimensions for each column. For CHAR and VARCHAR data types, the dimension is the length. For a DECIMAL or NUMERIC data type, the dimensions are precision and scale. + The row count unloaded to each file. If the HEADER option is specified, the row count includes the header line. + The total file size of all files unloaded and the total row count unloaded to all files. If the HEADER option is specified, the row count includes the header lines. + The author. Author is always "Amazon Redshift". You can specify VERBOSE only following MANIFEST. The manifest file is written to the same Amazon S3 path prefix as the unload files in the format `manifest`. For example, if UNLOAD specifies the Amazon S3 path prefix '`s3://amzn-s3-demo-bucket/venue_`', the manifest file location is '`s3://amzn-s3-demo-bucket/venue_manifest`'. HEADER Adds a header line containing column names at the top of each output file. Text transformation options, such as CSV, DELIMITER, ADDQUOTES, and ESCAPE, also apply to the header line. You can't use HEADER with FIXEDWIDTH. DELIMITER AS '*delimiter\$1character*' Specifies a single ASCII character that is used to separate fields in the output file, such as a pipe character ( \$1 ), a comma ( , ), or a tab ( \$1t ). The default delimiter for text files is a pipe character. The default delimiter for CSV files is a comma character. The AS keyword is optional. You can't use DELIMITER with FIXEDWIDTH. If the data contains the delimiter character, you need to specify the ESCAPE option to escape the delimiter, or use ADDQUOTES to enclose the data in double quotation marks. Alternatively, specify a delimiter that isn't contained in the data. FIXEDWIDTH '*fixedwidth\$1spec*' Unloads the data to a file where each column width is a fixed length, rather than separated by a delimiter. The *fixedwidth\$1spec* is a string that specifies the number of columns and the width of the columns. The AS keyword is optional. Because FIXEDWIDTH doesn't truncate data, the specification for each column in the UNLOAD statement needs to be at least as long as the length of the longest entry for that column. The format for *fixedwidth\$1spec* is shown below: ``` 'colID1:colWidth1,colID2:colWidth2, ...' ``` You can't use FIXEDWIDTH with DELIMITER or HEADER. ENCRYPTED [AUTO] Specifies that the output files on Amazon S3 are encrypted using Amazon S3 server-side encryption. If MANIFEST is specified, the manifest file is also encrypted. For more information, see [Unloading encrypted data files](t_unloading_encrypted_files.md). If you don't specify the ENCRYPTED parameter, UNLOAD automatically creates encrypted files using Amazon S3 server-side encryption with AWS-managed encryption keys (SSE-S3). For ENCRYPTED, you might want to unload to Amazon S3 using server-side encryption with an AWS KMS key (SSE-KMS). If so, use the [KMS_KEY_ID](#unload-parameters-kms-key-id) parameter to provide the key ID. You can't use the [Using the CREDENTIALS parameter](copy-parameters-authorization.md#copy-credentials) parameter with the KMS\$1KEY\$1ID parameter. If you run an UNLOAD command for data using KMS\$1KEY\$1ID, you can then do a COPY operation for the same data without specifying a key. If ENCRYPTED AUTO is used, the UNLOAD command fetches the default AWS KMS encryption key on the target Amazon S3 bucket property and encrypts the files written to Amazon S3 with the AWS KMS key. If the bucket doesn't have the default AWS KMS encryption key, UNLOAD automatically creates encrypted files using Amazon Redshift server-side encryption with AWS-managed encryption keys (SSE-S3). You can't use this option with KMS\$1KEY\$1ID, MASTER\$1SYMMETRIC\$1KEY, or CREDENTIALS that contains master\$1symmetric\$1key. KMS\$1KEY\$1ID '*key-id*' Specifies the key ID for an AWS Key Management Service (AWS KMS) key to be used to encrypt data files on Amazon S3. For more information, see [What is AWS Key Management Service?](https://docs.aws.amazon.com/kms/latest/developerguide/overview.html) If you specify KMS\$1KEY\$1ID, you must specify the [ENCRYPTED](#unload-parameters-encrypted) parameter also. If you specify KMS\$1KEY\$1ID, you can't authenticate using the CREDENTIALS parameter. Instead, use either [Using the IAM\$1ROLE parameter](copy-parameters-authorization.md#copy-iam-role) or [Using the ACCESS\$1KEY\$1ID and SECRET\$1ACCESS\$1KEY parameters](copy-parameters-authorization.md#copy-access-key-id). BZIP2 Unloads data to one or more bzip2-compressed files per slice. Each resulting file is appended with a `.bz2` extension. GZIP Unloads data to one or more gzip-compressed files per slice. Each resulting file is appended with a `.gz` extension. ZSTD Unloads data to one or more Zstandard-compressed files per slice. Each resulting file is appended with a `.zst` extension. ADDQUOTES Places quotation marks around each unloaded data field, so that Amazon Redshift can unload data values that contain the delimiter itself. For example, if the delimiter is a comma, you could unload and reload the following data successfully: ``` "1","Hello, World" ``` Without the added quotation marks, the string `Hello, World` would be parsed as two separate fields. Some output formats do not support ADDQUOTES. If you use ADDQUOTES, you must specify REMOVEQUOTES in the COPY if you reload the data. NULL AS '*null-string*' Specifies a string that represents a null value in unload files. If this option is used, all output files contain the specified string in place of any null values found in the selected data. If this option isn't specified, null values are unloaded as: + Zero-length strings for delimited output + Whitespace strings for fixed-width output If a null string is specified for a fixed-width unload and the width of an output column is less than the width of the null string, the following behavior occurs: + An empty field is output for non-character columns + An error is reported for character columns Unlike other data types where a user-defined string represents a null value, Amazon Redshift exports the SUPER data columns using the JSON format and represents it as null as determined by the JSON format. As a result, SUPER data columns ignore the NULL [AS] option used in UNLOAD commands. ESCAPE For CHAR and VARCHAR columns in delimited unload files, an escape character (`\`) is placed before every occurrence of the following characters: + Linefeed: `\n` + Carriage return: `\r` + The delimiter character specified for the unloaded data. + The escape character: `\` + A quotation mark character: `"` or `'` (if both ESCAPE and ADDQUOTES are specified in the UNLOAD command). If you loaded your data using a COPY with the ESCAPE option, you must also specify the ESCAPE option with your UNLOAD command to generate the reciprocal output file. Similarly, if you UNLOAD using the ESCAPE option, you need to use ESCAPE when you COPY the same data. ALLOWOVERWRITE By default, UNLOAD fails if it finds files that it would possibly overwrite. If ALLOWOVERWRITE is specified, UNLOAD overwrites existing files, including the manifest file. CLEANPATH The CLEANPATH option removes existing files located in the Amazon S3 path specified in the TO clause before unloading files to the specified location. If you include the PARTITION BY clause, existing files are removed only from the partition folders to receive new files generated by the UNLOAD operation. You must have the `s3:DeleteObject` permission on the Amazon S3 bucket. For information, see [Policies and Permissions in Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/access-policy-language-overview.html) in the *Amazon Simple Storage Service User Guide*. Files that you remove by using the CLEANPATH option are permanently deleted and can't be recovered. If the target Amazon S3 bucket has versioning enabled, UNLOAD with the CLEANPATH option does not remove previous versions of the files. You can't specify the CLEANPATH option if you specify the ALLOWOVERWRITE option. PARALLEL By default, UNLOAD writes data in parallel to multiple files, according to the number of slices in the cluster. The default option is ON or TRUE. If PARALLEL is OFF or FALSE, UNLOAD writes to one or more data files serially, sorted absolutely according to the ORDER BY clause, if one is used. The maximum size for a data file is 6.2 GB. So, for example, if you unload 13.4 GB of data, UNLOAD creates the following three files. ``` s3://amzn-s3-demo-bucket/key000 6.2 GB s3://amzn-s3-demo-bucket/key001 6.2 GB s3://amzn-s3-demo-bucket/key002 1.0 GB ``` The UNLOAD command is designed to use parallel processing. We recommend leaving PARALLEL enabled for most cases, especially if the files are used to load tables using a COPY command. MAXFILESIZE [AS] max-size [ MB \$1 GB ] Specifies the maximum size of files that UNLOAD creates in Amazon S3. Specify a decimal value between 5 MB and 6.2 GB. The AS keyword is optional. The default unit is MB. If MAXFILESIZE isn't specified, the default maximum file size is 6.2 GB. The size of the manifest file, if one is used, isn't affected by MAXFILESIZE. ROWGROUPSIZE [AS] size [ MB \$1 GB ] Specifies the size of row groups. Choosing a larger size can reduce the number of row groups, reducing the amount of network communication. Specify an integer value between 32 MB and 128 MB. The AS keyword is optional. The default unit is MB. If ROWGROUPSIZE isn't specified, the default size is 32 MB. To use this parameter, the storage format must be Parquet and the node type must be ra3.4xlarge, ra3.16xlarge, or dc2.8xlarge. REGION [AS] '*aws-region*' Specifies the AWS Region where the target Amazon S3 bucket is located. REGION is required for UNLOAD to an Amazon S3 bucket that isn't in the same AWS Region as the Amazon Redshift database. The value for *aws\$1region* must match an AWS Region listed in the [Amazon Redshift regions and endpoints](https://docs.aws.amazon.com/general/latest/gr/rande.html#redshift_region) table in the *AWS General Reference*. By default, UNLOAD assumes that the target Amazon S3 bucket is located in the same AWS Region as the Amazon Redshift database. EXTENSION '*extension-name*' Specifies the file extension to append to the names of the unloaded files. Amazon Redshift doesn't run any validation, so you must verify that the specified file extension is correct. If you specify a compression method without providing an extension, Amazon Redshift only adds the compression method's extension to the filename. If you don't provide any extension and don't specify a compression method, Amazon Redshift doesn't add anything to the filename. ## Usage notes ### Using ESCAPE for all delimited text UNLOAD operations When you UNLOAD using a delimiter, your data can include that delimiter or any of the characters listed in the ESCAPE option description. In this case, you must use the ESCAPE option with the UNLOAD statement. If you don't use the ESCAPE option with the UNLOAD, subsequent COPY operations using the unloaded data might fail. **Important** We strongly recommend that you always use ESCAPE with both UNLOAD and COPY statements. The exception is if you are certain that your data doesn't contain any delimiters or other characters that might need to be escaped. ### Loss of floating-point precision You might encounter loss of precision for floating-point data that is successively unloaded and reloaded. ### Limit clause The SELECT query can't use a LIMIT clause in the outer SELECT. For example, the following UNLOAD statement fails. ``` unload ('select * from venue limit 10') to 's3://amzn-s3-demo-bucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` Instead, use a nested LIMIT clause, as in the following example. ``` unload ('select * from venue where venueid in (select venueid from venue order by venueid desc limit 10)') to 's3://amzn-s3-demo-bucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` You can also populate a table using SELECT…INTO or CREATE TABLE AS using a LIMIT clause, then unload from that table. ### Unloading a column of the GEOMETRY data type You can only unload GEOMETRY columns to text or CSV format. You can't unload GEOMETRY data with the `FIXEDWIDTH` option. The data is unloaded in the hexadecimal form of the extended well-known binary (EWKB) format. If the size of the EWKB data is more than 4 MB, then a warning occurs because the data can't later be loaded into a table. ### Unloading the HLLSKETCH data type You can only unload HLLSKETCH columns to text or CSV format. You can't unload HLLSKETCH data with the `FIXEDWIDTH` option. The data is unloaded in the Base64 format for dense HyperLogLog sketches or in the JSON format for sparse HyperLogLog sketches. For more information, see [HyperLogLog functions](hyperloglog-functions.md). The following example exports a table containing HLLSKETCH columns into a file. ``` CREATE TABLE a_table(an_int INT, b_int INT); INSERT INTO a_table VALUES (1,1), (2,1), (3,1), (4,1), (1,2), (2,2), (3,2), (4,2), (5,2), (6,2); CREATE TABLE hll_table (sketch HLLSKETCH); INSERT INTO hll_table select hll_create_sketch(an_int) from a_table group by b_int; UNLOAD ('select * from hll_table') TO 's3://amzn-s3-demo-bucket/unload/' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole' NULL AS 'null' ALLOWOVERWRITE CSV; ``` ### Unloading a column of the VARBYTE data type You can only unload VARBYTE columns to text or CSV format. The data is unloaded in the hexadecimal form. You can't unload VARBYTE data with the `FIXEDWIDTH` option. The `ADDQUOTES` option of UNLOAD to a CSV is not supported. A VARBYTE column can't be a PARTITIONED BY column. ### FORMAT AS PARQUET clause Be aware of these considerations when using FORMAT AS PARQUET: + Unload to Parquet doesn't use file level compression. Each row group is compressed with SNAPPY. + If MAXFILESIZE isn't specified, the default maximum file size is 6.2 GB. You can use MAXFILESIZE to specify a file size of 5 MB–6.2 GB. The actual file size is approximated when the file is being written, so it might not be exactly equal to the number you specify. To maximize scan performance, Amazon Redshift tries to create Parquet files that contain equally sized 32-MB row groups. The MAXFILESIZE value that you specify is automatically rounded down to the nearest multiple of 32 MB. For example, if you specify MAXFILESIZE 200 MB, then each Parquet file unloaded is approximately 192 MB (32 MB row group x 6 = 192 MB). + If a column uses TIMESTAMPTZ data format, only the timestamp values are unloaded. The time zone information isn't unloaded. + Don't specify file name prefixes that begin with underscore (\$1) or period (.) characters. Redshift Spectrum treats files that begin with these characters as hidden files and ignores them. ### PARTITION BY clause Be aware of these considerations when using PARTITION BY: + Partition columns aren't included in the output file. + Make sure to include partition columns in the SELECT query used in the UNLOAD statement. You can specify any number of partition columns in the UNLOAD command. However, there is a limitation that there should be at least one nonpartition column to be part of the file. + If the partition key value is null, Amazon Redshift automatically unloads that data into a default partition called `partition_column=__HIVE_DEFAULT_PARTITION__`. + The UNLOAD command doesn't make any calls to an external catalog. To register your new partitions to be part of your existing external table, use a separate ALTER TABLE ... ADD PARTITION ... command. Or you can run a CREATE EXTERNAL TABLE command to register the unloaded data as a new external table. You can also use an AWS Glue crawler to populate your Data Catalog. For more information, see [Defining Crawlers](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html) in the *AWS Glue Developer Guide*. + If you use the MANIFEST option, Amazon Redshift generates only one manifest file in the root Amazon S3 folder. + The column data types that you can use as the partition key are SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, BOOLEAN, CHAR, VARCHAR, DATE, and TIMESTAMP. ### Using the ASSUMEROLE privilege to grant access to an IAM role for UNLOAD operations To provide access for specific users and groups to an IAM role for UNLOAD operations, a superuser can grant the ASSUMEROLE privilege on an IAM role to users and groups. For information, see [GRANT](r_GRANT.md). ### UNLOAD doesn't support Amazon S3 access point aliases You can't use Amazon S3 access point aliases with the UNLOAD command. ## Examples For examples that show how to use the UNLOAD command, see [UNLOAD examples](r_UNLOAD_command_examples.md). # UNLOAD examples These examples demonstrate various parameters of the UNLOAD command. The TICKIT sample data is used in many of the examples. For more information, see [Sample database](c_sampledb.md). **Note** These examples contain line breaks for readability. Do not include line breaks or spaces in your *credentials-args* string. ## Unload VENUE to a pipe-delimited file (default delimiter) The following example unloads the VENUE table and writes the data to `s3://amzn-s3-demo-bucket/unload/`: ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` By default, UNLOAD writes one or more files per slice. Assuming a two-node cluster with two slices per node, the previous example creates these files in `amzn-s3-demo-bucket`: ``` unload/0000_part_00 unload/0001_part_00 unload/0002_part_00 unload/0003_part_00 ``` To better differentiate the output files, you can include a prefix in the location. The following example unloads the VENUE table and writes the data to `s3://amzn-s3-demo-bucket/unload/venue_pipe_`: ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` The result is these four files in the `unload` folder, again assuming four slices. ``` venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00 ``` ## Unload LINEITEM table to partitioned Parquet files The following example unloads the LINEITEM table in Parquet format, partitioned by the `l_shipdate` column. ``` unload ('select * from lineitem') to 's3://amzn-s3-demo-bucket/lineitem/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' PARQUET PARTITION BY (l_shipdate); ``` Assuming four slices, the resulting Parquet files are dynamically partitioned into various folders. ``` s3://amzn-s3-demo-bucket/lineitem/l_shipdate=1992-01-02/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet s3://amzn-s3-demo-bucket/lineitem/l_shipdate=1992-01-03/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet s3://amzn-s3-demo-bucket/lineitem/l_shipdate=1992-01-04/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet ... ``` **Note** In some cases, the UNLOAD command used the INCLUDE option as shown in the following SQL statement. ``` unload ('select * from lineitem') to 's3://amzn-s3-demo-bucket/lineitem/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' PARQUET PARTITION BY (l_shipdate) INCLUDE; ``` In these cases, the `l_shipdate` column is also in the data in the Parquet files. Otherwise, the `l_shipdate` column data isn't in the Parquet files. ## Unload the VENUE table to a JSON file The following example unloads the VENUE table and writes the data in JSON format to `s3://amzn-s3-demo-bucket/unload/`. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' JSON; ``` Following are sample rows from the VENUE table. ``` venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+----------- 1 | Pinewood Racetrack | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0 ``` After unloading to JSON, the format of the file is similar to the following. ``` {"venueid":1,"venuename":"Pinewood Racetrack","venuecity":"Akron","venuestate":"OH","venueseats":0} {"venueid":2,"venuename":"Columbus \"Crew\" Stadium ","venuecity":"Columbus","venuestate":"OH","venueseats":0} {"venueid":4,"venuename":"Community, Ballpark, Arena","venuecity":"Kansas City","venuestate":"KS","venueseats":0} ``` ## Unload VENUE to a CSV file The following example unloads the VENUE table and writes the data in CSV format to `s3://amzn-s3-demo-bucket/unload/`. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' CSV; ``` Suppose that the VENUE table contains the following rows. ``` venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+----------- 1 | Pinewood Racetrack | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0 ``` The unload file looks similar to the following. ``` 1,Pinewood Racetrack,Akron,OH,0 2,"Columbus ""Crew"" Stadium",Columbus,OH,0 4,"Community, Ballpark, Arena",Kansas City,KS,0 ``` ## Unload VENUE to a CSV file using a delimiter The following example unloads the VENUE table and writes the data in CSV format using the pipe character (\$1) as the delimiter. The unloaded file is written to `s3://amzn-s3-demo-bucket/unload/`. The VENUE table in this example contains the pipe character in the value of the first row (`Pinewood Race|track`). It does this to show that the value in the result is enclosed in double quotation marks. A double quotation mark is escaped by a double quotation mark, and the entire field is enclosed in double quotation marks. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' CSV DELIMITER AS '|'; ``` Suppose that the VENUE table contains the following rows. ``` venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+------------- 1 | Pinewood Race|track | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0 ``` The unload file looks similar to the following. ``` 1|"Pinewood Race|track"|Akron|OH|0 2|"Columbus ""Crew"" Stadium"|Columbus|OH|0 4|Community, Ballpark, Arena|Kansas City|KS|0 ``` ## Unload VENUE with a manifest file To create a manifest file, include the MANIFEST option. The following example unloads the VENUE table and writes a manifest file along with the data files to s3://amzn-s3-demo-bucket/venue\$1pipe\$1: **Important** If you unload files with the MANIFEST option, you should use the MANIFEST option with the COPY command when you load the files. If you use the same prefix to load the files and don't specify the MANIFEST option, COPY fails because it assumes the manifest file is a data file. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest; ``` The result is these five files: ``` s3://amzn-s3-demo-bucket/venue_pipe_0000_part_00 s3://amzn-s3-demo-bucket/venue_pipe_0001_part_00 s3://amzn-s3-demo-bucket/venue_pipe_0002_part_00 s3://amzn-s3-demo-bucket/venue_pipe_0003_part_00 s3://amzn-s3-demo-bucket/venue_pipe_manifest ``` The following shows the contents of the manifest file. ``` { "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"} ] } ``` ## Unload VENUE with MANIFEST VERBOSE When you specify the MANIFEST VERBOSE option, the manifest file includes the following sections: + The `entries` section lists Amazon S3 path, file size, and row count for each file. + The `schema` section lists the column names, data types, and dimension for each column. + The `meta` section shows the total file size and row count for all files. The following example unloads the VENUE table using the MANIFEST VERBOSE option. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload_venue_folder/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest verbose; ``` The following shows the contents of the manifest file. ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket/venue_pipe_0000_part_00", "meta": { "content_length": 32295, "record_count": 10 }}, {"url":"s3://amzn-s3-demo-bucket/venue_pipe_0001_part_00", "meta": { "content_length": 32771, "record_count": 20 }}, {"url":"s3://amzn-s3-demo-bucket/venue_pipe_0002_part_00", "meta": { "content_length": 32302, "record_count": 10 }}, {"url":"s3://amzn-s3-demo-bucket/venue_pipe_0003_part_00", "meta": { "content_length": 31810, "record_count": 15 }} ], "schema": { "elements": [ {"name": "venueid", "type": { "base": "integer" }}, {"name": "venuename", "type": { "base": "character varying", 25 }}, {"name": "venuecity", "type": { "base": "character varying", 25 }}, {"name": "venuestate", "type": { "base": "character varying", 25 }}, {"name": "venueseats", "type": { "base": "character varying", 25 }} ] }, "meta": { "content_length": 129178, "record_count": 55 }, "author": { "name": "Amazon Redshift", "version": "1.0.0" } } ``` ## Unload VENUE with a header The following example unloads VENUE with a header row. ``` unload ('select * from venue where venueseats > 75000') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' header parallel off; ``` The following shows the contents of the output file with a header row. ``` venueid|venuename|venuecity|venuestate|venueseats 6|New York Giants Stadium|East Rutherford|NJ|80242 78|INVESCO Field|Denver|CO|76125 83|FedExField|Landover|MD|91704 79|Arrowhead Stadium|Kansas City|MO|79451 ``` ## Unload VENUE to smaller files By default, the maximum file size is 6.2 GB. If the unload data is larger than 6.2 GB, UNLOAD creates a new file for each 6.2 GB data segment. To create smaller files, include the MAXFILESIZE parameter. Assuming the size of the data in the previous example was 20 GB, the following UNLOAD command creates 20 files, each 1 GB in size. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' maxfilesize 1 gb; ``` ## Unload VENUE serially To unload serially, specify PARALLEL OFF. UNLOAD then writes one file at a time, up to a maximum of 6.2 GB per file. The following example unloads the VENUE table and writes the data serially to `s3://amzn-s3-demo-bucket/unload/`. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/venue_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off; ``` The result is one file named venue\$1serial\$1000. If the unload data is larger than 6.2 GB, UNLOAD creates a new file for each 6.2 GB data segment. The following example unloads the LINEORDER table and writes the data serially to `s3://amzn-s3-demo-bucket/unload/`. ``` unload ('select * from lineorder') to 's3://amzn-s3-demo-bucket/unload/lineorder_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off gzip; ``` The result is the following series of files. ``` lineorder_serial_0000.gz lineorder_serial_0001.gz lineorder_serial_0002.gz lineorder_serial_0003.gz ``` To better differentiate the output files, you can include a prefix in the location. The following example unloads the VENUE table and writes the data to `s3://amzn-s3-demo-bucket/venue_pipe_`: ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` The result is these four files in the `unload` folder, again assuming four slices. ``` venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00 ``` ## Load VENUE from unload files To load a table from a set of unload files, simply reverse the process by using a COPY command. The following example creates a new table, LOADVENUE, and loads the table from the data files created in the previous example. ``` create table loadvenue (like venue); copy loadvenue from 's3://amzn-s3-demo-bucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` If you used the MANIFEST option to create a manifest file with your unload files, you can load the data using the same manifest file. You do so with a COPY command with the MANIFEST option. The following example loads data using a manifest file. ``` copy loadvenue from 's3://amzn-s3-demo-bucket/venue_pipe_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest; ``` ## Unload VENUE to encrypted files The following example unloads the VENUE table to a set of encrypted files using an AWS KMS key. If you specify a manifest file with the ENCRYPTED option, the manifest file is also encrypted. For more information, see [Unloading encrypted data files](t_unloading_encrypted_files.md). ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_encrypt_kms' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' kms_key_id '1234abcd-12ab-34cd-56ef-1234567890ab' manifest encrypted; ``` The following example unloads the VENUE table to a set of encrypted files using a root symmetric key. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_encrypt_cmk' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' encrypted; ``` ## Load VENUE from encrypted files To load tables from a set of files that were created by using UNLOAD with the ENCRYPT option, reverse the process by using a COPY command. With that command, use the ENCRYPTED option and specify the same root symmetric key that was used for the UNLOAD command. The following example loads the LOADVENUE table from the encrypted data files created in the previous example. ``` create table loadvenue (like venue); copy loadvenue from 's3://amzn-s3-demo-bucket/venue_encrypt_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' manifest encrypted; ``` ## Unload VENUE data to a tab-delimited file ``` unload ('select venueid, venuename, venueseats from venue') to 's3://amzn-s3-demo-bucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t'; ``` The output data files look like this: ``` 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 ... ``` ## Unload VENUE to a fixed-width data file ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_fw_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth as 'venueid:3,venuename:39,venuecity:16,venuestate:2,venueseats:6'; ``` The output data files look like the following. ``` 1 Toyota Park Bridgeview IL0 2 Columbus Crew Stadium Columbus OH0 3 RFK Stadium Washington DC0 4 CommunityAmerica BallparkKansas City KS0 5 Gillette Stadium Foxborough MA68756 ... ``` ## Unload VENUE to a set of tab-delimited GZIP-compressed files ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t' gzip; ``` ## Unload VENUE to a GZIP-compressed text file ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' extension 'txt.gz' gzip; ``` ## Unload data that contains a delimiter This example uses the ADDQUOTES option to unload comma-delimited data where some of the actual data fields contain a comma. First, create a table that contains quotation marks. ``` create table location (id int, location char(64)); insert into location values (1,'Phoenix, AZ'),(2,'San Diego, CA'),(3,'Chicago, IL'); ``` Then, unload the data using the ADDQUOTES option. ``` unload ('select id, location from location') to 's3://amzn-s3-demo-bucket/location_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter ',' addquotes; ``` The unloaded data files look like this: ``` 1,"Phoenix, AZ" 2,"San Diego, CA" 3,"Chicago, IL" ... ``` ## Unload the results of a join query The following example unloads the results of a join query that contains a window function. ``` unload ('select venuecity, venuestate, caldate, pricepaid, sum(pricepaid) over(partition by venuecity, venuestate order by caldate rows between 3 preceding and 3 following) as winsum from sales join date on sales.dateid=date.dateid join event on event.eventid=sales.eventid join venue on event.venueid=venue.venueid order by 1,2') to 's3://amzn-s3-demo-bucket/tickit/winsum' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` The output files look like this: ``` Atlanta|GA|2008-01-04|363.00|1362.00 Atlanta|GA|2008-01-05|233.00|2030.00 Atlanta|GA|2008-01-06|310.00|3135.00 Atlanta|GA|2008-01-08|166.00|8338.00 Atlanta|GA|2008-01-11|268.00|7630.00 ... ``` ## Unload using NULL AS UNLOAD outputs null values as empty strings by default. The following examples show how to use NULL AS to substitute a text string for nulls. For these examples, we add some null values to the VENUE table. ``` update venue set venuestate = NULL where venuecity = 'Cleveland'; ``` Select from VENUE where VENUESTATE is null to verify that the columns contain NULL. ``` select * from venue where venuestate is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 72 | Cleveland Browns Stadium | Cleveland | | 73200 ``` Now, UNLOAD the VENUE table using the NULL AS option to replace null values with the character string '`fred`'. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred'; ``` The following sample from the unload file shows that null values were replaced with `fred`. It turns out that some values for VENUESEATS were also null and were replaced with `fred`. Even though the data type for VENUESEATS is integer, UNLOAD converts the values to text in the unload files, and then COPY converts them back to integer. If you are unloading to a fixed-width file, the NULL AS string must not be larger than the field width. ``` 248|Charles Playhouse|Boston|MA|0 251|Paris Hotel|Las Vegas|NV|fred 258|Tropicana Hotel|Las Vegas|NV|fred 300|Kennedy Center Opera House|Washington|DC|0 306|Lyric Opera House|Baltimore|MD|0 308|Metropolitan Opera|New York City|NY|0 5|Gillette Stadium|Foxborough|MA|5 22|Quicken Loans Arena|Cleveland|fred|0 101|Progressive Field|Cleveland|fred|43345 ... ``` To load a table from the unload files, use a COPY command with the same NULL AS option. **Note** If you attempt to load nulls into a column defined as NOT NULL, the COPY command fails. ``` create table loadvenuenulls (like venue); copy loadvenuenulls from 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred'; ``` To verify that the columns contain null, not just empty strings, select from LOADVENUENULLS and filter for null. ``` select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 72 | Cleveland Browns Stadium | Cleveland | | 73200 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 251 | Paris Hotel | Las Vegas | NV | ... ``` You can UNLOAD a table that contains nulls using the default NULL AS behavior and then COPY the data back into a table using the default NULL AS behavior; however, any non-numeric fields in the target table contain empty strings, not nulls. By default UNLOAD converts nulls to empty strings (white space or zero-length). COPY converts empty strings to NULL for numeric columns, but inserts empty strings into non-numeric columns. The following example shows how to perform an UNLOAD followed by a COPY using the default NULL AS behavior. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` In this case, when you filter for nulls, only the rows where VENUESEATS contained nulls. Where VENUESTATE contained nulls in the table (VENUE), VENUESTATE in the target table (LOADVENUENULLS) contains empty strings. ``` select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 251 | Paris Hotel | Las Vegas | NV | ... ``` To load empty strings to non-numeric columns as NULL, include the EMPTYASNULL or BLANKSASNULL options. It's OK to use both. ``` unload ('select * from venue') to 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://amzn-s3-demo-bucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' EMPTYASNULL; ``` To verify that the columns contain NULL, not just whitespace or empty strings, select from LOADVENUENULLS and filter for null. ``` select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 72 | Cleveland Browns Stadium | Cleveland | | 73200 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 251 | Paris Hotel | Las Vegas | NV | ... ``` ## Unload using ALLOWOVERWRITE parameter By default, UNLOAD doesn't overwrite existing files in the destination bucket. For example, if you run the same UNLOAD statement twice without modifying the files in the destination bucket, the second UNLOAD fails. 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; ``` ## Unload EVENT table using PARALLEL and MANIFEST parameters You can UNLOAD a table in parallel and generate a manifest file. The Amazon S3 data files are all created at the same level and names are suffixed with the pattern `0000_part_00`. The manifest file is at the same folder level as the data files and suffixed with the text `manifest`. The following SQL unloads the EVENT table and creates files with the base name `parallel` ``` unload ('select * from mytickit1.event') to 's3://amzn-s3-demo-bucket/parallel' iam_role 'arn:aws:iam::123456789012:role/MyRedshiftRole' parallel on manifest; ``` The Amazon S3 files listing is similar to the following. ``` Name Last modified Size parallel0000_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 52.1 KB parallel0001_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 53.4 KB parallel0002_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 52.1 KB parallel0003_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 51.1 KB parallel0004_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 54.6 KB parallel0005_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 53.4 KB parallel0006_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 54.1 KB parallel0007_part_00 - August 2, 2023, 14:54:39 (UTC-07:00) 55.9 KB parallelmanifest - August 2, 2023, 14:54:39 (UTC-07:00) 886.0 B ``` The `parallelmanifest` file content is similar to the following. ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket/parallel0000_part_00", "meta": { "content_length": 53316 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0001_part_00", "meta": { "content_length": 54704 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0002_part_00", "meta": { "content_length": 53326 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0003_part_00", "meta": { "content_length": 52356 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0004_part_00", "meta": { "content_length": 55933 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0005_part_00", "meta": { "content_length": 54648 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0006_part_00", "meta": { "content_length": 55436 }}, {"url":"s3://amzn-s3-demo-bucket/parallel0007_part_00", "meta": { "content_length": 57272 }} ] } ``` ## Unload EVENT table using PARALLEL OFF and MANIFEST parameters You can UNLOAD a table serially (PARALLEL OFF) and generate a manifest file. The Amazon S3 data files are all created at the same level and names are suffixed with the pattern `0000`. The manifest file is at the same folder level as the data files and suffixed with the text `manifest`. ``` unload ('select * from mytickit1.event') to 's3://amzn-s3-demo-bucket/serial' iam_role 'arn:aws:iam::123456789012:role/MyRedshiftRole' parallel off manifest; ``` The Amazon S3 files listing is similar to the following. ``` Name Last modified Size serial0000 - August 2, 2023, 15:54:39 (UTC-07:00) 426.7 KB serialmanifest - August 2, 2023, 15:54:39 (UTC-07:00) 120.0 B ``` The `serialmanifest` file content is similar to the following. ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket/serial000", "meta": { "content_length": 436991 }} ] } ``` ## Unload EVENT table using PARTITION BY and MANIFEST parameters You can UNLOAD a table by partition and generate a manifest file. A new folder is created in Amazon S3 with child partition folders, and the data files in the child folders with a name pattern similar to `0000_par_00`. The manifest file is at the same folder level as the child folders with the name `manifest`. ``` unload ('select * from mytickit1.event') to 's3://amzn-s3-demo-bucket/partition' iam_role 'arn:aws:iam::123456789012:role/MyRedshiftRole' partition by (eventname) manifest; ``` The Amazon S3 files listing is similar to the following. ``` Name Type Last modified Size partition Folder ``` In the `partition` folder are child folders with the partition name and the manifest file. Shown following is the bottom of the list of folders in the `partition` folder, similar to the following. ``` Name Type Last modified Size ... eventname=Zucchero/ Folder eventname=Zumanity/ Folder eventname=ZZ Top/ Folder manifest - August 2, 2023, 15:54:39 (UTC-07:00) 467.6 KB ``` In the `eventname=Zucchero/` folder are the data files similar to the following. ``` Name Last modified Size 0000_part_00 - August 2, 2023, 15:59:19 (UTC-07:00) 70.0 B 0001_part_00 - August 2, 2023, 15:59:16 (UTC-07:00) 106.0 B 0002_part_00 - August 2, 2023, 15:59:15 (UTC-07:00) 70.0 B 0004_part_00 - August 2, 2023, 15:59:17 (UTC-07:00) 141.0 B 0006_part_00 - August 2, 2023, 15:59:16 (UTC-07:00) 35.0 B 0007_part_00 - August 2, 2023, 15:59:19 (UTC-07:00) 108.0 B ``` The bottom of the `manifest` file content is similar to the following. ``` { "entries": [ ... {"url":"s3://amzn-s3-demo-bucket/partition/eventname=Zucchero/007_part_00", "meta": { "content_length": 108 }}, {"url":"s3://amzn-s3-demo-bucket/partition/eventname=Zumanity/007_part_00", "meta": { "content_length": 72 }} ] } ``` ## Unload EVENT table using MAXFILESIZE, ROWGROUPSIZE, and MANIFEST parameters You can UNLOAD a table in parallel and generate a manifest file. The Amazon S3 data files are all created at the same level and names are suffixed with the pattern `0000_part_00`. The generated Parquet data files are limited to 256 MB and row group size 128 MB. The manifest file is at the same folder level as the data files and suffixed with `manifest`. ``` unload ('select * from mytickit1.event') to 's3://amzn-s3-demo-bucket/eventsize' iam_role 'arn:aws:iam::123456789012:role/MyRedshiftRole' maxfilesize 256 MB rowgroupsize 128 MB parallel on parquet manifest; ``` The Amazon S3 files listing is similar to the following. ``` Name Type Last modified Size eventsize0000_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 24.5 KB eventsize0001_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 24.8 KB eventsize0002_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 24.4 KB eventsize0003_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 24.0 KB eventsize0004_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 25.3 KB eventsize0005_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 24.8 KB eventsize0006_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 25.0 KB eventsize0007_part_00.parquet parquet August 2, 2023, 17:35:21 (UTC-07:00) 25.6 KB eventsizemanifest - August 2, 2023, 17:35:21 (UTC-07:00) 958.0 B ``` The `eventsizemanifest` file content is similar to the following. ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket/eventsize0000_part_00.parquet", "meta": { "content_length": 25130 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0001_part_00.parquet", "meta": { "content_length": 25428 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0002_part_00.parquet", "meta": { "content_length": 25025 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0003_part_00.parquet", "meta": { "content_length": 24554 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0004_part_00.parquet", "meta": { "content_length": 25918 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0005_part_00.parquet", "meta": { "content_length": 25362 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0006_part_00.parquet", "meta": { "content_length": 25647 }}, {"url":"s3://amzn-s3-demo-bucket/eventsize0007_part_00.parquet", "meta": { "content_length": 26256 }} ] } ```