Gegevens opnemen met behulp van pg_azure_storage in Azure Cosmos DB for PostgreSQL

Artikel
08/15/2024

VAN TOEPASSING OP: Azure Cosmos DB for PostgreSQL (mogelijk gemaakt door de Citus-database-extensie naar PostgreSQL)

In dit artikel wordt beschreven hoe u de pg_azure_storage PostgreSQL-extensie gebruikt om gegevens rechtstreeks vanuit Azure Blob Storage (ABS) te bewerken en te laden in uw Azure Cosmos DB for PostgreSQL. ABS is een cloudeigen schaalbare, duurzame en veilige opslagservice. Deze kenmerken maken het een goede keuze voor het opslaan en verplaatsen van bestaande gegevens naar de cloud.

Database- en blobopslag voorbereiden

Als u gegevens uit Azure Blob Storage wilt laden, installeert u de pg_azure_storage PostgreSQL-extensie in uw database:

SELECT * FROM create_extension('azure_storage');

Belangrijk

De pg_azure_storage-extensie is alleen beschikbaar op Azure Cosmos DB for PostgreSQL-clusters met PostgreSQL 13 en hoger.

We hebben een openbare demonstratiegegevensset voorbereid voor dit artikel. Als u uw eigen gegevensset wilt gebruiken, migreert u uw on-premises gegevens naar de cloudopslag om te leren hoe u uw gegevenssets efficiënt kunt ophalen in Azure Blob Storage.

Notitie

Als u 'Container (anonieme leestoegang voor containers en blobs)' selecteert, kunt u bestanden uit Azure Blob Storage opnemen met behulp van hun openbare URL's en de containerinhoud opsommen zonder dat u een accountsleutel in pg_azure_storage hoeft te configureren. Voor containers die zijn ingesteld op toegangsniveau Privé (geen anonieme toegang)' of 'Blob (alleen anonieme leestoegang voor blobs)' is een toegangssleutel vereist.

Inhoud van container weergeven

Er is een demonstratie van een Azure Blob Storage-account en -container die vooraf zijn gemaakt voor deze procedure. De naam van de container is githuben bevindt zich in het pgquickstart account. We kunnen eenvoudig zien welke bestanden aanwezig zijn in de container met behulp van de azure_storage.blob_list(account, container) functie.

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

U kunt de uitvoer filteren met behulp van een reguliere SQL-component WHERE of met behulp van de prefix parameter van de blob_list UDF. Deze laatste filtert de geretourneerde rijen aan de zijde van Azure Blob Storage.

Notitie

Voor het weergeven van containerinhoud is een account en toegangssleutel of een container met anonieme toegang vereist.

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

Gegevens laden vanuit ABS

Gegevens laden met de opdracht COPY

Begin met het maken van een voorbeeldschema.

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

Het laden van gegevens in de tabellen wordt net zo eenvoudig als het aanroepen van de COPY opdracht.

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

U ziet hoe de extensie heeft herkend dat de URL's die aan de kopieeropdracht zijn verstrekt, afkomstig zijn van Azure Blob Storage, de bestanden die we hebben aangegeven, zijn gecomprimeerd met gzip en dat is ook automatisch voor ons afgehandeld.

De COPY opdracht ondersteunt meer parameters en indelingen. In het bovenstaande voorbeeld zijn de indeling en compressie automatisch geselecteerd op basis van de bestandsextensies. U kunt de indeling echter rechtstreeks vergelijkbaar met de reguliere COPY opdracht opgeven.

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

Momenteel ondersteunt de extensie de volgende bestandsindelingen:

indeling	beschrijving
CSV	Door komma's gescheiden waardenindeling die wordt gebruikt door PostgreSQL COPY
tsv	Door tabs gescheiden waarden, de standaard PostgreSQL COPY-indeling
binair	Binaire PostgreSQL COPY-indeling
sms verzenden	Een bestand met één tekstwaarde (bijvoorbeeld grote JSON of XML)

Gegevens laden met blob_get()

De COPY opdracht is handig, maar beperkt in flexibiliteit. Intern COPY maakt gebruik van de blob_get functie, die u rechtstreeks kunt gebruiken om gegevens in complexere scenario's te bewerken.

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

Notitie

In de bovenstaande query wordt het bestand volledig opgehaald voordat LIMIT 3 het wordt toegepast.

Met deze functie kunt u gegevens onderweg bewerken in complexe query's en importbewerkingen uitvoeren als 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

In de bovenstaande opdracht hebben we de gegevens gefilterd op accounts met een gravatar_id present en hoofdletters van hun aanmeldingen.

Opties voor blob_get()

In sommige situaties moet u mogelijk precies bepalen wat blob_get er wordt geprobeerd te doen met behulp van de decoderen compression options parameters.

Decoder kan worden ingesteld op auto (standaard) of een van de volgende waarden:

indeling	beschrijving
CSV	Door komma's gescheiden waardenindeling die wordt gebruikt door PostgreSQL COPY
tsv	Door tabs gescheiden waarden, de standaard PostgreSQL COPY-indeling
binair	Binaire PostgreSQL COPY-indeling
sms verzenden	Een bestand met één tekstwaarde (bijvoorbeeld grote JSON of XML)

compressionauto kan (standaardnone) of gzip.

Ten slotte is de options parameter van het type jsonb. Er zijn vier hulpprogrammafuncties waarmee u waarden kunt bouwen. Elke functie van het hulpprogramma wordt aangewezen voor de decoder die overeenkomt met de naam.

Decoder	opties, functie
CSV	`options_csv_get`
tsv	`options_tsv`
binair	`options_binary`
sms verzenden	`options_copy`

Door de functiedefinities te bekijken, kunt u zien welke parameters worden ondersteund door welke decoder.

options_csv_get - scheidingsteken, null_string, koptekst, aanhalingsteken, escape, force_not_null, force_null, content_encoding options_tsv - scheidingsteken, null_string, content_encoding options_copy - scheidingsteken, null_string, koptekst, aanhalingsteken, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Als u het bovenstaande weet, kunnen we opnamen met null gravatar_id verwijderen tijdens het parseren.

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

Toegang tot privéopslag

Uw accountnaam en toegangssleutel verkrijgen

Zonder een toegangssleutel mogen we geen containers weergeven die zijn ingesteld op privé- of blobtoegangsniveaus.
```
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>')
```
Open toegangssleutels in uw opslagaccount. Kopieer de naam van het opslagaccount en kopieer de sleutel uit de sectie Sleutel1 (u moet eerst Weergeven naast de sleutel selecteren).
Een account toevoegen aan pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
U kunt nu containers weergeven die zijn ingesteld op toegangsniveaus voor privé- en blobtoegang voor die opslag, maar alleen als de citus gebruiker, waaraan de azure_storage_admin rol is verleend. Als u een nieuwe gebruiker met de naam supportmaakt, is het niet standaard toegestaan om toegang te krijgen tot de inhoud van de container.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
Toestaan dat de support gebruiker een specifiek Azure Blob Storage-account gebruikt

Het verlenen van de machtiging is net zo eenvoudig als het aanroepen account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
We kunnen de toegestane gebruikers zien in de uitvoer van account_list, waarin alle accounts met gedefinieerde toegangssleutels worden weergegeven.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Als u ooit besluit dat de gebruiker geen toegang meer heeft. Bel account_user_removegewoon.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Volgende stappen

Gefeliciteerd, u hebt zojuist geleerd hoe u gegevens rechtstreeks vanuit Azure Blob Storage kunt laden in Azure Cosmos DB for PostgreSQL.

Meer informatie over het maken van een realtime dashboard met Azure Cosmos DB voor PostgreSQL.
Meer informatie over pg_azure_storage.
Meer informatie over ondersteuning voor Postgres COPY.

Delen via

Gegevens opnemen met behulp van pg_azure_storage in Azure Cosmos DB for PostgreSQL

Database- en blobopslag voorbereiden

Inhoud van container weergeven

Gegevens laden vanuit ABS

Gegevens laden met de opdracht COPY

Gegevens laden met blob_get()

Opties voor blob_get()

Toegang tot privéopslag

Volgende stappen

Feedback

Aanvullende resources