Extension Stockage Azure dans Azure Database pour PostgreSQL – Serveur flexible

Article
04/17/2024

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur flexible

Un cas d’usage courant pour les clients Microsoft est la possibilité d’importer et d’exporter des données entre Stockage Blob Azure et une instance de serveur flexible Azure Database pour PostgreSQL. L’extension Stockage Azure (azure_storage) dans le serveur flexible Azure Database pour PostgreSQL simplifie cette situation.

Stockage Blob Azure

Le Stockage Blob Azure est la solution de stockage d’objets pour le cloud. Stockage Blob est optimisé pour stocker des quantités massives de données non structurées. Les données non structurées sont des données qui n’obéissent pas à un modèle ou une définition de données en particulier, comme des données texte ou binaires.

Stockage Blob offre une hiérarchie de trois types de ressources :

Le compte de stockage est une entité administrative qui contient des services pour des éléments tels que des objets blob, des fichiers, des files d’attente, des tables ou des disques.

Lorsque vous créez un compte de stockage dans Azure, vous obtenez un espace de noms unique pour vos ressources de stockage. Cet espace de noms unique fait partie de l’URL. Le nom du compte de stockage doit être unique parmi tous les noms de compte de stockage existants dans Azure.
Un conteneur se trouve à l’intérieur d’un compte de stockage. Un conteneur est comme un dossier dans lequel les objets blob sont stockés.

Vous pouvez définir des stratégies de sécurité et affecter des stratégies au conteneur. Ces stratégies se déclenchent en cascade sur tous les objets blob du conteneur.

Un compte de stockage peut contenir un nombre illimité de conteneurs. Chaque conteneur peut avoir un nombre illimité d’objets blob, dans la limite de 500 To, taille maximale du compte de stockage.

Après avoir placé un objet blob dans un conteneur situé à l’intérieur d’un compte de stockage, vous pouvez faire référence à l’objet blob à l’aide d’une URL au format suivant : protocol://<storage_account_name>/blob.core.windows.net/<container_name>/<blob_name>.
Un objet blob est un élément de données qui réside dans le conteneur.

Le diagramme suivant montre la relation entre ces ressources.

Diagramme d’un exemple de ressources de stockage.

Principaux avantages du stockage des données en tant qu’objets blob dans Stockage Blob Azure

Le Stockage Blob Azure offre les avantages suivants :

Il s’agit d’une solution de stockage cloud évolutive et économique. Vous pouvez l’utiliser pour stocker des données de n’importe quelle taille et effectuer un scale-up ou un scale-down en fonction de vos besoins.
Cela fournit des couches de sécurité pour protéger vos données, telles que le chiffrement au repos et en transit.
Il communique avec d’autres services Azure et applications partenaires. Il s’agit d’une solution polyvalente répondant à un large éventail de cas d’usage, tels que la sauvegarde et la récupération d’urgence, l’archivage et l’analyse des données.
C’est une solution économique pour gérer et stocker de grandes quantités de données dans le cloud, que l’organisation soit une petite ou une grande entreprise. Vous payez uniquement pour le stockage dont vous avez besoin.

Importer des données du service Stockage Blob Azure vers Azure Database pour PostgreSQL - Serveur flexible

Pour charger des données à partir du Stockage Blob Azure, vous devez ajouter l’extension PostgreSQL azure_storage à la liste verte. Vous installez ensuite l’extension dans la base de données à l’aide de la commande CREATE EXTENSION :

 CREATE EXTENSION azure_storage;

Quand vous créez un compte de stockage, Azure génère 2 clés d’accès au compte de stockage de 512 bits pour ce compte. Vous pouvez utiliser ces clés pour autoriser l’accès aux données de votre compte de stockage par le biais de l’autorisation de clé partagée.

Avant de pouvoir importer les données, vous devez mapper le compte de stockage à l’aide de la méthode account_add. Indiquez la clé d’accès du compte qui a été définie lors de la création du compte. L’exemple de code suivant mappe le compte de stockage mystorageaccount et utilise la chaîne SECRET_ACCESS_KEY comme paramètre de clé d’accès :

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

Après avoir mappé le stockage, vous pouvez répertorier le contenu du compte de stockage et choisir les données à importer. L’exemple suivant suppose que vous avez créé un compte de stockage nommé mystorageaccount et un conteneur d’objets blob nommé mytestblob :

SELECT path, bytes, pg_size_pretty(bytes), content_type
FROM azure_storage.blob_list('mystorageaccount','mytestblob');

Vous pouvez filtrer la sortie de cette instruction à l’aide d’une clause SQL WHERE régulière ou du paramètre prefix de la méthode blob_list. La description du contenu du conteneur nécessite soit un compte et une clé d’accès, soit un conteneur avec un accès anonyme activé.

Enfin, vous pouvez utiliser l’instruction COPY ou la fonction blob_get pour importer des données du service Stockage Blob Azure vers une table Azure Database pour PostgreSQL – Serveur flexible existante.

Importer des données à l’aide d’une instruction COPY

L’exemple suivant montre l’importation de données à partir d’un fichier employee.csv qui réside dans le conteneur d’objets blob mytestblob dans le même compte de stockage Azure mystorageaccount via la commande COPY :

Créez une table cible qui correspond au schéma du fichier source :

CREATE TABLE employees (
  EmployeeId int PRIMARY KEY,
  LastName VARCHAR ( 50 ) UNIQUE NOT NULL,
  FirstName VARCHAR ( 50 ) NOT NULL
);

Utilisez une instruction COPY pour copier des données dans la table cible. Indiquez que la première ligne est des en-têtes.
```
COPY employees
FROM 'https://mystorageaccount.blob.core.windows.net/mytestblob/employee.csv'
WITH (FORMAT 'csv', header);
```

Importer des données à l’aide de la fonction blob_get

La fonction blob_get récupère un fichier à partir de Stockage Blob. Pour vérifier que blob_get peut analyser les données, vous pouvez passer une valeur avec un type qui correspond aux colonnes du fichier ou définissez explicitement les colonnes dans la clause FROM.

Vous pouvez utiliser la fonction blob_get au format suivant :

azure_storage.blob_get(account_name, container_name, path)

L’exemple suivant montre la même action de la même source vers la même cible à l’aide de la fonction blob_get :

INSERT INTO employees 
SELECT * FROM azure_storage.blob_get('mystorageaccount','mytestblob','employee.csv',options:= azure_storage.options_csv_get(header=>true)) AS res (
  CustomerId int,
  LastName varchar(50),
  FirstName varchar(50))

La commande COPY et la fonction blob_get prennent en charge les extensions de fichiers suivantes pour l’importation :

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

Exporter des données d’Azure Database pour PostgreSQL - Serveur flexible vers le service Stockage Blob Azure

Pour exporter des données d’un serveur flexible Azure Database pour PostgreSQL vers stockage Blob Azure, vous devez ajouter l’extension azure_storage à la liste verte. Vous installez ensuite l’extension PostgreSQL azure_storage dans la base de données à l’aide de la commande CREATE EXTENSION :

CREATE EXTENSION azure_storage;

Quand vous créez un compte de stockage, Azure génère 2 clés d’accès au compte de stockage de 512 bits pour ce compte. Vous pouvez utiliser ces clés pour autoriser l’accès aux données de votre compte de stockage via l’autorisation de clé partagée ou via des jetons SAP (signature d’accès partagé) avec la clé partagée.

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

Vous pouvez utiliser l’instruction COPY ou la fonction blob_put pour exporter des données d’une table Azure Database pour PostgreSQL vers le service Stockage Blob Azure. L’exemple suivant montre l’exportation de données d’une table d’employé vers un nouveau fichier nommé employee2.csv via la commande COPY. Le fichier réside dans le conteneur d’objets blob mytestblob dans le même compte de stockage Azure mystorageaccount.

COPY employees 
TO 'https://mystorageaccount.blob.core.windows.net/mytestblob/employee2.csv'
WITH (FORMAT 'csv');

De même, vous pouvez exporter des données à partir d’une table d’employé via la fonction blob_put, ce qui vous donne un contrôle encore plus fini sur les données exportées. L’exemple suivant exporte uniquement deux colonnes de la table, EmployeeId et LastName. Il ignore la colonne FirstName.

SELECT azure_storage.blob_put('mystorageaccount', 'mytestblob', 'employee2.csv', res) FROM (SELECT EmployeeId,LastName FROM employees) res;

La commande COPY et la fonction blob_put prennent en charge les extensions de fichiers suivantes pour l’exportation :

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

Répertorier les objets dans Stockage Azure

Pour répertorier les objets dans Stockage Blob Azure, vous devez ajouter l’extension azure_storage à la liste verte. Vous installez ensuite l’extension PostgreSQL azure_storage dans la base de données à l’aide de la commande CREATE EXTENSION :

CREATE EXTENSION azure_storage;

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

L’extension Stockage Azure fournit une méthode blob_list. Vous pouvez utiliser cette méthode pour répertorier les objets dans le stockage Blob au format suivant :

azure_storage.blob_list(account_name, container_name, prefix)

L’exemple suivant montre la liste des objets dans Stockage Azure à l’aide de la méthode blob_list à partir d’un compte de stockage nommé mystorageaccount et d’un conteneur d’objets blob appelé mytestbob. Les fichiers du conteneur présentent la chaîne employee.

SELECT path, size, last_modified, etag FROM azure_storage.blob_list('mystorageaccount','mytestblob','employee');

Attribuer des autorisations à un compte non administrateur pour accéder aux données à partir du Stockage Azure

Par défaut, seul le rôle administratif azure_pg_admin peut ajouter une clé de compte et accéder au compte de stockage dans Azure Database pour PostgreSQL – Serveur flexible.

Vous pouvez octroyer les autorisations d’accès aux données dans Stockage Azure aux utilisateurs non-administratifs d’Azure Database pour PostgreSQL – Serveur flexible de deux façons, en fonction de la granularité des autorisations :

Affectez azure_storage_admin à l’utilisateur non administrateur. Ce rôle est ajouté à l’installation de l’extension Stockage Azure. L’exemple suivant accorde ce rôle à un utilisateur non administrateur appelé support :
```
-- Allow adding/list/removing storage accounts
GRANT azure_storage_admin TO support;
```
Appelez la fonction account_user_add. L’exemple suivant ajoute des autorisations au rôle support dans le serveur flexible Azure Database pour PostgreSQL. Il s’agit d’une autorisation plus limitée, car elle permet à l’utilisateur d’accéder uniquement à un compte de stockage Azure nommé mystorageaccount.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```

Les utilisateurs administratifs du serveur flexible Azure Database pour PostgreSQL peuvent obtenir la liste des comptes de stockage et des autorisations dans la sortie de la fonction account_list. Cette fonction affiche tous les comptes avec des clés d’accès définies.

SELECT * FROM azure_storage.account_list();

Quand l’administrateur d’Azure Database pour PostgreSQL – Serveur flexible décide que l’utilisateur ne doit plus y avoir accès, l’administrateur peut utiliser la méthode ou fonction account_user_remove pour supprimer cet accès. L’exemple suivant annule l’accès au compte de stockage mystorageaccount du rôle support :

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

Étapes suivantes

Si l’extension que vous souhaitez utiliser n’apparaît pas, faites-le-nous savoir. Votez pour les demandes existantes ou créez de nouvelles demandes dans notre Forum de commentaires.