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.

Nella tabella seguente sono elencati i formati di dati supportati:

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.

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.

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.

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.

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.

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.