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

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

查询半结构化数据

Amazon Redshift 使用 PartiQL 语言提供对关系、半结构化和嵌套数据的 SQL 兼容访问。PartiQL 对动态类型进行操作。这样,就可以直观地筛选、联接和聚合结构化、半结构化和嵌套数据集的组合。在访问嵌套数据时,PartiQL 语法使用虚线表示法和数组下标来进行路径导航。它还使 FROM 子句项目可以迭代数组,并将 用于取消嵌套操作。以下部分介绍将 SUPER 数据类型与路径和数组导航、取消嵌套或联接结合使用的不同查询模式。

Amazon Redshift 使用 PartiQL 来分别使用 [...] 括号和点表示法启用导航到数组和结构。此外,您还可以使用点表示法混合导航到结构中,并使用括号表示法排列。例如,以下示例假定 c_orders SUPER 数据列是具有结构的数组,并且属性名为 o_orderkey。

运行以下命令以提取 customer_orders_lineitem 表中的数据。使用您自己的凭证替换 IAM 角色。

COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 'auto'; SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift 还使用表别名作为表示法的前缀。以下示例与上一示例的查询相同。

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

您可以在筛选、联接和聚合等所有类型的查询中使用点和括号表示法。您可以在具有通常列引用的查询中使用它们。以下示例使用用于筛选结果的 SELECT 语句。

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

以下示例在 GROUP BY 和 ORDER BY 子句中使用括号和点导航:

SELECT c_orders[0].o_orderdate, c_orders[0].o_orderstatus, count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL GROUP BY c_orders[0].o_orderstatus, c_orders[0].o_orderdate ORDER BY c_orders[0].o_orderdate;

Unnesting

Amazon Redshift 使用 PartiQL 语法,通过使用查询的 FROM 子句导航数组来迭代 SUPER 数组。使用上一个示例,以下示例循环访问属性 c_orders 值。

SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

取消嵌套语法是 FROM 子句的扩展。在标准 SQL 中,FROM 子句 x (AS) y 表示对于表 x 中的每个元组 y。同样,FROM 子句 x (AS) y(如果 x 是 SUPER 值,则对于 (SUPER) 数组值 x 中的每个 (SUPER) 值 y。左侧操作数也可以使用点和括号表示法进行常规导航。在上一个示例中,customer_orders_lineitem c 是对 customer_order_lineitem 基表的迭代,而 c.c_orders o 是对 c.c_orders 数组的迭代。要迭代作为数组中的数组的 o_lineitems 属性,您必须添加多个子句。

SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

当使用 AT 关键字迭代数组时,Amazon Redshift 还支持数组索引。子句 x AS y AT z 迭代数组 x 并生成作为数组索引的字段 z。以下示例显示数组索引的工作原理:

SELECT c_name, orders.o_orderkey AS orderkey, INDEX AS orderkey_index FROM customer_orders_lineitem c, c.c_orders AS orders AT INDEX ORDER BY orderkey_index; c_name | orderkey | orderkey_index -------------------+----------+---------------- Customer#000008251 | 3020007 | 0 Customer#000009452 | 4043971 | 0 (2 rows)

有关 FROM 子句的更多信息,请参阅FROM 子句

动态类型

动态类型不需要显式转换从点和括号路径中提取的数据。Amazon Redshift 使用动态类型来处理无架构 SUPER 数据,而无需声明数据类型即可在查询中使用它们。动态类型使用导航到 SUPER 数据列的结果,而不必将其显式转换为 Amazon Redshift 类型。动态类型在联接和 GROUP BY 子句中最有用。以下示例使用一个 SELECT 语句,该语句不需要将点和括号表达式显式转换为常规 Amazon Redshift 类型。有关类型兼容性和转换的信息,请参阅类型兼容性和转换

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus = 'P';

当 c_orders[0].o_orderstatus 是字符串“P”时,此查询中的相等符号的计算结果为 true。在所有其他情况下,等式符号的计算结果为 false,包括等式参数是不同类型的情况。

动态和静态类型

如果不使用动态类型,则无法确定 c_orders[0].o_orderstatus 是字符串、整数还是结构。您只能确定 c_orders[0].o_orderstatus 是 SUPER 数据类型,可以是 Amazon Redshift 标量、数组或结构。c_orders[0].o_orderstatus 的静态类型是 SUPER 数据类型。以前,类型在 SQL 中隐式为静态类型。

Amazon Redshift 使用动态类型来处理无架构数据。当查询计算数据时,c_orders[0].o_orderstatus 会变成特定类型。例如,评估 c_orders[0].o_orderstatus 对 customer_orders_lineitem 的第一个记录可能会生成整数。评估第二个记录可能会导致字符串。这些是表达式的动态类型。

将 SQL 运算符或函数与具有动态类型的点和括号表达式结合使用时,Amazon Redshift 生成的结果类似于将标准 SQL 运算符或函数与相应的静态类型结合使用。在此示例中,当路径表达式的动态类型为字符串时,与字符串“P”的比较有意义。当 c_orders[0].o_orderstatus 的动态类型是任何其他数据类型(字符串除外)时,相等性将返回 false。当使用拼写错误的参数时,其他函数返回 null。

以下示例使用静态类型编写上一个查询:

SELECT c_custkey FROM customer_orders_lineitem WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string' THEN c_orders[0].o_orderstatus::VARCHAR = 'P' ELSE FALSE END;

请注意相等谓词和比较谓词之间的以下区别。在前面的示例中,如果您将相等谓词替换为小于或等于谓词,则语义将生成 null 而不是 false。

SELECT c_orders[0]. o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus <= 'P';

在此示例中,如果 c_orders[0].o_orderstatus 是字符串,则 Amazon Redshift 在字母顺序等于或小于“P”时返回 true。如果字母顺序状态大于“P”,则 Amazon Redshift 返回 false。但是,如果 c_orders[0].o_orderstatus 不是字符串,则 Amazon Redshift 返回 null,因为 Amazon Redshift 无法比较不同类型的值,如以下查询所示:

SELECT c_custkey FROM customer_orders_lineitem WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string' THEN c_orders[0].o_orderstatus::VARCHAR <= 'P' ELSE NULL END;

动态类型不会排除在最便宜可比较的类型比较之外。例如,您可以将 CHAR 和 VARCHAR Amazon Redshift 标量类型转换为 SUPER。它们与字符串类似,包括忽略尾随空格字符(类似于 Amazon Redshift CHAR 和 VARCHAR 类型)。同样,整数、小数和浮点值与 SUPER 值可以比。具体而言,对于小数列,每个值也可以具有不同的小数位数。Amazon Redshift 仍将其视为动态类型。

对联接使用动态类型

对于联接,动态类型自动将值与不同的动态类型进行匹配,而不执行长 CASE WHEN 分析来找出可能出现的数据类型。例如,假定您的组织随着时间的推移更改了用于部分密钥的格式。

发布的初始整数部分键将替换为字符串部分键(如“A55”),之后将再次替换为数组部分键(如将字符串和数字组合起来的 [“X”, 10”)。Amazon Redshift 不必对部分键执行较长的大小写分析,并且可以使用联接,如以下示例所示。

SELECT c.c_name ,l.l_extendedprice ,l.l_discount FROM customer_orders_lineitem c ,c.c_orders o ,o.o_lineitems l ,supplier_partsupp s ,s.s_partsupps ps WHERE l.l_partkey = ps.ps_partkey AND c.c_nationkey = s.s_nationkey ORDER BY c.c_name;

以下示例演示相同查询在不使用动态键入的情况下可能很复杂且效率低下的程度:

SELECT c.c_name ,l.l_extendedprice ,l.l_discount FROM customer_orders_lineitem c ,c.c_orders o ,o.o_lineitems l ,supplier_partsupp s ,s.s_partsupps ps WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey) THEN l.l_partkey::integer = ps.ps_partkey::integer WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey) THEN l.l_partkey::varchar = ps.ps_partkey::varchar WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey) AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0]) AND IS_INTEGER(l.l_partkey[0]) AND IS_INTEGER(ps.ps_partkey[0]) THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer ELSE FALSE END AND c.c_nationkey = s.s_nationkey ORDER BY c.c_name;

延迟语义

默认情况下,对 SUPER 值的导航操作返回 null,而不是在导航无效时返回错误。如果 SUPER 值不是对象或 SUPER 值是对象,但不包含查询中使用的属性名称,则对象导航无效。例如,以下查询访问 SUPER 数据列 cdata 中的无效属性名称:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

如果 SUPER 值不是数组或者数组索引超出范围,则数组导航将返回 null。以下查询返回 null,因为 c_orders[1][1] 超出范围。

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

当使用动态类型强制转换 SUPER 值时,延迟语义尤其有用。将 SUPER 值强制转换为错误的类型将返回 null,而不是在强制转换无效时返回错误。例如,以下查询返回 null,因为它无法将对象属性 o_orderstatus 的字符串值“正常”强制转换为 INTEGER。Amazon Redshift 会将 VARCHAR 的错误返回到 INTEGER 强制转换,但不能为 SUPER 强制转换。

SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

自检类型

SUPER 数据列支持返回动态类型和有关 SUPER 值的其他类型信息的检查函数。最常见的示例是 JSON_TYPEOF 标量函数,该函数返回具有值布尔值、数字、字符串、对象、数组或 null 的 VARCHAR,具体取决于 SUPER 值的动态类型。对于 SUPER 数据列,Amazon Redshift 支持以下布尔函数:

  • DECIMAL_PRECISION

  • 发出 DECIMAL 请求

  • IS_ARRAY

  • IS_BIGINT

  • IS_CHAR

  • IS_DECIMAL

  • IS_FLOAT

  • IS_INTEGER

  • IS_OBJECT

  • IS_SCAL

  • IS_SMALLINT

  • IS_VARCHAR

  • JSON_TYPEOF

如果输入值为 null,则所有这些函数返回 false。IS_SCALALAR、IS_OBJECT 和 IS_ARRAY 相互排斥,涵盖 null 以外的所有可能值。

为推断与数据对应的类型,Amazon Redshift 使用 JSON_TYPEOF 函数,该函数返回 SUPER 值的类型(顶级),如以下示例所示:

SELECT JSON_TYPEOF(r_nations) FROM region_nations; json_typeof ------------- array (1 row)
SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations; json_typeof ------------- number

Amazon Redshift 将此值视为单个长字符串,类似于将此值插入到 VARCHAR 列而不是 SUPER。由于列是 SUPER,因此单个字符串仍然是有效的 SUPER 值,并且在 JSON_TYPEOF 中会记录差异:

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations; is_varchar ------------- true (1 row)
SELECT r_nations[4].n_name FROM region_nations WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) THEN r_nations[4].n_nationkey::INTEGER = 15 ELSE false END;

Order by (排序依据)

Amazon Redshift 不定义具有不同动态类型的值之间的 SUPER 比较。作为字符串的 SUPER 值既不小于也不大于作为数字的 SUPER 值。要将 ORDER BY 子句与 SUPER 列结合使用,Amazon Redshift 定义了在使用 ORDER BY 子句对 SUPER 值进行排名时,要观察的不同类型的总排序。Amazon Redshift动态类型的顺序是布尔型、数字、字符串、数组、对象。以下示例显示了不同类型的顺序:

INSERT INTO region_nations VALUES (100,'name1','comment1','AWS'), (200,'name2','comment2',1), (300,'name3','comment3',ARRAY(1, 'abc', null)), (400,'name4','comment4',-2.5), (500,'name5','comment5','Amazon'); SELECT r_nations FROM region_nations order by r_nations; r_nations ---------------- -2.5 1 "Amazon" "AWS" [1,"abc",null] (5 rows)

有关 ORDER BY 子句的更多信息,请参阅ORDER BY 子句