将半结构化数据加载到 Amazon Redshift - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

将半结构化数据加载到 Amazon Redshift

使用 SUPER 数据类型在 Amazon Redshift 中保留和查询分层数据和通用数据。Amazon Redshift 引入 json_parse 函数来解析 JSON 格式的数据并将其转换为 SUPER 表示形式。Amazon Redshift 还支持使用 COPY 命令加载 SUPER 列。受支持的文件格式包括 JSON、Avro、文本、逗号分隔值 (CSV) 格式、Parquet 和 ORC。

有关 json_parse 函数的信息,请参阅JSON_PARSE 函数

SUPER 数据类型的默认编码是 ZSTD。

将 JSON 文档解析为 SUPER 列

您可以使用 json_parse 函数将 JSON 数据插入或更新到 SUPER 列中。该函数以 JSON 格式解析数据,并将其转换为 SUPER 数据类型,您可以在 INSERT 或 UPDATE 语句中使用该数据类型。

以下示例将 JSON 数据插入到 SUPER 列中。如果 json_parse 函数在查询中缺失,Amazon Redshift 将该值视为单个字符串,而不是必须解析的 JSON 格式的字符串。

如果您更新了 SUPER 数据列,Amazon Redshift 会要求将完整的文档传递给列值。Amazon Redshift 不支持部分更新。

INSERT INTO region_nations VALUES(0, 'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to', 'AFRICA', JSON_PARSE('{"r_nations":[ {"n_comment":" haggle. carefully final deposits detect slyly agai", "n_nationkey":0, "n_name":"ALGERIA" }, {"n_comment":"ven packages wake quickly. regu", "n_nationkey":5, "n_name":"ETHIOPIA" }, {"n_comment":" pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t", "n_nationkey":14, "n_name":"KENYA" }, {"n_comment":"rns. blithely bold courts among the closely regular packages use furiously bold platelets?", "n_nationkey":15, "n_name":"MOROCCO" }, {"n_comment":"s. ironic, unusual asymptotes wake blithely r", "n_nationkey":16, "n_name":"MOZAMBIQUE" } ] }'));

使用 COPY 在 Amazon Redshift 中加载 SUPER 列

在以下部分中,您可以了解使用 COPY 命令将 JSON 数据加载到 Amazon Redshift 的不同方法。

从 JSON 和 Avro 复制数据

通过在 Amazon Redshift 中使用半结构化数据支持,您可以加载 JSON 文档,而无需将 JSON 结构的属性分解为多列。

Amazon Redshift 提供了两种使用 COPY 摄取 JSON 文档的方法,即使是完全或部分未知的 JSON 结构:

  1. 使用 noshred 选项将从 JSON 文档派生的数据存储到单个 SUPER 数据列中。当 schema 未知或预计将更改时,此方法非常有用。因此,此方法可以更容易地将整个元组存储在单个 SUPER 列中。

  2. 使用 autojsonpaths 选项将 JSON 文档分解为多个 Amazon Redshift 列。属性可以是 Amazon Redshift 标量或 SUPER 值。

您可以将这些选项与 JSON 或 Avro 格式一起使用。

分解前的 JSON 对象的最大大小为 4 MB。

将 JSON 文档复制到单个 SUPER 数据列

要将 JSON 文档复制到单个 SUPER 数据列中,请创建包含单个 SUPER 数据列的表。

CREATE TABLE region_nations_noshred (rdata SUPER);

将 Amazon S3 中的数据复制到单个 SUPER 数据列中。要将 JSON 源数据摄取到单个 SUPER 数据列中,请在 FORMAT JSON 子句中指定 noshred 选项。

COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 'noshred';

在 COPY 成功摄取 JSON 后,您的表具有包含整个 JSON 对象数据的 rdata SUPER 数据列。摄取的数据将维护 JSON 层次结构的所有属性。但是,叶子会转换为 Amazon Redshift 标量类型,以实现高效的查询处理。

使用以下查询检索原始 JSON 字符串。

SELECT rdata FROM region_nations_noshred;

当 Amazon Redshift 生成一个 SUPER 数据列时,它可以通过 JSON 序列化使用 JDBC 作为字符串进行访问。有关更多信息,请参阅序列化复杂嵌套 JSON

将 JSON 文档复制到多个 SUPER 数据列

您可以将 JSON 文档拆分为多个列,这些列可以是 SUPER 数据列或 Amazon Redshift 标量类型。Amazon Redshift 会将 JSON 对象的不同部分分布到不同的列。

CREATE TABLE region_nations ( r_regionkey smallint ,r_name varchar ,r_comment varchar ,r_nations super );

要将上一个示例的数据复制到表中,请在 FORMAT JSON 子句中指定 AUTO 选项,以将 JSON 值拆分到多个列中。COPY 将顶级 JSON 属性与列名匹配,并允许嵌套值作为 SUPER 值(如 JSON 数组和对象)摄取。

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 'auto';

当 JSON 属性名称混合使用大小写时,请在 FORMAT JSON 子句中指定 auto ignorecase 选项。有关 COPY 命令的更多信息,请参阅使用“auto ignorecase”选项从 JSON 数据中加载

在某些情况下,列名和 JSON 属性之间存在不匹配的情况,或者要加载的属性嵌套超过一层深度。如果是这样,请使用 jsonpaths 文件将 JSON 属性手动映射到 Amazon Redshift 列。

CREATE TABLE nations ( regionkey smallint ,name varchar ,comment super ,nations super );

假设您想要将数据加载到列名与 JSON 属性不匹配的表中。在下面的示例中,nations 表就是这样一个表。您可以创建一个 jsonpaths 文件,该文件将属性路径按其在 jsonpaths 数组中的位置映射到表列中。

{"jsonpaths": [ "$.r_regionkey", "$.r_name", "$.r_comment", "$.r_nations ] }

jsonpaths 文件的位置用作 FORMAT JSON 的参数。

COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

使用以下查询可访问显示传播到多个列的数据的表。使用 JSON 格式打印 SUPER 数据列。

SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

从文本和 CSV 复制数据

Amazon Redshift 将文本和 CSV 格式的 SUPER 列表示为单行 JSON 对象。CSV 中用于转义的双引号不需要用户干预。但是,对于文本格式,如果选定的分隔符也可能出现在 SUPER 字段中,请在 COPY 和 UNLOAD 期间使用 ESCAPE 选项。

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT CSV;
COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' DELIMITER ',' ESCAPE;

从列格式的 Parquet 和 ORC 中复制数据

如果您的半结构化或嵌套数据已经以 Apache Parquet 或 Apache ORC 格式提供,则可以使用 COPY 命令将数据摄取到 Amazon Redshift 中。

Amazon Redshift 表格结构应与 Parquet 或 ORC 文件的列数和列数据类型相匹配。通过在 COPY 命令中指定 SERIALIZETOJSON,您可以将文件中与表中的 SUPER 列对齐的任何列类型加载为 SUPER。这包括结构和数组类型。

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT PARQUET SERIALIZETOJSON;

以下示例使用 ORC 格式。

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT ORC SERIALIZETOJSON;

当日期或时间数据类型的属性位于 ORC 中时,Amazon Redshift 会在 SUPER 中对它们进行编码时将其转换为 varchar。