AnalyticDB:Gunakan tabel eksternal OSS terpartisi

Prasyarat

Sebelum memulai, pastikan Anda telah:

• Membuat server OSS di AnalyticDB for PostgreSQL. Lihat bagian Buat server OSS dalam *Gunakan tabel eksternal OSS untuk analisis data lake*

• Membuat pemetaan pengguna ke server OSS. Lihat bagian Buat pemetaan pengguna ke server OSS dalam *Gunakan tabel eksternal OSS untuk analisis data lake*

Cara kerja pemangkasan partisi

Pemangkasan partisi bergantung pada struktur direktori OSS berformat key=value. Mesin memetakan setiap filter kolom partisi ke jalur direktori dan hanya membaca objek di bawah jalur tersebut.

Format jalur objek OSS yang diperlukan adalah:

oss://bucket/partcol1=partval1/partcol2=partval2/

Sebagai contoh, tabel yang dipartisi berdasarkan year lalu month mengharapkan tata letak berikut:

oss://my-bucket/
├── year=2022/
│   ├── month=07/
│   │   ├── data1.csv
│   │   └── data2.csv
│   └── month=08/
│       └── data3.csv
└── year=2023/
    └── month=01/
        └── data4.csv

Kueri dengan WHERE year = 2022 AND month = 07 hanya membaca objek di bawah year=2022/month=07/ dan melewati semua direktori lainnya.

Buat tabel eksternal OSS terpartisi

Gunakan CREATE FOREIGN TABLE dengan PARTITION BY LIST untuk mendefinisikan tabel eksternal OSS terpartisi. Sintaksnya mengikuti konvensi yang sama seperti tabel partisi standar di AnalyticDB for PostgreSQL. Untuk sintaks lengkap CREATE FOREIGN TABLE, lihat Buat tabel eksternal OSS.

Pilih salah satu dari dua pola berikut tergantung apakah semua partisi memiliki struktur subpartisi yang sama.

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)
SUBPARTITION BY LIST (region)
    SUBPARTITION TEMPLATE (
       SUBPARTITION hangzhou VALUES ('hangzhou'),
       SUBPARTITION shanghai VALUES ('shanghai')
    )
( PARTITION "20170601" VALUES ('20170601'),
  PARTITION "20170602" VALUES ('20170602'));

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)
SUBPARTITION BY LIST (region)
(
    VALUES('20181218')
    (
        VALUES('hangzhou'),
        VALUES('shanghai')
    ),
    VALUES('20181219')
    (
        VALUES('nantong'),
        VALUES('anhui')
    )
);

Kelola partisi dan subpartisi

Gunakan ALTER TABLE untuk menambah atau menghapus partisi dan subpartisi setelah tabel dibuat.

Tambahkan partisi dan subpartisi

Tambahkan partisi ke tabel dengan templat subpartisi

Sistem menghasilkan subpartisi secara otomatis berdasarkan templat.

ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');

Gambar berikut menunjukkan struktur tabel yang telah diperbarui.

Tambahkan partisi ke tabel tanpa templat subpartisi

Tentukan subpartisi secara eksplisit karena tidak ada templat yang didefinisikan.

ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
(
    VALUES('hefei'),
    VALUES('guangzhou')
);

Tambahkan subpartisi ke partisi yang sudah ada

ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');

Gambar berikut menunjukkan struktur tabel yang telah diperbarui.

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

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

Hapus tabel eksternal OSS terpartisi

DROP FOREIGN TABLE ossfdw_parttable;

Kasus penggunaan: kueri data log yang dikirim dari Simple Log Service

Tabel eksternal OSS terpartisi sangat cocok untuk mengkueri data log yang dikirim oleh Simple Log Service ke OSS. Konfigurasikan OSS LogShipper untuk menulis objek dalam format direktori key=value agar AnalyticDB for PostgreSQL dapat melakukan pemangkasan partisi saat kueri dijalankan.

1. Kirim data log dari Simple Log Service ke OSS. Di panel OSS LogShipper, kami merekomendasikan Anda mengatur Shard Format menjadi date=%Y%m/userlogin. Hal ini menghasilkan struktur direktori OSS berikut:

   oss://testBucketName/adbpgossfdw
   ├── date=202002
   │   ├── userlogin_158561762910654****_647504382.csv
   │   └── userlogin_158561784923220****_647507440.csv
   └── date=202003
       └── userlogin_158561794424704****_647508762.csv

2. Buat tabel eksternal OSS terpartisi yang dipetakan ke kolom partisi date.

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

3. Lakukan kueri dan analisis rencana eksekusi untuk memverifikasi bahwa pemangkasan partisi berjalan. Sebagai contoh, asumsikan Anda ingin mengkueri jumlah total login pengguna pada Februari 2022.

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

   Output berikut mengonfirmasi bahwa pemangkasan aktif. Node Foreign Scan on userlogin_1_prt_1 dan baris Oss Url menunjukkan bahwa hanya direktori date=202002/ yang dipindai:

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

   Nilai dir=adbpgossfdw/date=202002/ mengonfirmasi bahwa hanya direktori date=202002 yang dipindai. Direktori date=202003 dilewati.

Langkah berikutnya

• Gunakan tabel eksternal OSS untuk analisis data lake

• Definisikan partisi tabel

• Apa itu Simple Log Service?