Gyakorlat – KÜLSŐ TÁBLA LÉTREHOZÁSA KIVÁLASZTÁSKÉNT

10 perc

Ebben a gyakorlatban a következőkre használja a CREATE EXTERNAL TABLE AS SELECT (CETAS) parancsot:

Tábla exportálása parquetként.
Hideg adatok áthelyezése az adatbázisból a tárolóba.
Hozzon létre egy külső táblát az exportált külső adatok eléréséhez.
Nézetek vagy helyettesítő karakterek keresése lekérdezési módszerként alkalmazható.
A lekérdezések korlátozása a mappák eltávolításával és a metaadatokkal a teljesítmény javítása érdekében.

Előfeltételek

Az internetkapcsolattal rendelkező SQL Server 2025-példány és a PolyBase Query Service for External Data szolgáltatás telepítve és engedélyezve van a korábbi gyakorlatokhoz hasonlóan.
A AdventureWorks2022 mintaadatbázis vissza lett állítva a kiszolgálóra annak érdekében, hogy mintaadatként használható legyen.
Egy Azure Storage-fiók data nevű Blob Storage-tárolóval. A tároló létrehozásához tekintse meg a blobok Azure Portallal való feltöltését, letöltését és listázását ismertető rövid útmutatót.
Az Azure szerepkör-alapú hozzáférésvezérlésének (RBAC) Storage Blob Data Közreműködő szerepe az Azure-ban szerepkörként lett hozzárendelve. További információ: Azure-szerepkör hozzárendelése blobadatokhoz való hozzáféréshez.
A CETAS-hez használandó READ, WRITE, LIST és CREATE engedélyekkel rendelkező blobtároló SAS-jogkivonata. Az SAS-jogkivonat létrehozásához lásd: Megosztott hozzáférésű jogosultságkódok (SAS)-jogkivonatok létrehozása a tárolókhoz.

A CETAS használatával tábla exportálása Parquet formátumba

Tegyük fel, hogy olyan üzleti elemzési csapattal dolgozik, amely 2012-nél régebbi adatokat szeretne exportálni egy SQL Server-táblából egy Azure Blob Storage-tárolóba. Az SQL Server közvetlen lekérdezése helyett az exportált adatokon szeretnék futtatni a jelentés lekérdezéseit.

Engedélyezze a CETAS-t az SQL Server-példányon.

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

Képernyőkép a PolyBase-exportálás engedélyezésének beállításáról.

Az alábbi adatfeltárási lekérdezés végrehajtásával megtudhatja, hogy milyen adatokat szeretne exportálni. Ebben az esetben a 2012-től vagy korábbiaktól származó adatokat keresi. 2011-től és 2012-től minden adatot exportálni szeretne.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Hozzon létre egy adatbázis-főkulcsot az adatbázishoz az előző gyakorlatokhoz hasonlóan.

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;

Hozza létre az adatbázis hatókörébe tartozó hitelesítő adatokat és külső adatforrást. Cserélje le a <sas_token> és <storageccount> helyettesítőket az Azure-ban létrehozott tárolási fiókra és SAS-jogkivonatra.

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

Hozza létre a Parquet külső fájlformátumát.

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

Hozza létre a külső táblát a CETAS használatával. Az alábbi lekérdezés létrehoz egy ext_data_2011_2012 nevű külső táblát, és exportálja a 2011- és 2012-től származó összes adatot az adatforrás által megadott helyre 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

Ellenőrizze az Azure Blob Storage-t az Azure Portalon. A következő struktúrát kell létrehoznia. Az SQL Server 2025 automatikusan létrehozza a fájlnevet az exportált adatok és a fájlformátum alapján.
Mostantól a külső táblázatot is elérheti, mint egy normál táblát.
```
SELECT * FROM ex_data_2011_2012
```

Az adatok most már exportálva lesznek a Parquetbe, és könnyen elérhetők a külső táblán keresztül. Az üzleti elemzési csapat lekérdezheti a külső táblát, vagy a jelentéskészítő eszközét a Parquet-fájlra irányíthatja.

A CETAS használata a ritka elérésű adatok adatbázisból való áthelyezéséhez

Az adatok kezelhetőségének megőrzése érdekében a vállalat úgy dönt, hogy áthelyezi a 2014-nél régebbi adatokat az SQL Server-adatbázisból. Az összes adatnak azonban továbbra is elérhetőnek kell lennie.

Ebben a példában a CETAS-ben exportálja az adatokat, és több külső táblát hoz létre, amelyeket később lekérdezhet. Union utasításokkal rendelkező nézettel lekérdezheti az adatokat, vagy létrehozhat egyetlen külső táblát, és helyettesítő karakterrel kereshet az exportált adatok almappáiban.

Először klónozza az eredeti táblát, mert az adatok exportálását és eltávolítását szeretné szimulálni, de nem feltétlenül szeretné törölni az aktuális adatforrást. Futtassa a következő utasítást:

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

Az első adatfeltárási lekérdezésből tudja, hogy 2014-ből 5551 rekord van. A 2014 előtti elemeket év szerint azonosított mappába kell exportálni. A 2011-ből származó adatok egy 2011nevű mappába kerülnek, és így tovább.

A külső táblák létrehozásához futtassa a következő parancsokat:

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;

A parancsok végrehajtása után frissítse az SSMS Object Explorert. Ezután nyissa meg a Databases>AdventureWorks2022>Tables>Külső táblákat a külső táblák megtekintéséhez.
Győződjön meg arról, hogy a következő mappák jelennek meg az Azure Storage-tárolóban:
A hideg adatok exportálása után törölheti azokat az eredeti tábla helyről.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

A külső táblát tartalmazó adatok lekérdezése

Nézet vagy helyettesítő keresés használatával lekérdezheti az exportált külső adatokat. Minden módszernek vannak előnyei és hátrányai. A nézetmetódus azért ajánlott ismétlődő kérésekhez, mert általában jobban teljesít, és fizikai táblákkal is kombinálható. A helyettesítő karakterek keresési módszere rugalmasabb és könnyebben használható feltárási célokra.

Használjon nézetet az adatok lekérdezéséhez

Most, hogy a régi adatokat exportálta és törölte az adatbázisból, a T-SQL használatával létrehozhat egy nézetet, amely lekérdezi az adatbázis összes külső tábláját és aktuális adatait.

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]

Az eredeti adatfeltárási lekérdezést ezúttal az újonnan létrehozott nézet használatával futtathatja ugyanazokkal az eredményekkel.

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

Helyettesítő karakterek keresése az adatok lekérdezéséhez

Az előző példában egy UNION utasításokkal rendelkező nézetet használt a három külső tábla összekapcsolásához. A kívánt eredmények elérésének másik módja az, hogy helyettesítő karakteres keresést használunk a mappastruktúra, beleértve az almappákat is, adott típusú adatok átvizsgálására.

Az alábbi T-SQL-példa az OPENROWSET használatával keres az ABS_Data adatforrásban és annak almappáiban Parquet-fájlokat.

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]

Mappaeliminációs és metaadat-információk

A külső táblák és az OPENROWSET egyaránt használhatja a filepath függvényt a fájl metaadatain alapuló információk gyűjtésére és szűrésére. A filepath függvény teljes elérési utakat, mappaneveket és fájlneveket ad vissza. Ezeket az információkat a külső tábla és az OPENROWSET parancsok keresési képességeinek javítására használhatja.

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)

Képernyőkép az SSMS-ről, amelyen a filepath függvény látható.

Ha egy adott mappából szeretne adatokat lekérni, és továbbra is használni szeretné a helyettesítő karakterek keresési módszerének funkcióit, a következő lekérdezést használhatja:

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

A végeredmény ugyanaz, de a mappaeliminációs metaadatok használatával a lekérdezés a teljes adatforrás vizsgálata helyett csak a szükséges mappákhoz fér hozzá, ami jobb lekérdezési teljesítményt eredményez. Ezeket az információkat tartsa szem előtt, amikor tárolóarchitektúrákat tervez a PolyBase képességeinek jobb kihasználása érdekében.

Például a következő mappaarchitektúra alapján:

Képernyőkép egy mappaarchitektúra-példáról egy tárolóban.

A következő lekérdezést használhatja:

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

A lekérdezés szempontjából nem számít, hogy az adatforrás mekkora méretű lesz. Az SQL Server csak a kijelölt mappából tölti be, olvassa be és lekérdezi az adatokat, kihagyva az összes többit.

Mivel az adatbázis nem tárol adatokat, az adatbázis-rendszergazdának nem kell külön stratégiát kialakítania az adatok kezeléséhez. A vállalatnak továbbra is meg kell tennie az összes szükséges óvintézkedést az adatok biztonságos karbantartásához, beleértve, de nem kizárólagosan a biztonsági mentéseket, a rendelkezésre állást és az engedélyeket.

Összefoglalás

Ebben a gyakorlatban a CETAS használatával áthelyezte a hideg adatokat egy adatbázisból az Azure Storage-ba, és exportált egy táblát Parquet fájlformátumban. Megtanulta, hogyan kérdezheti le a külső adatokat a feltáráshoz és a teljesítmény optimalizálásához.

A CETAS használatával kombinálhatja az OPENROWSET, a külső táblák, a nézetek, a mintakeresés és a filepath függvényeket. Az adatokat más adatbázisokból, például SQL Serverből, Oracle-ből, Teradata-ból és MongoDB-ből, vagy az Azure Blob Storage-ból, az Azure Data Lake Storage-ból vagy bármilyen S3-kompatibilis objektumtárolóból is elérheti és exportálhatja. A CETAS segít teljesítményű, tartós és skálázható megoldások tervezésében az összes PolyBase által támogatott adatforráshoz.

Visszajelzés

Hasznosnak találta ezt az oldalt?