Använda externa tabeller med Synapse SQL

En extern tabell pekar på data som finns i Hadoop, Azure Storage Blob eller Azure Data Lake Storage. Du kan använda externa tabeller för att läsa data från filer eller skriva data till filer i Azure Storage.

Med Synapse SQL kan du använda externa tabeller för att läsa externa data med hjälp av en dedikerad SQL-pool eller en serverlös SQL-pool.

Beroende på typen av extern datakälla kan du använda två typer av externa tabeller:

• Hadoop-externa tabeller som du kan använda för att läsa och exportera data i olika dataformat, till exempel CSV, Parquet och ORC. Hadoop-externa tabeller är tillgängliga i dedikerade SQL-pooler, men de är inte tillgängliga i serverlösa SQL-pooler.
• Interna externa tabeller som du kan använda för att läsa och exportera data i olika dataformat, till exempel CSV och Parquet. Interna externa tabeller är tillgängliga i serverlösa SQL-pooler och de är i offentlig förhandsversion i dedikerade SQL-pooler. Att skriva/exportera data med hjälp av CETAS och interna externa tabeller är endast tillgängligt i den serverlösa SQL-poolen, men inte i de dedikerade SQL-poolerna.

De viktigaste skillnaderna mellan Hadoop och interna externa tabeller:

Externa tabeller i en dedikerad SQL-pool och en serverlös SQL-pool

Du kan använda externa tabeller för att:

• Fråga Azure Blob Storage och Azure Data Lake Gen2 med Transact-SQL-instruktioner.
• Lagra frågeresultat till filer i Azure Blob Storage eller Azure Data Lake Storage med CETAS.
• Importera data från Azure Blob Storage och Azure Data Lake Storage och lagra dem i en dedikerad SQL-pool (endast Hadoop-tabeller i en dedikerad pool).

Du kan skapa externa tabeller i Synapse SQL-pooler via följande steg:

1. SKAPA EXTERN DATAKÄLLA för att referera till en extern Azure-lagring och ange de autentiseringsuppgifter som ska användas för att komma åt lagringen.
2. SKAPA EXTERNT FILFORMAT för att beskriva formatet för CSV- eller Parquet-filer.
3. SKAPA EXTERN TABELL ovanpå filerna som placeras på datakällan med samma filformat.

Eliminering av mapppartition

De interna externa tabellerna i Synapse-pooler kan ignorera de filer som placeras i mapparna som inte är relevanta för frågorna. Om dina filer lagras i en mapphierarki (till exempel - /year=2020/month=03/day=16) och värdena för year, monthoch day exponeras som kolumner, läser frågorna som innehåller filter som year=2020 filerna endast från undermapparna som placeras i year=2020 mappen. Filer och mappar som placeras i andra mappar (year=2021 eller year=2022) ignoreras i den här frågan. Den här elimineringen kallas partitionseliminering.

Elimineringen av mapppartitionen är tillgänglig i de interna externa tabeller som synkroniseras från Synapse Spark-poolerna. Om du har partitionerad datauppsättning och vill utnyttja partitionselimineringen med de externa tabeller som du skapar använder du de partitionerade vyerna i stället för de externa tabellerna.

Fileliminering

Vissa dataformat som Parquet och Delta innehåller filstatistik för varje kolumn (till exempel min/max-värden för varje kolumn). De frågor som filtrerar data läser inte filerna där de obligatoriska kolumnvärdena inte finns. Frågan utforskar först min/max-värden för kolumnerna som används i frågepredikatet för att hitta de filer som inte innehåller nödvändiga data. Dessa filer ignoreras och tas bort från frågeplanen. Den här tekniken kallas även för push-nedtryckning av filterpredikat och kan förbättra prestandan för dina frågor. Filter-pushdown är tillgängligt i serverlösa SQL-pooler i Parquet- och Delta-format. Om du vill använda filter-pushdown för strängtyperna använder du VARCHAR-typen med Latin1_General_100_BIN2_UTF8 sortering. Mer information om sortering finns i Sorteringstyper som stöds för Synapse SQL.

Säkerhet

Användaren måste ha SELECT behörighet i en extern tabell för att kunna läsa data. Externa tabeller får åtkomst till underliggande Azure Storage med hjälp av databasens begränsade autentiseringsuppgifter som definierats i datakällan med hjälp av följande regler:

• Datakälla utan autentiseringsuppgifter gör det möjligt för externa tabeller att komma åt offentligt tillgängliga filer i Azure Storage.
• Datakällan kan ha en autentiseringsuppgift som gör det möjligt för externa tabeller att endast komma åt filerna i Azure Storage med hjälp av SAS-token eller hanterad arbetsytasidentitet . Du kan till exempel läsa artikeln Utveckla lagringsfiler för lagringsåtkomstkontroll .

Exempel på SKAPA EXTERN DATAKÄLLA

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

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

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'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

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

Exempel på SKAPA EXTERNT FILFORMAT

I följande exempel skapas ett externt filformat för census-filer:

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

Exempel PÅ SKAPA EXTERN TABELL

I följande exempel skapas en extern tabell. Den returnerar den första raden:

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

Skapa och fråga externa tabeller från en fil i Azure Data Lake

Med hjälp av Data Lake-utforskningsfunktionerna i Synapse Studio kan du nu skapa och köra frågor mot en extern tabell med hjälp av Synapse SQL-poolen med ett enkelt högerklicka på filen. Gesten med ett klick för att skapa externa tabeller från ADLS Gen2-lagringskontot stöds bara för Parquet-filer.

Förutsättningar

• Du måste ha åtkomst till arbetsytan med minst åtkomstrollen Storage Blob Data Contributor till ADLS Gen2-kontot eller åtkomstkontrollistor (ACL) som gör att du kan köra frågor mot filerna.

• Du måste ha minst behörighet att skapa en extern tabell och köra frågor mot externa tabeller i Synapse SQL-poolen (dedikerad eller serverlös).

I panelen Data väljer du den fil som du vill skapa den externa tabellen från:

Ett dialogfönster öppnas. Välj dedikerad SQL-pool eller serverlös SQL-pool, ge tabellen ett namn och välj öppna skript:

SQL-skriptet genereras automatiskt och härleder schemat från filen:

Kör skriptet. Skriptet kör automatiskt select top 100 *.:

Den externa tabellen har nu skapats, för framtida utforskning av innehållet i den här externa tabellen kan användaren fråga den direkt från fönstret Data:

Nästa steg

Se CETAS-artikeln om hur du sparar frågeresultat i en extern tabell i Azure Storage. Eller så kan du börja fråga Apache Spark om externa Azure Synapse-tabeller.