一、timestamp字段與int字段相加

（一）場景

change_time字段是timestamp字段，代表一個紅綠燈周期的開始時間（先是綠燈、再是黃燈、最后紅燈），而green是int字段，代表綠燈的秒數(shù)，現(xiàn)在要求出綠燈的結束時間。即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插入數(shù)據(jù)。如果直接把insert放在with語句上面，那么就會如下報錯

（二）報錯

org.apache.hadoop.hive.ql.parse.ParseException:line 2:0 cannot recognize input near 'with' 'a1' 'as' in statement

（三）解決方法

把insert放在with的后面，select的前面文章來源地址http://www.zghlxwxcb.cn/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運行報錯（持續(xù)更新中）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！