云原生大数据计算服务 MaxCompute：OSS外部表相关分区操作

场景说明

创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作。支持自动解析OSS目录结构，识别分区以及为OSS外部表添加分区信息。

• MSCK方式适用于一次性补全全部缺失的历史分区的场景。MaxCompute会根据创建OSS外部表时指定的分区目录，自动补全OSS外部表的分区，而不用逐个按照分区列名和名称增加。

• 该方式不适用于补充增量数据，尤其是在OSS目录中包含大量分区（如超过1000个）的情况下。当新增分区数量远少于已有分区时，频繁执行 MSCK 命令会导致 OSS 目录被大量重复扫描，并触发元数据更新，从而降低命令执行效率。对于需要更新增量分区的场景，建议采用添加分区的方式。

语法格式

MSCK REPAIR TABLE <mc_oss_extable_name>;

MSCK REPAIR TABLE <mc_oss_extable_name> ADD PARTITIONS [WITH PROPERTIES (key:VALUE, key:VALUE ...)];

使用示例

以CSV分区外部表为例，其他OSS外部表格式使用MSCK命令的方式相同。

1. 准备分区数据

   1. 登录对象存储OSS控制台。

   2. 在Bucket下创建Demo2目录，按分区列direction在该目录下创建子目录（direction=N、direction=NE、direction=S、direction=SW、direction=W），并上传对应的数据文件（vehicle1.csv、vehicle2.csv、vehicle3.csv、vehicle4.csv、vehicle5.csv）到各子目录中。操作详情参考附录：准备示例数据。

2. 创建OSS分区外部表

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external2
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitude DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING
   )
   PARTITIONED BY (
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   WITH SERDEPROPERTIES (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   LOCATION 'oss://oss-cn-<region>-internal.aliyuncs.com/<bucket>/Demo2/';

3. 引入分区

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;

4. 查询OSS外部表

   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE';
   
   -- 返回结果如下：
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | vehicleid | recordid | patientid | calls | locationlatitude | locationlongitude | recordtime | direction |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 1         | 2        | 13        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE        |
   | 1         | 3        | 48        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE        |
   | 1         | 9        | 4         | 1     | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

场景说明

创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作。当需要通过手动执行命令为OSS外部表添加分区信息时，可使用ALTER TABLE ADD PARTITION命令。

该方式适用于历史分区已经创建完成，需要频繁地周期性追加分区的场景。在执行数据写入任务之前创建好分区，即使OSS上有新数据写入，也不需要刷新对应分区，外部表即可读取OSS目录上的最新数据。

语法格式

ALTER TABLE <mc_oss_extable_name> 
  ADD PARTITION (<col_name>=<col_value>)[
  PARTITION (<col_name>=<col_value>)...][location URL];

• col_name和col_value的值需要与分区数据文件所在目录名称对齐。一个ADD PARTITION对应一个子目录，多个OSS子目录需要使用多个ADD PARTITION。

• 例如，分区数据文件所在的OSS目录结构如下，col_name对应direction，col_value对应N、NE、S、SW、W。

  Demo2/
  ├── direction=N/
  ├── direction=NE/
  ├── direction=S/
  ├── direction=SW/
  └── direction=W/

使用示例

以CSV分区外部表为例，其他OSS格式外部表添加分区的方式相同。

1. 准备分区数据

   1. 登录对象存储OSS控制台。

   2. 在Bucket下创建Demo2目录，按分区列direction在该目录下创建子目录（direction=N、direction=NE、direction=S、direction=SW、direction=W），并上传对应的数据文件（vehicle1.csv、vehicle2.csv、vehicle3.csv、vehicle4.csv、vehicle5.csv）到各子目录中。操作详情参考附录：准备示例数据。

2. 创建OSS分区外部表

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external3
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitude DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING
   )
   PARTITIONED BY (
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   WITH SERDEPROPERTIES (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   LOCATION 'oss://oss-cn-<region>-internal.aliyuncs.com/<bucket>/Demo2/';

3. 添加分区

   ALTER TABLE mc_oss_csv_external3 ADD 
     PARTITION (direction='N')
     PARTITION (direction='NE')
     PARTITION (direction='S')
     PARTITION (direction='SW')
     PARTITION (direction='W');

4. 查询OSS外部表

   SELECT * FROM mc_oss_csv_external3 WHERE direction='NE';
   
   -- 返回结果如下：
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | vehicleid | recordid | patientid | calls | locationlatitude | locationlongitude | recordtime | direction |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 1         | 2        | 13        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE        |
   | 1         | 3        | 48        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE        |
   | 1         | 9        | 4         | 1     | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

场景说明

创建的OSS外部表为分区表时，删除分区表的某个或多个分区。更多操作请参考分区操作。

语法格式

ALTER TABLE <mc_oss_extable_name> DROP [IF EXISTS] PARTITION <pt_spec>[, PARTITION <pt_spec>...];

使用示例

1. 基于添加分区的示例表mc_oss_csv_external3，查看该表的分区列表。

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- 返回结果如下：
   direction=N
   direction=NE
   direction=S
   direction=SW
   direction=W
   
   OK

2. 删除direction=S和direction=SW分区

   ALTER TABLE mc_oss_csv_external3 DROP IF EXISTS
     PARTITION (direction = 'S'), PARTITION (direction = 'SW');

3. 再次查看分区列表，确认direction=S和direction=SW分区已被删除

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- 返回结果如下：
   direction=N
   direction=NE
   direction=W
   
   OK

场景说明

当前场景下，通过写入OSS分区外部表生成的OSS分区目录包含分区key值（例如dt=20250724）。如需通过OSS外部表向OSS写出的分区路径不包含分区key，可使用Keyless Partition功能。

使用限制

• 若使用固定value值的语法以动态分区形式写入外部表时则Keyless Partition参数无效，会生成<pt_col_name>=<pt_col_value>格式的OSS目录，且无法读出该目录下的数据。例如，INSERT OVERWRITE ext_tb PARTITION(dt) VALUES(1, 'xxx','20250724'), (2, 'xxx','20250724')

• 不支持Paimon外部表、Hudi外部表、Delta Lake外部表。

语法格式

在创建外部表时添加TBLPROPERTIES参数，以CSV外部表为例如下。

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)] 
STORED BY 'com.aliyun.odps.CsvStorageHandler'  
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)] 
LOCATION '<oss_location>'
TBLPROPERTIES('odps.external.output.partition.keys.omitted' = 'true'); -- 该参数为true后，写出OSS的分区目录不包含分区key。

使用示例

以CSV分区外部表为例，其他格式使用方式相同。

1. 创建CSV分区外部表

   CREATE EXTERNAL TABLE ext_csv_test03
   (
     id INT,
     name STRING
   )
   PARTITIONED BY (dt STRING)
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   LOCATION 'oss://oss-cn-<region>-internal.aliyuncs.com/<bucket>/keyless_partition/ext_csv_test03/'
   TBLPROPERTIES('odps.external.output.partition.keys.omitted' = 'true');

2. 以静态分区的形式，向外部表写入常量数据

   INSERT INTO ext_csv_test03 PARTITION (dt='20250724')
     VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
   
   SELECT * FROM ext_csv_test03;
   -- 返回结果如下：
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   OSS生成的目录如下，可以看到生成的20250724目录不包含分区keydt=。

   ext_csv_test03/
   └── 20250724/

3. 以部分静态分区和部分动态分区的形式，向外部表写入数据

   形式上是动态分区写入，但由于values中的分区对应数据只有一个值'xxx'，所以在MaxCompute的逻辑里是部分静态分区写入，部分动态分区写入。

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250724'), (2, 'xxx','20250724');
   
   SELECT * FROM ext_csv_test03;
   
   -- 返回结果如下：
   -- 因为生成的OSS分区目录不符合要求，所以无法查出新插入的数据。只能查出步骤2静态写入的数据。
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   OSS生成的目录如下，可以看到生成的dt=20250724目录包含分区keydt，所以此场景的写法不符合要求。

   ext_csv_test03/
   ├── 20250724/
   └── dt=20250724/

4. 以动态分区的形式，向外部表写入常量数据

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250725'), (2, 'yyy','20250726');
   
   SELECT * FROM ext_csv_test03;
   
   -- 返回结果如下：
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 2    | yyy  | 20250726 |
   | 1    | xxx  | 20250725 |
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   OSS生成的目录如下，可以看到生成的20250725和20250726目录不包含分区keydt=。

   ext_csv_test03/
   ├── 20250724/
   ├── 20250725/
   └── 20250726/

5. 以动态分区的形式，向外部表写入SELECT子句的数据

   1. 准备测试表

      CREATE TABLE table03 (id INT, name STRING, dt STRING);
      INSERT INTO table03
        VALUES (6, 'fff', '20250725'), (7, 'ggg', '20250725'), (8, 'hhh', '20250723');

   2. 将测试表数据以SELECT子句的形式写入外部表

      INSERT OVERWRITE ext_csv_test03 PARTITION(dt) SELECT * FROM table03;
      
      SELECT * FROM ext_csv_test03;
      -- 返回结果如下：
      +------+------+----------+
      | id   | name | dt       |
      +------+------+----------+
      | 2    | yyy  | 20250726 |
      | 8    | hhh  | 20250723 |
      | 6    | fff  | 20250725 |
      | 7    | ggg  | 20250725 |
      | 1    | aaa  | 20250724 |
      | 2    | bbb  | 20250724 |
      | 3    | ccc  | 20250724 |
      +------+------+----------+

      OSS生成的目录如下，可以看到生成的20250723和20250725目录不包含分区keydt=。

      ext_csv_test03/
      ├── 20250723/
      ├── 20250724/
      ├── 20250725/
      └── 20250726/

场景说明

当需要查询OSS外部表有数据的最大分区的数据时，可使用MAX_PT函数。更多操作请参考MAX_PT。

注意事项

• 当OSS外部表有多级分区时，MAX_PT只返回第一层有数据的分区中最大的分区值，且这个分区可以通过SHOW PARTITIONS列出。

• 若在OSS目录下找不到任何有文件存在的分区，执行MAX_PT函数时会报错。

• 对一个非分区表执行MAX_PT函数会报错。

语法格式

SELECT * FROM <mc_oss_extable_name>
WHERE <pt_col_name> = MAX_PT("<mc_oss_extable_name>");

-- 或者如下写法。
SELECT * FROM <mc_oss_extable_name>
WHERE <pt_col_name> = (SELECT MAX(<pt_col_name>) FROM <mc_oss_extable_name>);

使用示例

以CSV分区外部表为例，其他OSS格式外部表写入数据的方式相同。

1. 创建OSS分区外部表并写入数据

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external9
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitude DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   PARTITIONED BY (
     y STRING, m STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   WITH SERDEPROPERTIES (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   LOCATION 'oss://oss-cn-<region>-internal.aliyuncs.com/<bucket>/mc_oss_csv_external9';
   
   INSERT INTO mc_oss_csv_external9 PARTITION(y='2023', m='03') VALUES (1, 89, 76, 1, 46.81606, -92.08174, '9/14/2014 0:10', 'SW');
   INSERT INTO mc_oss_csv_external9 PARTITION(y='2023', m='02') VALUES (1, 14, 76, 1, 47.90836, -92.08174, '9/14/2014 0:10', 'W');
   INSERT INTO mc_oss_csv_external9 PARTITION(y='2022', m='03') VALUES (1, 56, 76, 1, 48.81748, -92.08174, '9/14/2014 0:10', 'S');
   INSERT INTO mc_oss_csv_external9 PARTITION(y='2022', m='02') VALUES (1, 78, 76, 1, 49.81678, -92.08174, '9/14/2014 0:10', 'SE');

2. OSS分区目录

   mc_oss_csv_external9/
   ├── y=2022/
         ├── m=02/
              └── d.csv
         └── m=03/
              └── c.csv
   └── y=2023/
         ├── m=02/
              └── b.csv
         └── m=03/
              └── a.csv

3. 多级分区的OSS外部表，针对不同的目录下为空的场景，MAX_PT返回值是不同的，具体如下。

   1. 当OSS分区目录均不为空时，MAX_PT函数返回一级分区2023。

      -- 查询外表的最大分区。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 返回结果：
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   2. 当OSS分区目录的最大一级目录部分为空时，MAX_PT函数返回一级分区2023。

      mc_oss_csv_external9/
      ├── y=2022/
            ├── m=02/
                 └── d.csv
            └── m=03/
                 └── c.csv
      └── y=2023/
            ├── m=02/
                 └── b.csv
            └── m=03/  -- 为空

      -- 查询外表的最大分区。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 返回结果：
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   3. 当OSS分区目录的最大一级目录完全为空时，MAX_PT函数返回一级分区2022。

      mc_oss_csv_external9/
      ├── y=2022/
            ├── m=02/
                 └── d.csv
            └── m=03/
                 └── c.csv
      └── y=2023/
            ├── m=02/  -- 为空
            └── m=03/  -- 为空

      -- 查询外表的最大分区。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 返回结果：
      +-----+
      | _c0 |
      +-----+
      | 2022 |
      +-----+

   4. 当分区目录的所有目录完全为空时，MAX_PT函数报错。

      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 返回报错：
      FAILED: ODPS-0130071:[1,8] Semantic analysis exception - 
        encounter runtime exception while evaluating function MAX_PT, 
        detailed message: table "project.default.mc_oss_csv_external9" 
        has no partitions or none of the partitions have any data