Usare tabelle esterne con Synapse SQL

  • Articolo

Una tabella esterna punta ai dati situati in Hadoop, BLOB del servizio di archiviazione di Azure o Azure Data Lake Storage. È possibile usare tabelle esterne per leggere dati da file o scrivere dati in file in Archiviazione di Azure.

Con Synapse SQL è possibile usare tabelle esterne per leggere dati esterni usando un pool SQL dedicato o un pool SQL serverless.

A seconda del tipo dell'origine dati esterna, è possibile usare due tipi di tabelle esterne:

  • Tabelle esterne Hadoop che è possibile usare per leggere ed esportare dati in diversi formati di dati, ad esempio CSV, Parquet e ORC. Le tabelle esterne Hadoop sono disponibili nei pool SQL dedicati, ma non sono disponibili nei pool SQL serverless.
  • Tabelle esterne native che è possibile usare per leggere ed esportare dati in vari formati di dati, ad esempio CSV e Parquet. Le tabelle esterne native sono disponibili nei pool SQL serverless e sono in anteprima pubblica nei pool SQL dedicati. La scrittura o l'esportazione di dati tramite CETAS e le tabelle esterne native sono disponibili solo nel pool SQL serverless, ma non nei pool SQL dedicati.

Differenze principali tra Hadoop e tabelle esterne native:

Tipo di tabella esterna Hadoop Nativo
Pool SQL dedicato Disponibili In anteprima pubblica sono disponibili solo tabelle Parquet.
Pool SQL serverless Non disponibile Disponibili
Formati supportati Delimitato/CSV, Parquet, ORC, Hive RC e RC Pool SQL serverless: delimitato/CSV, Parquet e Delta Lake
Pool SQL dedicato: Parquet (anteprima)
Eliminazione delle partizioni di cartelle No L'eliminazione delle partizioni è disponibile solo nelle tabelle partizionate create in formati Parquet o CSV sincronizzati dai pool di Apache Spark. È possibile creare tabelle esterne in cartelle partizionate Parquet, ma le colonne di partizionamento sono inaccessibili e ignorate, mentre l'eliminazione della partizione non verrà applicata. Non creare tabelle esterne nelle cartelle Delta Lake perché non sono supportate. Usare le viste partizionate Delta se è necessario eseguire query sui dati Delta Lake partizionati.
Eliminazione di file (pushdown predicato) No Sì nel pool SQL serverless. Per il pushdown della stringa, è necessario usare Latin1_General_100_BIN2_UTF8 le regole di confronto per le colonne per abilitare il VARCHAR pushdown. Per altre informazioni sulle regole di confronto, vedere Tipi di regole di confronto supportati per Synapse SQL.
Formato personalizzato per la posizione No Sì, usando caratteri jolly come /year=*/month=*/day=* per i formati Parquet o CSV. I percorsi delle cartelle personalizzate non sono disponibili in Delta Lake. Nel pool SQL serverless è anche possibile usare caratteri /logs/** jolly ricorsivi per fare riferimento a file Parquet o CSV in qualsiasi sottocartella sotto la cartella a cui si fa riferimento.
Analisi di cartelle ricorsive Sì Sì. Nei pool SQL serverless deve essere specificato /** alla fine del percorso. Nel pool dedicato le cartelle vengono sempre analizzate in modo ricorsivo.
autenticazione Archiviazione Archiviazione Access Key(SAK), passthrough microsoft Entra, identità gestita, applicazione personalizzata Microsoft Entra identity Firma di accesso condiviso, pass-through Microsoft Entra, identità gestita, applicazione personalizzata Microsoft Entra identity.
Mapping delle colonne Ordinale: le colonne nella definizione della tabella esterna vengono mappate alle colonne nei file Parquet sottostanti in base alla posizione. Pool serverless: per nome. Le colonne nella definizione della tabella esterna vengono mappate alle colonne nei file Parquet sottostanti in base alla corrispondenza dei nomi di colonna.
Pool dedicato: corrispondenza ordinale. Le colonne nella definizione della tabella esterna vengono mappate alle colonne nei file Parquet sottostanti in base alla posizione.
CETAS (esportazione/trasformazione) Sì CETAS con le tabelle native come destinazione funziona solo nel pool SQL serverless. Non è possibile usare i pool SQL dedicati per esportare i dati usando tabelle native.

Nota

Le tabelle esterne native sono la soluzione consigliata nei pool in cui sono disponibili a livello generale. Se è necessario accedere a dati esterni, usare sempre le tabelle native nei pool serverless. Nei pool dedicati è necessario passare alle tabelle native per leggere i file Parquet una volta che sono disponibili a livello generale. Usare le tabelle Hadoop solo se è necessario accedere ad alcuni tipi non supportati nelle tabelle esterne native (ad esempio ORC, RC) o se la versione nativa non è disponibile.

Tabelle esterne nel pool SQL dedicato e nel pool SQL serverless

È possibile usare tabelle esterne per:

  • Eseguire query su Archiviazione BLOB di Azure e Azure Data Lake Gen2 con istruzioni Transact-SQL.
  • Archiviare i risultati delle query in file in Archiviazione BLOB di Azure o azure Data Lake Archiviazione usando CETAS.
  • Importare dati da Archiviazione BLOB di Azure e Azure Data Lake Archiviazione e archiviarlo in un pool SQL dedicato (solo tabelle Hadoop nel pool dedicato).

Nota

Se usato insieme all'istruzione CREATE TABLE AS edizione Standard LECT, la selezione da una tabella esterna importa dati in una tabella all'interno del pool SQL dedicato.

Se le prestazioni delle tabelle esterne Hadoop nei pool dedicati non soddisfano gli obiettivi di prestazioni, è consigliabile caricare dati esterni nelle tabelle Datawarehouse usando l'istruzione COPY.

Per un'esercitazione sul caricamento, vedere Usare PolyBase per caricare dati da Archiviazione BLOB di Azure.

È possibile creare tabelle esterne nei pool Synapse SQL seguendo questa procedura:

  1. CREATE EXTERNAL DATA SOURCE per fare riferimento a un'archiviazione di Azure esterna e specificare le credenziali da usare per accedere all'archiviazione.
  2. CREATE EXTERNAL FILE FORMAT per descrivere il formato dei file CSV o Parquet.
  3. CREATE EXTERNAL TABLE sopra i file inseriti nell'origine dati con lo stesso formato di file.

Eliminazione delle partizioni di cartelle

Le tabelle esterne native nei pool Synapse possono ignorare i file inseriti nelle cartelle non pertinenti per le query. Se i file vengono archiviati in una gerarchia di cartelle (ad esempio - /year=2020/month=03/day=16) e i valori per year, monthe day vengono esposti come colonne, le query che contengono filtri come year=2020 leggeranno i file solo dalle sottocartelle inserite all'interno della year=2020 cartella. I file e le cartelle inseriti in altre cartelle (year=2021 o year=2022) verranno ignorati in questa query. Questa eliminazione è nota come eliminazione della partizione.

L'eliminazione della partizione di cartella è disponibile nelle tabelle esterne native sincronizzate dai pool di Spark di Synapse. Se è stato partizionato un set di dati e si vuole sfruttare l'eliminazione della partizione con le tabelle esterne create, usare le viste partizionate anziché le tabelle esterne.

Eliminazione dei file

Alcuni formati di dati, ad esempio Parquet e Delta, contengono statistiche di file per ogni colonna( ad esempio, valori min/max per ogni colonna). Le query che filtrano i dati non leggeranno i file in cui i valori di colonna richiesti non esistono. La query esplorerà innanzitutto i valori min/max per le colonne usate nel predicato di query per trovare i file che non contengono i dati necessari. Questi file verranno ignorati ed eliminati dal piano di query. Questa tecnica è nota anche come pushdown del predicato di filtro e può migliorare le prestazioni delle query. Il pushdown dei filtri è disponibile nei pool SQL serverless nei formati Parquet e Delta. Per sfruttare il pushdown del filtro per i tipi stringa, usare il tipo VARCHAR con le Latin1_General_100_BIN2_UTF8 regole di confronto. Per altre informazioni sulle regole di confronto, vedere Tipi di regole di confronto supportati per Synapse SQL.

Sicurezza

L'utente deve disporre SELECT dell'autorizzazione per una tabella esterna per leggere i dati. Le tabelle esterne accedono all'archiviazione di Azure sottostante usando le credenziali con ambito database definite nell'origine dati usando le regole seguenti:

  • L'origine dati senza credenziali consente alle tabelle esterne di accedere ai file disponibili pubblicamente nell'archiviazione di Azure.
  • L'origine dati può avere credenziali che consentono alle tabelle esterne di accedere solo ai file nell'archiviazione di Azure usando il token di firma di accesso condiviso o l'identità gestita dell'area di lavoro. Per esempi, vedere l'articolo Sviluppare il controllo di accesso alle risorse di archiviazione dei file di archiviazione.

Esempio di CREATE EXTERNAL DATA SOURCE

L'esempio seguente crea un'origine dati esterna Hadoop nel pool SQL dedicato per Azure Data Lake Gen2 che punta al set di dati di New York:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

L'esempio seguente crea un'origine dati esterna per Azure Data Lake Gen2 che punta al set di dati New York disponibile pubblicamente:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Esempio di CREATE EXTERNAL FILE FORMAT

L'esempio seguente crea un formato di file esterno per i dati di censimento:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Esempio di CREATE EXTERNAL TABLE

L'esempio seguente crea una tabella esterna. Restituisce la prima riga:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Creare ed eseguire query su tabelle esterne da un file in Azure Data Lake

Usando le funzionalità di esplorazione di Data Lake di Synapse Studio è ora possibile creare ed eseguire query su una tabella esterna usando il pool Synapse SQL con un semplice clic con il pulsante destro del mouse sul file. Il movimento con un clic per creare tabelle esterne dall'account di archiviazione ADLS Gen2 è supportato solo per i file Parquet.

Prerequisiti

  • È necessario avere accesso all'area di lavoro con almeno il Storage Blob Data Contributor ruolo di accesso all'account ADLS Gen2 o Controllo di accesso Elenchi (ACL) che consentono di eseguire query sui file.

  • È necessario disporre almeno delle autorizzazioni per creare una tabella esterna ed eseguire query su tabelle esterne nel pool SYNapse SQL (dedicato o serverless).

Nel pannello Dati selezionare il file da cui creare la tabella esterna:

externaltable1

Verrà visualizzata una finestra di dialogo. Selezionare il pool SQL dedicato o il pool SQL serverless, assegnare un nome alla tabella e selezionare Apri script:

externaltable2

Lo script SQL viene generato automaticamente deducendo lo schema dal file:

externaltable3

Eseguire lo script. Lo script eseguirà automaticamente SELECT TOP 100*:

externaltable4

La tabella esterna è stata creata. Per l'esplorazione futura del contenuto di questa tabella esterna, l'utente può eseguire una query direttamente dal riquadro Dati:

externaltable5

Passaggi successivi

Vedere l'articolo CETAS per informazioni su come salvare i risultati delle query in una tabella esterna in Archiviazione di Azure. In alternativa, è possibile eseguire query sulle tabelle esterne di Apache Spark per Azure Synapse.