COPY 示例 - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

如果我们为英文版本指南提供翻译,那么如果存在任何冲突,将以英文版本指南为准。在提供翻译时使用机器翻译。

COPY 示例

注意

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

从 DynamoDB 表中加载 FAVORITEMOVIES

AWS 开发工具包包含一个创建名为 Movies 的 DynamoDB 表的简单示例。(对于此示例,请参阅 DynamoDB入门指南.)以下示例加载 Amazon Redshift 移动表格和来自 DynamoDB 表。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://mybucket/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://mybucket/jsonpaths.txt';

使用清单指定数据文件

您可以使用清单确保 COPY 命令将从 Amazon S3 加载所有必需的文件,而且仅加载必需的文件。当您需要从不同的存储桶加载多个文件或加载未共享相同前缀的文件时,您也可使用清单。

例如,假设您需要加载以下三个文件: custdata1.txt, custdata2.txt、和 custdata3.txt。您可以使用下列命令将所有文件加载至 mybucketcustdata 通过指定一个前缀:

copy category from 's3://mybucket/custdata' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

如果由于错误仅存在两个文件,则 COPY 仅加载这两个文件并成功完成,从而导致不完整的数据加载。如果存储桶还包含恰巧使用相同前缀的不需要的文件(例如名为 custdata.backup 的文件),则 COPY 还加载此文件,从而导致加载不需要的数据。

为了确保加载所有必需的文件并防止加载不需要的文件,您可使用清单文件。清单是 JSON 格式的文本文件,其中列出了要通过 COPY 命令处理的文件。例如,以下清单将加载上例中的三个文件。

{ "entries":[ { "url":"s3://mybucket/custdata.1", "mandatory":true }, { "url":"s3://mybucket/custdata.2", "mandatory":true }, { "url":"s3://mybucket/custdata.3", "mandatory":true } ] }

可选的 mandatory 标志指示 COPY 是否应在文件不存在时终止。默认值为 false。无论任何强制设置,如果未找到文件,则COPY终止。在此示例中,如果未找到任何文件,COPY 将返回错误。将忽略可能会在仅指定键前缀(如 custdata.backup)的情况下选取的不需要的文件,因为它们不在清单上。

在从采用 ORC 或 Parquet 格式的数据文件中加载时,需要 meta 字段,如以下示例所示。

{ "entries":[ { "url":"s3://mybucket-alpha/orc/2013-10-04-custdata", "mandatory":true, "meta":{ "content_length":99 } }, { "url":"s3://mybucket-beta/orc/2013-10-05-custdata", "mandatory":true, "meta":{ "content_length":99 } } ] }

以下示例使用名为 cust.manifest 的清单。

copy customer from 's3://mybucket/cust.manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

您可以使用清单来加载不同存储桶或文件中未共享相同前缀的文件。以下示例显示了用于加载名称以日期戳开头的文件中的数据的 JSON。

{ "entries": [ {"url":"s3://mybucket/2013-10-04-custdata.txt","mandatory":true}, {"url":"s3://mybucket/2013-10-05-custdata.txt”,"mandatory":true}, {"url":"s3://mybucket/2013-10-06-custdata.txt”,"mandatory":true}, {"url":"s3://mybucket/2013-10-07-custdata.txt”,"mandatory":true} ] }

此清单可列出位于不同存储桶中的文件,前提是存储桶与集群位于同一 AWS 区域。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata1.txt","mandatory":false}, {"url":"s3://mybucket-beta/custdata1.txt","mandatory":false}, {"url":"s3://mybucket-beta/custdata2.txt","mandatory":false} ] }

从以竖线(默认分隔符)分隔的文件中加载 LISTING

以下示例是一个非常简单的示例,其中未指定任何选项并且输入文件包含默认分隔符,即竖线字符(“|”)。

copy listing from 's3://mybucket/data/listings_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

使用 Parquet 格式的列式数据加载 LISTING

以下示例从 Amazon S3 上的名为 parquet 的文件夹加载数据。

copy listing from 's3://mybucket/data/listings/parquet/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as parquet;

使用临时凭证加载 LISTING

以下示例使用 SESSION_TOKEN 参数指定临时会话凭证:

copy listing from 's3://mybucket/data/listings_pipe.txt' access_key_id '<access-key-id>' secret_access_key '<secret-access-key' session_token '<temporary-token>';

使用选项加载 EVENT

以下示例将竖线分隔的数据加载到 EVENT 表中并应用下列规则:

  • 如果使用了引号对来括起任何字符串,则会删除它们。

  • 空字符串和包含空白的字符串将作为 NULL 值加载。

  • 如果返回了 5 个以上的错误,则加载失败。

  • 时间戳值必须遵循指定的格式;例如,有效的时间戳为 2008-09-26 05:43:12

copy event from 's3://mybucket/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://mybucket/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。

catid catgroup catname catdesc
[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

以下示例显示了字段值用逗号隔开的文本文件的内容。

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://mybucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv;

或者,为了避免在您的输入中避开双引号,您可以使用QUOTEAS参数指定不同的引号字符。例如,以下版本的 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://mybucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv quote as '%';

加载具有显式的 IDENTITY 列值的 VENUE

以下示例假设在创建 VENUE 表时,至少将一个列(如 venueid 列)指定为 IDENTITY 列。此命令将覆盖 IDENTITY 列的自动生成值的默认 IDENTITY 行为,并将改为从 venue.txt 文件加载显式值。

copy venue from 's3://mybucket/data/venue.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' explicit_ids;

从以竖线分隔的 GZIP 文件中加载 TIME

以下示例从用竖线分隔的 GZIP 文件加载 TIME 表:

copy time from 's3://mybucket/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://mybucket/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_NEW 表:

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_noseats.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://mybucket/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://mybucket/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://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids;

此语句将失败,因为它未包含 IDENTITY 列(列列表中缺少 VENUEID),而是包含 EXPLICIT_IDS 参数:

copy venue(venuename, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids;

此语句将失败,因为它不包含 EXPLICIT_IDS 参数:

copy venue(venueid, venuename, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

使用 ESCAPE 选项复制数据

以下示例演示如何加载与分隔符字符(在此示例中为竖线字符)匹配的字符。在输入文件中,确保使用反斜杠字符 (\) 转义您要加载的所有竖线字符 (|)。然后使用 ESCAPE 参数加载此文件。

$ more redshiftinfo.txt 1|public\|event\|dwuser 2|public\|sales\|dwuser create table redshiftinfo(infoid int,tableinfo varchar(50)); copy redshiftinfo from 's3://mybucket/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 表。

CATID CATGROUP CATNAME CATDESC
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

使用“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://mybucket/category_object_auto.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto';

使用 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://mybucket/category_object_paths.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://mybucket/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://mybucket/category_array_data.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://mybucket/category_array_jsonpath.json';

从 Avro 中复制的示例

在以下示例中,您加载具有以下数据的 CATEGORY 表。

CATID CATGROUP CATNAME CATDESC
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

使用“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://mybucket/category_auto.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto';

使用 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://mybucket/category_object_paths.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format avro 's3://mybucket/category_path.avropath ';

使用 ESCAPE 选项为 COPY 准备文件

以下示例描述了在使用包含 ESCAPE 参数的 COPY 命令将数据导入到 Amazon Redshift 表中之前,如何准备数据以“转义”换行符。如果未准备数据以限定换行符,则 Amazon Redshift 将会在您运行 COPY 命令时返回加载错误,因为换行符一般用作记录分隔符。

例如,考虑要复制到 Amazon Redshift 表中的一个文件或外部表中的一个列。如果该文件或列包含 XML 格式的内容或类似数据,则需要确保使用反斜杠字符 (\) 转义此内容中的所有换行符 (\n)。

包含嵌入换行符的文件或表的一个好处是,它提供了相对轻松的匹配模式。每个嵌入的换行符很有可能始终跟随一个 > 字符(在这二者之间可能还包含一些空格字符(' ' 或制表符)),如下面的名为 nlTest1.txt 的文本文件的示例中所示。

$ cat nlTest1.txt <xml start> <newline characters provide> <line breaks at the end of each> <line in content> </xml>|1000 <xml> </xml>|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://mybucket/data/nlTest2.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' escape delimiter as '|'; select * from t2 order by 2; c1 | c2 -------------+------ <xml start> <newline characters provide> <line breaks at the end of each> <line in content> </xml> | 1000 <xml> </xml> | 2000 (2 rows)

您可以类似方式准备从外部数据库导出的数据文件。例如,对于 Oracle 数据库,可对要复制到 Amazon Redshift 中的表中的每个受影响的列使用 REPLACE 函数。

SELECT c1, REPLACE(c2, \n',\\n' ) as c2 from my_table_with_xml

此外,许多用于定期处理大量数据的数据库导出和提取、转换、加载 (ETL) 工具提供了指定转义字符和分隔符字符的选项。

将形状文件加载到 Amazon Redshift

以下示例说明了如何使用COPY加载Esri形状文件。有关加载形状文件的更多信息,请参阅 将形状文件加载到 Amazon Redshift.

加载形状文件

以下步骤展示如何从以下位置摄取OpenStret地图数据 Amazon S3 使用COPY命令。此示例假设挪威shapefile存档来自 Geofabrik的下载站点 已上传到私人 Amazon S3 的时间间隔。的 .shp, .shx、和 .dbf 文件必须共享相同的 Amazon S3 前缀和文件名。

不简化就拷贝数据

下列命令可创建表格和拷贝数据,使其符合最大几何尺寸,无需任何简化。打开 gis_osm_natural_free_1.shp 并检查此层中的列。 默认情况下,标识或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

使用模拟自动 max_容差 容差低于自动计算的容差可能会导致摄入错误。在这种情况下,使用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 列显示错误。加载的第二条记录在给定的容差范围内。但是,最终尺寸大于使用自动计算的容差,而不指定最大容差。

从压缩形状文件加载

Amazon Redshift COPY支持从压缩形状文件摄取数据。所有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.