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

Questa sezione illustra come creare e usare tabelle esterne native nei pool SQL Synapse. 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 SQL Synapse. Sono anche utili se si vogliono usare strumenti, come Power BI, in combinazione con il pool SQL Synapse. 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 di firma di accesso condiviso, l'identità Microsoft Entra o l'identità gestita dell'area di lavoro Synapse.

Nota

Nei pool SQL dedicati è possibile usare tabelle esterne native solo 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 o se è necessario accedere ai file CSV o ORC, usare tabelle esterne Hadoop. Le tabelle esterne native sono disponibili a livello generale nei pool SQL serverless. Per altre informazioni sulle differenze tra tabelle esterne native e Hadoop vedere 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ì (disponibilità generale)	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 viste)	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 un'identità Microsoft Entra o una chiave di firma di accesso condiviso. È 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 che risiede in un account di 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 affinché la tabella esterna possa farvi riferimento. 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 l'eliminazione delle 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 viste 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 letture, leggere, leggere parti dei file o persino leggere il file più volte. Eventuali modifiche al contenuto del file genererebbero risultati non corretti. Di conseguenza, la query del pool SQL ha esito negativo se rileva che l'ora di modifica di un file è stata modificata durante l'esecuzione della query. In alcuni scenari potrebbe essere utile creare una tabella per i file che vengono costantemente accodati. Per evitare errori di query dovuti a file costantemente accodati, è 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 di lettura ALLOW_INCONSISTENT_READS disabiliterà il controllo dell'ora 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 non corretti è minima rispetto ai file aggiornabili. Questa opzione potrebbe consentire di leggere i file accodati 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 il rischio di ottenere risultati che potrebbero non essere coerenti. Nei file accodabili è possibile ottenere risultati non corretti se si forzano più letture dei file sottostanti mediante il self-join della tabella. Nella maggior parte delle query "classiche", la tabella esterna ignorerà semplicemente alcune righe accodate durante l'esecuzione della query.

Tabella esterna Delta Lake

È possibile creare tabelle esterne in 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 sta nel fatto che nella tabella esterna Delta Lake è necessario fare riferimento a una cartella contenente la struttura Delta Lake.

Ecco 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
);

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

Tabelle Delta in cartelle partizionate

Le tabelle esterne nei pool SQL serverless non supportano il partizionamento nel 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 è osservato che in alcuni casi possono funzionare. L'uso di funzionalità non supportate come tabelle esterne in cartelle Delta partizionate può causare problemi o instabilità del pool serverless. Il supporto tecnico di Azure non sarà in grado di risolvere alcun problema se riguarda l'utilizzo delle tabelle nelle cartelle partizionate. In tal caso, verrebbe richiesto di passare alle 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 risiedere 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 di Synapse e l'account di archiviazione di Azure nella stessa area.

Passaggi successivi

Per informazioni su come archiviare i risultati di una query in un account di archiviazione, vedere l'articolo Archiviare i risultati della query nell'account di archiviazione.