Arsipkan data database ke Object Storage Service untuk kueri SQL - oss_fdw - PolarDB

Topik ini menjelaskan gambaran umum, batasan, dan penggunaan ekstensi oss_fdw.

Gambaran Umum

oss_fdw adalah ekstensi pembungkus data asing (FDW) untuk PolarDB for Oracle. Ekstensi ini menghubungkan data dalam Object Storage Service (OSS) dengan definisi tabel asing di kluster PolarDB, memungkinkan Anda menyimpan data ke OSS sambil tetap dapat membaca dan menulis menggunakan pernyataan SQL standar.

OSS adalah layanan penyimpanan cloud yang aman, hemat biaya, dan sangat andal, dirancang untuk memberikan ketersediaan data sebesar 99,995%. Data historis, data arsip hanya-baca, dan data dingin sangat ideal disimpan di OSS guna menghemat biaya penyimpanan.

Prasyarat

OSS telah diaktifkan dan bucket telah dibuat. Untuk informasi lebih lanjut, lihat Apa itu OSS?.

Batasan

Tabel asing oss_fdw hanya mendukung pernyataan SELECT, INSERT, dan TRUNCATE, tetapi tidak mendukung pernyataan UPDATE atau DELETE. Oleh karena itu, ekstensi ini hanya digunakan untuk mengarsipkan data: Setelah data ditulis ke OSS, data tersebut hanya dapat dibaca, tetapi tidak dapat diperbarui.

Penggunaan

Instal ekstensi

CREATE EXTENSION oss_fdw;

Buat server data asing

Konfigurasikan informasi koneksi OSS dan buat pemetaan antara PolarDB dan Bucket OSS.

Contoh:

CREATE SERVER ossserver
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
    host 'oss-cn-xxx.aliyuncs.com',
    bucket 'mybucket',
    id 'xxx',
    key 'xxx'
);

Catatan

Parameter dalam pernyataan:

host: Titik akhir OSS.
bucket: Nama Bucket OSS.
id/key: ID AccessKey dan Rahasia AccessKey akun Alibaba Cloud Anda.

Petakan tabel asing ke direktori OSS

Buat tabel asing OSS di PolarDB dan petakan ke direktori untuk sumber data asing seperti yang ditentukan dalam Buat server data asing.
```
CREATE FOREIGN TABLE t1_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive/');
```

Impor data ke tabel asing OSS.

INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');

Data yang dimasukkan ke dalam tabel ditulis ke file di direktori archive/. Anda dapat menanyakan tabel asing menggunakan metode berikut:

EXPLAIN SELECT COUNT(*) FROM t1_oss;
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate  (cost=6.54..6.54 rows=1 width=8)
   ->  Foreign Scan on t1_oss  (cost=0.00..6.40 rows=54 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 1
         Total size of OSS file: 1292 bytes
(5 rows)

SELECT COUNT(*) FROM t1_oss;
 count
-------
   100
(1 row)

Saat pernyataan INSERT dieksekusi pada tabel lagi, data ditulis ke file baru di direktori OSS.

INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');
INSERT 0 100

EXPLAIN SELECT COUNT(*) FROM t1_oss;
                            QUERY PLAN
-------------------------------------------------------------------
 Aggregate  (cost=12.07..12.08 rows=1 width=8)
   ->  Foreign Scan on t1_oss  (cost=0.00..11.80 rows=108 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 2
         Total size of OSS file: 2584 bytes
(5 rows)

SELECT COUNT(*) FROM t1_oss;
 count
-------
   200
(1 row)

Eksekusi pernyataan TRUNCATE untuk menghapus semua file pemetaan OSS untuk tabel asing.

TRUNCATE t1_oss;
TRUNCATE TABLE

SELECT COUNT(*) FROM t1_oss;
WARNING:  does not match any file in oss
 count
-------
     0
(1 row)

Petakan tabel asing ke awalan direktori

Buat tabel asing menggunakan opsi prefix.

CREATE FOREIGN TABLE t2_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (prefix 'prefix/file_');

Beberapa file dengan nama awalan yang sama dibuat saat Anda mengeksekusi pernyataan INSERT pada tabel asing.

INSERT INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
INSERT 0 100

INSERT INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
INSERT 0 100

EXPLAIN SELECT COUNT(*) FROM t2_oss;
                            QUERY PLAN
-------------------------------------------------------------------
 Aggregate  (cost=12.07..12.08 rows=1 width=8)
   ->  Foreign Scan on t2_oss  (cost=0.00..11.80 rows=108 width=0)
         Directory on OSS: prefix/file_
         Number Of OSS file: 2
         Total size of OSS file: 2584 bytes
(5 rows)

SELECT COUNT(*) FROM t2_oss;
 count
-------
   200
(1 row)

Format penyimpanan file OSS

oss_fdw memungkinkan Anda menetapkan format data yang disimpan di OSS. Nilai defaultnya adalah CSV. Anda juga dapat secara eksplisit menentukan format. Saat Anda mengeksekusi pernyataan INSERT pada tabel asing OSS, data ditulis dalam format CSV ke file OSS.

CREATE FOREIGN TABLE t3_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive_csv/', format 'csv');

Lihat file untuk tabel asing OSS

Buat tabel asing OSS dan eksekusi pernyataan INSERT tiga kali untuk menulis data ke tiga file OSS.

CREATE FOREIGN TABLE t4_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive_file_list/');

INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');

Lihat file untuk tabel asing OSS menggunakan fungsi berikut dan tentukan nama tabel dan nama skema tabel asing OSS (nilai defaultnya adalah public):

SELECT * FROM oss_fdw_list_file('t4_oss');
                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

SELECT * FROM oss_fdw_list_file('t4_oss', 'public');
                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

Kompresi OSS

Parameter compressiontype menentukan algoritma kompresi untuk menulis data ke file OSS. Parameter ini kosong secara default, yang menunjukkan bahwa data tidak dikompresi. Nilai valid: gzip dan zstd.

Parameter compressionlevel menentukan tingkat kompresi. Tingkat kompresi yang lebih tinggi menunjukkan bahwa lebih banyak CPU digunakan selama kompresi dan dekompresi, serta volume data yang lebih kecil ditransfer melalui jaringan dan ruang OSS yang lebih sedikit digunakan oleh tabel asing.

Algoritma kompresi Gzip

Nilai valid tingkat kompresi untuk algoritma kompresi Gzip: 1 hingga 9. Nilai default: 6.

CREATE FOREIGN TABLE t5_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir 'archive_file_compression/',
    compressiontype 'gzip',
    compressionlevel '9'
);

INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');

Saat Anda melihat file tabel asing OSS, ukuran file yang dikompresi dengan Gzip jauh lebih kecil daripada file yang tidak dikompresi.

SELECT * FROM oss_fdw_list_file('t4_oss');
                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

SELECT * FROM oss_fdw_list_file('t5_oss');
                        name                         | size
-----------------------------------------------------+-------
 archive_file_compression/_t5_oss_741147752563794.gz | 23654
 archive_file_compression/_t5_oss_741147752633713.gz | 23654
 archive_file_compression/_t5_oss_741147752828680.gz | 23654
(3 rows)

Algoritma kompresi Zstandard

Catatan

Algoritma kompresi Zstandard hanya didukung pada kluster yang menjalankan PostgreSQL 14 (versi revisi 14.9.13.0 atau lebih baru).

Nilai valid tingkat kompresi untuk algoritma kompresi Zstandard: -7 hingga 22. Nilai default: 6.

CREATE FOREIGN TABLE t6_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir 'archive_file_zstd/',
    compressiontype 'zstd',
    compressionlevel '9'
);

INSERT INTO t6_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t6_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t6_oss VALUES (generate_series(1,10000), 0.1, 'hello');

Saat Anda melihat file tabel asing OSS, ukuran file yang dikompresi dengan Zstandard jauh lebih kecil daripada file yang tidak dikompresi.

SELECT * FROM oss_fdw_list_file('t4_oss');
                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

SELECT * FROM oss_fdw_list_file('t6_oss');
                     name                      | size
-----------------------------------------------+------
 archive_file_zstd/_t6_oss_748106174612293.zst | 6710
 archive_file_zstd/_t6_oss_748106174700206.zst | 6710
 archive_file_zstd/_t6_oss_748106174866829.zst | 6710
(3 rows)

Hapus ekstensi

DROP EXTENSION oss_fdw;