Mengonfigurasi PolyBase untuk mengakses data eksternal di Azure Blob Storage

Berlaku untuk: SQL Server (khusus Windows) Tidak didukung Azure SQL Database Tidak didukung Azure Synapse Analytics Not supported Analytics Platform System (PDW)

Artikel ini menjelaskan cara menggunakan PolyBase pada instans SQL Server untuk mengkueri data eksternal dalam Azure Blob Storage.

Prasyarat

Jika Anda belum menginstal PolyBase, lihat Penginstalan PolyBase. Artikel penginstalan menjelaskan prasyarat.

SQL Server 2022

Di SQL Server 2022 (16.x), konfigurasikan sumber data eksternal Anda untuk menggunakan konektor baru saat Anda tersambung ke Azure Storage. Tabel di bawah ini meringkas perubahan:

Sumber Data Eksternal Dari Untuk
Azure Blob Storage wasb[s] abs
ADLS Gen2 abfs[s] adls

Mengonfigurasi konektivitas Azure Blob Storage

Pertama, konfigurasikan SQL Server PolyBase untuk menggunakan Azure Blob Storage.

  1. Jalankan sp_configure dengan 'konektivitas hadoop' diatur ke penyedia Azure Blob Storage. Untuk menemukan nilai penyedia, lihat Konfigurasi Konektivitas PolyBase. Secara Default, konektivitas Hadoop diatur ke 7.

    -- Values map to various external data sources.  
    -- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
    -- 2.1 to 2.3 on Windows Server, and Azure Blob Storage  
    sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
    GO
    
    RECONFIGURE
    GO
    
  2. Mulai ulang SQL Server menggunakan services.msc. Memulai ulang SQL Server memulai ulang layanan ini:

    • SQL Server PolyBase Data Movement Service
    • SQL Server Mesin PolyBase

    hentikan dan mulai layanan PolyBase di services.msc

Mengonfigurasi sebuah tabel eksternal

Untuk mengkueri data di sumber data Hadoop, Anda harus menentukan tabel eksternal untuk digunakan dalam kueri Transact-SQL. Langkah-langkah berikut menjelaskan cara mengonfigurasi tabel eksternal.

  1. Membuat kunci master pada database. Kunci master diperlukan untuk mengenkripsi rahasia kredensial.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
    
  2. Buat kredensial lingkup database untuk Azure Blob Storage; IDENTITY bisa apa saja karena tidak digunakan.

    -- IDENTITY: any string (this is not used for authentication to Azure storage).  
    -- SECRET: your Azure storage account key.  
    CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';
    
  3. Buat sumber data eksternal dengan CREATE EXTERNAL DATA SOURCE. Perhatikan bahwa saat menyambungkan ke Azure Storage melalui konektor WASB[s], autentikasi harus dilakukan dengan kunci akun penyimpanan, bukan dengan tanda tangan akses bersama (SAS).

    -- LOCATION:  Azure account storage account name and blob container name.  
    -- CREDENTIAL: The database scoped credential created above.  
    CREATE EXTERNAL DATA SOURCE AzureStorage with (  
          TYPE = HADOOP,
          LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
          CREDENTIAL = AzureStorageCredential  
    );  
    
  4. Buat format file eksternal dengan 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. Buat tabel eksternal yang menunjuk ke data yang disimpan di penyimpanan Azure dengan CREATE EXTERNAL TABLE. Dalam contoh ini, data eksternal berisi data sensor mobil; LOCATION tidak bisa / tetapi /Demo/ karena dalam contoh ini tidak perlu ada sebelumnya.

    -- 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. Membuat statistik pada tabel eksternal.

    CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
    

Kueri PolyBase

Ada tiga fungsi yang cocok untuk PolyBase:

  • Kueri ad hoc terhadap tabel eksternal.
  • Mengimpor data.
  • Mengekspor data.

Kueri berikut memberikan contoh dengan data sensor mobil fiktif.

Kueri ad hoc

Kueri ad hoc berikut menggabungkan relasional dengan data Hadoop. Ini memilih pelanggan yang mengemudi lebih cepat dari 35 mph, dan bergabung dengan data pelanggan terstruktur yang disimpan dalam SQL Server dengan data sensor mobil yang disimpan di 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)  

Mengimpor data dengan PolyBase

Kueri berikut mengimpor data eksternal ke dalam SQL Server. Contoh ini mengimpor data untuk driver cepat ke SQL Server untuk melakukan analisis yang lebih mendalam. Untuk meningkatkan performa, ia memanfaatkan teknologi Columnstore.

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;  

Mengekspor data dengan PolyBase

Kueri berikut mengekspor data dari SQL Server ke Azure Blob Storage. Pertama-tama aktifkan ekspor PolyBase. Kemudian, buat tabel eksternal untuk tujuan sebelum mengekspor data ke dalamnya.

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

Ekspor PolyBase dengan metode ini dapat membuat beberapa file.

Menampilkan objek PolyBase di SSMS

Di SSMS, tabel eksternal ditampilkan dalam folder Terpisah Tabel Eksternal. Sumber data eksternal dan format file eksternal berada dalam subfolder di bawah Sumber Daya Eksternal.

Objek PolyBase di SSMS

Langkah berikutnya

Untuk tutorial selengkapnya tentang membuat sumber data eksternal dan tabel eksternal ke berbagai sumber data, lihat referensi PolyBase Transact-SQL.

Jelajahi cara lain untuk menggunakan dan memantau PolyBase di artikel berikut: