Øvelse - OPRET EKSTERN TABEL SOM SELECT

10 minutter

I denne øvelse bruger du CREATE EXTERNAL TABLE AS SELECT (CETAS) til at:

Eksportér en tabel som Parquet.
Flyt kolde data ud af en database til lageret.
Opret en ekstern tabel for at få adgang til de eksporterede eksterne data.
Brug visninger eller wildcard-søgning som forespørgselsstrategier.
Begræns forespørgsler ved at bruge mappe-eliminering og metadata-information for at forbedre ydeevnen.

Forudsætninger

En SQL Server 2025-instans med internetforbindelse og funktionen PolyBase Query Service for External Data installeret og aktiveret som ved tidligere øvelser.
AdventureWorks2022 eksempeldatabasen genoprettes på din server for at bruge den til eksempeldata.
En Azure Storage-konto med en Blob Storage-container kaldet data oprettet. For at oprette lagringen, se Quickstart: Upload, download og list blobs med Azure-portalen.
Azure rollebaseret adgangskontrol (RBAC) Storage Blob Data Contributor-rollen er tildelt i Azure. For mere information, se Tildel en Azure-rolle for adgang til blob-data.
En blob-container SAS-token med READ,WRITE, LIST og CREATE-tilladelser, som skal bruges til CETAS. For at oprette SAS-tokenet, se Opret shared access signature (SAS) tokens for dine lagringscontainere.

Brug CETAS til at eksportere en tabel som Parquet

Forestil dig, at du arbejder med et business analytics-team, der ønsker at eksportere data ældre end 2012 fra en SQL Server-tabel til en Azure Blob Storage-container. De vil køre deres rapportforespørgsler på disse eksporterede data i stedet for direkte at forespørge SQL Server.

Aktivér CETAS på SQL Server-instansen.

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

Skærmbillede af konfigurationen af muligheden for at tillade PolyBase-eksport.

Udfør følgende dataundersøgelsesforespørgsel for at forstå, hvilke data du ønsker at eksportere. I dette tilfælde leder du efter data, der er fra 2012 eller tidligere. Du vil eksportere alle data fra 2011 og 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Opret en databasehovednøgle til databasen, som i de tidligere øvelser.

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;

Opret databasens scopede legitimationsoplysninger og eksterne datakilder. Erstat <sas_token> og <storageccount> pladsholderne med den lagerkonto og SAS-token, du oprettede i Azure.

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

Opret det eksterne filformat for Parquet.

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

Opret den eksterne tabel ved hjælp af CETAS. Følgende forespørgsel opretter en ekstern tabel med navnet ext_data_2011_2012 og eksporterer alle data fra 2011 og 2012 til den placering, som datakilden ABS_Dataangiver.

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

Tjek din Azure Blob Storage i Azure portal. Du bør se følgende struktur oprettet. SQL Server 2025 opretter automatisk filnavnet baseret på, hvor meget data den eksporterer, og filformatet.
Du kan nu få adgang til den eksterne tabel som en almindelig tabel.
```
SELECT * FROM ex_data_2011_2012
```

Dataene eksporteres nu til Parquet og er let tilgængelige via den eksterne tabel. Business analytics-teamet kan forespørge den eksterne tabel eller pege deres rapporteringsværktøj på Parquet-filen.

Brug CETAS til at flytte kolde data ud af databasen

For at holde dataene håndterbare beslutter din virksomhed at flytte data ældre end 2014 fra SQL Server-databasen. Dog skal alle data stadig være tilgængelige.

I dette eksempel eksporterer du dataene via CETAS og genererer flere eksterne tabeller, som du kan forespørge senere. Du kan bruge en visning med UNION-sætninger til at forespørge dataene, eller oprette en enkelt ekstern tabel og bruge et wildcard til at søge gennem undermapperne med de eksporterede data.

Først skal du klone den oprindelige tabel, fordi du vil simulere eksport og fjernelse af dataene, men ikke nødvendigvis slette den nuværende datakilde. Kør følgende sætning:

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

Fra den første dataudforskningsforespørgsel ved du, at der er 5551 poster fra 2014. Alt før 2014 bør eksporteres til en mappe identificeret efter år. Data fra 2011 går ind i en mappe kaldet 2011, og så videre.

For at oprette de eksterne tabeller skal følgende kommandoer udføres:

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;

Efter du har udført disse kommandoer, opdater du SSMS Object Explorer. Åbn derefter Databaser>AdventureWorks2022>Tabeller>Eksterne Tabeller for at se de eksterne tabeller.
Bekræft, at følgende mapper vises i Azure Storage-containeren:
Efter at de kolde data er eksporteret, kan du slette dem fra den oprindelige tabelplacering.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Forespørg data, der inkluderer den eksterne tabel

Du kan bruge en visning eller en wildcard-søgning til at forespørge de eksporterede eksterne data. Hver metode har fordele og ulemper. View-metoden anbefales til gentagne forespørgsler, fordi den normalt yder bedre og også kan kombineres med fysiske tabeller. Metoden med jokerkort-søgning er mere fleksibel og nemmere at bruge til udforskningsformål.

Brug en visning til at forespørge dataene

Nu hvor de gamle data er eksporteret og slettet fra databasen, kan du bruge T-SQL til at oprette en visning, der forespørger alle eksterne tabeller og de aktuelle data i din database.

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]

Du kan køre den oprindelige dataudforskningsforespørgsel, denne gang ved hjælp af den nyoprettede visning, for at se de samme resultater.

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

Brug en wildcard-søgning til at forespørge dataene

I det foregående eksempel brugte du en visning med UNION-sætninger til at forbinde de tre eksterne tabeller. En anden måde at opnå de ønskede resultater på er at bruge en wildcard-søgning til at scanne mappestrukturen, inklusive undermapper, for data af en bestemt type.

Følgende T-SQL-eksempel bruger OPENROWSET til at søge på tværs af datakilden ABS_Data , inklusive dens undermapper, efter Parquet-filer.

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]

Mappe-eliminering og metadatainformation

Både eksterne tabeller og OPENROWSET kan bruge filepath funktionen til at indsamle og filtrere information baseret på filmetadata. Funktionen filepath returnerer fulde stier, mappenavne og filnavne. Du kan bruge den information til at forbedre søgemulighederne for både den eksterne tabel og OPENROWSET-kommandoen.

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)

Skærmbillede af SSMS, der viser filsti-funktionen.

Hvis du vil hente data fra en bestemt mappe og stadig bruge funktionaliteten i wildcard-søgemetoden, kan du bruge følgende forespørgsel:

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

Slutresultaterne er de samme, men ved at bruge mappe-eliminationsmetadata får din forespørgsel kun adgang til de nødvendige mapper i stedet for at scanne hele datakilden, hvilket giver bedre forespørgselsydelse. Hav denne information i baghovedet, når du designer lagringsarkitekturer for bedre at udnytte PolyBase-funktioner.

For eksempel, givet følgende mappearkitektur:

Skærmbillede, der viser et eksempel på en mappearkitektur i en opbevaringsbeholder.

Du kan bruge følgende forespørgsel:

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

Til formålet med denne forespørgsel er det ligegyldigt, hvor stor datakilden bliver. SQL Server indlæser, læser og forespørger kun data fra den valgte mappe og springer alle andre over.

Da der ikke gemmes data i databasen, behøver databaseadministratoren ikke designe en specifik strategi for at håndtere disse data. Virksomheden skal stadig tage alle nødvendige forholdsregler for sikkert at vedligeholde dataene, herunder men ikke begrænset til backups, tilgængelighed og tilladelser.

Resumé

I denne øvelse brugte du CETAS til at flytte kolde data ud af en database til Azure Storage og eksportere en tabel som Parquet-filformat. Du lærte måder at forespørge de eksterne data til udforskning og optimere ydeevnen.

Du kan bruge CETAS til at kombinere OPENROWSET, eksterne tabeller, visninger, wildcard-søgning og filsti-funktioner. Du kan tilgå og eksportere data fra andre databaser som SQL Server, Oracle, Teradata og MongoDB, eller fra Azure Blob Storage, Azure Data Lake Storage eller enhver S3-kompatibel objektlagring. CETAS kan hjælpe dig med at designe performante, holdbare og skalerbare løsninger på tværs af alle PolyBase-understøttede datakilder.

Feedback

Var denne side nyttig?