HIVE处理JSON数据：GET_JSON_OBJECT、JSON_TUPLE、JSONFILE

概述

• JSON(JavaScript Object Notation, JS 对象简谱)
  是一种轻量级的数据交换格式
  采用完全独立于编程语言的文本格式来存储和表示数据

• 样例

{
    "is_del": true,
    "id": {
        "id": "a1"
    },
    "p": [{
        "hp": 9,
        "mp": 7
    }, {
        "hp": 8,
        "mp": 8
    }]
}

解析JSON的函数

• HIVE版本：3.1.2

CREATE OR REPLACE VIEW v AS
SELECT '{"is_del":true,"id":{"id":"Z"},"p":[{"hp":9,"mp":7},{"hp":8,"mp":8}]}' AS js;

GET_JSON_OBJECT

• 返回值类型是字符串
• 可深层解析

SELECT
  GET_JSON_OBJECT(js,'$.is_del'),
  GET_JSON_OBJECT(js,'$.id.id'),
  GET_JSON_OBJECT(js,'$.p[0].hp'),
  GET_JSON_OBJECT(js,'$.p[0].mp'),
  GET_JSON_OBJECT(js,'$.p[1].hp'),
  GET_JSON_OBJECT(js,'$.p[1].mp')
FROM v;

结果

JSON_TUPLE函数

• 只进行一次解析，比多次调用GET_JSON_OBJECT的效率高
• 是UDTF，可以和LATERAL VIEW结合使用
• 返回值类型是字符串
• 列名默认值：c0、c1、c2……
• 貌似只能解析1层

SELECT JSON_TUPLE(v.js,'is_del','id','p') FROM v;

结果

SELECT t.* FROM v
LATERAL VIEW JSON_TUPLE(v.js,'is_del','id','p')t AS a,b,c;

结果

JSON表

什么是JSON表

• JSON表是HIVE中的一种存储格式
  是JSON文件格式存储的

• 4.0.0版本建表语法

STORED AS JSONFILE

• 3.1.2版本建表语法

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
STORED AS TEXTFILE

示例

• 数据样貌

{"is_del":true,"id":{"id":"a1"},"p":[{"hp":9,"mp":7},{"hp":8,"mp":8}]}
{"is_del":false,"id":{"i":"a2"},"p":[{"hp":5,"mp":4},{"hp":3,"mp":3}]}

• 创建数据文件

echo '{"is_del":true,"id":{"id":"a1"},"p":[{"hp":9,"mp":7},{"hp":8,"mp":8}]}' >/tmp/a
echo '{"is_del":false,"id":{"i":"a2"},"p":[{"hp":5,"mp":4},{"hp":3,"mp":3}]}' >>/tmp/a
cat /tmp/a

• 创建JSON表，导入数据，查询数据

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(
  `is_del` STRING,
  `id`     MAP<STRING,STRING>,
  `p`      ARRAY<STRUCT<mp:INT,hp:INT>>)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe';
LOAD DATA LOCAL INPATH '/tmp/a' OVERWRITE INTO TABLE t1;
SELECT * FROM t1;