Delen via

Deltatabel virtualiseren met PolyBase

Van toepassing op: SQL Server 2022 (16.x) en latere versies

SQL Server 2022 (16.x) kan rechtstreeks vanuit een deltatabelmap query's uitvoeren op gegevens. Met dit concept, meestal gegevensvirtualisatie genoemd, kunnen de gegevens op de oorspronkelijke locatie blijven, maar kunnen ze worden opgevraagd vanuit een SQL Server-exemplaar met T-SQL-opdrachten zoals elke andere tabel. Deze functie maakt gebruik van PolyBase-connectors en minimaliseert de noodzaak voor het kopiëren van gegevens via ETL-processen.

In het volgende voorbeeld wordt de map deltatabel opgeslagen in Azure Blob Storage en geopend via OPENROWSET of een externe tabel.

Maak kennis met gegevensvirtualisatie met PolyBase voor meer informatie over gegevensvirtualisatie.

Vooraf configureren

1. PolyBase inschakelen in sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Een gebruikersdatabase maken

In deze oefening maakt u een voorbeelddatabase met standaardinstellingen en -locatie. Je gebruikt deze lege voorbeelddatabase om met de gegevens te werken en de gescope referentie op te slaan. In dit voorbeeld wordt een nieuwe lege database met de naam Delta_demo gebruikt.

CREATE DATABASE [Delta_demo];

3. Maak een hoofdsleutel en database-gerelateerde referentie

De master key van de database in de gebruikersdatabase is vereist om het credential-geheim binnen het databasebereik te versleutelen. delta_storage_dsc In dit voorbeeld bevindt de deltatabel zich in Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Externe gegevensbron maken

Database-georiënteerde referentie wordt gebruikt voor de externe gegevensbron. In dit voorbeeld bevindt de deltatabel zich in Azure Data Lake Storage Gen2, dus gebruik voorvoegsel adls en de SHARED ACCESS SIGNATURE identiteitsmethode. Zie CREATE EXTERNAL DATA SOURCE voor meer informatie over de connectors en voorvoegsels, waaronder nieuwe instellingen voor SQL Server 2022 (16.x).

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Als uw opslagaccount bijvoorbeeld een naam delta_lake_sample heeft en de container de naam sinkheeft, is de code:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

OPENROWSET gebruiken om toegang te krijgen tot de gegevens

In dit voorbeeld heeft de map Gegevenstabel de naam Contoso.

Omdat de externe gegevensbron Delta_ED is toegewezen aan een containerniveau. De Contoso deltatabelmap bevindt zich in een rootmap. Als u een query wilt uitvoeren op een bestand in een mapstructuur, geeft u een maptoewijzing op ten opzichte van de parameter LOCATION van de externe gegevensbron.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Gegevens opvragen met een externe tabel

CREATE EXTERNAL TABLE kan ook worden gebruikt om de deltatabelgegevens in SQL Server te virtualiseren. De kolommen moeten worden gedefinieerd en sterk getypt. Hoewel externe tabellen meer moeite kosten om te maken, bieden ze ook extra voordelen ten opzichte van het uitvoeren van query's op een externe gegevensbron met OPENROWSET. U kunt het volgende doen:

  • De definitie van het typen van gegevens voor een bepaalde kolom versterken
  • Null-waarde definiëren
  • COLLATIE definiëren
  • Statistieken maken voor een kolom om de kwaliteit van het queryplan te optimaliseren
  • Een gedetailleerder model maken in SQL Server voor gegevenstoegang om uw beveiligingsmodel te verbeteren

Zie CREATE EXTERNAL TABLE voor meer informatie.

In het volgende voorbeeld wordt dezelfde gegevensbron gebruikt.

1. Externe bestandsindeling maken

Als u de opmaak van het bestand wilt definiëren, is een externe bestandsindeling vereist. Externe bestandsindelingen worden ook aanbevolen vanwege herbruikbaarheid. Zie CREATE EXTERNAL FILE FORMAT voor meer informatie.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Externe tabel maken

De deltatabelbestanden bevinden zich op /delta/Delta_yob/ en de externe gegevensbron voor dit voorbeeld is S3-compatibele objectopslag, eerder geconfigureerd onder de gegevensbron s3_eds. PolyBase kan de map van de deltatabel of het absolute bestand als locatie gebruiken, dat zich zou bevinden op delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Beperkingen

Als u een externe tabel maakt die verwijst naar een gepartitioneerde deltatabel, wordt de kolom die wordt gebruikt voor partitionering, geretourneerd NULL bij het uitvoeren van query's op de externe tabel. Als u echter een OPENROWSET query gebruikt, wordt de kolomwaarde correct geretourneerd. Als u deze beperking wilt omzeilen, maakt u een weergave voor de OPENROWSET query en voert u vervolgens een query uit op de weergave om de gepartitioneerde kolomwaarden op te halen om correct te retourneren.

Er kunnen de volgende fouten optreden bij het uitvoeren van query's op een externe Delta-tabel:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Dit kan gebeuren omdat er een queryhint is die kan worden toegevoegd aan de QUERYTRACEON deltabestand-metagegevensquery en waarvoor de sysadmin serverrol moet worden uitgevoerd. Als dit gebeurt, kunt u het probleem oplossen door traceringsvlag 14073 globaal in te schakelen. Hierdoor wordt voorkomen dat de queryhint wordt toegevoegd.

Verwante inhoud

  • Last updated on