本文旨在收集整理ODPS开发中入门及进阶级知识，尽可能涵盖大多ODPS开发问题，成为一本mini百科全书，后续也会持续更新。希望通过笔者的梳理和理解，帮助刚接触ODPS开发的同学快速上手。
本文为该系列第一篇：入门篇。
笔者不才，有任何错误纰漏，欢迎大家指正。
基础功能介绍
▐ 功能分类
一般来说，数据开发包括了以下几个类型：
▐ MaxCompute功能
在此，我们重点介绍一下其中MaxCompute模块（MaxCompute是适用于数据分析场景的企业级SaaS模式云数据仓库）的功能：
基础SQL
▐ DDL
具体语句1：
--创建新表。 create [external] table [if not exists] <table_name> [primary key (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)] [comment <table_comment>] [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)] --用于创建聚簇表时设置表的Shuffle和Sort属性。 [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] --仅限外部表。 [stored by StorageHandler] --仅限外部表。 [with serdeproperties (options)] --仅限外部表。 [location <osslocation>] --指定表为Transactional1.0表，后续可以对该表执行更新或删除表数据操作，但是Transactional表有部分使用限制，请根据需求创建。 [tblproperties("transactional"="true")] --指定表为Transactional2.0表，后续可以做upsert，增量查询，time-travel等操作 [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>] ; ------------------------------------------------------------------- --例子： CREATE TABLE IF NOT EXISTS xxx.xxxx_xxxx_xxxx_hh ( xxxxx STRING COMMENT '商品' ,xxxxx STRING COMMENT '名字' ) COMMENT 'xxx表' PARTITIONED BY ( ds STRING COMMENT 'yyyymmddhh' ) LIFECYCLE 7 ;
参数说明：
external：可选。表示创建的表为外部表。
if not exists：可选。如果不指定if not exists选项而存在同名表，会报错。
table_name：必填。表名。
primary key（pk）：可选。表的主键。
col_name：可选，表的列名。
col_comment：可选。列的注释内容。
data_type：可选。列的数据类型。
not null：可选。禁止该列的值为NULL。default_value：可选。指定列的默认值。
table_comment：可选。表注释内容。
lifecycle：可选。表的生命周期。
partitioned by ( [comment ], ...：可选。指定分区表的分区字段。
具体语句2：修改表的所有人
alter table <table_name> changeowner to <new_owner>; ----------------------------------------------------------例子--将表test1的所有人修改为ALIYUN$xxx@aliyun.comalter table test1 changeowner to 'ALIYUN$xxx@aliyun.com'; --将表test1的所有人修改为名称为ram_test的RAM用户alter table test1 changeowner to 'RAM$13xxxxxxxxxxx:ram_test';
参数说明：
table_name：必填。待修改Owner的表名。
new_owner：必填。修改后的Owner账号。如果要修改Owner为RAM用户，格式为：RAM$:，其中UID为阿里云账号的账号ID，ram_name为RAM用户显示名称。
具体语句3：修改表的注释
alter table <table_name> set comment '<new_comment>';----------------------------------------------------------例子alter table sale_detail set comment 'new coments for table sale_detail';
参数说明：
table_name：必填。待修改注释的表的名称。
new_comment：必填。修改后的注释名称。
具体语句4：修改表的修改时间
alter table <table_name> touch;----------------------------------------------------------例子alter table sale_detail touch;
参数说明：table_name：必填。待修改表的修改时间的表名称。
具体语句5：重命名表
alter table <table_name> rename to <new_table_name>;----------------------------------------------------------例子alter table sale_detail rename to sale_detail_rename;
参数说明：
table_name：必填。待修改名称的表。
new_table_name：必填。修改后的表名称。如果已存在与new_table_name同名的表，会返回报错。
具体语句6：删除表
drop table [if exists] <table_name>;----------------------------------------------------------例子drop table if exists sale_detail;
参数说明：if exists：可选。如果不指定if exists且表不存在，则返回异常。如果指定if exists，无论表是否存在，均返回成功。table_name：必填。待删除的表名。
具体语句7：查看表或视图信息
--查看表或视图信息。desc <table_name|view_name> [partition (<pt_spec>)]; --查看外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。desc extended <table_name>;----------------------------------------------------------例子desc test1;
参数说明：table_name：必填。待查看表的名称。view_name：必填。待查看视图的名称。pt_spec：可选。待查看分区表的指定分区。extended：如果表为外部表、聚簇表或Transactional表，需要包含此参数。
具体语句8：查看分区信息
desc <table_name> partition (<pt_spec>);----------------------------------------------------------例子--查询分区表sale_detail的分区信息。desc sale_detail partition (xxxx_date='201310',region='beijing');
参数说明：table_name：必填。待查看分区信息的分区表名称。pt_spec：必填。待查看的分区信息。
具体语句9：查看建表语句
show create table <table_name>;----------------------------------------------------------例子--查看表sale_detail的建表语句。show create table sale_detail;
参数说明：table_name：必填。待查看建表语句的表的名称。
具体语句10：列出所有分区
show partitions <table_name>;----------------------------------------------------------例子--列出sale_detail中的所有分区。show partitions sale_detail;
参数说明：table_name：必填。待查看分区信息的分区表名称。
具体语句11：清空列数据
ALTER TABLE <table_name> [partition ( <pt_spec>[, <pt_spec>....] )] CLEAR COLUMN column1[, column2, column3, ...] [without touch];
参数说明：table_name：将要执行清空列数据的表名称。column1 , column2...：将要被清空数据的列名称。partition：指定分区。pt_spec：分区描述。without touch：表示不更新LastDataModifiedTime。
具体语句12：复制表
clone table <[<src_project_name>.]<src_table_name>> [partition(<pt_spec>), ...] to <[<dest_project_name>.]<dest_table_name>> [if exists [overwrite | ignore]] ; ------------------------------------------------------------------------------例子 --复制表数据。clone table xxxx_detail partition (xxxx_date='2013', region='china') to xxxx_detail_clone if exists overwrite;
参数说明：
src_project_name：可选。源表所属MaxCompute项目名称。
src_table_name：必填。源表名称。
pt_spec：可选。源表的分区信息。
dest_project_name：可选。
dest_table_name：必填。目标表名称。
▐ DML
具体语句1：插入或覆写数据
--插入：直接向表或静态分区中插入数据，可以在insert语句中直接指定分区值，将数据插入指定的分区。如果您需要插入少量测试数据，可以配合VALUES使用。 --覆写：先清空表或静态分区中的原有数据，再向表或静态分区中插入数据。 insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]] <select_statement> from <from_statement> [zorder by <zcol_name> [, <zcol_name> ...]]; ---------------------------------------------------------------------------- --例子 --向源表追加数据。其中：insert into table table_name可以简写为insert into table_name，但insert overwrite table table_name不可以省略table关键字。 insert into xxxx_detail partition (xxxx_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3); --执行insert overwrite命令向表xxxx_detail_insert中覆写数据，调整select子句中列的顺序。 insert overwrite table xxxx_detail_insert partition (xxxx_date='2013', region='china') select xxxx_id, xxxx_name, xxxx_price from xxxx_detail;
参数说明：table_name：必填。需要插入数据的目标表名称。pt_spec：可选。需要插入数据的分区信息。col_name：可选。需要插入数据的目标表的列名称。select_statement：必填。select子句，从源表中查询需要插入目标表的数据。from_statement：必填。from子句，表示数据来源。zorder by [, ...]：可选。向表或分区写入数据时，支持根据指定的一列或多列，把排序列数据相近的行排列在一起，提升查询时的过滤性能，在一定程度上降低存储成本。
具体语句2：插入或覆写动态分区数据
--在使用MaxCompute SQL处理数据时，分区列的值在select子句中提供，系统自动根据分区列的值将数据插入到相应分区。 insert {into|overwrite} table <table_name> partition (<ptcol_name>[, <ptcol_name> ...]) <select_statement> from <from_statement>; ------------------------------------------------------------------------------例子--指定一级分区，将数据插入目标表。insert overwrite table sale_detail_dypart partition (sale_date='2013', region)select shop_name,customer_id,total_price,region from sale_detail; --将源表sale_detail中的数据插入到目标表sale_detail_dypart。insert overwrite table sale_detail_dypart partition (sale_date, region)select shop_name,customer_id,total_price,sale_date,region from sale_detail;
参数说明：
table_name：必填。需要插入数据的目标表名。
ptcol_name：必填。目标表分区列的名称。
select_statement：必填。select子句，从源表中查询需要插入目标表的数据。
from_statement：必填。from子句，表示数据来源。例如，源表名称。
具体语句3：更新或删除数据
--删除操作：用于删除Transactional或Delta Table表中满足指定条件的单行或多行数据。delete from <table_name> [where <where_condition>]; --清空列数据：将不再使用的列数据从磁盘删除并置NULL，从而达到降低存储成本的目的。ALTER TABLE <table_name> [partition ( <pt_spec>[, <pt_spec>....] )] CLEAR COLUMN column1[, column2, column3, ...] [without touch]; --更新操作：用于将Transactional表或Delta Table表中行对应的单列或多列数据更新为新值。--方式1update <table_name> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];--方式2update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];--方式3UPDATE <table_name> SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ] [ FROM <additional_tables> ] [ WHERE <where_condition> ]
参数说明：
table_name：必填。
where_condition：可选。WHERE子句，用于筛选满足条件的数据。
partition：指定分区，若未指定，则表示操作所有分区。
pt_spec：分区描述。
without touch：表示不更新LastDataModifiedTime。
col1_name、col2_name：待修改行对应的列名称。
value1、value2：至少更新一个列值。修改后的新值。
where_condition：可选。WHERE子句，用于筛选满足条件的数据。
additional_tables：可选，from子句。
具体语句4：merge into
merge into <target_table> as <alias_name_t> using <source expression|table_name> as <alias_name_s>--从on开始对源表和目标表的数据进行关联判断。on <boolean expression1>--when matched…then指定on的结果为True的行为。多个when matched…then之间的数据无交集。when matched [and <boolean expression2>] then update set <set_clause_list>when matched [and <boolean expression3>] then delete --when not matched…then指定on的结果为False的行为。when not matched [and <boolean expression4>] then insert values <value_list> ------------------------------------------------------------------------------例子--执行merge into操作，对符合on条件的数据用源表的数据对目标表进行更新操作，对不符合on条件并且源表中满足event_type为I的数据插入目标表。命令示例如下：merge into acid_address_book_base1 as t using tmp_table1 as s on s.id = t.id and t.year='2020' and t.month='08' and t.day='20' and t.hour='16' when matched then update set t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone when not matched and (s._event_type_='I') then insert values(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');
参数说明：
target_table：必填。目标表名称，必须是实际存在的表。
alias_name_t：必填。目标表的别名。
source expression|table_name：必填。关联的源表名称、视图或子查询。
alias_name_s：必填。关联的源表、视图或子查询的别名。
boolean expression1：必填。BOOLEAN类型判断条件，判断结果必须为True或False。
boolean expression2：可选。update、delete、insert操作相应的BOOLEAN类型判断条件。
set_clause_list：当出现update操作时必填。
value_list：当出现insert操作时必填。
具体语句5：Values
--insert … valuesinsert into table <table_name>[partition (<pt_spec>)][(<col1_name> ,<col2_name>,...)] values (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),... --values tablevalues (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),<table_name> (<col1_name> ,<col2_name>,...)...
参数说明：table_name：必填。待插入数据的表名称。pt_spec：可选。需要插入数据的目标分区信息。col_name：可选。需要插入数据的目标列名称。col_value：可选。目标表中列对应的列值。
具体语句6：Load
--将Hologres、OSS、Amazon Redshift、BigQuery外部存储的CSV格式或其他开源格式数据导入MaxCompute的表或表的分区。{load overwrite|into} table <table_name> [partition (<pt_spec>)]from location <external_location>stored by <StorageHandler>[with serdeproperties (<Options>)]; ------------------------------------------------------------------------------例子load overwrite table xxxx_data_csv_loadfromlocation 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/'stored by 'com.aliyun.odps.CsvStorageHandler'with serdeproperties ( 'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole', --AliyunODPSDefaultRole的ARN信息，可通过RAM角色管理页面获取。 'odps.text.option.delimiter'=',');
参数说明：
table_name：必填。需要插入数据的目标表名称。
pt_spec：可选。需要插入数据的目标表分区信息。
external_location：必填。指定读取外部存储数据的OSS目录。
StorageHandler：必填。指定内置的StorageHandler名称。
Options：可选。指定外部表相关参数。
具体语句7：Unload
--将MaxCompute的数据导出至OSS、Hologres外部存储，OSS支持以CSV格式或其他开源格式存储数据。unload from {<select_statement>|<table_name> [partition (<pt_spec>)]} into location <external_location>stored by <StorageHandler>[with serdeproperties ('<property_name>'='<property_value>',...)]; ------------------------------------------------------------------------------例子--控制导出文件个数：设置单个Worker读取MaxCompute表数据的大小，单位为MB。由于MaxCompute表有压缩，导出到OSS的数据一般会膨胀4倍左右。set odps.stage.mapper.split.size=256;--导出数据。unload from sale_detail partition (sale_date='2013',region='china')intolocation 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'stored by 'com.aliyun.odps.TsvStorageHandler'with serdeproperties ('odps.properties.rolearn'='acs:ram::139699392458****:role/AliyunODPSDefaultRole', 'odps.text.option.gzip.output.enabled'='true');
参数说明：
select_statement：select查询子句，
table_name、pt_spec：使用表名称或表名称加分区名称的方式指定需要导出的数据。
external_location：必填。
StorageHandler：必填。指定内置的StorageHandler名称。
'=''：可选。property_name为属性名称，property_value为属性值。
具体语句8：Explain
--分析查询语句或表结构来分析性能瓶颈explain <dml query>;------------------------------------------------------------------------------例子explain select a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp from (select * from sale_detail_jt where sale_date='2013' and region='china') a inner join (select * from sale_detail where sale_date='2013' and region='china') b on a.customer_id=b.customer_id group by a.customer_id order by a.customer_id limit 10;
参数说明：
dml query：必填。select语句。
具体语句9：公用表表达式
--临时命名结果集，用于简化SQL，可以更好地提高SQL语句的可读性与执行效率with <cte_name> as ( <cte_query> ) [,<cte_name2> as ( <cte_query2> ) ,……] ------------------------------------------------------------------------------例子 with a as (select * from src where key is not null), b as (select * from src2 where value > 0), c as (select * from src3 where value > 0), d as (select a.key, b.value from a join b on a.key=b.key), e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)insert overwrite table srcp partition (p='abc')select * from d union all select * from e;
参数说明：
cte_name：必填。CTE的名称，不能与当前with子句中的其他CTE的名称相同。查询中任何使用到cte_name标识符的地方，均指CTE。
cte_query：必填。一个select语句。select的结果集用于填充CTE。
