Exercițiu - CREEAZĂ UN TABEL EXTERN CA SELECT

10 minute

În acest exercițiu, folosești CREATE EXTERNAL TABLE AS SELECT (CETAS) pentru a:

Exportați un tabel ca tabel ca tabel.
Mutați datele reci dintr-o bază de date în stocare.
Creează un tabel extern pentru a accesa datele externe exportate.
Folosește vizualizări sau căutare wildcard ca strategii de interogare.
Limitează interogările folosind eliminarea folderelor și informațiile despre metadate pentru a îmbunătăți performanța.

Prerequisites

O instanță SQL Server 2025 cu conectivitate la internet și funcția PolyBase Query Service for External Data instalată și activată, ca în exercițiile anterioare.
Baza de date de exemple AdventureWorks2022 restaurată pe serverul tău pentru a fi folosită ca date de probă.
Un cont Azure Storage cu un container Blob Storage numit data creat. Pentru a crea spațiul de stocare, vezi Quickstart: Upload, download, and list blobs cu portalul Azure.
Rolul Azure Storage Blob Data Contributor de control al accesului bazat pe rol (RBAC) este atribuit în Azure. Pentru mai multe informații, vezi Atribui un rol Azure pentru acces la datele blob.
Un token SAS pentru container blob cu permisiuni READ, WRITE, LIST și CREATE care trebuie folosit pentru CETAS. Pentru a crea tokenul SAS, vezi Creează tokenuri cu semnătură de acces partajat (SAS) pentru containerele tale de stocare.

Folosește CETAS pentru a exporta o masă ca parchet

Imaginează-ți că lucrezi cu o echipă de business analytics care dorește să exporte date mai vechi de 2012 dintr-un tabel SQL Server într-un container Azure Blob Storage. Ei doresc să ruleze interogările de raport pe aceste date exportate, în loc să interogheze direct SQL Server.

Activează CETAS pe instanța SQL Server.

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

Captură de ecran cu configurarea opțiunii de a permite exportul PolyBase.

Executați următoarea interogare de explorare a datelor pentru a înțelege ce date doriți să exportați. În acest caz, cauți date din 2012 sau mai vechi. Vrei să exporti toate datele din 2011 și 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Creează o cheie principală a bazei de date pentru baza de date, ca în exercițiile anterioare.

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;

Creează datele cu scop de bază de date și sursa externă de date. Înlocuiește <sas_token> și <storageccount> placeholderii cu contul de stocare și tokenul SAS pe care l-ai creat în 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
);

Creează formatul de fișier extern pentru Parquet.

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

Creează tabelul extern folosind CETAS. Interogarea următoare creează un tabel extern denumit ext_data_2011_2012 și exportă toate datele din 2011 și 2012 către locația specificată de sursa ABS_Datade date.

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

Verifică stocarea Azure Blob în portalul Azure. Ar trebui să vezi următoarea structură creată. SQL Server 2025 creează automat numele fișierului în funcție de cantitatea de date exportate și formatul fișierului.
Acum poți accesa masa externă ca la o masă obișnuită.
```
SELECT * FROM ex_data_2011_2012
```

Datele sunt acum exportate în Parquet și sunt ușor accesibile prin tabelul extern. Echipa de business analytics poate interoga tabelul extern sau poate indica instrumentul de raportare către fișierul Parquet.

Folosește CETAS pentru a muta date reci din baza de date

Pentru a menține datele gestionabile, compania dumneavoastră decide să mute datele mai vechi de 2014 din baza de date SQL Server. Totuși, toate datele trebuie să fie încă accesibile.

Pentru acest exemplu, exporti datele prin CETAS și generezi mai multe tabele externe pe care le poți interoga ulterior. Poți folosi o vizualizare cu instrucțiuni UNION pentru a interoga datele sau poți crea un singur tabel extern și folosi un wildcard pentru a căuta prin subfolderele datelor exportate.

În primul rând, clonează tabelul original, pentru că vrei să simulezi exportul și eliminarea datelor, dar nu neapărat să ștergi sursa actuală de date. Rulați următoarea declarație:

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

Din prima interogare de explorare a datelor, știi că există 5551 de înregistrări din 2014. Tot ce este înainte de 2014 ar trebui exportat într-un folder identificat după an. Datele din 2011 merg într-un folder numit 2011, și așa mai departe.

Pentru a crea tabelele externe, rulează următoarele comenzi:

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;

După ce executi aceste comenzi, reîmprospătează SSMS Object Explorer. Apoi deschideTabelele>Externe AdventureWorks2022>Baze> de Date pentru a vedea tabelele externe.
Confirmați că următoarele foldere apar în containerul Azure Storage:
După ce datele reci sunt exportate, le poți șterge din locația originală a tabelului.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Interogarea datelor care includ tabelul extern

Poți folosi o vizualizare sau o căutare wildcard pentru a interoga datele externe exportate. Fiecare metodă are avantaje și dezavantaje. Metoda de vizualizare este recomandată pentru cereri repetitive deoarece de obicei performează mai bine și poate fi combinată și cu tabele fizice. Metoda de căutare wildcard este mai flexibilă și mai ușor de folosit în scopuri de explorare.

Folosește o vizualizare pentru a interoga datele

Acum că datele vechi sunt exportate și șterse din baza de date, poți folosi T-SQL pentru a crea o vizualizare care interoghează toate tabelele externe și datele curente din baza ta de date.

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]

Poți rula interogarea originală de explorare a datelor, de data aceasta folosind vizualizarea nou creată, pentru a vedea aceleași rezultate.

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

Folosiți o căutare wildcard pentru a interoga datele

În exemplul anterior, ai folosit o vizualizare cu instrucțiuni UNION pentru a uni cele trei tabele externe. O altă modalitate de a obține rezultatele dorite este utilizarea unei căutări wildcard pentru a scana structura folderelor, inclusiv subfolderele, pentru orice date de un anumit tip.

Următorul exemplu T-SQL folosește OPENROWSET pentru a căuta fișiere Parquet ABS_Data în sursa de date, inclusiv în subfolderele sale.

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]

Eliminarea folderelor și informații despre metadate

Atât tabelele externe, cât și OPENROWSET pot folosi funcția filepath pentru a colecta și filtra informații pe baza metadatelor fișierelor. Funcția filepath returnează căi complete, nume de foldere și nume de fișiere. Poți folosi aceste informații pentru a îmbunătăți capacitățile de căutare atât ale comenzilor tabelului extern, cât și ale comenzilor OPENROWSET.

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)

Captură de ecran cu SSMS care arată funcția de cale a fișierului.

Dacă vrei să recuperezi date dintr-un anumit folder și totuși să folosești funcționalitatea metodei de căutare wildcard, poți folosi următoarea interogare:

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

Rezultatele finale sunt aceleași, dar folosind metadatele de eliminare a folderelor, interogarea ta accesează doar folderele necesare în loc să scaneze întreaga sursă de date, ceea ce produce o performanță mai bună a interogării. Ține cont de aceste informații când proiectezi arhitecturi de stocare pentru a folosi mai bine capabilitățile PolyBase.

De exemplu, având în vedere următoarea arhitectură de foldere:

Captură de ecran care arată un exemplu de arhitectură a folderelor într-un container de stocare.

Ai putea folosi următoarea interogare:

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

În scopul acestei interogări, nu contează cât de mare crește sursa de date. SQL Server încarcă, citește și interoghează doar datele din folderul selectat, sărind peste toate celelalte.

Deoarece nu sunt stocate date în baza de date, administratorul bazei de date nu trebuie să conceapă o strategie specifică pentru a gestiona aceste date. Compania trebuie totuși să ia toate măsurile de precauție necesare pentru a menține datele în siguranță, inclusiv, dar fără a se limita la, backup-uri, disponibilitate și permisiuni.

Rezumat

În acest exercițiu, ai folosit CETAS pentru a muta date reci dintr-o bază de date în Azure Storage și a exporta un tabel ca format de fișier Parquet. Ai învățat metode de a interoga datele externe pentru explorare și de optimizare a performanței.

Poți folosi CETAS pentru a combina OPENROWSET, tabele externe, vizualizări, căutare wildcard și funcții de filepath. Poți accesa și exporta date din alte baze de date precum SQL Server, Oracle, Teradata și MongoDB, sau din Azure Blob Storage, Azure Data Lake Storage sau orice stocare obiect compatibilă cu S3. CETAS vă poate ajuta să proiectați soluții performante, durabile și scalabile pentru toate sursele de date suportate de PolyBase.

Feedback

Această pagină a fost utilă?