MaxCompute:Operasi partisi untuk tabel eksternal OSS

Skenario

Saat membuat tabel eksternal OSS terpartisi, Anda juga harus memuat data partisinya. MaxCompute secara otomatis menemukan partisi dengan mengurai struktur direktori OSS dan menambahkan metadata partisi ke tabel eksternal.

• Perintah MSCK sesuai untuk operasi satu kali guna memuat semua partisi historis. MaxCompute secara otomatis menemukan dan menambahkan partisi ke tabel eksternal OSS berdasarkan direktori partisi yang ditentukan saat pembuatan tabel, sehingga menghilangkan kebutuhan untuk menambahkannya satu per satu.

• Jika hanya beberapa partisi baru yang ditambahkan ke tabel yang sudah memiliki banyak partisi, menjalankan perintah MSCK berulang kali tidak efisien karena akan memindai seluruh direktori OSS berulang kali dan memicu pembaruan metadata, yang berdampak negatif pada performa. Untuk pembaruan partisi inkremental, disarankan untuk add partitions secara manual.

Sintaks

MSCK REPAIR TABLE <mc_oss_extable_name>;

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

Contoh

Bagian ini menggunakan tabel eksternal CSV terpartisi sebagai contoh. Perintah MSCK digunakan dengan cara yang sama untuk format tabel eksternal OSS lainnya.

1. Persiapkan data partisi

   1. Masuk ke Konsol Object Storage Service (OSS).

   2. Di bucket tersebut, buat direktori Demo2. Di dalam direktori ini, buat subdirektori berdasarkan kolom partisi direction (direction=N, direction=NE, direction=S, direction=SW, dan direction=W), lalu unggah file data yang sesuai (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv, dan vehicle5.csv) ke masing-masing subdirektori. Untuk informasi lebih lanjut, lihat Appendix: Prepare sample data.

2. Buat tabel eksternal OSS terpartisi

   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. Muat partisi

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;

4. Kueri tabel eksternal OSS

   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE';
   
   -- Hasil berikut dikembalikan:
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Skenario

Jika tabel eksternal OSS merupakan tabel partisi, Anda harus melakukan operasi tambahan untuk memuat data partisinya. Untuk menambahkan informasi partisi secara manual, gunakan perintah ALTER TABLE ADD PARTITION.

Metode ini ideal untuk menambahkan partisi baru secara berkala setelah partisi historis dimuat. Dengan menambahkan partisi sebelum menulis data ke dalamnya, Anda memastikan tabel eksternal dapat langsung membaca data baru tanpa perlu langkah refresh metadata terpisah.

Sintaks

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

• Nilai col_name dan col_value harus sesuai dengan nama direktori yang berisi file data partisi. Satu klausa ADD PARTITION bersesuaian dengan satu subdirektori, dan diperlukan beberapa klausa ADD PARTITION untuk beberapa subdirektori OSS.

• Sebagai contoh, dalam struktur direktori OSS untuk file data partisi, col_name bersesuaian dengan direction, dan col_value bersesuaian dengan N, NE, S, SW, atau W.

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

Contoh

Bagian ini menggunakan tabel eksternal CSV terpartisi sebagai contoh. Metode penambahan partisi sama untuk format tabel eksternal OSS lainnya.

1. Persiapkan data partisi

   1. Masuk ke Konsol Object Storage Service (OSS).

   2. Di bucket tersebut, buat direktori Demo2. Di dalam direktori ini, buat subdirektori berdasarkan kolom partisi direction (direction=N, direction=NE, direction=S, direction=SW, dan direction=W), lalu unggah file data yang sesuai (vehicle1.csv, vehicle2.csv, vehicle3.csv, vehicle4.csv, dan vehicle5.csv) ke masing-masing subdirektori. Untuk informasi lebih lanjut, lihat Appendix: Prepare sample data.

2. Buat tabel eksternal OSS terpartisi

   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. Tambahkan partisi

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

4. Kueri tabel eksternal OSS

   SELECT * FROM mc_oss_csv_external3 WHERE direction='NE';
   
   -- Hasil berikut dikembalikan:
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+
   | 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        |
   +-----------+----------+-----------+-------+------------------+-------------------+------------+-----------+

Skenario

Anda dapat menghapus satu atau beberapa partisi dari tabel eksternal OSS terpartisi. Untuk informasi lebih lanjut, lihat Partition operations.

Sintaks

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

Contoh

1. Lihat daftar partisi tabel sampel mc_oss_csv_external3 dari contoh Add a partition.

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- Hasil berikut dikembalikan:
   direction=N
   direction=NE
   direction=S
   direction=SW
   direction=W
   
   OK

2. Drop partisi direction=S dan direction=SW.

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

3. Lihat kembali daftar partisi untuk memastikan bahwa partisi direction=S dan direction=SW telah dihapus.

   SHOW PARTITIONS mc_oss_csv_external3;
   
   -- Hasil berikut dikembalikan:
   direction=N
   direction=NE
   direction=W
   
   OK

Skenario

Dalam skenario ini, saat menulis data ke tabel eksternal OSS terpartisi, direktori partisi OSS yang dihasilkan biasanya mencakup kunci dan nilai partisi (misalnya, dt=20250724). Jika Anda ingin menulis ke path partisi OSS yang tidak menyertakan kunci partisi, Anda dapat menggunakan fitur Keyless Partition.

Batasan

• Jika Anda menentukan nilai tetap value saat menulis data ke tabel eksternal menggunakan partisi dinamis, parameter Keyless Partition tidak berpengaruh. Direktori OSS yang dihasilkan menggunakan format <pt_col_name>=<pt_col_value>, dan Anda tidak dapat membaca data dari direktori tersebut. Contohnya, INSERT OVERWRITE ext_tb PARTITION(dt) VALUES(1, 'xxx','20250724'), (2, 'xxx','20250724').

• Fitur ini tidak didukung untuk tabel eksternal Paimon, Hudi, dan Delta Lake.

Sintaks

Tambahkan parameter TBLPROPERTIES saat membuat tabel eksternal. Berikut adalah contoh untuk tabel eksternal 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'); -- Saat parameter ini bernilai true, direktori partisi OSS output tidak menyertakan kunci partisi.

Contoh

Bagian ini menggunakan tabel eksternal CSV terpartisi sebagai contoh. Metodenya sama untuk format lainnya.

1. Buat tabel eksternal CSV terpartisi

   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. Tulis data konstan ke partisi statis

   INSERT INTO ext_csv_test03 PARTITION (dt='20250724')
     VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
   
   SELECT * FROM ext_csv_test03;
   -- Hasil berikut dikembalikan:
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   Direktori yang dihasilkan oleh OSS adalah sebagai berikut: direktori 20250724 tidak berisi kunci partisi dt=.

   ext_csv_test03/
   └── 20250724/

3. Tulis data menggunakan satu nilai partisi dinamis

   Secara formal, ini merupakan penulisan partisi dinamis, tetapi karena data untuk kolom partisi dalam klausa values hanya memiliki satu nilai, 'xxx', maka dalam logika MaxCompute dianggap sebagai penulisan partisi yang sebagian statis dan sebagian dinamis.

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250724'), (2, 'xxx','20250724');
   
   SELECT * FROM ext_csv_test03;
   
   -- Hasil berikut dikembalikan:
   -- Karena direktori partisi OSS yang dihasilkan berada dalam format yang tidak diharapkan, data yang baru dimasukkan tidak dapat dikueri. Hanya data dari penulisan statis pada Langkah 2 yang dikembalikan.
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   Seperti terlihat pada direktori yang dihasilkan oleh OSS, direktori dt=20250724 yang dihasilkan berisi kunci partisi dt, sehingga sintaks yang digunakan dalam skenario ini salah.

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

4. Tulis data konstan menggunakan partisi dinamis

   INSERT OVERWRITE ext_csv_test03 PARTITION(dt)
     VALUES (1, 'xxx','20250725'), (2, 'yyy','20250726');
   
   SELECT * FROM ext_csv_test03;
   
   -- Hasil berikut dikembalikan:
   +------+------+----------+
   | id   | name | dt       |
   +------+------+----------+
   | 2    | yyy  | 20250726 |
   | 1    | xxx  | 20250725 |
   | 1    | aaa  | 20250724 |
   | 2    | bbb  | 20250724 |
   | 3    | ccc  | 20250724 |
   +------+------+----------+

   Direktori yang dihasilkan oleh OSS adalah sebagai berikut. Direktori 20250725 dan 20250726 yang dihasilkan tidak berisi kunci partisi dt=.

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

5. Tulis data dari klausa SELECT menggunakan partisi dinamis.

   1. Persiapkan tabel uji.

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

   2. Tulis data dari tabel uji melalui klausa SELECT

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

      Seperti terlihat pada direktori yang dihasilkan oleh OSS, direktori 20250723 dan 20250725 tidak berisi kunci partisi dt=.

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

Skenario

Anda dapat menggunakan fungsi MAX_PT untuk mengkueri data dari partisi maksimum yang berisi data dalam tabel eksternal OSS. Untuk informasi lebih lanjut, lihat MAX_PT.

Pertimbangan

• Saat tabel eksternal OSS memiliki partisi multi-level, MAX_PT hanya mengembalikan nilai partisi maksimum di antara partisi level pertama yang berisi data, dan partisi tersebut dapat ditampilkan menggunakan SHOW PARTITIONS.

• Jika tidak ditemukan partisi yang berisi file di direktori OSS, fungsi MAX_PT mengembalikan error.

• Menjalankan fungsi MAX_PT pada tabel non-partisi akan menghasilkan error.

Sintaks

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

-- Anda juga dapat menggunakan sintaks berikut.
SELECT * FROM <mc_oss_extable_name>
WHERE <pt_col_name> = (SELECT MAX(<pt_col_name>) FROM <mc_oss_extable_name>);

Contoh

Bagian ini menggunakan tabel eksternal CSV terpartisi sebagai contoh. Fungsi MAX_PT bekerja dengan cara yang sama untuk format tabel eksternal OSS lainnya.

1. Buat tabel eksternal OSS terpartisi dan tulis data ke dalamnya

   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. Direktori partisi 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. Untuk tabel eksternal OSS dengan partisi multi-level, nilai kembali MAX_PT bervariasi tergantung direktori mana yang kosong, sebagai berikut.

   1. Saat semua direktori partisi OSS tidak kosong, fungsi MAX_PT mengembalikan partisi level pertama 2023.

      -- Kueri partisi maksimum tabel eksternal.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- Hasil berikut dikembalikan:
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   2. Saat bagian direktori tingkat atas dari direktori partisi OSS kosong, fungsi MAX_PT mengembalikan partisi level pertama 2023.

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

      -- Kueri partisi maksimum tabel eksternal.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- Hasil berikut dikembalikan:
      +-----+
      | _c0 |
      +-----+
      | 2023 |
      +-----+

   3. Saat direktori level pertama terbesar dari direktori partisi OSS benar-benar kosong, fungsi MAX_PT mengembalikan partisi level pertama 2022.

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

      -- Kueri partisi maksimum tabel eksternal.
      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- Hasil berikut dikembalikan:
      +-----+
      | _c0 |
      +-----+
      | 2022 |
      +-----+

   4. Saat semua direktori dalam direktori partisi benar-benar kosong, fungsi MAX_PT menghasilkan error.

      SELECT MAX_PT("mc_oss_csv_external9");
      
      -- Error berikut dikembalikan:
      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