创建 sql 表并在 json 数组中读取值

Question

我写了一个基于json文件生成表的查询。json 文件有一个小节是 json 数组，我似乎无法让我的表从 json 数组中获取值。

这是我尝试从中创建表的 json 示例：

{"student": "1", "ai": [{"grade": "a", "term": 1}, {"grade": "b", "term": 2}], "year": "2017"}

桌子：

CREATE EXTERNAL TABLE student(
  student string COMMENT 'from deserializer', 
  grade string COMMENT 'from deserializer', 
  term string COMMENT 'from deserializer', 
  year string COMMENT 'from deserializer')
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
LOCATION 'students.json'

我也尝试做ai.grade但没有帮助。

我得到的当前输出是： 学生年级学期年 1 2017 1 2017

我想要的是：

student      grade      term      year
1            a          1         2017
1            b          2         2017

请参阅图片，因为上面的结构可能没有显示。

Answer 1

你应该能够使用以下 Hive 语法创建表：

CREATE EXTERNAL TABLE student(
  student string COMMENT 'from deserializer', 
  ai array<struct<grade:string,tinyint>> COMMENT 'from deserializer', 
  year tinyint COMMENT 'from deserializer')
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
LOCATION 'students.json'

获得表后，可以使用以下 Presto 查询，该查询将数组UNNEST为列。 请注意，示例查询中的 WITH 子句仅用于模拟上面的student表，创建student表后应将其删除。

WITH student AS (
    SELECT
        1 AS student,
        CAST(ARRAY[ROW('a', 1), ROW('b', 2)] AS ARRAY(ROW(grade VARCHAR, term TINYINT))) AS ai,
        2017 AS year
)
SELECT student, grade, term, year 
FROM student 
  CROSS JOIN UNNEST(ai)

 student | grade | term | year 
---------+-------+------+------
       1 | a     |    1 | 2017 
       1 | b     |    2 | 2017 
(2 rows)