分区和列操作为您提供了变更MaxCompute中表的分区或列的操作方法,您可以根据实际业务场景执行相应操作。
MaxCompute SQL支持的分区和列操作如下。
操作类型 | 功能 |
---|---|
添加分区 | 为已存在的分区表新增分区。仅支持新增分区值,不支持新增分区字段。 |
删除分区 | 为已存在的分区表删除分区。 |
添加列或注释 | 为已存在的非分区表或分区表添加列或注释。 |
修改列名 | 为已存在的非分区表或分区表修改列名称。 |
修改列注释 | 为已存在的非分区表或分区表修改列注释。 |
同时修改列名及列注释 | 为已存在的非分区表或分区表同时修改列名称和列注释。 |
修改表或分区的更新时间 | 修改非分区表或分区表中分区的更新时间。 |
修改分区值 | 更改分区表的分区值。 |
合并分区 | 对分区表的分区进行合并,即同一个分区表下的多个分区合并成一个分区,同时删除被合并的分区维度的信息,把数据移动到指定分区。 |
添加分区
为已存在的分区表新增分区。仅支持新增分区值,不支持新增分区字段。
- 语法格式
--一次添加一个分区。 ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec; --一次添加多个分区。 ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [PARTITION partition_spec PARTITION partition_spec...]; --partition_spec格式。 partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
- 参数说明
- table_name:待新增分区的分区表名称。
- IF NOT EXISTS:如果未指定IF NOT EXISTS而同名的分区已存在,会执行失败并返回报错。
- partition_spec:新增的分区,不区分大小写。partition_col是分区名称,partition_col_value是分区值。
- 限制条件
- MaxCompute单表支持的分区数量上限为6万个。
- 对于有多级分区的表,如果需要添加新的分区值,必须指明全部的分区。
- 示例
--给表sale_detail添加一个分区,用来存储2013年12月杭州地区的销售记录。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='201312', region='hangzhou'); --给表sale_detail添加一个分区,用来存储2013年12月上海地区的销售记录。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='201312', region='shanghai'); --给表sale_detail同时添加两个分区,用来存储2013年12月杭州和上海地区的销售记录。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='201312', region='hangzhou') PARTITION (sale_date='201312', region='shanghai'); --给表sale_detail添加分区,仅指定一个分区字段sale_date,返回报错。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='20111011'); --给表sale_detail添加分区,仅指定一个分区字段region,返回报错。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (region='shanghai');
删除分区
为已存在的分区表删除分区。
MaxCompute支持通过条件筛选方式删除分区。如果您希望一次性删除符合某个规则条件的一个或多个分区,可以使用表达式指定筛选条件,通过筛选条件匹配分区并批量删除分区。
- 语法格式
- 未指定筛选条件
--一次删除一个分区。 ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec; --一次删除多个分区。 ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec,PARTITION partition_spec[,PARTITION partition_spec....]; --partition_spec格式。 partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
- 指定筛选条件
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_filtercondition; --分区过滤子句PARTITION partition_filtercondition的格式。 PARTITION partition_filtercondition : PARTITION (partition_col relational_operators partition_col_value) | PARTITION (scalar(partition_col) relational_operators partition_col_value) | PARTITION (partition_filtercondition1 AND|OR partition_filtercondition2) | PARTITION (NOT partition_filtercondition) | PARTITION (partition_filtercondition1)[,PARTITION (partition_filtercondition2), ...]
- 未指定筛选条件
- 参数说明
- table_name:待删除分区的分区表名称。
- IF EXISTS:如果未指定IF EXISTS,且分区不存在,会执行失败并返回报错。
- partition_spec:删除的分区,不区分大小写。partition_col是分区名称,partition_col_value是分区值。
- PARTITION partition_filtercondition:删除的分区,不区分大小写。
- partition_col:分区名称。
- relational_operators:关系运算符,详情请参见运算符
- partition_col_value:分区列比较值或正则表达式,与分区列数据类型保持一致。
- scalar():Scalar函数。Scalar函数基于输入值生成对应的标量,对分区列的值(partition_col的值)进行处理后再按照指定的关系运算符relational_operators与partition_col_value做比较。
- 分区过滤条件支持逻辑运算符NOT、AND和OR。支持通过NOT过滤条件子句,取过滤规则的补集。支持多个过滤条件子句以AND或OR的关系组成整体分区匹配规则。
- 支持多个分区过滤子句,当多个分区过滤子句以英文逗号(,)分隔时,每个过滤子句的逻辑以OR的关系组成整体分区匹配规则。
- 限制条件
- 每个分区过滤子句只能访问一个分区列。
- 表达式用到的函数必须是内建的Scalar函数。
- 示例
- 未指定筛选条件
--从表sale_detail中删除一个分区,2013年12月杭州分区的销售记录。 ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='201312',region='hangzhou'); --从表sale_detail中同时删除两个分区,2013年12月杭州和上海分区的销售记录。 ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='201312',region='hangzhou'),PARTITION(sale_date='201312',region='shanghai');
- 指定筛选条件
--创建分区表。 CREATE TABLE IF NOT EXISTS sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) PARTITIONED BY (sale_date STRING); --添加分区。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date= '201910') PARTITION (sale_date= '201911') PARTITION (sale_date= '201912') PARTITION (sale_date= '202001') PARTITION (sale_date= '202002') PARTITION (sale_date= '202003') PARTITION (sale_date= '202004') PARTITION (sale_date= '202005') PARTITION (sale_date= '202006') PARTITION (sale_date= '202007'); --批量删除分区。 ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date >= '202007'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date LIKE '20191%'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date IN ('202002','202004','202006')); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date BETWEEN '202001' AND '202007'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(substr(sale_date, 1, 4) = '2020'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201912' OR sale_date >= '202006'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date > '201912' AND sale_date <= '202004'); ALTER TABLE sale_detail DROP IF EXISTS PARTITION(NOT sale_date > '202004'); --支持多个分区过滤表达式,表达式之间是OR的关系。 ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'), PARTITION(sale_date >= '202007'); --添加其他格式分区。 ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date= '2019-10-05'); PARTITION (sale_date= '2019-10-06') PARTITION (sale_date= '2019-10-07'); --批量删除分区,使用正则表达式匹配分区。 ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date RLIKE '2019-\\d+-\\d+'); --创建多级分区表。 CREATE TABLE IF NOT EXISTS region_sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) PARTITIONED BY (sale_date STRING , region STRING ); --添加分区。 ALTER TABLE region_sale_detail ADD IF NOT EXISTS PARTITION (sale_date= '201910',region = 'shanghai') PARTITION (sale_date= '201911',region = 'shanghai') PARTITION (sale_date= '201912',region = 'shanghai') PARTITION (sale_date= '202001',region = 'shanghai') PARTITION (sale_date= '202002',region = 'shanghai') PARTITION (sale_date= '201910',region = 'beijing') PARTITION (sale_date= '201911',region = 'beijing') PARTITION (sale_date= '201912',region = 'beijing') PARTITION (sale_date= '202001',region = 'beijing') PARTITION (sale_date= '202002',region = 'beijing'); --执行如下语句批量删除多级分区,两个匹配条件是或的关系,会将sale_date小于201911或region等于beijing的分区都删除掉。 ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'),PARTITION(region = 'beijing'); --如果删除sale_date小于201911且region等于beijing的分区,可以使用如下方法。 ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911', region = 'beijing');
批量删除多级分区时,在一个PARTITION过滤子句中,不能根据多个分区列编写组合条件匹配分区,如下语句会报错FAILED: ODPS-0130071:[1,82] Semantic analysis exception - invalid column reference region, partition expression must have one and only one column reference
,--分区过滤子句只能访问一个分区列,如下语句报错。 ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911' AND region = 'beijing');
- 未指定筛选条件
添加列或注释
为已存在的非分区表或分区表添加列或注释。
- 语法格式
- 添加列
ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...);
- 同时添加列和注释
ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX',col_name2 type2 comment 'XXX');
- 添加列
- 参数说明
- table_name:待新增列的表名称。添加的新列不支持指定顺序,默认在最后一列。
- col_name:新增列的名称。
- type:新增列的数据类型。
- comment:新增列的注释。
- 示例
--给表sale_detail添加两个列。 ALTER TABLE sale_detail ADD COLUMNS (customer_name STRING, education BIGINT); --给表sale_detail添加两个列并同时添加列注释。 ALTER TABLE sale_detail ADD COLUMNS (customer_name STRING comment '客户', education BIGINT comment '教育' );
修改列名
为已存在的非分区表或分区表修改列名称。
- 语法格式
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;
- 参数说明
- table_name:待修改列名的表名称。
- old_col_name:待修改的列名称。
old_col_name
必须是已存在的列。 - new_col_name:修改后的列名称。表中不能有名为
new_col_name
的列。
- 示例
--修改表sale_detail的列名。 ALTER TABLE sale_detail CHANGE COLUMN customer_name RENAME TO customer;
修改列注释
为已存在的非分区表或分区表修改列注释。
- 语法格式
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT 'comment_string';
- 参数说明
- table_name:待修改列注释的表名称。
- col_name:待修改注释的列名称。
col_name
必须是已存在的列。 - comment_string:修改后的注释信息。内容最长为1024字节。
- 示例
--修改表sale_detail的列注释。 ALTER TABLE sale_detail CHANGE COLUMN customer COMMENT 'customer';
同时修改列名及列注释
为已存在的非分区表或分区表同时修改列名称和列注释。
- 语法格式
ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_type COMMENT 'column_comment';
- 参数说明
- table_name:待修改列名以及注释的表名称。
- old_col_name:待修改列的名称。
old_col_name
必须是已存在的列。 - new_col_name:新的列名称。表中不能有名为
new_col_name
的列。 - column_type:列的数据类型,不可更改。
- column_comment:修改后的注释信息。内容最长为1024字节。
- 示例
--修改表sale_detail的列名和列注释。 ALTER TABLE sale_detail CHANGE COLUMN customer customer_name string COMMENT '客户';
修改表或分区的更新时间
MaxCompute SQL提供TOUCH
操作,用于修改非分区表或分区表中分区的LastDataModifiedTime
。此操作会将LastDataModifiedTime
修改为当前时间。此时,MaxCompute会认为数据有变动,重新计算生命周期。
- 语法格式
--修改非分区表的更新时间。 ALTER TABLE table_name TOUCH; --修改分区表中分区的更新时间。 ALTER TABLE table_name TOUCH PARTITION(partition_col='partition_col_value', ...);
- 参数说明
- table_name:待修改更新时间的表名称。如果表不存在,则返回报错。
- partition_col='partition_col_value':需要修改更新时间的分区名称以及分区值。如果指定的分区以及分区值不存在,则返回报错。
- 示例
--修改非分区表的更新时间。 ALTER TABLE result_table TOUCH; --修改分区表中分区的更新时间。 ALTER TABLE sale_detail TOUCH PARTITION(sale_date='201312');
修改分区值
MaxCompute SQL支持通过RENAME
操作更改分区表的分区值。
- 语法格式
ALTER TABLE table_name PARTITION (partition_col1 = 'partition_col_value1', partition_col2 = 'partiton_col_value2', ...) RENAME TO PARTITION (partition_col1 = 'partition_col_newvalue1', partition_col2 = 'partiton_col_newvalue2', ...);
- 参数说明
- table_name:待修改分区值的表名称。如果此表不存在,则返回报错。
- partition_col = partition_col_value:表的分区名称以及对应的分区值。
- partition_col = partition_col_newvalue:表的分区名称和修改后的分区值。
- 限制条件
- 不支持修改分区列的列名,只能修改分区列对应的值。
- 修改多级分区的一个或多个分区值,多级分区的每一级的分区值都必须填写。
- 示例
--修改表sale_detail的分区值。 ALTER TABLE sale_detail PARTITION (sale_date = '201312', region = 'hangzhou') RENAME TO PARTITION (sale_date = '201310', region = 'beijing');
合并分区
MaxCompute SQL提供MERGE PARTITION
对分区表的分区进行合并,即同一个分区表下的多个分区合并成一个分区,同时删除被合并的分区维度的信息,把数据移动到指定分区。
说明
- 不支持外部表和SHARD表,对于CLUSTERED表合并后的分区文件会消除CLUSTERED属性。
- 一次性合并分区数量限制为4000个。
- 语法格式
ALTER TABLE <table_name> MERGE [IF EXISTS] PARTITION(<predicate>) [, PARTITION(<predicate2>) ...] OVERWRITE PARTITION(<fullPartitionSpec>) [PURGE];
- 参数说明
- table_name:待合并分区的分区表名称。
- IF EXISTS:如果未指定IF EXISTS,且分区不存在,会执行失败并返回报错。如果指定
IF EXISTS
后不存在满足MERGE
条件的分区,则不生成新分区。如果运行过程中出现源数据被并发修改(包括INSERT
、RENAME
或DROP
)时,即使指定IF EXISTS
也会报错。 - predicate:筛选待合并分区需要满足的条件。
- fullPartitionSpec:目标分区信息。
- 示例
--分区表的分区和数据如下。 odps@ project_name>SHOW PARTITIONS intpstringstringstring; ds=20181101/hh=00/mm=00 ds=20181101/hh=00/mm=10 ds=20181101/hh=10/mm=00 ds=20181101/hh=10/mm=10 OK odps@ project_name>DESC intpstringstringstring; +------------+------------+------------+------------+ | value | ds | hh | mm | +------------+------------+------------+------------+ | 1 | 20181101 | 00 | 00 | | 1 | 20181101 | 00 | 10 | | 1 | 20181101 | 10 | 00 | | 1 | 20181101 | 10 | 10 | +------------+------------+------------+------------+ --合并所有满足hh='00'的分区到hh='00',mm='00'中。 odps@ project_name>ALTER TABLE intpstringstringstring MERGE PARTITION(hh='00') OVERWRITE PARTITION(ds='20181101', hh='00', mm='00'); ID = 20190404025755844g80qwa7a OK --查看合并后的分区。 odps@ project_name>SHOW PARTITIONS intpstringstringstring; ds=20181101/hh=00/mm=00 ds=20181101/hh=10/mm=00 ds=20181101/hh=10/mm=10 OK --两个分区的数据已经合并到了一个分区中。 odps@ project_name>DESC intpstringstringstring; +------------+------------+------------+------------+ | value | ds | hh | mm | +------------+------------+------------+------------+ | 1 | 20181101 | 00 | 00 | | 1 | 20181101 | 00 | 00 | | 1 | 20181101 | 10 | 00 | | 1 | 20181101 | 10 | 10 | +------------+------------+------------+------------+
MERGE PARTITION
允许指定多个谓词条件,示例如下,指定到具体分区下,合并剩余分区。odps@ project_name>ALTER TABLE intpstringstringstring MERGE IF EXISTS PARTITION(ds='20181101', hh='00', mm='00'), PARTITION(ds='20181101', hh='10', mm='00'), PARTITION(ds='20181101', hh='10', mm='10') OVERWRITE PARTITION(ds='20181101', hh='00', mm='00') PURGE; ID = 20190404034632854g431sqzt2 OK odps@ project_name>SHOW PARTITIONS intpstringstringstring; ds=20181101/hh=00/mm=00 OK