Esempi di avvio rapido per l'estensione Archiviazione di Azure in Database di Azure per PostgreSQL

Di seguito è riportato un elenco di esempi che consentono di imparare a usare l'estensione Archiviazione di Azure.

Creare un account di archiviazione di Azure e popolarlo con i dati

Creare un account di archiviazione di Azure. Per creare un account di archiviazione di Azure, se non ne è già disponibile uno, personalizzare i valori di <resource_group>, <location><account_name>, e <container_name>eseguire il comando seguente dell'interfaccia della riga di comando di Azure:

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

Creare un contenitore BLOB. Per creare il contenitore BLOB, eseguire l'interfaccia della riga di comando di Azure seguente:
```
az storage container create --account-name $storage_account --name $blob_container -o tsv
```
Recuperare una delle due chiavi di accesso assegnate all'account di archiviazione. Assicurarsi di copiare il valore del access_key perché è necessario passarlo come argomento a azure_storage.account_add in un passaggio successivo. Per recuperare la prima delle due chiavi di accesso, eseguire il comando seguente dell'interfaccia della riga di comando di Azure:
```
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
```

Scaricare il file con il set di dati usato durante gli esempi e caricarlo nel contenitore BLOB. Per scaricare il file con il set di dati, eseguire il comando seguente dell'interfaccia della riga di comando di Azure:

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

Annotazioni

È possibile elencare i contenitori o i BLOB archiviati in essi per un account di archiviazione specifico, ma solo se all'utente o al ruolo PostgreSQL viene concessa l'autorizzazione per il riferimento a tale account di archiviazione usando azure_storage.account_user_add. Ai membri del azure_storage_admin ruolo viene concesso questo privilegio su tutti gli account di archiviazione di Azure aggiunti usando azure_storage.account_add. Per impostazione predefinita, al ruolo vengono concessi azure_pg_admin solo i membri di azure_storage_admin .

Creare una tabella in cui vengono caricati i dati

Verrà ora creata la tabella in cui si importa il contenuto dei file caricati nell'account di archiviazione. A tale scopo, connettersi all'istanza del server flessibile di Database di Azure per PostgreSQL usando PostgreSQL per Visual Studio Code (anteprima),psql, PgAdmin o il client delle preferenze ed eseguire l'istruzione seguente:

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

Preparare l'estensione per l'utilizzo

Prima di procedere, assicurarsi di:

Aggiungere la chiave di accesso dell'account di archiviazione

Questo esempio illustra come aggiungere un riferimento a un account di archiviazione, insieme alla chiave di accesso dell'account di archiviazione necessario per accedere al contenuto tramite la funzionalità fornita dall'estensione nell'istanza azure_storage del server flessibile di Database di Azure per PostgreSQL.

<account_name> deve essere impostato sul nome dell'account di archiviazione. Se sono stati usati gli script precedenti, questo valore deve corrispondere a qualsiasi valore impostato sulla variabile di ambiente storage_account in tali script.

Analogamente, <access_key> deve essere impostato sul valore recuperato dall'account di archiviazione.

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

Suggerimento

Se si desidera recuperare il nome dell'account di archiviazione e una delle relative chiavi di accesso dal portale di Azure, cercare l'account di archiviazione, nel menu delle risorse selezionare Chiavi di accesso, copiare il nome dell'account di archiviazione e copiare la chiave dalla sezione key1 (è necessario selezionare Mostra accanto alla chiave per prima).

Concedere l'accesso a un utente o a un ruolo nel riferimento all'archiviazione BLOB di Azure

Questo esempio illustra come concedere l'accesso a un utente o a un ruolo denominato <regular_user>, in modo che tale utente PostgreSQL possa usare l'estensione azure_storage per accedere ai BLOB archiviati nei contenitori ospitati dall'account di archiviazione di Azure di riferimento.

<regular_user> deve essere impostato sul nome di un utente o di un ruolo esistente.

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

Elencare tutti i BLOB in un contenitore

Questo esempio illustra come elencare tutti i BLOB esistenti all'interno del contenitore <container_name> dell'account <account_name>di archiviazione .

<container_name> deve essere impostato sul nome del contenitore BLOB. Se sono stati usati gli script precedenti, questo valore deve corrispondere a qualsiasi valore impostato sulla variabile di ambiente blob_container in tali script.

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

Elencare i BLOB con un prefisso di nome specifico

Questo esempio illustra come elencare tutti i BLOB esistenti all'interno del contenitore <container_name> dell'account <account_name>di archiviazione , il cui nome BLOB inizia con <blob_name_prefix>.

<blob_name_prefix> deve essere impostato su qualsiasi prefisso che si vuole che i BLOB enumerati includano nei nomi. Se si desidera restituire tutti i BLOB, è possibile impostare questo parametro su una stringa vuota o non specificare nemmeno un valore per questo parametro, nel qual caso il valore predefinito è una stringa vuota.

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

In alternativa, è possibile usare la sintassi seguente:

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

Importare dati usando un'istruzione COPY FROM

L'esempio seguente illustra l'importazione di dati da un BLOB denominato storage_extension_sample.parquet che si trova nel contenitore <container_name> BLOB nell'account <account_name>di archiviazione di Azure tramite il COPY comando :

Creare una tabella che corrisponda allo schema del file di origine:

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

Usare un'istruzione COPY per copiare i dati nella tabella di destinazione. Il formato viene dedotto come Parquet dall'estensione del file.

TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.parquet';

Usare un'istruzione COPY per copiare i dati nella tabella di destinazione. Poiché il formato di codifica non può essere dedotto dall'estensione di file, viene specificato in modo esplicito tramite l'opzione FORMAT .
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/parquet_without_extension'
WITH (FORMAT 'parquet');
```
Usare un'istruzione COPY per copiare i dati nella tabella di destinazione. Il formato di codifica può essere dedotto dall'estensione di file. Tuttavia, la presenza di intestazioni di colonna nella prima riga deve essere configurata in modo esplicito tramite HEADERS l'opzione .
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.csv'
WITH (HEADERS);
```
Eseguire l'istruzione seguente SELECT per verificare che i dati vengano caricati nella tabella.
```
SELECT *
FROM sample_data
LIMIT 100;
```

Esportare dati usando un'istruzione COPY TO

Gli esempi seguenti illustrano l'esportazione di dati da una tabella denominata sample_data, in più BLOB con nomi diversi e caratteristiche come il formato di codifica, tutti presenti nel contenitore <container_name> BLOB nell'account <account_name>di archiviazione di Azure, tramite il COPY comando :

Creare una tabella che corrisponda allo schema del file di origine:

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

Caricare i dati nella tabella. Eseguire istruzioni INSERT per popolarla con diverse righe sintetiche oppure usare l'esempio di importazione dei dati usando un'istruzione COPY FROM per popolarla con il contenuto del set di dati di esempio.

Usare un'istruzione COPY per copiare i dati dalla tabella di destinazione. Specificare che il formato di codifica deve essere parquet.

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

Usare un'istruzione COPY per copiare i dati dalla tabella di destinazione. Specificare che il formato di codifica deve essere CSV e la prima riga del file risultante contiene intestazioni di colonna.
```
COPY sample_data
TO 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample_exported.csv'
WITH (FORMAT 'csv', HEADERS);
```

Eseguire l'istruzione seguente SELECT per verificare che il BLOB esista nell'account di archiviazione.

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

Leggere il contenuto da un BLOB

La blob_get funzione recupera il contenuto di un BLOB specifico, nel contenitore <container_name> di riferimento dell'archiviazione <account_name> . Per blob_get sapere come analizzare i dati, è possibile passare un valore nel formato NULL::table_name, dove table_name fa riferimento a una tabella il cui schema corrisponde a quello del BLOB letto. Nell'esempio si riferisce alla sample_data tabella creata all'inizio.

<blob_name> deve essere impostato sul percorso completo del BLOB di cui si vuole leggere il contenuto.

In questo caso, il decodificatore che deve essere usato per analizzare il BLOB viene dedotto dall'estensione di .parquet file.

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

In alternativa, è possibile definire in modo esplicito lo schema del risultato usando la AS clausola dopo la funzione 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;

Leggere, filtrare e modificare il contenuto letto da un BLOB

Questo esempio illustra la possibilità di filtrare e modificare il contenuto importato dal BLOB, prima di caricarlo in una tabella 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;

Leggere il contenuto dal file con opzioni personalizzate (intestazioni, delimitatori di colonna, caratteri di escape)

In questo esempio viene illustrato come usare separatori personalizzati e caratteri di escape passando il risultato di options_copy all'argomento 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')
        );

Usare l'opzione decodificatore

In questo esempio viene illustrato l'uso dell'opzione decoder . Quando l'opzione decodificatore non è presente, viene dedotta dall'estensione del file. Tuttavia, quando il nome file non ha un'estensione o quando tale estensione del nome file non corrisponde a quella associata al decodificatore che deve essere usata per analizzare correttamente il contenuto del file, è possibile passare in modo esplicito l'argomento decodificatore.

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

Calcolare le aggregazioni sul contenuto di un BLOB

Questo esempio illustra come eseguire operazioni di aggregazione sulle informazioni archiviate in un contenitore BLOB, senza la necessità di importare il contenuto del BLOB nelle tabelle 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;

Scrivere contenuto in un BLOB

La blob_put funzione compone il contenuto di un BLOB specifico (sample_data_copy.parquet in questo caso) e lo carica nel contenitore <container_name> di riferimento dell'archiviazione <account_name> . Questo esempio usa blob_get per costruire un set di cinque righe, che vengono quindi passate alla blob_put funzione di aggregazione che le carica come BLOB denominato sample_data_copy.parquet.

Il formato di codifica viene dedotto come parquet, in base all'estensione .parquetdi file .

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;

Il formato di codifica viene dedotto come CSV, in base all'estensione .csvdi file .

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;

Non è possibile dedurre il formato di codifica perché il file non ha un'estensione di file, pertanto è configurato in modo esplicito come parquet. Inoltre, l'algoritmo di compressione è impostato su 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;

Elencare tutti i riferimenti agli account di archiviazione di Azure

Questo esempio illustra come individuare gli account di archiviazione di Azure a cui l'estensione azure_storage può fare riferimento in questo database, insieme al tipo di autenticazione usato per accedere a ogni account di archiviazione e quali utenti o ruoli vengono concessi, tramite la funzione azure_storage.account_user_add , per accedere a tale account di archiviazione di Azure tramite la funzionalità fornita dall'estensione.

SELECT * FROM azure_storage.account_list();

Revocare l'accesso a un utente o a un ruolo nel riferimento all'archiviazione BLOB di Azure

Questo esempio illustra come revocare l'accesso a un utente o a un ruolo denominato <regular_user>, in modo che tale utente PostgreSQL non possa usare l'estensione azure_storage per accedere ai BLOB archiviati in contenitori ospitati dall'account di archiviazione di Azure di riferimento.