查询半结构化数据 - Amazon Redshift

查询半结构化数据

Amazon Redshift 使用 PartiQL 语言提供对关系数据、半结构化数据和嵌套数据的 SQL 兼容访问。

PartiQL 使用动态类型进行操作。这种方法可以对结构化、半结构化和嵌套数据集的组合进行直观的筛选、联接和聚合。在访问嵌套数据时,PartiQL 语法使用点记法和数组下标进行路径导航。它还使 FROM 子句项能够对数组进行迭代并用于非嵌套操作。以下内容介绍了将 SUPER 数据类型的使用与路径和数组导航、取消嵌套、逆透视转换和联接相结合的不同查询模式。

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

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;

取消嵌套查询

为了取消嵌套查询,Amazon Redshift 使用 PartiQL 语法迭代 SUPER 数组。它通过使用查询的 FROM 子句导航数组来实现这一点。使用前面的示例,以下示例对 c_orders 的属性值进行迭代。

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

取消嵌套语法是 FROM 子句的扩展。在标准 SQL 中,FROM 子句 x (AS) y 表示 y 迭代关系 x 中的每个元组。在这种情况下,x 指的是关系,而 y 指的是关系 x 的别名。同样,使用 FROM 子句项 x (AS) y 进行取消嵌套的 PartiQL 语法表示 y 迭代(SUPER)数组表达式 x 中的每个(SUPER)值。在这种情况下,x 是一个 SUPER 表达式,而 yx 的别名。

左侧操作数也可以使用点和括号表示法进行常规导航。在上一个示例中,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;

Amazon Redshift 还在使用 AT 关键字迭代数组时支持数组索引。子句 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)

以下示例对标量数组进行迭代:

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data; SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index; index | element -------+---------- 0 | 1 1 | 2.3 2 | 45000000 (3 rows)

以下示例对多个级别的数组进行迭代。该示例使用多个 unnest 子句来迭代到最内层的数组。f.multi_level_array AS 数组迭代 multi_level_array。数组 AS 元素是对 multi_level_array 中的数组的迭代。

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array; SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element; array | element -----------+--------- [1.1,1.2] | 1.1 [1.1,1.2] | 1.2 [2.1,2.2] | 2.1 [2.1,2.2] | 2.2 [3.1,3.2] | 3.1 [3.1,3.2] | 3.2 (6 rows)

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

对象逆透视

为执行逆透视,Amazon Redshift 使用 PartiQL 语法迭代 SUPER 对象。它使用带有 UNPIVOT 关键字的查询的 FROM 子句来执行此操作。在这种情况下,表达式是 c.c_orders[0] 对象。示例查询会遍历此对象返回的每个属性。

SELECT attr as attribute_name, json_typeof(val) as value_type FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr WHERE c_custkey = 9451; attribute_name | value_type -----------------+------------ o_orderstatus | string o_clerk | string o_lineitems | array o_orderdate | string o_shippriority | number o_totalprice | number o_orderkey | number o_comment | string o_orderpriority | string (9 rows)

与取消嵌套一样,逆透视语法也是 FROM 子句的扩展。不同之处在于,逆透视的语法使用 UNPIVOT 关键字来表示它正在迭代对象而不是数组。它使用 AS value_alias 迭代对象内的所有值并使用 AT attribute_alias 迭代所有属性。请考虑以下语法片段:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift 支持在单个 FROM 子句中使用对象反转置和数组取消嵌套,如下所示:

SELECT attr as attribute_name, val as object_value FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr WHERE c_custkey = 9451;

当您使用对象逆透视时,Amazon Redshift 不支持关联的逆透视。具体来说,假设您有一个案例,其中在不同查询级别有多个逆透视示例,并且内部逆透视引用了外部逆透视。Amazon Redshift 不支持此类多重逆透视。

有关 FROM 子句的更多信息,请参阅FROM 子句。有关演示如何使用 PIVOT 和 UNPIVOT 查询结构化数据的示例,请参阅 PIVOT 和 UNPIVOT 示例

动态键入

动态键入不需要显式转换从点和括号路径中提取的数据。Amazon Redshift 使用动态键入处理无 schemal 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 使用动态键入来处理无 schema 数据。当查询计算数据时,c_orders[0].o_orderstatus 是一种特定的类型。例如,在 customer_orders_lineitem 的第一条记录上评估 c_orders[0].o_orderstatus 可能会导致一个整数。对第二条记录进行评估可能会导致字符串。它们都是表达式的动态类型。

当将 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 是一个字符串,则如果它的字母顺序等于或小于“P”,Amazon Redshift 返回 true。如果 Amazon Redshift 按字母顺序大于“P”,则返回 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 仍将它们视为动态类型。

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[1]) AND IS_INTEGER(ps.ps_partkey[1]) 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 的字符串值“Good”转换为 INTEGER。Amazon Redshift 针对 VARCHAR 到 INTEGER 的转换返回错误,但不针对 SUPER 转换返回错误。

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

自检类型

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

  • DECIMAL_PRECISION

  • DECIMAL_SCALE

  • IS_ARRAY

  • IS_BIGINT

  • IS_CHAR

  • IS_DECIMAL

  • IS_FLOAT

  • IS_INTEGER

  • IS_OBJECT

  • IS_SCALAR

  • IS_SMALLINT

  • IS_VARCHAR

  • JSON_TYPEOF

如果输入值为 null,所有这些函数都返回 false。IS_SCALAR、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 定义了在 Amazon Redshift 使用 ORDER BY 子句对 SUPER 值进行排名时,需要观察的不同类型的总排序。动态类型之间的顺序是布尔值、数字、字符串、数组、对象。以下示例显示不同类型的顺序:

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 子句