Creare e usare tabelle esterne native usando pool SQL in Azure Synapse Analytics

In questa sezione si apprenderà come creare e usare tabelle esterne native nei pool Synapse SQL. Le tabelle esterne native offrono prestazioni migliori rispetto alle tabelle esterne con TYPE=HADOOP nella definizione dell'origine dati esterna. Ciò è dovuto al fatto che le tabelle esterne native usano codice nativo per accedere ai dati esterni.

Le tabelle esterne sono utili quando si vuole controllare l'accesso ai dati esterni nel pool Synapse SQL. Le tabelle esterne sono utili anche se si vogliono usare strumenti, ad esempio Power BI, insieme al pool Synapse SQL. Le tabelle esterne possono accedere a due tipi di archiviazione:

  • Archiviazione pubblica, che consente agli utenti di accedere ai file archiviati pubblicamente.
  • Archiviazione protetta, in cui gli utenti accedono ai file di archiviazione usando le credenziali SAS, l'identità Azure AD o l'identità gestita dell'area di lavoro Synapse.

Nota

Nei pool SQL dedicati è possibile usare solo tabelle esterne native con un tipo di file Parquet e questa funzionalità è disponibile in anteprima pubblica. Se si vuole usare la funzionalità lettore Parquet disponibile a livello generale nei pool SQL dedicati o è necessario accedere ai file CSV o ORC, usare tabelle esterne Hadoop. Le tabelle esterne native sono disponibili a livello generale nei pool SQL serverless. Altre informazioni sulle differenze tra tabelle esterne native e Hadoop in Usare tabelle esterne con Synapse SQL.

La tabella seguente elenca i formati di dati supportati:

Formato dati (tabelle esterne native) Pool SQL serverless Pool SQL dedicato
Parquet Sì (GA) Sì (anteprima pubblica)
CSV No (in alternativa, usare tabelle esterne Hadoop)
delta No
Spark No
Dataverse No
Formati di dati di Azure Cosmos DB (JSON, BSON e così via) No (in alternativa, creare visualizzazioni) No

Prerequisiti

Il primo passaggio consiste nel creare un database in cui verranno create le tabelle. Creare quindi gli oggetti seguenti usati in questo esempio:

  • CREDENZIALI IN AMBITO DATABASE sqlondemand che consentono l'accesso all'account di archiviazione di Azure https://sqlondemandstorage.blob.core.windows.net protetto da SAS.

    CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
    WITH IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
    
  • ORIGINE DATI ESTERNA sqlondemanddemo che fa riferimento all'account di archiviazione demo protetto con chiave SAS e ORIGINE DATI ESTERNA nyctlc che fa riferimento all'account di archiviazione di Azure disponibile pubblicamente in https://azureopendatastorage.blob.core.windows.net/nyctlc/.

    CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
        LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
        CREDENTIAL = sqlondemand
    );
    GO
    CREATE EXTERNAL DATA SOURCE nyctlc
    WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
    GO
    CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
    WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );
    
  • Formati di file QuotedCSVWithHeaderFormat e ParquetFormat che descrivono i tipi di file CSV e Parquet.

    CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
    WITH (  
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
    );
    GO
    CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
    GO
    CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
    GO
    

Le query in questo articolo vengono eseguite nel database di esempio e usano tali oggetti.

Tabella esterna in un file

È possibile creare tabelle esterne che accedono ai dati in un account di archiviazione di Azure che consente l'accesso agli utenti con identità Azure AD o chiave SAS. È possibile creare tabelle esterne nello stesso modo in cui si creano le normali tabelle esterne di SQL Server.

La query seguente crea una tabella esterna che legge il file population.csv dall'account di archiviazione di Azure SynapseSQL demo a cui viene fatto riferimento tramite l'origine dati sqlondemanddemo e che viene protetto con credenziali in ambito database denominate sqlondemand.

Le credenziali dell'origine dati e in ambito database vengono create nello script di installazione.

Nota

Cambiare la prima riga della query, ossia [mydbname], in modo da usare il database creato.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Le tabelle CSV native sono attualmente disponibili solo nei pool SQL serverless.

Tabella esterna in un set di file

È possibile creare tabelle esterne che leggono i dati da un set di file inseriti nell'archiviazione di Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

È possibile specificare il modello che i file devono soddisfare per fare riferimento alla tabella esterna. Il modello è obbligatorio solo per le tabelle Parquet e CSV. Se si usa il formato Delta Lake, è necessario specificare solo una cartella radice e la tabella esterna troverà automaticamente il modello.

Nota

La tabella viene creata nella struttura di cartelle partizionata, ma non è possibile sfruttare alcune eliminazioni di partizione. Se si desidera ottenere prestazioni migliori ignorando i file che non soddisfano un criterio (ad esempio un anno o un mese specifico in questo caso), usare le visualizzazioni sui dati esterni.

Tabella esterna su file accodabili

I file a cui fa riferimento una tabella esterna non devono essere modificati durante l'esecuzione della query. Nella query a esecuzione prolungata il pool SQL può ripetere le operazioni di lettura, leggere parti dei file o persino leggere il file più volte. Le modifiche del contenuto del file causano risultati errati. Pertanto, il pool SQL non riesce la query se rileva che l'ora di modifica di qualsiasi file viene modificata durante l'esecuzione della query. In alcuni scenari potrebbe essere necessario creare una tabella nei file che vengono costantemente aggiunti. Per evitare errori di query dovuti a file aggiunti costantemente, è possibile specificare che la tabella esterna deve ignorare letture potenzialmente incoerenti usando l'impostazione TABLE_OPTIONS .

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

L'opzione ALLOW_INCONSISTENT_READS di lettura disabiliterà il controllo del tempo di modifica dei file durante il ciclo di vita della query e leggerà qualsiasi elemento disponibile nei file a cui fa riferimento la tabella esterna. Nei file accodati il contenuto esistente non viene aggiornato e vengono aggiunte solo nuove righe. Pertanto, la probabilità di risultati errati viene ridotta a icona rispetto ai file aggiornabili. Questa opzione potrebbe consentire di leggere i file aggiunti di frequente senza gestire gli errori.

Questa opzione è disponibile solo nelle tabelle esterne create nel formato di file CSV.

Nota

Come suggerisce il nome dell'opzione, l'autore della tabella accetta un rischio che i risultati non siano coerenti. Nei file accodabili è possibile ottenere risultati non corretti se si forzano più letture dei file sottostanti aggiungendo automaticamente la tabella. Nella maggior parte delle query "classiche", la tabella esterna ignorerà solo alcune righe aggiunte durante l'esecuzione della query.

Tabella esterna Delta Lake

È possibile creare tabelle esterne sopra una cartella Delta Lake. L'unica differenza tra le tabelle esterne create in un singolo file o un set di file e le tabelle esterne create in un formato Delta Lake è che nella tabella esterna Delta Lake è necessario fare riferimento a una cartella contenente la struttura Delta Lake.

Cartella ECDC COVID-19 Delta Lake

Un esempio di definizione di tabella creata in una cartella Delta Lake è:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Impossibile creare tabelle esterne in una cartella partizionata. Esaminare gli altri problemi noti nella pagina self-help del pool SQL serverless di Synapse.

Tabelle delta nelle cartelle partizionate

Le tabelle esterne nei pool SQL serverless non supportano il partizionamento in formato Delta Lake. Se sono stati partizionati set di dati Delta Lake, usare viste partizionate anziché tabelle.

Importante

Non creare tabelle esterne nelle cartelle Delta Lake partizionate, anche se si nota che potrebbero funzionare in alcuni casi. L'uso di funzionalità non supportate come tabelle esterne in cartelle delta partizionate può causare problemi o instabilità del pool serverless. supporto tecnico di Azure non sarà in grado di risolvere alcun problema se usa tabelle in cartelle partizionate. Verrà chiesto di passare a viste partizionate Delta e riscrivere il codice per usare solo la funzionalità supportata prima di procedere con la risoluzione dei problemi.

Usare una tabella esterna

È possibile usare tabelle esterne nelle query nello stesso modo in cui si usano nelle query di SQL Server.

Nella query seguente viene illustrato l'uso della tabella esterna population creata nella sezione precedente. La query restituisce i nomi dei paesi/aree geografiche con la popolazione del 2019 in ordine decrescente.

Nota

Cambiare la prima riga della query, ossia [mydbname], in modo da usare il database creato.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Le prestazioni di questa query possono variare a seconda dell'area. L'area di lavoro potrebbe non essere inserita nella stessa area degli account di archiviazione di Azure usati in questi esempi. Per i carichi di lavoro di produzione, posizionare l'area di lavoro Synapse e l'archiviazione di Azure nella stessa area.

Passaggi successivi

Per informazioni su come archiviare i risultati di una query nell'archiviazione, vedere Archiviare i risultati delle query nell'articolo archiviazione .