数据格式参数 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

数据格式参数

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

除标准数据格式以外,COPY 支持 Amazon S3 中有关 COPY 的以下列式数据格式:

支持列式中的 COPY,其中带有特定限制。有关更多信息,请参阅从列式数据格式中执行 COPY 操作

数据格式参数
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 的格式如下所示:

'colLabel1:colWidth1,colLabel:colWidth2, ...'
SHAPEFILE [ SIMPLIFY [AUTO] ['tolerance'] ]

支持在输入数据中使用 SHAPEFILE 格式。预设情况下,shapefile 的第一列是 GEOMETRYIDENTITY 列。所有后续列都遵循 shapefile 中指定的顺序。

您不能将 SHAPEFILE 与 FIXEDWIDTH、REMOVEQUOTES 或 ESCAPE 一起使用。

要将 GEOGRAPHY 对象与 COPY FROM SHAPEFILE 一起使用,请首先提取到 GEOMETRY 列,然后将对象强制转换为 GEOGRAPHY 对象。

SIMPLIFY [tolerance]

(可选)使用 Ramer-Douglas-Peucker 算法和给定的容差简化摄入过程中的所有几何体。

SIMPLIFY AUTO [tolerance]

(可选)仅简化大于最大几何大小的几何体。这种简化使用 Ramer-Douglas-Peucker 算法和自动计算的容差(如果不超过指定容差)。此算法计算在指定容差范围内存储对象的大小。公差值是可选的。

有关加载 shapefile 的示例,请参阅将 shapefile 加载到 Amazon Redshift

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'

  • 'auto ignorecase'

  • 's3://jsonpaths_file'

默认为 'auto'

COPY 会将 Avro 源数据中的数据元素自动映射到目标表中的列。它的执行方式是通过将 Avro schema 中的字段名称与目标表中的列名称相匹配。'auto' 的匹配区分大小写,'auto ignorecase' 的匹配不区分大小写。

Amazon Redshift 表中的列名称始终小写,因此,当您使用 'auto' 选项时,匹配的字段名称也必须为小写。如果字段名称不是全部小写,则可以使用 'auto ignorecase' 选项。使用默认的 'auto' 参数时,COPY 仅识别结构中的第一层字段,或外部字段

要将列名称显式映射到 Avro 字段名称,您可以使用 JSONPaths 文件

默认情况下,COPY 会尝试将目标表中的所有列与 Avro 字段名称匹配。要加载列的子集,您可以选择性地指定包含列的列表。如果列列表中省略了目标表中的列,则 COPY 将加载目标列的 DEFAULT 表达式。如果目标列没有默认值,则 COPY 将尝试加载 NULL。如果某个列包含在列列表中,并且 COPY 在 Avro 数据中找不到匹配的字段,则 COPY 会尝试将 NULL 加载到该列中。

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

Avro Schema

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

{ "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 会自动将字段名称与列名称匹配。列名称始终为小写形式,因此匹配的字段名称也必须为小写形式,除非您指定了 ‘auto ignorecase’ 选项。与列名称不匹配的任何字段名称都将被忽略。顺序无关紧要。在上述示例中,COPY 将映射到列名称 idguidnameaddress

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

如果与键关联的值是一个复杂的 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'

  • 'auto ignorecase'

  • 's3://jsonpaths_file'

  • 'noshred'

默认为 'auto'。在加载 JSON 文档时,Amazon Redshift 不会将 JSON 结构的属性分解为多个列。

默认情况下,COPY 会尝试将目标表中的所有列与 JSON 字段名称键匹配。要加载列的子集,您可以选择性地指定包含列的列表。如果 JSON 字段名称键包含大写字符,则您可以使用 'auto ignorecase' 选项或 JSONPaths 文件 将列名称显式地映射到 JSON 字段名称键。

如果列列表省略了目标表中的列,则 COPY 将加载目标列的 DEFAULT 表达式。如果目标列没有默认值,则 COPY 将尝试加载 NULL。如果某个列包含在列列表中,并且 COPY 在 JSON 数据中找不到匹配的字段,则 COPY 会尝试将 NULL 加载到该列。

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

COPY 会将 JSON 源数据中的数据元素映射到目标表中的列。它的操作方式是通过将源名称-值对中的对象键(即名称)与目标表中的列名称匹配。

请参阅以下有关每个 json_option 值的详细信息:

'auto'

使用此选项时,匹配区分大小写。Amazon Redshift 表中的列名称始终小写,因此,当您使用 'auto' 选项时,匹配的 JSON 字段名称也必须为小写。

“auto ignorecase”

使用此选项时,匹配不区分大小写。Amazon Redshift 表中的列名称始终小写,因此,当您使用 'auto ignorecase' 选项时,相应的 JSON 字段名称可以是小写、大写或大小写混合。

's3://jsonpaths_file'

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

在某些情况下,由 jsonpaths_file 指定的文件的前缀与由 copy_from_s3_objectpath 为数据文件指定的路径的前缀相同。如果是这样,COPY 会将 JSONPaths 文件作为数据文件读取并返回错误。例如,假设您的数据文件使用对象路径 s3://mybucket/my_data.json,并且您的 JSONPaths 文件是 s3://mybucket/my_data.jsonpaths。在这种情况下,COPY 会尝试加载 my_data.jsonpaths 作为数据文件。

“noshred”

使用此选项,Amazon Redshift 不会在加载 JSON 文档时将 JSON 结构的属性分解为多个列。

JSON 数据文件

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

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

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

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

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

["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 表达式

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

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 选项,也不得加密 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 表达式。

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

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

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

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

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

将 JSONPaths 与 Avro 数据一起使用

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

{ "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 文件。

{ "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" 数组的字段。

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

列式数据格式参数

除标准数据格式以外,COPY 支持 Amazon S3 中有关 COPY 的以下列式数据格式。支持列式中的 COPY,其中带有特定限制。有关更多信息,请参阅从列式数据格式中执行 COPY 操作

ORC

从使用优化的行列式 (ORC) 文件格式的文件中加载数据。

PARQUET

从使用 Parquet 文件格式的文件中加载数据。