Bagikan melalui


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:

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.