Adatok betöltése pg_azure_storage használatával az Azure Cosmos DB for PostgreSQL-ben

Cikk
05/30/2024

A KÖVETKEZŐKRE VONATKOZIK: Azure Cosmos DB for PostgreSQL (a Citus adatbázisbővítménye a PostgreSQL-re)

Ez a cikk bemutatja, hogyan használhatja a pg_azure_storage PostgreSQL-bővítményt az adatok manipulálására és betöltésére az Azure Cosmos DB for PostgreSQL-be közvetlenül az Azure Blob Storage-ból (ABS). Az ABS egy natív felhőbeli skálázható, tartós és biztonságos tárolási szolgáltatás. Ezek a jellemzők jó választássá teszik a meglévő adatok felhőbe való tárolását és áthelyezését.

Adatbázis és blobtároló előkészítése

Az Azure Blob Storage-ból való adatok betöltéséhez telepítse a pg_azure_storage PostgreSQL-bővítményt az adatbázisba:

SELECT * FROM create_extension('azure_storage');

Fontos

A pg_azure_storage bővítmény csak a PostgreSQL 13-at vagy újabb verziót futtató Azure Cosmos DB for PostgreSQL-fürtökön érhető el.

Készítettünk egy nyilvános bemutató adatkészletet ehhez a cikkhez. A saját adatkészlet használatához kövesse a helyszíni adatok felhőbeli tárolóba való migrálását, és ismerje meg, hogyan szerezheti be hatékonyan az adathalmazokat az Azure Blob Storage-ba.

Feljegyzés

A "Tároló (tárolók és blobok névtelen olvasási hozzáférése)" lehetőséget választva a nyilvános URL-címek használatával betölthet fájlokat az Azure Blob Storage-ból, és anélkül sorolhatja fel a tároló tartalmát, hogy konfigurálnia kellene egy fiókkulcsot a pg_azure_storage. A "Privát (névtelen hozzáférés nélkül)" vagy a "Blob (csak blobok névtelen olvasási hozzáférése)" hozzáférési szintű tárolókhoz hozzáférési kulcs szükséges.

Tároló tartalmának listázása

Ehhez az útmutatóhoz előre létrehozott bemutató Azure Blob Storage-fiók és tároló áll rendelkezésre. A tároló neve githuba fiókban pgquickstart van. A függvény használatával könnyen láthatjuk, hogy mely fájlok találhatók a azure_storage.blob_list(account, container) tárolóban.

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

A kimenetet egy normál SQL-záradékkal WHERE vagy az prefixblob_list UDF paraméterével szűrheti. Ez utóbbi szűri a visszaadott sorokat az Azure Blob Storage oldalán.

Feljegyzés

A tároló tartalmának listázásához fiókra és hozzáférési kulcsra vagy engedélyezett névtelen hozzáféréssel rendelkező tárolóra van szükség.

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

Adatok betöltése ABS-ből

Adatok betöltése a COPY paranccsal

Először hozzon létre egy mintaséma.

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

Az adatok táblákba való betöltése olyan egyszerű lesz, mint a parancs meghívása COPY .

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

Figyelje meg, hogy a bővítmény felismerte, hogy a másolási parancshoz megadott URL-címek az Azure Blob Storage-ból származnak, az általunk mutatott fájlok gzip tömörítve voltak, és azokat is automatikusan kezeltük.

A COPY parancs további paramétereket és formátumokat támogat. A fenti példában a formátum és a tömörítés automatikusan ki lett választva a fájlkiterjesztések alapján. A formátumot azonban közvetlenül a normál COPY parancshoz hasonlóan is megadhatja.

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

A bővítmény jelenleg a következő fájlformátumokat támogatja:

format	leírás
csv	A PostgreSQL COPY által használt vesszővel tagolt értékek formátuma
tsv	Tabulátorral tagolt értékek, az alapértelmezett PostgreSQL COPY formátum
bináris	Bináris PostgreSQL COPY formátum
text	Egyetlen szöveges értéket (például nagy JSON-t vagy XML-t) tartalmazó fájl

Adatok betöltése blob_get()

A COPY parancs kényelmes, de rugalmas. A belső MÁSOLÁS függvényt blob_get használja, amellyel közvetlenül kezelheti az adatokat összetettebb helyzetekben.

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

Feljegyzés

A fenti lekérdezésben a fájl teljes lekérése az alkalmazás előtt LIMIT 3 történik.

Ezzel a függvénnyel menet közben módosíthatja az adatokat összetett lekérdezésekben, és importálhatja a következőt: 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

A fenti parancsban szűrtük az adatokat a gravatar_id jelenlegi és nagybetűs bejelentkezésekkel rendelkező fiókokra.

A blob_get() beállításai

Bizonyos helyzetekben előfordulhat, hogy pontosan szabályoznia kell, hogy milyen blob_get kísérleteket kíséreljen meg végrehajtani a decoder, compression és options a paraméterek használatával.

A dekóder beállítása auto (alapértelmezett) vagy az alábbi értékek bármelyike lehet:

format	leírás
csv	A PostgreSQL COPY által használt vesszővel tagolt értékek formátuma
tsv	Tabulátorral tagolt értékek, az alapértelmezett PostgreSQL COPY formátum
bináris	Bináris PostgreSQL COPY formátum
text	Egyetlen szöveges értéket (például nagy JSON-t vagy XML-t) tartalmazó fájl

compression lehet auto (alapértelmezett) none vagy gzip.

Végül a options paraméter típusa jsonb. Négy segédprogramfüggvény segíti az értékek összeállítását. Az egyes segédprogramfüggvények a dekóder nevével egyezőként lesznek kijelölve.

Dekóder	options függvény
csv	`options_csv_get`
tsv	`options_tsv`
bináris	`options_binary`
text	`options_copy`

A függvénydefiníciók megtekintésével láthatja, hogy mely paramétereket támogatja a dekóder.

options_csv_get - elválasztó, null_string, fejléc, idézet, escape, force_not_null, force_null, content_encoding options_tsv - elválasztó, null_string, content_encoding options_copy - elválasztó, null_string, fejléc, idézet, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

A fentiek ismeretében a null értékű gravatar_id felvételeket az elemzés során elvethetjük.

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

Privát tároló elérése

A fiók nevének és hozzáférési kulcsának beszerzése

Hozzáférési kulcs nélkül nem tudjuk felsorolni a privát vagy blob hozzáférési szintekre beállított tárolókat.
```
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>')
```
A tárfiókban nyissa meg az Access-kulcsokat. Másolja ki a Tárfiók nevét, és másolja a kulcsot az 1. kulcs szakaszból (először a Megjelenítés gombot kell választania a kulcs mellett).
Fiók hozzáadása pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Most már listázhatja a tárolók privát és blob hozzáférési szintjeire beállított tárolókat, de csak felhasználóként citus , amely rendelkezik a azure_storage_admin szerepkörével. Ha új felhasználót hoz létre, supportalapértelmezés szerint nem férhet hozzá a tároló tartalmához.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
Adott Azure Blob Storage-fiók használatának engedélyezése a support felhasználó számára

Az engedély megadása olyan egyszerű, mint a hívás account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Láthatjuk az engedélyezett felhasználókat a kimenetben account_list, amely az összes definiált hozzáférési kulcsú fiókot megjeleníti.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Ha bármikor úgy dönt, hogy a felhasználónak többé nem kell hozzáféréssel rendelkeznie. Csak hívja .account_user_remove
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Következő lépések

Gratulálunk, most megtanulta, hogyan tölthet be adatokat az Azure Cosmos DB for PostgreSQL-be közvetlenül az Azure Blob Storage-ból.

Megtudhatja, hogyan hozhat létre valós idejű irányítópultot az Azure Cosmos DB for PostgreSQL használatával.
További információ a pg_azure_storage.
További információ a Postgres COPY támogatásáról.

Megosztás a következőn keresztül:

Adatok betöltése pg_azure_storage használatával az Azure Cosmos DB for PostgreSQL-ben

Adatbázis és blobtároló előkészítése

Tároló tartalmának listázása

Adatok betöltése ABS-ből

Adatok betöltése a COPY paranccsal

Adatok betöltése blob_get()

A blob_get() beállításai

Privát tároló elérése

Következő lépések

Visszajelzés

Visszajelzés

További források