教程：从 Amazon S3 加载数据 - Amazon Redshift
先决条件 概述步骤 1：创建集群步骤 2：下载数据文件步骤 3：将文件上载到 Amazon S3 桶步骤 4：创建示例表步骤 5：运行 COPY 命令步骤 6：对数据库执行 vacuum 和分析操作步骤 7：清理资源摘要

教程：从 Amazon S3 加载数据

在本教程中，您将了解从 Amazon S3 桶中的数据文件将数据加载到您的 Amazon Redshift 数据库表中的完整过程。

在本教程中，您将执行以下操作：

  • 下载使用逗号分隔值 (CSV) 格式、字符分隔格式和固定宽度格式的数据文件。

  • 创建一个 Amazon S3 桶，然后将数据文件上载到该桶。

  • 启动 Amazon Redshift 集群并创建数据库表。

  • 使用 COPY 命令从 Amazon S3 上的数据文件加载表。

  • 诊断加载错误并修改您的 COPY 命令来更正这些错误。

估计时间：60 分钟

估算费用：集群每小时 1.00 美元

先决条件

您需要以下先决条件：

  • 用于启动 Amazon Redshift 集群并在 Amazon S3 中创建桶的 AWS 账户。

  • 您从 Amazon S3 加载测试数据的 AWS 凭证（IAM 角色）。如果您需要一个新的 IAM 角色，请转到创建 IAM 角色。

  • SQL 客户端，如 Amazon Redshift 控制台查询编辑器。

本教程设计为单独使用。除了本教程之外，还建议您完成以下教程来更全面地了解如何设计和使用 Amazon Redshift 数据库：

概述

您可以通过使用 INSERT 命令或 COPY 命令来将数据添加到您的 Amazon Redshift 表。在 Amazon Redshift 数据仓库的规模和速度方面，COPY 命令要比 INSERT 命令快许多倍且更高效。

COPY 命令使用 Amazon Redshift 大规模并行处理 (MPP) 架构来从多个数据来源并行读取和加载数据。您可以从 Amazon S3、Amazon EMR 或任何可通过 Secure Shell (SSH) 连接访问的远程主机上的数据文件加载。或者，您可以直接从 Amazon DynamoDB 表加载。

在本教程中，您将使用 COPY 命令来从 Amazon S3 加载数据。此处提到的许多原则也适用于从其他数据来源加载。

要了解有关使用 COPY 命令的更多信息，请参阅以下资源：

步骤 1：创建集群

如果您已有要使用的集群，则可跳过这一步。

在本教程的练习中，将使用四节点集群。

创建集群

  1. 登录 AWS Management Console，然后通过以下网址打开 Amazon Redshift 控制台：https://console.aws.amazon.com/redshiftv2/。

    使用导航菜单，选择预置集群控制面板

    重要

    请确保您具有执行集群操作所需的权限。有关授予必要权限的信息，请参阅授权 Amazon Redshift 访问 AWS 服务。

  2. 在右上角，选择要在其中创建集群的 AWS 区域。在本教程中，选择美国西部（俄勒冈州）。

  3. 在导航菜单上，选择集群，然后选择创建集群。此时将显示创建集群页面。

  4. 在创建集群页面输入您的集群参数。除更改以下值外，选择您自己的参数值：

    • 选择节点类型 dc2.large。

    • 为节点数量选择 4。

    • 在集群权限部分中，从可用 IAM 角色中选择一个 IAM 角色。此角色应是您之前创建的有权访问 Amazon S3 的角色。然后，选择关联 IAM 角色以将该角色添加到集群的关联的 IAM 角色列表中。

  5. 选择创建集群。

按 Amazon Redshift 入门指南中的步骤操作，以从 SQL 客户端连接到您的集群并测试连接。您无需完成此入门中的剩余步骤，即可创建表、上传数据和尝试示例查询。

步骤 2：下载数据文件

在此步骤中，您将一组示例数据文件下载到计算机。在下一个步骤中，您将这些文件上载到 Amazon S3 桶。

下载数据文件

  1. 下载压缩文件：LoadingDataSampleFiles.zip。

  2. 将文件提取到您计算机上的文件夹中。

  3. 验证您的文件夹是否包含以下文件。

    customer-fw-manifest
customer-fw.tbl-000
customer-fw.tbl-000.bak
customer-fw.tbl-001
customer-fw.tbl-002
customer-fw.tbl-003
customer-fw.tbl-004
customer-fw.tbl-005
customer-fw.tbl-006
customer-fw.tbl-007
customer-fw.tbl.log
dwdate-tab.tbl-000
dwdate-tab.tbl-001
dwdate-tab.tbl-002
dwdate-tab.tbl-003
dwdate-tab.tbl-004
dwdate-tab.tbl-005
dwdate-tab.tbl-006
dwdate-tab.tbl-007
part-csv.tbl-000
part-csv.tbl-001
part-csv.tbl-002
part-csv.tbl-003
part-csv.tbl-004
part-csv.tbl-005
part-csv.tbl-006
part-csv.tbl-007

步骤 3：将文件上载到 Amazon S3 桶

在此步骤中，您将创建一个 Amazon S3 桶并将数据文件上载到该桶。

要将文件上载到 Amazon S3 桶

  1. 在 Amazon S3 中创建一个桶。

    有关创建桶的更多信息，请参阅《Amazon Simple Storage Service 用户指南》中的创建桶。

    1. 登录到 AWS Management Console，然后通过以下网址打开 Amazon S3 控制台：https://console.aws.amazon.com/s3/。

    2. 选择创建桶。

    3. Choose an AWS 区域。

      在集群所在的区域中创建桶。如果您的集群位于美国西部（俄勒冈州）区域，请选择美国西部（俄勒冈州）区域 (us-west-2)。

    4. 在创建桶对话框的桶名称框中，输入桶名称。

      所选的桶名称在 Amazon S3 的所有现有桶名称中必须具有唯一性。确保唯一性的一种办法是以您所在的组织的名称作为您的桶名称的前缀。桶名称必须符合特定规则。有关更多信息，请转至《Amazon Simple Storage Service 用户指南》中的桶限制。

    5. 为其余选项选择推荐的默认值。

    6. 选择创建桶。

      Amazon S3 成功创建桶后，控制台的桶面板中将显示空桶。

  2. 创建一个文件夹。

    1. 选择新桶的名称。

    2. 选择创建文件夹按钮。

    3. 将新文件夹命名为 load。

      注意

      您创建的桶不会显示在沙盒中。在本练习中，您将对象添加到实际桶。您需要根据对象在桶中存储的时间支付象征性的费用。有关 Amazon S3 定价的更多信息，请前往 Amazon S3 定价页面。

  3. 将数据文件上载到新的 Amazon S3 桶。

    1. 选择数据文件夹的名称。

    2. 在上传向导中，选择添加文件。

      按照 Amazon S3 控制台的说明上载您下载并提取的所有文件。

    3. 选择上传。

用户凭证

Amazon Redshift COPY 命令必须具有对 Amazon S3 桶中的文件对象的读访问权。如果您使用同一用户凭证来创建 Amazon S3 桶并运行 Amazon Redshift COPY 命令，则 COPY 命令将具有所有必要权限。如果您希望使用其他用户凭证，则可以通过使用 Amazon S3 访问控制来授予访问权限。Amazon Redshift COPY 命令至少需要 ListBucket 和 GetObject 权限才能访问 Amazon S3 桶中的文件对象。有关如何控制对 Amazon S3 资源的访问权限的更多信息，请前往管理您的 Amazon S3 资源的访问权限。

步骤 4：创建示例表

在本教程中，您将基于 Star Schema Benchmark (SSB) Schema 使用一组表（共 5 个）。下图显示了 SSB 数据模型。

SSB 表可能已存在于当前数据库中。如果是这样的话，请先从数据库中删除这些表，然后再在下一步骤中使用 CREATE TABLE 命令创建这些表。本教程中使用的表可能包含与现有表不同的属性。

创建示例表

  1. 要删除 SSB 表，请在 SQL 客户端中运行以下命令。

    drop table part cascade;
drop table supplier;
drop table customer;
drop table dwdate;
drop table lineorder;

  2. 在 SQL 客户端中运行以下 CREATE TABLE 命令。

    CREATE TABLE part 
(
  p_partkey     INTEGER NOT NULL,
  p_name        VARCHAR(22) NOT NULL,
  p_mfgr        VARCHAR(6),
  p_category    VARCHAR(7) NOT NULL,
  p_brand1      VARCHAR(9) NOT NULL,
  p_color       VARCHAR(11) NOT NULL,
  p_type        VARCHAR(25) NOT NULL,
  p_size        INTEGER NOT NULL,
  p_container   VARCHAR(10) NOT NULL
);

CREATE TABLE supplier 
(
  s_suppkey   INTEGER NOT NULL,
  s_name      VARCHAR(25) NOT NULL,
  s_address   VARCHAR(25) NOT NULL,
  s_city      VARCHAR(10) NOT NULL,
  s_nation    VARCHAR(15) NOT NULL,
  s_region    VARCHAR(12) NOT NULL,
  s_phone     VARCHAR(15) NOT NULL
);

CREATE TABLE customer 
(
  c_custkey      INTEGER NOT NULL,
  c_name         VARCHAR(25) NOT NULL,
  c_address      VARCHAR(25) NOT NULL,
  c_city         VARCHAR(10) NOT NULL,
  c_nation       VARCHAR(15) NOT NULL,
  c_region       VARCHAR(12) NOT NULL,
  c_phone        VARCHAR(15) NOT NULL,
  c_mktsegment   VARCHAR(10) NOT NULL
);

CREATE TABLE dwdate 
(
  d_datekey            INTEGER NOT NULL,
  d_date               VARCHAR(19) NOT NULL,
  d_dayofweek          VARCHAR(10) NOT NULL,
  d_month              VARCHAR(10) NOT NULL,
  d_year               INTEGER NOT NULL,
  d_yearmonthnum       INTEGER NOT NULL,
  d_yearmonth          VARCHAR(8) NOT NULL,
  d_daynuminweek       INTEGER NOT NULL,
  d_daynuminmonth      INTEGER NOT NULL,
  d_daynuminyear       INTEGER NOT NULL,
  d_monthnuminyear     INTEGER NOT NULL,
  d_weeknuminyear      INTEGER NOT NULL,
  d_sellingseason      VARCHAR(13) NOT NULL,
  d_lastdayinweekfl    VARCHAR(1) NOT NULL,
  d_lastdayinmonthfl   VARCHAR(1) NOT NULL,
  d_holidayfl          VARCHAR(1) NOT NULL,
  d_weekdayfl          VARCHAR(1) NOT NULL
);
CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);

步骤 5：运行 COPY 命令

您将运行 COPY 命令来加载 SSB Schema 中的每个表。该 COPY 命令示例演示了使用多个 COPY 命令选项从不同文件格式加载并诊断加载错误。

COPY 命令语法

基本 COPY 命令语法如下所示。

COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options]

要运行 COPY 命令，您需提供以下值。

表名称

COPY 命令的目标表。该表必须已存在于数据库中。该表可以是临时的或永久的。COPY 命令会将新输入数据追加到该表中的任何现有行。

列列表

默认情况下，COPY 按顺序将源数据中的字段加载到表列中。您可以选择指定一个列列表（该列表是列名称的以逗号分隔的列表）以将数据字段映射到特定列。您在本教程中不使用列列表。有关更多信息，请参阅 COPY 命令参考中的Column List。

数据来源

您可以使用 COPY 命令从 Amazon S3 桶、Amazon EMR 集群、使用 SSH 连接的远程主机或 Amazon DynamoDB 表加载数据。在本教程中，您将从 Amazon S3 桶中的数据文件进行加载。在从 Amazon S3 进行加载时，您必须提供桶的名称和数据文件的位置。为此，请提供数据文件的对象路径或清单文件的位置，该清单文件明确列出了各个数据文件及其位置。

  • 键前缀

    存储在 Amazon S3 中的对象由对象键唯一标识，其中包括桶名称、文件夹名称（如果有）和对象名称。键前缀 是指一组带有相同前缀的对象。对象路径是 COPY 命令用于加载共享键前缀的所有对象的键前缀。例如，键前缀 custdata.txt 可以是一个文件或一组文件，包括 custdata.txt.001、custdata.txt.002 等。

  • 清单文件

    在某些情况下，您可能需要加载具有不同前缀的文件，例如，从多个桶或文件夹中进行加载。在其他情况下，您可能需要排除共享前缀的文件。在这些情况下，您可以使用清单文件。清单文件 明确列出了每个加载文件及其唯一对象键。在本教程的后面，您将使用清单文件来加载 PART 表。

凭证

要访问包含要加载的数据的 AWS 资源，您必须为具有足够权限的用户提供 AWS 访问凭证。这些凭证包括 IAM 角色的 Amazon 资源名称（ARN）。要从 Amazon S3 加载数据，凭证必须包括 ListBucket 和 GetObject 权限。如果数据已加密，则需要其它凭证。有关更多信息，请参阅 COPY 命令参考中的授权参数。有关管理访问的更多信息，请转到管理对 Amazon S3 资源的访问权限。

选项

您可以为 COPY 命令指定大量参数，以指定文件格式、管理数据格式、管理错误和控制其他功能。在本教程中，您将使用以下 COPY 命令选项和功能：

加载 SSB 表

您将使用以下 COPY 命令加载 SSB Schema 中的每个表。针对每个表的命令演示了不同的 COPY 选项和疑难解答方法。

要加载 SSB 表，请按以下步骤操作：

  1. 替换桶名称和 AWS 凭证

  2. 使用 NULL AS 加载 PART 表

  3. 使用 REGION 加载 SUPPLIER 表

  4. 使用 MANIFEST 加载 CUSTOMER 表

  5. 使用 DATEFORMAT 加载 DWDATE 表

  6. 使用多个文件加载 LINEORDER 表

替换桶名称和 AWS 凭证

本教程中的 COPY 命令采用以下格式显示。

copy table from 's3://<your-bucket-name>/load/key_prefix' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
options;

对于每个 COPY 命令，请执行以下操作：

  1. 将 <your-bucket-name> 替换为您的集群所在区域中的桶名称。

    此步骤假定桶和集群位于同一区域中。或者，您也可以通过对 COPY 命令使用 REGION 选项来指定区域。

  2. 用您自己的 AWS 账户 和 IAM 角色替换 <aws-account-id> 和 <role-name>。用单引号引起来的凭证字符串的区段不得包含任何空格或换行符。请注意，ARN 格式可能与示例略有不同。运行 COPY 命令时，最好从 IAM 控制台复制角色的 ARN，以确保其准确无误。

使用 NULL AS 加载 PART 表

在此步骤中，您将使用 CSV 和 NULL AS 选项加载 PART 表。

COPY 命令可从多个文件并行加载数据，这比从一个文件加载数据快得多。为了演示此原则，本教程中每个表的数据将拆分为 8 个文件，即使这些文件非常小。在后面的步骤中，您将比较从一个文件加载所需的时间与从多个文件加载所需的时间的差异。有关更多信息，请参阅 加载数据文件。

键前缀

您可以通过为文件集指定键前缀，或通过在清单文件中明确列出文件，来从多个文件加载。在此步骤中，您将使用键前缀。在后面的步骤中，您将使用清单文件。键前缀 's3://amzn-s3-demo-bucket/load/part-csv.tbl' 加载 load 文件夹中的以下一组文件。

part-csv.tbl-000
part-csv.tbl-001
part-csv.tbl-002
part-csv.tbl-003
part-csv.tbl-004
part-csv.tbl-005
part-csv.tbl-006
part-csv.tbl-007
CSV 格式

CSV（表示用逗号分隔的值）是一种用于导入和导出电子表格数据的常见格式。CSV 比逗号分隔的格式更灵活，因为它使您能够在字段中包含带引号的字符串。CSV 格式的 COPY 的默认引号字符是双引号 (")，但您可以通过使用 QUOTE AS 选项指定另一种引号字符。在字段中使用引号字符时，应使用另一种引号字符对该字符进行转义。

PART 表中采用 CSV 格式的数据文件中的以下摘录显示了使用双引号引起来的字符串 ("LARGE ANODIZED BRASS")。它还显示一个用引号引起来的字符串中使用两个双引号引起来的字符串 ("MEDIUM ""BURNISHED"" TIN")。

15,dark sky,MFGR#3,MFGR#47,MFGR#3438,indigo,"LARGE ANODIZED BRASS",45,LG CASE
22,floral beige,MFGR#4,MFGR#44,MFGR#4421,medium,"PROMO, POLISHED BRASS",19,LG DRUM
23,bisque slate,MFGR#4,MFGR#41,MFGR#4137,firebrick,"MEDIUM ""BURNISHED"" TIN",42,JUMBO JAR

PART 表的数据包含将导致 COPY 命令失败的字符。在本练习中，您将诊断并纠正错误。

要加载采用 CSV 格式的数据，请将 csv 添加到您的 COPY 命令。运行以下命令可加载 PART 表。

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
csv;

您可能会收到类似于以下内容的错误消息。

An error occurred when executing the SQL command:
copy part from 's3://amzn-s3-demo-bucket/load/part-csv.tbl' 
credentials' ...

ERROR: Load into table 'part' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 1.46s

1 statement(s) failed.
1 statement(s) failed.

要获取有关错误的更多信息，请查询 STL_LOAD_ERRORS 表。以下查询使用 SUBSTRING 函数缩短列以方便阅读，并使用 LIMIT 10 减少返回的行数。您可以调整 substring(filename,22,25) 中的值以允许您的桶名称的长度。

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as reason
from stl_load_errors 
order by query desc
limit 10;
 query  |    filename      | line |  column   |    type    | pos |      
--------+-------------------------+-----------+------------+------------+-----+----
 333765 | part-csv.tbl-000 |    1 |           |            |   0 |

 line_text        | field_text |                    reason
------------------+------------+----------------------------------------------
 15,NUL next,     |            | Missing newline: Unexpected character 0x2c f
NULL AS

part-csv.tbl 数据文件使用 NUL 终止符（\x000 或 \x0）来指示 NULL 值。

注意

尽管 NUL 和 NULL 的拼写非常相似，但两者并不相同。NUL 是一种带代码点 x000 的 UTF-8 字符，通常用于指示记录结束 (EOR)。NULL 是一个表示数据缺失的 SQL 值。

默认情况下，COPY 将 NUL 终止符视为 EOR 字符并终止记录，这通常会导致意外结果或错误。没有在文本数据中指示 NULL 的单个标准方法。因此，使用 NULL AS COPY 命令选项可以指定加载表时替换为 NULL 的字符。在本示例中，您希望 COPY 将 NUL 终止符视为 NULL 值。

注意

接收 NULL 值的表列必须配置为不可为 null。即，该表列不得包含 CREATE TABLE 规格中的 NOT NULL 约束。

要使用 NULL AS 选项加载 PART，请运行以下 COPY 命令。

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv
null as '\000';

要验证 COPY 加载的 NULL 值，请运行以下命令以仅选择包含 NULL 的行。

select p_partkey, p_name, p_mfgr, p_category from part where p_mfgr is null;
 p_partkey |  p_name  | p_mfgr | p_category
-----------+----------+--------+------------
        15 | NUL next |        | MFGR#47
        81 | NUL next |        | MFGR#23
       133 | NUL next |        | MFGR#44 
(2 rows)

使用 REGION 加载 SUPPLIER 表

在此步骤中，您将使用 DELIMITER 和 REGION 选项来加载 SUPPLIER 表。

注意

我们在 AWS 示例桶中提供了加载 SUPPLIER 表的文件。因此，在此步骤中，您无需上传文件。

字符分隔的格式

字符分隔的文件中的字段由某个特定字符（如竖线字符 (|)、逗号 (,) 或制表符 (\t)）分隔。字符分隔文件的可使用任一 ASCII 字符（包括非打印 ASCII 字符之一）作为分隔符。通过使用 DELIMITER 选项指定分隔符。默认分隔符是竖线字符 (|)。

SUPPLIER 表中数据的以下摘要使用竖线分隔的格式。

1|1|257368|465569|41365|19950218|2-HIGH|0|17|2608718|9783671|4|2504369|92072|2|19950331|TRUCK
1|2|257368|201928|8146|19950218|2-HIGH|0|36|6587676|9783671|9|5994785|109794|6|19950416|MAIL
REGION

只要可能，您应在 Amazon Redshift 集群所在的 AWS 区域中找到加载数据。如果您的数据和集群位于同一区域中，则将减少延迟并避免跨区域数据传输成本。有关更多信息，请参阅Amazon Redshift 加载数据的最佳实践。

如果您必须从另一个 AWS 区域加载数据，可使用 REGION 选项指定从中查找加载数据的 AWS 区域。如果您指定一个区域，则所有加载数据（包括清单文件）必须位于已命名的区域中。有关更多信息，请参阅 REGION。

如果您的集群位于美国东部（弗吉尼亚北部）区域，请运行以下命令来从位于美国西部（俄勒冈州）区域中的 Amazon S3 桶中用竖线分隔的数据加载 SUPPLIER 表。在本示例中，请不要更改桶名称。

copy supplier from 's3://awssampledbuswest2/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-west-2';

如果您的集群未位于美国东部（弗吉尼亚北部）区域，请运行以下命令来从位于美国东部（弗吉尼亚北部）区域中的 Amazon S3 桶中用竖线分隔的数据加载 SUPPLIER 表。在本示例中，请不要更改桶名称。

copy supplier from 's3://awssampledb/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-east-1';

使用 MANIFEST 加载 CUSTOMER 表

在此步骤中，您将使用 FIXEDWIDTH、MAXERROR、ACCEPTINVCHARS 和 MANIFEST 选项来加载 CUSTOMER 表。

本练习中的示例数据包含的字符将在 COPY 命令尝试加载数据时导致错误。您将使用 MAXERRORS 选项和 STL_LOAD_ERRORS 系统表来诊断加载错误，然后使用 ACCEPTINVCHARS 和 MANIFEST 选项来消除错误。

固定宽度格式

固定宽度格式将每个字段定义为固定数量的字符，而不是使用分隔符隔开的字段。CUSTOMER 表中数据的以下摘要使用固定宽度格式。

1   Customer#000000001   IVhzIApeRb           MOROCCO  0MOROCCO  AFRICA      25-705 
2   Customer#000000002   XSTf4,NCwDVaWNe6tE   JORDAN   6JORDAN   MIDDLE EAST 23-453
3   Customer#000000003   MG9kdTD              ARGENTINA5ARGENTINAAMERICA     11-783

标签/宽度对的顺序必须与表列的顺序完全一致。有关更多信息，请参阅 FIXEDWIDTH。

CUSTOMER 表数据的固定宽度规范字符串如下所示。

fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, 
c_region :12, c_phone:15,c_mktsegment:10'

要从固定宽度数据加载 CUSTOMER 表，请运行以下命令。

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10';

您应收到类似于以下内容的错误消息。

An error occurred when executing the SQL command:
copy customer
from 's3://amzn-s3-demo-bucket/load/customer-fw.tbl'
credentials'...

ERROR: Load into table 'customer' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 2.95s

1 statement(s) failed.
MAXERROR

原定设置情况下，COPY 首次遇到错误时，该命令将失败并返回错误消息。要在测试期间节省时间，您可以使用 MAXERROR 选项来指示 COPY 在跳过指定数量的错误后失败。由于我们预计了首次测试加载 CUSTOMER 表数据时的错误，因此将 maxerror 10 添加到 COPY 命令。

要使用 FIXEDWIDTH 和 MAXERROR 选项进行测试，请运行以下命令。

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10;

这一次，您将收到类似于以下内容的警告消息，而不是错误消息。

Warnings:
Load into table 'customer' completed, 112497 record(s) loaded successfully.
Load into table 'customer' completed, 7 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.

该警告指示 COPY 遇到了 7 个错误。要查看这些错误，请查询 STL_LOAD_ERRORS 表，如以下示例所示。

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as error_reason
from stl_load_errors 
order by query desc, filename 
limit 7;

STL_LOAD_ERRORS 查询的结果应类似于以下内容。

 query  |         filename          | line |  column   |    type    | pos |           line_text           | field_text |              error_reason
--------+---------------------------+------+-----------+------------+-----+-------------------------------+------------+----------------------------------------------
 334489 | customer-fw.tbl.log       |    2 | c_custkey | int4       |  -1 | customer-fw.tbl               | customer-f | Invalid digit, Value 'c', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    6 | c_custkey | int4       |  -1 | Complete                      | Complete   | Invalid digit, Value 'C', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    3 | c_custkey | int4       |  -1 | #Total rows                   | #Total row | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    5 | c_custkey | int4       |  -1 | #Status                       | #Status    | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    1 | c_custkey | int4       |  -1 | #Load file                    | #Load file | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
(7 rows)

通过检查结果，您可以看到 error_reasons 列中有以下两条消息：

  • Invalid digit, Value '#', Pos 0, Type: Integ

    这些错误是由 customer-fw.tbl.log 文件导致的。问题在于它是一个日志文件而不是数据文件且不应加载。可以使用清单文件来避免加载错误文件。

  • String contains invalid or unsupported UTF8

    VARCHAR 数据类型支持多达 3 个字符的多字节 UTF-8 字符。如果加载数据包含不支持的或无效的字符，则可使用 ACCEPTINVCHARS 选项将每个无效字符替换为指定的替代字符。

另一个加载问题是更难以检测 – 加载生成了意外结果。要调查此问题，请运行以下命令来查询 CUSTOMER 表。

select c_custkey, c_name, c_address        
from customer
order by c_custkey
limit 10;

 c_custkey |          c_name           |         c_address
-----------+---------------------------+---------------------------
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         3 | Customer#000000003        | MG9kdTD
         3 | Customer#000000003        | MG9kdTD
         4 | Customer#000000004        | XxVSJsL
         4 | Customer#000000004        | XxVSJsL
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
(10 rows)

行应是唯一的，但存在重复项。

检查意外结果的另一种方法是验证已加载的行的数量。在我们的示例中，应已加载 100000 行，但加载消息报告已加载 112497 条记录。导致加载了额外的行的原因是，COPY 加载了无关的文件 customer-fw.tbl0000.bak。

在本练习中，您将使用清单文件来避免加载错误文件。

ACCEPTINVCHARS

原定设置情况下，在 COPY 遇到列的数据类型不支持的字符时，它会跳过该行并返回错误。有关无效的 UTF-8 字符的信息，请参阅多字节字符加载错误。

您可使用 MAXERRORS 选项忽略错误并继续加载，然后查询 STL_LOAD_ERRORS 以找到无效的字符，并修复数据文件。但是，MAXERRORS 最适合用于解决加载问题，并且通常不应用于生产环境。

ACCEPTINVCHARS 选项通常是用于管理无效字符的更佳选择。ACCEPTINVCHARS 指示 COPY 将每个无效字符替换为指定的有效字符，然后继续加载操作。您可以指定任何有效的 ASCII 字符（NULL 除外）作为替换字符。默认替换字符是问号 ( ? )。COPY 将多字节字符替换为等长的替换字符串。例如，一个 4 字节字符将替换为 '????'。

COPY 命令将返回包含无效 UTF-8 字符的行数。它还为每个受影响的行向 STL_REPLACEMENTS 系统表添加一个条目，每个节点分片最多添加 100 个行。还将替换其他无效的 UTF-8 字符，但不会记录这些替换事件。

ACCEPTINVCHARS 仅对 VARCHAR 列有效。

在此步骤中，您将使用替换字符 '^' 添加 ACCEPTINVCHARS。

MANIFEST

当您使用键前缀从 Amazon S3 执行 COPY 命令时，可能会加载不需要的表。例如，'s3://amzn-s3-demo-bucket/load/ 文件夹包含 8 个数据文件，这些文件共享键前缀 customer-fw.tbl：customer-fw.tbl0000、customer-fw.tbl0001 等。但是，同一文件夹也包含无关的文件：customer-fw.tbl.log 和 customer-fw.tbl-0001.bak。

要确保您加载所有正确的文件且仅有正确的文件，请使用清单文件。清单是采用 JSON 格式的文本文件，可明确列出要加载的每个源文件的唯一对象键。文件对象可位于不同的文件夹或桶中，但它们必须位于同一区域内。有关更多信息，请参阅 MANIFEST。

下面显示了 customer-fw-manifest 文本。

{
  "entries": [
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-000"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-001"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-002"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-003"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-004"},    
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-005"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-006"}, 
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-007"} 
    ]
}
使用清单文件加载 CUSTOMER 表的数据

  1. 在文本编辑器中打开文件 customer-fw-manifest。

  2. 将 <your-bucket-name> 替换为您的桶的名称。

  3. 保存该文件。

  4. 将文件上传到您的桶上的加载文件夹中。

  5. 运行以下 COPY 命令。

    copy customer from 's3://<your-bucket-name>/load/customer-fw-manifest'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10 
acceptinvchars as '^'
manifest;

使用 DATEFORMAT 加载 DWDATE 表

在此步骤中，您将使用 DELIMITER 和 DATEFORMAT 选项加载 DWDATE 表。

加载 DATE 和 TIMESTAMP 列时，COPY 应为默认格式，即 YYYY-MM-DD（对于日期）和 YYYY-MM-DD HH:MI:SS（对于时间戳）。如果加载数据不使用默认格式，则可使用 DATEFORMAT 和 TIMEFORMAT 指定格式。

以下摘要显示 DWDATE 表中的日期格式。请注意，列 2 中的日期格式不一致。

19920104	1992-01-04          Sunday		January	1992	199201	Jan1992	1	4	4	1...
19920112	January 12, 1992	Monday		January	1992	199201	Jan1992	2	12	12	1...
19920120	January 20, 1992	Tuesday	    January	1992	199201	Jan1992	3	20	20	1...
DATEFORMAT

您只能指定一种日期格式。如果加载数据包含不一致的格式（可能位于不同的列中），或者格式在加载时未知，则使用带 'auto' 参数的 DATEFORMAT。指定 'auto' 后，COPY 命令将识别任何有效的日期或时间格式并将它转换为默认格式。在使用 DATEFORMAT 和 TIMEFORMAT 字符串时，'auto' 选项将识别一些不受支持的格式。有关更多信息，请参阅 在 DATEFORMAT 和 TIMEFORMAT 中使用自动识别。

要加载 DWDATE 表，请运行以下 COPY 命令。

copy dwdate from 's3://<your-bucket-name>/load/dwdate-tab.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '\t' 
dateformat 'auto';

使用多个文件加载 LINEORDER 表

此步骤使用 GZIP 和 COMPUPDATE 选项加载 LINEORDER 表。

在本练习中，您将从一个数据文件中加载 LINEORDER 表，然后从多个文件再次加载该表。通过执行此操作，您可以比较两种方法的加载时间。

注意

我们在 AWS 示例桶中提供了加载 LINEORDER 表的文件。因此，在此步骤中，您无需上传文件。

GZIP、LZOP 和 BZIP2

您可以使用 gzip、lzop 或 bzip2 压缩格式压缩您的文件。在从压缩文件加载时，COPY 会在加载过程中解压缩这些文件。压缩文件将节省存储空间并缩短上传时间。

COMPUPDATE

当 COPY 加载无压缩编码的空表时，它会分析加载数据以确定最佳编码。然后，它会修改该表以在开始加载前使用这些编码。此分析过程比较费时，最多对每个表执行一次此过程。要节省时间，您可以通过关闭 COMPUPDATE 来跳过此步骤。为了准确评估 COPY 时间，您将在此步骤中关闭 COMPUPDATE。

多个文件

COPY 命令可在从多个文件并行加载（而不是从一个文件加载）时非常高效地加载数据。您可以将数据拆分成多个文件，以便文件数是您的集群中的切片数的倍数。这样，Amazon Redshift 将划分工作负载并在切片之间均匀分配数据。每个节点的切片数取决于集群的节点大小。有关每个节点大小的切片数的更多信息，请转到《Amazon Redshift 管理指南》中的关于集群和节点。

例如，本教程中使用的每个 dc2.large 计算节点具有两个切片，因此 4 节点集群具有 8 个切片。在前面的步骤中，加载数据已包含在 8 个文件中，尽管这些文件非常小。在此步骤中，您将比较从一个大文件加载所需的时间与从多个文件加载所需的时间的差异。

您在本教程中使用的文件包含约 1500 万条记录，约占 1.2 GB 空间。这些文件在 Amazon Redshift 范围中非常小，但足以展示从多个文件加载的性能优势。这些文件过大，下载它们并将其上载到 Amazon S3 所需的时间对于本教程来说过多。因此，您将直接从 AWS 示例桶加载这些文件。

以下屏幕截图显示 LINEORDER 的数据文件。

使用多个文件评估 COPY 的性能

  1. 从一个文件运行针对 COPY 的以下命令。请不要更改桶名称。

    copy lineorder from 's3://awssampledb/load/lo/lineorder-single.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
gzip
compupdate off
region 'us-east-1';

  2. 您的结果应类似于以下内容。请注意执行时间。

    Warnings:
Load into table 'lineorder' completed, 14996734 record(s) loaded successfully.

0 row(s) affected.
copy executed successfully

Execution time: 51.56s

  3. 从多个文件运行针对 COPY 的以下命令。请不要更改桶名称。

    copy lineorder from 's3://awssampledb/load/lo/lineorder-multi.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
gzip
compupdate off
region 'us-east-1';

  4. 您的结果应类似于以下内容。请注意执行时间。

    Warnings:
Load into table 'lineorder' completed, 14996734 record(s) loaded successfully.

0 row(s) affected.
copy executed successfully

Execution time: 17.7s

  5. 比较执行时间。

    在我们的示例中，加载 1500 万条记录所需的时间已从 51.56 秒减少至 17.7 秒（减少了 65.7%）。

    这些结果通过使用 4 节点集群获得。如果您的集群具有更多节点，则将加倍节省时间。对于具有数十至数百个节点的典型 Amazon Redshift 集群，差异甚至更大。如果您有一个单节点集群，则执行时间之间的差异很小。

步骤 6：对数据库执行 vacuum 和分析操作

当添加、删除或修改大量行时，您应运行 VACUUM 命令，然后运行 ANALYZE 命令。vacuum 将从已删除行中恢复空间并还原排序顺序。ANALYZE 命令更新统计元数据，这使查询优化程序能够生成更准确的查询计划。有关更多信息，请参阅 对表执行 vacuum 操作。

如果您按排序键顺序加载数据，则 vacuum 操作的速度会很快。在本教程中，虽然您添加了大量行，但这些行添加到了空表中。在这种情况下，无需重新排序，而且您不删除任何行。COPY 在加载空表后自动更新统计数据，因此您的统计数据应是最新的。但是，要实现出色的事务管理，您需要通过对数据库执行 vacuum 和分析操作来完成本教程。

要对数据库执行 vacuum 和分析操作，请运行以下命令。

vacuum;
analyze;

步骤 7：清理资源

只要您的集群正在运行，就将继续产生费用。完成本教程后，您应按照《Amazon Redshift 入门指南》中的步骤 5：撤消访问权限并删除示例集群中的步骤操作，以使您的环境返回上一个状态。

如果您希望保留集群，但恢复 SSB 表使用的存储空间，请运行以下命令。

drop table part;
drop table supplier;
drop table customer;
drop table dwdate;
drop table lineorder;

下一步

Summary

Summary

在本教程中，您已将数据文件上载到 Amazon S3，然后已使用 COPY 命令将数据从文件加载到 Amazon Redshift 表中。

您已使用以下格式加载数据：

  • 字符分隔的

  • CSV

  • 固定宽度

您使用了 STL_LOAD_ERRORS 系统表来针对加载错误，然后使用了 REGION、MANIFEST、MAXERROR、ACCEPTINVCHARS、DATEFORMAT 和 NULL AS 选项纠正这些错误。

您应用了加载数据的以下最佳实践：

有关 Amazon Redshift 最佳实践的更多信息，请参阅以下链接：