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 credenziali di firma di accesso condiviso, identità Microsoft Entra o identità gestita dell'area di lavoro di 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à di lettura Parquet disponibile a livello generale nei pool SQL dedicati oppure è 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.

Nella tabella seguente sono elencati i formati di dati supportati:

Formato dati (tabelle esterne native)	Pool SQL serverless	Pool SQL dedicato
Parquet	Sì (GA)	Sì (anteprima pubblica)
CSV	Sì	No (in alternativa, usare tabelle esterne hadoop)
delta	Sì	No
Spark	Sì	No
Dataverse	Sì	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. Prima di creare credenziali con ambito database, il database deve avere una chiave master per proteggere le credenziali. Per altre informazioni, vedere CREATE MASTER KEY (Transact-SQL). 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 una chiave di firma di accesso condiviso o identità Microsoft Entra. È 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 poter 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 partizionate, ma non è possibile sfruttare alcune eliminazioni di partizioni. Se si desidera ottenere prestazioni migliori ignorando i file che non soddisfano alcuni criteri (ad esempio anno o 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ò riprovare a leggere, leggere parti dei file o persino leggere il file più volte. Le modifiche del contenuto del file causerebbero risultati errati. Di conseguenza, il pool SQL non riesce la query se rileva che l'ora di modifica di un file viene modificata durante l'esecuzione della query. In alcuni scenari è possibile 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 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 accodabili il contenuto esistente non viene aggiornato e vengono aggiunte solo nuove righe. Pertanto, la probabilità di risultati errati è 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 in formato di file CSV.

Nota

Come suggerisce il nome dell'opzione, l'autore della tabella accetta un rischio che i risultati potrebbero non essere 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 nella parte superiore di 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.

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 differenziali nelle cartelle partizionate

Le tabelle esterne nei pool SQL serverless non supportano il partizionamento in formato Delta Lake. Usare viste partizionate Delta anziché tabelle se sono presenti set di dati Delta Lake partizionati.

Importante

Non creare tabelle esterne in cartelle Delta Lake partizionate anche se si noterà 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 si usano tabelle in cartelle partizionate. Verrà chiesto di passare a viste partizionate Delta e riscrivere il codice in modo da 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 relativo all'archiviazione .