一、timestamp字段与int字段相加
(一)场景
change_time字段是timestamp字段,代表一个红绿灯周期的开始时间(先是绿灯、再是黄灯、最后红灯),而green是int字段,代表绿灯的秒数,现在要求出绿灯的结束时间。即change_time字段+green字段
(二)timestamp字段与int字段无法直接相加,可以先把change_time字段转为时间戳,然后和green字段相加,最后再转为日期
样例:from_unixtime(unix_timestamp(change_time, 'yyyy-MM-dd HH:mm:ss') + green) AS new_timestamp
(三)SQL语句
成功!!!
二、with语句与insert结合使用
(一)场景
在DWS层中,对多层SQL使用with语句嵌套查询,然后insert插入数据。如果直接把insert放在with语句上面,那么就会如下报错
(二)报错
org.apache.hadoop.hive.ql.parse.ParseException:line 2:0 cannot recognize input near 'with' 'a1' 'as' in statement文章来源:https://www.toymoban.com/news/detail-615715.html
(三)解决方法
把insert放在with的后面,select的前面文章来源地址https://www.toymoban.com/news/detail-615715.html
(四)SQL语句
with a1 as( select b1.site_id, b1.phase_id, b1.phase_start, b1.program_id, b1.lane_direction, b1.device_direction, b1.min_gree_end, b1.phase_end, b1.team_id, b1.name, t8.device_no, t9.lane_num lane_no from dws_pass as b1 left join hurys_dc_dwd.dwd_radar_config as t8 on t8.direction=b1.device_direction and t8.device_no=b1.device_no --得到真正的雷达编号字段 left join hurys_dc_dwd.dwd_radar_lane as t9 on t9.device_no=b1.device_no and t9.lane_direction=b1.lane_direction --得到车道编号字段 group by b1.site_id, b1.phase_id, b1.phase_start, b1.program_id, b1.lane_direction, b1.device_direction, b1.min_gree_end, b1.phase_end, b1.team_id, b1.name, t8.device_no, t9.lane_num) insert overwrite table dws_pass_sparetime_1hour partition(day) select a1.site_id, phase_id, program_id, phase_start, min_gree_end, phase_end, a1.device_no, team_id, name, t10.create_time, concat(substr(create_time, 1, 14), '00:00') start_time, a1.lane_no, section_no, coil_no, device_direction direction, lane_direction, target_id, target_type, drive_in_time, day from a1 left join hurys_dc_dwd.dwd_pass as t10 on t10.device_no=a1.device_no and t10.lane_no=a1.lane_no and t10.create_time between a1.min_gree_end and a1.phase_end group by a1.site_id, phase_id, program_id, phase_start, min_gree_end, phase_end, a1.device_no, team_id, name, t10.create_time, a1.lane_no, section_no, coil_no, device_direction, lane_direction, target_id, target_type, drive_in_time, day ;
到了这里,关于一百三十七、Hive——HQL运行报错(持续更新中)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!