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

適用於： SQL Server (僅限 Windows) Azure SQL 資料庫 Azure Synapse Analytics Analytics Platform System (PDW)

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

Prerequisites

如果您尚未安裝 PolyBase，請參閱 PolyBase 安裝。 安裝文章說明必要條件。

SQL Server 2022

在 SQL Server 2022 (16.x) 中，將外部資料來源設定為在連線到 Azure 儲存體時使用新的連接器。 下表摘述差異：

外部資料來源	寄件者	收件者
Azure Blob 儲存體	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

設定 Azure Blob 儲存體連線

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

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

   • SQL Server PolyBase Data Movement Service
   • SQL Server PolyBase Engine

   

設定外部資料表

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

1. 在資料庫上建立主要金鑰。 將認證祕密加密時需要主要金鑰。

   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 連接器連線到 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 (LOCATION='/Demo/',
         DATA_SOURCE = AzureStorage,  
         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  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- 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 (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      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 T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

使用此方法所進行的 PolyBase 匯出可能會建立多個檔案。

在 SSMS 中檢視 PolyBase 物件

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

下一步

如需將外部資料來源和外部資料表建立至各種資料來源的其他教學課程，請參閱 PolyBase Transact-SQL 參考。

在下列文章中，探索更多使用和監視 PolyBase 的方式：

• PolyBase 向外延展群組。
• PolyBase 疑難排解.
• CREATE EXTERNAL DATA SOURCE