sql - 在 Athena 中，如何在结构中的数组中查询结构的成员？

Question

我试图弄清楚如何查询我在哪里检查 usage 的值，给定以下表创建:

CREATE EXTERNAL TABLE IF NOT EXISTS foo.test (
     `id` string,
     `foo` struct< usages:array< struct< usage:string,
     method_id:int,
     start_at:string,
     end_at:string,
     location:array<string> >>> 
) PARTITIONED BY (
         timestamp date 
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1' ) LOCATION 's3://foo.bar/' TBLPROPERTIES ('has_encrypted_data'='false');

我想要这样的查询:

SELECT * FROM "foo"."test" WHERE foo.usages.usage is null;

当我这样做时，我得到:

SYNTAX_ERROR: line 1:53: Expression "foo"."usages" is not of type ROW

如果我在直接索引数组的地方进行查询，如下所示，它可以工作。

SELECT * FROM "foo"."test" WHERE foo.usages[1].usage is null;

我的总体目标是查询 usages 数组中的所有项目，并找到 usages 数组中至少一项具有成员 的任何行用法为空。

Answer 1

Athena 基于 Presto。在 Presto 318 中，您可以使用 any_match :

SELECT * FROM "foo"."test"
WHERE any_match(foo.usages, element -> element.usage IS NULL);

我认为该功能在 Athena 中尚不可用，但您可以使用 reduce 模拟它.

SELECT * FROM "foo"."test"
WHERE reduce(
  foo.usages, -- array to reducing
  false, -- initial state
  (state, element) -> state OR element.usage IS NULL, -- combining function
  state -> state); -- output function (identity in this case)