Cvičení – VYTVOŘENÍ EXTERNÍ TABULKY JAKO SELECT

10 min

V tomto cvičení použijete funkci CREATE EXTERNAL TABLE AS SELECT (CETAS) k:

Exportujte tabulku jako Parquet.
Přesunutí studených dat z databáze do úložiště
Vytvořte externí tabulku pro přístup k exportovaným externím datům.
Použití zobrazení nebo vyhledávání pomocí zástupných znaků jako strategií dotazů
Omezte dotazy pomocí odstranění složek a informací o metadatech za účelem zlepšení výkonu.

Požadavky

Instance SQL Serveru 2025 s připojením k internetu a funkcí PolyBase Query Service pro externí data nainstalovanou a povolenou jako v předchozích cvičeních.
Ukázková databáze AdventureWorks2022 byla obnovena na vašem serveru pro použití jako ukázková data.
Účet služby Azure Storage s kontejnerem Blob Storage s názvem data vytvořen. Pokud chcete vytvořit úložiště, přečtěte si Rychlý start: Nahrání, stažení a výpis objektů blob pomocí webu Azure Portal.
Řízení přístupu na základě rolí v Azure (RBAC), role Přispěvatele dat objektů blob ve službě Storage , přiřazena v Azure. Další informace najdete v tématu Přiřazení role Azure pro přístup k datům objektů blob.
Token SAS pro kontejner objektů blob s oprávněními pro ČTENÍ, ZÁPIS, VÝPISa VYTVÁŘENÍ k použití pro CETAS. Pokud chcete vytvořit token SAS, přečtěte si téma Vytvoření tokenů sdíleného přístupového podpisu (SAS) pro kontejnery úložiště.

Použití CETAS k exportu tabulky ve formátu Parquet

Představte si, že pracujete s týmem obchodní analýzy, který chce exportovat data starší než 2012 z tabulky SQL Serveru do kontejneru Azure Blob Storage. Chtějí spouštět dotazy sestavy na tato exportovaná data místo přímého dotazování SQL Serveru.

Povolte CETAS v instanci SQL Serveru.

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

snímek obrazovky konfigurace možnosti povolení exportu PolyBase

Spusťte následující dotaz pro zkoumání dat, abyste pochopili, jaká data chcete exportovat. V tomto případě hledáte data z roku 2012 nebo starší. Chcete exportovat všechna data z let 2011 a 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Vytvořte hlavní klíč databáze pro databázi stejně jako v předchozích cvičeních.

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;

Vytvořte přihlašovací údaje s vymezeným oborem databáze a externí zdroj dat. Nahraďte zástupné symboly <sas_token> a <storageccount> účtem úložiště a tokenem SAS, který jste vytvořili v 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
);

Vytvořte formát externího souboru pro Parquet.

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

Vytvořte externí tabulku pomocí CETAS. Následující dotaz vytvoří externí tabulku s názvem ext_data_2011_2012 a exportuje všechna data z roku 2011 a 2012 do umístění určeného zdrojem dat 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

Zkontrolujte službu Azure Blob Storage na webu Azure Portal. Měla by se zobrazit následující struktura vytvořená. SQL Server 2025 automaticky vytvoří název souboru na základě toho, kolik dat exportuje a formát souboru.
K externí tabulce teď můžete přistupovat jako k běžné tabulce.
```
SELECT * FROM ex_data_2011_2012
```

Data se teď exportují do Parquet a jsou snadno přístupná prostřednictvím externí tabulky. Tým business analytiky může provádět dotazy na externí tabulku nebo nasměrovat nástroj pro vytváření sestav na soubor formátu Parquet.

Přesun studených dat z databáze pomocí CETAS

Aby byla data spravovatelná, vaše společnost se rozhodne přesunout data starší než 2014 z databáze SQL Serveru. Všechna data ale musí být stále přístupná.

V tomto příkladu exportujete data prostřednictvím CETAS a vygenerujete několik externích tabulek, které můžete dotazovat později. Pomocí zobrazení s příkazy UNION můžete dotazovat data nebo vytvořit jednu externí tabulku a pomocí zástupného znaku prohledat podsložky exportovaných dat.

Nejprve naklonujte původní tabulku, protože chcete simulovat export a odebrání dat, ale nechcete nutně odstranit aktuální zdroj dat. Spusťte následující příkaz:

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

Z prvního dotazu na zkoumání dat víte, že z roku 2014 existuje 5551 záznamů. Všechno před rokem 2014 by se mělo exportovat do složky identifikované rokem. Data z roku 2011 se přejdou do složky s názvem 2011atd.

Pokud chcete vytvořit externí tabulky, spusťte následující příkazy:

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;

Po spuštění těchto příkazů aktualizujte SSMS Průzkumník objektů. Potom otevřete Databases>AdventureWorks2022>Tables>External Tables, abyste viděli externí tabulky.
Ověřte, že se v kontejneru Azure Storage zobrazují následující složky:
Po exportu studených dat je můžete odstranit z původního umístění tabulky.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Dotazování dat, která obsahují externí tabulku

K dotazování na exportovaná externí data můžete použít pohled nebo vyhledávání pomocí zástupných znaků. Každá metoda má výhody a nevýhody. Metoda zobrazení se doporučuje pro opakované požadavky, protože obvykle funguje lépe a lze ji také kombinovat s fyzickými tabulkami. Metoda vyhledávání pomocí zástupných znaků je flexibilnější a snadněji se používá pro účely průzkumu.

Použijte zobrazení k dotazování dat

Teď, když jsou stará data exportována a odstraněna z databáze, můžete pomocí jazyka T-SQL vytvořit zobrazení, které se dotazuje na všechny externí tabulky a aktuální data ve vaší databázi.

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]

Pomocí nově vytvořeného zobrazení můžete spustit původní dotaz pro zkoumání dat, abyste viděli stejné výsledky.

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

Použití vyhledávání se zástupným znakem k dotazování dat

V předchozím příkladu jste použili zobrazení s příkazy UNION ke spojení tří externích tabulek. Dalším způsobem, jak dosáhnout požadovaných výsledků, je použít vyhledávání se zástupným znakem ke kontrole struktury složek, včetně podsložek, pro všechna data určitého typu.

Následující příklad T-SQL používá OPENROWSET k vyhledávání ve zdroji dat ABS_Data, včetně jejích podsložek, pro soubory Parquet.

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]

Odstranění složky a informace o metadatech

Externí tabulky i OPENROWSET mohou použít funkci filepath ke shromažďování a filtrování informací na základě metadat souborů. Funkce filepath vrátí úplné cesty, názvy složek a názvy souborů. Tyto informace můžete použít ke zlepšení možností hledání externí tabulky i příkazů 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)

snímek obrazovky SSMS zobrazující funkci cesta k souboru

Pokud chcete načíst data z konkrétní složky a přesto používat funkci metody vyhledávání se zástupnými cardy, můžete použít následující dotaz:

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

Koncové výsledky jsou stejné, ale pomocí metadat odstranění složky váš dotaz přistupuje pouze k požadovaným složkám místo prohledávání celého zdroje dat, což vede k lepšímu výkonu dotazů. Tyto informace mějte na paměti při návrhu architektur úložiště pro lepší použití funkcí PolyBase.

Například vzhledem k následující architektuře složek:

Snímek obrazovky znázorňující příklad architektury složek v kontejneru úložiště

Můžete použít následující dotaz:

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

Pro účely tohoto dotazu nezáleží na tom, jak velký zdroj dat roste. SQL Server načítá, čte a dotazuje se jenom na data z vybrané složky a přeskočí všechny ostatní.

Vzhledem k tomu, že v databázi nejsou uložená žádná data, správce databáze nemusí navrhnout konkrétní strategii pro správu těchto dat. Společnost stále musí provést všechna požadovaná opatření k bezpečné údržbě dat, včetně zálohování, dostupnosti a oprávnění, a to i mimo jiné.

Shrnutí

V tomto cvičení jste použili CETAS k přesunu studených dat z databáze do Azure Storage a exportování tabulky jako formátu souboru Parquet. Naučili jste se způsoby dotazování externích dat pro zkoumání a optimalizaci výkonu.

Funkci CETAS můžete použít ke kombinování funkcí OPENROWSET, externích tabulek, zobrazení, vyhledávání pomocí zástupných znaků a cest k souborům. Můžete přistupovat k datům z jiných databází, jako jsou SQL Server, Oracle, Teradata a MongoDB, nebo z Azure Blob Storage, Azure Data Lake Storage nebo jakéhokoli úložiště objektů kompatibilního s S3. CETAS vám může pomoct navrhovat výkonná, odolná a škálovatelná řešení ve všech podporovaných zdrojích dat PolyBase.

Váš názor

Byla tato stránka užitečná?