Hive exists使用出现的问题分析

2019独角兽企业重金招聘Python工程师标准>>>

两种类型表：

dw_v_order   ->可以删除和添加

dw_v_user     ->可以删除和添加

src_t_user      ->普通的文本格式

测试 文本和删除添加的表  在删除和查询 使用exists是  表名+字段关联和别名+字段，那些能够成功。

1.dw_v_order 为主

delete from dw_v_order where exists(select 1 from dw_v_user uu where 1=2 
and dw_v_order.userid=uu.userid);
成功

delete from dw_v_order o where exists(select 1 from dw_v_user uu where 1=2 
and o.userid=uu.userid);
失败

select * from dw_v_order where exists(select 1 from dw_v_user uu where 1=2 
and dw_v_order.userid=uu.userid);
成功

select * from dw_v_order o where exists(select 1 from dw_v_user uu where 1=2 
and o.userid=uu.userid);
失败

2.src_t_user为主：

select * from src_t_user where exists(select 1 from dw_v_user uu where 1=2 
and src_t_user.id=uu.userid);
失败

select * from src_t_user o where exists(select 1 from dw_v_user uu where 1=2 
and o.id=uu.userid);
成功
 

结论：能删除的表为主：exists关联时必须是表名+字段

          文本的表为主：exists关联是必须是别名+字段

建表语句：

create table src_t_user (

id int comment '用户id'

)comment '用户表'

partitioned by(year string comment'按年分区')

row format delimited

fields terminated by '\t'

stored as textfile;

 

create table dw_v_order(
orderid int comment '主键id',
ordertypeid int comment '订单类型',
parentaliasid string comment '父订单号',
aliascode string comment '订单号',
userid int comment '用户'
)comment '订单表'
partitioned by(year string comment'按年分区')
clustered by (orderid) into 2 buckets 
stored as orc TBLPROPERTIES('transactional'='true'); 
alter table dw_v_order add partition(year='2012');
alter table dw_v_order add partition(year='2013');
alter table dw_v_order add partition(year='2014');
alter table dw_v_order add partition(year='2015');
alter table dw_v_order add partition(year='2016');
alter table dw_v_order add partition(year='2017');

create table dw_v_user ( 
userid int comment '用户id'
)comment '用户表'
partitioned by(year string comment'按年分区')
clustered by (userid) into 2 buckets 
stored as orc TBLPROPERTIES('transactional'='true'); 
alter table dw_v_user add partition(year='2012');
alter table dw_v_user add partition(year='2013');
alter table dw_v_user add partition(year='2014');
alter table dw_v_user add partition(year='2015');
alter table dw_v_user add partition(year='2016');
alter table dw_v_user add partition(year='2017');

 

转载于:https://my.oschina.net/ilovetao/blog/1556225