Trasformare i file di dati con l'istruzione CREATE EXTERNAL TABLE AS SELECT

  • 5 minuti

Il linguaggio SQL include diverse funzionalità e funzioni che consentono di modificare i dati. È ad esempio possibile usare SQL per:

  • Filtrare righe e colonne in un set di dati.
  • Rinominare i campi dati e ed eseguire conversioni tra tipi di dati.
  • Calcolare i campi dati derivati.
  • Modificare i valori stringa.
  • Raggruppare e aggregare dati.

I pool SQL serverless di Azure Synapse possono essere usati per eseguire istruzioni SQL che trasformano i dati e salvano in modo permanente i risultati come file in un data lake per ulteriori elaborazioni o query. Se si ha familiarità con la sintassi di Transact-SQL, è possibile creare un'istruzione SELECT che applica la trasformazione specifica a cui si è interessati e archiviare i risultati dell'istruzione SELECT in un formato di file di propria scelta con uno schema di tabella di metadati su cui è possibile eseguire query usando SQL.

È possibile usare un'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS) in un pool SQL dedicato o in un pool SQL serverless per salvare in modo permanente i risultati di una query in una tabella esterna, che archivia i dati in un file nel data lake.

L'istruzione CETAS include un'istruzione SELECT che esegue query sui dati e li modifica da qualsiasi origine valida (ad esempio, una tabella o una vista esistente in un database oppure una funzione OPENROWSET che legge dati basati su file dal data lake). I risultati dell'istruzione SELECT vengono quindi salvati in modo permanente in una tabella esterna, ovvero un oggetto metadati in un database che fornisce un'astrazione relazionale sui dati archiviati nei file. Il diagramma seguente illustra visivamente il concetto:

A diagram showing a CREATE EXTERNAL TABLE AS SELECT statement saving query results as a file.

Applicando questa tecnica, è possibile usare SQL per estrarre e trasformare i dati da file o tabelle e archiviare i risultati trasformati per l'elaborazione o l'analisi downstream. Le operazioni successive sui dati trasformati possono essere eseguite sulla tabella relazionale nel database del pool SQL o direttamente sui file di dati sottostanti.

Creazione di oggetti di database esterni per supportare CETAS

Per usare le espressioni CETAS, è necessario creare i tipi seguenti di oggetto in un database per un pool SQL serverless o dedicato. Quando si usa un pool SQL serverless, creare questi oggetti in un database personalizzato (creato usando l'istruzione CREATE DATABASE), non nel database predefinito.

Origine dati esterna

Un'origine dati esterna incapsula in un data lake una connessione a una posizione del file system. È quindi possibile usare questa connessione per specificare un percorso relativo in cui vengono salvati i file di dati per la tabella esterna creata dall'istruzione CETAS.

Se i dati di origine per l'istruzione CETAS si trovano in file nello stesso percorso del data lake, è possibile usare la stessa origine dati esterna nella funzione OPENROWSET usata per eseguire una query su di essa. In alternativa, è possibile creare un'origine dati esterna separata per i file di origine o usare un percorso di file completo nella funzione OPENROWSET.

Per creare un'origine dati esterna, usare l'istruzione CREATE EXTERNAL DATA SOURCE, come illustrato in questo esempio:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

L'esempio precedente presuppone che gli utenti che eseguono query che usano l'origine dati esterna abbiano autorizzazioni sufficienti per accedere ai file. Un approccio alternativo consiste nell'incapsulare una credenziale nell'origine dati esterna in modo che possa essere usata per accedere ai dati dei file senza concedere a tutti gli utenti le autorizzazioni per leggerli direttamente:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Suggerimento

Oltre all'autenticazione di firma di accesso condiviso, è possibile definire le credenziali che usano l'identità gestita (l'identità di Microsoft Entra usata dall'area di lavoro Azure Synapse), un'entità di sicurezza Microsoft Entra specifica o un'autenticazione passthrough in base all'identità dell'utente che esegue la query, che rappresenta il tipo predefinito di autenticazione. Per altre informazioni sull'uso delle credenziali in un pool SQL serverless, vedere l'articolo Controllare l'accesso all'account di archiviazione per il pool SQL serverless in Azure Synapse Analytics nella documentazione di Azure Synapse Analytics.

Formato di file esterno

L'istruzione CETAS crea una tabella con i dati archiviati nei file. È necessario specificare il formato dei file che si vogliono creare come formato di file esterno.

Per creare un formato di file esterno, usare l'istruzione CREATE EXTERNAL FILE FORMAT, come illustrato in questo esempio:

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Suggerimento

In questo esempio i file verranno salvati in formato Parquet. È anche possibile creare formati di file esterni per altri tipi di file. Per informazioni dettagliate, vedere CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Uso dell'istruzione CETAS

Dopo aver creato un'origine dati esterna e un formato di file esterno, è possibile usare l'istruzione CETAS per trasformare i dati e archiviare i risultati in una tabella esterna.

Si supponga, ad esempio, che i dati di origine che si vogliono trasformare siano costituiti da ordini di vendita in file di testo delimitati da virgole archiviati in una cartella in un data lake. Si vogliono filtrare i dati in modo da includere solo gli ordini contrassegnati come "special order" e salvare i dati trasformati come file Parquet in una cartella diversa nello stesso data lake. È possibile usare la stessa origine dati esterna per le cartelle di origine e di destinazione, come illustrato in questo esempio:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

I parametri LOCATION e BULK dell'esempio precedente sono rispettivamente i percorsi relativi dei risultati e dei file di origine. I percorsi sono relativi alla posizione del file system a cui fa riferimento l'origine dati esterna files.

Un punto importante da comprendere è che è necessario usare un'origine dati esterna per specificare la posizione in cui devono essere salvati i dati trasformati per la tabella esterna. Quando i dati di origine basati su file vengono archiviati nella stessa gerarchia di cartelle, è possibile usare la stessa origine dati esterna. In caso contrario, è possibile usare una seconda origine dati per definire una connessione ai dati di origine o usare il percorso completo, come illustrato in questo esempio:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Rimozione delle tabelle esterne

Se la tabella esterna contenente i dati trasformati non è più necessaria, è possibile rimuoverla dal database usando l'istruzione DROP EXTERNAL TABLE, come illustrato di seguito:

DROP EXTERNAL TABLE SpecialOrders;

È tuttavia importante comprendere che le tabelle esterne sono un'astrazione dei metadati rispetto ai file che contengono i dati effettivi. La rimozione di una tabella esterna non comporta l'eliminazione dei file sottostanti.