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. 登入Object Storage Service控制台。

   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. 登入Object Storage Service控制台。

   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