Gunakan tabel eksternal OSS terpartisi - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL mendukung tabel eksternal OSS terpartisi (OSS FDW). Jika kolom partisi disertakan dalam klausa WHERE suatu kueri, fitur ini dapat secara signifikan mengurangi jumlah data yang dipindai dari OSS, sehingga meningkatkan performa kueri.

Catatan penggunaan

Fitur tabel eksternal OSS terpartisi mensyaratkan bahwa objek di OSS mengikuti struktur direktori tertentu. Jalur direktori harus menggunakan format oss://bucket/partcol1=partval1/partcol2=partval2/, di mana partcol1 dan partcol2 adalah kolom partisi, sedangkan partval1 dan partval2 adalah nilai yang sesuai untuk partisi tersebut.

Sebagai contoh, jika sebuah tabel terpartisi dipartisi berdasarkan kolom year dan disubpartisi berdasarkan kolom month, objek untuk partisi tertentu, seperti year=2022 dan month=07, harus disimpan di direktori oss://bucket/year=2022/month=07/.

Buat server OSS, pemetaan pengguna OSS, dan OSS FDW

Sebelum menggunakan OSS FDW, Anda harus terlebih dahulu membuat server OSS, pemetaan pengguna OSS, dan OSS FDW.

Untuk informasi tentang cara membuat server OSS, lihat Create an OSS server.
Untuk informasi tentang cara membuat pemetaan pengguna OSS, lihat Create an OSS user mapping.
Untuk informasi tentang cara membuat OSS FDW, lihat Create an OSS foreign table.

Buat tabel terpartisi

Anda dapat menggunakan pernyataan CREATE FOREIGN TABLE untuk membuat tabel eksternal OSS terpartisi. Sintaksnya sama dengan pembuatan tabel terpartisi standar. Untuk informasi selengkapnya, lihat Defining partitioned tables.

Untuk informasi lebih lanjut mengenai sintaks CREATE FOREIGN TABLE, lihat Create an OSS foreign table.

Catatan

Tabel eksternal OSS saat ini hanya mendukung partisi list.

Buat tabel terpartisi bernama ossfdw_parttable dengan templat partisi:

CREATE FOREIGN TABLE ossfdw_parttable( 
  key text,
  value bigint,
  pt text, -- Kunci partisi
  region text -- Kunci subpartisi
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt) -- Partisi tabel berdasarkan kolom pt.
SUBPARTITION BY LIST (region) -- Subpartisi tabel berdasarkan kolom region.
    SUBPARTITION TEMPLATE ( -- Templat subpartisi
       SUBPARTITION hangzhou VALUES ('hangzhou'),
       SUBPARTITION shanghai VALUES ('shanghai')
    )
( PARTITION "20170601" VALUES ('20170601'), 
  PARTITION "20170602" VALUES ('20170602'));

Buat tabel terpartisi bernama ossfdw_parttable1 tanpa templat partisi:

CREATE FOREIGN TABLE ossfdw_parttable1( 
  key text,
  value bigint,
  pt text, -- Kunci partisi
  region text -- Kunci subpartisi
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt) -- Partisi tabel berdasarkan kolom pt.
SUBPARTITION BY LIST (region) -- Subpartisi tabel berdasarkan kolom region.
(
    -- Dua partisi berikut dapat memiliki subpartisi yang berbeda.
    VALUES('20181218')
    (
        VALUES('hangzhou'),
        VALUES('shanghai') 
    ),
    VALUES('20181219')
    (
        VALUES('nantong'),
        VALUES('anhui') 
    ) 
);

Ubah tabel eksternal OSS terpartisi

Gunakan pernyataan ALTER TABLE untuk mengubah tabel eksternal OSS terpartisi. AnalyticDB for PostgreSQL mendukung penambahan dan penghapusan partisi pada tabel eksternal OSS terpartisi.

Tambahkan partisi

Tambahkan partisi
- Untuk menambahkan partisi ke tabel ossfdw_parttable, jalankan pernyataan berikut. Karena tabel ini menggunakan templat partisi, sistem secara otomatis membuat subpartisi yang sesuai.
```
ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');
```
  Skema diperbarui sebagai berikut:
- Untuk menambahkan partisi ke tabel ossfdw_parttable1, jalankan pernyataan berikut. Karena tabel ini tidak menggunakan templat partisi, Anda harus secara eksplisit menentukan subpartisinya.
```
ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
(
    VALUES('hefei'),
    VALUES('guangzhou') 
);
```
Tambahkan subpartisi

Untuk menambahkan subpartisi ke partisi 20170603 dari tabel ossfdw_parttable, jalankan pernyataan berikut:
```
ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');
```
Skema diperbarui sebagai berikut:

Hapus partisi

Untuk menghapus partisi, jalankan pernyataan berikut:

ALTER TABLE ossfdw_parttable DROP PARTITION FOR ('20170601');

Untuk menghapus subpartisi, jalankan pernyataan berikut:

ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170602') DROP PARTITION FOR ('hangzhou');

Hapus tabel terpartisi

Gunakan pernyataan DROP FOREIGN TABLE untuk menghapus tabel terpartisi.

Contoh:

DROP FOREIGN TABLE ossfdw_parttable;

Akses data SLS dengan tabel eksternal OSS

Mengakses data yang dikirim dari Log Service (SLS) merupakan kasus penggunaan umum untuk tabel eksternal OSS terpartisi. Jika SLS menulis data ke OSS menggunakan struktur direktori yang kompatibel, Anda dapat mendefinisikan tabel terpartisi untuk mengkueri data tersebut.

Untuk informasi selengkapnya tentang Log Service (SLS), lihat What is Log Service.

Create an OSS shipping task (old version).
Pada panel OSS LogShipper, saat mengonfigurasi tugas pengiriman, kami menyarankan agar Anda mengatur Partition format menjadi date=%Y%m/userlogin. Contoh struktur direktori OSS yang dihasilkan ditunjukkan di bawah ini:
```
oss://testBucketName/adbpgossfdw
├── date=202002
│ ├── userlogin_158561762910654****_647504382.csv
│ └── userlogin_158561784923220****_647507440.csv
└── date=202003
    └── userlogin_158561794424704****_647508762.csv
```

Buat tabel eksternal OSS terpartisi dengan skema yang sesuai dengan data log dari SLS:

CREATE FOREIGN TABLE userlogin (
        uid integer,
        name character varying,
        source integer,
        logindate timestamp without time zone,
        "date" int
) SERVER oss_serv OPTIONS (
    dir 'adbpgossfdw/',
    format 'text'
)
PARTITION BY LIST ("date")
(
        VALUES ('202002'), 
        VALUES ('202003') 
)

Lihat rencana eksekusi untuk kueri terhadap data log.

Sebagai contoh, untuk menganalisis jumlah login pengguna pada Februari 2020, jalankan pernyataan berikut:

EXPLAIN SELECT uid, count(uid) FROM userlogin WHERE "date" = 202002 GROUP BY uid;

Output berikut dikembalikan:

 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1 (slice2; segments: 3) (cost=5135.10..5145.10 rows=1000 width=12)
   -> HashAggregate (cost=5135.10..5145.10 rows=334 width=12)
         Group Key: userlogin_1_prt_1.uid
         -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=5100.10..5120.10 rows=334 width=12)
               Hash Key: userlogin_1_prt_1.uid
               -> HashAggregate (cost=5100.10..5100.10 rows=334 width=12)
                     Group Key: userlogin_1_prt_1.uid
                     ->t; Append (cost=0.00..100.10 rows=333334 width=4)
                           -> Foreign Scan on userlogin_1_prt_1 (cost=0.00..100.10 rows=333334 width=4)
                                 Filter: (date = 202002)
                                 Oss Url: endpoint=oss-cn-hangzhou-zmf-internal.aliyuncs.com bucket=adbpg-regress dir=adbpgossfdw/date=202002/ filetype=plain|text
                                 Oss Parallel (Max 4) Get: total 0 file(s) with 0 bytes byte(s).
 Optimizer: Postgres query optimizer
(13 rows)

Rencana eksekusi menunjukkan bahwa kueri hanya memindai objek dari direktori date=202002 di OSS. Pemindaian data yang lebih sedikit secara signifikan meningkatkan performa kueri.