MaxCompute分区和列操作

为已存在的分区表新增分区。

• 限制条件

  • MaxCompute单表支持的分区数量上限为6万个。
  • 对于有多级分区的表，如果需要添加新的分区值，必须指明全部的分区。
  • 仅支持新增分区值，不支持新增分区字段。

• 命令格式

  alter table  add [if not exists] partition  [partition  partition ...];
  

• 参数说明

  • table_name：必填。待新增分区的分区表名称。
  • if not exists：可选。如果未指定if not exists而同名的分区已存在，会执行失败并返回报错。
  • pt_spec：必填。新增的分区，格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, …)。partition_col是分区字段，partition_col_value是分区值。分区字段不区分大小写，分区值区分大小写。

• 使用示例

  • 示例1：给表sale_detail添加一个分区，用来存储2013年12月杭州地区的销售记录。

    alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
    

  • 示例1：给表sale_detail添加一个分区，用来存储2013年12月杭州地区的销售记录。

    alter table sale_detail add if not exists partition (sale_date='201312', region='beijing') partition (sale_date='201312', region='shanghai');
    

  • 示例3：给表sale_detail添加分区，仅指定一个分区字段sale_date，返回报错，需要同时指定2个分区字段sale_date和region。

    alter table sale_detail add if not exists partition (sale_date='20111011');
    

为已存在的分区表删除分区。

MaxCompute支持通过条件筛选方式删除分区。如果您希望一次性删除符合某个规则条件的一个或多个分区，可以使用表达式指定筛选条件，通过筛选条件匹配分区并批量删除分区。

• 限制条件

  • 每个分区过滤子句只能访问一个分区列。
  • 表达式用到的函数必须是内建的Scalar函数。

• 命令格式

  • 未指定筛选条件

    --一次删除一个分区。
    alter table  drop [if exists] partition ;
    --一次删除多个分区。
    alter table  drop [if exists] partition ,partition [,partition ....];
    

  • 指定筛选条件

    alter table  drop [if exists] partition ;
    

• 参数说明

  • table_name：必填。待删除分区的分区表名称。

  • if exists：可选。如果未指定if exists且分区不存在，则返回报错。

  • pt_spec：必填。删除的分区。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。分区字段不区分大小写，分区值区分大小写。

  • partition_filtercondition：指定筛选条件时必填。分区筛选条件，不区分大小写。格式为：

    partition_filtercondition
        : partition (  )
        | partition (scalar()  )
        | partition ( AND|OR )
        | partition (NOT )
        | partition ()[,partition (), ...]
    

  • partition_col：分区名称。

  • relational_operators：关系运算符。

  • partition_col_value：分区列比较值或正则表达式，与分区列数据类型保持一致。

  • scalar()：Scalar函数。Scalar函数基于输入值生成对应的标量，对分区列的值（partition_col）进行处理后再按照指定的关系运算符relational_operators与partition_col_value做比较。

  • 分区过滤条件支持逻辑运算符NOT、AND和OR。支持通过NOT过滤条件子句，取过滤规则的补集。支持多个过滤条件子句以AND或OR的关系组成整体分区匹配规则。

  • 支持多个分区过滤子句，当多个分区过滤子句以英文逗号（,）分隔时，每个过滤子句的逻辑以OR的关系组成整体分区匹配规则。

• 使用示例

  • 未指定筛选条件

    --从表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  add columns (  comment [''][,   comment ''...]);
  

• 参数说明

  • table_name：必填。待新增列的表名称。添加的新列不支持指定顺序，默认在最后一列。
  • col_name：必填。新增列的名称。
  • type：必填。新增列的数据类型。
  • col_comment：可选。新增列的注释。

• 使用示例

  • 示例1：给表sale_detail添加两个列。

    alter table sale_detail add columns (customer_name STRING, education BIGINT);
    

  • 示例2：给表sale_detail添加两个列并同时添加列注释。

    alter table sale_detail add columns (customer_name STRING comment '客户', education BIGINT comment '教育' );
    

为已存在的非分区表或分区表修改列名称。

• 命令格式

  alter table  change column  rename to ;
  

• 参数说明

  • table_name：必填。待修改列名的表名称。
  • old_col_name：必填。待修改的列名称。old_col_name必须是已存在的列。
  • new_col_name：必填。修改后的列名称。表中不能有名为new_col_name的列。

• 使用示例

  --修改表sale_detail的列名customer_name为customer。
  alter table sale_detail change column customer_name rename to customer;
  

为已存在的非分区表或分区表修改列注释。

• 语法格式

  alter table  change column  comment '';
  

• 参数说明

  • table_name：必填。待修改列注释的表名称。
  • col_name：必填。待修改注释的列名称。col_name必须是已存在的列。
  • col_comment：必填。修改后的注释信息。注释内容为长度不超过1024字节的有效字符串，否则报错。

• 使用示例

  --修改表sale_detail的列customer的注释。
  alter table sale_detail change column customer comment 'customer';
  

修改非分区表或分区表的列名或注释。

• 命令格式

  alter table  change column    comment '';
  

• 参数说明

  • table_name：必填。需要修改列名以及注释的表名称。
  • old_col_name：必填。需要修改的列名称。old_col_name必须是已存在的列。
  • new_col_name：必填。新的列名称。表中不能有名为new_col_name的列。
  • column_type：必填。列的数据类型。
  • col_comment：可选。修改后的注释信息。内容最长为1024字节。

• 使用示例

  --修改表sale_detail的列名customer_name为customer_newname，注释“客户”为“customer”。
  alter table sale_detail change column customer_name customer_newname STRING comment 'customer';
  

修改表的非分区列的非空属性。即如果表的非分区列值禁止为NULL，您可以通过本命令修改分区列值允许为NULL。

您可以通过desc extended table_name;命令查看Nullable属性值，判断列的非空属性。如果Nullable为true，表示允许为NULL；如果Nullable为false，表示禁止为NULL。

• 使用限制

  修改分区列值允许为NULL后，不可回退，不支持再修改分区列值禁止为NULL，请谨慎操作。

• 命令格式

  alter table  change column  null;
  

• 参数说明

  • table_name：必填。待修改列非空属性的表名称。
  • old_col_name：必填。待修改的非分区列的名称。old_col_name必须是已存在的非分区列。

• 使用示例

  --创建一张分区表，id列禁止为NULL。
  create table null_test(id int not null, name string) partitioned by (ds string);
  --修改id列允许为NULL。
  alter table null_test change column id null;
  

MaxCompute SQL提供touch操作，用于修改分区表中分区的LastDataModifiedTime。此操作会将LastDataModifiedTime修改为当前时间。此时，MaxCompute会认为数据有变动，重新计算生命周期。

• 使用限制

  对于有多级分区的表，必须指明全部的分区。

• 命令格式

  alter table  touch partition ();
  

• 参数说明

  • table_name：必填。待修改分区更新时间的分区表名称。如果表不存在，则返回报错。
  • pt_spec：必填。需要修改更新时间的分区信息。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。如果指定的分区字段或分区值不存在，则返回报错。

• 使用示例

  --修改表sale_detail的分区sale_date='201312', region='shanghai'的LastDataModifiedTime。
  alter table sale_detail touch partition (sale_date='201312', region='shanghai');
  

MaxCompute SQL支持通过rename操作更改分区表的分区值。

• 使用限制

  • 不支持修改分区列的列名，只能修改分区列对应的值。
  • 对于有多级分区的表，必须指明全部的分区。

• 命令格式

  alter table  partition () rename to partition ();
  

• 参数说明

  • table_name：必填。待修改分区值的表名称。
  • pt_spec：必填。需要修改分区值的分区信息。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。如果指定的分区字段或分区值不存在，则返回报错。
  • new_pt_spec：必填。修改后的分区信息。格式为(partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...)。partition_col是分区字段，new_partition_col_value是新分区值。

• 使用示例

  --修改表sale_detail的分区值。
  alter table sale_detail partition (sale_date = '201312', region = 'hangzhou') rename to partition (sale_date = '201310', region = 'beijing');
  

MaxCompute SQL提供merge partition对分区表的分区进行合并，即将同一个分区表下的多个分区合并成一个分区，同时删除被合并的分区维度的信息，把数据移动到指定分区。

• 使用限制

  • 不支持外部表，聚簇表合并后的分区会消除聚簇属性。
  • 一次性合并分区数量限制为4000个。

• 命令格式

  alter table  merge [if exists] partition () [, partition() ...] overwrite partition () [purge];
  

• 参数说明

  • table_name：必填。待合并分区的分区表名称。
  • if exists：可选。如果未指定if exists，且分区不存在，会执行失败并返回报错。如果指定if exists后不存在满足merge条件的分区，则不生成新分区。如果运行过程中出现源数据被并发修改（包括insert、rename或drop）时，即使指定if exists也会报错。
  • predicate：必填。筛选待合并分区需要满足的条件。
  • fullpartitionSpec：必填。目标分区信息。

• 使用示例

  • 示例1：合并满足指定条件的分区到目标分区。

    --查看分区表的分区。
    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
    
    --合并所有满足hh='00'的分区到hh='00'，mm='00'中。
    alter table intpstringstringstring merge partition(hh='00') overwrite partition(ds='20181101', hh='00', mm='00');
    
    --查看合并后的分区。
    show partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00
    ds=20181101/hh=10/mm=00
    ds=20181101/hh=10/mm=10                    
    

  • 示例2：合并指定的多个分区到目标分区。

    --合并多个指定分区。
    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;
    --查看分区表的分区。
    show partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00