Erfassen von Daten mithilfe von pg_azure_storage in Azure Cosmos DB for PostgreSQL

Artikel
06/16/2023

GILT FÜR: Azure Cosmos DB for PostgreSQL (unterstützt von der Citus-Datenbankerweiterung auf PostgreSQL)

In diesem Artikel wird gezeigt, wie Sie die PostgreSQL-Erweiterung „pg_azure_storage“ verwenden, um Daten zu bearbeiten und direkt aus Azure Blob Storage (ABS) in Azure Cosmos DB for PostgreSQL zu laden. ABS ist ein cloudnativer skalierbarer, permanenter und sicherer Speicherdienst. Aufgrund dieser Merkmale eignet sich der Dienst zum Speichern und Verschieben vorhandener Daten in die Cloud.

Vorbereiten von Datenbank und Blob Storage

Um Daten aus Azure Blob Storage zu laden, installieren Sie die PostgreSQL-Erweiterung pg_azure_storage in Ihrer Datenbank:

SELECT * FROM create_extension('azure_storage');

Wichtig

Die Erweiterung pg_azure_storage ist nur für Azure Cosmos DB for PostgreSQL-Cluster verfügbar, in denen PostgreSQL 13 und höher ausgeführt wird.

Wir haben ein öffentliches Demo-Dataset für diesen Artikel vorbereitet. Um Ihr eigenes Dataset zu verwenden, folgen Sie Migrieren von lokalen Daten in Cloudspeicher, um zu erfahren, wie Sie Ihre Datasets effizient in Azure Blob Storage übertragen können.

Hinweis

Durch Auswählen von „Container (anonymer Lesezugriff für Container und Blobs)“ können Sie Dateien aus Azure Blob Storage mithilfe ihrer öffentlichen URLs erfassen und den Containerinhalt auflisten, ohne dass ein Kontoschlüssel in pg_azure_storage konfiguriert werden muss. Container, die auf die Zugriffsebene „Privat (kein anonymer Zugriff)“ oder „Blob (anonymer Lesezugriff nur für Blobs)“ festgelegt sind, erfordern einen Zugriffsschlüssel.

Auflisten von Containerinhalten

Es gibt ein Azure Blob Storage-Demo-Konto und einen Container, der für diese Vorgehensweise vorab erstellt wurde. Der Name des Containers ist github, und er befindet sich im pgquickstart-Konto. Wir können so problemlos sehen, welche Dateien im Container vorhanden sind, indem die Funktion azure_storage.blob_list(account, container) verwendet wird.

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

Sie können die Ausgabe entweder mithilfe einer regulären SQL-WHERE-Klausel oder mithilfe des prefix-Parameters der blob_list-UDF filtern. Letzteres filtert die zurückgegebenen Zeilen auf der Azure Blob Storage-Seite.

Hinweis

Für das Auflisten von Containerinhalten sind ein Konto und ein Zugriffsschlüssel oder ein Container mit aktiviertem anonymem Zugriff erforderlich.

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

Laden von Daten aus ABS

Laden von Daten mit dem Befehl COPY

Beginnen Sie mit dem Erstellen eines Stichproben-Schemas.

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

Das Laden von Daten in die Tabellen wird so einfach wie das Aufrufen des Befehls 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';

Beachten Sie, dass die Erweiterung erkannt hat, dass die dem Kopierbefehl übergebenen URLs von Azure Blob Storage stammen, die Dateien, auf die wir verwiesen haben, mit gzip komprimiert waren und dies auch automatisch für uns erledigt wurde.

Der COPY-Befehl unterstützt weitere Parameter und Formate. Im vorstehenden Beispiel wurden das Format und die Komprimierung basierend auf den Dateierweiterungen automatisch ausgewählt. Sie können das Format jedoch direkt angeben, ähnlich wie bei dem regulären COPY-Befehl.

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

Die Erweiterung unterstützt derzeit die folgenden Dateiformate:

format	description
csv	Durch Komma getrenntes Format für Werte, das von PostgreSQL COPY verwendet wird
tsv	Durch Tabstopp getrennte Werte – das Standardformat PostgreSQL COPY
BINARY	Format Binary PostgreSQL COPY
text	Datei mit einem einzelnen Textwert (z. B. große JSON oder XML)

Laden von Daten mit blob_get()

Der COPY-Befehl ist nützlich, aber in der Flexibilität begrenzt. Intern verwendet der Befehl COPY die blob_get-Funktion, die Sie direkt zum Bearbeiten von Daten in komplexeren Szenarios verwenden können.

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

Hinweis

In der vorstehenden Abfrage wird die Datei vollständig abgerufen, bevor LIMIT 3 angewendet wird.

Mit dieser Funktion können Sie Daten in komplexen Abfragen nach Bedarf bearbeiten, und einen Import als INSERT FROM SELECT ausführen.

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

Im vorstehenden Befehl haben wir die Daten in Konten mit vorhandenem gravatar_id gefiltert und ihre Anmeldeinformationen nach Bedarf mit Großbuchstaben versehen.

Optionen für blob_get()

In einigen Situationen müssen Sie möglicherweise genau kontrollieren, was blob_get versucht auszuführen, wenn die decoder-, compression- und options-Parameter verwendet werden.

Decoder kann auf auto (Standard) oder einen der anderen Werte festgelegt werden:

format	description
csv	Durch Komma getrenntes Format für Werte, das von PostgreSQL COPY verwendet wird
tsv	Durch Tabstopp getrennte Werte – das Standardformat PostgreSQL COPY
BINARY	Format Binary PostgreSQL COPY
text	Datei mit einem einzelnen Textwert (z. B. große JSON oder XML)

compression kann entweder auto (Standard), none oder gzip sein.

Schließlich ist der options-Parameter vom Typ jsonb. Es gibt vier Hilfsfunktionen, mit denen entsprechende Werte erstellt werden können. Jede Hilfsfunktion ist für den Decoder vorgesehen, der seinem Namen entspricht.

Decoder	Optionen-Funktion
csv	`options_csv_get`
tsv	`options_tsv`
BINARY	`options_binary`
text	`options_copy`

Anhand der Funktionsdefinitionen können Sie sehen, welche Parameter von welchem Decoder unterstützt werden.

options_csv_get - delimiter, null_string, header, quote, escape, force_not_null, force_null, content_encoding options_tsv - delimiter, null_string, content_encoding options_copy - delimiter, null_string, header, quote, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Mit Kenntnis der vorstehenden Ausführungen können wir Aufzeichnungen mit Null-gravatar_id während der Analyse verwerfen.

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

Zugreifen auf privaten Speicher

Kontoname und Zugriffsschlüssel abrufen

Ohne Zugriffsschlüssel dürfen wir keine Container auflisten, die auf privaten oder Blob-Zugriffsebenen festgelegt sind.
```
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>')
```
Öffnen Sie in Ihrem Speicherkonto Zugriffsschlüssel. Kopieren Sie den Namen des Speicherkontos und den Schlüssel aus Abschnitt Schlüssel1 (Sie müssen zuerst Show neben dem Schlüssel auswählen).
Hinzufügen eines Kontos zu pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Jetzt können Sie Container auflisten, die auf privaten und Blob-Zugriffsebenen für diesen Speicher festlegt sind, aber nur als citus-Benutzer, dem die azure_storage_admin-Rolle zugewiesen wurde. Wenn Sie einen neuen Benutzer mit Namen support erstellen, ist es standardmäßig nicht zulässig, auf Containerinhalte zuzugreifen.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
Der support-Benutzer kann ein bestimmtes Azure Blob Storage-Konto verwenden

Das Zuweisen der Berechtigung ist so einfach wie das Aufrufen von account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Wir können die zulässigen Benutzer in der Ausgabe von account_list sehen, in der alle Konten mit definierten Zugriffsschlüsseln angezeigt werden.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Wenn Sie entscheiden sollten, dass der Benutzer keinen Zugriff mehr haben darf. Rufen Sie einfach unter account_user_remove an.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Nächste Schritte

Herzlichen Glückwunsch, Sie haben gerade gelernt, wie Sie Daten direkt aus Azure Blob Storage in Azure Cosmos DB for PostgreSQL laden.

Erfahren Sie, wie Sie ein Echtzeitdashboard mit Azure Cosmos DB for PostgreSQL erstellen.
Erfahren Sie mehr über Azure Storage.
Erfahren Sie mehr über den Postgres COPY-Support.

Share via

Erfassen von Daten mithilfe von pg_azure_storage in Azure Cosmos DB for PostgreSQL

Vorbereiten von Datenbank und Blob Storage

Auflisten von Containerinhalten

Laden von Daten aus ABS

Laden von Daten mit dem Befehl COPY

Laden von Daten mit blob_get()

Optionen für blob_get()

Zugreifen auf privaten Speicher

Nächste Schritte

Zusätzliche Ressourcen