json-serde解析数组结构问题

cvxl0en2  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(437)

我的json对象:

{
    "_total": 4,
    "values": [
        {
            "total-follower-count": 63911,
            "organic-follower-count": 6000,
            "paid-follower-count": 3911,
            "time": 1399334400000
        },
        {
            "total-follower-count": 64023,
            "organic-follower-count": 6000,
            "paid-follower-count": 4023,
            "time": 1399420800000
        },
        {
            "total-follower-count": 64023,
            "organic-follower-count": 6000,
            "paid-follower-count": 4023,
            "time": 1399507200000
        },
        {
            "total-follower-count": 64048,
            "organic-follower-count": 6000,
            "paid-follower-count": 4048,
            "time": 1399593600000
        }
    ]
}

在aws雅典娜,我创建了一个如下表。

CREATE EXTERNAL TABLE IF NOT EXISTS Linkedindata 
(
  `_total` INT,
   values array<struct<total_follower_count:INT,
                       organic_follower_count:INT,
                       paid_follower_count:INT>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  "mapping.total_follower_count"="total-follower-count",
  "mapping.organic_follower_count"="organic-follower-count", 
  "mapping.paid_follower_count"="paid-follower-count" 
  )
LOCATION 's3://mybucket/'
TBLPROPERTIES ('has_encrypted_data'='false')

我无法访问“total\u count”之类的数组值。你能帮忙吗。谢谢

e4yzc0pl

e4yzc0pl1#

你需要展平你的阵列,并在上面执行你的选择。
这里有明确的例子,
http://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
希望有帮助。

相关问题