Comment ingérer des données avec pg_azure_storage dans Azure Cosmos DB for PostgreSQL

S’APPLIQUE À : Azure Cosmos DB for PostgreSQL (avec l’extension de base de données Citus pour PostgreSQL)

Cet article explique comment utiliser l’extension pg_azure_storage PostgreSQL pour manipuler et charger des données dans votre instance Azure Cosmos DB for PostgreSQL directement à partir de Stockage Blob Azure(ABS). ABS est un service de stockage scalable, durable et sécurisé natif Cloud. Ces caractéristiques en font un choix adapté pour le stockage et le déplacement de données existantes dans le cloud.

Préparer la base de données et le stockage blob

Pour charger des données à partir de Stockage Blob Azure, installez l’extension PostgreSQL pg_azure_storage dans votre base de données :

SELECT * FROM create_extension('azure_storage');

Nous avons préparé un jeu de données de démonstration public pour cet article. Pour utiliser votre propre jeu de données, suivez la page Migrer vos données locales vers le stockage cloud pour apprendre à obtenir efficacement vos jeux de données dans Stockage Blob Azure.

Lister le contenu d’un conteneur

Il existe une démonstration de compte et de conteneur Stockage Blob Azure précréés pour ce guide pratique. Le nom du conteneur est github, et il se trouve dans le compte pgquickstart. Vous pouvez facilement voir quels fichiers sont présents dans le conteneur à l’aide de la fonction azure_storage.blob_list(account, container).

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

Vous pouvez filtrer la sortie à l’aide d’une clause SQL WHERE standard ou à l’aide du paramètre prefix de la fonction définie par l’utilisateur blob_list. Ce dernier filtre les lignes retournées du côté du Stockage Blob Azure.

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

Charger des données à partir de Stockage Blob Azure

Charger des données avec la commande COPY

Commencez par créer un exemple de sché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');

Le chargement de données dans les tables devient aussi simple que l’appel de la commande 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';

Notez que l’extension a reconnu que les URL fournies à la commande de copie proviennent de Stockage Blob Azure, que les fichiers que nous avons signalés ont été compressés en gzip et que cela a également été géré automatiquement pour nous.

La commande COPY prend en charge davantage de paramètres et de formats. Dans l’exemple ci-dessus, le format et la compression ont été sélectionnés automatiquement en fonction des extensions de fichier. Vous pouvez toutefois fournir le format directement similaire à la commande standard COPY.

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

Actuellement, l’extension prend en charge les formats de fichier suivants :

Charger des données avec blob_get()

La commande COPY est pratique, mais limitée en flexibilité. En interne, COPY utilise la fonction blob_get, que vous pouvez utiliser directement pour manipuler des données dans des scénarios plus complexes.

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

Avec cette fonction, vous pouvez manipuler des données à la volée dans des requêtes complexes et effectuer des importations en tant que 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

Dans la commande ci-dessus, nous avons filtré les données sur des comptes ayant un gravatar_id et mis en majuscules leurs identifiants de connexion à la volée.

Options pour blob_get()

Dans certaines situations, vous devrez peut-être contrôler exactement les tentatives blob_get à effectuer à l’aide des paramètres decoder, compression et options.

Le décodeur peut être défini sur auto (par défaut) avec l’une des valeurs suivantes :

compression peut être auto (valeur par défaut), none ou gzip.

Enfin, le paramètre options est de type jsonb. Il existe quatre fonctions utilitaires qui aident à créer des valeurs pour celui-ci. Chaque fonction utilitaire est désignée pour le décodeur correspondant à son nom.

En examinant les définitions de fonction, vous pouvez voir quels paramètres sont pris en charge par le décodeur.

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

Sachant ce qui précède, vous pouvez ignorer les enregistrements avec un gravatar_id null lors de l’analyse.

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

Accéder au stockage privé

1. Obtenez le nom de votre compte et la clé d’accès.

   Sans clé d’accès, vous ne serez pas autorisé à répertorier les conteneurs définis sur les niveaux d’accès Privé ou 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>')
   

   Dans votre compte de stockage, ouvrez Clés d’accès. Copiez le nom du compte de stockage et copiez la clé à partir de la section key1 (vous devez d’abord sélectionner Afficher en regard de la clé).

   

2. Ajout d’un compte à pg_azure_storage

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

   Vous pouvez maintenant répertorier les conteneurs définis sur les niveaux d’accès Privé et Blob pour ce stockage, mais uniquement en tant qu’utilisateur citus, auquel le rôle azure_storage_admin a été accordé. Si vous créez un utilisateur nommé support, il n’est pas autorisé à accéder au contenu du conteneur par défaut.

   SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
   

   ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
   

3. Autoriser l’utilisateur support à utiliser un compte Stockage Blob Azure spécifique

   L’octroi de l’autorisation est aussi simple que l’appel de account_user_add.

   SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
   

   Vous pouvez voir les utilisateurs autorisés dans la sortie de account_list, qui affiche tous les comptes avec des clés d’accès définies.

   SELECT * FROM azure_storage.account_list();
   

    account_name     | allowed_users
   ------------------+---------------
    mystorageaccount | {support}
   (1 row)
   

   Si vous le décidez, l’utilisateur ne pourra plus avoir accès. Il suffit d’appeler account_user_remove.

   SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
   

Étapes suivantes

Félicitations, vous venez d’apprendre à charger des données dans Azure Cosmos DB for PostgreSQL directement à partir de Stockage Blob Azure.

• Découvrez comment créer un tableau de bord en temps réel avec Azure Cosmos DB for PostgreSQL.
• En savoir plus sur pg_azure_storage.
• Découvrez la prise en charge de Postgres COPY.