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