Jak ingestovat data pomocí pg_azure_storage ve službě Azure Cosmos DB for PostgreSQL

Článek
08/15/2024

PLATÍ PRO: Azure Cosmos DB for PostgreSQL (využívající rozšíření databáze Citus do PostgreSQL)

Tento článek ukazuje, jak pomocí rozšíření pg_azure_storage PostgreSQL manipulovat s daty a načítat je do služby Azure Cosmos DB for PostgreSQL přímo ze služby Azure Blob Storage (ABS). ABS je škálovatelná, odolná a zabezpečená služba úložiště nativní pro cloud. Díky těmto charakteristikám je vhodné ukládat a přesouvat stávající data do cloudu.

Příprava databáze a úložiště objektů blob

Pokud chcete načíst data ze služby Azure Blob Storage, nainstalujte pg_azure_storage do databáze rozšíření PostgreSQL:

SELECT * FROM create_extension('azure_storage');

Důležité

Rozšíření pg_azure_storage je k dispozici pouze v clusterech Azure Cosmos DB for PostgreSQL se systémem PostgreSQL 13 a novějším.

Připravili jsme veřejnou ukázkovou datovou sadu pro tento článek. Pokud chcete použít vlastní datovou sadu, postupujte podle migrace místních dat do cloudového úložiště a zjistěte, jak efektivně získat datové sady do Azure Blob Storage.

Poznámka:

Když vyberete Kontejner (anonymní přístup pro čtení pro kontejnery a objekty blob), budete moct ingestovat soubory ze služby Azure Blob Storage pomocí jejich veřejných adres URL a vytvořit výčet obsahu kontejneru bez nutnosti konfigurovat klíč účtu v pg_azure_storage. Kontejnery nastavené na úroveň přístupu Private (bez anonymního přístupu) nebo Blob (anonymní přístup jen pro čtení pro objekty blob) budou vyžadovat přístupový klíč.

Výpis obsahu kontejneru

Pro tento postup je předem vytvořený ukázkový účet služby Azure Blob Storage a kontejner. Název kontejneru je githuba je v pgquickstart účtu. Pomocí funkce snadno zjistíme, které soubory jsou v kontejneru azure_storage.blob_list(account, container) .

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

Výstup můžete filtrovat buď pomocí běžné klauzule SQL WHERE , nebo pomocí prefix parametru blob_list UDF. Druhý filtruje vrácené řádky na straně služby Azure Blob Storage.

Poznámka:

Výpis obsahu kontejneru vyžaduje účet a přístupový klíč nebo kontejner s povoleným anonymním přístupem.

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

Načtení dat z ABS

Načtení dat pomocí příkazu COPY

Začněte vytvořením ukázkového schématu.

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

Načítání dat do tabulek se stává stejně jednoduché jako volání COPY příkazu.

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

Všimněte si, jak rozšíření rozpoznalo, že adresy URL zadané pro příkaz kopírování pocházejí ze služby Azure Blob Storage, soubory, na které jsme odkazovali, byly komprimované a které se také automaticky zpracovaly za nás.

Příkaz COPY podporuje více parametrů a formátů. Ve výše uvedeném příkladu se na základě přípon souborů automaticky vybral formát a komprese. Formát ale můžete zadat přímo podobně jako běžný COPY příkaz.

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

V současné době přípona podporuje následující formáty souborů:

format	description
csv	Formát hodnot oddělených čárkami používaný nástrojem PostgreSQL COPY
tsv	Hodnoty oddělené tabulátory, výchozí formát PostgreSQL COPY
binární	Binární formát PostgreSQL COPY
text	Soubor obsahující jednu textovou hodnotu (například velký JSON nebo XML)

Načtení dat pomocí blob_get()

Příkaz COPY je pohodlný, ale omezený v flexibilitě. Funkce COPY interně používá blob_get funkci, kterou můžete použít přímo k manipulaci s daty ve složitějších scénářích.

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

Poznámka:

Ve výše uvedeném dotazu je soubor před použití plně načten LIMIT 3 .

Pomocí této funkce můžete manipulovat s daty za běhu ve složitých dotazech a provádět importy jako 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

Ve výše uvedeném příkazu jsme data vyfiltrovali na účty s aktuálními gravatar_id a velkými písmeny jejich přihlášení za běhu.

Možnosti pro blob_get()

V některých situacích možná budete muset řídit přesně to, co blob_get se pokouší provést pomocí decodercompression parametrů a parametrůoptions.

Dekodér lze nastavit na auto (výchozí) nebo na některou z následujících hodnot:

format	description
csv	Formát hodnot oddělených čárkami používaný nástrojem PostgreSQL COPY
tsv	Hodnoty oddělené tabulátory, výchozí formát PostgreSQL COPY
binární	Binární formát PostgreSQL COPY
text	Soubor obsahující jednu textovou hodnotu (například velký JSON nebo XML)

compression může být buď auto (výchozí), none nebo gzip.

options Nakonec je parametr typu jsonb. Existují čtyři utility funkce, které pomáhají vytvářet hodnoty pro ni. Každá funkce nástroje je určena pro dekodér odpovídající jeho názvu.

dekodér	options – funkce
csv	`options_csv_get`
tsv	`options_tsv`
binární	`options_binary`
text	`options_copy`

Když se podíváte na definice funkcí, uvidíte, které parametry jsou podporovány dekodérem.

options_csv_get – oddělovač, null_string, záhlaví, uvozovky, řídicí force_not_null, force_null, content_encoding options_tsv – oddělovač, null_string, content_encoding options_copy – oddělovač, null_string, záhlaví, uvozovky, řídicí znak, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Když znáte výše uvedené informace, můžeme během analýzy zahodit nahrávky s hodnotou null gravatar_id .

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

Přístup k privátnímu úložišti

Získání názvu účtu a přístupového klíče

Bez přístupového klíče nebudeme moct vypsat kontejnery, které jsou nastavené na úrovně přístupu k privátním objektům nebo objektům 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>')
```
V účtu úložiště otevřete přístupové klíče. Zkopírujte název účtu úložiště a zkopírujte klíč z oddílu key1 (musíte nejprve vybrat Zobrazit vedle klíče).
Přidání účtu do pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Teď můžete vypsat kontejnery nastavené na úrovně přístupu k privátním objektům a objektům blob pro toto úložiště, ale jenom jako citus uživatel, který má přiřazenou azure_storage_admin roli. Pokud vytvoříte nového uživatele s názvem support, ve výchozím nastavení nebude mít povolený přístup k obsahu kontejneru.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
support Povolit uživateli použití konkrétního účtu služby Azure Blob Storage

Udělení oprávnění je stejně jednoduché jako volání account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Ve výstupu account_listuvidíme povolené uživatele, kteří zobrazují všechny účty s definovanými přístupovými klíči.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Pokud se někdy rozhodnete, že uživatel už by neměl mít přístup. Stačí zavolat account_user_remove.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Další kroky

Blahopřejeme, právě jste se dozvěděli, jak načíst data do služby Azure Cosmos DB for PostgreSQL přímo ze služby Azure Blob Storage.

Zjistěte, jak vytvořit řídicí panel v reálném čase pomocí služby Azure Cosmos DB for PostgreSQL.
Přečtěte si další informace o pg_azure_storage.
Přečtěte si o podpoře postgres COPY.

Sdílet prostřednictvím

Jak ingestovat data pomocí pg_azure_storage ve službě Azure Cosmos DB for PostgreSQL

Příprava databáze a úložiště objektů blob

Výpis obsahu kontejneru

Načtení dat z ABS

Načtení dat pomocí příkazu COPY

Načtení dat pomocí blob_get()

Možnosti pro blob_get()

Přístup k privátnímu úložišti

Další kroky

Váš názor

Další materiály