Övning – SKAPA EXTERN TABELL SOM SELECT

10 minuter

I den här övningen använder du CREATE EXTERNAL TABLE AS SELECT (CETAS) för att:

Exportera en tabell som Parquet.
Flytta kalla data från en databas till lagring.
Skapa en extern tabell för att komma åt exporterade externa data.
Använd vyer eller jokerteckensökning som frågestrategier.
Begränsa frågor med hjälp av information om mappeliminering och metadata för att förbättra prestanda.

Förutsättningar

En SQL Server 2025-instans med internetanslutning och funktionen PolyBase Query Service for External Data installerad och aktiverad som för tidigare övningar.
Exempeldatabasen AdventureWorks2022 återställdes till servern för att användas för exempeldata.
Ett Azure Storage-konto med en Blob Storage-container med namnet data skapat. Information om hur du skapar lagringen finns i Snabbstart: Ladda upp, ladda ned och lista blobar med Azure-portalen.
Rollbaserad åtkomstkontroll (RBAC) i Azure, Storage Blob Data Contributor-rollen tilldelad i Azure. Mer information finns i Tilldela en Azure-roll för åtkomst till blobdata.
En SAS-token för blobcontainer med behörighet att LÄSA, SKRIVA, LISTA och SKAPA som ska användas för CETAS. Information om hur du skapar SAS-token finns i Skapa sas-token (signatur för delad åtkomst) för dina lagringscontainrar.

Använd CETAS för att exportera en tabell som Parquet

Anta att du arbetar med ett affärsanalysteam som vill exportera data som är äldre än 2012 från en SQL Server-tabell till en Azure Blob Storage-container. De vill köra sina rapportfrågor på dessa exporterade data i stället för att fråga SQL Server direkt.

Aktivera CETAS på SQL Server-instansen.

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

Skärmbild av hur du konfigurerar alternativet för att tillåta PolyBase-export.

Kör följande datautforskningsfråga för att förstå vilka data du vill exportera. I det här fallet letar du efter data som är från 2012 eller tidigare. Du vill exportera alla data från 2011 och 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Skapa en databashuvudnyckel för databasen, som i föregående övningar.

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;

Skapa autentiseringsuppgifter för databasens omfattning och en extern datakälla. Ersätt platshållarna <sas_token> och <storageccount> med lagringskontot och SAS-token som du skapade 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
);

Skapa det externa filformatet för Parquet.

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

Skapa den externa tabellen med hjälp av CETAS. Följande fråga skapar en extern tabell med namnet ext_data_2011_2012 och exporterar alla data från 2011 och 2012 till den plats som anges av datakällan 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

Kontrollera din Azure Blob Storage i Azure-portalen. Du bör se att följande struktur har skapats. SQL Server 2025 skapar automatiskt filnamnet baserat på hur mycket data det exporterar och filformatet.
Nu kan du komma åt den externa tabellen som en vanlig tabell.
```
SELECT * FROM ex_data_2011_2012
```

Data exporteras nu till Parquet och är lättillgängliga via den externa tabellen. Affärsanalysteamet kan göra en förfrågan mot den externa tabellen eller rikta sitt rapporteringsverktyg mot Parquet-filen.

Använda CETAS för att flytta kalla data från databasen

För att hålla data hanterbara bestämmer sig företaget för att flytta data som är äldre än 2014 från SQL Server-databasen. Alla data måste dock fortfarande vara tillgängliga.

I det här exemplet exporterar du data via CETAS och genererar flera externa tabeller som du kan köra frågor mot senare. Du kan använda en vy med UNION-instruktioner för att fråga efter data eller skapa en enda extern tabell och använda ett jokertecken för att söka igenom undermapparna för exporterade data.

Först klonar du den ursprungliga tabellen eftersom du vill simulera export och borttagning av data men inte nödvändigtvis vill ta bort den aktuella datakällan. Kör följande uttryck:

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

Från den första dataanalysen vet du att det finns 5551 poster från 2014. Allt före 2014 bör exporteras till en mapp som identifieras per år. Data från 2011 hamnar i en mapp med namnet 2011och så vidare.

Kör följande kommandon för att skapa de externa tabellerna:

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;

När du har kört dessa kommandon uppdaterar du SSMS Object Explorer. Öppna sedan Databaser>AdventureWorks2022>Tabeller>externa tabeller för att se de externa tabellerna.
Kontrollera att följande mappar visas i Azure Storage-containern:
När kalldata har exporterats kan du ta bort dem från den ursprungliga tabellplatsen.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Fråga efter data som innehåller den externa tabellen

Du kan använda en vy eller en jokerteckensökning för att söka i exporterade externa data. Varje metod har fördelar och nackdelar. Visningsmetoden rekommenderas för repetitiva begäranden eftersom den vanligtvis presterar bättre och kan även kombineras med fysiska tabeller. Sökmetoden för jokertecken är mer flexibel och enklare att använda i utforskningssyfte.

Använd en vy för att ställa frågor mot datan.

Nu när gamla data exporteras och tas bort från databasen kan du använda T-SQL för att skapa en vy som frågar alla externa tabeller och aktuella data i databasen.

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öra den ursprungliga datautforskningsfrågan, den här gången med hjälp av den nyligen skapade vyn, för att se samma resultat.

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

Använd en sökning med jokertecken för att söka efter data

I föregående exempel använde du en vy med UNION-instruktioner för att ansluta de tre externa tabellerna. Ett annat sätt att uppnå önskat resultat är att använda en jokerteckensökning för att söka igenom mappstrukturen, inklusive undermappar, efter data av en viss typ.

I följande T-SQL-exempel används OPENROWSET för att söka i ABS_Data datakällan, inklusive dess undermappar, 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]

Information om mappeliminering och metadata

Både externa tabeller och OPENROWSET kan använda funktionen filepath för att samla in och filtrera information baserat på filmetadata. Funktionen filepath returnerar fullständiga sökvägar, mappnamn och filnamn. Du kan använda den informationen för att förbättra sökfunktionerna för både den externa tabellen och OPENROWSET-kommandona.

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ärmbild av SSMS som visar funktionen filepath.

Om du vill hämta data från en viss mapp och fortfarande använder funktionen för sökmetoden med jokertecken kan du använda följande fråga:

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

Slutresultatet är detsamma, men genom att använda metadata för mappeliminering kommer frågan bara åt de mappar som krävs i stället för att söka igenom hela datakällan, vilket ger bättre frågeprestanda. Tänk på den här informationen när du utformar lagringsarkitekturer för att bättre använda PolyBase-funktioner.

Till exempel med tanke på följande mapparkitektur:

Skärmbild som visar ett exempel på mapparkitektur i en lagringscontainer.

Du kan använda följande fråga:

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

I den här frågan spelar det ingen roll hur stor datakällan växer. SQL Server läser in, läser och frågar bara data från den valda mappen och hoppar över alla andra.

Eftersom inga data lagras i databasen behöver databasadministratören inte utforma någon specifik strategi för att hantera dessa data. Företaget måste fortfarande vidta alla nödvändiga försiktighetsåtgärder för att på ett säkert sätt underhålla data, inklusive men inte begränsat till säkerhetskopior, tillgänglighet och behörigheter.

Sammanfattning

I den här övningen använde du CETAS för att flytta kalla data från en databas till Azure Storage och exportera en tabell som Parquet-filformat. Du har lärt dig olika sätt att fråga externa data för utforskning och optimera prestanda.

Du kan använda CETAS för att kombinera OPENROWSET, externa tabeller, vyer, jokerteckensökning och filvägsfunktioner. Du kan komma åt och exportera data från andra databaser som SQL Server, Oracle, Teradata och MongoDB, eller från Azure Blob Storage, Azure Data Lake Storage eller någon S3-kompatibel objektlagring. CETAS kan hjälpa dig att utforma högpresterande, hållbara och skalbara lösningar i alla PolyBase-datakällor som stöds.

Feedback

Var den här sidan till hjälp?