Panduan untuk mendesain tabel terdistribusi menggunakan kumpulan SQL khusus di Azure Synapse Analytics

Artikel ini berisi rekomendasi untuk mendesain tabel terdistribusi hash dan round robin di kumpulan SQL khusus.

Artikel ini mengasumsikan Anda terbiasa dengan konsep distribusi data dan perpindahan data di kumpulan SQL khusus. Untuk informasi selengkapnya, lihat Arsitektur Azure Synapse Analytics.

Apa itu tabel terdistribusi?

Tabel terdistribusi muncul sebagai tabel tunggal, tetapi baris benar-benar disimpan di 60 distribusi. Baris didistribusikan dengan algoritme hash atau round-robin.

Distribusi hash meningkatkan performa kueri pada tabel fakta besar, dan merupakan fokus dari artikel ini. Distribusi round-robin berguna untuk meningkatkan kecepatan pemuatan. Pilihan desain ini memiliki dampak signifikan pada peningkatan performa kueri dan pemuatan.

Opsi penyimpanan tabel lainnya adalah mereplikasi tabel kecil di semua simpul Komputasi. Untuk informasi selengkapnya, lihat Panduan desain untuk tabel yang direplikasi. Untuk memilih di antara tiga opsi dengan cepat, lihat Tabel terdistribusi di ringkasan tabel.

Sebagai bagian dari desain tabel, pahami sebanyak mungkin tentang data Anda dan bagaimana data dikueri.  Misalnya, pertimbangkan pertanyaan-pertanyaan ini:

• Seberapa besar tabelnya?
• Seberapa sering tabel direfresh?
• Apakah saya memiliki tabel fakta dan dimensi di kumpulan SQL khusus?

Terdistribusi hash

Tabel terdistribusi hash mendistribusikan baris tabel di seluruh simpul Komputasi menggunakan fungsi hash deterministik untuk menetapkan setiap baris ke satu distribusi.

Karena nilai identik selalu di-hash ke distribusi yang sama, SQL Analytics memiliki pengetahuan bawaan tentang lokasi baris. Dalam kumpulan SQL khusus, pengetahuan ini digunakan untuk meminimalkan pergerakan data selama kueri, yang meningkatkan performa kueri.

Tabel terdistribusi hash bekerja dengan baik untuk tabel fakta besar dalam skema bintang. Tabel tersebut memiliki jumlah baris yang sangat besar dan tetap mencapai performa tinggi. Ada beberapa pertimbangan desain yang membantu Anda untuk mendapatkan performa sistem terdistribusi dirancang untuk menyediakan. Memilih kolom distribusi yang tepat adalah salah satu pertimbangan yang dijelaskan dalam artikel ini.

Pertimbangkan untuk menggunakan tabel terdistribusi hash saat:

• Ukuran tabel pada disk lebih dari 2 GB.
• Tabel ini sering menyisipkan, memperbarui, dan menghapus operasi.

Terdistribusi round-robin

Tabel terdistribusi round-robin mendistribusikan baris tabel secara merata di semua distribusi. Penugasan baris ke distribusi bersifat acak. Tidak seperti tabel terdistribusi hash, baris dengan nilai yang sama tidak dijamin untuk ditempatkan pada distribusi yang sama.

Akibatnya, sistem terkadang perlu menjalankan operasi pemindahan data untuk mengatur data Anda dengan lebih baik sebelum dapat menyelesaikan kueri. Langkah ekstra ini dapat memperlambat kueri Anda. Misalnya, penggabungan tabel round-robin biasanya membutuhkan perombakan baris, yang berdampak pada performa.

Pertimbangkan untuk menggunakan distribusi round-robin untuk tabel Anda dalam skenario berikut:

• Saat memulai sebagai titik awal yang sederhana karena ini adalah default
• Jika tidak ada kunci bergabung yang jelas
• Jika tidak ada kolom kandidat yang baik untuk hash yang mendistribusikan tabel
• Jika tabel tidak berbagi kunci gabungan umum dengan tabel lain
• Jika gabungan kurang signifikan dibandingkan gabungan lainnya dalam kueri
• Ketika tabel adalah tabel penahapan sementara

Tutorial Memuat data taksi New York memberikan contoh memuat data ke dalam tabel penahapan round-robin.

Memilih kolom distribusi

Tabel terdistribusi hash memiliki kolom distribusi yang atau kumpulan kolom yang merupakan kunci hash. Misalnya, kode berikut membuat tabel terdistribusi hash dengan ProductKey sebagai kolom distribusi.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

Distribusi hash dapat diterapkan pada beberapa kolom untuk distribusi tabel dasar yang lebih merata. Distribusi multi-kolom akan memungkinkan Anda memilih hingga delapan kolom untuk distribusi. Ini tidak hanya mengurangi penyimpangan data dari waktu ke waktu tetapi juga meningkatkan performa kueri. Contohnya:

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Catatan

Distribusi multi-kolom di Azure Synapse Analytics dapat diaktifkan dengan mengubah tingkat kompatibilitas database menjadi 50 dengan perintah ini. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Untuk informasi selengkapnya tentang mengatur tingkat kompatibilitas database, lihat MENGUBAH KONFIGURASI CAKUPAN DATABASE. Untuk informasi selengkapnya tentang distribusi multi-kolom, lihat CREATE MATERIALIZED VIEW, CREATE TABLE, atau CREATE TABLE AS SELECT.

Data yang disimpan dalam kolom distribusi dapat diperbarui. Updates ke data dalam kolom distribusi dapat mengakibatkan operasi acak data.

Memilih kolom distribusi adalah keputusan desain yang penting karena nilai dalam kolom hash menentukan cara baris didistribusikan. Pilihan terbaik tergantung pada beberapa faktor, dan biasanya melibatkan pertukaran. Setelah kolom distribusi atau kumpulan kolom dipilih, Anda tidak dapat mengubahnya. Jika Anda tidak memilih kolom terbaik untuk pertama kalinya, Anda dapat menggunakan CREATE TABLE AS SELECT (CTAS) untuk membuat ulang tabel dengan kolom distribusi berbeda.

Pilih kolom distribusi dengan data yang terdistribusi secara merata

Untuk performa terbaik, semua distribusi harus memiliki jumlah baris yang kira-kira sama. Ketika satu atau beberapa distribusi memiliki jumlah baris yang tidak proporsional, beberapa distribusi menyelesaikan bagian kueri paralelnya sebelum yang lain. Karena kueri tidak dapat diselesaikan hingga semua distribusi selesai diproses, setiap kueri hanya secepat distribusi yang paling lambat.

• Penyimpanan data berarti data tidak didistribusikan secara merata di seluruh distribusi
• Pemrosesan penyimpangan berarti bahwa beberapa distribusi membutuhkan waktu lebih lama daripada yang lain saat menjalankan kueri paralel. Ini bisa terjadi ketika data disimpangkan.

Untuk menyeimbangkan pemrosesan paralel, pilih kolom distribusi atau kumpulan kolom yang:

• Memiliki banyak nilai unik. Kolom distribusi dapat memiliki nilai duplikat. Semua baris dengan nilai yang sama ditetapkan ke distribusi yang sama. Karena ada 60 distribusi, beberapa distribusi dapat memiliki > 1 nilai unik sementara yang lain mungkin berakhir dengan nilai nol.
• Tidak memiliki NULL, atau hanya memiliki sedikit NULL. Untuk contoh ekstrem, jika semua nilai dalam kolom distribusi adalah NULL, semua baris ditetapkan ke distribusi yang sama. Hasilnya, pemrosesan kueri condong ke satu distribusi, dan tidak mendapat manfaat dari pemrosesan paralel.
• Bukan kolom tanggal. Semua data untuk tanggal yang sama berada dalam distribusi yang sama, atau akan membuat mengkluster catatan berdasarkan tanggal. Jika beberapa pengguna melakukan pemfilteran pada tanggal yang sama (seperti tanggal saat ini), maka hanya 1 dari 60 distribusi yang melakukan semua pekerjaan pemrosesan.

Pilih kolom distribusi yang meminimalkan perpindahan data

Untuk mendapatkan kueri hasil kueri yang benar, kueri mungkin memindahkan data dari satu simpul Komputasi ke simpul lainnya. Perpindahan data biasanya terjadi ketika kueri memiliki gabungan dan agregasi pada tabel terdistribusi. Memilih kolom distribusi atau kumpulan kolom yang membantu meminimalkan perpindahan data adalah salah satu strategi terpenting untuk mengoptimalkan performa kumpulan SQL khusus Anda.

Untuk meminimalkan perpindahan data, pilih kolom distribusi atau kumpulan kolom yang:

• Digunakan dalam klausul JOIN, GROUP BY, DISTINCT, OVER, dan HAVING. Saat dua tabel fakta besar memiliki gabungan yang sering, performa kueri meningkat saat Anda mendistribusikan kedua tabel di salah satu kolom gabungan. Saat tabel tidak digunakan dalam gabungan, pertimbangkan untuk mendistribusikan tabel pada kolom atau kumpulan kolom yang sering berada di klausul GROUP BY.
• Tidak digunakan dalam klausul WHERE. Saat klausa kueri WHERE dan kolom distribusi tabel berada di kolom yang sama, kueri dapat mengalami kecondongan data tinggi, yang menyebabkan beban pemrosesan hanya jatuh pada beberapa distribusi. Ini berdampak pada performa kueri, idealnya banyak distribusi berbagi beban pemrosesan.
• Bukan kolom tanggal. Klausa WHERE sering disaring berdasarkan tanggal. Ketika ini terjadi, semua pemrosesan hanya dapat berjalan pada beberapa distribusi yang memengaruhi performa kueri. Idealnya, banyak distribusi berbagi beban pemrosesan.

Setelah Anda mendesain tabel terdistribusi hash, langkah selanjutnya adalah memuat data ke dalam tabel. Untuk panduan memuat, lihat Ringkasan memuat.

Cara mengetahui apakah distribusi Anda adalah pilihan yang tepat

Setelah data dimuat ke dalam tabel terdistribusi hash, periksa untuk melihat seberapa merata baris terdistribusi di 60 distribusi. Baris per distribusi dapat bervariasi hingga 10% tanpa dampak nyata pada performa. Pertimbangkan topik berikut untuk mengevaluasi kolom distribusi Anda.

Menentukan apakah tabel memiliki penyimpangan data

Cara cepat untuk memeriksa penyimpangan data adalah dengan menggunakan DBCC PDW_SHOWSPACEUSED. Kode SQL berikut mengembalikan jumlah baris tabel yang disimpan di masing-masing dari 60 distribusi. Untuk performa yang seimbang, baris dalam tabel terdistribusi Anda harus tersebar merata di semua distribusi.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Untuk mengidentifikasi tabel mana yang memiliki penyimpangan data lebih dari 10%:

1. Buat tampilan dbo.vTableSizes yang ditampilkan dalam artikel Ringkasan tabel.
2. Jalankan kueri berikut:

select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

Memeriksa paket kueri untuk perpindahan data

Kumpulan kolom distribusi yang baik memungkinkan gabungan dan agregasi dengan perpindahan data yang minimal. Ini memengaruhi cara bergabung yang harus ditulis. Untuk mendapatkan perpindahan data minimal untuk gabungan pada dua tabel terdistribusi hash, salah satu kolom gabungan harus berupa kolom distribusi atau kolom. Ketika dua tabel terdistribusi hash bergabung pada kolom distribusi dengan jenis data yang sama, penggabungan tersebut tidak memerlukan perpindahan data. Gabungan dapat menggunakan kolom tambahan tanpa menimbulkan perpindahan data.

Untuk menghindari perpindahan data selama bergabung:

• Tabel yang terlibat dalam bergabung harus didistribusikan hash pada salah satu kolom yang berpartisipasi dalam bergabung.
• Jenis data kolom gabungan harus cocok di antara kedua tabel.
• Kolom harus digabungkan dengan operator yang sama.
• Jenis gabungan mungkin bukan CROSS JOIN.

Untuk melihat apakah kueri mengalami perpindahan data, Anda bisa melihat paket kueri.

Mengatasi masalah kolom distribusi

Tidak perlu menyelesaikan semua kasus penyimpangan data. Mendistribusikan data adalah masalah menemukan keseimbangan yang tepat antara meminimalkan penyimpangan data dan perpindahan data. Meminimalkan penyimpangan data dan perpindahan data tidak selalu memungkinkan. Terkadang manfaat dari perpindahan data yang minimal mungkin lebih besar daripada dampak dari penyimpangan data.

Untuk memutuskan apakah Anda harus mengatasi penyimpangan data dalam tabel, Anda harus memahami sebanyak mungkin tentang volume data dan kueri dalam beban kerja Anda. Anda dapat menggunakan langkah-langkah dalam artikel Pemantauan kueri untuk memantau dampak penyimpangan pada performa kueri. Secara khusus, cari berapa lama waktu yang dibutuhkan untuk menyelesaikan kueri besar pada distribusi individual.

Karena Anda tidak dapat mengubah kolom distribusi pada tabel yang sudah ada, cara umum untuk mengatasi penyimpangan data adalah dengan membuat ulang tabel dengan kolom distribusi yang berbeda.

Membuat ulang tabel dengan kumpulan kolom distribusi baru

Contoh ini menggunakan CREATE TABLE AS SELECT untuk membuat ulang tabel dengan kolom distribusi hash atau kolom yang berbeda.

Pertama gunakan CREATE TABLE AS SELECT (CTAS) tabel baru dengan kunci baru. Lalu, buat ulang statistik dan akhirnya, tukar tabel dengan menamai ulang.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Langkah berikutnya

Untuk membuat tabel terdistribusi, gunakan salah satu pernyataan berikut:

• BUAT TABEL (kumpulan SQL khusus)
• CREATE TABLE AS SELECT (kumpulan SQL khusus)