Cara menyerap data menggunakan pg_azure_storage di Azure Cosmos DB for PostgreSQL

Artikel
08/15/2024

BERLAKU UNTUK: Azure Cosmos DB for PostgreSQL (didukung oleh ekstensi database Citus ke PostgreSQL)

Artikel ini memperlihatkan cara menggunakan ekstensi pg_azure_storage PostgreSQL untuk memanipulasi dan memuat data ke Azure Cosmos DB for PostgreSQL langsung dari Azure Blob Storage (ABS). ABS adalah layanan penyimpanan cloud-native yang dapat diskalakan, tahan lama, dan aman. Karakteristik ini menjadikannya pilihan yang baik untuk menyimpan dan memindahkan data yang ada ke cloud.

Menyiapkan database dan penyimpanan blob

Untuk memuat data dari Azure Blob Storage, instal pg_azure_storage ekstensi PostgreSQL di database Anda:

SELECT * FROM create_extension('azure_storage');

Penting

Ekstensi pg_azure_storage hanya tersedia di kluster Azure Cosmos DB for PostgreSQL yang menjalankan PostgreSQL 13 ke atas.

Kami telah menyiapkan himpunan data demonstrasi publik untuk artikel ini. Untuk menggunakan himpunan data Anda sendiri, ikuti migrasi data lokal Anda ke penyimpanan cloud untuk mempelajari cara memasukkan himpunan data Anda secara efisien ke Azure Blob Storage.

Catatan

Memilih "Kontainer (akses baca anonim untuk kontainer dan blob)" akan memungkinkan Anda menyerap file dari Azure Blob Storage menggunakan URL publik mereka dan menghitung konten kontainer tanpa perlu mengonfigurasi kunci akun di pg_azure_storage. Kontainer yang diatur ke tingkat akses "Privat (tanpa akses anonim)" atau "Blob (akses baca anonim hanya untuk blob)" akan memerlukan kunci akses.

Cantumkan konten kontainer

Ada demonstrasi akun Azure Blob Storage dan kontainer yang telah dibuat sebelumnya untuk panduan ini. Nama kontainer adalah github, dan ada di pgquickstart akun . Kita dapat dengan mudah melihat file mana yang ada dalam kontainer dengan menggunakan azure_storage.blob_list(account, container) fungsi .

SELECT path, bytes, pg_size_pretty(bytes), content_type
  FROM azure_storage.blob_list('pgquickstart','github');

-[ RECORD 1 ]--+-------------------
path           | events.csv.gz
bytes          | 41691786
pg_size_pretty | 40 MB
content_type   | application/x-gzip
-[ RECORD 2 ]--+-------------------
path           | users.csv.gz
bytes          | 5382831
pg_size_pretty | 5257 kB
content_type   | application/x-gzip

Anda dapat memfilter output baik dengan menggunakan klausa SQL WHERE reguler, atau dengan menggunakan prefix parameter blob_list UDF. Yang terakhir memfilter baris yang dikembalikan di sisi Azure Blob Storage.

Catatan

Mencantumkan konten kontainer memerlukan akun dan kunci akses atau kontainer dengan akses anonim yang diaktifkan.

SELECT * FROM azure_storage.blob_list('pgquickstart','github','e');

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

SELECT *
  FROM azure_storage.blob_list('pgquickstart','github')
 WHERE path LIKE 'e%';

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

Memuat data dari ABS

Memuat data dengan perintah COPY

Mulailah dengan membuat skema sampel.

CREATE TABLE github_users
(
	user_id bigint,
	url text,
	login text,
	avatar_url text,
	gravatar_id text,
	display_login text
);

CREATE TABLE github_events
(
	event_id bigint,
	event_type text,
	event_public boolean,
	repo_id bigint,
	payload jsonb,
	repo jsonb,
	user_id bigint,
	org jsonb,
	created_at timestamp
);

CREATE INDEX event_type_index ON github_events (event_type);
CREATE INDEX payload_index ON github_events USING GIN (payload jsonb_path_ops);

SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');

Memuat data ke dalam tabel menjadi semahal memanggil COPY perintah.

-- download users and store in table

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz';

-- download events and store in table

COPY github_events
FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';

Perhatikan bagaimana ekstensi mengenali bahwa URL yang disediakan untuk perintah salin berasal dari Azure Blob Storage, file yang kami arahkan dikompresi gzip dan yang juga ditangani secara otomatis untuk kami.

Perintah ini COPY mendukung lebih banyak parameter dan format. Dalam contoh di atas, format dan pemadatan dipilih secara otomatis berdasarkan ekstensi file. Namun, Anda dapat memberikan format yang langsung mirip dengan perintah reguler COPY .

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');

Saat ini ekstensi mendukung format file berikut:

format	description
CSV	Format nilai yang dipisahkan koma yang digunakan oleh PostgreSQL COPY
tsv	Nilai yang dipisahkan tab, format SALIN PostgreSQL default
biner	Format SALINAN PostgreSQL Biner
text	File yang berisi nilai teks tunggal (misalnya, JSON atau XML besar)

Memuat data dengan blob_get()

Perintah COPY ini nyaman, tetapi terbatas pada fleksibilitas. COPY secara internal menggunakan blob_get fungsi , yang dapat Anda gunakan secara langsung untuk memanipulasi data dalam skenario yang lebih kompleks.

SELECT *
  FROM azure_storage.blob_get(
         'pgquickstart', 'github',
         'users.csv.gz', NULL::github_users
       )
 LIMIT 3;

-[ RECORD 1 ]-+--------------------------------------------
user_id       | 21
url           | https://api.github.com/users/technoweenie
login         | technoweenie
avatar_url    | https://avatars.githubusercontent.com/u/21?
gravatar_id   |
display_login | technoweenie
-[ RECORD 2 ]-+--------------------------------------------
user_id       | 22
url           | https://api.github.com/users/macournoyer
login         | macournoyer
avatar_url    | https://avatars.githubusercontent.com/u/22?
gravatar_id   |
display_login | macournoyer
-[ RECORD 3 ]-+--------------------------------------------
user_id       | 38
url           | https://api.github.com/users/atmos
login         | atmos
avatar_url    | https://avatars.githubusercontent.com/u/38?
gravatar_id   |
display_login | atmos

Catatan

Dalam kueri di atas, file sepenuhnya diambil sebelum LIMIT 3 diterapkan.

Dengan fungsi ini, Anda dapat memanipulasi data dengan cepat dalam kueri kompleks, dan melakukan impor sebagai INSERT FROM SELECT.

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users)
      WHERE gravatar_id IS NOT NULL;

INSERT 0 264308

Dalam perintah di atas, kami memfilter data ke akun dengan gravatar_id saat ini dan huruf besar mencatat login mereka dengan cepat.

Opsi untuk blob_get()

Dalam beberapa situasi, Anda mungkin perlu mengontrol dengan tepat upaya apa yang blob_get harus dilakukan dengan menggunakan decoderparameter , dan compression options .

Decoder dapat diatur ke auto (default) atau salah satu nilai berikut:

format	description
CSV	Format nilai yang dipisahkan koma yang digunakan oleh PostgreSQL COPY
tsv	Nilai yang dipisahkan tab, format SALIN PostgreSQL default
biner	Format SALINAN PostgreSQL Biner
text	File yang berisi nilai teks tunggal (misalnya, JSON atau XML besar)

compression dapat berupa auto (default), none atau gzip.

Terakhir, options parameter berjenis jsonb. Ada empat fungsi utilitas yang membantu membangun nilai untuk itu. Setiap fungsi utilitas ditunjuk untuk dekoder yang cocok dengan namanya.

Decoder	fungsi opsi
CSV	`options_csv_get`
tsv	`options_tsv`
biner	`options_binary`
text	`options_copy`

Dengan melihat definisi fungsi, Anda dapat melihat parameter mana yang didukung oleh dekoder mana.

options_csv_get - pemisah, null_string, header, kutipan, escape, force_not_null, force_null, content_encoding options_tsv - pemisah, null_string, content_encoding options_copy - pemisah, null_string, header, kutipan, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Mengetahui hal di atas, kita dapat membuang rekaman dengan null gravatar_id selama penguraian.

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users,
                                    options := azure_storage.options_csv_get(force_not_null := ARRAY['gravatar_id']));

INSERT 0 264308

Mengakses penyimpanan privat

Mendapatkan nama akun dan kunci akses Anda

Tanpa kunci akses, kami tidak akan diizinkan untuk mencantumkan kontainer yang diatur ke tingkat akses Privat atau Blob.
```
SELECT * FROM azure_storage.blob_list('mystorageaccount','privdatasets');
```
```
ERROR:  azure_storage: missing account access key
HINT:  Use SELECT azure_storage.account_add('<account name>', '<access key>')
```
Di akun penyimpanan Anda, buka Kunci akses. Salin nama akun Penyimpanan dan salin bagian Kunci dari key1 (Anda harus memilih Tampilkan di samping kunci terlebih dahulu).
Menambahkan akun ke pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Sekarang Anda dapat mencantumkan kontainer yang diatur ke tingkat akses Privat dan Blob untuk penyimpanan tersebut citus tetapi hanya sebagai pengguna, yang memiliki azure_storage_admin peran yang diberikan kepadanya. Jika Anda membuat pengguna baru bernama support, pengguna tersebut tidak akan diizinkan untuk mengakses konten kontainer secara default.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
support Mengizinkan pengguna menggunakan akun Azure Blob Storage tertentu

Memberikan izin sama sederhananya dengan account_user_addmemanggil .
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Kita dapat melihat pengguna yang diizinkan dalam output account_list, yang menunjukkan semua akun dengan kunci akses yang ditentukan.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Jika Anda pernah memutuskan, bahwa pengguna seharusnya tidak lagi memiliki akses. Cukup hubungi account_user_remove.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Langkah berikutnya

Selamat, Anda baru saja mempelajari cara memuat data ke Azure Cosmos DB for PostgreSQL langsung dari Azure Blob Storage.

Pelajari cara membuat dasbor real-time dengan Azure Cosmos DB for PostgreSQL.
Pelajari selengkapnya tentang pg_azure_storage.
Pelajari tentang dukungan Postgres COPY.

Bagikan melalui

Cara menyerap data menggunakan pg_azure_storage di Azure Cosmos DB for PostgreSQL

Menyiapkan database dan penyimpanan blob

Cantumkan konten kontainer

Memuat data dari ABS

Memuat data dengan perintah COPY

Memuat data dengan blob_get()

Opsi untuk blob_get()

Mengakses penyimpanan privat

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan: