Mata in data med hjälp av pg_azure_storage i Azure Cosmos DB för PostgreSQL

Artikel
08/15/2024

GÄLLER FÖR: Azure Cosmos DB for PostgreSQL (drivs av Citus-databastillägget till PostgreSQL)

Den här artikeln visar hur du använder pg_azure_storage PostgreSQL-tillägget för att manipulera och läsa in data i Azure Cosmos DB for PostgreSQL direkt från Azure Blob Storage (ABS). ABS är en molnbaserad skalbar, hållbar och säker lagringstjänst. De här egenskaperna gör det till ett bra val att lagra och flytta befintliga data till molnet.

Förbereda databas- och bloblagring

Om du vill läsa in data från Azure Blob Storage installerar du pg_azure_storage PostgreSQL-tillägget i databasen:

SELECT * FROM create_extension('azure_storage');

Viktigt!

Tillägget pg_azure_storage är endast tillgängligt i Azure Cosmos DB för PostgreSQL-kluster som kör PostgreSQL 13 och senare.

Vi har förberett en offentlig demonstrationsdatauppsättning för den här artikeln. Om du vill använda din egen datauppsättning följer du migrera dina lokala data till molnlagring för att lära dig hur du får dina datamängder effektivt till Azure Blob Storage.

Kommentar

Om du väljer "Container (anonym läsåtkomst för containrar och blobar)" kan du mata in filer från Azure Blob Storage med hjälp av deras offentliga URL:er och räkna upp containerinnehållet utan att behöva konfigurera en kontonyckel i pg_azure_storage. Containrar som är inställda på åtkomstnivå "Privat (ingen anonym åtkomst)" eller "Blob (endast anonym läsåtkomst för blobar)" kräver en åtkomstnyckel.

Visa en lista över containerinnehåll

Det finns en demonstration av Azure Blob Storage-kontot och containern som skapats i förväg för den här instruktioner. Containerns namn är github, och det finns i pgquickstart kontot. Vi kan enkelt se vilka filer som finns i containern med hjälp azure_storage.blob_list(account, container) av funktionen .

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

Du kan filtrera utdata antingen med hjälp av en vanlig SQL-sats WHERE eller med hjälp av parametern prefix för blob_list UDF. Det senare filtrerar de returnerade raderna på Azure Blob Storage-sidan.

Kommentar

En lista över containerinnehåll kräver ett konto och en åtkomstnyckel eller en container med aktiverad anonym åtkomst.

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

Läsa in data från ABS

Läsa in data med kommandot COPY

Börja med att skapa ett exempelschema.

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

Att läsa in data i tabellerna blir lika enkelt som att anropa COPY kommandot.

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

Observera hur tillägget kände igen att URL:erna som tillhandahålls till kopieringskommandot kommer från Azure Blob Storage, filerna som vi pekade på komprimerades med gzip och som också hanterades automatiskt åt oss.

Kommandot COPY stöder fler parametrar och format. I exemplet ovan valdes formatet och komprimering automatiskt baserat på filnamnstilläggen. Du kan dock ange formatet som liknar det vanliga COPY kommandot.

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

Tillägget stöder för närvarande följande filformat:

format	description
csv	Format för kommaavgränsade värden som används av PostgreSQL COPY
tsv	Tabbavgränsade värden, standardformatet PostgreSQL COPY
binary	Binärt PostgreSQL COPY-format
text	En fil som innehåller ett enda textvärde (till exempel stor JSON eller XML)

Läsa in data med blob_get()

Kommandot COPY är praktiskt, men begränsat i flexibilitet. Internt använder blob_get COPY funktionen, som du kan använda direkt för att manipulera data i mer komplexa scenarier.

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

Kommentar

I ovanstående fråga hämtas filen helt innan LIMIT 3 den tillämpas.

Med den här funktionen kan du ändra data direkt i komplexa frågor och importera som 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

I kommandot ovan filtrerade vi data till konton med en gravatar_id nuvarande och versaler deras inloggningar i farten.

Alternativ för blob_get()

I vissa situationer kan du behöva kontrollera exakt vad blob_get som ska göras med hjälp av parametrarna decoderoch compression options .

Avkodaren kan anges till auto (standard) eller något av följande värden:

format	description
csv	Format för kommaavgränsade värden som används av PostgreSQL COPY
tsv	Tabbavgränsade värden, standardformatet PostgreSQL COPY
binary	Binärt PostgreSQL COPY-format
text	En fil som innehåller ett enda textvärde (till exempel stor JSON eller XML)

compression kan vara antingen auto (standard) none eller gzip.

Slutligen är parametern options av typen jsonb. Det finns fyra verktygsfunktioner som hjälper dig att skapa värden för den. Varje verktygsfunktion är avsedd för avkodaren som matchar dess namn.

avkodare	funktion för alternativ
csv	`options_csv_get`
tsv	`options_tsv`
binary	`options_binary`
text	`options_copy`

Genom att titta på funktionsdefinitionerna kan du se vilka parametrar som stöds av vilken avkodare.

options_csv_get - avgränsare, null_string, rubrik, citat, escape, force_not_null, force_null, content_encoding options_tsv - avgränsare, null_string, content_encoding options_copy - avgränsare, null_string, rubrik, citat, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Med vetskapen om ovanstående kan vi ignorera inspelningar med null gravatar_id under parsning.

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

Åtkomst till privat lagring

Hämta ditt kontonamn och din åtkomstnyckel

Utan en åtkomstnyckel kan vi inte visa en lista över containrar som är inställda på åtkomstnivåer för privat eller 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>')
```
Öppna Åtkomstnycklar i ditt lagringskonto. Kopiera lagringskontots namn och kopiera avsnittet Nyckel från key1 (du måste välja Visa bredvid nyckeln först).
Lägga till ett konto i pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Nu kan du visa en lista över containrar som är inställda på åtkomstnivåer för privat och blobb för lagringen citus , men bara som användare, som har tilldelats rollen azure_storage_admin . Om du skapar en ny användare med namnet supportfår den inte åtkomst till containerinnehållet som standard.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
support Tillåt att användaren använder ett specifikt Azure Blob Storage-konto

Att bevilja behörigheten är lika enkelt som att anropa account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Vi kan se de tillåtna användarna i utdata från account_list, som visar alla konton med definierade åtkomstnycklar.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Om du bestämmer dig för att användaren inte längre ska ha åtkomst. Ring account_user_removebara .
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Nästa steg

Grattis, du har precis lärt dig hur du läser in data i Azure Cosmos DB för PostgreSQL direkt från Azure Blob Storage.

Lär dig hur du skapar en instrumentpanel i realtid med Azure Cosmos DB for PostgreSQL.
Läs mer om pg_azure_storage.
Läs mer om Stöd för Postgres COPY.

Dela via

Mata in data med hjälp av pg_azure_storage i Azure Cosmos DB för PostgreSQL

Förbereda databas- och bloblagring

Visa en lista över containerinnehåll

Läsa in data från ABS

Läsa in data med kommandot COPY

Läsa in data med blob_get()

Alternativ för blob_get()

Åtkomst till privat lagring

Nästa steg

Feedback

Ytterligare resurser