MaxCompute:OSS 外部テーブルのパーティション操作

シナリオ

パーティション化された OSS 外部テーブルを作成する際は、パーティションデータもロードする必要があります。MaxCompute は、OSS ディレクトリ構造を解析することでパーティションを自動的に検出し、外部テーブルにパーティションメタデータを追加します。

• MSCK コマンドは、すべての過去のパーティションをロードするための1回限りの操作に適しています。MaxCompute は、テーブル作成時に指定されたパーティションディレクトリに基づいて、OSS 外部テーブルにパーティションを自動的に検出し、追加します。これにより、個別にパーティションを追加する必要がなくなります。

• すでに多くのパーティションを持つテーブルに少数の新しいパーティションのみを追加する場合、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 外部テーブルを例として使用します。MSCK コマンドは、他の OSS 外部テーブル形式でも同様に使用されます。

1. パーティションデータの準備

   1. Object Storage Service (OSS) コンソールにログインします。

   2. バケット内に 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 外部テーブルがパーティションテーブルである場合、パーティションデータをロードするために追加の操作を実行する必要があります。パーティション情報を手動で追加するには、ALTER TABLE ADD PARTITION コマンドを使用できます。

この方法は、過去のパーティションがロードされた後、定期的に新しいパーティションを追加するのに理想的です。パーティションにデータを書き込む前にパーティションを追加することで、個別のメタデータリフレッシュステップなしで、外部テーブルが新しいデータをすぐに読み取れるようになります。

構文

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

• col_name と col_value の値は、パーティションデータファイルを含むディレクトリの名前と一致する必要があります。1つの ADD PARTITION 句は1つのサブディレクトリに対応し、複数の 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 (OSS) コンソールにログインします。

   2. バケット内に 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 外部テーブルから1つ以上のパーティションを削除できます。詳細については、「パーティション操作」をご参照ください。

構文

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 パーティションディレクトリにはパーティションキーと値 (dt=20250724 など) が含まれます。パーティションキーを含まない OSS パーティションパスに書き込みたい場合は、キーレスパーティション機能を使用できます。

制限事項

• 動的パーティションを使用して外部テーブルにデータを書き込む際に固定の 値 を指定した場合、キーレスパーティションパラメーターは効果がありません。OSS ディレクトリは <pt_col_name>=<pt_col_value> 形式で生成され、このディレクトリからデータを読み取ることはできません。例：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 パーティションディレクトリにはパーティションキーが含まれません。

例

このセクションでは、パーティション化された 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 ディレクトリにはパーティションキー dt= が含まれていません。

   ext_csv_test03/
   └── 20250724/

3. 単一の動的パーティション値を使用したデータ書き込み

   形式的には、これは動的パーティション書き込みですが、values 句のパーティション列のデータが 'xxx' という1つの値しか持たないため、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 ディレクトリにはパーティションキー dt が含まれているため、このシナリオで使用される構文は正しくありません。

   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 ディレクトリにはパーティションキー dt= が含まれていません。

   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 ディレクトリにはパーティションキー dt= が含まれていません。

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

シナリオ

OSS 外部テーブル内でデータを含む最大パーティションからデータをクエリするには、MAX_PT 関数を使用できます。詳細については、「MAX_PT」をご参照ください。

考慮事項

• OSS 外部テーブルに多階層パーティションがある場合、MAX_PT はデータを含む第1レベルのパーティションの中で最大パーティション値のみを返し、このパーティションは 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 外部テーブルを例として使用します。MAX_PT 関数は、他の 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 関数は第1レベルのパーティション 2023 を返します。

      -- 外部テーブルの最大パーティションをクエリします。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 次の結果が返されます。
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   2. OSS パーティションディレクトリのトップレベルディレクトリ部分が空の場合、MAX_PT 関数は第1レベルのパーティション 2023 を返します。

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

      -- 外部テーブルの最大パーティションをクエリします。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 次の結果が返されます。
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   3. OSS パーティションディレクトリの最大の第1レベルディレクトリが完全に空の場合、MAX_PT 関数は第1レベルのパーティション 2022 を返します。

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

      -- 外部テーブルの最大パーティションをクエリします。
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 次の結果が返されます。
      +-----+
      | _c0 |
      +-----+
      | 2022 |
      +-----+

   4. パーティションディレクトリ内のすべてのディレクトリが完全に空の場合、MAX_PT 関数はエラーを報告します。

      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- 次のエラーが返されます。
      FAILED: ODPS-0130071:[1,8] セマンティック解析例外 - MAX_PT 関数を評価中にランタイム例外が発生しました。詳細メッセージ: テーブル "project.default.mc_oss_csv_external9" にパーティションがないか、どのパーティションにもデータがありません。