Oefening: CREATE EXTERNAL TABLE AS SELECT

10 minuten

In deze oefening gebruikt u CREATE EXTERNAL TABLE AS SELECT (CETAS) om:

Een tabel exporteren als Parquet.
Verplaats koude gegevens uit een database naar de opslag.
Maak een externe tabel voor toegang tot de geëxporteerde externe gegevens.
Gebruik weergaven of jokerzoekopdracht als strategieën voor zoekopdrachten.
Beperk queries door mapeliminatie en metagegevens te gebruiken om de prestaties te verbeteren.

Vereiste voorwaarden

Een SQL Server 2025-exemplaar met internetverbinding en de PolyBase Query-service voor externe gegevens is geïnstalleerd en ingeschakeld, net als voor eerdere oefeningen.
De AdventureWorks2022-voorbeelddatabase is hersteld naar uw server om te gebruiken voor voorbeeldgegevens.
Een Azure Storage-account met een Blob Storage-container met de naam data gemaakt. Als u de opslag wilt maken, raadpleegt u quickstart: blobs uploaden, downloaden en vermelden met Azure Portal.
De rol Op rollen gebaseerd toegangsbeheer (RBAC) voor opslagblobgegevensbijdrager van Azure die is toegewezen in Azure. Zie Een Azure-rol toewijzen voor toegang tot blobgegevensvoor meer informatie.
Een SAS-token voor blobcontainers met LEES-, SCHRIJF-, LIJST- en CREATE-machtigingen die moeten worden gebruikt voor CETAS. Zie SAS-tokens (Shared Access Signature) maken voor uw opslagcontainers om het SAS-token te creëren.

CETAS gebruiken om een tabel als Parquet te exporteren

Stel dat u met een business analytics-team werkt dat gegevens die ouder zijn dan 2012 willen exporteren van een SQL Server-tabel naar een Azure Blob Storage-container. Ze willen hun rapportquery's uitvoeren op deze geëxporteerde gegevens in plaats van rechtstreeks query's uit te voeren op SQL Server.

Schakel CETAS in op het SQL Server-exemplaar.

EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;

Schermopname van het configureren van de optie voor het toestaan van PolyBase-export.

Voer de volgende query voor gegevensverkenning uit om te begrijpen welke gegevens u wilt exporteren. In dit geval zoekt u gegevens uit 2012 of eerder. U wilt alle gegevens uit 2011 en 2012 exporteren.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Maak een databasehoofdsleutel voor de database, zoals in de vorige oefeningen.

Use AdventureWorks2022

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXEC sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Maak de aan de database toegewezen inloggegevens en externe gegevensbron. Vervang de <sas_token>- en <storageccount>-placeholder door het opslagaccount en het SAS-token dat u in Azure hebt gemaakt.

-- DATABASE SCOPED CREDENTIAL
CREATE DATABASE SCOPED CREDENTIAL blob_storage
      WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = '<sas_token>';

-- AZURE BLOB STORAGE DATA SOURCE
CREATE EXTERNAL DATA SOURCE ABS_Data
WITH
(
 LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
,CREDENTIAL = blob_storage
);

Maak de externe bestandsindeling voor Parquet.

-- PARQUET FILE FORMAT
CREATE EXTERNAL FILE FORMAT ffParquet
WITH (FORMAT_TYPE = PARQUET);

Maak de externe tabel met CETAS. Met de volgende query maakt u een externe tabel met de naam ext_data_2011_2012 en exporteert u alle gegevens uit 2011 en 2012 naar de locatie die is opgegeven door de gegevensbron ABS_Data.

CREATE EXTERNAL TABLE ex_data_2011_2012
WITH(
        LOCATION = 'data_2011_20122',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
	[PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
WHERE YEAR([DUEDATE]) < 2013
GO

Controleer uw Azure Blob Storage in Azure Portal. Je zou de volgende structuur moeten zien. SQL Server 2025 maakt automatisch de bestandsnaam op basis van de hoeveelheid gegevens die worden geëxporteerd en de bestandsindeling.
U hebt nu toegang tot de externe tabel, zoals een gewone tabel.
```
SELECT * FROM ex_data_2011_2012
```

De gegevens worden nu geëxporteerd naar Parquet en zijn eenvoudig toegankelijk via de externe tabel. Het business analytics-team kan een query uitvoeren op de externe tabel of het rapportagehulpmiddel naar het Parquet-bestand laten verwijzen.

CETAS gebruiken om koude gegevens uit de database te verplaatsen

Als u de gegevens beheerbaar wilt houden, besluit uw bedrijf om gegevens ouder dan 2014 te verplaatsen uit de SQL Server-database. Alle gegevens moeten echter nog steeds toegankelijk zijn.

In dit voorbeeld exporteert u de gegevens via CETAS en genereert u verschillende externe tabellen die u later kunt opvragen. U kunt een weergave met UNION-instructies gebruiken om een query uit te voeren op de gegevens of een enkele externe tabel maken en een jokerteken gebruiken om door de submappen van de geëxporteerde gegevens te zoeken.

Kloon eerst de oorspronkelijke tabel, omdat u het exporteren en verwijderen van de gegevens wilt simuleren, maar niet noodzakelijkerwijs de huidige gegevensbron wilt verwijderen. Voer de volgende instructie uit:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

Vanaf de eerste query voor gegevensverkenning weet u dat er 5551 records uit 2014 zijn. Alles vóór 2014 moet worden geëxporteerd naar een map die per jaar wordt geïdentificeerd. Gegevens uit 2011 gaan naar een map met de naam 2011, enzovoort.

Voer de volgende opdrachten uit om de externe tabellen te maken:

CREATE EXTERNAL TABLE ex_2011
WITH(
        LOCATION = '2011',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2011;

CREATE EXTERNAL TABLE ex_2012
WITH(
        LOCATION = '2012',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2012;

CREATE EXTERNAL TABLE ex_2013
WITH(
        LOCATION = '2013',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2013;

Nadat u deze opdrachten hebt uitgevoerd, vernieuwt u SSMS-objectverkenner. Open vervolgens Databases>AdventureWorks2022>Tables>External Tables om de externe tabellen te zien.
Controleer of de volgende mappen worden weergegeven in de Azure Storage-container:
Nadat de koude gegevens zijn geëxporteerd, kunt u deze verwijderen uit de oorspronkelijke tabellocatie.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Query's uitvoeren op gegevens die de externe tabel bevatten

U kunt een weergave of een zoekopdracht met jokertekens gebruiken om een query uit te voeren op de geëxporteerde externe gegevens. Elke methode heeft voor- en nadelen. De weergavemethode wordt aanbevolen voor terugkerende aanvragen, omdat deze meestal beter presteert en ook kan worden gecombineerd met fysieke tabellen. De zoekmethode met jokertekens is flexibeler en eenvoudiger te gebruiken voor verkenningsdoeleinden.

Een weergave gebruiken om een query uit te voeren op de gegevens

Nu de oude gegevens worden geëxporteerd en verwijderd uit de database, kunt u T-SQL gebruiken om een weergave te maken waarmee alle externe tabellen en de huidige gegevens in uw database worden opgevraagd.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2]

U kunt de oorspronkelijke query voor gegevensverkenning uitvoeren, deze keer met behulp van de zojuist gemaakte weergave, om dezelfde resultaten te zien.

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Een zoekopdracht met jokertekens gebruiken om een query uit te voeren op de gegevens

In het voorgaande voorbeeld hebt u een weergave met UNION-instructies gebruikt om de drie externe tabellen samen te voegen. Een andere manier om de gewenste resultaten te bereiken, is door een zoekopdracht met jokertekens te gebruiken om de mapstructuur, inclusief submappen, te scannen op gegevens van een bepaald type.

In het volgende T-SQL-voorbeeld wordt OPENROWSET gebruikt om te zoeken in de ABS_Data gegevensbron, inclusief de submappen, voor Parquet-bestanden.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Informatie over het verwijderen van mappen en metagegevens

Zowel externe tabellen als OPENROWSET kunnen de filepath functie gebruiken om informatie te verzamelen en te filteren op basis van bestandsmetagegevens. De filepath functie retourneert volledige paden, mapnamen en bestandsnamen. U kunt deze informatie gebruiken om de zoekmogelijkheden van zowel de externe tabel als openROWSET-opdrachten te verbeteren.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

Schermopname van SSMS met de bestandspadfunctie.

Als u gegevens uit een bepaalde map wilt ophalen en nog steeds de functionaliteit van de zoekmethode met jokertekens wilt gebruiken, kunt u de volgende query gebruiken:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

De eindresultaten zijn hetzelfde, maar door de metagegevens voor het verwijderen van mappen te gebruiken, heeft uw query alleen toegang tot de vereiste mappen in plaats van de hele gegevensbron te scannen, waardoor betere queryprestaties worden geproduceerd. Houd rekening met deze informatie wanneer u opslagarchitecturen ontwerpt om polybase-mogelijkheden beter te gebruiken.

Bijvoorbeeld, op basis van de volgende maparchitectuur:

Schermopname van een voorbeeld van een maparchitectuur in een opslagcontainer.

U kunt de volgende query gebruiken:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

Voor deze query maakt het niet uit hoe groot de gegevensbron groeit. SQL Server laadt, leest en voert alleen query's uit op de gegevens uit de geselecteerde map, waarbij alle andere worden overgeslagen.

Omdat er geen gegevens in de database worden opgeslagen, hoeft de databasebeheerder geen specifieke strategie te ontwerpen om deze gegevens te beheren. Het bedrijf moet nog steeds alle vereiste voorzorgsmaatregelen nemen om de gegevens veilig te onderhouden, inclusief maar niet beperkt tot back-ups, beschikbaarheid en machtigingen.

Overzicht

In deze oefening hebt u CETAS gebruikt om koude gegevens uit een database naar Azure Storage te verplaatsen en een tabel als Parquet-bestandsindeling te exporteren. U hebt manieren geleerd om een query uit te voeren op de externe gegevens voor verkenning en om de prestaties te optimaliseren.

U kunt CETAS gebruiken om OPENROWSET, externe tabellen, weergaven, zoekopdrachten met jokertekens en bestandspadfuncties te combineren. U kunt gegevens openen en exporteren uit andere databases, zoals SQL Server, Oracle, Teradata en MongoDB, of vanuit Azure Blob Storage, Azure Data Lake Storage of een S3-compatibele objectopslag. CETAS kan u helpen bij het ontwerpen van krachtige, duurzame en schaalbare oplossingen in alle door PolyBase ondersteunde gegevensbronnen.

Feedback

Is deze pagina nuttig?