ODPS开发大全：入门篇-低调大师

ODPS开发大全：入门篇

2024-07-08 355

本文旨在收集整理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 (<col_name> <data_type> [comment <col_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>:<ram_name>，其中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 <zcol_name> [, <zcol_name> ...]：可选。向表或分区写入数据时，支持根据指定的一列或多列，把排序列数据相近的行排列在一起，提升查询时的过滤性能，在一定程度上降低存储成本。

具体语句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>'：可选。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。

▐ DQL

SELECT语句

1. SELECT语法

[with <cte>[, ...] ]SELECT [all | distinct] <SELECT_expr>[, <except_expr>][, <replace_expr>] ...       from <table_reference>       [where <where_condition>]       [group by {<col_list>|rollup(<col_list>)}]       [having <having_condition>]       [window <window_clause>]       [order by <order_condition>]       [distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]       [limit <number>]

下面将介绍SELECT命令格式及如何实现嵌套查询、分组查询、排序等操作。

2. SELECT语序

--语法顺序from <table_reference>[where <where_condition>][group by <col_list>][having <having_condition>][window <window_name> AS (<window_definition>)][qualify <expression>]select [all | distinct] <select_expr>, <select_expr>, ...[order by <order_condition>][distribute by <distribute_condition> [sort by <sort_condition>] ][limit <number>]

场景1：from->where->group by->having->select->order by->limit

场景2：from->where->select->distribute by->sort by

3. WITH子句

with A as (SELECT 1 as C),B as (SELECT * from A) SELECT * from B;

在同一WITH子句中的CTE必须具有唯一的名字。

在WITH子句中定义的CTE仅对在同一WITH子句中的其他CTE可以使用。

4. 列表达式

------------------------------------------------------------------------------例子 --读取表xxxx_detail的列shop_nameSELECT xxxx_name from xxxx_detail;--查询表xxxx_detail中region列数据，如果有重复值时仅显示一条。SELECT distinct region from xxxx_detail;--选出xxxx_detail表中列名不为xxxx_name的所有列SELECT `(xxxx_name)?+.+` from xxxx_detail;--去重多列时，distinct的作用域是SELECT的列集合，不是单个列。SELECT distinct region, xxxx_date from xxxx_detail;

用列名指定要读取的列。

用星号（*）代表查询所有的列。

可以使用正则表达式。

在选取的列名前可以使用distinct去掉重复字段，只返回去重后的值。

5. 排除列

--读取xxxx_detail表的数据，并排除region列的数据。------------------------------------------------------------------------------例子SELECT * except(region) from xxxx_detail;

当希望读取表内大多数列的数据，同时要排除表中少数列的数据时。

表示读取表数据时会排除指定列（col1、col2）的数据。

6. WHERE

--配合关系运算符，筛选满足指定条件的数据。关系运算符包含：  >、<、=、>=、<=、<>  like、rlike  in、not in  between…and------------------------------------------------------------------------------例子SELECT * from xxxx_detailwhere xxxx_date >= '2008' and xxxx_date <= '2014';--等价于如下语句。SELECT * from xxxx_detail where xxxx_date between '2008' and '2014';

where子句为过滤条件。如果表是分区表，可以实现列裁剪。

7. GROUP BY

------------------------------------------------------------------------------例子
--直接使用输入表列名region作为group by的列，即以region值分组SELECT region from xxxx_detail group by region;
--以region值分组，返回每一组的销售额总量。SELECT sum(xxxx_price) from xxxx_detail group by region;
--以region值分组，返回每一组的region值（组内唯一）及销售额总量。SELECT region, sum (xxxx_price) from xxxx_detail group by region;

group by操作优先级高于SELECT操作，因此group by的取值是SELECT输入表的列名或由输入表的列构成的表达式。需要注意的是：

group by取值为正则表达式时，必须使用列的完整表达式。

SELECT语句中没有使用聚合函数的列必须出现在GROUP BY中。

8. HAVING

------------------------------------------------------------------------------例子--为直观展示数据呈现效果，向sale_detail表中追加数据。insert into sale_detail partition (sale_date='2014', region='shanghai')   values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);--使用having子句配合聚合函数实现过滤。SELECT region,sum(total_price) from sale_detail group by region having sum(total_price)<305;

通常HAVING子句与聚合函数一起使用，实现过滤。

9. ORDER BY

------------------------------------------------------------------------------例子
--查询表xxxx_detail的信息，并按照xxxx_price升序排列前2条。SELECT * from xxxx_detail order by xxxx_price limit 2;
--将表xxx_detail按照xxxx_price升序排序后，输出从第3行开始的3行数据。SELECT xxxx_id,xxxx_price from xxxx_detail order by xxxx_price limit 3 offset 2;

默认对数据进行升序排序，如果降序排序，需要使用desc关键字。

order by默认要求带limit数据行数限制，没有limit会返回报错。

10. DISTRIBUTE BY哈希分片

------------------------------------------------------------------------------例子
--查询表xxxx_detail中的列region值并按照region值进行哈希分片。SELECT region from xxxx_detail distribute by region;--等价于如下语句。SELECT region as r from xxxx_detail distribute by region;SELECT region as r from xxxx_detail distribute by r;

distribute by控制Map（读数据）的输出在Reducer中是如何划分的，如果不希望Reducer的内容存在重叠，或需要对同一分组的数据一起处理，可以使用distribute by来保证同组数据分发到同一个Reducer中。

11. SORT BY局部排序

------------------------------------------------------------------------------例子
--查询表xxxx_detail中的列region和xxxx_price的值并按照region值进行哈希分片，然后按照xxxx_price对哈希分片结果进行局部升序排序。SELECT region,xxxx_price from xxxx_detail distribute by region sort by xxxx_price;
--查询表xxxx_detail中的列region和xxxx_price的值并按照region值进行哈希分片，然后按照xxxx_price对哈希分片结果进行局部降序排序。SELECT region,xxxx_price from xxxx_detail distribute by region sort by xxxx_price desc;
--如果sort by语句前没有distribute by，sort by会对每个Reduce中的数据进行局部排序。SELECT region,xxxx_price from xxxx_detail sort by xxxx_price desc;

sort by默认对数据进行升序排序，如果降序排序，需要使用desc关键字。

如果sort by语句前有distribute by，sort by会对distribute by的结果按照指定的列进行排序。

12. LIMIT限制输出行数

SELECT * FROM xxxxx.xxxx_xxxx_xxxxWHERE ds = 20240520LIMIT 100;

limit <number>中的number是常数，用于限制输出行数，取值范围为int32位取值范围。

子查询

1. 基础子查询

--格式1select <select_expr> from (<select_statement>) [<sq_alias_name>];
--格式2select (<select_statement>) from <table_name>;

普通查询操作的对象是目标表，但是查询的对象也可以是另一个select语句，这种查询为子查询。在from子句中，子查询可以被当作一张表，与其他表或子查询进行join操作。

2. IN SUBQUERY

--in subquery与left semi join用法类似--格式一select<select_expr1>from<table_name1>where<select_expr2>  in(select<select_expr3>from<table_name2>);--等效于leftsemijoin如下语句。select<select_expr1>from<table_name1><alias_name1>leftsemijoin<table_name2><alias_name2>  on<alias_name1>.<select_expr2>=<alias_name2>.<select_expr3>;
--格式二select<select_expr1>from<table_name1>where<select_expr2>  in(select<select_expr3>from<table_name2>where     <table_name1>.<col_name>=<table_name2>.<col_name>);
------------------------------------------------------------------------------例子set odps.sql.allow.fullscan=true;select * from xxxx_detail where xxxx_price in (select xxxx_price from shop);
set odps.sql.allow.fullscan=true;select * from xxxx_detail where xxxx_price   in (select xxxx_price from shop where xxxx_id = shop.xxxx_id);

select_expr1：必填。格式为col1_name, col2_name, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。