HIVE: lateral view explode & json_turpeはjson配列の行転列&フィールド分割を実現する

問題の説明
ビジネスの必要性のため、jsonフォーマットだけでなく、次の表pay_のようなjson配列のフィールドもあります.infos:
pay_id
infos
1111
[{"uid":123,"terminalFrom":0,"couponBatchId":1410115799,"cost":5},{"uid":123,"terminalFrom":0,"couponBatchId":1410116199,,"cost":7,}]
1112
[{"uid":124,"terminalFrom":1,"couponBatchId":1410115799,"cost":20}]
統計の必要性のために、各jsonの具体的なkeyとvalueを解析する必要があります.例えば、上記の表では、すべてのcouponBatchId分類のcostの値を要求する必要があります.
解決策

explode

infosフィールドでは、まず配列の行転列問題を解決する必要があります.これはexplode関数に渡されます.

select 
  explode(split(substring(infos,3,length(coupon_enties)-4),'\\},\\{'))  as entity
from pay_infos

explodeによるinfosの一度の処理は、単一jsonの文字列に従って複数行に変換され、以下のようになる.
entity
"uid":123,"terminalFrom":0,"couponBatchId":1410115799,"cost":5
"uid":123,"terminalFrom":0,"couponBatchId":1410116199,,"cost":7
"uid":124,"terminalFrom":1,"couponBatchId":1410115799,"cost":20
他のフィールド情報を保持する必要がある場合はlateral viewを使用します.

select pay_id,concat('{',entity,'}') as coupons
from
(       select * 
       from pay_infos
) a 
lateral view explode(split(substring(infos,3,length(infos)-4),'\\},\\{')) b as entity

結果は次のとおりです.
pay_id
coupons
1111
"uid":123,"terminalFrom":0,"couponBatchId":1410115799,"cost":5
1111
"uid":123,"terminalFrom":0,"couponBatchId":1410116199,,"cost":7
1112
"uid":124,"terminalFrom":1,"couponBatchId":1410115799,"cost":20

json_turpe

key:value形式の情報は、完成したjsonにつづって使用できます.json_turpe、つまり行転列、例えば上から出力された表をsourceと呼び、栗SQLは以下の通りです.

select info.*
from 
(
  select concat('{',entity,'}') as coupons
  from source
  lateral view json_tuple(coupons,'couponBatchId','terminalFrom','cost','uid') info 
  as couponBatchId,terminalFrom,cost,uid

couponBatchId
terminalFrom
cost
uid
1410115799
0
5
123
1410116199
0
7
123
1410115799
1
20
124
結果は次のとおりです.
couponBatchId
terminalFrom
cost
uid
1410115799
0
5
123
1410116199
0
7
123
1410115799
1
20
124
フルSQL

select info.*
from 
(
  (select 
  pay_id,concat('{',entity,'}') as coupons
  from
  (       
    select *  from  pay_infos
  ) a 
  lateral view explode(split(substring(infos,3,length(infos)-4),'\\},\\{')) b as entity) source
  lateral view json_tuple(coupons,'couponBatchId','terminalFrom','cost','uid') info 
  as couponBatchId,terminalFrom,cost,uid

説明

concatは、完全なjson

を補うために使用される.

splitの場合、セパレータはエスケープする必要があります.sqlが「」に書かれている場合(shellスクリプトでhive-e「$sql」を呼び出す場合など)、4つの''エスケープ特殊文字、すなわちsplit(infos,'\\\\},\\\\{')

が必要です.