Extension pg_azure_storage

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

L’extension pg_azure_storage vous permet de charger des données dans plusieurs formats de fichiers directement à partir du stockage Blob Azure vers votre cluster Azure Cosmos DB for PostgreSQL. L’activation de l’extension déverrouille également les nouvelles fonctionnalités de la commande COPY. Les conteneurs avec le niveau d’accès Privé ou Blob nécessitent l’ajout d’une clé d’accès privée.

Vous pouvez créer l’extension en exécutant :

SELECT create_extension('azure_storage');

azure_storage.account_add

La fonction permet d’ajouter l’accès à un compte de stockage.

azure_storage.account_add
        (account_name_p text
        ,account_key_p text);

Arguments

account_name_p

Un compte de stockage Blob Azure (ABS) contient tous vos objets ABS : blobs, fichiers, files d’attente et tables. Le compte de stockage fournit pour votre ABS un espace de noms unique, accessible de n’importe où dans le monde via HTTPS.

account_key_p

Vos clés d’accès de stockage Blob Azure (ABS) sont similaires au mot de passe racine de votre compte de stockage. Veillez toujours à protéger vos clés d’accès. Utilisez Azure Key Vault pour gérer et effectuer la rotation de vos clés en toute sécurité. La clé de compte est stockée dans une table accessible par le superutilisateur postgres, azure_storage_admin et tous les rôles disposant de ces autorisations d’administrateur. Pour voir quels comptes de stockage existent, utilisez la fonction account_list.

azure_storage.account_remove

La fonction permet de révoquer l’accès du compte au compte de stockage.

azure_storage.account_remove
        (account_name_p text);

Arguments

account_name_p

Le compte de stockage Blob Azure (ABS) contient tous vos objets ABS : blobs, fichiers, files d’attente et tables. Le compte de stockage fournit pour votre ABS un espace de noms unique, accessible de n’importe où dans le monde via HTTPS.

azure_storage.account_user_add

La fonction permet d’ajouter l’accès d’un rôle à un compte de stockage.

azure_storage.account_user_add
        ( account_name_p text
        , user_p regrole);

Arguments

account_name_p

Un compte de stockage Blob Azure (ABS) contient tous vos objets ABS : blobs, fichiers, files d’attente et tables. Le compte de stockage fournit pour votre ABS un espace de noms unique, accessible de n’importe où dans le monde via HTTPS.

user_p

Rôle créé par l’utilisateur visible sur le cluster.

azure_storage.account_user_remove

La fonction permet de supprimer l’accès d’un rôle à un compte de stockage.

azure_storage.account_user_remove
        (account_name_p text
        ,user_p regrole);

Arguments

account_name_p

Un compte de stockage Blob Azure (ABS) contient tous vos objets ABS : blobs, fichiers, files d’attente et tables. Le compte de stockage fournit pour votre ABS un espace de noms unique, accessible de n’importe où dans le monde via HTTPS.

user_p

Rôle créé par l’utilisateur visible sur le cluster.

azure_storage.account_list

La fonction répertorie le compte et le rôle ayant accès au stockage d’objets blob Azure.

azure_storage.account_list
        (OUT account_name text
        ,OUT allowed_users regrole[]
        )
Returns TABLE;

Arguments

account_name

Le compte de stockage Blob Azure (ABS) contient tous vos objets ABS : blobs, fichiers, files d’attente et tables. Le compte de stockage fournit pour votre ABS un espace de noms unique, accessible de n’importe où dans le monde via HTTPS.

allowed_users

Répertorie les utilisateurs ayant accès au stockage Blob Azure.

Type de retour

TABLE

azure_storage.blob_list

La fonction répertorie les fichiers blob disponibles dans un conteneur d’utilisateurs avec leurs propriétés.

azure_storage.blob_list
        (account_name text
        ,container_name text
        ,prefix text DEFAULT ''::text
        ,OUT path text
        ,OUT bytes bigint
        ,OUT last_modified timestamp with time zone
        ,OUT etag text
        ,OUT content_type text
        ,OUT content_encoding text
        ,OUT content_hash text
        )
Returns SETOF record;

Arguments

account_name

Le storage account name fournit pour vos données de stockage Azure un espace de noms unique, accessible de n’importe où dans le monde par le biais du protocole HTTPS.

container_name

Un conteneur regroupe un ensemble d’objets blob, à la manière d’un répertoire dans un système de fichiers. Un compte de stockage peut contenir un nombre illimité de conteneurs, et un conteneur peut stocker un nombre illimité d’objets blob. Un nom de conteneur doit être un nom DNS valide, car il fait partie de l’URI unique utilisé pour adresser le conteneur ou ses objets BLOB. Suivez ces règles lorsque vous nommez un conteneur :

• Les noms de conteneur doivent comprendre entre 3 et 63 caractères.
• Les noms de conteneur doivent commencer par une lettre ou un chiffre, et peuvent comporter uniquement des lettres minuscules, des chiffres et des tirets (-).
• Deux tirets consécutifs ou plus ne sont pas autorisés dans les noms de conteneurs.

L’URI d’un conteneur est similaire à : https://myaccount.blob.core.windows.net/mycontainer

prefix

Retourne un fichier à partir d’un conteneur d’objets blob avec des initiales de chaîne correspondantes.

path

Chemin d’accès complet du répertoire d’objets blob Azure.

octets

Taille de l’objet fichier en octets.

last_modified

Quand le contenu du fichier a-t-il été modifié pour la dernière fois.

etag

Une propriété ETag est utilisée pour l’accès concurrentiel optimiste pendant les mises à jour. Il ne s’agit pas d’un timestamp, car il existe une autre propriété appelée Timestamp qui stocke la dernière fois qu’un enregistrement a été mis à jour. Par exemple, si vous chargez une entité et que vous souhaitez la mettre à jour, l’ETag doit correspondre à ce qui est actuellement stocké. Il est important de définir l’ETag approprié, car si plusieurs utilisateurs modifient le même élément, vous ne souhaitez pas qu’ils remplacent les modifications des autres utilisateurs.

content_type

L’objet Blob représente un blob, qui est un objet de type Fichier de données brutes immuables. Il peut être lu en tant que données texte ou binaires, ou converti en ReadableStream afin que ses méthodes puissent être utilisées pour le traitement des données. Les objets Blob peuvent représenter des données qui ne sont pas nécessairement dans un format natif JavaScript.

content_encoding

Stockage Azure vous permet de définir la propriété Content-Encoding sur un blob. Pour le contenu compressé, vous pouvez définir la propriété sur GZIP. Lorsque le navigateur accède au contenu, il désactive automatiquement le contenu.

content_hash

Ce hachage est utilisé pour vérifier l'intégrité de l'objet blob pendant le transport. Lorsque cet en-tête est spécifié, le service de stockage compare le hachage qui est arrivé avec celui qui a été envoyé. Si les deux hachages ne correspondent pas, l'opération échoue avec le code d'erreur 400 (Demande incorrecte).

Type de retour

Enregistrement SETOF

azure_storage.blob_get

La fonction permet de charger le contenu du ou des fichiers à partir du conteneur, avec une prise en charge supplémentaire du filtrage ou de la manipulation des données, avant l’importation.

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF record;

Il existe une version surchargée de la fonction, contenant le paramètre rec qui vous permet de définir facilement l’enregistrement de format de sortie.

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,rec anyelement
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF anyelement;

Arguments

account

Le compte de stockage fournit pour vos données de stockage Azure un espace de noms unique, accessible de n’importe où dans le monde par le biais du protocole HTTPS.

conteneur

Un conteneur regroupe un ensemble d’objets blob, à la manière d’un répertoire dans un système de fichiers. Un compte de stockage peut contenir un nombre illimité de conteneurs, et un conteneur peut stocker un nombre illimité d’objets blob. Un nom de conteneur doit être un nom DNS valide, car il fait partie de l’URI unique utilisé pour adresser le conteneur ou ses objets BLOB.

path

Nom de blob existant dans le conteneur.

rec

Définissez la structure de sortie d’enregistrement.

decoder

Spécifier le format de blob. Decoder peut être défini sur auto (valeur par défaut) ou sur l’une des valeurs suivantes

description de decoder

Format	Description
csv	Format sous forme de valeurs séparées par des virgules utilisé par PostgreSQL COPY
tsv	Valeurs séparées par des tabulations, format POSTGRESQL COPY par défaut
binary	Format PostgreSQL COPY binaire
text	Fichier contenant une valeur de texte unique (par exemple, un fichier JSON ou XML volumineux)

compression

Définit le format de compression. Les options disponibles sont auto, gzip & none. L’utilisation de l’option auto (par défaut) devine la compression en fonction de l’extension de fichier (.gz == gzip). L’option none force à ignorer l’extension et à ne pas tenter de décoder. gzip force l’utilisation du décodeur gzip (lorsque vous avez un fichier compressé au format gzip avec une extension non standard). Actuellement, nous ne prenons pas en charge d’autres formats de compression pour l’extension.

options

Pour gérer les en-têtes personnalisés, les séparateurs personnalisés, les caractères d’échappement, etc., options fonctionne de la même manière que la commande COPY dans PostgreSQL. Le paramètre utilise la fonction blob_get.

Type de retour

Enregistrement SETOF / anyelement

azure_storage.options_csv_get

La fonction agit comme une fonction utilitaire appelée en tant que paramètre dans blob_get, ce qui est utile pour décoder le contenu csv.

azure_storage.options_csv_get
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Arguments

delimiter

Spécifie le caractère qui sépare les colonnes dans chaque ligne du fichier. La valeur par défaut est un caractère de tabulation au format texte, une virgule au format CSV. Il doit s’agir d’un caractère codé sur un octet.

null_string

Spécifie la chaîne qui représente une valeur nulle. La valeur par défaut est \N (barre oblique inverse-N) au format texte et une chaîne vide sans guillemets au format CSV. Vous préférerez peut-être une chaîne vide même au format texte pour les cas où vous ne souhaitez pas distinguer les valeurs nulles des chaînes vides.

en-tête

Spécifie que le fichier contient une ligne d’en-tête avec les noms de chaque colonne du fichier. Lors de la sortie, la première ligne contient les noms de colonnes de la table.

quote

Spécifie le caractère de guillemet à utiliser lorsqu’une valeur de données est entre guillemets. La valeur par défaut est double-quote (guillemet double). Il doit s’agir d’un caractère codé sur un octet.

échappement

Spécifie le caractère qui doit apparaître avant un caractère de données qui correspond à la valeur QUOTE. La valeur par défaut est identique à la valeur QUOTE (de sorte que le caractère de guillemet est doublé s’il apparaît dans les données). Il doit s’agir d’un caractère codé sur un octet.

force_not_null

Ne pas faire correspondre les valeurs des colonnes spécifiées à la chaîne nulle. Dans le cas par défaut où la chaîne nulle est vide, cela signifie que les valeurs vides sont lues comme des chaînes de longueur nulle plutôt que des valeurs nulles, même si elles ne sont pas entre guillemets.

force_null

Faire correspondre les valeurs des colonnes spécifiées à la chaîne nulle, même si elle est entre guillemets, et si une correspondance est trouvée, définissez la valeur sur NULL. Dans le cas par défaut où la chaîne nulle est vide, elle convertit une chaîne vide entre guillemets en valeur NULL.

content_encoding

Spécifie que le fichier est codé selon la valeur de encoding_name. Si l’option est omise, le codage client actuel est utilisé.

Type de retour

jsonb

azure_storage.options_copy

La fonction agit comme une fonction utilitaire appelée en tant que paramètre dans blob_get.

azure_storage.options_copy
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_quote text[] DEFAULT NULL::text[]
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Arguments

delimiter

Spécifie le caractère qui sépare les colonnes dans chaque ligne du fichier. La valeur par défaut est un caractère de tabulation au format texte, une virgule au format CSV. Il doit s’agir d’un caractère codé sur un octet.

null_string

Spécifie la chaîne qui représente une valeur nulle. La valeur par défaut est \N (barre oblique inverse-N) au format texte et une chaîne vide sans guillemets au format CSV. Vous préférerez peut-être une chaîne vide même au format texte pour les cas où vous ne souhaitez pas distinguer les valeurs nulles des chaînes vides.

en-tête

Spécifie que le fichier contient une ligne d’en-tête avec les noms de chaque colonne du fichier. Lors de la sortie, la première ligne contient les noms de colonnes de la table.

quote

Spécifie le caractère de guillemet à utiliser lorsqu’une valeur de données est entre guillemets. La valeur par défaut est double-quote (guillemet double). Il doit s’agir d’un caractère codé sur un octet.

échappement

Spécifie le caractère qui doit apparaître avant un caractère de données qui correspond à la valeur QUOTE. La valeur par défaut est identique à la valeur QUOTE (de sorte que le caractère de guillemet est doublé s’il apparaît dans les données). Il doit s’agir d’un caractère codé sur un octet.

force_quote

Force l’utilisation de guillemets pour toutes les valeurs non nulles dans chaque colonne spécifiée. La sortie NULL n’est jamais entre guillemets. Si * est spécifié, les valeurs non nulles sont entre guillemets dans toutes les colonnes.

force_not_null

Ne pas faire correspondre les valeurs des colonnes spécifiées à la chaîne nulle. Dans le cas par défaut où la chaîne nulle est vide, cela signifie que les valeurs vides sont lues comme des chaînes de longueur nulle plutôt que des valeurs nulles, même si elles ne sont pas entre guillemets.

force_null

Faire correspondre les valeurs des colonnes spécifiées à la chaîne nulle, même si elle est entre guillemets, et si une correspondance est trouvée, définissez la valeur sur NULL. Dans le cas par défaut où la chaîne nulle est vide, elle convertit une chaîne vide entre guillemets en valeur NULL.

content_encoding

Spécifie que le fichier est codé selon la valeur de encoding_name. Si l’option est omise, le codage client actuel est utilisé.

Type de retour

jsonb

azure_storage.options_tsv

La fonction agit comme une fonction utilitaire appelée en tant que paramètre dans blob_get. Elle est utile pour décoder le contenu tsv.

azure_storage.options_tsv
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Arguments

delimiter

Spécifie le caractère qui sépare les colonnes dans chaque ligne du fichier. La valeur par défaut est un caractère de tabulation au format texte, une virgule au format CSV. Il doit s’agir d’un caractère codé sur un octet.

null_string

Spécifie la chaîne qui représente une valeur nulle. La valeur par défaut est \N (barre oblique inverse-N) au format texte et une chaîne vide sans guillemets au format CSV. Vous préférerez peut-être une chaîne vide même au format texte pour les cas où vous ne souhaitez pas distinguer les valeurs nulles des chaînes vides.

content_encoding

Spécifie que le fichier est codé selon la valeur de encoding_name. Si l’option est omise, le codage client actuel est utilisé.

Type de retour

jsonb

azure_storage.options_binary

La fonction agit comme une fonction utilitaire appelée en tant que paramètre dans blob_get. Elle est utile pour décoder le contenu binaire.

azure_storage.options_binary
        (content_encoding text DEFAULT NULL::text)
Returns jsonb;

Arguments

content_encoding

Spécifie que le fichier est codé selon la valeur de encoding_name. Si cette option est omise, le codage client actuel est utilisé.

Type de retour

jsonb

Autorisations 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 que citus user, 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.

Exemples

Les exemples utilisés se servent d’un exemple de compte de stockage Azure (pgquickstart) avec des fichiers personnalisés chargés pour la couverture de différents cas d’usage. Nous pouvons commencer par créer une table utilisée dans l’ensemble de l’exemple utilisé.

CREATE TABLE IF NOT EXISTS public.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 without time zone
        );

Ajout de la clé d’accès du compte de stockage (obligatoire pour le niveau d’accès = privé)

L’exemple illustre l’ajout d’une clé d’accès pour le compte de stockage afin d’obtenir l’accès pour l’interrogation à partir d’une session sur le cluster Azure Cosmos DB for Postgres.

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

Suppression de la clé d’accès du compte de stockage

L’exemple illustre la suppression de la clé d’accès pour un compte de stockage. Cette action entraînerait la suppression de l’accès aux fichiers hébergés dans le compartiment privé dans le conteneur.

SELECT azure_storage.account_remove('pgquickstart');

Ajout d’un accès pour un rôle au Stockage Blob Azure

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

Répertorier tous les rôles ayant un accès sur Stockage Blob Azure

SELECT * FROM azure_storage.account_list();

Suppression des rôles ayant un accès sur Stockage Blob Azure

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

Répertorier les objets au sein d’un conteneur public

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer');

Répertorier les objets au sein d’un conteneur private

SELECT * FROM azure_storage.blob_list('pgquickstart','privatecontainer');

Répertorier les objets avec des initiales de chaîne spécifiques dans le conteneur public

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer','e');

Autre possibilité

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer') WHERE path LIKE 'e%';

Lire le contenu d’un objet dans un conteneur

La fonction blob_get récupère un fichier à partir du stockage d’objets blob. Pour que blob_get sache comment analyser les données, vous pouvez transmettre une valeur (NULL::table_name), qui a le même format que le fichier.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv.gz'
        , NULL::events)
LIMIT 5;

Nous pouvons également définir explicitement les colonnes dans la clause FROM.

SELECT * FROM azure_storage.blob_get('pgquickstart','publiccontainer','events.csv')
AS res (
         event_id BIGINT
        ,event_type TEXT
        ,event_public BOOLEAN
        ,repo_id BIGINT
        ,payload JSONB
        ,repo JSONB
        ,user_id BIGINT
        ,org JSONB
        ,created_at TIMESTAMP WITHOUT TIME ZONE)
LIMIT 5;

Utiliser l’option decoder

L’exemple illustre l’utilisation de l’option decoder. Normalement, le format est déduit de l’extension du fichier, mais lorsque le contenu du fichier n’a pas d’extension correspondante, vous pouvez transmettre l’argument decoder.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events'
        , NULL::events
        , decoder := 'csv')
LIMIT 5;

Utiliser la compression avec l’option decoder

L’exemple montre comment appliquer la compression gzip à un fichier compressé gzip sans extension .gz standard.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events-compressed'
        , NULL::events
        , decoder := 'csv'
        , compression := 'gzip')
LIMIT 5;

Importer du contenu filtré & modifier avant le chargement à partir de l’objet de format csv

L’exemple illustre la possibilité de filtrer et de modifier le contenu importé à partir d’un objet dans un conteneur avant de le charger dans une table SQL.

SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;

Interroger le contenu à partir d’un fichier avec des en-têtes, des séparateurs personnalisés, des caractères d’échappement

Vous pouvez utiliser des séparateurs personnalisés et des caractères d’échappement en passant le résultat de azure_storage.options_copy à l’argument options.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events_pipe.csv'
        ,NULL::events
        ,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
        );

Requête d’agrégation sur le contenu d’un objet dans le conteneur

De cette façon, vous pouvez interroger des données sans les importer.

SELECT event_type,COUNT(1) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
GROUP BY event_type
ORDER BY 2 DESC
LIMIT 5;

Étapes suivantes