DataBaser.Net: Impala

1 get_json_object - java
2 get_json_object - c++
3 json split

1 get_json_object - java #

json 지원이 안 된다고 말하기는 좀 그렇다..
udf를 만들어 쓰면 된다.

CDH5.12 quckstart 를 기준으로..
os쉘에서..

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

impala쉘에서..

이렇게 하면 임시 함수

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] >

[edit]

2 get_json_object - c++ #

https://github.com/cloudera/impala-udf-samples

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로 실행시킨 것과

건수가 다른 것을 확인(json 배열을 split하는 쿼리였다)하였다.

[edit]

3 json split #

배열의 길이를 알아내는 약간의 꽁수(?)를 써서 해결한다. 기다리다보면 누가 udf 만들겠지 뭐..

test 테이블이 있다.

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"}]');

dummy 테이블이 있다.
물론 dummy 테이블은 1,2,3, ..... 10000 까지 있다. 여기서는 귀찮으니 2개만..

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

udf가 있다.

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 --이걸 꼭 써야 한다.
;

2018-11-10 실제 상황

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*\{', '')

주의해야 할 것은 \s가 아니라 \\s라는 것..

결과

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

정규표현식 Online: https://regexr.com/

Contents

1 get_json_object - java #

2 get_json_object - c++ #

3 json split #