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.
Notes
Les fonctions account_user_add,account_add, account_remove et account_user_remove nécessitent la définition d’autorisations pour chaque nœud individuel du 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
Notes
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.
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
Notes
Il existe quatre fonctions utilitaires, appelées en tant que paramètre dans blob_get qui aident à créer des valeurs pour celle-ci. Chaque fonction utilitaire est désignée pour le décodeur correspondant à son nom.
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
Notes
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');
Conseil
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é).
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');
Notes
L’ajout d’une clé d’accès est obligatoire.
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;