一、目的
在Hive的with嵌套语句时，HQL报错Line 2:5 Ambiguous table alias 't2'
二、报错详情
org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias 't2'文章来源地址https://www.toymoban.com/news/detail-807611.html
三、原SQL语句
with a2 as(
with t2 as(
select
get_json_object(event_json,'$.id') id,
get_json_object(event_json,'$.deviceNo') device_no,
get_json_object(event_json,'$.createTime') create_time,
get_json_object(event_json,'$.objList') obj_list
from hurys_dc_ods.ods_event)
select
get_json_object(list_json,'$.id') id,
t2.device_no, create_time,
get_json_object(list_json,'$.eventType') event_type,
''lane_no, ''speed,
get_json_object(list_json,'$.posX') pos_x,
get_json_object(list_json,'$.posY') pos_y,
''brand,''source_image, ''source_num, ''source_type, ''source_url, ''direction_radar,
''congestion_grade,''target_count, ''lane_no_original, ''event_type_detail,
get_json_object(list_json,'$.targetLen') target_len,
''queue_len,''queue_count,'' pos_head,''pos_tail,
date(t2.create_time) day
from t2
lateral view explode(split(regexp_replace(regexp_replace(obj_list,
'\\[|\\]','') ,
'\\}\\,\\{','\\}\\;\\{'),
'\\;')
)list_obj as list_json
where t2.obj_list is not null
group by t2.device_no, get_json_object(list_json,'$.id'), create_time, get_json_object(list_json,'$.eventType'), get_json_object(list_json,'$.posX'), get_json_object(list_json,'$.posY'), get_json_object(list_json,'$.targetLen'))
select
a2.id, device_no, create_time, event_type, lane_no, speed, pos_x, pos_y, brand, source_image, source_num, source_type, source_url, direction_radar, congestion_grade, target_count, lane_no_original, event_type_detail, target_len, queue_len, queue_count, pos_head, pos_tail, day
from a2
;
四、报错原因
看报错提示，Ambiguous table alias 't2'，似乎是with嵌套子语句命名t2报错，但是我试了很多其他命名，都报类似的错误，如果大家知道原因的话还望告知，谢谢！
五、解决方式
既然不能使用with嵌套子语句，那就换种方式。
（一）with语句等同与另一种SQL方式
1、原有SQL方式
> select w.word,count(1) num from
> (select explode(split(line,"\\s")) word from wordcount) w
> group by w.word order by num desc;
2、with语句
> with
> t1 as (select explode(split(line,"\\s")) word from wordcount)
> select t1.word,count(1) num from t1 group by word order by num desc;
（二）新的SQL如下
with a2 as (
select
get_json_object(list_json,'$.id') id,
t2.device_no, create_time,
get_json_object(list_json,'$.eventType') event_type,
''lane_no, ''speed,
get_json_object(list_json,'$.posX') pos_x,
get_json_object(list_json,'$.posY') pos_y,
''brand,''source_image, ''source_num, ''source_type, ''source_url, ''direction_radar,
''congestion_grade,''target_count, ''lane_no_original, ''event_type_detail,
get_json_object(list_json,'$.targetLen') target_len,
''queue_len,''queue_count,'' pos_head,''pos_tail,
date(t2.create_time) day
from ( select
get_json_object(event_json,'$.id') id,
get_json_object(event_json,'$.deviceNo') device_no,
get_json_object(event_json,'$.createTime') create_time,
get_json_object(event_json,'$.objList') obj_list
from hurys_dc_ods.ods_event) as t2
lateral view explode(split(regexp_replace(regexp_replace(obj_list,
'\\[|\\]','') ,
'\\}\\,\\{','\\}\\;\\{'),
'\\;')
)list_obj as list_json
where t2.obj_list is not null
group by t2.device_no, get_json_object(list_json,'$.id'), create_time, get_json_object(list_json,'$.eventType'), get_json_object(list_json,'$.posX'), get_json_object(list_json,'$.posY'), get_json_object(list_json,'$.targetLen'))
select
a2.id, device_no, create_time, event_type, lane_no, speed, pos_x, pos_y, brand, source_image, source_num, source_type, source_url, direction_radar, congestion_grade, target_count, lane_no_original, event_type_detail, target_len, queue_len, queue_count, pos_head, pos_tail, day
from a2;
六、检查新的SQL运行效果
执行成功！
虽然还是不清楚之前SQL报错的原因，但是换种方式能运行就行。
对于之前SQL报错的原因，如果大家知道的话还请告诉我，谢谢！文章来源：https://www.toymoban.com/news/detail-807611.html
org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias 't2'
到了这里，关于二百零九、Hive——with嵌套语句报错：hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias ‘t2‘的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
