json - 用于复杂嵌套 Json 的 Hive

Question

我有一个原始输入 json 片段('/home/user/testsample.json')-

{"key": "somehashvalue","columns": [["Event:2014-03-26 00\\:29\\:13+0200:json","{\"user\":{\"credType\":\"ADDRESS\",\"credValue\":\"01:AA:A4:G1:HH:UU\",\"cAgent\":null,\"cType\":\"ACE\"},\"timestamp\":1395786553,\"sessionId\":1395785353,\"className\":\"Event\",\"subtype\":\"CURRENTLYACTIVE\",\"vType\":\"TEST\",\"vId\":1235080,\"eType\":\"CURRENTLYACTIVE\",\"eData\":\"1\"}",1395786553381001],["Event:2014-03-26 00\\:29\\:13+0200:","",1395786553381001]]}

我尝试使用 Json serde 将上述 json 解析到我的配置单元列。但是，上面的 1395786553381001 没有以 SerDe 可以映射到 Hive 列的格式存在，即这个值在没有 Key 的情况下存在(因为 Hive 理解 Json 列/值出现在 :)

所以我改为采用 Array 类型方法并创建了一个表 -

CREATE TABLE mytesttable (
  key string, 
  columns array < array< string > >
  )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

LOAD DATA LOCAL INPATH '/home/user/testsample.json'
OVERWRITE INTO TABLE mytesttable;

从 mytesttable 中选择列 [0][1]；给-

{"user":{"credType":"ADDRESS","credValue":"01:AA:A4:G1:HH:UU","cAgent":null,"cType":"ACE"},"timestamp":1395786553,"sessionId":1395785353,"className":"Event","subtype":"CURRENTLYACTIVE","vType":"TEST","vId":1235080,"eType":"CURRENTLYACTIVE","eData":"1"}

上面看起来很干净，但我还需要列 [*][2]，即在 Json 配置单元列中进行进一步转换。

我编写了一个正则表达式配置单元查询来清理 '/home/user/testsample.json' 中存在的原始 Json(假设它存在于表 tablewithinputjson 中)

SELECT
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(ij.columna, '["][{]', '{'),'[}]["]', '}'), '\\\\', '') AS columna
FROM tablewithinputjson ij;

上面的查询返回-

{"key": "somehashvalue","columns": [["Event:2014-03-26 00:29:13+0200:json",{"user":{"credType":"ADDRESS","credValue":"01:AA:A4:G1:HH:UU","cAgent":null,"cType":"ACE"},"timestamp":1395786553,"sessionId":1395785353,"className":"Event","subtype":"CURRENTLYACTIVE","vType":"TEST","vId":1235080,"eType":"CURRENTLYACTIVE","eData":"1"},1395786553381001],["Event:2014-03-26 00:29:13+0200:","",1395786553381001]]}

但在这里，1395786553381001 不能映射到配置单元列，因为它出现在 之后，而不是 : 之后，或者更具体地说，这个值在没有键的情况下存在。(我可以添加“测试”:在 1395786553381001 之前，但我不想自定义输入数据 - 因为 a)太多的自定义是我不满意的 b)似乎不是一个好的解决方案 c)它没有必要浪费我的 hadoop 集群空间和时间)

不要再混淆了，我无法想出一个 Hive 表格式来完全解析和映射原始 Json 片段中的所有字段。欢迎提出任何建议。如果看起来太困惑，请告诉我。

Answer 1

发布端到端解决方案。将 JSON 转换为 Hive 表的分步过程:

步骤 1) 如果还没有安装 maven

>$ sudo apt-get install maven

步骤 2) 安装 git(如果还没有的话)

>sudo git clone https://github.com/rcongiu/Hive-JSON-Serde.git

第 3 步)进入 $HOME/HIVE-JSON_Serde 文件夹

第 4 步)构建 serde 包

>sudo mvn -Pcdh5 clean package

步骤 5) serde 文件将位于 $HOME/Hive-JSON-Serde/json-serde/target/json-serde-1.3.7-SNAPSHOT-jar-with-dependencies.jar

step 6) 在hive中添加serde作为依赖jar

 hive> ADD JAR $HOME/Hive-JSON-Serde/json-serde/target/json-serde-1.3.7- SNAPSHOT-jar-with-dependencies.jar;

第 7 步)在 $HOME/books.json 中创建 json 文件(示例)

{"value": [{"id": "1","bookname": "A","properties": {"subscription": "1year","unit": "3"}},{"id": "2","bookname":"B","properties":{"subscription": "2years","unit": "5"}}]}

步骤 8) 在 hive 中创建 tmp1 表

 hive>CREATE TABLE tmp1 (
      value ARRAY<struct<id:string,bookname:string,properties:struct<subscription:string,unit:string>>>   
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 
    'mapping.value' = 'value'   
) 
STORED AS TEXTFILE;

第9步)将数据从json加载到tmp1表

>LOAD DATA LOCAL INPATH '$HOME/books.json' INTO TABLE tmp1;

step 10) create a tmp2 table to do explode operation form tmp1, 这个中间步骤是将多级json结构分解成多行注意:如果你的 JSON 结构简单且单层，请避免这一步

hive>create table tmp2 as 
 SELECT *
 FROM tmp1
 LATERAL VIEW explode(value) itemTable AS items;

步骤 11) 创建 hive 表并从 tmp2 表加载值

hive>create table books as 
select value[0].id as id, value[0].bookname as name, value[0].properties.subscription as subscription, value[0].properties.unit as unit from tmp2;

第 12 步)删除 tmp 表

hive>drop table tmp1;
hive>drop table tmp2;

第13步)测试hive表

hive>select * from books;

输出:

id name 订阅单位

1 B 1 年 3

2 B 2 年 5