MaxCompute:Operações de partição para tabelas externas do OSS

Cenários

Tabelas externas particionadas no OSS exigem o carregamento dos dados de partição. O MaxCompute analisa a estrutura de diretórios do OSS para descobrir partições e adiciona os metadados correspondentes à tabela externa.

• O comando MSCK é adequado para operações únicas de carregamento de todas as partições históricas. O MaxCompute descobre e adiciona partições com base no diretório especificado durante a criação da tabela, sem necessidade de adição individual.

• Adicionar poucas partições novas a uma tabela já extensa torna a execução frequente do MSCK ineficiente. Esse processo varre repetidamente todo o diretório do OSS e aciona atualizações de metadados, degradando o desempenho. Para atualizações incrementais, recomendamos adicionar partições manualmente.

Sintaxe

MSCK REPAIR TABLE <mc_oss_extable_name>;

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

Exemplos

O exemplo a seguir usa uma tabela externa CSV particionada. O comando MSCK funciona da mesma forma para outros formatos de tabelas externas do OSS.

1. Prepare os dados da partição

   1. Faça login no OSS console.

   2. No bucket, crie o diretório Demo2. Dentro dele, crie subdiretórios baseados na coluna de partição direction (direction=N, direction=NE, direction=S, direction=SW e direction=W) e envie os arquivos de dados correspondentes (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv e vehicle5.csv) para cada subdiretório. Para mais informações, consulte Apêndice: Preparar dados de amostra.

2. Crie uma tabela externa particionada no 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. Carregue as partições

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;

4. Consulte a tabela externa do 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Cenários

Tabelas externas particionadas no OSS exigem o carregamento dos dados de partição. Para adicionar informações de partição manualmente, use o comando ALTER TABLE ADD PARTITION.

Esse método é ideal para adicionar novas partições regularmente após o carregamento das partições históricas. Adicionar a partição antes de gravar dados nela garante que a tabela externa leia os novos dados imediatamente, sem etapa adicional de atualização de metadados.

Sintaxe

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

• Os valores de col_name e col_value devem corresponder ao nome do diretório que contém os arquivos de dados da partição. Cada cláusula ADD PARTITION refere-se a um subdiretório; portanto, use múltiplas cláusulas ADD PARTITION para vários subdiretórios do OSS.

• Por exemplo, na estrutura de diretórios do OSS para os arquivos de dados da partição, col_name corresponde a direction e col_value corresponde a N, NE, S, SW ou W.

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

Exemplos

O exemplo a seguir usa uma tabela externa CSV particionada. O método de adição de partições aplica-se igualmente a outros formatos de tabelas externas do OSS.

1. Prepare os dados da partição

2. Crie uma tabela externa particionada no 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. Adicione partições

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

4. Consulte a tabela externa do 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Cenários

Remova uma ou mais partições de uma tabela externa particionada no OSS. Para mais informações, consulte Operações de partição.

Sintaxe

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

Exemplos

1. Visualize a lista de partições da tabela de amostra mc_oss_csv_external3 do exemplo de Adicionar uma partição.

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

2. Remova as partições direction=S e direction=SW.

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

3. Visualize novamente a lista de partições para confirmar a remoção de direction=S e direction=SW.

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

Cenários

Ao gravar dados em uma tabela externa particionada no OSS, o diretório de partição gerado inclui a chave e o valor da partição (por exemplo, dt=20250724). Para gravar em um caminho de partição do OSS sem a chave de partição, use o recurso Keyless Partition.

Limitações

• Especificar um valor fixo ao gravar dados em uma tabela externa com partições dinâmicas anula o efeito do parâmetro Keyless Partition. O sistema gera um diretório do OSS no formato <pt_col_name>=<pt_col_value>, impedindo a leitura dos dados. Exemplo: INSERT OVERWRITE ext_tb PARTITION(dt) VALUES(1, 'xxx','20250724'), (2, 'xxx','20250724')

• Este recurso não tem suporte para tabelas externas Paimon, Hudi e Delta Lake.

Sintaxe

Adicione o parâmetro TBLPROPERTIES ao criar uma tabela externa, conforme o exemplo de tabela externa CSV a seguir.

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.

Exemplos

Esta seção usa uma tabela externa CSV particionada como exemplo. O método aplica-se também a outros formatos.

1. Crie uma tabela externa CSV particionada

   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. Grave dados constantes em uma partição estática

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

   O diretório gerado pelo OSS é o seguinte: o diretório 20250724 não contém a chave de partição dt=.

   ext_csv_test03/
   └── 20250724/

3. Grave dados usando um único valor de partição dinâmica

   Embora formalmente seja uma gravação de partição dinâmica, como a coluna de partição na cláusula values tem apenas um valor distinto ('xxx'), o MaxCompute a trata como uma gravação mista (parcialmente estática e parcialmente dinâmica).

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

   No diretório gerado pelo OSS, observe que o diretório dt=20250724 contém a chave de partição dt. Portanto, a sintaxe usada neste cenário está incorreta.

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

4. Grave dados constantes usando partições dinâmicas

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

   Os diretórios gerados pelo OSS são os seguintes. Os diretórios 20250725 e 20250726 não contêm a chave de partição dt=.

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

5. Grave dados de uma cláusula SELECT usando partições dinâmicas.

   1. Prepare uma tabela de teste.

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

   2. Grave dados da tabela de teste via cláusula 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 |
      +------+------+----------+

      Conforme demonstrado pelos diretórios gerados pelo OSS, os diretórios 20250723 e 20250725 não contêm a chave de partição dt=.

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

Cenários

A função MAX_PT consulta dados da maior partição com conteúdo em uma tabela externa do OSS. Para mais informações, consulte MAX_PT.

Considerações

• Em tabelas externas do OSS com partições multinível, a MAX_PT retorna apenas o valor máximo entre as partições de primeiro nível que contêm dados. Essa partição pode ser listada via SHOW PARTITIONS.

• Se o diretório do OSS não tiver partições com arquivos, a função MAX_PT retornará um erro.

• Executar a função MAX_PT em uma tabela não particionada gera um erro.

Sintaxe

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

Exemplos

O exemplo a seguir usa uma tabela externa CSV particionada. A função MAX_PT funciona da mesma forma para outros formatos de tabelas externas do OSS.

1. Crie uma tabela externa particionada no OSS e grave dados nela

   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. Diretório de partição do 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. Em tabelas externas do OSS com partição multinível, o valor retornado pela MAX_PT varia conforme os diretórios vazios, conforme descrito a seguir.

   1. Se todos os diretórios de partição do OSS contiverem dados, a função MAX_PT retorna a partição de primeiro nível 2023.

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

   2. Se o subdiretório de nível superior da partição do OSS estiver vazio, a função MAX_PT ainda retorna a partição de primeiro nível 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. Se o maior diretório de primeiro nível da partição do OSS estiver completamente vazio, a função MAX_PT retorna a partição de primeiro nível 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. Se todos os diretórios dentro da partição estiverem completamente vazios, a função MAX_PT gera um erro.

      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