Partition operations for OSS external tables

Scenarios

When you create a partitioned OSS external table, you must also load its partition data. MaxCompute automatically discovers partitions by parsing the OSS directory structure and adds the partition metadata to the external table.

• The MSCK command is suitable for one-time operations to load all historical partitions. MaxCompute automatically discovers and adds partitions to the OSS external table based on the partition directory specified during table creation. This eliminates the need to add them individually.

• If you are adding only a few new partitions to a table that already has many, frequently running the MSCK command is inefficient. It repeatedly scans the entire OSS directory and triggers metadata updates, which degrades performance. For incremental partition updates, it is recommended that you add partitions manually instead.

Syntax

MSCK REPAIR TABLE <mc_oss_extable_name>;

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

Examples

This section uses a partitioned CSV external table as an example. The MSCK command is used in the same way for other OSS external table formats.

1. Prepare the partition data

   1. Log on to the Object Storage Service (OSS) console.

   2. In the bucket, create a Demo2 directory. In this directory, create subdirectories based on the partition column direction (direction=N, direction=NE, direction=S, direction=SW, and direction=W), and upload the corresponding data files (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv, and vehicle5.csv) to each subdirectory. For more information, see Appendix: Prepare sample data.

2. Create a partitioned OSS external table

   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. Load partitions

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;

4. Query the OSS external table

   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE';
   
   -- The following result is returned:
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Scenarios

If an OSS external table is a partitioned table, you must perform an additional operation to load its partition data. To manually add the partition information, you can use the ALTER TABLE ADD PARTITION command.

This method is ideal for adding new partitions on a regular basis after the historical partitions are loaded. By adding a partition before writing data to it, you ensure the external table can read the new data immediately, without a separate metadata refresh step.

Syntax

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

• The values of col_name and col_value must align with the name of the directory that contains the partition data files. One ADD PARTITION clause corresponds to one subdirectory, and multiple ADD PARTITION clauses are required for multiple OSS subdirectories.

• For example, in the OSS directory structure for the partition data files, col_name corresponds to direction, and col_value corresponds to N, NE, S, SW, or W.

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

Examples

This section uses a partitioned CSV external table as an example. The method for adding partitions is the same for other OSS external table formats.

1. Prepare the partition data

   1. Log on to the Object Storage Service (OSS) console.

   2. In the bucket, create a Demo2 directory. In this directory, create subdirectories based on the partition column direction (direction=N, direction=NE, direction=S, direction=SW, and direction=W), and upload the corresponding data files (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv, and vehicle5.csv) to each subdirectory. For more information, see Appendix: Prepare sample data.

2. Create a partitioned OSS external table

   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. Add partitions

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

4. Query the OSS external table

   SELECT * FROM mc_oss_csv_external3 WHERE direction='NE';
   
   -- The following result is returned:
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Scenarios

You can drop one or more partitions from a partitioned OSS external table. For more information, see Partition operations.

Syntax

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

Examples

1. View the partition list of the sample table mc_oss_csv_external3 from the Add a partition example.

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- The following result is returned:
   direction=N
   direction=NE
   direction=S
   direction=SW
   direction=W
   
   OK

2. Drop the direction=S and direction=SW partitions.

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

3. View the partition list again to confirm that the direction=S and direction=SW partitions have been dropped.

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- The following result is returned:
   direction=N
   direction=NE
   direction=W
   
   OK

Scenarios

In this scenario, when you write data to a partitioned OSS external table, the generated OSS partition directory includes the partition key and value (for example, dt=20250724). If you want to write to an OSS partition path that does not include the partition key, you can use the Keyless Partition feature.

Limitations

• If you specify a fixed value when writing data to an external table by using dynamic partitions, the Keyless Partition parameter has no effect. An OSS directory is generated in the <pt_col_name>=<pt_col_value> format, and you cannot read the data from this directory. For example, INSERT OVERWRITE ext_tb PARTITION(dt) VALUES(1, 'xxx','20250724'), (2, 'xxx','20250724')

• This feature is not supported for Paimon, Hudi, and Delta Lake external tables.

Syntax

Add the TBLPROPERTIES parameter when you create an external table. The following is an example for a CSV external table.

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'); -- When this parameter is true, the output OSS partition directory does not include the partition key.

Examples

This section uses a partitioned CSV external table as an example. The method is the same for other formats.

1. Create a partitioned CSV external table

   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. Write constant data to a static partition

   INSERT INTO ext_csv_test03 PARTITION (dt='20250724')
     VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
   
   SELECT * FROM ext_csv_test03;
   -- The following result is returned:
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   The directory generated by OSS is as follows: the 20250724 directory does not contain the partition key dt=.

   ext_csv_test03/
   └── 20250724/

3. Write data using a single dynamic partition value

   Formally, this is a dynamic partition write, but because the data for the partition column in the values clause has only one value, 'xxx', it is considered a partially static and partially dynamic partition write in MaxCompute's logic.

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250724'), (2, 'xxx','20250724');
   
   SELECT * FROM ext_csv_test03;
   
   -- The following result is returned:
   -- Because the generated OSS partition directory is in an unexpected format, the newly inserted data cannot be queried. Only the data from the static write in Step 2 is returned.
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   As you can see in the directory generated by OSS, the generated dt=20250724 directory contains the partition key dt, so the syntax used in this scenario is incorrect.

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

4. Write constant data using dynamic partitions

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250725'), (2, 'yyy','20250726');
   
   SELECT * FROM ext_csv_test03;
   
   -- The following result is returned:
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 2    | yyy  | 20250726 |
   | 1    | xxx  | 20250725 |
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   The directories generated by OSS are as follows. The generated 20250725 and 20250726 directories do not contain the partition key dt=.

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

5. Write data from a SELECT clause using dynamic partitions.

   1. Prepare a test table.

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

   2. Write data from the test table via a SELECT clause

      INSERT OVERWRITE ext_csv_test03 PARTITION(dt) SELECT * FROM table03;
      
      SELECT * FROM ext_csv_test03;
      -- The following result is returned:
      +------+------+----------+
      | id   | name | dt       |
      +------+------+----------+
      | 2    | yyy  | 20250726 |
      | 8    | hhh  | 20250723 |
      | 6    | fff  | 20250725 |
      | 7    | ggg  | 20250725 |
      | 1    | aaa  | 20250724 |
      | 2    | bbb  | 20250724 |
      | 3    | ccc  | 20250724 |
      +------+------+----------+

      As you can see from the directories generated by OSS, the 20250723 and 20250725 directories do not contain the partition key dt=.

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

Scenarios

You can use the MAX_PT function to query data from the maximum partition that contains data in an OSS external table. For more information, see MAX_PT.

Considerations

• When an OSS external table has multi-level partitions, MAX_PT returns only the maximum partition value among the first-level partitions that contain data, and this partition can be listed by using SHOW PARTITIONS.

• If no partitions containing files are found in the OSS directory, the MAX_PT function returns an error.

• Executing the MAX_PT function on a non-partitioned table reports an error.

Syntax

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

-- You can also use the following syntax.
SELECT * FROM <mc_oss_extable_name>
WHERE <pt_col_name> = (SELECT MAX(<pt_col_name>) FROM <mc_oss_extable_name>);

Examples

This section uses a partitioned CSV external table as an example. The MAX_PT function works the same way for other OSS external table formats.

1. Create a partitioned OSS external table and write data to it

   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 partition directory

   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. For an OSS external table with multi-level partitions, the return value of MAX_PT varies depending on which directory is empty, as follows.

   1. When all OSS partition directories are not empty, the MAX_PT function returns the first-level partition 2023.

      -- Query the maximum partition of the external table.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- The following result is returned:
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   2. When the top-level directory part of the OSS partition directory is empty, the MAX_PT function returns the first-level partition 2023.

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

      -- Query the maximum partition of the external table.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- The following result is returned:
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   3. When the largest first-level directory of an OSS partition directory is completely empty, the MAX_PT function returns the first-level partition 2022.

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

      -- Query the maximum partition of the external table.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- The following result is returned:
      +-----+
      | _c0 |
      +-----+
      | 2022 |
      +-----+

   4. When all directories in the partition directory are completely empty, the MAX_PT function reports an error.

      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- The following error is returned:
      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