Latihan - BUAT TABEL EKSTERNAL SEBAGAI PILIH

10 menit

Dalam latihan ini, Anda menggunakan CREATE EXTERNAL TABLE AS SELECT (CETAS) untuk:

Ekspor tabel sebagai Parquet.
Pindahkan data dingin dari database ke penyimpanan eksternal.
Buat tabel eksternal untuk mengakses data eksternal yang diekspor.
Gunakan tampilan atau pencarian wildcard sebagai strategi kueri.
Batasi kueri dengan menggunakan informasi penghapusan folder dan metadata untuk meningkatkan performa.

Prasyarat

Instans SQL Server 2025 dengan konektivitas internet dan fitur PolyBase Query Service for External Data diinstal dan diaktifkan seperti untuk latihan sebelumnya.
Database sampel AdventureWorks2022 dipulihkan ke server Anda untuk digunakan untuk data sampel.
Akun Azure Storage dengan kontainer Blob Storage bernama data dibuat. Untuk membuat penyimpanan, lihat Mulai Cepat: Mengunggah, mengunduh, dan mencantumkan blob dengan portal Microsoft Azure.
Peran Kontributor Data Blob Penyimpanan kontrol akses berbasis peran Azure (RBAC) yang ditetapkan di Azure. Untuk informasi selengkapnya, lihat Menetapkan peran Azure untuk akses ke data blob.
Token SAS untuk kontainer blob dengan izin READ, WRITE, LIST, dan CREATE yang akan digunakan untuk CETAS. Untuk membuat token SAS, lihat Membuat token tanda tangan akses bersama (SAS) untuk kontainer penyimpanan Anda.

Gunakan CETAS untuk mengekspor tabel sebagai Parquet

Bayangkan Anda bekerja dengan tim analitik bisnis yang ingin mengekspor data yang lebih lama dari 2012 dari tabel SQL Server ke kontainer Azure Blob Storage. Mereka ingin menjalankan kueri laporan mereka pada data yang diekspor ini daripada langsung mengkueri SQL Server.

Aktifkan CETAS pada instans SQL Server.

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

Cuplikan layar mengonfigurasi opsi untuk mengizinkan ekspor PolyBase.

Jalankan kueri eksplorasi data berikut untuk memahami data apa yang ingin Anda ekspor. Dalam hal ini, Anda mencari data yang berasal dari 2012 atau yang lebih lama. Anda ingin mengekspor semua data dari 2011 dan 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Buat kunci master database untuk database, seperti pada latihan sebelumnya.

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;

Buat kredensial lingkup database dan sumber data eksternal. Ganti <sas_token> dan <storageccount> dengan akun penyimpanan dan token SAS yang Anda buat di 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
);

Buat format file eksternal untuk Parquet.

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

Buat tabel eksternal menggunakan CETAS. Kueri berikut membuat tabel eksternal bernama ext_data_2011_2012 dan mengekspor semua data dari 2011 dan 2012 ke lokasi yang ditentukan oleh sumber ABS_Datadata .

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

Periksa Azure Blob Storage Anda di portal Microsoft Azure. Anda akan melihat struktur berikut dibuat. SQL Server 2025 secara otomatis membuat nama file berdasarkan berapa banyak data yang diekspor dan format file.
Sekarang Anda dapat mengakses tabel eksternal seperti tabel biasa.
```
SELECT * FROM ex_data_2011_2012
```

Data sekarang diekspor ke Parquet dan mudah diakses melalui tabel eksternal. Tim analitik bisnis dapat melakukan kueri pada tabel eksternal atau mengarahkan alat pelaporan mereka ke file Parquet.

Menggunakan CETAS untuk memindahkan data dingin dari database

Agar data tetap dapat dikelola, perusahaan Anda memutuskan untuk memindahkan data yang lebih lama dari 2014 dari database SQL Server. Namun, semua data masih harus dapat diakses.

Untuk contoh ini, Anda mengekspor data melalui CETAS dan menghasilkan beberapa tabel eksternal yang dapat Anda kueri nanti. Anda bisa menggunakan tampilan dengan pernyataan UNION untuk mengkueri data, atau membuat satu tabel eksternal dan menggunakan kartubebas untuk mencari subfolder data yang diekspor.

Pertama, kloning tabel asli, karena Anda ingin mensimulasikan mengekspor dan menghapus data tetapi tidak selalu ingin menghapus sumber data saat ini. Jalankan pernyataan berikut:

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

Dari kueri eksplorasi data pertama, Anda tahu ada 5551 rekaman dari 2014. Semuanya sebelum 2014 harus diekspor ke folder yang diidentifikasi berdasarkan tahun. Data dari 2011 masuk ke folder bernama 2011, dan sebagainya.

Untuk membuat tabel eksternal, jalankan perintah berikut:

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;

Setelah Anda menjalankan perintah ini, refresh SSMS Object Explorer. Kemudian bukaTabel Eksternal>AdventureWorks2022>> untuk melihat tabel eksternal.
Konfirmasikan bahwa folder berikut muncul di kontainer Azure Storage:

Setelah data dingin diekspor, Anda dapat menghapusnya dari lokasi tabel asli.

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

Mengkueri data yang menyertakan tabel eksternal

Anda bisa menggunakan pandangan atau pencarian karakter pengganti untuk melakukan kueri pada data eksternal yang diekspor. Setiap metode memiliki kelebihan dan kekurangan. Metode tampilan direkomendasikan untuk permintaan berulang karena biasanya berkinerja lebih baik, dan juga dapat dikombinasikan dengan tabel fisik. Metode pencarian wildcard lebih fleksibel dan lebih mudah dipakai untuk tujuan eksplorasi.

Menggunakan tampilan untuk mengkueri data

Sekarang setelah data lama diekspor dan dihapus dari database, Anda bisa menggunakan T-SQL untuk membuat tampilan yang mengkueri semua tabel eksternal dan data saat ini dalam database Anda.

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]

Anda bisa menjalankan kueri eksplorasi data asli, kali ini menggunakan tampilan yang baru dibuat, untuk melihat hasil yang sama.

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

Menggunakan pencarian karakter pengganti untuk kueri data

Dalam contoh sebelumnya, Anda menggunakan tampilan dengan pernyataan UNION untuk menggabungkan tiga tabel eksternal. Cara lain untuk mencapai hasil yang diinginkan adalah dengan menggunakan pencarian wildcard untuk menelusuri struktur folder, termasuk subfolder, untuk data apa pun dari jenis tertentu.

Contoh T-SQL berikut menggunakan OPENROWSET untuk mencari file Parquet di seluruh sumber data ABS_Data, termasuk subfoldernya.

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]

Eliminasi folder dan informasi metadata

Baik tabel eksternal maupun OPENROWSET dapat menggunakan filepath fungsi untuk mengumpulkan dan memfilter informasi berdasarkan metadata file. Fungsi filepath mengembalikan jalur lengkap, nama folder, dan nama file. Anda dapat menggunakan informasi tersebut untuk meningkatkan kemampuan pencarian tabel eksternal dan perintah 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)

Cuplikan layar SSMS memperlihatkan fungsi jalur file.

Jika Anda ingin mengambil data dari folder tertentu dan masih menggunakan fungsi pencarian wildcard, Anda bisa menggunakan kueri berikut:

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

Hasil akhirnya sama, tetapi dengan menggunakan metadata eliminasi folder, kueri Anda hanya mengakses folder yang diperlukan alih-alih memindai seluruh sumber data, menghasilkan performa kueri yang lebih baik. Ingatlah informasi ini saat Anda merancang arsitektur penyimpanan untuk menggunakan kemampuan PolyBase dengan lebih baik.

Misalnya, dengan arsitektur folder berikut:

Cuplikan layar memperlihatkan contoh arsitektur folder dalam kontainer penyimpanan.

Anda bisa menggunakan kueri berikut:

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

Untuk tujuan kueri ini, tidak masalah seberapa besar sumber data tumbuh. SQL Server memuat, membaca, dan mengkueri hanya data dari folder yang dipilih, melewati semua yang lain.

Karena tidak ada data yang disimpan dalam database, administrator database tidak perlu merancang strategi tertentu untuk mengelola data ini. Perusahaan masih harus mengambil semua tindakan pencegahan yang diperlukan untuk mempertahankan data dengan aman, termasuk tetapi tidak terbatas pada pencadangan, ketersediaan, dan izin.

RINGKASAN

Dalam latihan ini, Anda menggunakan CETAS untuk memindahkan data dingin dari database ke Azure Storage dan mengekspor tabel sebagai format file Parquet. Anda mempelajari cara untuk mengkueri data eksternal untuk eksplorasi dan mengoptimalkan performa.

Anda dapat menggunakan CETAS untuk menggabungkan OPENROWSET, tabel eksternal, tampilan, pencarian karakter pengganti, dan fungsi filepath. Anda dapat mengakses dan mengekspor data dari database lain seperti SQL Server, Oracle, Teradata, dan MongoDB, atau dari Azure Blob Storage, Azure Data Lake Storage, atau penyimpanan objek yang kompatibel dengan S3. CETAS dapat membantu Anda merancang solusi berkinerja, tahan lama, dan dapat diskalakan di semua sumber data yang didukung PolyBase.

Saran dan Komentar

Apakah halaman ini membantu?