Merancang tabel menggunakan kumpulan SQL khusus di Azure Synapse Analytics
Artikel ini menyediakan konsep pengantar utama untuk merancang tabel di kumpulan SQL khusus.
Menentukan kategori tabel
Skema bintang mengatur data menjadi tabel fakta dan dimensi. Beberapa tabel digunakan untuk integrasi atau data penahapan sebelum berpindah ke tabel fakta atau dimensi. Saat Anda merancang tabel, putuskan apakah data tabel akan berada dalam tabel fakta, dimensi, atau integrasi. Keputusan ini menginformasikan struktur dan distribusi tabel yang sesuai.
Tabel fakta berisi data kuantitatif yang umumnya dihasilkan dalam sistem transaksional, kemudian dimuat ke dalam kumpulan SQL khusus. Contohnya, bisnis ritel menghasilkan transaksi penjualan setiap hari, kemudian memuat data ke dalam tabel fakta kumpulan SQL khusus untuk analisis.
Tabel dimensi berisi data atribut yang mungkin berubah, tetapi biasanya jarang berubah. Misalnya, nama dan alamat pelanggan disimpan dalam tabel dimensi dan hanya diperbarui pada saat profil pelanggan berubah. Untuk mengecilkan ukuran tabel fakta yang besar, nama dan alamat pelanggan tidak perlu ada di setiap baris tabel fakta. Sebagai gantinya, tabel fakta dan tabel dimensi dapat berbagi ID pelanggan. Kueri dapat menggabungkan dua tabel untuk mengasosiasikan profil dan transaksi pelanggan.
Tabel integrasi menyediakan tempat untuk mengintegrasikan atau staging data. Anda bisa membuat tabel integrasi sebagai tabel reguler, tabel eksternal, atau tabel sementara. Contohnya, Anda dapat memuat data ke tabel penahapan, melakukan transformasi pada data dalam penahapan, kemudian menyisipkan data ke dalam tabel produksi.
Skema dan nama tabel
Skema adalah cara yang baik untuk mengelompokkan tabel, digunakan dengan cara yang sama, bersama-sama. Jika Anda memigrasikan beberapa database dari solusi lokal ke kumpulan SQL khusus, sebaiknya migrasikan semua tabel fakta, dimensi, dan integrasi ke satu skema dalam kumpulan SQL khusus.
Contohnya, Anda dapat menyimpan semua tabel dalam kumpulan SQL sampel khusus WideWorldImportersDW dalam satu skema yang disebut wwi. Kode berikut membuat skema yang ditentukan pengguna bernama wwi.
CREATE SCHEMA wwi;
Untuk menampilkan organisasi tabel di kumpulan SQL khusus, Anda dapat menggunakan fakta, dim, dan int sebagai awalan untuk nama tabel. Tabel berikut menunjukkan beberapa nama skema dan tabel untuk WideWorldImportersDW.
| Tabel WideWorldImportersDW | Jenis tabel | Kumpulan SQL khusus |
|---|---|---|
| Kota | Dimensi | wwi.DimCity |
| Urutan | Fakta | wwi.FactOrder |
Persistensi tabel
Tabel menyimpan data secara permanen di Penyimpanan Azure, untuk sementara di Penyimpanan Azure, atau di penyimpanan data di luar kumpulan SQL khusus.
Tabel reguler
Tabel reguler menyimpan data di Azure Storage sebagai bagian dari kumpulan SQL khusus. Tabel dan data tetap ada terlepas dari apakah sesi terbuka atau tidak. Contoh berikut membuat tabel biasa dengan dua kolom.
CREATE TABLE MyTable (col1 int, col2 int );
Tabel sementara
Tabel sementara hanya ada selama durasi sesi. Anda dapat menggunakan tabel sementara untuk mencegah pengguna lain melihat hasil sementara dan juga untuk mengurangi kebutuhan pembersihan.
Tabel sementara menggunakan penyimpanan lokal untuk menawarkan kinerja cepat. Untuk informasi lebih lanjut, lihat Tabel sementara.
Tabel eksternal
Tabel eksternal mengacu pada data yang terletak di blob Azure Storage atau Azure Data Lake Store. Ketika digunakan dengan pernyataan CREATE TABLE AS SELECT, dengan memilih dari tabel eksternal, data akan diimpor ke kumpulan SQL khusus.
Dengan demikian, tabel eksternal berguna untuk memuat data. Untuk tutorial pemuatan, lihat Menggunakan PolyBase untuk memuat data dari Azure blob storage.
Jenis data
Kumpulan SQL khusus mendukung jenis data yang paling umum digunakan. Untuk daftar jenis data yang didukung, lihat jenis data dalam referensi CREATE TABLE dalam pernyataan CREATE TABLE. Untuk panduan tentang penggunaan jenis data, lihat Jenis data.
Tabel terdistribusi
Fitur mendasar dari kumpulan SQL khusus adalah caranya menyimpan dan beroperasi pada tabel di seluruh distribusi. Kumpulan SQL khusus mendukung tiga metode untuk mendistribusikan data: round-robin (default), hash, dan direplikasi.
Tabel terdistribusi hash
Tabel terdistribusi hash mendistribusikan baris berdasarkan nilai di kolom distribusi. Tabel terdistribusi hash dirancang untuk mencapai performa tinggi untuk kueri pada tabel besar. Ada beberapa faktor yang perlu dipertimbangkan saat memilih kolom distribusi.
Untuk informasi lebih lanjut, lihat Panduan desain untuk tabel terdistribusi.
Tabel yang direplikasi
Tabel yang direplikasi memiliki salinan tabel lengkap yang tersedia di setiap node Komputasi. Kueri berjalan cepat pada tabel yang direplikasi karena gabungan pada tabel yang direplikasi tidak memerlukan pergerakan data. Namun, replikasi membutuhkan penyimpanan ekstra dan tidak praktis untuk tabel besar.
Untuk informasi selengkapnya, lihat Panduan desain untuk tabel yang direplikasi.
Tabel round-robin
Tabel round-robin mendistribusikan baris tabel secara merata di semua distribusi. Baris didistribusikan secara acak. Pemuatan data ke dalam tabel round-robin sangatlah cepat. Perlu diingat bahwa kueri dapat memerlukan lebih banyak pergerakan data daripada metode distribusi lainnya.
Untuk informasi lebih lanjut, lihat Panduan desain untuk tabel terdistribusi.
Metode distribusi umum untuk tabel
Kategori tabel sering kali menentukan opsi mana yang akan dipilih untuk mendistribusikan tabel.
| Kategori tabel | Opsi distribusi yang disarankan |
|---|---|
| Fakta | Gunakan distribusi hash dengan indeks penyimpan kolom berkluster. Performa meningkat ketika dua tabel hash digabungkan pada kolom distribusi yang sama. |
| Dimensi | Gunakan direplikasi untuk tabel yang lebih kecil. Jika tabel terlalu besar untuk disimpan pada setiap simpul Komputasi, gunakan hash yang didistribusikan. |
| Staging | Gunakan round-robin untuk tabel pentahapan. Beban dengan CTAS cepat. Setelah data berada dalam tabel penahapan, gunakan INSERT...SELECT untuk memindahkan data ke tabel produksi. |
Catatan
Untuk saran tentang strategi distribusi tabel terbaik untuk digunakan berdasarkan beban kerja Anda, lihat Azure Synapse SQL Distribution Advisor.
Partisi tabel
Tabel yang dipartisi menyimpan dan melakukan operasi pada baris tabel sesuai dengan rentang data. Contohnya, tabel dapat dipartisi berdasarkan hari, bulan, atau tahun. Anda dapat meningkatkan performa kueri melalui penghapusan partisi yang membatasi pemindaian kueri ke data dalam partisi. Anda juga dapat mempertahankan data melalui pengalihan partisi. Karena data dalam kumpulan SQL sudah didistribusikan, terlalu banyak partisi dapat memperlambat kinerja kueri. Untuk informasi lebih lanjut, lihat Panduan pemartisian. Saat partisi beralih ke partisi tabel yang tidak kosong, pertimbangkan untuk menggunakan opsi TRUNCATE_TARGET dalam pernyataan ALTER TABLE Anda jika data yang ada akan dipotong. Kode di bawah ini menukarkan data harian yang diubah menjadi SalesFact, menimpa semua data yang ada.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Indeks penyimpan kolom
Secara default, kumpulan SQL khusus menyimpan tabel sebagai indeks penyimpan kolom berkluster. Bentuk penyimpanan data ini mencapai kompresi data dan performa kueri yang tinggi pada tabel besar.
Indeks penyimpan kolom berkluster biasanya merupakan pilihan terbaik, tetapi dalam beberapa kasus indeks berkluster atau bertumpuk merupakan struktur penyimpanan yang sesuai.
Tip
Tabel bertumpuk dapat sangat berguna untuk memuat data sementara, seperti tabel penahapan, yang diubah menjadi tabel akhir.
Untuk daftar fitur penyimpan kolom, lihat Yang baru untuk indeks penyimpan kolom. Untuk meningkatkan performa indeks penyimpan kolom, lihat Memaksimalkan kualitas grup baris untuk indeks penyimpan kolom.
Statistik
Pengoptimal kueri menggunakan statistik tingkat kolom saat membuat rencana untuk menjalankan kueri.
Untuk meningkatkan performa kueri, penting untuk memiliki statistik pada kolom individual, terutama kolom yang digunakan dalam gabungan kueri. Membuat statistik terjadi secara otomatis.
Memperbarui statistik tidak terjadi secara otomatis. Perbarui statistik setelah sejumlah besar baris telah ditambahkan atau diubah. Contohnya, perbarui statistik setelah pemuatan. Untuk informasi lebih lanjut, lihat Panduan statistik.
Kunci primer dan kunci unik
PRIMARI KEY hanya didukung ketika kedua NONCLUSTERED dan NOT ENFORCED digunakan. Batasan UNIQUE hanya didukung dengan digunakannya TIDAK DIBERLAKUKAN. Periksa batasan tabel kumpulan SQL khusus.
Perintah untuk membuat tabel
Anda dapat membuat tabel sebagai tabel kosong baru. Anda juga dapat membuat dan mengisi tabel dengan hasil dari pernyataan pilih. Berikut perintah T-SQL untuk membuat tabel.
| Pernyataan T-SQL | Deskripsi |
|---|---|
| CREATE TABLE | Membuat tabel kosong dengan menentukan semua kolom dan opsi tabel. |
| CREATE EXTERNAL TABLE | Membuat tabel eksternal. Definisi tabel disimpan di kumpulan SQL khusus. Data tabel disimpan di penyimpanan Azure Blob atau Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Mengisi tabel baru dengan hasil dari pernyataan pilih. Kolom tabel dan jenis data didasarkan pada hasil pernyataan pilih. Untuk mengimpor data, pernyataan ini dapat memilih dari tabel eksternal. |
| CREATE EXTERNAL TABLE AS SELECT | Membuat tabel eksternal baru dengan mengekspor hasil pernyataan pilih ke lokasi eksternal. Lokasinya berada di penyimpanan Azure Blob atau Azure Data Lake Store. |
Menyelaraskan data sumber dengan kumpulan SQL khusus
Tabel kumpulan SQL khusus diisi dengan cara memuat data dari sumber data lain. Untuk melakukan pemuatan yang berhasil, jumlah dan jenis data dari kolom pada data sumber harus sejajar dengan definisi tabel di kumpulan SQL khusus. Menyelaraskan data mungkin merupakan bagian tersulit dalam merancang tabel Anda.
Jika data berasal dari beberapa penyimpanan data, Anda memuat data ke dalam kumpulan SQL khusus dan menyimpannya dalam tabel integrasi. Setelah data berada dalam tabel integrasi, Anda dapat menggunakan kekuatan kumpulan SQL khusus untuk melakukan operasi transformasi. Setelah data disiapkan, Anda dapat menyisipkannya ke dalam tabel produksi.
Fitur tabel yang tidak didukung
Kumpulan SQL khusus mendukung banyak fitur tabel yang ditawarkan oleh database lain, tetapi tidak semuanya. Daftar berikut menampilkan beberapa fitur tabel yang tidak didukung di kumpulan SQL khusus:
- Kunci asing, Periksa Batasan Tabel
- Kolom Komputasi
- Tampilan Terindeks
- Sekuens
- Kolom Tersebar
- Kunci Pengganti. Terapkan dengan Identitas.
- Sinonim
- Pemicu
- Indeks Unik
- Jenis yang Ditentukan Pengguna
Kueri ukuran tabel
Catatan
Untuk jumlah yang akurat dari kueri di bagian ini, pastikan bahwa pemeliharaan indeks terjadi secara teratur dan setelah perubahan data besar.
Salah satu cara sederhana untuk mengidentifikasi ruang dan baris yang dikonsumsi oleh tabel di masing-masing dari 60 distribusi, adalah menggunakan DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Namun, menggunakan perintah DBCC dapat cukup membatasi. Tampilan manajemen dinamis (DMV) menampilkan lebih banyak detail daripada perintah DBCC. Mulailah dengan membuat tampilan ini:
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
GETDATE() AS [execution_time]
, DB_NAME() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]
, nt.[name] AS [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]
, tp.[distribution_policy_desc] AS [distribution_policy_name]
, c.[name] AS [distribution_column]
, nt.[distribution_id] AS [distribution_id]
, i.[type] AS [index_type]
, i.[type_desc] AS [index_type_desc]
, nt.[pdw_node_id] AS [pdw_node_id]
, pn.[type] AS [pdw_node_type]
, pn.[name] AS [pdw_node_name]
, di.name AS [dist_name]
, di.position AS [dist_position]
, nps.[partition_number] AS [partition_nmbr]
, nps.[reserved_page_count] AS [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]
, nps.[in_row_data_page_count]
+ nps.[row_overflow_used_page_count]
+ nps.[lob_used_page_count] AS [data_space_page_count]
, nps.[reserved_page_count]
- (nps.[reserved_page_count] - nps.[used_page_count])
- ([in_row_data_page_count]
+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]
, nps.[row_count] AS [row_count]
from
sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
ON nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
ON nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
ON nt.[object_id] = nps.[object_id]
AND nt.[pdw_node_id] = nps.[pdw_node_id]
AND nt.[distribution_id] = nps.[distribution_id]
AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
ON cdp.[object_id] = c.[object_id]
AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
[execution_time]
, [database_name]
, [schema_name]
, [table_name]
, [two_part_name]
, [node_table_name]
, [node_table_name_seq]
, [distribution_policy_name]
, [distribution_column]
, [distribution_id]
, [index_type]
, [index_type_desc]
, [pdw_node_id]
, [pdw_node_type]
, [pdw_node_name]
, [dist_name]
, [dist_position]
, [partition_nmbr]
, [reserved_space_page_count]
, [unused_space_page_count]
, [data_space_page_count]
, [index_space_page_count]
, [row_count]
, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]
, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]
, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]
, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]
, ([unused_space_page_count] * 8.0) AS [unused_space_KB]
, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]
, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]
, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]
, ([data_space_page_count] * 8.0) AS [data_space_KB]
, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]
, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]
, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]
, ([index_space_page_count] * 8.0) AS [index_space_KB]
, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]
, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]
, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
Ringkasan ruang tabel
Kueri ini menampilkan baris dan spasi menurut tabel. Hal ini memungkinkan Anda melihat tabel mana yang ukurannya paling besar dan apakah termasuk jenis round-robin, replikasi, atau terdistribusi hash. Untuk tabel terdistribusi hash, kueri memperlihatkan kolom distribusi.
SELECT
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
, COUNT(distinct partition_nmbr) as nbr_partitions
, SUM(row_count) as table_row_count
, SUM(reserved_space_GB) as table_reserved_space_GB
, SUM(data_space_GB) as table_data_space_GB
, SUM(index_space_GB) as table_index_space_GB
, SUM(unused_space_GB) as table_unused_space_GB
FROM
dbo.vTableSizes
GROUP BY
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
ORDER BY
table_reserved_space_GB desc
;
Ruang tabel menurut jenis distribusi
SELECT
distribution_policy_name
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;
Ruang tabel menurut jenis indeks
SELECT
index_type_desc
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;
Ringkasan ruang distribusi
SELECT
distribution_id
, SUM(row_count) as total_node_distribution_row_count
, SUM(reserved_space_MB) as total_node_distribution_reserved_space_MB
, SUM(data_space_MB) as total_node_distribution_data_space_MB
, SUM(index_space_MB) as total_node_distribution_index_space_MB
, SUM(unused_space_MB) as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY distribution_id
ORDER BY distribution_id
;
Langkah berikutnya
Setelah membuat tabel untuk kumpulan SQL khusus Anda, langkah selanjutnya adalah memuat data ke dalam tabel. Untuk tutorial pemuatan, lihat Memuat data ke kumpulan SQL khusus dan tinjau Strategi pemuatan data untuk kumpulan SQL khusus di Azure Synapse Analytics.