Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

数据格式参数

默认情况下,COPY 命令要求源数据是字符分隔的 UTF-8 文本。默认分隔符是竖线字符 (|)。如果源数据采用的是其他格式,请使用以下参数指定数据格式。

数据格式参数

FORMAT [AS]

可选。标识数据格式关键字。FORMAT 参数如下所述。

CSV [ QUOTE [AS] 'quote_character' ]

支持在输入数据中使用 CSV 格式。要自动对分隔符、换行符和回车符进行转义,可用 QUOTE 参数指定的字符将字段括起来。默认引号字符是双引号 (")。当在字段中使用了引号字符时,应使用另一个引号字符对其进行转义。例如,如果引号字符为双引号,那么要插入字符串 A "quoted" word,输入文件应包含字符串 "A ""quoted"" word"。当使用了 CSV 参数时,默认分隔符为逗号 (,)。您可使用 DELIMITER 参数指定一个不同的分隔符。

当某个字段用引号括起来时,分隔符和引号字符之间的空格将被忽略。如果分隔符为空格字符(如制表符),则分隔符不会被视为空格。

CSV 不能与 FIXEDWIDTH、REMOVEQUOTES 或 ESCAPE 一起使用。

QUOTE [AS] 'quote_character'

可选。指定在使用 CSV 参数时要用作引号字符的字符。默认值为双引号 (")。如果您使用 QUOTE 参数定义双引号以外的引号字符,则不需要对字段中的双引号进行转义。QUOTE 参数只能与 CSV 参数一起使用。AS 关键字是可选的。

DELIMITER [AS] ['delimiter_char']

指定用于在输入文件中分隔字段的单个 ASCII 字符,如竖线字符 (|)、逗号 (,) 或制表符 (\t)。支持非打印 ASCII 字符。ASCII 字符还可以用八进制形式表示,使用格式“\ddd”,其中“d”是八进制数字 (0–7)。默认分隔符是竖线字符 (|),除非使用了 CSV 参数,在这种情况下,默认分隔符是逗号 (,)。AS 关键字是可选的。DELIMITER 不能与 FIXEDWIDTH 一起使用。

FIXEDWIDTH 'fixedwidth_spec'

从一个文件中加载数据,该文件中的每个列是宽度固定的列,而不是由分隔符分隔的列。fixedwidth_spec 是用于指定用户定义的列标签和列宽度的字符串。列标签可以是文本字符串或整数,具体取决于用户的选择。列标签与列名称没有关联。标签/宽度对的顺序必须与表列的顺序完全一致。FIXEDWIDTH 不能与 CSV 或 DELIMITER 一起使用。在 Amazon Redshift 中,CHAR 和 VARCHAR 列的长度以字节表示,因此在准备要加载的文件时,请确保您指定的列宽度可容纳多字节字符的二进制长度。有关更多信息,请参阅 字符类型

fixedwidth_spec 的格式如下所示:

Copy
'colLabel1:colWidth1,colLabel:colWidth2, ...'
AVRO [AS] 'avro_option'

指定源数据采用 Avro 格式。

从以下服务和协议执行 COPY 的操作支持 Avro 格式:

  • Amazon S3

  • Amazon EMR

  • 远程主机 (SSH)

从 DynamoDB 执行 COPY 的操作不支持 Avro。

Avro 是一个数据序列化协议。Avro 源文件包含一个定义数据结构的 schema。Avro schema 类型必须为 record。COPY 接受使用默认的非压缩编解码器及 deflatesnappy 压缩编解码器创建的 Avro 文件。有关 Avro 的更多信息,请转到 Apache Avro

avro_option 的有效值如下:

  • 'auto'

  • 's3://jsonpaths_file'

默认为 'auto'

'auto'

COPY 会通过以下方法将 Avro 源数据中的数据元素自动映射到目标表中的列:将 Avro schema 中的字段名称与目标表中的列名称匹配。匹配区分大小写。Amazon Redshift 表中的列名称始终小写,因此,当您使用“auto”选项时,匹配字段名称也必须为小写。如果字段名称包含大写字符,则您可以使用 JSONPaths file 将列名称显式地映射到 Avro 字段名称。由于存在默认的 'auto' 参数,COPY 只会识别结构中的第一层字段或外部字段

默认情况下,COPY 会尝试将目标表中的所有列与 Avro 字段名称匹配。要加载列的子集,您可以选择性地指定包含列的列表。

如果列列表省略了目标表中的列,则 COPY 将加载目标列的 DEFAULT 表达式。如果目标列没有默认值,则 COPY 将尝试加载 NULL。

如果某个列包含在列列表中,并且 COPY 在 Avro 数据中找不到匹配的字段,则 COPY 会尝试将 NULL 加载到该列中。

如果 COPY 尝试将 NULL 分配到一个定义为 NOT NULL 的列,COPY 命令将失败。

's3://jsonpaths_file'

要将 Avro 数据元素显式映射到列,您可以使用 JSONPaths 文件。有关使用 JSONPaths 文件映射 Avro 数据的更多信息,请参阅 JSONPaths file

Avro Schema

Avro 源数据文件包含一个定义数据结构的 Schema。COPY 将读取作为 Avro 源数据文件的一部分的 schema 以将数据元素映射到目标表列。以下示例显示了一个 Avro schema。

Copy
{ "name": "person", "type": "record", "fields": [ {"name": "id", "type": "int"}, {"name": "guid", "type": "string"}, {"name": "name", "type": "string"}, {"name": "address", "type": "string"} }

Avro schema 是使用 JSON 格式定义的。顶级 JSON 对象包含三个名称/值对,这三个名称(即)分别为 "name""type""fields"

"fields" 键与定义数据结构中每个字段的名称和数据类型的对象数组配对。默认情况下,COPY 会自动将字段名称与列名称匹配。列名称始终为小写形式,因此匹配的字段名称也必须为小写形式。与列名称不匹配的任何字段名称都将被忽略。顺序不重要。在上述示例中,COPY 将映射到列名称 idguidnameaddress

由于存在默认的 'auto' 参数,COPY 只会将第一层对象映射到列。若要映射到 schema 中的更深层次,或者如果字段名称与列名称不匹配,请使用 JSONPaths 文件定义映射。有关更多信息,请参阅 JSONPaths file

如果与键关联的值是一个复杂的 Avro 数据类型(如字节、数组、记录、映射或链接),COPY 会将该值作为一个字符串加载,这里的字符串是数据的 JSON 表示形式。COPY 会将 Avro 枚举数据类型作为字符串加载,其中的内容是类型的名称。有关示例,请参阅从 JSON 格式数据执行的 COPY 操作

Avro 文件标头(包括 schema 和文件元数据)的最大大小为 1 MB。 

单个 Avro 数据块的最大大小为 4 MB。这与最大行大小不同。如果超过了单个 Avro 数据块的最大大小,则即使生成的行大小未达到 4 MB 的行大小限制,COPY 命令也会失败。

在计算行大小时,Amazon Redshift 在内部对竖线字符 ( | ) 计为两个字符。如果您的输入数据中包含大量竖线字符,则即使数据块小于 4 MB,行大小也可能超过 4 MB。

JSON [AS] 'json_option'

源数据采用 JSON 格式。

从以下服务和协议执行 COPY 的操作支持 JSON 格式:

  • Amazon S3

  • 从 Amazon EMR 执行 COPY 的操作

  • 从 SSH 执行 COPY 的操作

从 DynamoDB 执行 COPY 的操作不支持 JSON。

json_option 的有效值如下:

  • 'auto'

  • 's3://jsonpaths_file'

默认为 'auto'

'auto'

COPY 通过以下方法将 JSON 源数据中的数据元素映射到目标表中的列:将源名称/值对中的对象键(即名称)映射到目标表中的列的名称。匹配区分大小写。Amazon Redshift 表中的列名称始终小写,因此,当您使用“auto”选项时,匹配的 JSON 字段名称也必须为小写。如果 JSON 字段名称键包含大写字符,则您可以使用 JSONPaths file 将列名称显式地映射到 JSON 字段名称键。

默认情况下,COPY 会尝试将目标表中的所有列与 JSON 字段名称键匹配。要加载列的子集,您可以选择性地指定包含列的列表。

如果列列表省略了目标表中的列,则 COPY 将加载目标列的 DEFAULT 表达式。如果目标列没有默认值,则 COPY 将尝试加载 NULL。

如果某个列包含在列列表中,并且 COPY 在 JSON 数据中找不到匹配的字段,则 COPY 会尝试将 NULL 加载到该列。

如果 COPY 尝试将 NULL 分配到一个定义为 NOT NULL 的列,COPY 命令将失败。

's3://jsonpaths_file'

COPY 使用命名的 JSONPaths 文件将 JSON 源数据中的数据元素映射到目标表中的列。s3://jsonpaths_file 参数必须是显式引用单个文件的 Amazon S3 对象键,例如 's3://mybucket/jsonpaths.txt';而不能是键前缀。有关使用 JSONPaths 文件的更多信息,请参阅 JSONPaths file

注意

如果 jsonpaths_file 指定的文件与数据文件的 copy_from_s3_objectpath 指定的路径具有相同的前缀,则 COPY 会将 JSONPaths 文件作为数据文件读取并返回错误。例如,如果数据文件使用了对象路径 s3://mybucket/my_data.json,而 JSONPaths 文件是 s3://mybucket/my_data.jsonpaths,则 COPY 会尝试将 my_data.jsonpaths 作为数据文件加载。

JSON 数据文件

JSON 数据文件包含一组对象或数组。COPY 会将每个 JSON 对象或数组加载到目标表中的一行中。与某个行对应的每个对象或数组都必须是独立的根级结构;即,它不能是另一个 JSON 结构的成员。

JSON 对象 以大括号 ({}) 开头和结尾,并包含名称/值对的无序集合。每个成对的名称和值由冒号分隔,而每个名称/值对由逗号分隔。默认情况下,名称/值对中的对象键(即名称)必须与表中的对应列的名称匹配。Amazon Redshift 表中的列名称始终小写,因此,匹配的 JSON 字段名称键也必须为小写。如果您的列名称与 JSON 键不匹配,请使用 JSONPaths file 将列显式映射到键。

JSON 对象中的顺序不重要。与列名称不匹配的任何名称都将被忽略。下面显示了一个简单 JSON 对象的结构。

Copy
{ "column1": "value1", "column2": value2, "notacolumn" : "ignore this value" }

JSON 数组 以中括号 ([]) 开头和结尾,并包含由逗号分隔的值的有序集合。如果您的数据文件使用了数组,则必须指定 JSONPaths 文件以将值与列匹配。下面显示了一个简单 JSON 数组的结构。

Copy
["value1", value2]

JSON 必须格式正确。例如,对象或数组不能用逗号或除空格以外的任何其他字符分隔。字符串必须括在双引号字符中。引号字符必须是简单引号 (0x22),而不能是倾斜引号或“智能”引号。

单个 JSON 对象或数组(包括大括号或中括号)的最大大小为 4 MB。这与最大行大小不同。如果超过了单个 JSON 对象或数组的最大大小,则即使生成的行大小未达到 4 MB 的行大小限制,COPY 命令也会失败。

在计算行大小时,Amazon Redshift 在内部对竖线字符 ( | ) 计为两个字符。如果您的输入数据中包含大量竖线字符,则即使对象大小小于 4 MB,行大小也可能超过 4 MB。

COPY 会将 \n 作为换行符加载并且会将 \t 作为制表符加载。要加载反斜杠,请使用反斜杠 ( \\ ) 对其进行转义。

COPY 将在指定的 JSON 源中搜索格式正确且有效的 JSON 对象或数组。如果 COPY 在找到可用的 JSON 结构之前遇到任何非空格字符,或在有效的 JSON 对象或数组之间遇到此类字符,COPY 将为每个实例返回错误。这些错误将计入 MAXERROR 错误计数。当错误计数等于或超过 MAXERROR 时,COPY 将失败。

对于每个错误,Amazon Redshift 都会在 STL_LOAD_ERRORS 系统表中记录一行。LINE_NUMBER 列将记录导致错误的 JSON 对象的最后一行。

如果指定了 IGNOREHEADER,COPY 将忽略 JSON 数据中指定数量的行。JSON 数据中的换行符始终计入到 IGNOREHEADER 计算中。

默认情况下,COPY 将空字符串作为空字段加载。如果指定了 EMPTYASNULL,COPY 会将 CHAR 和 VARCHAR 字段的空字符串作为 NULL 加载。其他数据类型(如 INT)的空字符串始终作为 NULL 加载。

不支持将以下选项与 JSON 一起使用:

  • CSV

  • DELIMITER

  • ESCAPE

  • FILLRECORD

  • FIXEDWIDTH

  • IGNOREBLANKLINES

  • NULL AS

  • READRATIO

  • REMOVEQUOTES

有关更多信息,请参阅 从 JSON 格式数据执行的 COPY 操作。有关 JSON 数据结构的更多信息,请转到 www.json.org

JSONPaths 文件

如果您正在从 JSON 格式的源数据或 Avro 源数据加载,则在默认情况下,COPY 会通过以下方式将源数据中的第一层数据元素映射到目标表中的列:将名称/值对中的每个名称(即对象键)与目标表中的列的名称匹配。

如果您的列名称与对象键不匹配,或要映射到数据层次结构中的更深层次,则可以使用 JSONPaths 文件将 JSON 或 Avro 数据元素显式映射到列。JSONPaths 文件通过匹配目标表或列列表中的列顺序来将 JSON 数据元素映射到列。

JSONPaths 文件只能包含一个 JSON 对象(非数组)。JSON 对象是一个名称/值对。对象键(即名称/值对的名称)必须为 "jsonpaths"。名称/值对中的 是一组 JSONPath 表达式。每个 JSONPath 表达式都引用 JSON 数据层次结构或 Avro schema 中的一个元素,这与 XPath 表达式引用 XML 文档中的元素相似。有关更多信息,请参阅 JSONPath Expressions

要使用 JSONPaths 文件,请将 JSON 或 AVRO 关键字添加到 COPY 命令并使用以下格式指定 JSONPaths 文件的 S3 存储桶名称和对象路径。

Copy
COPY tablename FROM 'data_source' CREDENTIALS 'credentials-args' FORMAT AS { AVRO | JSON } 's3://jsonpaths_file';

s3://jsonpaths_file 参数必须是显式引用单个文件(如 's3://mybucket/jsonpaths.txt')的 Amazon S3 对象键,而不能是键前缀。

注意

如果要从 Amazon S3 加载且 jsonpaths_file 指定的文件与数据文件的 copy_from_s3_objectpath 指定的路径具有相同的前缀,则 COPY 会将 JSONPaths 文件作为数据文件读取并返回错误。例如,如果数据文件使用了对象路径 s3://mybucket/my_data.json,而 JSONPaths 文件是 s3://mybucket/my_data.jsonpaths,则 COPY 会尝试将 my_data.jsonpaths 作为数据文件加载。

注意

如果键名称是除 "jsonpaths" 以外的任何字符串,则 COPY 命令不会返回错误,但会忽略 jsonpaths_file 并改为使用 'auto' 参数。

如果出现以下任一情况,COPY 命令将失败:

  • JSON 格式不正确。

  • 存在多个 JSON 对象。

  • 对象外部存在除空格以外的任何字符。

  • 数组元素是一个空字符串或者不是一个字符串。

MAXERROR 不适用于 JSONPaths 文件。

如果 ENCRYPTED 参数与 COPY 一起使用,则 JSONPaths 文件不会加密。

有关更多信息,请参阅 从 JSON 格式数据执行的 COPY 操作

JSONPath 表达式

JSONPaths 文件使用 JSONPath 表达式将数据字段映射到目标列。每个 JSONPath 表达式对应于 Amazon Redshift 目标表中的一个列。JSONPath 数组元素的顺序必须与目标表或列列表(如果使用了列列表)中列的顺序一致。

如上所示,字段名称和值均需要使用双引号字符。引号字符必须是简单引号 (0x22),而不能是倾斜引号或“智能”引号。

如果 JSONPath 表达式引用的对象元素在 JSON 数据中找不到,则 COPY 将尝试加载 NULL 值。如果引用的对象的格式不正确,则 COPY 将返回加载错误。

如果 JSONPath 表达式引用的数组元素在 JSON 或 Avro 数据中找不到,则 COPY 将失败并返回以下错误:Invalid JSONPath format: Not an array or index out of range.请从 JSONPaths 中删除在源数据中不存在的所有数组元素,并确认源数据中的数组的格式正确。

JSONPath 表达式可使用括号表示法或点表示法,但不能将两者结合使用。以下示例显示了使用括号表示法的 JSONPath 表达式。

Copy
{ "jsonpaths": [ "$['venuename']", "$['venuecity']", "$['venuestate']", "$['venueseats']" ] }

以下示例显示了使用点表示法的 JSONPath 表达式。

Copy
{ "jsonpaths": [ "$.venuename", "$.venuecity", "$.venuestate", "$.venueseats" ] }

在 Amazon Redshift COPY 语法的上下文中,JSONPath 表达式必须指定 JSON 或 Avro 分层数据结构中单个名称元素的显式路径。Amazon Redshift 不支持可能解析为不确定路径或多个名称元素的任何 JSONPath 元素(如通配符或筛选表达式)。

有关更多信息,请参阅 从 JSON 格式数据执行的 COPY 操作

将 JSONPaths 与 Avro 数据一起使用

以下示例显示了具有多个层次 Avro schema。

Copy
{ "name": "person", "type": "record", "fields": [ {"name": "id", "type": "int"}, {"name": "guid", "type": "string"}, {"name": "isActive", "type": "boolean"}, {"name": "age", "type": "int"}, {"name": "name", "type": "string"}, {"name": "address", "type": "string"}, {"name": "latitude", "type": "double"}, {"name": "longitude", "type": "double"}, { "name": "tags", "type": { "type" : "array", "name" : "inner_tags", "items" : "string" } }, { "name": "friends", "type": { "type" : "array", "name" : "inner_friends", "items" : { "name" : "friends_record", "type" : "record", "fields" : [ {"name" : "id", "type" : "int"}, {"name" : "name", "type" : "string"} ] } } }, {"name": "randomArrayItem", "type": "string"} ] }

以下示例显示了使用 AvroPath 表达式引用前面的 schema 的 JSONPaths 文件。

Copy
{ "jsonpaths": [ "$.id", "$.guid", "$.address", "$.friends[0].id" ] }

JSONPaths 示例包含以下元素:

jsonpaths

包含 AvroPath 表达式的 JSON 对象的名称。

[ … ]

方括号将包含路径元素的 JSON 数组括起。

$

美元符号表示 Avro schema 中的根元素,即 "fields" 数组。

"$.id",

AvroPath 表达式的目标。在此实例中,目标是 "fields" 数组中名为 "id" 的元素。表达式用逗号分隔。

"$.friends[0].id"

方括号表示数组索引。JSONPath 表达式使用从零开始的索引,因此该表达式引用 "friends" 数组中名为 "id" 的第一个元素。

Avro schema 语法需要使用内部字段 来定义记录和数组数据类型的结构。AvroPath 表达式将会忽略内部字段。例如,字段 "friends" 定义了一个名为 "inner_friends" 的数组,该数组又定义了一个名为 "friends_record" 的记录。要引用字段 "id" 的 AvroPath 表达式可忽略额外字段以直接引用目标字段。以下 AvroPath 表达式引用了两个属于 "friends" 数组的字段。

Copy
"$.friends[0].id" "$.friends[0].name"
BZIP2

一个值,用于指定输入文件采用压缩 bzip2 格式(.bz2 文件)。COPY 操作将读取每个压缩文件并在加载时解压数据。

GZIP

一个值,用于指定输入文件采用压缩 gzip 格式(.gz 文件)。COPY 操作将读取每个压缩文件并在加载时解压数据。

LZOP

一个值,用于指定输入文件采用压缩 lzop 格式(.lzo 文件)。COPY 操作将读取每个压缩文件并在加载时解压数据。

注意

COPY 不支持使用 lzop --filter 选项压缩的文件。