从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 # COPY 示例 **注意** 为便于阅读，这些示例包含换行符。请不要在您的 *credentials-args* 字符串中包含换行符或空格。 **Topics** + [从 DynamoDB 表中加载 FAVORITEMOVIES](#r_COPY_command_examples-load-favoritemovies-from-an-amazon-dynamodb-table) + [从 Amazon S3 桶中加载 LISTING](#r_COPY_command_examples-load-listing-from-an-amazon-s3-bucket) + [从 Amazon EMR 集群中加载 LISTING](#copy-command-examples-emr) + [Example: COPY from Amazon S3 using a manifest](#copy-command-examples-manifest) + [从以竖线（默认分隔符）分隔的文件中加载 LISTING](#r_COPY_command_examples-load-listing-from-a-pipe-delimited-file-default-delimiter) + [使用 Parquet 格式的列式数据加载 LISTING](#r_COPY_command_examples-load-listing-from-parquet) + [使用 ORC 格式的列式数据加载 LISTING](#r_COPY_command_examples-load-listing-from-orc) + [使用选项加载 EVENT](#r_COPY_command_examples-load-event-with-options) + [从固定宽度的数据文件中加载 VENUE](#r_COPY_command_examples-load-venue-from-a-fixed-width-data-file) + [从 CSV 文件中加载 CATEGORY](#load-from-csv) + [加载具有显式的 IDENTITY 列值的 VENUE](#r_COPY_command_examples-load-venue-with-explicit-values-for-an-identity-column) + [从以竖线分隔的 GZIP 文件中加载 TIME](#r_COPY_command_examples-load-time-from-a-pipe-delimited-gzip-file) + [加载时间戳或日期戳](#r_COPY_command_examples-load-a-time-datestamp) + [从具有默认值的文件中加载数据](#r_COPY_command_examples-load-data-from-a-file-with-default-values) + [使用 ESCAPE 选项复制数据](#r_COPY_command_examples-copy-data-with-the-escape-option) + [从 JSON 中复制的示例](#r_COPY_command_examples-copy-from-json) + [从 Avro 中复制的示例](#r_COPY_command_examples-copy-from-avro) + [使用 ESCAPE 选项为 COPY 准备文件](#r_COPY_preparing_data) + [将 shapefile 加载到 Amazon Redshift](#copy-example-spatial-copy-shapefile) + [带有 NOLOAD 选项的 COPY 命令](#r_COPY_command_examples-load-noload-option) + [带有多字节分隔符和 ENCODING 选项的 COPY 命令](#r_COPY_command_examples-load-encoding-multibyte-delimiter-option) ## 从 DynamoDB 表中加载 FAVORITEMOVIES AWS 开发工具包包括一个创建名为 *Movies* 的 DynamoDB 表的简单示例。（有关此示例，请参阅 [DynamoDB 入门](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStarted.html)。） 以下示例加载包含 DynamoDB 表中数据的 Amazon Redshift MOVIES 表。Amazon Redshift 表必须已存在于数据库中。 ``` copy favoritemovies from 'dynamodb://Movies' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' readratio 50; ``` ## 从 Amazon S3 桶中加载 LISTING 以下示例从 Amazon S3 桶加载 LISTING。COPY 命令将加载 `/data/listing/` 文件夹中的所有文件。 ``` copy listing from 's3://amzn-s3-demo-bucket/data/listing/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` ## 从 Amazon EMR 集群中加载 LISTING 以下示例从 Amazon EMR 集群的 lzop 压缩文件加载使用制表符分隔数据的 SALES 表。COPY 加载 `myoutput/` 文件夹中每个以 `part-` 开头的文件。 ``` copy sales from 'emr://j-SAMPLE2B500FC/myoutput/part-*' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '\t' lzop; ``` 以下示例将加载包含 Amazon EMR 集群中的 JSON 格式的数据的 SALES 表。COPY 加载 `myoutput/json/` 文件夹中的每个文件。 ``` copy sales from 'emr://j-SAMPLE2B500FC/myoutput/json/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' JSON 's3://amzn-s3-demo-bucket/jsonpaths.txt'; ``` ## 使用清单指定数据文件 您可以使用清单确保 COPY 命令将从 Amazon S3 加载所有必需的文件，而且仅加载必需的文件。当您需要从不同的桶加载多个文件或加载未共享相同前缀的文件时，您也可使用清单。 例如，假设您需要加载下列三个文件：`custdata1.txt`、`custdata2.txt` 和 `custdata3.txt`。您可使用以下命令通过指定前缀来加载 `amzn-s3-demo-bucket` 中以 `custdata` 开头的所有文件： ``` copy category from 's3://amzn-s3-demo-bucket/custdata' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` 如果由于错误仅存在两个文件，则 COPY 仅加载这两个文件并成功完成，从而导致不完整的数据加载。如果桶还包含恰巧使用相同前缀的不需要的文件（例如名为 `custdata.backup` 的文件），则 COPY 还加载此文件，从而导致加载不需要的数据。 为了确保加载所有必需的文件并防止加载不需要的文件，您可使用清单文件。清单是 JSON 格式的文本文件，其中列出了要通过 COPY 命令处理的文件。例如，以下清单将加载上例中的三个文件。 ``` { "entries":[ { "url":"s3://amzn-s3-demo-bucket/custdata.1", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/custdata.2", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/custdata.3", "mandatory":true } ] } ``` 可选的 `mandatory` 标志指示 COPY 是否应在文件不存在时终止。默认为 `false`。如果未找到任何文件，则无论 mandatory 设置如何，COPY 都会终止。在此示例中，如果未找到任何文件，COPY 将返回错误。将忽略可能会在仅指定键前缀（如 `custdata.backup`）的情况下选取的不需要的文件，因为它们不在清单上。 在从采用 ORC 或 Parquet 格式的数据文件中加载时，需要 `meta` 字段，如以下示例所示。 ``` { "entries":[ { "url":"s3://amzn-s3-demo-bucket1/orc/2013-10-04-custdata", "mandatory":true, "meta":{ "content_length":99 } }, { "url":"s3://amzn-s3-demo-bucket2/orc/2013-10-05-custdata", "mandatory":true, "meta":{ "content_length":99 } } ] } ``` 以下示例使用名为 `cust.manifest` 的清单。 ``` copy customer from 's3://amzn-s3-demo-bucket/cust.manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as orc manifest; ``` 您可以使用清单来加载不同桶或文件中未共享相同前缀的文件。以下示例显示了用于加载名称以日期戳开头的文件中的数据的 JSON。 ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket/2013-10-04-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-05-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-06-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-07-custdata.txt","mandatory":true} ] } ``` 此清单可列出位于不同桶中的文件，前提是桶与集群位于同一 AWS 区域。 ``` { "entries": [ {"url":"s3://amzn-s3-demo-bucket1/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/custdata2.txt","mandatory":false} ] } ``` ## 从以竖线（默认分隔符）分隔的文件中加载 LISTING 以下示例是一个非常简单的示例，其中未指定任何选项并且输入文件包含默认分隔符，即竖线字符（“\$1”）。 ``` copy listing from 's3://amzn-s3-demo-bucket/data/listings_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` ## 使用 Parquet 格式的列式数据加载 LISTING 以下示例从 Amazon S3 上的名为 parquet 的文件夹加载数据。 ``` copy listing from 's3://amzn-s3-demo-bucket/data/listings/parquet/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as parquet; ``` ## 使用 ORC 格式的列式数据加载 LISTING 以下示例从 Amazon S3 上名为 `orc` 的文件夹加载数据。 ``` copy listing from 's3://amzn-s3-demo-bucket/data/listings/orc/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as orc; ``` ## 使用选项加载 EVENT 以下示例将竖线分隔的数据加载到 EVENT 表中并应用下列规则： + 如果使用了引号对来括起任何字符串，则会删除它们。 + 空字符串和包含空白的字符串将作为 NULL 值加载。 + 如果返回了 5 个以上的错误，则加载失败。 + 时间戳值必须遵循指定的格式；例如，有效的时间戳为 `2008-09-26 05:43:12`。 ``` copy event from 's3://amzn-s3-demo-bucket/data/allevents_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' removequotes emptyasnull blanksasnull maxerror 5 delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS'; ``` ## 从固定宽度的数据文件中加载 VENUE ``` copy venue from 's3://amzn-s3-demo-bucket/data/venue_fw.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth 'venueid:3,venuename:25,venuecity:12,venuestate:2,venueseats:6'; ``` 上例假设数据文件与所示的样本数据是使用相同的方式设置格式的。在下面的示例中，空格充当占位符，以便所有列的宽度与规范中的规定相同： ``` 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 ``` ## 从 CSV 文件中加载 CATEGORY 假设您要加载具有下表中所示值的 CATEGORY。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_COPY_command_examples.html) 以下示例显示了字段值用逗号隔开的文本文件的内容。 ``` 12,Shows,Musicals,Musical theatre 13,Shows,Plays,All "non-musical" theatre 14,Shows,Opera,All opera, light, and "rock" opera 15,Concerts,Classical,All symphony, concerto, and choir concerts ``` 如果您在加载文件时使用 DELIMITER 参数指定逗号分隔的输入，则 COPY 命令失败，因为一些输入字段包含逗号。您可通过使用 CSV 参数并将包含逗号的字段括在引号字符中来避免以上问题。如果用引号括起来的字符串中出现引号字符，则需要通过双引号字符来进行转义。默认引号字符为双引号，因此您需要使用一个额外的双引号对每个双引号进行转义。您的新输入文件与下面类似。 ``` 12,Shows,Musicals,Musical theatre 13,Shows,Plays,"All ""non-musical"" theatre" 14,Shows,Opera,"All opera, light, and ""rock"" opera" 15,Concerts,Classical,"All symphony, concerto, and choir concerts" ``` 假定文件名为 `category_csv.txt`，则可通过使用以下 COPY 命令加载文件： ``` copy category from 's3://amzn-s3-demo-bucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv; ``` 或者，若要避免对输入中的双引号进行转义，可通过使用 QUOTE AS 参数来指定其他引号字符。例如，以下版本的 `category_csv.txt` 使用“`%`”作为引号字符。 ``` 12,Shows,Musicals,Musical theatre 13,Shows,Plays,%All "non-musical" theatre% 14,Shows,Opera,%All opera, light, and "rock" opera% 15,Concerts,Classical,%All symphony, concerto, and choir concerts% ``` 以下 COPY 命令使用 QUOTE AS 来加载 `category_csv.txt`： ``` copy category from 's3://amzn-s3-demo-bucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv quote as '%'; ``` ## 加载具有显式的 IDENTITY 列值的 VENUE 以下示例假设在创建 VENUE 表时，至少将一个列（如 `venueid` 列）指定为 IDENTITY 列。此命令将覆盖 IDENTITY 列的自动生成值的默认 IDENTITY 行为，并将改为从 venue.txt 文件加载显式值。使用 EXLICIT\$1IDS 选项时，Amazon Redshift 不会检查表中是否加载了重复的 IDENTITY 值。 ``` copy venue from 's3://amzn-s3-demo-bucket/data/venue.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' explicit_ids; ``` ## 从以竖线分隔的 GZIP 文件中加载 TIME 以下示例从用竖线分隔的 GZIP 文件加载 TIME 表： ``` copy time from 's3://amzn-s3-demo-bucket/data/timerows.gz' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' gzip delimiter '|'; ``` ## 加载时间戳或日期戳 以下示例加载具有带格式的时间戳的数据。 **注意** `HH:MI:SS` 的 TIMEFORMAT 还可支持超出 `SS` 的高达微秒细节级别的小数秒。此示例中使用的文件 `time.txt` 包含一行，即 `2009-01-12 14:15:57.119568`。 ``` copy timestamp1 from 's3://amzn-s3-demo-bucket/data/time.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' timeformat 'YYYY-MM-DD HH:MI:SS'; ``` 此复制的结果如下所示： ``` select * from timestamp1; c1 ---------------------------- 2009-01-12 14:15:57.119568 (1 row) ``` ## 从具有默认值的文件中加载数据 以下示例使用 TICKIT 数据库中的 VENUE 表的变体。考虑使用以下语句定义的 VENUE\$1NEW 表： ``` create table venue_new( venueid smallint not null, venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000'); ``` 考虑未包含任何 VENUESEATS 列值的 venue\$1noseats.txt 数据文件，如以下示例中所示： ``` 1|Toyota Park|Bridgeview|IL| 2|Columbus Crew Stadium|Columbus|OH| 3|RFK Stadium|Washington|DC| 4|CommunityAmerica Ballpark|Kansas City|KS| 5|Gillette Stadium|Foxborough|MA| 6|New York Giants Stadium|East Rutherford|NJ| 7|BMO Field|Toronto|ON| 8|The Home Depot Center|Carson|CA| 9|Dick's Sporting Goods Park|Commerce City|CO| 10|Pizza Hut Park|Frisco|TX| ``` 以下 COPY 语句将成功地从此文件中加载表并对已省略的列应用 DEFAULT 值（“1000”）： ``` copy venue_new(venueid, venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_noseats.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|'; ``` 现在查看加载的表： ``` select * from venue_new order by venueid; venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 1000 2 | Columbus Crew Stadium | Columbus | OH | 1000 3 | RFK Stadium | Washington | DC | 1000 4 | CommunityAmerica Ballpark | Kansas City | KS | 1000 5 | Gillette Stadium | Foxborough | MA | 1000 6 | New York Giants Stadium | East Rutherford | NJ | 1000 7 | BMO Field | Toronto | ON | 1000 8 | The Home Depot Center | Carson | CA | 1000 9 | Dick's Sporting Goods Park | Commerce City | CO | 1000 10 | Pizza Hut Park | Frisco | TX | 1000 (10 rows) ``` 在以下示例中，除了假设此文件中未包含任何 VENUESEATS 数据之外，还假设未包含任何 VENUENAME 数据： ``` 1||Bridgeview|IL| 2||Columbus|OH| 3||Washington|DC| 4||Kansas City|KS| 5||Foxborough|MA| 6||East Rutherford|NJ| 7||Toronto|ON| 8||Carson|CA| 9||Commerce City|CO| 10||Frisco|TX| ``` 通过使用相同的表定义，以下 COPY 语句失败，因为未为 VENUENAME 指定任何 DEFAULT 值，并且 VENUENAME 是一个非 NULL 列： ``` copy venue(venueid, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|'; ``` 现在考虑使用 IDENTITY 列的 VENUE 表的变体： ``` create table venue_identity( venueid int identity(1,1), venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000'); ``` 与上例一样，假设 VENUESEATS 列没有源文件中的对应值。以下 COPY 语句成功地加载表（包括预先定义的 IDENTITY 数据值）而不是自动生成这些值： ``` copy venue(venueid, venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids; ``` 此语句将失败，因为它未包含 IDENTITY 列（列列表中缺少 VENUEID），而是包含 EXPLICIT\$1IDS 参数： ``` copy venue(venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids; ``` 此语句将失败，因为它不包含 EXPLICIT\$1IDS 参数： ``` copy venue(venueid, venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|'; ``` ## 使用 ESCAPE 选项复制数据 以下示例演示如何加载与分隔符字符（在此示例中为竖线字符）匹配的字符。在输入文件中，确保使用反斜杠字符 (\$1) 转义您要加载的所有竖线字符 (\$1)。然后使用 ESCAPE 参数加载此文件。 ``` $ more redshiftinfo.txt 1|public\|event\|dwuser 2|public\|sales\|dwuser create table redshiftinfo(infoid int,tableinfo varchar(50)); copy redshiftinfo from 's3://amzn-s3-demo-bucket/data/redshiftinfo.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' escape; select * from redshiftinfo order by 1; infoid | tableinfo -------+-------------------- 1 | public|event|dwuser 2 | public|sales|dwuser (2 rows) ``` 如果没有 ESCAPE 参数，此 COPY 命令将失败，并返回 `Extra column(s) found` 错误。 **重要** 如果使用包含 ESCAPE 参数的 COPY 加载数据，则还必须在 UNLOAD 命令中指定 ESCAPE 参数与以生成反向输出文件。同样，如果您使用 ESCAPE 参数执行 UNLOAD 命令，则在您对相同数据执行 COPY 操作时需要使用 ESCAPE 参数。 ## 从 JSON 中复制的示例 在以下示例中，您加载具有以下数据的 CATEGORY 表。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_COPY_command_examples.html) **Topics** + [使用“auto”选项从 JSON 数据中加载](#copy-from-json-examples-using-auto) + [使用“auto ignorecase”选项从 JSON 数据中加载](#copy-from-json-examples-using-auto-ignorecase) + [使用 JSONPaths 文件从 JSON 数据中加载](#copy-from-json-examples-using-jsonpaths) + [使用 JSONPaths 文件从 JSON 数组中加载](#copy-from-json-examples-using-jsonpaths-arrays) ### 使用“auto”选项从 JSON 数据中加载 要使用 `'auto'` 选项从 JSON 数据加载，JSON 数据必须包含一组对象。键名称必须与列名称匹配，但顺序并不重要。下面显示了名为 `category_object_auto.json` 的文件的内容。 ``` { "catdesc": "Major League Baseball", "catid": 1, "catgroup": "Sports", "catname": "MLB" } { "catgroup": "Sports", "catid": 2, "catname": "NHL", "catdesc": "National Hockey League" } { "catid": 3, "catname": "NFL", "catgroup": "Sports", "catdesc": "National Football League" } { "bogus": "Bogus Sports LLC", "catid": 4, "catgroup": "Sports", "catname": "NBA", "catdesc": "National Basketball Association" } { "catid": 5, "catgroup": "Shows", "catname": "Musicals", "catdesc": "All symphony, concerto, and choir concerts" } ``` 若要从上例中的 JSON 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_object_auto.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto'; ``` ### 使用“auto ignorecase”选项从 JSON 数据中加载 要使用 `'auto ignorecase'` 选项从 JSON 数据加载，JSON 数据必须包含一组对象。键名称的大小写不必与列名称匹配，顺序并不重要。下面显示了名为 `category_object_auto-ignorecase.json` 的文件的内容。 ``` { "CatDesc": "Major League Baseball", "CatID": 1, "CatGroup": "Sports", "CatName": "MLB" } { "CatGroup": "Sports", "CatID": 2, "CatName": "NHL", "CatDesc": "National Hockey League" } { "CatID": 3, "CatName": "NFL", "CatGroup": "Sports", "CatDesc": "National Football League" } { "bogus": "Bogus Sports LLC", "CatID": 4, "CatGroup": "Sports", "CatName": "NBA", "CatDesc": "National Basketball Association" } { "CatID": 5, "CatGroup": "Shows", "CatName": "Musicals", "CatDesc": "All symphony, concerto, and choir concerts" } ``` 若要从上例中的 JSON 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_object_auto ignorecase.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto ignorecase'; ``` ### 使用 JSONPaths 文件从 JSON 数据中加载 如果 JSON 数据对象未直接对应于列名称，则可使用 JSONPaths 文件将 JSON 元素映射到列。顺序在 JSON 源数据中也不重要，但 JSONPaths 文件表达式的顺序必须与列顺序匹配。假设您具有以下名为 `category_object_paths.json` 的数据文件。 ``` { "one": 1, "two": "Sports", "three": "MLB", "four": "Major League Baseball" } { "three": "NHL", "four": "National Hockey League", "one": 2, "two": "Sports" } { "two": "Sports", "three": "NFL", "one": 3, "four": "National Football League" } { "one": 4, "two": "Sports", "three": "NBA", "four": "National Basketball Association" } { "one": 6, "two": "Shows", "three": "Musicals", "four": "All symphony, concerto, and choir concerts" } ``` 以下名为 `category_jsonpath.json` 的 JSONPaths 文件会将源数据映射到表列。 ``` { "jsonpaths": [ "$['one']", "$['two']", "$['three']", "$['four']" ] } ``` 若要从上例中的 JSON 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_object_paths.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/category_jsonpath.json'; ``` ### 使用 JSONPaths 文件从 JSON 数组中加载 若要从包含一组数组的 JSON 数据加载，必须使用 JSONPaths 文件将数组元素映射到列。假设您具有以下名为 `category_array_data.json` 的数据文件。 ``` [1,"Sports","MLB","Major League Baseball"] [2,"Sports","NHL","National Hockey League"] [3,"Sports","NFL","National Football League"] [4,"Sports","NBA","National Basketball Association"] [5,"Concerts","Classical","All symphony, concerto, and choir concerts"] ``` 以下名为 `category_array_jsonpath.json` 的 JSONPaths 文件会将源数据映射到表列。 ``` { "jsonpaths": [ "$[0]", "$[1]", "$[2]", "$[3]" ] } ``` 若要从上例中的 JSON 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_array_data.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/category_array_jsonpath.json'; ``` ## 从 Avro 中复制的示例 在以下示例中，您加载具有以下数据的 CATEGORY 表。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_COPY_command_examples.html) **Topics** + [使用“auto”选项从 Avro 数据中加载](#copy-from-avro-examples-using-auto) + [使用“auto ignorecase”选项从 Avro 数据中加载](#copy-from-avro-examples-using-auto-ignorecase) + [使用 JSONPaths 文件从 Avro 数据中加载](#copy-from-avro-examples-using-avropaths) ### 使用“auto”选项从 Avro 数据中加载 若要使用 `'auto'` 参数从 Avro 数据加载，Avro schema 中的字段名称必须与列名称匹配。在使用 `'auto'` 参数时，顺序并不重要。下面显示了名为 `category_auto.avro` 的文件的 schema。 ``` { "name": "category", "type": "record", "fields": [ {"name": "catid", "type": "int"}, {"name": "catdesc", "type": "string"}, {"name": "catname", "type": "string"}, {"name": "catgroup", "type": "string"}, } ``` Avro 文件中的数据为二进制格式，不是人类可读的格式。下面显示了 `category_auto.avro` 文件中的数据的 JSON 表示形式。 ``` { "catid": 1, "catdesc": "Major League Baseball", "catname": "MLB", "catgroup": "Sports" } { "catid": 2, "catdesc": "National Hockey League", "catname": "NHL", "catgroup": "Sports" } { "catid": 3, "catdesc": "National Basketball Association", "catname": "NBA", "catgroup": "Sports" } { "catid": 4, "catdesc": "All symphony, concerto, and choir concerts", "catname": "Classical", "catgroup": "Concerts" } ``` 要从上例中的 Avro 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_auto.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto'; ``` ### 使用“auto ignorecase”选项从 Avro 数据中加载 若要使用 `'auto ignorecase'` 参数从 Avro 数据加载，Avro schema 中的字段名称的大小写不必与列名称的大小写匹配。在使用 `'auto ignorecase'` 参数时，顺序并不重要。下面显示了名为 `category_auto-ignorecase.avro` 的文件的 schema。 ``` { "name": "category", "type": "record", "fields": [ {"name": "CatID", "type": "int"}, {"name": "CatDesc", "type": "string"}, {"name": "CatName", "type": "string"}, {"name": "CatGroup", "type": "string"}, } ``` Avro 文件中的数据为二进制格式，不是人类可读的格式。下面显示了 `category_auto-ignorecase.avro` 文件中的数据的 JSON 表示形式。 ``` { "CatID": 1, "CatDesc": "Major League Baseball", "CatName": "MLB", "CatGroup": "Sports" } { "CatID": 2, "CatDesc": "National Hockey League", "CatName": "NHL", "CatGroup": "Sports" } { "CatID": 3, "CatDesc": "National Basketball Association", "CatName": "NBA", "CatGroup": "Sports" } { "CatID": 4, "CatDesc": "All symphony, concerto, and choir concerts", "CatName": "Classical", "CatGroup": "Concerts" } ``` 要从上例中的 Avro 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_auto-ignorecase.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto ignorecase'; ``` ### 使用 JSONPaths 文件从 Avro 数据中加载 如果 Avro schema 中的字段名称未直接对应于列名称，则可使用 JSONPaths 文件将 schema 元素映射到列。JSONPaths 文件表达式的顺序必须与列顺序一致。 假设您具有名为 `category_paths.avro` 的数据文件，其中包含的数据与上例相同，但具有以下架构。 ``` { "name": "category", "type": "record", "fields": [ {"name": "id", "type": "int"}, {"name": "desc", "type": "string"}, {"name": "name", "type": "string"}, {"name": "group", "type": "string"}, {"name": "region", "type": "string"} ] } ``` 以下名为 `category_path.avropath` 的 JSONPaths 文件会将源数据映射到表列。 ``` { "jsonpaths": [ "$['id']", "$['group']", "$['name']", "$['desc']" ] } ``` 要从上例中的 Avro 数据文件加载，请执行以下 COPY 命令。 ``` copy category from 's3://amzn-s3-demo-bucket/category_object_paths.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format avro 's3://amzn-s3-demo-bucket/category_path.avropath '; ``` ## 使用 ESCAPE 选项为 COPY 准备文件 以下示例描述了在使用包含 ESCAPE 参数的 COPY 命令将数据导入到 Amazon Redshift 表中之前，如何准备数据以“转义”换行符。如果未准备数据以限定换行符，则 Amazon Redshift 将会在您运行 COPY 命令时返回加载错误，因为换行符一般用作记录分隔符。 例如，考虑要复制到 Amazon Redshift 表中的一个文件或外部表中的一个列。如果该文件或列包含 XML 格式的内容或类似数据，则需要确保使用反斜杠字符 (\$1) 转义此内容中的所有换行符 (\$1n)。 包含嵌入换行符的文件或表提供了相对轻松的匹配模式。每个嵌入的换行符很有可能始终跟随一个 `>` 字符（在这二者之间可能还包含一些空格字符（`' '` 或制表符）），如下面的名为 `nlTest1.txt` 的文本文件的示例中所示。 ``` $ cat nlTest1.txt |1000 |2000 ``` 在以下示例中，您可运行文本处理实用工具预先处理源文件，并在需要的位置插入转义字符。（`|` 字符旨在用作分隔符，以便在列数据复制到 Amazon Redshift 表中后分隔这些数据。） ``` $ sed -e ':a;N;$!ba;s/>[[:space:]]*\n/>\\\n/g' nlTest1.txt > nlTest2.txt ``` 同样，可使用 Perl 执行类似操作： ``` cat nlTest1.txt | perl -p -e 's/>\s*\n/>\\\n/g' > nlTest2.txt ``` 为了便于将 `nlTest2.txt` 文件中的数据加载到 Amazon Redshift 中，我们在 Amazon Redshift 中创建了一个包含两列的表。第一列 c1 是字符列，用于放置 `nlTest2.txt` 文件中 XML 格式的内容。第二列 c2 将放置从同一文件加载的整数值。 在运行 `sed` 命令后，可使用 ESCAPE 参数将 `nlTest2.txt` 文件中的数据正确地加载到 Amazon Redshift 表中。 **注意** 如果您在 COPY 命令中包含 ESCAPE 参数，则它会将一些包含反斜杠字符的特殊字符（包括换行符）进行转义。 ``` copy t2 from 's3://amzn-s3-demo-bucket/data/nlTest2.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' escape delimiter as '|'; select * from t2 order by 2; c1 | c2 -------------+------ | 1000 | 2000 (2 rows) ``` 您可以类似方式准备从外部数据库导出的数据文件。例如，对于 Oracle 数据库，可对要复制到 Amazon Redshift 中的表中的每个受影响的列使用 REPLACE 函数。 ``` SELECT c1, REPLACE(c2, \n',\\n' ) as c2 from my_table_with_xml ``` 此外，许多用于定期处理大量数据的数据库导出和提取、转换、加载 (ETL) 工具提供了指定转义字符和分隔符字符的选项。 ## 将 shapefile 加载到 Amazon Redshift 以下示例演示如何使用 COPY 加载 Esri shapefile。有关加载 shapefile 的更多信息，请参阅[将 shapefile 加载到 Amazon Redshift](spatial-copy-shapefile.md)。 ### 加载一个 shapefile 以下步骤介绍如何使用 COPY 命令从 Amazon S3 中摄取 OpenStreetMap 数据。此示例假定 [Geofabrik 下载站点的](https://download.geofabrik.de/europe.html) Norway shapefile 归档已经上载到 AWS 区域中的私有 Amazon S3 桶。`.shp`、`.shx` 和 `.dbf` 文件必须共享相同的 Amazon S3 前缀和文件名。 #### 无需简化即可摄取数据 以下命令可创建表并摄取可适合最大几何大小的数据，而无需进行任何简化。在您的首选 GIS 软件中打开 `gis_osm_natural_free_1.shp`，然后检查此图层中的列。预设情况下，IDENTITY 或 GEOMETRY 列位于首位。当 GEOMETRY 列位于首位时，您可以创建表，如下所示。 ``` CREATE TABLE norway_natural ( wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR); ``` 或者，当 IDENTITY 列位于首位时，您可以创建表，如下所示。 ``` CREATE TABLE norway_natural_with_id ( fid INT IDENTITY(1,1), wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR); ``` 现在，您可以使用 COPY 摄取数据。 ``` COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully ``` 或者，您可以按如下所示摄取数据。 ``` COPY norway_natural_with_id FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural_with_id' completed, 83891 record(s) loaded successfully. ``` #### 通过简化摄取数据 以下命令创建一个表，并尝试在不进行任何简化的情况下摄取无法适合最大几何大小的数据。检查 `gis_osm_water_a_free_1.shp` shapefile 并创建相应的表，如下所示。 ``` CREATE TABLE norway_water ( wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR); ``` 当 COPY 命令运行时，会导致错误。 ``` COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; ERROR: Load into table 'norway_water' failed. Check 'stl_load_errors' system table for details. ``` 查询 `STL_LOAD_ERRORS` 显示几何体过大。 ``` SELECT line_number, btrim(colname), btrim(err_reason) FROM stl_load_errors WHERE query = pg_last_copy_id(); line_number | btrim | btrim -------------+--------------+----------------------------------------------------------------------- 1184705 | wkb_geometry | Geometry size: 1513736 is larger than maximum supported size: 1048447 ``` 为了克服这个问题，将 `SIMPLIFY AUTO` 参数添加到 COPY 命令中以简化几何体。 ``` COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989196 record(s) loaded successfully. ``` 要查看简化的行和几何体，请查询 `SVL_SPATIAL_SIMPLIFY`。 ``` SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+---------------------- 20 | 1184704 | -1 | 1513736 | t | 1008808 | 1.276386653895e-05 20 | 1664115 | -1 | 1233456 | t | 1023584 | 6.11707814796635e-06 ``` 使用容差低于自动计算容差的 SIMPLIFY AUTO *max\$1tolerance* 可能会导致摄入误差。在这种情况下，请使用 MAXERROR 忽略错误。 ``` COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO 1.1E-05 MAXERROR 2 CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989195 record(s) loaded successfully. INFO: Load into table 'norway_water' completed, 1 record(s) could not be loaded. Check 'stl_load_errors' system table for details. ``` 再次查询 `SVL_SPATIAL_SIMPLIFY` 来识别 COPY 未成功加载的记录。 ``` SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+----------------- 29 | 1184704 | 1.1e-05 | 1513736 | f | 0 | 0 29 | 1664115 | 1.1e-05 | 1233456 | t | 794432 | 1.1e-05 ``` 在这个示例中，第一条记录没有成功适应，因此 `simplified` 列显示为 false。第二条记录已在给定容差范围内加载。但是，最终大小比使用自动计算的容差大，而不指定最大公差。 ### 正在从压缩的 shapefile 文件加载 Amazon Redshift COPY 支持从压缩的 shapefile 中摄取数据。所有 shapefile 组件必须具有相同的 Amazon S3 前缀和相同的压缩后缀。例如，假设您要加载上面示例中的数据。在本例中，文件 `gis_osm_water_a_free_1.shp.gz`、`gis_osm_water_a_free_1.dbf.gz` 和 `gis_osm_water_a_free_1.shx.gz` 必须共享相同的 Amazon S3 目录。COPY 命令需要 GZIP 选项，FROM 子句必须指定正确的压缩文件，如下所示。 ``` COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/compressed/gis_osm_natural_free_1.shp.gz' FORMAT SHAPEFILE GZIP CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully. ``` ### 正在将数据加载到具有不同列顺序的表 如果您有一个没有将 `GEOMETRY` 作为第一列的表，则可以使用列映射将列映射到目标表。例如，创建一个将 `osm_id` 指定为第一列的表。 ``` CREATE TABLE norway_natural_order ( osm_id BIGINT, wkb_geometry GEOMETRY, code INT, fclass VARCHAR, name VARCHAR); ``` 然后使用列映射摄取 shapefile。 ``` COPY norway_natural_order(wkb_geometry, osm_id, code, fclass, name) FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural_order' completed, 83891 record(s) loaded successfully. ``` ### 将数据加载到具有 geography 列的表中 如果某个表具有 `GEOGRAPHY` 列，请首先提取到 `GEOMETRY` 列，然后将对象强制转换为 `GEOGRAPHY` 对象。例如，在将 shapefile 复制到 `GEOMETRY` 列后，对表进行更改，添加 `GEOGRAPHY` 数据类型。 ``` ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY; ``` 然后将 geometry 转换为 geography。 ``` UPDATE norway_natural SET wkb_geography = wkb_geometry::geography; ``` （可选）您可以删除 `GEOMETRY` 列。 ``` ALTER TABLE norway_natural DROP COLUMN wkb_geometry; ``` ## 带有 NOLOAD 选项的 COPY 命令 要在实际加载数据之前验证数据文件，请使用带有 NOLOAD 选项的 COPY 命令。Amazon Redshift 会解析输入文件并显示发生的任何错误。以下示例使用 NOLOAD 选项，实际上并未将任何行加载到表中。 ``` COPY public.zipcode1 FROM 's3://amzn-s3-demo-bucket/mydata/zipcode.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'us-east-1' NOLOAD CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/myRedshiftRole'; Warnings: Load into table 'zipcode1' completed, 0 record(s) loaded successfully. ``` ## 带有多字节分隔符和 ENCODING 选项的 COPY 命令 以下示例从包含多字节数据的 Amazon S3 文件中加载 LATIN1。COPY 命令以八进制形式 `\302\246\303\254` 指定分隔符，来分隔编码为 ISO-8859-1 的输入文件中的字段。要以 UTF-8 形式指定相同的分隔符，请指定 `DELIMITER '¦ì'`。 ``` COPY latin1 FROM 's3://amzn-s3-demo-bucket/multibyte/myfile' IAM_ROLE 'arn:aws:iam::123456789012:role/myRedshiftRole' DELIMITER '\302\246\303\254' ENCODING ISO88591 ```