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

Článek
06/23/2023

PLATÍ PRO: Azure Cosmos DB for PostgreSQL (využívá rozšíření databáze Citus pro 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 z Azure Blob Storage (ABS). ABS je škálovatelná, odolná a zabezpečená služba úložiště nativní pro cloud. Díky těmto vlastnostem je vhodný pro ukládání a přesouvání stávajících dat do cloudu.

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

Pokud chcete načíst data z 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.

Pro tento článek jsme připravili veřejnou ukázkovou datovou sadu. Pokud chcete použít vlastní datovou sadu, postupujte podle pokynů v tématu Migrace místních dat do cloudového úložiště, kde se dozvíte, jak datové sady efektivně dostat do Azure Blob Storage.

Poznámka

Výběrem možnosti Kontejner (anonymní přístup pro čtení pro kontejnery a objekty blob) umožníte ingestovat soubory z Azure Blob Storage pomocí jejich veřejných adres URL a vytvořit výčet obsahu kontejneru, aniž byste museli konfigurovat klíč účtu v pg_azure_storage. Kontejnery nastavené na úroveň přístupu Privátní (bez anonymního přístupu) nebo Objekt blob (anonymní přístup pro čtení pouze pro objekty blob) budou vyžadovat přístupový klíč.

Vypsat obsah kontejneru

Pro tento postup je k dispozici ukázka, Azure Blob Storage účet a kontejner předem vytvořené. 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í parametru prefixblob_list UDF. Druhý filtr filtruje vrácené řádky na straně 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 je 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, že rozšíření rozpoznalo, že adresy URL poskytnuté příkazu pro kopírování pocházejí z Azure Blob Storage, soubory, na které jsme odkazovali, byly zkomprimované gzip a byly také automaticky zpracovány za nás.

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

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

V současné době rozšíření podporuje následující formáty souborů:

formát	description
csv	Formát hodnot oddělených čárkami, který používá 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 má omezenou flexibilitu. 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 se soubor před tím, než LIMIT 3 se použije, plně načte.

Pomocí této funkce můžete průběžně manipulovat s daty 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 vyfiltrovali data na účty s přítomným účtem gravatar_id a jejich přihlášení jsme za běhu vyfiltrovali velkými písmeny.

Možnosti pro blob_get()

V některých situacích může být potřeba pomocí parametrů , a options přesně určit, compression co blob_get se má provéstdecoder.

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

formát	description
csv	Formát hodnot oddělených čárkami, který používá 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. K dispozici jsou čtyři funkce nástroje, které pro ni pomáhají vytvářet hodnoty. 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í, můžete zjistit, které parametry jsou podporované kterým dekodérem.

options_csv_get - oddělovač, null_string, header, quote, escape, force_not_null, force_null, content_encoding options_tsv – oddělovač, null_string, content_encoding options_copy – oddělovač, null_string, záhlaví, uvozovky, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

S vědomím výše uvedeného 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ých klíčů

Bez přístupového klíče nebudeme moct vypisovat kontejnery, které jsou nastavené na úroveň přístupu Privátní nebo 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 část Klíč z klíče1 (napřed musíte 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 úroveň privátního přístupu a přístupu k objektům blob pro dané úložiště, ale jenom jako citus uživatele, který má přidělenou azure_storage_admin roli. Pokud vytvoříte nového uživatele s názvem support, nebude mít ve výchozím nastavení 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žívat konkrétní účet Azure Blob Storage

Udělení oprávnění je jednoduché jako volání account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Povolené uživatele vidíme ve výstupu account_listpříkazu , který zobrazuje 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 byste se někdy rozhodli, že by uživatel už 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 naučili načítat data do služby Azure Cosmos DB for PostgreSQL přímo z Azure Blob Storage.

Zjistěte, jak vytvořit řídicí panel v reálném čase se službou 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.

Share via

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

Vypsat obsah 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

Další materiály