Systeemeigen externe tabellen maken en gebruiken met behulp van SQL-pools in Azure Synapse Analytics

In deze sectie leert u hoe u systeemeigen externe tabellen maakt en gebruikt in Synapse SQL-pools. Systeemeigen externe tabellen hebben betere prestaties in vergelijking met externe tabellen in TYPE=HADOOP hun definitie van de externe gegevensbron. Dit komt doordat systeemeigen externe tabellen systeemeigen code gebruiken voor toegang tot externe gegevens.

Externe tabellen zijn handig als u de toegang tot externe gegevens in de Synapse SQL-pool wilt beheren. Externe tabellen zijn ook handig als u hulpprogramma's, zoals Power BI, wilt gebruiken in combinatie met de Synapse SQL-pool. Externe tabellen hebben toegang tot twee soorten opslag:

• Openbare opslag waar gebruikers toegang hebben tot openbare opslagbestanden.
• Beveiligde opslag waarbij gebruikers toegang hebben tot opslagbestanden met sas-referenties, Microsoft Entra-identiteit of beheerde identiteit van de Synapse-werkruimte.

Notitie

In toegewezen SQL-pools kunt u alleen systeemeigen externe tabellen met een Parquet-bestandstype gebruiken en deze functie is beschikbaar als openbare preview. Als u algemeen beschikbare Parquet-lezerfunctionaliteit wilt gebruiken in toegewezen SQL-pools of als u toegang nodig hebt tot CSV- of ORC-bestanden, gebruikt u Externe Hadoop-tabellen. Systeemeigen externe tabellen zijn algemeen beschikbaar in serverloze SQL-pools. Meer informatie over de verschillen tussen systeemeigen en externe Hadoop-tabellen in Externe tabellen gebruiken met Synapse SQL.

De volgende tabel bevat de ondersteunde gegevensindelingen:

Gegevensindeling (systeemeigen externe tabellen)	Serverloze SQL-pool	Toegewezen SQL-pool
Parquet	Ja (GA)	Ja (openbare preview)
CSV	Ja	Nee (U kunt ook Externe Hadoop-tabellen gebruiken)
delta	Ja	Nee
Spark	Ja	Nee
Dataverse	Ja	Nee
Azure Cosmos DB-gegevensindelingen (JSON, BSON enzovoort)	Nee (u kunt ook weergaven maken)	Nee

Vereisten

De eerste stap bestaat uit het maken van een database waarin de tabellen worden gemaakt. Voordat u een databasereferentie met een bereik maakt, moet de database een hoofdsleutel hebben om de referentie te beveiligen. Zie CREATE MASTER KEY (Transact-SQL) voor meer informatie hierover. Maak vervolgens de volgende objecten die in dit voorbeeld worden gebruikt:

• REFERENTIES VAN DATABASECONFIGURATIE sqlondemand waarmee toegang tot een met SAS beveiligde https://sqlondemandstorage.blob.core.windows.net Azure-opslagaccount mogelijk wordt.

  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'
  

• EXTERNE GEGEVENSBRON sqlondemanddemo die verwijst naar een demo-opslagaccount dat wordt beveiligd met een SAS-sleutel en EXTERNE GEGEVENSBRON nyctlc die verwijst naar een openbaar beschikbaar Azure-opslagaccount op locatie 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/' );
  

• De bestandsindelingen QuotedCSVWithHeaderFormat en ParquetFormat die bestandstypen van het type CSV en Parquet beschrijven.

  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
  

De query's in dit artikel worden uitgevoerd op uw voorbeelddatabase en gebruiken deze objecten.

Externe tabel in een bestand

U kunt externe tabellen maken die toegang hebben tot gegevens in een Azure-opslagaccount waarmee gebruikers met een Microsoft Entra-identiteit of SAS-sleutel toegang hebben tot gebruikers. U kunt externe tabellen op dezelfde manier maken als gewone externe tabellen van SQL Server.

Met de volgende query maakt u een externe tabel die het bestand population.csv van het SynapseSQL Azure demo-opslagaccount leest waarnaar wordt verwezen met behulp van de gegevensbron sqlondemanddemo en die wordt beveiligd met databaseconfiguratiereferenties met de naam sqlondemand.

De referenties voor de gegevensbron en de databaseconfiguratie zijn gemaakt in het installatiescript.

Notitie

Wijzig de eerste regel in de query, d.w.z. [mydbname], zodat u de database gebruikt die u hebt gemaakt.

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

Systeemeigen CSV-tabellen zijn momenteel alleen beschikbaar in de serverloze SQL-pools.

Externe tabel voor een set bestanden

U kunt externe tabellen maken die gegevens lezen uit een set bestanden die zijn geplaatst in 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
);

U kunt het patroon opgeven waaraan de bestanden moeten voldoen om te kunnen worden verwezen door de externe tabel. Het patroon is alleen vereist voor Parquet- en CSV-tabellen. Als u de Delta Lake-indeling gebruikt, moet u alleen een hoofdmap opgeven en wordt het patroon automatisch gevonden in de externe tabel.

Notitie

De tabel wordt gemaakt in de gepartitioneerde mapstructuur, maar u kunt geen partities verwijderen. Als u betere prestaties wilt krijgen door de bestanden te overslaan die niet voldoen aan een bepaald criterium (zoals specifieke jaar of maand in dit geval), gebruikt u weergaven voor externe gegevens.

Externe tabel voor toevoegbare bestanden

De bestanden waarnaar wordt verwezen door een externe tabel, moeten niet worden gewijzigd terwijl de query wordt uitgevoerd. In de langlopende query kan de SQL-pool leesbewerkingen opnieuw proberen, delen van de bestanden lezen of zelfs meerdere keren het bestand lezen. Wijzigingen van de bestandsinhoud veroorzaken verkeerde resultaten. De SQL-pool mislukt daarom de query als wordt gedetecteerd dat de wijzigingstijd van een bestand wordt gewijzigd tijdens de uitvoering van de query. In sommige scenario's wilt u mogelijk een tabel maken voor de bestanden die voortdurend worden toegevoegd. Als u de queryfouten wilt voorkomen vanwege voortdurend toegevoegde bestanden, kunt u opgeven dat de externe tabel mogelijk inconsistente leesbewerkingen moet negeren met behulp van de TABLE_OPTIONS instelling.

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"]}'
);

Met de ALLOW_INCONSISTENT_READS leesoptie wordt de tijdscontrole voor het wijzigen van bestanden tijdens de levenscyclus van de query uitgeschakeld en wordt gelezen wat er beschikbaar is in de bestanden waarnaar wordt verwezen door de externe tabel. In toevoegbare bestanden wordt de bestaande inhoud niet bijgewerkt en worden alleen nieuwe rijen toegevoegd. Daarom wordt de kans op verkeerde resultaten geminimaliseerd in vergelijking met de bijwerkbare bestanden. Met deze optie kunt u mogelijk de vaak toegevoegde bestanden lezen zonder de fouten te verwerken.

Deze optie is alleen beschikbaar in de externe tabellen die zijn gemaakt in de CSV-bestandsindeling.

Notitie

Zoals de optienaam al aangeeft, accepteert de maker van de tabel een risico dat de resultaten mogelijk niet consistent zijn. In de toevoegbare bestanden krijgt u mogelijk onjuiste resultaten als u meerdere leesbewerkingen van de onderliggende bestanden forceert door de tabel zelf te koppelen. In de meeste klassieke query's negeert de externe tabel slechts enkele rijen die zijn toegevoegd terwijl de query werd uitgevoerd.

Externe Delta Lake-tabel

Externe tabellen kunnen worden gemaakt boven op een Delta Lake-map. Het enige verschil tussen de externe tabellen die zijn gemaakt op één bestand of een bestandsset en de externe tabellen die zijn gemaakt in een Delta Lake-indeling, is dat u in de externe Delta Lake-tabel naar een map met de Delta Lake-structuur moet verwijzen.

Een voorbeeld van een tabeldefinitie die is gemaakt in een Delta Lake-map is:

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

Externe tabellen kunnen niet worden gemaakt in een gepartitioneerde map. Bekijk de andere bekende problemen op de self-helppagina van de serverloze SQL-pool in Synapse.

Delta-tabellen in gepartitioneerde mappen

Externe tabellen in serverloze SQL-pools bieden geen ondersteuning voor partitionering in Delta Lake-indeling. Gebruik gepartitioneerde Delta-weergaven in plaats van tabellen als u Delta Lake-gegevenssets hebt gepartitioneerd.

Belangrijk

Maak geen externe tabellen in gepartitioneerde Delta Lake-mappen, zelfs niet als u ziet dat ze in sommige gevallen kunnen werken. Het gebruik van niet-ondersteunde functies zoals externe tabellen in gepartitioneerde deltamappen kan problemen of instabiliteit van de serverloze pool veroorzaken. ondersteuning voor Azure kan geen probleem oplossen als er tabellen in gepartitioneerde mappen worden gebruikt. U wordt gevraagd om over te stappen naar gepartitioneerde Delta-weergaven en uw code te herschrijven om alleen de ondersteunde functie te gebruiken voordat u doorgaat met het oplossen van problemen.

Een externe tabel gebruiken

U kunt externe tabellen in uw query's op dezelfde manier gebruiken als u deze in SQL Server-query's gebruikt.

Met de volgende query wordt dit gedemonstreerd met behulp van de externe tabel population die we in de vorige sectie hebben gemaakt. Deze tabel retourneert de namen van landen/regio's met hun populatie in 2019 in aflopende volgorde.

Notitie

Wijzig de eerste regel in de query, d.w.z. [mydbname], zodat u de database gebruikt die u hebt gemaakt.

USE [mydbname];
GO

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

De prestaties van deze query kunnen variëren, afhankelijk van de regio. Uw werkruimte wordt mogelijk niet in dezelfde regio geplaatst als de Azure-opslagaccounts die in deze voorbeelden worden gebruikt. Voor productieworkloads plaatst u uw Synapse-werkruimte en Azure-opslag in dezelfde regio.

Volgende stappen

Raadpleeg store-queryresultaten in het opslagartikel voor informatie over het opslaan van resultaten van een query naar opslag.