練習 - CREATE EXTERNAL TABLE AS SELECT

10 分鐘

在此練習中，您會使用 CREATE EXTERNAL TABLE AS SELECT （CETAS）來：

將數據表導出為 Parquet。
將非經常性數據移出資料庫到記憶體。
建立外部數據表以存取導出的外部數據。
使用檢視或通配符搜尋作為查詢策略。
使用資料夾刪除和元數據資訊來限制查詢，以改善效能。

先決條件

SQL Server 2025 實例，具有因特網連線能力，以及安裝並啟用 適用於外部數據的 PolyBase 查詢服務 功能，如先前練習所示。
AdventureWorks2022 範例資料庫已還原至您的伺服器，以用於範例數據。
已建立 Blob 記憶體容器的 data Azure 記憶體帳戶。若要建立記憶體，請參閱快速入門：使用 Azure 入口網站上傳、下載及列出 Blob。
在 Azure 中指派的 Azure 角色型存取控制（RBAC） 記憶體 Blob 資料參與者 角色。如需詳細資訊，請參閱指派 Azure 角色以存取 Blob 資料。
Blob 容器 SAS 令牌，其中包含要用於 CETAS 的 READ、 WRITE、 LIST 和 CREATE 許可權。若要建立 SAS 令牌，請參閱建立記憶體容器的共用存取簽章（SAS）令牌。

使用 CETAS 將數據表匯出為 Parquet

假設您與商務分析小組合作，其想要將 2012 年以上的數據從 SQL Server 數據表導出至 Azure Blob 記憶體容器。他們想要在此導出的數據上執行報表查詢，而不是直接查詢 SQL Server。

在 SQL Server 實例上啟用 CETAS。

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

設定選項以允許PolyBase導出的螢幕快照。

執行下列數據探索查詢，以瞭解您想要匯出的數據。在此情況下，您要尋找 2012 或更早版本的數據。您想要從 2011 和 2012 匯出所有數據。
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

如先前練習所示，為資料庫建立資料庫主要密鑰。

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;

建立資料庫範圍認證和外部數據源。 <sas_token>將和 <storageccount> 佔位元取代為您在 Azure 中建立的記憶體帳戶和 SAS 令牌。

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

建立 Parquet 的外部文件格式。

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

使用 CETAS 建立外部數據表。下列查詢會建立名為 ext_data_2011_2012 的外部數據表，並將所有數據從 2011 和 2012 匯出至數據源 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

在 Azure 入口網站中檢查您的 Azure Blob 記憶體。您應該會看到已建立下列結構。 SQL Server 2025 會根據它匯出的數據量和檔格式，自動建立檔名。
您現在可以像一般數據表一樣存取外部數據表。
```
SELECT * FROM ex_data_2011_2012
```

數據現在會匯出至 Parquet，而且可以輕鬆地透過外部資料表存取。商務分析小組可以查詢外部數據表，或將其報告工具指向 Parquet 檔案。

使用 CETAS 將冷數據移出資料庫

為了讓數據保持可管理，您的公司決定從 SQL Server 資料庫移動早於 2014 年的數據。不過，所有數據仍必須可供存取。

在此範例中，您會透過 CETAS 匯出資料，併產生數個外部資料表，以供稍後查詢。您可以使用具有 UNION 語句的檢視來查詢數據，或建立單一外部數據表，並使用通配符來搜尋匯出數據的子資料夾。

首先，複製原始數據表，因為您想要模擬導出和移除數據，但不一定想要刪除目前的數據源。執行以下語句：

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

從第一個數據探索查詢中，您知道 2014 年有 5551 筆記錄。 2014年之前的所有項目都應該導出到依年份識別的資料夾。 2011 中的數據會進入名為 2011的資料夾，依此命名。

若要建立外部資料表，請執行下列命令：

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;

執行這些命令之後，請重新整理 SSMS 物件總管。然後開啟 Databases>AdventureWorks2022>數據表>外部數據表 以查看外部數據表。
確認下列資料夾出現在 Azure 記憶體容器中：

匯出冷數據之後，您可以從原始資料表位置刪除它。

DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014

查詢包含外部數據表的數據

您可以使用檢視或通配符搜尋來查詢導出的外部數據。每個方法都有優點和缺點。建議針對重複的要求使用檢視方法，因為它通常執行得更好，而且也可以與實體數據表結合。通配符搜尋方法更有彈性且更容易用於探索目的。

使用檢視來查詢數據

既然已從資料庫匯出和刪除舊數據，您可以使用 T-SQL 來建立檢視，以查詢資料庫中的所有外部數據表和目前數據。

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]

您可以執行原始數據探索查詢，這次使用新建立的檢視來查看相同的結果。

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

使用通配符搜尋來查詢數據

在上述範例中，您使用具有 UNION 語句的檢視來聯結三個外部數據表。達成所需結果的另一種方法是使用通配符搜尋來掃描特定類型之任何數據的資料夾結構，包括子資料夾。

下列 T-SQL 範例會使用 OPENROWSET 來搜尋 Parquet 檔案的 ABS_Data 數據源，包括其子資料夾。

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]

資料夾刪除和元數據資訊

外部數據表和 OPENROWSET 都可以使用函 filepath 式，根據檔案元數據收集及篩選資訊。函 filepath 式會傳回完整路徑、資料夾名稱和檔名。您可以使用該資訊來改善外部資料表和 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)

顯示 filepath 函式的 SSMS 螢幕快照。

如果您想要從特定資料夾擷取數據，但仍使用通配符搜尋方法的功能，您可以使用下列查詢：

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

最終結果相同，但藉由使用資料夾消除元數據，您的查詢只會存取所需的資料夾，而不是掃描整個數據源，產生更佳的查詢效能。當您設計記憶體架構以更妥善地使用PolyBase功能時，請記住這項資訊。

例如，假設有下列資料夾架構：

顯示記憶體容器中資料夾架構範例的螢幕快照。

您可以使用下列查詢：

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

針對此查詢的目的，數據源成長程度並不重要。 SQL Server 只會載入、讀取和查詢所選資料夾中的數據，略過所有其他數據。

因為資料庫中沒有儲存任何數據，因此資料庫管理員不需要設計特定策略來管理此數據。公司仍必須採取所有必要的預防措施，以安全地維護數據，包括但不限於備份、可用性和許可權。

總結

在此練習中，您已使用 CETAS 將非經常性數據移出資料庫到 Azure 記憶體，並將數據表匯出為 Parquet 檔格式。您已瞭解如何查詢外部數據以進行探索，並優化效能。

您可以使用 CETAS 來結合 OPENROWSET、外部數據表、檢視、通配符搜尋和 filepath 函式。您可以從 SQL Server、Oracle、Teradata 和 MongoDB 等其他資料庫，或從 Azure Blob 記憶體、Azure Data Lake Storage 或任何 S3 相容的物件記憶體存取和導出數據。 CETAS 可協助您在所有 PolyBase 支援的數據源上設計高效能、持久且可調整的解決方案。

意見反應

此頁面對您有幫助嗎？