Esempi di avvio rapido per l'estensione Archiviazione di Azure nel server flessibile Database di Azure per PostgreSQL

Gli esempi seguenti illustrano come 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 Archiviazione di Azure, se non è già disponibile, personalizzare i valori di <resource_group>, <location>, <account_name> e <container_name> ed eseguire il comando interfaccia della riga di comando di Azure seguente:

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 il comando interfaccia della riga di comando di Azure seguente:
```
az storage container create --account-name $storage_account --name $blob_container -o tsv
```
Ottenere una delle due chiavi di accesso assegnate all'account di archiviazione. Assicurarsi di copiare il valore della chiave di accesso perché è necessario passarlo come argomento a azure_storage.account_add in un passaggio successivo.

Per ottenere il primo dei due tasti di scelta, eseguire il comando interfaccia della riga di comando di Azure seguente:
```
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 interfaccia della riga di comando di Azure seguente:

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 Archiviazione di Azure aggiunti tramite 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 caricare i dati

Creare la tabella in cui si importa il contenuto dei file caricati nell'account di archiviazione. A tale scopo, connettersi all'istanza di Database di Azure per PostgreSQL server flessibile 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. Per accedere al contenuto dell'account di archiviazione, l'estensione nell'azure_storageistanza di Database di Azure per PostgreSQL server flessibile richiede la chiave di accesso.

Impostare <account_name> sul nome dell'account di archiviazione. Se sono stati usati gli script precedenti, questo valore deve corrispondere al valore impostato per la storage_account variabile di ambiente in tali script.

Impostare <access_key> sul valore recuperato dall'account di archiviazione.

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

Suggerimento

Per 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 della risorsa selezionare Chiavi di accesso. Copiare il nome dell'account di archiviazione e copiare la chiave dalla sezione key1 . È necessario selezionare Mostra accanto alla chiave.

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 l'utente PostgreSQL possa usare l'estensione azure_storage per accedere ai BLOB archiviati in contenitori ospitati dall'account di archiviazione Azure di riferimento.

Impostare <regular_user> 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 .

Impostare <container_name> sul nome del contenitore BLOB. Se sono stati usati gli script precedenti, questo valore deve corrispondere al valore impostato per la blob_container variabile di ambiente 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>.

Impostare <blob_name_prefix> sul prefisso che si desidera che i BLOB enumerati includano nei nomi. Se si desidera restituire tutti i BLOB, impostare questo parametro su una stringa vuota o non specificare 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

Nell'esempio seguente viene illustrato come importare dati da un BLOB denominato storage_extension_sample.parquet che si trova nel contenitore <container_name> BLOB nell'account <account_name>Archiviazione di Azure . L'importazione usa 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, specificarlo in modo esplicito usando 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, è necessario configurare in modo esplicito la presenza di intestazioni di colonna nella prima riga usando l'opzione HEADERS .
```
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 i dati usando un'istruzione COPY TO

Gli esempi seguenti illustrano come esportare dati da una tabella denominata sample_data a più BLOB con nomi e caratteristiche diversi, ad esempio il formato di codifica. Tutti i BLOB si trovano nel contenitore <container_name> BLOB nell'account <account_name>Archiviazione di Azure . Usare il COPY comando per esportare i dati:

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
);

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

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 analizzare blob_get i dati, 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.

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

In questo caso, il decodificatore che analizza 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 come 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 come usare l'opzione decoder . Quando non si include l'opzione decodificatore, il sistema lo deduce dall'estensione del file. Se il nome file non ha un'estensione o se l'estensione del nome file non corrisponde al decodificatore che il sistema deve 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 .csvdel 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;

Il formato di codifica non può essere dedotto perché il file non ha un'estensione di file, quindi è 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 quali Azure account di archiviazione 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. Mostra anche gli utenti o i ruoli a cui viene concessa l'autorizzazione, tramite la funzione azure_storage.account_user_add, per accedere a tale account di archiviazione 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 l'utente PostgreSQL non possa usare l'azure_storageestensione per accedere ai BLOB archiviati in contenitori ospitati dall'account di archiviazione di riferimento Azure.