Menggunakan tabel asing OSS yang dipartisi - AnalyticDB

Fitur partisi tabel didukung untuk tabel asing Object Storage Service (OSS). Jika klausa WHERE dalam pernyataan kueri mencakup kolom partisi, efisiensi kueri dapat ditingkatkan dengan memindai lebih sedikit data dari OSS.

Perhatian

Untuk menggunakan fitur partisi tabel asing OSS, pastikan direktori objek berformat sebagai berikut: oss://bucket/partcol1=partval1/partcol2=partval2/. Dalam direktori ini, partcol1 dan partcol2 menunjukkan kolom partisi, sementara partval1 dan partval2 menunjukkan nilai di kolom partisi sebelumnya.

Sebagai contoh, jika sebuah tabel dipartisi berdasarkan kolom tahun dan subpartisi berdasarkan kolom bulan, dengan nilai di kolom year adalah 2022 dan nilai di kolom month adalah 07, partisi terkait disimpan di direktori oss://bucket/year=2022/month=07.

Buat server OSS, pemetaan pengguna ke server OSS, dan tabel asing OSS

Sebelum menggunakan tabel asing OSS, Anda harus membuat server OSS, pemetaan pengguna ke server OSS, dan tabel asing OSS.

Untuk informasi tentang cara membuat server OSS, lihat bagian "Buat Server OSS" dalam topik Gunakan Tabel Asing OSS untuk Analisis Danau Data.
Untuk informasi tentang cara membuat pemetaan pengguna ke server OSS, lihat bagian "Buat Pemetaan Pengguna ke Server OSS" dalam topik Gunakan Tabel Asing OSS untuk Analisis Danau Data.
Untuk informasi tentang cara membuat tabel asing OSS, lihat bagian "Buat Tabel Asing OSS" dalam topik Gunakan Tabel Asing OSS untuk Analisis Danau Data.

Buat tabel yang dipartisi

Anda dapat menjalankan pernyataan CREATE FOREIGN TABLE untuk membuat tabel asing OSS yang dipartisi. Sintaksis yang digunakan untuk membuat tabel asing OSS yang dipartisi sama dengan yang digunakan untuk membuat tabel partisi standar. Untuk informasi lebih lanjut, lihat Tentukan Partisi Tabel.

Untuk informasi lebih lanjut tentang CREATE FOREIGN TABLE, lihat Buat Tabel Asing OSS.

Catatan

Tabel asing OSS hanya mendukung partisi daftar.

Buat tabel yang dipartisi bernama ossfdw_parttable dan tentukan template partisi. Contoh:

CREATE FOREIGN TABLE ossfdw_parttable(            
  key text,
  value bigint,
  pt text,                                        -- Tentukan kunci partisi.
  region text                                     -- Tentukan kunci subpartisi.
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt)                            -- Gunakan bidang pt untuk mempartisi tabel.
SUBPARTITION BY LIST (region)                     -- Gunakan bidang region untuk mensubpartisi tabel.
    SUBPARTITION TEMPLATE (                       -- Tentukan template subpartisi.
       SUBPARTITION hangzhou VALUES ('hangzhou'),
       SUBPARTITION shanghai VALUES ('shanghai')
    )
( PARTITION "20170601" VALUES ('20170601'), 
  PARTITION "20170602" VALUES ('20170602'));

Buat tabel yang dipartisi bernama ossfdw_parttable1 tanpa menentukan template partisi. Contoh:

CREATE FOREIGN TABLE ossfdw_parttable1(            
  key text,
  value bigint,
  pt text,                                        -- Tentukan kunci partisi.
  region text                                     -- Tentukan kunci subpartisi.
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt)                            -- Gunakan bidang pt untuk mempartisi tabel.
SUBPARTITION BY LIST (region)                     -- Gunakan bidang region untuk mensubpartisi tabel.
(
    -- Partisi berikut berisi subpartisi yang berbeda: 
    VALUES('20181218')
    (
        VALUES('hangzhou'),
        VALUES('shanghai') 
    ),
    VALUES('20181219')
    (
        VALUES('nantong'),
        VALUES('anhui') 
    )    
);

Menyesuaikan skema tabel asing OSS yang dipartisi

Anda dapat menjalankan pernyataan ALTER TABLE untuk menyesuaikan skema tabel asing OSS yang dipartisi. AnalyticDB for PostgreSQL memungkinkan Anda membuat atau menghapus partisi dan subpartisi di tabel asing OSS.

Buat partisi dan subpartisi

Buat Partisi
- Buat partisi di tabel ossfdw_parttable. Sistem secara otomatis menghasilkan subpartisi yang sesuai berdasarkan template partisi yang ditentukan saat pembuatan tabel. Contoh:
```
ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');
```
  Gambar berikut menunjukkan perubahan pada skema tabel.
- Buat partisi di tabel ossfdw_parttable1. Anda harus menentukan subpartisi di partisi karena tidak ada template partisi yang ditentukan saat pembuatan tabel. Contoh:
```
ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
(
    VALUES('hefei'),
    VALUES('guangzhou') 
);
```
Buat Subpartisi
Buat subpartisi di partisi 20170603 dari tabel ossfdw_parttable. Contoh:
```
ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');
```
Gambar berikut menunjukkan perubahan pada skema tabel.

Hapus partisi dan subpartisi

Hapus Partisi. Contoh:

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

Hapus Subpartisi. Contoh:

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

Hapus tabel yang dipartisi

Anda dapat menjalankan pernyataan DROP FOREIGN TABLE untuk menghapus tabel yang dipartisi.

Hapus tabel yang dipartisi. Contoh:

DROP FOREIGN TABLE ossfdw_parttable;

Gunakan tabel asing OSS untuk mengakses data log yang dikirim oleh Layanan Log

Tabel asing OSS yang dipartisi dapat digunakan saat mengakses data log yang dikirim oleh Simple Log Service. Jika Anda menggunakan jalur objek yang sesuai saat menulis data dari Simple Log Service ke OSS, Anda dapat membuat tabel asing OSS yang dipartisi.

Untuk informasi lebih lanjut tentang Simple Log Service, lihat Apa itu Simple Log Service?

Kirim Data Log dari Simple Log Service ke OSS.

Di panel OSS LogShipper, kami sarankan Anda menyetel Shard Format ke date=%Y%m/userlogin. Contoh jalur objek OSS yang dihasilkan adalah dalam format berikut:

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

Buat Tabel Asing OSS yang Dipartisi Berdasarkan Bidang Kunci Data Log yang Dikirim oleh Simple Log Service. Contoh:

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

Kueri dan Analisis Rencana Eksekusi Pernyataan SELECT pada Data Log yang Dikirim oleh Simple Log Service.

Sebagai contoh, anggaplah Anda ingin menanyakan jumlah total login pengguna pada Februari 2022.

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

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

Informasi yang dikembalikan menunjukkan bahwa hanya data di direktori date=202002 yang perlu dipindai. Semakin sedikit data yang dipindai, semakin tinggi efisiensi kueri.