MaxCompute:Opérations sur les partitions des tables externes OSS

Scénarios

Une table externe OSS partitionnée nécessite le chargement de ses données de partition. MaxCompute analyse la structure des répertoires OSS pour découvrir les partitions et ajoute les métadonnées de partition à la table externe.

• La commande MSCK est adaptée aux opérations ponctuelles permettant de charger toutes les partitions historiques. MaxCompute découvre et ajoute les partitions en fonction du répertoire de partition spécifié lors de la création de la table, ce qui évite de les ajouter individuellement.

• Si vous n'ajoutez que quelques nouvelles partitions à une table qui en contient déjà beaucoup, l'exécution fréquente de la commande MSCK s'avère inefficace. Elle analyse de manière répétée l'intégralité du répertoire OSS et déclenche des mises à jour de métadonnées, ce qui dégrade les performances. Pour les mises à jour incrémentielles des partitions, nous vous recommandons d'ajouter les partitions manuellement.

Syntaxe

MSCK REPAIR TABLE <mc_oss_extable_name>;

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

Exemples

L'exemple suivant utilise une table externe CSV partitionnée. La commande MSCK fonctionne de la même manière pour les autres formats de tables externes OSS.

1. Préparer les données de partition

   1. Connectez-vous à la console OSS.

   2. Dans le bucket, créez un répertoire Demo2. Dans ce répertoire, créez des sous-répertoires basés sur la colonne de partition direction (direction=N, direction=NE, direction=S, direction=SW et direction=W), puis téléchargez les fichiers de données correspondants (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv et vehicle5.csv) dans chaque sous-répertoire. Pour plus d'informations, consultez Annexe : Préparer les exemples de données.

2. Créer une table externe OSS partitionnée

   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. Charger les partitions

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;

4. Interroger la table externe OSS

   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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Scénarios

Une table externe OSS partitionnée nécessite le chargement de ses données de partition. Pour ajouter manuellement les informations de partition, utilisez la commande ALTER TABLE ADD PARTITION.

Cette méthode est idéale pour ajouter régulièrement de nouvelles partitions après le chargement des partitions historiques. En ajoutant une partition avant d'y écrire des données, vous garantissez que la table externe peut lire immédiatement les nouvelles données, sans étape distincte d'actualisation des métadonnées.

Syntaxe

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

• Les valeurs de col_name et col_value doivent correspondre au nom du répertoire contenant les fichiers de données de partition. Une clause ADD PARTITION correspond à un sous-répertoire ; plusieurs clauses ADD PARTITION sont nécessaires pour plusieurs sous-répertoires OSS.

• Par exemple, dans la structure de répertoires OSS pour les fichiers de données de partition, col_name correspond à direction, et col_value correspond à N, NE, S, SW ou W.

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

Exemples

L'exemple suivant utilise une table externe CSV partitionnée. La méthode d'ajout de partitions est identique pour les autres formats de tables externes OSS.

1. Préparer les données de partition

   1. Connectez-vous à la console OSS.

   2. Dans le bucket, créez un répertoire Demo2. Dans ce répertoire, créez des sous-répertoires basés sur la colonne de partition direction (direction=N, direction=NE, direction=S, direction=SW et direction=W), puis téléchargez les fichiers de données correspondants (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv et vehicle5.csv) dans chaque sous-répertoire. Pour plus d'informations, consultez Annexe : Préparer les exemples de données.

2. Créer une table externe OSS partitionnée

   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. Ajouter des partitions

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

4. Interroger la table externe OSS

   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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Scénarios

Supprimez une ou plusieurs partitions d'une table externe OSS partitionnée. Pour plus d'informations, consultez Opérations sur les partitions.

Syntaxe

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

Exemples

1. Consultez la liste des partitions de la table exemple mc_oss_csv_external3 à partir de l'exemple Ajouter des partitions.

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

2. Supprimez les partitions direction=S et direction=SW.

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

3. Consultez à nouveau la liste des partitions pour confirmer que les partitions direction=S et direction=SW ont été supprimées.

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

Scénarios

Lorsque vous écrivez des données dans une table externe OSS partitionnée, le répertoire de partition OSS généré inclut la clé et la valeur de partition (par exemple, dt=20250724). Pour écrire dans un chemin de partition OSS qui n'inclut pas la clé de partition, utilisez la fonctionnalité Keyless Partition.

Limites

• Si vous spécifiez une valeur fixe lors de l'écriture de données dans une table externe en utilisant des partitions dynamiques, le paramètre Keyless Partition n'a aucun effet. Un répertoire OSS est généré au format <pt_col_name>=<pt_col_value>, et vous ne pouvez pas lire les données depuis ce répertoire. Par exemple, INSERT OVERWRITE ext_tb PARTITION(dt) VALUES(1, 'xxx','20250724'), (2, 'xxx','20250724')

• Cette fonctionnalité n'est pas prise en charge pour les tables externes Paimon, Hudi et Delta Lake.

Syntaxe

Ajoutez le paramètre TBLPROPERTIES lors de la création d'une table externe, comme illustré dans l'exemple de table externe CSV suivant.

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.

Exemples

Cette section utilise une table externe CSV partitionnée comme exemple. La méthode est identique pour les autres formats.

1. Créer une table externe CSV partitionnée

   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. Écrire des données constantes dans une partition statique

   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 |
   +------+------+----------+

   Le répertoire généré par OSS est le suivant : le répertoire 20250724 ne contient pas la clé de partition dt=.

   ext_csv_test03/
   └── 20250724/

3. Écrire des données en utilisant une seule valeur de partition dynamique

   Formellement, il s'agit d'une écriture de partition dynamique, mais parce que la colonne de partition dans la clause values n'a qu'une seule valeur distincte, 'xxx', MaxCompute la traite comme une écriture partiellement statique et partiellement dynamique.

   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 |
   +------+------+----------+

   Comme vous pouvez le voir dans le répertoire généré par OSS, le répertoire dt=20250724 généré contient la clé de partition dt, donc la syntaxe utilisée dans ce scénario est incorrecte.

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

4. Écrire des données constantes en utilisant des partitions dynamiques

   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 |
   +------+------+----------+

   Les répertoires générés par OSS sont les suivants. Les répertoires 20250725 et 20250726 générés ne contiennent pas la clé de partition dt=.

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

5. Écrire des données à partir d'une clause SELECT en utilisant des partitions dynamiques.

   1. Préparez une table de test.

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

   2. Écrire des données depuis la table de test via une clause SELECT

      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 |
      +------+------+----------+

      Comme vous pouvez le constater à partir des répertoires générés par OSS, les répertoires 20250723 et 20250725 ne contiennent pas la clé de partition dt=.

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

Scénarios

La fonction MAX_PT interroge les données de la partition la plus récente contenant des données dans une table externe OSS. Pour plus d'informations, consultez MAX_PT.

Considérations

• Lorsqu'une table externe OSS possède des partitions multi-niveaux, MAX_PT renvoie uniquement la valeur de partition maximale parmi les partitions de premier niveau qui contiennent des données, et cette partition peut être listée en utilisant SHOW PARTITIONS.

• Si aucune partition contenant des fichiers n'est trouvée dans le répertoire OSS, la fonction MAX_PT renvoie une erreur.

• L'exécution de la fonction MAX_PT sur une table non partitionnée génère une erreur.

Syntaxe

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>);

Exemples

L'exemple suivant utilise une table externe CSV partitionnée. La fonction MAX_PT fonctionne de la même manière pour les autres formats de tables externes OSS.

1. Créer une table externe OSS partitionnée et y écrire des données

   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. Répertoire de partition 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. Pour une table externe OSS partitionnée multi-niveaux, la valeur de retour de MAX_PT varie selon le répertoire vide, comme suit.

   1. Lorsque tous les répertoires de partition OSS ne sont pas vides, la fonction MAX_PT renvoie la partition de premier niveau 2023.

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

   2. Lorsque la partie répertoire de premier niveau du répertoire de partition OSS est vide, la fonction MAX_PT renvoie la partition de premier niveau 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. Lorsque le plus grand répertoire de premier niveau d'un répertoire de partition OSS est complètement vide, la fonction MAX_PT renvoie la partition de premier niveau 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. Lorsque tous les répertoires du répertoire de partition sont complètement vides, la fonction MAX_PT signale une erreur.

      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