odps sql 删除、更新、插入数据

ODPS不支持直接通过delete语句删除数据

假设有表a有字段

col_1	col_2	col_3	col_4	ds

ds类型为string,已有数据20191201 到20191231

若表a为非分区表

TRUNCATE 清空全部数据

TRUNCATE TABLE a;

清空全部数据，保留表结构

INSERT OVERWRITE 清空全部数据

INSERT OVERWRITE TABLE a SELECT FROM a WHERE 1=2;

INSERT OVERWRITE 删除部分数据

• 如要删除a表里的Col=1的数据，使用如下命令即可

INSERT OVERWRITE TABLE a SELECT FROM a WHERE Col <> 1;

• 删除20号（含）以后的数据

insert overwrite table a 
select *
from a 
where ds<20191220;

• 删除10号(含）以前的数据

insert into table a 
select *
from a 
where ds>=20191211;

• 删除10号（含）到20号（含） 的数据

insert overwrite table a
select *
from a 
where ds<20191210
union all 
select *
from a 
and ds>20191220;

若表a为分区表

• drop
  只需要删除对应的分区即可删除对应的数据:

ALTER TABLE table_name DROP [IF EXISTS] PARTITION (partition_spec);
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

• insert overwrite
• • 删除某个分区全部数据

INSERT OVERWRITE TABLE a PARTITION(ds= '20191231')
SELECT  col_1
        ,col_2
        ,col_3
        ,col_4
        ,col_5
FROM    a
WHERE   1 = 2
AND     ds = '20191231'
;

• • 删除a表某分区里col_1=123的数据

insert overwrite table a partition (ds=20191231') 
select col_1
        ,col_2
        ,col_3
        ,col_4
        ,col_5 
from a
where col_1<>123
 and ds='20191231';

更新数据 分区表

一般用来更新表，比如某段时间的数据有错误，就要这种方法进行更新

• 更新全部数据

insert overwrite table a PARTITION(ds)
select *
from b 
where ds>0

• 更新20号（含）以后的数据

insert overwrite table a PARTITION(ds)
select *
from b 
where ds>=20191220;

• 更新10号(含）以前的数据

insert overwrite table a PARTITION(ds)
select *
from b 
where ds<=20191210;

• 更新10号（含）到20号（含） 的数据

insert overwrite table a PARTITION(ds)
select *
from b 
where ds between 20191210 and 20191220;

插入数据

• 将b表12月10号到20号的数据插入到a 表中

• • 如果a是非分区表,a表就仅剩下插入的数据

insert overwrite table a
select *
from b
where ds between 20191210
and 20191220;

• • 如果a 是分区表，a表中10号到20 号的数据是b表的，并且不影响a表中其他日期的数据

insert overwrite table a partition (ds) 
select *
from b
where ds between 20191210
and 20191220;

注意：

• ** 插入时要保证b表和a表的对应的字段名和类型保持一致**
• 对分区表插入有两种情况,注意这两种情况的区别

INSERT OVERWRITE TABLE a PARTITION(ds)
SELECT   col_1
        ,col_2
        ,col_3
        ,col_4
        ,col_5 
        ,ds 
FROM    temp_test_b
WHERE  ds = '20191231'
;

INSERT OVERWRITE TABLE a PARTITION(ds='20191231')
SELECT   col_1
        ,col_2
        ,col_3
        ,col_4
        ,col_5 
FROM    temp_test_b
WHERE  ds = '20191231'
;

参考资料：

https://help.aliyun.com/knowledge_detail/150534.html?spm=5176.13910061.0.0.6e709179ssnlIl&aly_as=CvQU-xjIb#title-l37-w96-zdf