sql - 在 SQL (Athena) 中取消嵌套 : How to convert array of structs into an array of values plucked from the structs?

Question

我正在从贝叶斯统计模型中获取样本，使用 Avro 对它们进行序列化，将它们上传到 S3，并使用 Athena 查询它们。

我需要帮助编写一个在表中取消嵌套数组的查询。

CREATE TABLE 查询如下所示:

CREATE EXTERNAL TABLE `model_posterior`(
  `job_id` bigint,
  `model_id` bigint,
  `parents` array<struct<`feature_name`:string,`feature_value`:bigint, `is_zid`:boolean>>,
  `posterior_samples` struct <`parameter`:string,`is_scaled`:boolean,`samples`:array<double>>)

“posterior_samples”列中的“samples”数组是存储样本的地方。我设法使用以下查询取消嵌套“posterior_samples”结构:

WITH samples AS (
    SELECT model_id, parents, sample, sample_index
    FROM posterior_db.model_posterior 
    CROSS JOIN UNNEST(posterior_samples.samples) WITH ORDINALITY AS t (sample, sample_index)
    WHERE job_id = 111000020709
)
SELECT * FROM samples

现在我想要的是取消嵌套 parent 列。此列中的每条记录都是一个结构数组。我正在尝试创建一个列，该列仅包含该结构数组中“feature_value”键的值数组。 (我想要一个数组的原因是父数组的长度可以大于 1)。

换句话说，对于父行中的每个数组，我想要一个相同大小的数组。该数组应仅包含来自原始数组中结构的“feature_value”键的值。

关于如何解决这个问题的任何建议？

谢谢。

Answer 1

您可以使用 transform功能描述 here .
假设我们有名为 samples 的表与您的问题中提到的结构。然后您可以编写如下所示的查询

SELECT *, transform(parents, parent -> parent.feature_value) as only_ feature_values

FROM samples

注意:这在语法上不是完美的查询，但您可以使用它。

希望这会有所帮助。干杯:)