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

从 JSON 格式数据执行的 COPY 操作

JSON 数据结构由一组对象 或数组 组成。JSON 对象 以大括号开头和结尾,并包含名称-值对的无序集合。每个名称和值由冒号分隔,而每个名称/值对由逗号分隔。名称是用双引号括起的字符串。引号字符必须是简单引号 (0x22),而不能是倾斜引号或“智能”引号。

JSON 数组 以中括号开头和结尾,并包含由逗号分隔的值的有序集合。值可以是用双引号括起的字符串、数字、布尔值 true 或 false、null、JSON 对象或数组。

JSON 对象和数组可以嵌套,从而实现分层的数据结构。以下示例显示了包含两个有效对象的 JSON 数据结构。

{ "id": 1006410, "title": "Amazon Redshift Database Developer Guide" } { "id": 100540, "name": "Amazon Simple Storage Service User Guide" }

下面显示了与两个 JSON 数组相同的数据。

[ 1006410, "Amazon Redshift Database Developer Guide" ] [ 100540, "Amazon Simple Storage Service User Guide" ]

JSON 的 COPY 选项

将 COPY 与 JSON 格式数据结合使用时,可以指定以下选项:

  • 'auto' – COPY 自动从 JSON 文件加载字段。

  • 'auto ignorecase' – COPY 自动从 JSON 文件加载字段,同时忽略字段名称的大小写。

  • s3://jsonpaths_file – COPY 使用 JSONPaths 文件解析 JSON 源数据。JSONPaths 文件 是一个包含单个 JSON 对象的文本文件,其中的对象名称 "jsonpaths" 与 JSONPath 表达式数组配对。如果该名称是 "jsonpaths" 之外的任何字符串,则 COPY 将使用 'auto' 参数而不是使用 JSONPaths 文件。

有关说明如何使用 'auto''auto ignorecase' 或 JSONPaths 文件以及使用 JSON 对象或数组加载数据的示例,请参阅从 JSON 中复制的示例

JSONPath 选项

在 Amazon Redshift COPY 语法中,JSONPath 表达式使用括号表示法或点表示法指定 JSON 层次数据结构中单个名称元素的显式路径。Amazon Redshift 不支持可能解析为不确定路径或多个名称元素的任何 JSONPath 元素(如通配符或筛选表达式)。因此,Amazon Redshift 无法解析复杂、多级的数据结构。

下面是包含使用括号表示法的 JSONPath 表达式的 JSONPaths 文件的示例。美元符号 ($) 表示根级别结构。

{ "jsonpaths": [ "$['id']", "$['store']['book']['title']", "$['location'][0]" ] }

在上面的示例中,$['location'][0] 引用数组中的第一个元素。JSON 使用从 0 开始的数组索引。数组索引必须是正整数(大于或等于零)。

以下示例显示了使用点表示法的前一个 JSONPaths 文件。

{ "jsonpaths": [ "$.id", "$.store.book.title", "$.location[0]" ] }

您不能在 jsonpaths 数组中将括号表示法和点表示法混合。括号表示法和点表示法中均可使用括号来引用数组元素。

使用点表示法时,JSONPath 表达式不能包含下列字符:

  • 单直引号 ( ' )

  • 句点或点 (.)

  • 中括号 ( [ ] )(除非用于引用数组元素)

如果 JSONPath 表达式引用的名称-值对中的值是对象或数组,则整个对象或数组将作为字符串加载,包括大括号或中括号。例如,假定 JSON 数据包含以下对象。

{ "id": 0, "guid": "84512477-fa49-456b-b407-581d0d851c3c", "isActive": true, "tags": [ "nisi", "culpa", "ad", "amet", "voluptate", "reprehenderit", "veniam" ], "friends": [ { "id": 0, "name": "Martha Rivera" }, { "id": 1, "name": "Renaldo" } ] }

JSONPath 表达式 $['tags'] 之后将返回以下值。

"["nisi","culpa","ad","amet","voluptate","reprehenderit","veniam"]"

JSONPath 表达式 $['friends'][1] 之后将返回以下值。

"{"id": 1,"name": "Renaldo"}"

jsonpaths 数组中的每个 JSONPath 表达式对应于 Amazon Redshift 目标表中的一个列。jsonpaths 数组元素的顺序必须与目标表或列列表(如果使用了列列表)中列的顺序一致。

有关说明如何使用 'auto' 参数或 JSONPaths 文件以及使用 JSON 对象或数组加载数据的示例,请参阅从 JSON 中复制的示例

有关如何复制多个 JSON 文件的信息,请参阅使用清单指定数据文件

在 JSON 中转义字符

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

例如,假设您在桶 escape.json 中名为 s3://mybucket/json/ 的文件中具有以下 JSON。

{ "backslash": "This is a backslash: \\", "newline": "This sentence\n is on two lines.", "tab": "This sentence \t contains a tab." }

运行下列命令以创建 ESCAPES 表并加载 JSON。

create table escapes (backslash varchar(25), newline varchar(35), tab varchar(35)); copy escapes from 's3://mybucket/json/escape.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as json 'auto';

查询 ESCAPES 表以查看结果。

select * from escapes; backslash | newline | tab ------------------------+-------------------+---------------------------------- This is a backslash: \ | This sentence | This sentence contains a tab. : is on two lines. (1 row)

数值精度丢失

当您将数字从 JSON 格式的数据文件加载到定义为数字数据类型的列时,您可能会丢失精度。某些浮点值在计算机系统中无法准确表示。因此,您从 JSON 文件复制的数据可能无法按预期进行舍入。为避免精度丢失,我们建议使用以下替代方法之一:

  • 通过用双引号字符将值括起来将数字表示为字符串。

  • 使用 ROUNDEC 对数字进行舍入而不是截断。

  • 不要使用 JSON 或 Avro 文件,而应使用 CSV、字符分隔或固定宽度的文本文件。