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, o que elimina a necessidade de adição individual.

• Adicionar poucas partições novas a uma tabela com muitas existentes torna a execução frequente do comando MSCK ineficiente. Essa prática varre repetidamente todo o diretório do OSS e aciona atualizações de metadados, degradando o desempenho. Para atualizações incrementais, recomendamos add partitions 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 tabela externa do OSS.

1. Prepare os dados da partição

   1. Faça login no OSS console.

   2. No bucket, crie um diretório Demo2. Nesse diretório, crie subdiretórios com base na coluna de partição direction (direction=N, direction=NE, direction=S, direction=SW e direction=W) e faça upload dos arquivos de dados correspondentes (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv e vehicle5.csv) em cada subdiretório. Para obter mais informações, consulte Appendix: Prepare sample data.

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.

Este 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 separada 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 corresponde a um subdiretório; portanto, use várias cláusulas ADD PARTITION para múltiplos 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 para adicionar partições é o mesmo para outros formatos de tabela externa do OSS.

1. Prepare os dados da partição

   1. Faça login no OSS console.

   2. No bucket, crie um diretório Demo2. Nesse diretório, crie subdiretórios com base na coluna de partição direction (direction=N, direction=NE, direction=S, direction=SW e direction=W) e faça upload dos arquivos de dados correspondentes (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv e vehicle5.csv) em cada subdiretório. Para obter mais informações, consulte Appendix: Prepare sample data.

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 obter mais informações, consulte Partition operations.

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 exemplo mc_oss_csv_external3 do exemplo Add partitions.

   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 das partições 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). Use o recurso Keyless Partition para gravar em um caminho de partição do OSS sem a chave de partição.

Limitações

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

• Este recurso não é suportado 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 igualmente 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

   Formalmente, trata-se de uma gravação de partição dinâmica. Contudo, como a coluna de partição na cláusula values tem apenas um valor distinto, 'xxx', o MaxCompute a processa como uma gravação 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, 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 por meio de uma 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 |
      +------+------+----------+

      Nos 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 dados em uma tabela externa do OSS. Para obter 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. É possível listar essa partição com 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 resulta em 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 tabela externa 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ções multinível, o valor retornado pela MAX_PT varia conforme os diretórios vazios, conforme descrito abaixo.

   1. Se todos os diretórios de partição do OSS contiverem dados, a função MAX_PT retornará 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 retornará 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 retornará 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 de partição estiverem completamente vazios, a função MAX_PT retornará 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