1 get_json_object - java

2 get_json_object - c++

3 json split

hadoop fs -put /usr/lib/hive/lib/hive-exec-1.1.0-cdh5.12.0.jar /tmp

create function get_json_object(string, string) returns string 
location '/tmp/hive-exec-1.1.0-cdh5.12.0.jar' symbol='org.apache.hadoop.hive.ql.udf.UDFJson';

create function get_json_object
location '/tmp/hive-exec-1.1.0-cdh5.12.0.jar' symbol='org.apache.hadoop.hive.ql.udf.UDFJson';

create table test1(payload string);
insert into test1 values('{"product_id":220003038067,"timestamps":"1340321132000"}');

select
   payload
,  get_json_object(payload, '$.product_id') product_id
from test1;

[quickstart.cloudera:21000] > select
                            >    payload
                            > ,  get_json_object(payload, '$.product_id') product_id
                            > from test1;
Query: select payload
,  get_json_object(payload, '$.product_id') product_id
from test1
Query submitted at: 2018-02-28 07:32:40 (Coordinator: http://quickstart.cloudera:25000)
Query progress can be monitored at: http://quickstart.cloudera:25000/query_plan?query_id=b04f737db6c69282:5e9d56c300000000
+----------------------------------------------------------+--------------+
| payload                                                  | product_id   |
+----------------------------------------------------------+--------------+
| {"product_id":220003038067,"timestamps":"1340321132000"} | 220003038067 |
+----------------------------------------------------------+--------------+
Fetched 1 row(s) in 0.30s
[quickstart.cloudera:21000] >

root 로 
sudo yum install gcc-c++ cmake boost-devel 
sudo yum install impala-udf-devel 

mkdir json_udf 
cd json_udf 
cmake /root/impala-get-json-object-udf-master/ 
make 
cd build 

mv libjsonUdf.so /home/hdfs/libjsonUdf.so 
cd /home/hdfs 
chown hdfs:hdfs libjsonUdf.so 

su hdfs 
hadoop fs -mkdir /user/hive/udfs hadoop fs -put -f libjsonUdf.so /user/hive/udfs 

create function lostark.get_json_object2(string, string) returns string location '/user/hive/udfs/libjsonUdf.so' symbol='JsonGetObject';

• java 버전의 get_json_object로 실행시킨 것과
  
• c++ 버전의 get_json_object로 실행시킨 것과
  

drop table test;

create table test(id int, payload string);
insert into test values(1, '[{"key1":1,"key2":"aaa"},{"key1":2,"key2":"bbb"}]');
insert into test values(2, '[{"key1":3,"key2":"ccc"},{"key1":4,"key2":"ddd"}]');

create table dummy(seq int);
insert into dummy values (1),(2);

create function get_json_object(string, string) returns string location '/tmp/hive-exec-1.1.0-cdh5.12.0.jar' symbol='org.apache.hadoop.hive.ql.udf.UDFJson';

select
    a.id
,   a.payload
,   (length(payload) - length(regexp_replace(payload, '\}\\s*,\\s*\{', '')))/3 + 1 array_size
,   b.seq
from test a
    inner join dummy b
        on b.seq <= (length(a.payload) - length(regexp_replace(a.payload, '\}\\s*,\\s*\{', '')))/3 + 1
where b.seq <= 100 --이걸 꼭 써야 한다.
;

• row수가 늘어나면 정말 느렸다.
  
• dummy테이블에 1000개의 row를 만들었다.
  
• 그랬더니 테이블*1000개의 row를 생성 후 filtering 하는 거였다. ㅎ 어처구니 없게..
  
• where절에 적당한(?) dummy row수를 줘야 한다. seq <= 100 처럼..
  
  
  

+----+---------------------------------------------------+------------+-----+
| id | payload                                           | array_size | seq |
+----+---------------------------------------------------+------------+-----+
| 1  | [{"key1":1,"key2":"aaa"},{"key1":2,"key2":"bbb"}] | 2          | 1   |
| 1  | [{"key1":1,"key2":"aaa"},{"key1":2,"key2":"bbb"}] | 2          | 2   |
| 2  | [{"key1":3,"key2":"ccc"},{"key1":4,"key2":"ddd"}] | 2          | 1   |
| 2  | [{"key1":3,"key2":"ccc"},{"key1":4,"key2":"ddd"}] | 2          | 2   |
+----+---------------------------------------------------+------------+-----+

select
  id
, get_json_object(payload, '$.key1') key1_value
, get_json_object(payload, '$.key2') key2_value
from (
  select
  id
  , seq
  , get_json_object(payload, concat('$.[', cast(seq-1 as string), ']')) payload
  from (
    select
    a.id
    ,   a.payload
    ,   b.seq
    from test a
    inner join dummy b
    on b.seq <= (regexp_replace(a.payload, '\}\\s*,\\s*\{', '},{') - length(regexp_replace(regexp_replace(a.payload, '\}\\s*,\\s*\{', '},{'), '},{', '')))/3 + 1
  ) t
) t;

select regexp_replace('}, {', '\}\\s*,\\s*\{', '')

+----+------------+------------+
| id | key1_value | key2_value |
+----+------------+------------+
| 2  | 3          | ccc        |
| 2  | 4          | ddd        |
| 1  | 1          | aaa        |
| 1  | 2          | bbb        |
+----+------------+------------+