Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

UNLOAD 示例

将 VENUE 卸载到竖线分隔的文件(默认分隔符)

注意

为便于阅读,这些示例包含换行符。请不要在您的 credentials-args 字符串中包含换行符或空格。

以下示例卸载 VENUE 表并将数据写入到 s3://mybucket/unload/

Copy
unload ('select * from venue') to 's3://mybucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

默认情况下,UNLOAD 为每个切片写入一个或多个文件。假定一个双节点集群中的每个节点有两个切片,上一个示例在 mybucket 中会创建这些文件:

Copy
unload/0000_part_00 unload/0001_part_00 unload/0002_part_00 unload/0003_part_00

为了更好地区分输出文件,可在位置中包含前缀。以下示例卸载 VENUE 表并将数据写入到 s3://mybucket/venue_pipe_

Copy
unload ('select * from venue') to 's3://mybucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

最后在 unload 文件夹中生成四个文件,再次假定有四个切片。

Copy
venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

将 VENUE 卸载到较小的文件

默认情况下,文件的最大大小为 6.2 GB。如果卸载数据大于 6.2GB,UNLOAD 将为每个 6.2GB 数据段创建一个新文件。要创建较小的文件,请包括 MAXFILESIZE 参数。假设上一个示例中的数据大小为 20 GB,则下面的 UNLOAD 命令将创建 20 个文件,每个文件的大小为 1 GB。

Copy
unload ('select * from venue') to 's3://mybucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' maxfilesize 1 gb;

连续卸载 VENUE

要连续卸载,请指定 PARALLEL OFF。然后,UNLOAD 将一次写入一个文件,每个文件的大小最多为 6.2GB。

以下示例连续卸载 VENUE 表并将数据写入到 s3://mybucket/unload/

Copy
unload ('select * from venue') to 's3://mybucket/unload/venue_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off;

最后生成一个名为 venue_serial_000 的文件。

如果卸载数据大于 6.2GB,UNLOAD 将为每个 6.2GB 数据段创建一个新文件。以下示例连续卸载 LINEORDER 表并将数据写入到 s3://mybucket/unload/

Copy
unload ('select * from lineorder') to 's3://mybucket/unload/lineorder_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off gzip;

最后生成下面的一系列文件。

Copy
lineorder_serial_0000.gz lineorder_serial_0001.gz lineorder_serial_0002.gz lineorder_serial_0003.gz

为了更好地区分输出文件,可在位置中包含前缀。以下示例卸载 VENUE 表并将数据写入到 s3://mybucket/venue_pipe_

Copy
unload ('select * from venue') to 's3://mybucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

最后在 unload 文件夹中生成四个文件,再次假定有四个切片。

Copy
venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

使用清单文件卸载 VENUE

要创建清单文件,请包含 MANIFEST 选项。以下示例卸载 VENUE 表,并将清单文件与数据文件一起写入到 s3://mybucket/venue_pipe_:

重要

如果使用 MANIFEST 选项卸载文件,则应在加载文件时将 MANIFEST 选项与 COPY 命令结合使用。如果您使用相同的前缀来加载文件且不指定 MANIFEST 选项,则 COPY 将失败,因为它假定清单文件是数据文件。

Copy
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

最后生成下面的 5 个文件:

Copy
s3://mybucket/venue_pipe_0000_part_00 s3://mybucket/venue_pipe_0001_part_00 s3://mybucket/venue_pipe_0002_part_00 s3://mybucket/venue_pipe_0003_part_00 s3://mybucket/venue_pipe_manifest

从卸载文件加载 VENUE

要从一组卸载文件加载表,只需使用 COPY 命令反向执行该过程即可。以下示例创建一个名为 LOADVENUE 的新表,并从上一个示例中创建的数据文件加载该表。

Copy
create table loadvenue (like venue); copy loadvenue from 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

如果您使用 MANIFEST 选项通过卸载文件创建清单文件,则可通过带 MANIFEST 选项的 COPY 命令,使用相同的清单文件加载数据。以下示例使用清单文件加载数据。

Copy
copy loadvenue from 's3://mybucket/venue_pipe_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

将 VENUE 卸载到加密文件

以下示例使用 KMS 密钥将 VENUE 表卸载到一组加密文件。如果您使用 ENCRYPTED 选项来指定清单文件,清单文件也将加密。有关更多信息,请参阅 卸载加密数据文件

Copy
unload ('select * from venue') to 's3://mybucket/venue_encrypt_kms' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' kms_key_id '1234abcd-12ab-34cd-56ef-1234567890ab' manifest encrypted;

以下示例使用主对称密钥将 VENUE 表卸载到一组加密文件。

Copy
unload ('select * from venue') to 's3://mybucket/venue_encrypt_cmk' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' encrypted;

从加密文件加载 VENUE

要从使用带 ENCRYPT 选项的 UNLOAD 创建的一组文件中加载表,请使用带 ENCRYPTED 选项的 COPY 命令反向执行该过程并指定用于 UNLOAD 命令的相同主对称密钥。以下示例从上一个示例创建的加密数据文件中加载 LOADVENUE 表。

Copy
create table loadvenue (like venue); copy loadvenue from 's3://mybucket/venue_encrypt_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' manifest encrypted;

将 VENUE 数据卸载到制表符分隔的文件

Copy
unload ('select venueid, venuename, venueseats from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t';

输出数据文件如下所示:

Copy
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 ...

使用临时凭证卸载 VENUE

您可以使用临时安全凭证限制用户对您的数据具有的访问权限。临时安全凭证可增强安全性,因为它们时效短,过期后无法重复使用。拥有这些临时安全凭证的用户只能在凭证过期之前访问您的资源。有关更多信息,请参阅 COPY 命令使用说明中的 临时安全凭证

以下示例使用临时凭证卸载 LISTING 表:

Copy
unload ('select venueid, venuename, venueseats from venue') to 's3://mybucket/venue_tab' credentials 'aws_access_key_id=<temporary-access-key-id>;aws_secret_access_key=<temporary-secret-access-key>;token=<temporary-token>' delimiter as '\t';

重要

临时安全凭证在 UNLOAD 语句的整个期间内必须有效。如果临时安全凭证在加载过程中过期,则 UNLOAD 将失败,事务将被回滚。例如,如果临时安全凭证在 15 分钟后过期而 UNLOAD 需要一个小时,则 UNLOAD 将在完成前失败。

将 VENUE 卸载到固定宽度的数据文件

Copy
unload ('select * from venue') to 's3://mybucket/venue_fw_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth as 'venueid:3,venuename:39,venuecity:16,venuestate:2,venueseats:6';

输出数据文件将类似于以下内容:

Copy
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 ...

将 VENUE 卸载到一组制表符分隔的 GZIP 压缩文件

Copy
unload ('select * from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t' gzip;

卸载包含分隔符的数据

此示例使用 ADDQUOTES 选项卸载逗号分隔的数据,其中一些实际数据字段中包含逗号。

首先,创建一个包含引号的表。

Copy
create table location (id int, location char(64)); insert into location values (1,'Phoenix, AZ'),(2,'San Diego, CA'),(3,'Chicago, IL');

然后,使用 ADDQUOTES 选项卸载数据。

Copy
unload ('select id, location from location') to 's3://mybucket/location_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter ',' addquotes;

卸载的数据文件如下所示:

Copy
1,"Phoenix, AZ" 2,"San Diego, CA" 3,"Chicago, IL" ...

卸载联接查询的结果

以下示例卸载包含窗口函数的联接查询的结果。

Copy
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://mybucket/tickit/winsum' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

输出文件如下所示:

Copy
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 ...

使用 NULL AS 进行卸载

默认情况下,UNLOAD 将 null 值作为空字符串输出。以下示例说明如何使用 NULL AS 来将文本字符串替换为 null 值。

对于这些示例,我们将向 VENUE 表添加几个 null 值。

Copy
update venue set venuestate = NULL where venuecity = 'Cleveland';

从 VENUESTATE 为 null 的 VENUE 中选择,以验证列是否包含 NULL。

Copy
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 (3 rows)

现在,使用 NULL AS 选项对 VENUE 表执行 UNLOAD 以便将 null 值替换为字符串“fred”。

Copy
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred';

以下来自卸载文件的示例说明已将 null 值替换为 fred。这证明 VENUESEATS 的一些值也为 null,并且已替换为 fred。即使 VENUESEATS 的数据类型为整数,UNLOAD 也会将值转换为卸载文件中的文本,然后 COPY 会将其转换回整数。如果卸载到固定宽度的文件,则 NULL AS 字符串不得大于字段宽度。

Copy
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 ...

要从卸载文件加载表,请使用带相同 NULL AS 选项的 COPY 命令。

注意

如果您尝试将 null 加载到定义为 NOT NULL 的列中,则 COPY 命令将失败。

Copy
create table loadvenuenulls (like venue); copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred';

要确认列包含 null 而不仅仅是包含空字符串,请从 LOADVENUENULLS 中选择并针对 null 进行筛选。

Copy
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 | ...

您可以使用默认 NULL AS 行为对包含 null 的表执行 UNLOAD 操作,然后使用默认 NULL AS 行为对数据执行 COPY 以复制回表中;不过,目标表中的任何非数字字段将包含空字符串而不是 null。默认情况下,UNLOAD 将 null 转换为空字符串(空格或零长度)。对于数字列,COPY 会将空字符串转换为 NULL,但会将空字符串插入非数字列中。以下示例说明如何在执行 COPY 后使用默认 NULL AS 行为执行 UNLOAD。

Copy
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

在本示例中,当您针对 null 进行筛选时,仅显示那些 VENUESEATS 包含 null 的行。其中,VENUESTATE 在表 (VENUE) 中包含 null,目标表 (LOADVENUENULLS) 中的 VENUESTATE 包含空字符串。

Copy
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 | ...

要将空字符串作为 NULL 加载到非数字列,请包含 EMPTYASNULL 或 BLANKSASNULL 选项。可以同时使用这两个选项。

Copy
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' EMPTYASNULL;

要确认列包含 NULL,而不只是包含空格或空字符串,请从 LOADVENUENULLS 中选择并针对 null 进行筛选。

Copy
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 | ...

ALLOWOVERWRITE 示例

默认情况下,UNLOAD 不会覆盖目标存储桶中的现有文件。例如,如果您运行同一个 UNLOAD 语句两次,而不修改目标存储桶中的文件,则第二次运行 UNLOAD 时将失败。要覆盖现有文件(包括清单文件),请指定 ALLOWOVERWRITE 选项。

Copy
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest allowoverwrite;