Estensione pg_azure_storage

SI APPLICA A: Azure Cosmos DB for PostgreSQL (con tecnologia basata su estensione di database Citus per PostgreSQL)

L'estensione pg_azure_storage consente di caricare i dati in più formati di file direttamente dall'archiviazione BLOB di Azure al cluster Azure Cosmos DB per PostgreSQL. L'abilitazione dell'estensione sblocca anche nuove funzionalità del comando COPY . I contenitori con livello di accesso "Privato" o "BLOB" richiedono l'aggiunta di una chiave di accesso privata.

È possibile creare l'estensione eseguendo:

SELECT create_extension('azure_storage');

azure_storage.account_add

La funzione consente di aggiungere l'accesso a un account di archiviazione.

azure_storage.account_add
        (account_name_p text
        ,account_key_p text);

Argomenti

account_name_p

Un account di archiviazione BLOB di Azure contiene tutti gli oggetti ABS: BLOB, file, code e tabelle. L'account di archiviazione fornisce uno spazio dei nomi univoco per abs accessibile da qualsiasi parte del mondo tramite HTTPS.

account_key_p

Le chiavi di accesso di Archiviazione BLOB di Azure (ABS) sono simili a una password radice per l'account di archiviazione. Fare sempre attenzione a proteggere le chiavi di accesso. Usare Azure Key Vault per gestire e ruotare le chiavi in modo sicuro. La chiave dell'account viene archiviata in una tabella accessibile dall'utente con privilegi avanzati postgres, azure_storage_admin e tutti i ruoli a cui sono concesse tali autorizzazioni di amministratore. Per vedere quali account di archiviazione esistono, usare la funzione account_list.

azure_storage.account_remove

La funzione consente di revocare l'accesso dell'account all'account di archiviazione.

azure_storage.account_remove
        (account_name_p text);

Argomenti

account_name_p

L'account di archiviazione BLOB di Azure contiene tutti gli oggetti ABS: BLOB, file, code e tabelle. L'account di archiviazione fornisce uno spazio dei nomi univoco per abs accessibile da qualsiasi parte del mondo tramite HTTPS.

azure_storage.account_user_add

La funzione consente di aggiungere l'accesso per un ruolo a un account di archiviazione.

azure_storage.account_user_add
        ( account_name_p text
        , user_p regrole);

Argomenti

account_name_p

Un account di archiviazione BLOB di Azure contiene tutti gli oggetti ABS: BLOB, file, code e tabelle. L'account di archiviazione fornisce uno spazio dei nomi univoco per abs accessibile da qualsiasi parte del mondo tramite HTTPS.

user_p

Ruolo creato dall'utente visibile nel cluster.

azure_storage.account_user_remove

La funzione consente di rimuovere l'accesso per un ruolo a un account di archiviazione.

azure_storage.account_user_remove
        (account_name_p text
        ,user_p regrole);

Argomenti

account_name_p

Un account di archiviazione BLOB di Azure contiene tutti gli oggetti ABS: BLOB, file, code e tabelle. L'account di archiviazione fornisce uno spazio dei nomi univoco per abs accessibile da qualsiasi parte del mondo tramite HTTPS.

user_p

Ruolo creato dall'utente visibile nel cluster.

azure_storage.account_list

La funzione elenca l'account e il ruolo che hanno accesso all'archiviazione BLOB di Azure.

azure_storage.account_list
        (OUT account_name text
        ,OUT allowed_users regrole[]
        )
Returns TABLE;

Argomenti

account_name

L'account di archiviazione BLOB di Azure contiene tutti gli oggetti ABS: BLOB, file, code e tabelle. L'account di archiviazione fornisce uno spazio dei nomi univoco per abs accessibile da qualsiasi parte del mondo tramite HTTPS.

allowed_users

Elenca gli utenti che hanno accesso all'archiviazione BLOB di Azure.

Tipo restituito

TABLE

azure_storage.blob_list

La funzione elenca i file BLOB disponibili all'interno di un contenitore utente con le relative proprietà.

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;

Argomenti

account_name

storage account name fornisce uno spazio dei nomi univoco per i dati di archiviazione di Azure accessibili da qualsiasi parte del mondo tramite HTTPS.

container_name

Un contenitore consente di organizzare un set di BLOB, in modo simile a una directory in un file system. Un account di archiviazione può contenere un numero illimitato di contenitori, ciascuno dei quali può archiviare un numero illimitato di BLOB. Un nome contenitore deve essere un nome DNS valido, perché fa parte dell'URI univoco usato per fare riferimento al contenitore o ai relativi BLOB. Seguire queste regole per la denominazione di un contenitore:

• I nomi dei contenitori devono avere una lunghezza compresa fra 3 e 63 caratteri.
• I nomi dei contenitori devono iniziare con una lettera o un numero e possono contenere solo lettere, numeri e il carattere del trattino (-).
• Due o più caratteri trattini consecutivi non sono consentiti nei nomi dei contenitori.

L'URI per un contenitore è simile al seguente: https://myaccount.blob.core.windows.net/mycontainer

prefix

Restituisce il file dal contenitore BLOB con iniziali di stringa corrispondenti.

path

Percorso completo della directory BLOB di Azure.

bytes

Dimensioni dell'oggetto file in byte.

last_modified

Data dell'ultima modifica del contenuto del file.

etag

Una proprietà ETag viene usata per la concorrenza ottimistica durante gli aggiornamenti. Non è un timestamp perché esiste un'altra proprietà denominata Timestamp che archivia l'ultima volta che un record è stato aggiornato. Ad esempio, se si carica un'entità e si vuole aggiornarla, l'ETag deve corrispondere a ciò che è attualmente archiviato. L'impostazione dell'ETag appropriato è importante perché se sono presenti più utenti che modificano lo stesso elemento, non si vogliono sovrascrivere le modifiche dell'altro.

content_type

L'oggetto BLOB rappresenta un BLOB, che è un oggetto simile a un file di dati non modificabili e non elaborati. Possono essere letti come dati di testo o binari o convertiti in un flusso leggibile in modo che i relativi metodi possano essere usati per l'elaborazione dei dati. I BLOB possono rappresentare dati che non sono necessariamente in un formato nativo JavaScript.

content_encoding

Archiviazione di Azure consente di definire la proprietà Content-Encoding in un BLOB. Per il contenuto compresso, è possibile impostare la proprietà su GZIP. Quando il browser accede al contenuto, decomprime automaticamente il contenuto.

content_hash

Questo hash viene usato per verificare l'integrità del BLOB durante il trasporto. Quando si specifica questa intestazione, il servizio di archiviazione controlla l'hash che è arrivato con quello inviato. Se i due hash non corrispondono, l'operazione non riesce con codice di errore 400 (richiesta non valida).

Tipo restituito

Record SETOF

azure_storage.blob_get

La funzione consente di caricare il contenuto di file \ file dall'interno del contenitore, con il supporto aggiunto per filtrare o modificare i dati, prima dell'importazione.

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;

È disponibile una versione di overload della funzione contenente il parametro rec che consente di definire facilmente il record di formato di output.

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;

Argomenti

Account

L'account di archiviazione fornisce uno spazio dei nomi univoco per i dati Archiviazione di Azure accessibili da qualsiasi parte del mondo tramite HTTPS.

container

Un contenitore consente di organizzare un set di BLOB, in modo simile a una directory in un file system. Un account di archiviazione può contenere un numero illimitato di contenitori, ciascuno dei quali può archiviare un numero illimitato di BLOB. Un nome contenitore deve essere un nome DNS valido, perché fa parte dell'URI univoco usato per fare riferimento al contenitore o ai relativi BLOB.

path

Nome blob esistente nel contenitore.

Rec

Definire la struttura di output del record.

decodificatore

Specificare il decodificatore di formato BLOB può essere impostato su automatico (impostazione predefinita) o su uno dei valori seguenti

Descrizione decodificatore

Formato	Descrizione
csv	Formato di valori delimitati da virgole usato da PostgreSQL COPY
tsv	Valori delimitati da tabulazioni, formato predefinito PostgreSQL COPY
binary	Formato PostgreSQL COPY binario
Testo	Un file contenente un singolo valore di testo (ad esempio, JSON o XML di grandi dimensioni)

compressione

Definisce il formato di compressione. Le opzioni disponibili sono auto, gzip & none. L'uso dell'opzione auto (impostazione predefinita), indovina la compressione in base all'estensione di file (.gz == gzip). L'opzione none forza a ignorare l'estensione e non tentare di decodificare. Mentre gzip forza l'uso del decodificatore gzip (per quando si dispone di un file con gzipped con estensione non standard). Attualmente non sono supportati altri formati di compressione per l'estensione.

opzioni

Per la gestione di intestazioni personalizzate, separatori personalizzati, caratteri di escape e così via, options funziona in modo analogo al COPY comando in PostgreSQL, il parametro usa per blob_get funzione.

Tipo restituito

SETOF Record/anyelement

azure_storage.options_csv_get

La funzione funge da funzione di utilità denominata come parametro all'interno di blob_get, utile per decodificare il contenuto 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;

Argomenti

delimiter

Specifica il carattere che separa le colonne all'interno di ogni riga (riga) del file. Il valore predefinito è un carattere di tabulazioni in formato testo, una virgola in formato CSV. Deve essere un singolo carattere a un byte.

null_string

Specifica la stringa che rappresenta un valore Null. Il valore predefinito è \N (barra rovesciata-N) in formato testo e una stringa vuota senza virgolette in formato CSV. È possibile preferire una stringa vuota anche in formato testo per i casi in cui non si vogliono distinguere i valori Null dalle stringhe vuote.

di autorizzazione

Specifica che il file contiene una riga di intestazione con i nomi di ogni colonna nel file. Nell'output la prima riga contiene i nomi delle colonne della tabella.

Offerta

Specifica il carattere di virgolette da utilizzare quando viene racchiuso tra virgolette un valore di dati. Il valore predefinito è virgolette doppie. Deve essere un singolo carattere a un byte.

escape

Specifica il carattere che deve essere visualizzato prima di un carattere di dati corrispondente al valore quote. Il valore predefinito è uguale al valore quote (in modo che il carattere tra virgolette venga raddoppiato se viene visualizzato nei dati). Deve essere un singolo carattere a un byte.

force_not_null

Non associare i valori delle colonne specificate alla stringa Null. Nel caso predefinito in cui la stringa Null è vuota, significa che i valori vuoti vengono letti come stringhe di lunghezza zero anziché null, anche quando non sono racchiusi tra virgolette.

force_null

Trova la corrispondenza con i valori delle colonne specificate rispetto alla stringa Null, anche se è stata racchiusa tra virgolette e se viene trovata una corrispondenza, imposta il valore su NULL. Nel caso predefinito in cui la stringa Null è vuota, converte una stringa vuota tra virgolette in NULL.

content_encoding

Specifica che il file è codificato nella encoding_name. Se l'opzione viene omessa, viene usata la codifica client corrente.

Tipo restituito

jsonb

azure_storage.options_copy

La funzione funge da funzione di utilità denominata come parametro all'interno di 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;

Argomenti

delimiter

Specifica il carattere che separa le colonne all'interno di ogni riga (riga) del file. Il valore predefinito è un carattere di tabulazioni in formato testo, una virgola in formato CSV. Deve essere un singolo carattere a un byte.

null_string

Specifica la stringa che rappresenta un valore Null. Il valore predefinito è \N (barra rovesciata-N) in formato testo e una stringa vuota senza virgolette in formato CSV. È possibile preferire una stringa vuota anche in formato testo per i casi in cui non si vogliono distinguere i valori Null dalle stringhe vuote.

di autorizzazione

Specifica che il file contiene una riga di intestazione con i nomi di ogni colonna nel file. Nell'output la prima riga contiene i nomi delle colonne della tabella.

Offerta

Specifica il carattere di virgolette da utilizzare quando viene racchiuso tra virgolette un valore di dati. Il valore predefinito è virgolette doppie. Deve essere un singolo carattere a un byte.

escape

Specifica il carattere che deve essere visualizzato prima di un carattere di dati corrispondente al valore quote. Il valore predefinito è uguale al valore quote (in modo che il carattere tra virgolette venga raddoppiato se viene visualizzato nei dati). Deve essere un singolo carattere a un byte.

force_quote

Forza l'uso delle virgolette per tutti i valori non NULL in ogni colonna specificata. L'output NULL non è mai racchiuso tra virgolette. Se * è specificato, i valori non NULL vengono racchiusi tra virgolette in tutte le colonne.

force_not_null

Non associare i valori delle colonne specificate alla stringa Null. Nel caso predefinito in cui la stringa Null è vuota, significa che i valori vuoti vengono letti come stringhe di lunghezza zero anziché null, anche quando non sono racchiusi tra virgolette.

force_null

Trova la corrispondenza con i valori delle colonne specificate rispetto alla stringa Null, anche se è stata racchiusa tra virgolette e se viene trovata una corrispondenza, imposta il valore su NULL. Nel caso predefinito in cui la stringa Null è vuota, converte una stringa vuota tra virgolette in NULL.

content_encoding

Specifica che il file è codificato nella encoding_name. Se l'opzione viene omessa, viene usata la codifica client corrente.

Tipo restituito

jsonb

azure_storage.options_tsv

La funzione funge da funzione di utilità denominata come parametro all'interno di blob_get. È utile per decodificare il contenuto tsv.

azure_storage.options_tsv
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Argomenti

delimiter

Specifica il carattere che separa le colonne all'interno di ogni riga (riga) del file. Il valore predefinito è un carattere di tabulazioni in formato testo, una virgola in formato CSV. Deve essere un singolo carattere a un byte.

null_string

Specifica la stringa che rappresenta un valore Null. Il valore predefinito è \N (barra rovesciata-N) in formato testo e una stringa vuota senza virgolette in formato CSV. È possibile preferire una stringa vuota anche in formato testo per i casi in cui non si vogliono distinguere i valori Null dalle stringhe vuote.

content_encoding

Specifica che il file è codificato nella encoding_name. Se l'opzione viene omessa, viene usata la codifica client corrente.

Tipo restituito

jsonb

azure_storage.options_binary

La funzione funge da funzione di utilità denominata come parametro all'interno di blob_get. È utile per decodificare il contenuto binario.

azure_storage.options_binary
        (content_encoding text DEFAULT NULL::text)
Returns jsonb;

Argomenti

content_encoding

Specifica che il file è codificato nella encoding_name. Se questa opzione viene omessa, viene utilizzata la codifica client corrente.

Tipo restituito

jsonb

Autorizzazioni Ora è possibile elencare i contenitori impostati su Livelli di accesso privato e BLOB per tale archiviazione, ma solo come citus user, che ha il azure_storage_admin ruolo concesso. Se si crea un nuovo utente denominato support, non sarà consentito accedere al contenuto del contenitore per impostazione predefinita.

Esempi

Gli esempi usati usano l'account (pgquickstart) di archiviazione di Azure di esempio con i file personalizzati caricati per l'aggiunta alla copertura di casi d'uso diversi. È possibile iniziare creando una tabella usata nel set di esempio usato.

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

Aggiunta della chiave di accesso dell'account di archiviazione (obbligatoria per il livello di accesso = privato)

L'esempio illustra l'aggiunta della chiave di accesso per l'account di archiviazione per ottenere l'accesso per l'esecuzione di query da una sessione nel cluster Azure Cosmos DB per Postgres.

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

Rimozione della chiave di accesso dell'account di archiviazione

Nell'esempio viene illustrata la rimozione della chiave di accesso per un account di archiviazione. Questa azione comporta la rimozione dell'accesso ai file ospitati nel bucket privato nel contenitore.

SELECT azure_storage.account_remove('pgquickstart');

Aggiunta dell'accesso per un ruolo all'archiviazione BLOB di Azure

SELECT * FROM azure_storage.account_user_add('pgquickstart', 'support');

Elencare tutti i ruoli con accesso nell'archivio BLOB di Azure

SELECT * FROM azure_storage.account_list();

Rimozione dei ruoli con accesso nell'archiviazione BLOB di Azure

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

Elencare gli oggetti all'interno di un public contenitore

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer');

Elencare gli oggetti all'interno di un private contenitore

SELECT * FROM azure_storage.blob_list('pgquickstart','privatecontainer');

Elencare gli oggetti con iniziali di stringa specifiche all'interno del contenitore pubblico

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer','e');

In alternativa

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer') WHERE path LIKE 'e%';

Leggere il contenuto da un oggetto in un contenitore

La blob_get funzione recupera un file dall'archivio BLOB. Per blob_get sapere come analizzare i dati, è possibile passare un valore (NULL::table_name), che ha lo stesso formato del file.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv.gz'
        , NULL::events)
LIMIT 5;

In alternativa, è possibile definire in modo esplicito le colonne nella FROM clausola .

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;

Usare l'opzione decodificatore

Nell'esempio viene illustrato l'uso dell'opzione decoder . In genere il formato viene dedotto dall'estensione del file, ma quando il contenuto del file non ha un'estensione corrispondente, è possibile passare l'argomento decodificatore.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events'
        , NULL::events
        , decoder := 'csv')
LIMIT 5;

Usare la compressione con l'opzione decodificatore

L'esempio mostra come applicare l'uso della compressione gzip in un file compresso gzip senza un'estensione .gz standard.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events-compressed'
        , NULL::events
        , decoder := 'csv'
        , compression := 'gzip')
LIMIT 5;

Importare contenuto filtrato e modificare prima del caricamento dall'oggetto formato CSV

L'esempio illustra la possibilità di filtrare e modificare il contenuto importato dall'oggetto nel contenitore prima di caricarlo in una tabella SQL.

SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;

Eseguire query sul contenuto dal file con intestazioni, separatori personalizzati, caratteri di escape

È possibile usare separatori personalizzati e caratteri di escape passando il risultato di azure_storage.options_copy all'argomento options .

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events_pipe.csv'
        ,NULL::events
        ,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
        );

Query di aggregazione sul contenuto di un oggetto nel contenitore

In questo modo è possibile eseguire query sui dati senza importarli.

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;

Passaggi successivi