Exemples de démarrage rapide pour l’extension Stockage Azure dans Azure Database pour PostgreSQL

Voici une liste d’exemples pour vous aider à apprendre à utiliser l’extension Stockage Azure.

Créer un compte de stockage Azure et le remplir avec des données

Créez un compte stockage Azure. Pour créer un compte de stockage Azure, si vous n’en avez pas encore, personnalisez les valeurs de <resource_group>, <location>et <account_name><container_name>exécutez la commande Azure CLI suivante :

random_suffix=$(tr -dc 'a-z0-9' </dev/urandom | head -c8)
resource_group="resource-group-$random_suffix"
location="eastus2"
storage_account="storageaccount$random_suffix"
blob_container="container-$random_suffix"
az group create --name $resource_group --location $location
az storage account create --resource-group $resource_group --name $storage_account --location $location --sku Standard_LRS --kind BlobStorage --public-network-access enabled --access-tier hot
echo "Take note of the storage account name, which you'll have to replace in subsequent examples, whenever you find a reference to <account_name>:"
echo $storage_account
echo "Take note of the container name, which you'll have to replace in subsequent examples, whenever you find a reference to <container_name>:"
echo $blob_container

Créez un conteneur d’objets blob. Pour créer le conteneur d’objets blob, exécutez l’interface Azure CLI suivante :
```
az storage container create --account-name $storage_account --name $blob_container -o tsv
```
Récupérez l’une des deux clés d’accès affectées au compte de stockage. Veillez à copier la valeur de votre access_key lorsque vous devez le transmettre en tant qu’argument à azure_storage.account_add dans une étape suivante. Pour récupérer la première des deux clés d’accès, exécutez la commande Azure CLI suivante :
```
access_key=$(az storage account keys list --resource-group $resource_group --account-name $storage_account --query [0].value)
echo "Following is the value of your access key:"
echo $access_key
```

Téléchargez le fichier avec le jeu de données utilisé pendant les exemples et chargez-le dans votre conteneur d’objets blob. Pour télécharger le fichier avec le jeu de données, exécutez la commande Azure CLI suivante :

mkdir --parents azure_storage_examples
cd azure_storage_examples
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/storage_extension_sample.parquet
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "storage_extension_sample.parquet" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/parquet_without_extension
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "parquet_without_extension" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/storage_extension_sample.csv
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "storage_extension_sample.csv" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/csv_without_extension
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "csv_without_extension" --account-key $access_key --overwrite --output none --only-show-errors

Note

Vous pouvez lister les conteneurs ou les objets blob stockés dans ces conteneurs pour un compte de stockage spécifique, mais uniquement si votre utilisateur ou rôle PostgreSQL est autorisé à accéder à ce compte de stockage à l’aide de azure_storage.account_user_add. Les membres du azure_storage_admin rôle bénéficient de ce privilège sur tous les comptes de stockage Azure qui ont été ajoutés à l’aide de azure_storage.account_add. Par défaut, seuls les membres du azure_pg_admin rôle sont accordés azure_storage_admin .

Créer une table dans laquelle les données sont chargées

Nous allons créer la table dans laquelle nous importons le contenu des fichiers que nous avons chargés dans le compte de stockage. Pour ce faire, connectez-vous à votre instance de serveur flexible Azure Database pour PostgreSQL à l’aide de PostgreSQL pour Visual Studio Code (préversion), psql, PgAdmin ou le client de vos préférences, puis exécutez l’instruction suivante :

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Préparer l’extension pour l’utilisation

Avant de continuer, assurez-vous que vous :

Ajouter une clé d’accès au compte de stockage

Cet exemple montre comment ajouter une référence à un compte de stockage, ainsi que la clé d’accès de ce compte de stockage qui est nécessaire pour accéder à son contenu via les fonctionnalités fournies par l’extension azure_storage dans votre instance de serveur flexible Azure Database pour PostgreSQL.

<account_name> doit être défini sur le nom de votre compte de stockage. Si vous avez utilisé les scripts précédents, cette valeur doit correspondre à la valeur que vous avez définie sur la variable d’environnement storage_account dans ces scripts.

De même, <access_key> vous devez définir la valeur que vous avez extraite de votre compte de stockage.

SELECT azure_storage.account_add('<account_name>', '<access_key>');

Conseil / Astuce

Si vous souhaitez récupérer le nom du compte de stockage et l’une de ses clés d’accès à partir du portail Azure, recherchez votre compte de stockage, dans le menu de ressources, sélectionnez Clés d’accès, copiez le nom du compte de stockage et copiez la clé à partir de la section Key1 (vous devez sélectionner Afficher en regard de la clé en premier).

Accorder l’accès à un utilisateur ou à un rôle sur la référence de stockage Blob Azure

Cet exemple montre comment accorder l’accès à un utilisateur ou à un rôle nommé <regular_user>, afin que cet utilisateur PostgreSQL puisse utiliser l’extension azure_storage pour accéder aux objets blob stockés dans des conteneurs hébergés par le compte de stockage Azure référencé.

<regular_user> doit être défini sur le nom d’un utilisateur ou d’un rôle existant.

SELECT * FROM azure_storage.account_user_add('<account_name>', '<regular_user>');

Répertorier tous les objets blob dans un conteneur

Cet exemple montre comment répertorier tous les objets blob existants dans le conteneur <container_name> du compte <account_name>de stockage.

<container_name> doit être défini sur le nom de votre conteneur d’objets blob. Si vous avez utilisé les scripts précédents, cette valeur doit correspondre à la valeur que vous avez définie sur la variable d’environnement blob_container dans ces scripts.

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>');

Répertorier les objets blob avec un préfixe de nom spécifique

Cet exemple montre comment répertorier tous les objets blob existants dans le conteneur <container_name> du compte <account_name>de stockage, dont le nom d’objet blob commence par <blob_name_prefix>.

<blob_name_prefix> doit être défini sur le préfixe que vous souhaitez que les objets blob énumérés soient inclus dans leurs noms. Si vous souhaitez renvoyer tous les objets blob, vous pouvez définir ce paramètre sur une chaîne vide ou ne pas même spécifier de valeur pour ce paramètre, auquel cas la valeur est par défaut une chaîne vide.

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>','<blob_name_prefix>');

Vous pouvez également utiliser la syntaxe suivante :

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>') WHERE path LIKE '<blob_name_prefix>%';

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

L’exemple suivant montre l’importation de données à partir d’un objet blob appelé storage_extension_sample.parquet qui réside dans le conteneur <container_name> d’objets blob dans le compte <account_name>de stockage Azure, via la COPY commande :

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

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Utilisez une COPY instruction pour copier des données dans la table cible. Le format est déduit en tant que Parquet à partir de l’extension du fichier.
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.parquet';
```
Utilisez une COPY instruction pour copier des données dans la table cible. Étant donné que le format d’encodage ne peut pas être déduit de l’extension de fichier, il est explicitement spécifié via l’option FORMAT .
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/parquet_without_extension'
WITH (FORMAT 'parquet');
```
Utilisez une COPY instruction pour copier des données dans la table cible. Le format d’encodage peut être déduit à partir de l’extension de fichier. Toutefois, la présence d’en-têtes de colonne dans la première ligne doit être configurée explicitement via HEADERS l’option.
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.csv'
WITH (HEADERS);
```
Exécutez l’instruction suivante SELECT pour vérifier que les données sont chargées dans la table.
```
SELECT *
FROM sample_data
LIMIT 100;
```

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

Les exemples suivants montrent l’exportation de données à partir d’une table appelée sample_data, vers plusieurs objets blob avec différents noms et caractéristiques comme leur format d’encodage, qui résident tous dans le conteneur <container_name> d’objets blob dans le compte <account_name>stockage Azure, via la COPY commande :

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

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Chargez des données dans la table. Exécutez des instructions INSERT pour la remplir avec plusieurs lignes synthétiques ou utilisez les données d’importation à l’aide d’un exemple d’instruction COPY FROM pour la remplir avec le contenu de l’exemple de jeu de données.

Utilisez une COPY instruction pour copier des données hors de la table cible. Spécifiez que le format d’encodage doit être parquet.

COPY sample_data
TO 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample_exported.parquet'
WITH (FORMAT 'parquet');

Utilisez une COPY instruction pour copier des données hors de la table cible. Spécifiez que le format d’encodage doit être CSV et que la première ligne du fichier résultant contient des en-têtes de colonne.
```
COPY sample_data
TO 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample_exported.csv'
WITH (FORMAT 'csv', HEADERS);
```

Exécutez l’instruction suivante SELECT pour vérifier que l’objet blob existe dans le compte de stockage.

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>') WHERE path LIKE 'storage_extension_sample_exported%';

Lire du contenu à partir d’un objet blob

La blob_get fonction récupère le contenu d’un objet blob spécifique, dans le conteneur <container_name> référencé du <account_name> stockage. Pour blob_get savoir comment analyser les données, vous pouvez passer une valeur dans le formulaire NULL::table_name, où table_name fait référence à une table dont le schéma correspond à celui de l’objet blob en cours de lecture. Dans l’exemple, il fait référence à la sample_data table que nous avons créée au début.

<blob_name> doit être défini sur le chemin d’accès complet de l’objet blob dont vous souhaitez lire le contenu.

Dans ce cas, le décodeur qui doit être utilisé pour analyser l’objet blob est déduit de l’extension de .parquet fichier.

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
LIMIT 5;

Vous pouvez également définir explicitement le schéma du résultat à l’aide de la AS clause après la fonction blob_get .

SELECT * FROM azure_storage.blob_get('<account_name>','<container_name>','storage_extension_sample.parquet')
AS res (
        id BIGINT PRIMARY KEY,
        sample_text TEXT,
        sample_integer INTEGER,
        sample_timestamp TIMESTAMP)
LIMIT 5;

Lire, filtrer et modifier le contenu lu à partir d’un objet blob

Cet exemple illustre la possibilité de filtrer et de modifier le contenu importé à partir de l’objet blob, avant de le charger dans une table SQL.

SELECT concat('P-',id::text) FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
WHERE sample_integer=780
LIMIT 5;

Lire du contenu à partir d’un fichier avec des options personnalisées (en-têtes, délimiteurs de colonnes, caractères d’échappement)

Cet exemple montre comment utiliser des séparateurs personnalisés et des caractères d’échappement en passant le résultat de options_copy à l’argument options .

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.csv'
        ,NULL::sample_data
        ,options := azure_storage.options_csv_get(header := 'true')
        );

Utiliser l’option décodeur

Cet exemple illustre l’utilisation de l’option decoder . Lorsque l’option de décodage n’est pas présente, elle est déduite de l’extension du fichier. Toutefois, lorsque le nom de fichier n’a pas d’extension ou que cette extension de nom de fichier ne correspond pas à celle associée au décodeur qui doit être utilisée pour analyser correctement le contenu du fichier, vous pouvez transmettre explicitement l’argument décodeur.

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'parquet_without_extension'
        , NULL::sample_data
        , decoder := 'parquet')
LIMIT 5;

Calcul des agrégations sur le contenu d’un objet blob

Cet exemple montre comment effectuer des opérations d’agrégation sur des informations stockées dans un conteneur d’objets blob, sans avoir à importer le contenu de l’objet blob dans des tables PostgreSQL.

SELECT sample_integer, COUNT(*) FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
GROUP BY sample_integer
ORDER BY 2 DESC
LIMIT 5;

Écrire du contenu dans un objet blob

La blob_put fonction compose le contenu d’un objet blob spécifique (sample_data_copy.parquet dans ce cas) et le charge dans le conteneur <container_name> référencé du <account_name> stockage. Cet exemple utilise blob_get pour construire un ensemble de cinq lignes, qui sont ensuite passées à la blob_put fonction d’agrégation qui les charge en tant qu’objet blob nommé sample_data_copy.parquet.

Le format d’encodage est déduit en tant que parquet, en fonction de l’extension .parquetde fichier.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_data_copy.parquet'
        , top_5_sample_data)
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Le format d’encodage est déduit au format CSV, en fonction de l’extension .csvde fichier.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_data_copy.csv'
        , top_5_sample_data)
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Le format d’encodage ne peut pas être déduit, car le fichier n’a pas d’extension de fichier. Il est donc explicitement configuré en tant que parquet. En outre, l’algorithme de compression est défini sur zstd.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_parquet_data_copy_without_extension_with_zstd_compression'
        , top_5_sample_data
        ,'parquet'
        ,'zstd')
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Répertorier toutes les références aux comptes de stockage Azure

Cet exemple montre comment déterminer quels comptes de stockage Azure l’extension azure_storage peuvent référencer dans cette base de données, ainsi que le type d’authentification utilisé pour accéder à chaque compte de stockage et quels utilisateurs ou rôles sont autorisés, via la fonction azure_storage.account_user_add , pour accéder à ce compte de stockage Azure via les fonctionnalités fournies par l’extension.

SELECT * FROM azure_storage.account_list();

Révoquer l’accès d’un utilisateur ou d’un rôle sur la référence de stockage Blob Azure

Cet exemple montre comment révoquer l’accès d’un utilisateur ou d’un rôle nommé <regular_user>, afin que cet utilisateur PostgreSQL ne puisse pas utiliser l’extension azure_storage pour accéder aux objets blob stockés dans des conteneurs hébergés par le compte de stockage Azure référencé.