二百零九、Hive——with嵌套语句报错：hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias ‘t2‘

一、目的

在Hive的with嵌套语句时，HQL报错Line 2:5 Ambiguous table alias 't2'

二、报错详情

org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias 't2'

三、原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报错的原因，如果大家知道的话还请告诉我，谢谢！

org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:5 Ambiguous table alias 't2'