cannot recognize input near ‘,‘ ‘deviceid‘ ‘,‘ in expression specification

人生苦短，我爱数据分析

于 2023-01-11 19:29:08 发布

阅读量2.8k

点赞数

文章标签： json mysql 数据库

本文链接：https://blog.csdn.net/weixin_38131197/article/details/128649741

版权

遇到问题：
把sql脚本放到shell里面跑报错，报错信息如下
cannot recognize input near ‘,’ ‘deviceid’ ‘,’ in expression specification

报错sql脚本：

hive -e"
use zx_user;
select a.pt,a.eventid,a.platform,a.sdk_from
,GET_JSON_OBJECT(GET_JSON_OBJECT(priv_ext,'$.ext'),'$.scene') as scene
,b.scene_name
,a.srcid
,sum(adcost) as adcost
,count(distinct concat(requestid,"-",deviceid,"-",srcid)) show_uv
,sum(adcost)/1000/100 as adcost2
  from zx_user.temp_lx_funcfion_base a
  left join adanalysis.dim_lianxin_scene b
    on GET_JSON_OBJECT(GET_JSON_OBJECT(a.priv_ext,'$.ext'),'$.scene')=b.scene_id
 where a.pt>='2022-11-01' and a.pt<='2022-11-30'
 group by a.pt,a.eventid,a.platform,a.sdk_from
,GET_JSON_OBJECT(GET_JSON_OBJECT(priv_ext,'$.ext'),'$.scene')
,b.scene_name
,a.srcid;
">temp_lx_funcfion_base_nov.txt
sz temp_lx_funcfion_base_nov.txt

解决办法：“” 修改为 ‘’
concat(requestid,“-”,deviceid,“-”,srcid)
修改为
concat(requestid,‘-’,deviceid,‘-’,srcid)

修改后可执行代码：

hive -e"
use zx_user;
select a.pt,a.eventid,a.platform,a.sdk_from
,GET_JSON_OBJECT(GET_JSON_OBJECT(priv_ext,'$.ext'),'$.scene') as scene
,b.scene_name
,a.srcid
,sum(adcost) as adcost
,count(distinct concat(requestid,'-',deviceid,'-',srcid)) show_uv
,sum(adcost)/1000/100 as adcost2
  from zx_user.temp_lx_funcfion_base a
  left join adanalysis.dim_lianxin_scene b
    on GET_JSON_OBJECT(GET_JSON_OBJECT(a.priv_ext,'$.ext'),'$.scene')=b.scene_id
 where a.pt>='2022-11-01' and a.pt<='2022-11-30'
 group by a.pt,a.eventid,a.platform,a.sdk_from
,GET_JSON_OBJECT(GET_JSON_OBJECT(priv_ext,'$.ext'),'$.scene')
,b.scene_name
,a.srcid;
">temp_lx_funcfion_base_nov.txt
sz temp_lx_funcfion_base_nov.txt