Creare oggetti di database esterni

Completato

È possibile usare la funzione OPENROWSET nelle query SQL eseguite nel database master predefinito del pool SQL serverless predefinito per esplorare i dati nel data lake. A volte può essere tuttavia necessario creare un database personalizzato contenente alcuni oggetti che semplificano l'uso di dati esterni nel data lake su cui è necessario eseguire query frequenti.

Creazione di un database

È possibile creare un database in un pool SQL serverless come in un'istanza di SQL Server. È possibile usare l'interfaccia grafica in Synapse Studio o un'istruzione CREATE DATABASE. Una considerazione consiste nell'impostare le regole di confronto del database in modo che supportino la conversione dei dati di testo nei file in tipi di dati Transact-SQL appropriati.

Il codice di esempio seguente crea un database denominato salesDB con regole di confronto che semplificano l'importazione di dati di testo codificati UTF-8 nelle colonne VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Creazione di un'origine dati esterna

È possibile usare la funzione OPENROWSET con un percorso BULK per eseguire query sui dati dei file dal proprio database come nel database master; ma se si prevede di eseguire query frequenti sui dati nella stessa posizione, è più efficiente definire un'origine dati esterna che faccia riferimento a tale posizione. Ad esempio, il codice seguente crea un'origine dati denominata file per l'ipotetica cartella https://mydatalake.blob.core.windows.net/data/files/:

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

Un vantaggio di un'origine dati esterna consiste nel semplificare una query OPENROWSET per usare la combinazione dell'origine dati e il percorso relativo delle cartelle o dei file sui quali eseguire le query:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

In questo esempio il parametro BULK viene usato per specificare il percorso relativo per tutti i file csv nella cartella orders, ovvero una sottocartella della cartella files a cui fa riferimento l'origine dati.

Un altro vantaggio dell'uso di un'origine dati consiste nell'assegnare all'origine dati una credenziale da usare quando si accede allo spazio di archiviazione sottostante, consentendo di fornire l'accesso ai dati tramite SQL senza consentire agli utenti di accedere ai dati direttamente nell'account di archiviazione. Ad esempio, il codice seguente crea una credenziale che usa una firma di accesso condiviso per eseguire l'autenticazione in base all'account di archiviazione di Azure sottostante che ospita il data lake.

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

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

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.

Creazione di un formato di file esterno

Anche se un'origine dati esterna semplifica il codice necessario per accedere ai file con la funzione OPENROWSET, è comunque necessario fornire i dettagli del formato per il file a cui si accede, che possono includere più impostazioni per i file di testo delimitati. È possibile incapsulare queste impostazioni in un formato di file esterno, come illustrato di seguito:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Dopo aver creato i formati di file per i file di dati specifici su cui è necessario lavorare, è possibile usare il formato di file per creare tabelle esterne, come descritto di seguito.

Creazione di una tabella esterna

Quando è necessario eseguire molte analisi o report dai file nel data lake, l'uso della funzione OPENROWSET può generare codice complesso che include origini dati e percorsi di file. Per semplificare l'accesso ai dati, è possibile incapsulare i file in una tabella esterna; gli utenti e le applicazioni di reporting possono eseguire query usando un'istruzione SQL SELECT standard proprio come per qualsiasi altra tabella di database. Per creare una tabella esterna, usare l'istruzione CREATE EXTERNAL TABLE, specificando lo schema di colonna come per una tabella standard e includendo una clausola WITH che specifica l'origine dati esterna, il percorso relativo e il formato di file esterno per i dati.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Creando un database contenente gli oggetti esterni discussi in questa unità, è possibile fornire un livello di database relazionale sui file in un data lake, semplificando l'accesso ai dati da parte di molti analisti dei dati e strumenti di creazione di report tramite la semantica delle query SQL standard.