Skapa och använda interna externa tabeller med hjälp av SQL-pooler i Azure Synapse Analytics

I det här avsnittet får du lära dig hur du skapar och använder interna externa tabeller i Synapse SQL-pooler. Interna externa tabeller har bättre prestanda jämfört med externa tabeller med TYPE=HADOOP i definitionen för den externa datakällan. Det beror på att interna externa tabeller använder intern kod för att komma åt externa data.

Externa tabeller är användbara när du vill styra åtkomsten till externa data i Synapse SQL-poolen. Externa tabeller är också användbara om du vill använda verktyg, till exempel Power BI, tillsammans med Synapse SQL-pool. Externa tabeller har åtkomst till två typer av lagring:

• Offentlig lagring där användare har åtkomst till offentliga lagringsfiler.
• Skyddad lagring där användare får åtkomst till lagringsfiler med hjälp av SAS-autentiseringsuppgifter, Microsoft Entra-identitet eller hanterad identitet för Synapse-arbetsytan.

Kommentar

I dedikerade SQL-pooler kan du bara använda interna externa tabeller med en Parquet-filtyp, och den här funktionen är i offentlig förhandsversion. Om du vill använda allmänt tillgängliga Parquet-läsarfunktioner i dedikerade SQL-pooler, eller om du behöver komma åt CSV- eller ORC-filer, använder du hadoop-externa tabeller. Interna externa tabeller är allmänt tillgängliga i serverlösa SQL-pooler. Läs mer om skillnaderna mellan interna och Hadoop-externa tabeller i Använda externa tabeller med Synapse SQL.

I följande tabell visas de dataformat som stöds:

Dataformat (interna externa tabeller)	Serverlös SQL-pool	Dedikerad SQL-pool
Parquet	Ja (GA)	Ja (offentlig förhandsversion)
CSV	Ja	Nej (du kan också använda externa Hadoop-tabeller)
delta	Ja	Nej
Spark	Ja	Nej
Dataverse	Ja	Nej
Azure Cosmos DB-dataformat (JSON, BSON osv.)	Nej (du kan också skapa vyer)	Inga

Förutsättningar

Det första steget är att skapa en databas där tabellerna skapas. Innan du skapar en databasomfångsbegränsad autentiseringsuppgift måste databasen ha en huvudnyckel för att skydda autentiseringsuppgifterna. Mer information om detta finns i CREATE MASTER KEY (Transact-SQL). Skapa sedan följande objekt som används i det här exemplet:

• DATABASE SCOPED CREDENTIAL sqlondemand som ger åtkomst till SAS-skyddat https://sqlondemandstorage.blob.core.windows.net Azure Storage-konto.

  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'
  

• EXTERN DATAKÄLLA sqlondemanddemo som refererar till demolagringskonto som skyddas med SAS-nyckel och EXTERN DATAKÄLLA nyctlc som refererar till offentligt tillgängligt Azure Storage-konto på plats 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/' );
  

• Filformat QuotedCSVWithHeaderFormat och ParquetFormat som beskriver CSV- och parquet-filtyper.

  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
  

Frågorna i den här artikeln körs på exempeldatabasen och använder dessa objekt.

Extern tabell i en fil

Du kan skapa externa tabeller som har åtkomst till data på ett Azure-lagringskonto som ger åtkomst till användare med viss Microsoft Entra-identitet eller SAS-nyckel. Du kan skapa externa tabeller på samma sätt som du skapar vanliga externa SQL Server-tabeller.

Följande fråga skapar en extern tabell som läser population.csv-filen från SynapseSQL demo Azure Storage-konto som refereras med hjälp av sqlondemanddemo datakällan och skyddas med databasomfattande autentiseringsuppgifter som kallas sqlondemand.

Datakälla och databasomfattande autentiseringsuppgifter skapas i installationsskriptet.

Kommentar

Ändra den första raden i frågan, t.ex. [mydbname], så att du använder databasen som du skapade.

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

Interna CSV-tabeller är för närvarande endast tillgängliga i serverlösa SQL-pooler.

Extern tabell på en uppsättning filer

Du kan skapa externa tabeller som läser data från en uppsättning filer som placeras på Azure Storage:

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

Du kan ange det mönster som filerna måste uppfylla för att kunna refereras till av den externa tabellen. Mönstret krävs endast för Parquet- och CSV-tabeller. Om du använder Delta Lake-format måste du bara ange en rotmapp, så hittar den externa tabellen automatiskt mönstret.

Kommentar

Tabellen skapas i partitionerad mappstruktur, men du kan inte utnyttja viss partitionseliminering. Om du vill få bättre prestanda genom att hoppa över de filer som inte uppfyller något kriterium (till exempel ett visst år eller en månad i det här fallet) använder du vyer på externa data.

Extern tabell med tilläggsfiler

De filer som refereras till av en extern tabell bör inte ändras medan frågan körs. I den långvariga frågan kan SQL-poolen försöka läsa igen, läsa delar av filerna eller till och med läsa filen flera gånger. Ändringar av filinnehållet skulle orsaka felaktiga resultat. Därför misslyckas SQL-poolen med frågan om den upptäcker att ändringstiden för en fil ändras under frågekörningen. I vissa scenarier kanske du vill skapa en tabell över de filer som ständigt läggs till. För att undvika frågefel på grund av filer som läggs till hela tiden kan du ange att den externa tabellen ska ignorera potentiellt inkonsekventa läsningar med hjälp av TABLE_OPTIONS inställningen.

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äsalternativet ALLOW_INCONSISTENT_READS inaktiverar tidskontrollen för filändring under frågelivscykeln och läser det som är tillgängligt i de filer som refereras till av den externa tabellen. I tilläggsbara filer uppdateras inte det befintliga innehållet och endast nya rader läggs till. Därför minimeras sannolikheten för felaktiga resultat jämfört med de uppdateringsbara filerna. Det här alternativet kan göra att du kan läsa de filer som ofta läggs till utan att hantera felen.

Det här alternativet är endast tillgängligt i de externa tabeller som skapats i CSV-filformat.

Kommentar

Som alternativnamnet antyder accepterar skaparen av tabellen en risk att resultatet kanske inte är konsekvent. I de tilläggsbara filerna kan du få felaktiga resultat om du tvingar fram flera läsningar av de underliggande filerna genom att ansluta tabellen själv. I de flesta "klassiska" frågor ignorerar den externa tabellen bara några rader som läggs till medan frågan kördes.

Delta Lake extern tabell

Externa tabeller kan skapas ovanpå en Delta Lake-mapp. Den enda skillnaden mellan de externa tabeller som skapats på en enda fil eller en filuppsättning och de externa tabeller som skapats i ett Delta Lake-format är att du i den externa Delta Lake-tabellen måste referera till en mapp som innehåller Delta Lake-strukturen.

Ett exempel på en tabelldefinition som skapats i en Delta Lake-mapp är:

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

Det går inte att skapa externa tabeller i en partitionerad mapp. Granska de andra kända problemen på självhjälpssidan för Synapse-serverlös SQL-pool.

Deltatabeller i partitionerade mappar

Externa tabeller i serverlösa SQL-pooler stöder inte partitionering i Delta Lake-format. Använd Delta-partitionerade vyer i stället för tabeller om du har partitionerat Delta Lake-datauppsättningar.

Viktigt!

Skapa inte externa tabeller på partitionerade Delta Lake-mappar även om du ser att de kan fungera i vissa fall. Om du använder funktioner som inte stöds, till exempel externa tabeller i partitionerade deltamappar, kan det orsaka problem eller instabilitet i den serverlösa poolen. Azure-supporten kan inte lösa något problem om det använder tabeller i partitionerade mappar. Du uppmanas att övergå till deltapartitionerade vyer och skriva om koden för att endast använda funktionen som stöds innan du fortsätter med problemlösningen.

Använda en extern tabell

Du kan använda externa tabeller i dina frågor på samma sätt som du använder dem i SQL Server-frågor.

Följande fråga visar detta med hjälp av den externa populationstabell som vi skapade i föregående avsnitt. Den returnerar namn på land/region med sin befolkning 2019 i fallande ordning.

Kommentar

Ändra den första raden i frågan, t.ex. [mydbname], så att du använder databasen som du skapade.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Prestanda för den här frågan kan variera beroende på region. Din arbetsyta kanske inte placeras i samma region som de Azure-lagringskonton som används i dessa exempel. För produktionsarbetsbelastningar placerar du din Synapse-arbetsyta och Azure Storage i samma region.

Nästa steg

Information om hur du lagrar resultatet av en fråga i lagring finns i Artikeln Lagra frågeresultat i lagringen .