分区和列操作为您提供了变更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_operatorspartition_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');
      如下语句会报错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条件的分区,则不生成新分区。如果运行过程中出现源数据被并发修改(包括INSERTRENAMEDROP)时,即使指定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