将 JSON 文档解析为 SUPER 列使用 COPY 在 Amazon Redshift 中加载 JSON 数据

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

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

有关以下示例中使用的表的信息，请参阅SUPER sample 数据集。

有关 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 结构：

使用 noshred 选项将从 JSON 文档派生的数据存储到单个 SUPER 数据列中。当 schema 未知或预计将更改时，此方法非常有用。因此，此方法可以更容易地将整个元组存储在单个 SUPER 列中。
使用 auto 或 jsonpaths 选项将 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;

Jsonpath 文件将 JSON 文档中的字段映射到表格列。您可以提取其它列，例如分配键和排序键，同时仍将完整文档作为 SUPER 列加载。以下查询将完整文档加载到国家/地区列。name 列为排序键，而 regionkey 列为分配键。


CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

根 jsonpath "$" 映射到文档的根目录，如下所示：


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

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


COPY nations_sorted 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_sorted_jsonpaths.json';

从文本和 CSV 复制数据

Amazon Redshift 将文本和 CSV 格式的 SUPER 列表示为序列化 JSON。要使用正确的类型信息加载 SUPER 列，则需要有效的 JSON 格式。取消引用对象、数组、数字、布尔值和空值。用双引号将字符串值括起来。SUPER 列对文本和 CSV 格式使用标准转义规则。对于 CSV 格式，分隔符将根据 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。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

SUPER sample 数据集

卸载半结构化数据