步骤 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 命令选项和功能:
-
键前缀
有关如何通过指定键前缀从多个文件加载的信息,请参阅使用 NULL AS 加载 PART 表。
-
CSV 格式
有关如何加载 CSV 格式的数据的信息,请参阅使用 NULL AS 加载 PART 表。
-
NULL AS
有关如何使用 NULL AS 选项加载 PART 的信息,请参阅使用 NULL AS 加载 PART 表。
-
字符分隔的格式
有关如何使用 DELIMITER 选项的信息,请参阅使用 REGION 加载 SUPPLIER 表。
-
REGION
有关如何使用 REGION 选项的信息,请参阅使用 REGION 加载 SUPPLIER 表。
-
固定格式宽度
有关如何从固定宽度数据加载 CUSTOMER 表的信息,请参阅使用 MANIFEST 加载 CUSTOMER 表。
-
MAXERROR
有关如何使用 MAXERROR 选项的信息,请参阅使用 MANIFEST 加载 CUSTOMER 表。
-
ACCEPTINVCHARS
有关如何使用 ACCEPTINVCHARS 选项的信息,请参阅使用 MANIFEST 加载 CUSTOMER 表。
-
MANIFEST
有关如何使用 MANIFEST 选项的信息,请参阅使用 MANIFEST 加载 CUSTOMER 表。
-
DATEFORMAT
有关如何使用 DATEFORMAT 选项的信息,请参阅使用 DATEFORMAT 加载 DWDATE 表。
-
GZIP、LZOP 和 BZIP2
有关如何压缩文件的信息,请参阅使用多个文件加载 LINEORDER 表。
-
COMPUPDATE
有关如何使用 COMPUPDATE 选项的信息,请参阅使用多个文件加载 LINEORDER 表。
-
多个文件
有关如何加载多个文件的信息,请参阅使用多个文件加载 LINEORDER 表。
加载 SSB 表
您将使用以下 COPY 命令加载 SSB Schema 中的每个表。针对每个表的命令演示了不同的 COPY 选项和疑难解答方法。
要加载 SSB 表,请按以下步骤操作:
替换桶名称和 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 命令,请执行以下操作:
-
将
<your-bucket-name>
替换为您的集群所在区域中的桶名称。此步骤假定桶和集群位于同一区域中。或者,您也可以通过对 COPY 命令使用 REGION 选项来指定区域。
-
用您自己的 AWS 账户 和 IAM 角色替换
<aws-account-id>
和<role-name>
。用单引号引起来的凭证字符串的区段不得包含任何空格或换行符。请注意,ARN 格式可能与示例略有不同。运行 COPY 命令时,最好从 IAM 控制台复制角色的 ARN,以确保其准确无误。
使用 NULL AS 加载 PART 表
在此步骤中,您将使用 CSV 和 NULL AS 选项加载 PART 表。
COPY 命令可从多个文件并行加载数据,这比从一个文件加载数据快得多。为了演示此原则,本教程中每个表的数据将拆分为 8 个文件,即使这些文件非常小。在后面的步骤中,您将比较从一个文件加载所需的时间与从多个文件加载所需的时间的差异。有关更多信息,请参阅 加载数据文件。
键前缀
您可以通过为文件集指定键前缀,或通过在清单文件中明确列出文件,来从多个文件加载。在此步骤中,您将使用键前缀。在后面的步骤中,您将使用清单文件。键前缀 's3://mybucket/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://mybucket/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://mybucket/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://mybucket/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 表的数据
-
在文本编辑器中打开文件
customer-fw-manifest
。 -
将
<your-bucket-name>
替换为您的桶的名称。 -
保存该文件。
-
将文件上传到您的桶上的加载文件夹中。
-
运行以下 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 的性能
-
从一个文件运行针对 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'; -
您的结果应类似于以下内容。请注意执行时间。
Warnings: Load into table 'lineorder' completed, 14996734 record(s) loaded successfully. 0 row(s) affected. copy executed successfully Execution time: 51.56s
-
从多个文件运行针对 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'; -
您的结果应类似于以下内容。请注意执行时间。
Warnings: Load into table 'lineorder' completed, 14996734 record(s) loaded successfully. 0 row(s) affected. copy executed successfully Execution time: 17.7s
-
比较执行时间。
在我们的示例中,加载 1500 万条记录所需的时间已从 51.56 秒减少至 17.7 秒(减少了 65.7%)。
这些结果通过使用 4 节点集群获得。如果您的集群具有更多节点,则将加倍节省时间。对于具有数十至数百个节点的典型 Amazon Redshift 集群,差异甚至更大。如果您有一个单节点集群,则执行时间之间的差异很小。