設定 PolyBase 存取 Azure Blob 儲存體中的外部資料

適用於： SQL Server 2016（13.x）及更新版本 - 僅限 Windows

本文說明如何在 SQL Server 執行個體上使用 PolyBase 來查詢位於 Azure Blob 儲存體中的外部資料。

Prerequisites

如果你還沒安裝 PolyBase，請參考 Windows 上的 PolyBase 安裝。 安裝文章說明必要條件。

SQL Server 2022

在 SQL Server 2022 (16.x) 中，將外部資料來源設定為在連線到 Azure 儲存體時使用新的連接器。 下表彙總這些變更。

外部資料來源	寄件者	收件者
Azure Blob 儲存體	wasb[s]	腹肌
ADLS 第 2 代	abfs[s]	adls

設定 Azure Blob 儲存體連線

首先，設定 SQL Server PolyBase 使用 Azure Blob 儲存體。

1. 使用 services.msc 重新啟動 SQL Server。 重新啟動 SQL Server 時，會重新啟動下列服務︰

   • SQL Server PolyBase 資料移動服務
   • SQL Server PolyBase Engine（SQL 資料庫伺服器 PolyBase 引擎）

   

設定外部資料表

若要查詢 Hadoop 資料來源中的資料，您必須定義要在 Transact-SQL 查詢中使用的外部資料表。 下列步驟描述如何設定外部資料表。

1. 在資料庫上建立一個資料庫主金鑰（DMK）。 DMK 是加密認證密碼所必需的。

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
   

2. 使用共用存取簽章 (SAS)，為 Azure Blob 儲存體建立資料庫範圍資格證書；IDENTITY 可以是任何東西，因為它不會被使用。

   CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
        -- Remove ? from the beginning of the SAS token
        SECRET = '<azure_shared_access_signature>';
   

3. 使用 CREATE EXTERNAL DATA SOURCE 建立外部資料來源。 透過 WASB[s] 連接器連接 Azure 儲存裝置時，使用共享存取簽章（SAS）進行驗證。

   -- LOCATION:  Azure account storage account name and blob container name.
   -- CREDENTIAL: The database scoped credential created above.
   CREATE EXTERNAL DATA SOURCE AzureStorage
   WITH (
       LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
       CREDENTIAL = AzureStorageCredential
   );
   

4. 使用 CREATE EXTERNAL FILE FORMAT 建立外部檔案格式。

   -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   CREATE EXTERNAL FILE FORMAT TextFileFormat
   WITH (
       FORMAT_TYPE = DELIMITEDTEXT,
       FORMAT_OPTIONS (FIELD_TERMINATOR = '|', USE_TYPE_DEFAULT = TRUE)
   );
   

5. 使用 CREATE EXTERNAL TABLE 建立外部資料表以指向 Azure 儲存體中所儲存的資料。 在此範例中，外部資料包含汽車感應器資料；LOCATION 不能為 /，但如本範例中的 /Demo/ 不需要事前存在。

   -- LOCATION: path to file or directory that contains the data (relative to HDFS root).
   CREATE EXTERNAL TABLE [dbo].[CarSensor_Data]
   (
       SensorKey INT NOT NULL,
       CustomerKey INT NOT NULL,
       GeographyKey INT NULL,
       Speed FLOAT NOT NULL,
       YearMeasured INT NOT NULL
   )
   WITH (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       FILE_FORMAT = TextFileFormat
   );
   

6. 在外部資料表上建立統計資料。

   CREATE STATISTICS StatsForSensors
   ON CarSensor_Data(CustomerKey, Speed);
   

PolyBase 查詢

PolyBase 適合用於三個功能︰

• 針對外部資料表進行的臨機操作查詢。
• 匯入資料。
• 匯出資料。

下列查詢會提供具有虛構車輛感應器資料的範例。

特定查詢

下列臨時查詢將關聯式資料與 Hadoop 資料聯結。 其會選取開車速度超過 35 mph 的客戶，並聯結儲存在 SQL Server 中的結構化客戶資料與儲存在 Hadoop 中的車輛感應器資料。

SELECT DISTINCT Insured_Customers.FirstName,
                Insured_Customers.LastName,
                Insured_Customers.YearlyIncome,
                CarSensor_Data.Speed
FROM Insured_Customers,
    CarSensor_Data
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey
    AND CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC
OPTION (FORCE EXTERNALPUSHDOWN); -- or OPTION (DISABLE EXTERNALPUSHDOWN)

使用 PolyBase 載入資料

下列查詢會將外部資料匯入至 SQL Server。 此範例會將快速驅動程式的資料匯入至 SQL Server，以執行更深入的分析。 為了提升效能，它採用了欄位儲存技術。

SELECT DISTINCT Insured_Customers.FirstName,
                Insured_Customers.LastName,
                Insured_Customers.YearlyIncome,
                Insured_Customers.MaritalStatus
INTO Fast_Customers
FROM Insured_Customers
    INNER JOIN (SELECT *
                FROM CarSensor_Data
                WHERE Speed > 35
    ) AS SensorD
        ON Insured_Customers.CustomerKey = SensorD.CustomerKey
ORDER BY YearlyIncome;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers
ON Fast_Customers;

使用 PolyBase 匯出資料

下列查詢會將資料從 SQL Server 匯出至 Azure Blob 儲存體。 先啟用 PolyBase 匯出。 接著建立適用於目的地的外部資料表，再將資料匯出至其中。

-- Enable INSERT into external table
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009]
(
    FirstName CHAR (25) NOT NULL,
    LastName CHAR (25) NOT NULL,
    YearlyIncome FLOAT NULL,
    MaritalStatus CHAR (1) NOT NULL
)
WITH (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    FILE_FORMAT = TextFileFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.
INSERT INTO dbo.FastCustomer2009
SELECT T.*
FROM Insured_Customers AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

用這種方法匯出 PolyBase 可能會產生多個檔案。

在 SSMS 中檢視 PolyBase 物件

在 SSMS 中，外部資料表會顯示在個別的資料夾 [外部資料表] 中。 外部資料來源和外部檔案格式會在 [外部資源] 下方的子資料夾中。

相關內容

• PolyBase Transact-SQL 參考
• PolyBase 彈性擴展群組
• 監視 PolyBase 並進行疑難排解
• CREATE EXTERNAL DATA SOURCE （Transact-SQL）