SQL CRUD（阿里ODPS版）

增

拷贝表

CREATE TABLE table2 AS SELECT * FROM table1 WHERE column1 = 'value1';

分区

• 增加分区

alter table table_name add if not exists partition(dt='20230301');

插入数据

INSERT INTO TABLE table_name1 partition (dt = '20230305')
select store_id,item_id from table_name2 limit 10;

添加字段（列）

https://help.aliyun.com/apsara/enterprise/v_3_14_0_20210519/odps/enterprise-ascm-user-guide/add-column.html?spm=a2c4g.14484438.10004.3

Alter table table_name 
add columns (col1 string COMMENT '类目');

删

删除表

https://help.aliyun.com/apsara/enterprise/v_3_14_0_20210519/odps/enterprise-ascm-user-guide/delete-a-table-1.html?spm=a2c4g.14484438.10004.2

DROP TABLE [IF EXISTS] table_name;

删除分区

alter table tablename
drop if exists partition (dt='20230301');

删除字段

alter table table_name drop column cate_level3_id;

改

表

修改表名

ALTER TABLE table_name RENAME TO new_table_name;

分区

分区改名

alter table tablename partition(dt='20230305.wrs')
rename to partition (dt='20230305.wrs.rename');

字段

修改字段类型

Alter table table_name change column cate_level3_id cate_level3_id bigint;

修改字段名

alter table table_name
change column hash comb_hash string;

修改字段注释

参考：专有云文档中心 > 大数据计算服务 > 用户指南 > 修改列注释

ALTER TABLE table_name 
CHANGE COLUMN col_name COMMENT 'comment_string';

禁止和恢复生命周期

参考：https://help.aliyun.com/apsara/enterprise/v_3_14_0_20210519/odps/enterprise-ascm-user-guide/disable-lifecycle.html

-- 禁止表trans的生命周期功能。
ALTER TABLE trans DISABLE LIFECYCLE;

查

查看有哪些分区

– 会在日志中打印

show partitions table;

其他

ROW_NUMBER使用

SELECT 
111110000000+row_number() over (order by pic) as item_id,
pic,emb,dt 
FROM {0} LIMIT 100;

with … as …

with platform_item_pic as(
-------- 标品
select  title
        ,main_pic
from hive_table
where ds='${bizdate}' and status='3')

UNION

INSERT OVERWRITE TABLE table_name partition(dt='model.finetune.canyin.epoch2')
select pic_id,hash_emb from
((select * from table_name where dt='v3')
union 
(select * from table_name where dt='v4')
UNION 
(select * from table_name where dt='v6'))
group by pic_id,hash_emb
;

表复制

https://help.aliyun.com/zh/maxcompute/user-guide/clone-table?spm=a2c4g.11186623.0.0.b4d02342fQa1WX
可以直接对不存在的表B进行填充

clone table A partition (ds='20231231') 
to B;

$■$