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. In alcuni casi, tuttavia, potrebbe essere necessario creare un database personalizzato contenente alcuni oggetti che semplificano l'uso di dati esterni nel data lake su cui è necessario eseguire spesso query.

Creazione di un database

È possibile creare un database in un pool SQL serverless esattamente 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 supporti 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 semplifica l'importazione di dati di testo codificati UTF-8 in 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, proprio come nel database master ; ma se si prevede di eseguire query sui dati nella stessa posizione di frequente, è più efficiente definire un'origine dati esterna che fa riferimento a tale posizione. Ad esempio, il codice seguente crea un'origine dati denominata files per la cartella ipotetica https://mydatalake.blob.core.windows.net/data/files/ :

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

Uno dei vantaggi di un'origine dati esterna consiste nel fatto che è possibile semplificare una query OPENROWSET per usare la combinazione dell'origine dati e il percorso relativo alle cartelle o ai file su cui si vuole eseguire una 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 dei file a cui fa riferimento l'origine dati.

Un altro vantaggio dell'uso di un'origine dati consiste nel fatto che è possibile assegnare credenziali per l'origine dati da usare quando si accede all'archiviazione sottostante, consentendo 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 l'autenticazione nell'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 SAS, è possibile definire le credenziali che utilizzano l'identità gestita (l'identità di Microsoft Entra utilizzata dall'area di lavoro di Azure Synapse), un principale di Microsoft Entra specifico o l'autenticazione pass-through basata sull'identità dell'utente che esegue la query (che è il tipo di autenticazione predefinito). Per altre informazioni sull'uso delle credenziali in un pool SQL serverless, vedere l'articolo Controllare l'accesso dell'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

Mentre un'origine dati esterna semplifica il codice necessario per accedere ai file con la funzione OPENROWSET, è comunque necessario specificare i dettagli del formato per il file a cui si accede; che può 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 da usare, è possibile usare il formato di file per creare tabelle esterne, come illustrato di seguito.

Creazione di una tabella esterna

Quando è necessario eseguire numerose analisi o report da file nel data lake, l'uso della funzione OPENROWSET può comportare codice complesso che include origini dati e percorsi di file. Per semplificare l'accesso ai dati, è possibile incapsulare i file in una tabella esterna; che gli utenti e le applicazioni di creazione di report possono eseguire query usando un'istruzione SQL SELECT standard esattamente come 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 descritti 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 di dati e strumenti di creazione di report usando la semantica di query SQL standard.