Muat data ritel Contoso ke kumpulan SQL khusus di Azure Synapse Analytics

Artikel
06/01/2023

Dalam tutorial ini, Anda belajar menggunakan perintah PolyBase dan T-SQL untuk memuat dua tabel dari data ritel Contoso ke dalam kumpulan SQL khusus.

Dalam tutorial ini, Anda akan:

Mengonfigurasi PolyBase untuk memuat dari penyimpanan blob Azure
Memuat data publik ke dalam database Anda
Lakukan pengoptimalan setelah pemuatan selesai.

Sebelum Anda mulai

Untuk menjalankan tutorial ini, Anda memerlukan akun Azure yang sudah memiliki kumpulan SQL khusus. Jika Anda tidak memiliki gudang data tersedia, lihat Buat gudang data dan atur aturan firewall tingkat server.

Konfigurasikan sumber data

PolyBase menggunakan objek eksternal T-SQL untuk menentukan lokasi dan atribut data eksternal. Definisi objek eksternal disimpan dalam kumpulan SQL khusus. Data disimpan secara eksternal.

Buat info masuk

Lompati langkah ini jika Anda memuat data publik Contoso. Anda tidak memerlukan akses aman ke data publik karena data tersebut sudah dapat diakses oleh siapa saja.

Jangan lompati langkah ini jika Anda menggunakan tutorial ini sebagai templat untuk memuat data Anda sendiri. Untuk mengakses data melalui info masuk, gunakan skrip berikut ini untuk membuat info masuk yang dicakup database. Lalu gunakan saat menentukan lokasi sumber data.

-- A: Create a master key.
-- Only necessary if one does not already exist.
-- Required to encrypt the credential secret in the next step.

CREATE MASTER KEY;


-- B: Create a database scoped credential
-- IDENTITY: Provide any string, it is not used for authentication to Azure storage.
-- SECRET: Provide your Azure storage account key.


CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'user',
    SECRET = '<azure_storage_account_key>'
;


-- C: Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure blob storage.
-- LOCATION: Provide Azure storage account name and blob container name.
-- CREDENTIAL: Provide the credential created in the previous step.

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

Buat sumber data eksternal

Gunakan perintah BUAT SUMBER DATA EKSTERNAL ini untuk menyimpan lokasi data, dan jenis data.

CREATE EXTERNAL DATA SOURCE AzureStorage_west_public
WITH
(  
    TYPE = Hadoop
,   LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
);

Penting

Jika Anda memilih untuk membuat kontainer penyimpanan blob azure Anda menjadi publik, ingatlah bahwa sebagai pemilik data Anda akan dikenakan biaya keluar data saat data meninggalkan pusat data.

Konfigurasikan format data

Data disimpan dalam file teks di penyimpanan blob Azure, dan setiap bidang dipisahkan dengan pemisah. Di SQL Server Management Studio, jalankan perintah BUAT FORMAT FILE EKSTERNAL berikut ini untuk menentukan format data dalam file teks. Data Contoso tidak dikompresi dan pipa diberi pemisah.

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(   FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = '|'
                    ,    STRING_DELIMITER = ''
                    ,    DATE_FORMAT         = 'yyyy-MM-dd HH:mm:ss.fff'
                    ,    USE_TYPE_DEFAULT = FALSE
                    )
);

Buat skema untuk tabel eksternal

Sekarang setelah Anda menentukan sumber data dan format file, Anda siap untuk membuat skema untuk tabel eksternal.

Untuk membuat tempat untuk menyimpan data Contoso di database Anda, buat skema.

CREATE SCHEMA [asb]
GO

Buat tabel eksternal

Jalankan skrip berikut untuk membuat tabel eksternal DimProduct dan FactOnlineSales. Yang Anda lakukan di sini adalah menentukan nama kolom dan jenis data, dan mengikatnya ke lokasi dan format file penyimpanan blob Azure. Definisi disimpan di gudang data dan data masih berada di Blob Azure Storage.

Parameter LOCATION adalah folder di bagian folder akar di Blob Azure Storage. Setiap tabel berada di folder yang berbeda.

--DimProduct
CREATE EXTERNAL TABLE [asb].DimProduct (
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL,
    [ProductDescription] [nvarchar](400) NULL,
    [ProductSubcategoryKey] [int] NULL,
    [Manufacturer] [nvarchar](50) NULL,
    [BrandName] [nvarchar](50) NULL,
    [ClassID] [nvarchar](10) NULL,
    [ClassName] [nvarchar](20) NULL,
    [StyleID] [nvarchar](10) NULL,
    [StyleName] [nvarchar](20) NULL,
    [ColorID] [nvarchar](10) NULL,
    [ColorName] [nvarchar](20) NOT NULL,
    [Size] [nvarchar](50) NULL,
    [SizeRange] [nvarchar](50) NULL,
    [SizeUnitMeasureID] [nvarchar](20) NULL,
    [Weight] [float] NULL,
    [WeightUnitMeasureID] [nvarchar](20) NULL,
    [UnitOfMeasureID] [nvarchar](10) NULL,
    [UnitOfMeasureName] [nvarchar](40) NULL,
    [StockTypeID] [nvarchar](10) NULL,
    [StockTypeName] [nvarchar](40) NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [AvailableForSaleDate] [datetime] NULL,
    [StopSaleDate] [datetime] NULL,
    [Status] [nvarchar](7) NULL,
    [ImageURL] [nvarchar](150) NULL,
    [ProductURL] [nvarchar](150) NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/DimProduct/'
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

--FactOnlineSales
CREATE EXTERNAL TABLE [asb].FactOnlineSales
(
    [OnlineSalesKey] [int]  NOT NULL,
    [DateKey] [datetime] NOT NULL,
    [StoreKey] [int] NOT NULL,
    [ProductKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [int] NULL,
    [SalesQuantity] [int] NOT NULL,
    [SalesAmount] [money] NOT NULL,
    [ReturnQuantity] [int] NOT NULL,
    [ReturnAmount] [money] NULL,
    [DiscountQuantity] [int] NULL,
    [DiscountAmount] [money] NULL,
    [TotalCost] [money] NOT NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/FactOnlineSales/'
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

Muat data

Ada berbagai cara untuk mengakses data eksternal. Anda bisa mengkueri data langsung dari tabel eksternal, memuat data ke tabel baru di gudang data, atau menambahkan data eksternal ke tabel gudang data yang sudah ada.

Buat skema baru

CTAS membuat tabel baru yang berisi data. Pertama, buat skema untuk data contoso.

CREATE SCHEMA [cso]
GO

Muat data ke dalam tabel baru

Untuk memuat data dari penyimpanan blob Azure ke dalam tabel gudang data, gunakan pernyataan CREATE TABLE AS SELECT (Transact-SQL). Pemuatan dengan CTAS memanfaatkan tabel eksternal yang diketik dengan jelas yang telah Anda buat. Untuk memuat data ke tabel baru, gunakan satu pernyataan CTAS per tabel.

CTAS membuat tabel baru dan mengisinya dengan hasil pernyataan tertentu. CTAS menentukan tabel baru untuk memiliki kolom dan jenis data yang sama dengan hasil pernyataan pemilihan. Jika Anda memilih semua kolom dari tabel eksternal, tabel baru akan menjadi replika kolom dan jenis data dalam tabel eksternal.

Dalam contoh ini, kita membuat tabel dimensi dan fakta sebagai tabel terdistribusi hash.

SELECT GETDATE();
GO

CREATE TABLE [cso].[DimProduct]            WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[DimProduct]             OPTION (LABEL = 'CTAS : Load [cso].[DimProduct]             ');
CREATE TABLE [cso].[FactOnlineSales]       WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[FactOnlineSales]        OPTION (LABEL = 'CTAS : Load [cso].[FactOnlineSales]        ');

Lacak kemajuan pemuatan

Anda dapat melacak kemajuan pemuatan Anda menggunakan tampilan manajemen dinamis (DMV).

-- To see all requests
SELECT * FROM sys.dm_pdw_exec_requests;

-- To see a particular request identified by its label
SELECT * FROM sys.dm_pdw_exec_requests as r
WHERE r.[label] = 'CTAS : Load [cso].[DimProduct]             '
      OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
;

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
WHERE
    r.[label] = 'CTAS : Load [cso].[DimProduct]             '
    OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Optimalkan kompresi columnstore

Secara default, kumpulan SQL khusus menyimpan tabel sebagai indeks columnstore berkluster. Setelah selesai dimuat, beberapa baris data mungkin tidak dikompresi ke dalam columnstore. Ada beberapa alasan berbeda mengapa hal ini dapat terjadi. Untuk mempelajari selengkapnya, lihat kelola indeks columnstore.

Untuk mengoptimalkan performa kueri dan kompresi columnstore setelah dimuat, bangun ulang tabel untuk memaksa indeks columnstore mengompresi semua baris.

SELECT GETDATE();
GO

ALTER INDEX ALL ON [cso].[DimProduct]               REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales]          REBUILD;

Untuk informasi selengkapnya tentang memelihara indeks columnstore, lihat artikel kelola indeks columnstore.

Optimalkan statistik

Paling baik untuk segera membuat statistik kolom tunggal setelah dimuat. Jika Anda tahu kolom tertentu tidak akan berada dalam predikat kueri, Anda dapat melompati pembuatan statistik pada kolom tersebut. Jika Anda membuat statistik kolom tunggal di setiap kolom, mungkin perlu waktu lama untuk membangun ulang semua statistik.

Jika Anda memutuskan untuk membuat statistik kolom tunggal di setiap kolom dari setiap tabel, Anda dapat menggunakan sampel kode prosedur tersimpan prc_sqldw_create_stats dalam artikel statistik.

Contoh berikut adalah titik awal yang baik untuk membuat statistik. Ini membuat statistik kolom tunggal di setiap kolom dalam tabel dimensi, dan di setiap kolom gabungan dalam tabel fakta. Anda dapat selalu menambahkan statistik kolom tunggal atau multikolom ke kolom tabel fakta lainnya di kemudian hari.

CREATE STATISTICS [stat_cso_DimProduct_AvailableForSaleDate] ON [cso].[DimProduct]([AvailableForSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_BrandName] ON [cso].[DimProduct]([BrandName]);
CREATE STATISTICS [stat_cso_DimProduct_ClassID] ON [cso].[DimProduct]([ClassID]);
CREATE STATISTICS [stat_cso_DimProduct_ClassName] ON [cso].[DimProduct]([ClassName]);
CREATE STATISTICS [stat_cso_DimProduct_ColorID] ON [cso].[DimProduct]([ColorID]);
CREATE STATISTICS [stat_cso_DimProduct_ColorName] ON [cso].[DimProduct]([ColorName]);
CREATE STATISTICS [stat_cso_DimProduct_ETLLoadID] ON [cso].[DimProduct]([ETLLoadID]);
CREATE STATISTICS [stat_cso_DimProduct_ImageURL] ON [cso].[DimProduct]([ImageURL]);
CREATE STATISTICS [stat_cso_DimProduct_LoadDate] ON [cso].[DimProduct]([LoadDate]);
CREATE STATISTICS [stat_cso_DimProduct_Manufacturer] ON [cso].[DimProduct]([Manufacturer]);
CREATE STATISTICS [stat_cso_DimProduct_ProductDescription] ON [cso].[DimProduct]([ProductDescription]);
CREATE STATISTICS [stat_cso_DimProduct_ProductKey] ON [cso].[DimProduct]([ProductKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductLabel] ON [cso].[DimProduct]([ProductLabel]);
CREATE STATISTICS [stat_cso_DimProduct_ProductName] ON [cso].[DimProduct]([ProductName]);
CREATE STATISTICS [stat_cso_DimProduct_ProductSubcategoryKey] ON [cso].[DimProduct]([ProductSubcategoryKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductURL] ON [cso].[DimProduct]([ProductURL]);
CREATE STATISTICS [stat_cso_DimProduct_Size] ON [cso].[DimProduct]([Size]);
CREATE STATISTICS [stat_cso_DimProduct_SizeRange] ON [cso].[DimProduct]([SizeRange]);
CREATE STATISTICS [stat_cso_DimProduct_SizeUnitMeasureID] ON [cso].[DimProduct]([SizeUnitMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_Status] ON [cso].[DimProduct]([Status]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeID] ON [cso].[DimProduct]([StockTypeID]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeName] ON [cso].[DimProduct]([StockTypeName]);
CREATE STATISTICS [stat_cso_DimProduct_StopSaleDate] ON [cso].[DimProduct]([StopSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_StyleID] ON [cso].[DimProduct]([StyleID]);
CREATE STATISTICS [stat_cso_DimProduct_StyleName] ON [cso].[DimProduct]([StyleName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitCost] ON [cso].[DimProduct]([UnitCost]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureID] ON [cso].[DimProduct]([UnitOfMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureName] ON [cso].[DimProduct]([UnitOfMeasureName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitPrice] ON [cso].[DimProduct]([UnitPrice]);
CREATE STATISTICS [stat_cso_DimProduct_UpdateDate] ON [cso].[DimProduct]([UpdateDate]);
CREATE STATISTICS [stat_cso_DimProduct_Weight] ON [cso].[DimProduct]([Weight]);
CREATE STATISTICS [stat_cso_DimProduct_WeightUnitMeasureID] ON [cso].[DimProduct]([WeightUnitMeasureID]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CurrencyKey] ON [cso].[FactOnlineSales]([CurrencyKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CustomerKey] ON [cso].[FactOnlineSales]([CustomerKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_DateKey] ON [cso].[FactOnlineSales]([DateKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_OnlineSalesKey] ON [cso].[FactOnlineSales]([OnlineSalesKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_ProductKey] ON [cso].[FactOnlineSales]([ProductKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_PromotionKey] ON [cso].[FactOnlineSales]([PromotionKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_StoreKey] ON [cso].[FactOnlineSales]([StoreKey]);

Prestasi tercapai!

Anda berhasil memuat data ke dalam gudang data Anda. Kerja yang bagus!

Sekarang Anda bisa mulai mengkueri tabel untuk menjelajahi data Anda. Jalankan kueri berikut untuk mengetahui total penjualan per merek:

SELECT  SUM(f.[SalesAmount]) AS [sales_by_brand_amount]
,       p.[BrandName]
FROM    [cso].[FactOnlineSales] AS f
JOIN    [cso].[DimProduct]      AS p ON f.[ProductKey] = p.[ProductKey]
GROUP BY p.[BrandName]

Langkah berikutnya

Untuk memuat kumpulan data lengkap, jalankan contoh muat gudang data ritel Contoso lengkap dari repositori sampel Microsoft SQL Server. Untuk tips pengembangan selengkapnya, lihat Keputusan desain dan teknik pengkodean untuk gudang data.