Hive侧视图不工作aws雅典娜

zwghvu4y 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(341)

我正在进行aws cloudtrail日志分析，我陷入了从一行提取json的困境，
这是我的表定义。

CREATE EXTERNAL TABLE cloudtrail_logs (
eventversion STRING,
eventName STRING,
awsRegion STRING,
requestParameters STRING,
elements STRING  ,
additionalEventData STRING
)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://XXXXXX/CloudTrail'

如果我跑了 select elements from cl1 limit 1 它返回这个结果。

{"groupId":"sg-XXXX","ipPermissions":{"items":[{"ipProtocol":"tcp","fromPort":22,"toPort":22,"groups":{},"ipRanges":{"items":[{"cidrIp":"0.0.0.0/0"}]},"prefixListIds":{}}]}}

我需要将这个结果显示为虚拟列，比如，

| groupId | ipProtocol | fromPort | toPort| ipRanges.items.cidrIp|
|---------|------------|--------- | ------|-----------------------------|
| -1      | 0          |          |       |                             |

我使用的是aws雅典娜，我尝试了横向视图，得到的对象在aws中不工作。
这是一张外桌

Hive JSON Arrays amazon-web-services amazon-athena

来源：https://stackoverflow.com/questions/42879657/hive-lateral-view-not-working-aws-athena

1条答案

按热度按时间

7vux5j2d1#

select  json_extract_scalar(i.item,'$.ipProtocol')  as ipProtocol
       ,json_extract_scalar(i.item,'$.fromPort')    as fromPort
       ,json_extract_scalar(i.item,'$.toPort')      as toPort

from    cloudtrail_logs
        cross join unnest (cast(json_extract(elements,'$.ipPermissions.items') 
            as array(json))) as i (item)
;

ipProtocol | fromPort | toPort
------------+----------+--------
 "tcp"      | 22       | 22

赞(0）回复(0）举报 2021-06-26

我来回答

Hive侧视图不工作aws雅典娜

1条答案

相关问题

热门标签

最新问答