1. 데이터 준비(vi json.dat)

{"country":"US","page":227,"data":{"impressions":{"s":10,"o":10}}}}

{"country":"US2","page":228,"data":{"impressions":{"s":11,"o":13}}}}

2. HDFS에 로딩

hdfs dfs -put ./json.dat /tmp/json.dat 

3. table 생성

create table hive_parsing_json_table(json string);

4. data를 테이블에 입력

load data inpath '/tmp/json.dat' into table hive_parsing_json_table;

5. select v1.Country, v2.Page, v4.impressions_s, v4.impressions_o

from hive_parsing_json_table hpjp

lateral view json_tuple(hpjp.json, 'country', 'page', 'data') v1 as Country, Page, data

lateral view json_tuple(v1.data, 'ad') v2 ad Ad

lateral view json_tuple(v2.Ad, 'impressions') v3 as Impressions

lateral view json_tuple(v3.Impressions, 's', 'o') v4 as impressions_s, impressions_o;

6. 결과

v1.country v1.page v4.impressions_s v4.impressions_o

US           227      10                   10

US2          228      11                  13