search

nazgul33 / impala-get-json-object-udf

A UDF for Cloudera Impala ( hive get_json_object equivalent )
32 stars 25 forks source link

get_json_object in where condition #6

Closed chaogehello closed 6 years ago

chaogehello commented 6 years ago

table data like this:

user_id real_name auth_status extend_info  
20005140 d3 3 {"kill": false, "memberType": 1}
20004911 d34 3 {"kill": false, "memberType": 1}
20005136 d44 3 {"kill": false, "killTime": "2018-02-10 10:10:54", "memberType": 3, "memberExpireTime": "2024-02-28 00:00:00"}
20004905 autotest 3 {"kill": false, "killTime": "2018-03-23 00:00:00", "memberType": 1}
20005133 autotest2 3 {"kill": false, "memberType": 1}

correctly sql： select c1.username,c1.real_name,nvl2(c2.username,'0','1') as total,c2.user_id,c2.nn from consignor c1 left outer join (select user_id,username, json_get_object(extend_info,'$.kill') as nn from consignor ) c2 on c1.user_id=c2.user_id where c2.username is NULL;

incorrectly sql: At the same time,if i run this sql,impala-deamon crushing.

select c1.username,c1.real_name,nvl2(c2.username,'0','1') as total,c2.user_id,c2.nn from consignor c1 left outer join (select user_id,username, json_get_object(extend_info,'$.kill') as nn from consignor where json_get_object(extend_info,'$.kill')='false' ) c2 on c1.user_id=c2.user_id where c2.username is NULL;

error message : Could not connect to AvatarTest2:21050 (code THRIFTTRANSPORT): TTransportException('Could not connect to AvatarTest2:21050',)

avatartest is my computer's hostname

it seems like "json function" can not in where condition??

BASE ON : CDH 14.2 HUE 3.9 IMPALA 2.11.0