雅典娜查询数组列

Question

我需要您帮助查询雅典娜中的数组列。目前我有一个表格，如下所述：

1   2020-05-06 01:13:48 dv1 [{addedtitle=apple, addedvalue=null, keytitle=Increase apple, key=p9, recvalue=0.899999999, unit=lbs, isbalanced=null}, {addedtitle=Orange (12%), addedvalue=15.0, keytitle=Increase Orange, key=p8, recvalue=18.218999999999998, unit=fl oz, isbalanced=null}, {addedtitle=Lemon, addedvalue=32.0, keytitle=Increase Lemon, key=p10, recvalue=33.6, unit=oz, isbalanced=null}, {addedtitle=Calcium (100%), addedvalue=86.0, keytitle=Increase Calcium , key=p6, recvalue=88.72002, unit=oz, isbalanced=null}, {addedtitle=Mango, addedvalue=10.0, keytitle=Increase Mango, key=p11, recvalue=11.7, unit=oz, isbalanced=null}]
2   2020-05-07 04:30:45 dev2    [{addedtitle=apple (12%), addedvalue=0.0, keytitle=Increase apple, key=p8, recvalue=0.88034375, unit=fl oz, isbalanced=null}, {addedtitle=Orange(31.4%), addedvalue=0.0, keytitle=Decrease Orange, key=p10, recvalue=1.83733225, unit=fl oz, isbalanced=null}, {addedtitle=Tree, addedvalue=0.0, keytitle=Increase Tree, key=p11, recvalue=1.69, unit=oz, isbalanced=null}]
5   2020-05-06 12:55:12 dev5    [{addedtitle=salt, addedvalue=0.0, keytitle=Increase salt, key=p9, recvalue=0.052500000000000005, unit=lbs, isbalanced=null}]
6   2020-05-08 07:03:59 dev6    [{addedtitle=Sugar, addedvalue=6.0, keytitle=Decrease sugar, key=p9, recvalue=2.4000000000000004, unit=fl oz, isbalanced=null}]
7   2020-05-06 12:52:39 dev7    []
8   2020-05-06 04:15:05 dev8    []
9   2020-05-07 05:02:38 dev9    []

我需要将第 3 个数组列分解为更多列，以便我可以在 Quicksight 中导入它。目前有一个问题，因为 quicksight 无法识别第 3 列，因为它显示了不受支持的数据类型。

有人可以帮忙如何将这个数组分解成列/行进行分析吗？

Answer 1

不幸的是，示例中类似 JSON 的数据不是 Athena 可以解析的格式。

对于发现此问题的其他任何人，我可以解释如果数据是 JSON 格式(例如{"addedtitle": "apple",…而不是{addedtitle=apple,…(。我还假设列之间有制表符而不是空格(如果有空格，则必须使用 Grok serde(。

首先，创建一个读取制表符分隔值的表：

CREATE EXTERNAL TABLE my_table (
line_number int,
date_stamp timestamp,
id string,
data string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't'
ESCAPED BY '\'
LINES TERMINATED BY 'n'
LOCATION 's3://my-bucket/path/to/data/'

请注意data列的类型化方式是string类型而不是复杂类型。如果每一行都只是 JSON，我们可以使用 JSON serde 并将类型指定为复杂类型 - 但据我所知，TSV 的 serde 不支持复杂类型(也不支持嵌入式 JSON(。

要从 JSON 数据中提取属性，我们可以使用 JSON 函数，并且UNNEST每个元素创建行。您可能是在追求两者的结合，例如：

SELECT
id,
JSON_EXTRACT_SCALAR(element, '$.addedtitle') AS addedtitle,
JSON_EXTRACT_SCALAR(element, '$.recvalue') AS recvalue,
FROM my_table, UNNEST (JSON_PARSE(data) as ARRAY(JSON)) AS t(element)

鉴于您问题中的数据，这将返回：

id   | addedtitle    | recvalue
-----+---------------+----------------------
dv1  | apple         | 0.899999999
dv1  | Orange        | 18.218999999999998
dv1  | Lemon         | 33.6
dv1  | Calcium       | 88.72002
dv1  | Mango         | 11.7
dev2 | apple (12%)   | 0.88034375
dev2 | Orange(31.4%) | 1.83733225
dev2 | Tree          | 1.69
dev5 | salt          | 0.052500000000000005
dev6 | Sugar         | 2.4000000000000004

请注意，上述假设数据列是有效的 JSON，从您的问题来看，情况并非如此。数据看起来不像是雅典娜支持的格式。