Mata in data med hjälp av pg_azure_storage i Azure Cosmos DB för 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');

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 instruktionerna för att migrera dina data på plats till molnlagring för att lära dig hur du effektivt får dina datamängder till Azure Blob Storage.

Visa en lista över containerinnehåll

Det finns ett demonstrationskonto och en container för Azure Blob Storage som skapats i förväg för den här instruktionen. 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.

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

Ladda 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:

Ladda data med blob_get()

Kommandot COPY är praktiskt, men begränsat i flexibilitet. COPY använder internt blob_get-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

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 där gravatar_id är närvarande och ändrade deras inloggningar till versaler i farten.

Alternativ för blob_get()

I vissa situationer kan du behöva kontrollera exakt vad blob_get försöker göra genom att använda parametrarna decoder, compression och options.

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

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.

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 - innehållskodning

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

1. 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).

   

2. Lägga till ett konto i pg_azure_storage

   SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
   

   Nu kan du lista de containrar som är inställda på åtkomstnivåerna Privat och Blobb för den lagringen, men endast som citus-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
   

3. 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. Bara ring account_user_remove.

   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.