Mendesain tabel menggunakan Synapse SQL di Azure Synapse Analytics

Artikel
06/01/2023

Dokumen ini mencakup konsep utama untuk mendesain tabel dengan kumpulan SQL khusus dan kumpulan SQL tanpa server.

Kumpulan SQL tanpa server adalah layanan kueri atas data di data lake Anda. Ia tidak memiliki penyimpanan lokal untuk penyerapan data. Kumpulan SQL khusus mewakili kumpulan sumber daya analitik yang disediakan saat menggunakan Synapse SQL. Ukuran kumpulan SQL khusus ditentukan oleh Data Warehousing Units (DWU).

Tabel berikut mencantumkan topik yang relevan dengan kumpulan SQL khusus vs. kumpulan SQL tanpa server:

Topik	kumpulan SQL khusus	kumpulan SQL tanpa server
Menentukan kategori tabel	Ya	Tidak
Nama skema	Ya	Ya
Nama tabel	Ya	Tidak
Persistensi tabel	Ya	Tidak
Tabel reguler	Ya	Tidak
Tabel sementara	Ya	Ya
Tabel eksternal	Ya	Ya
Jenis data	Ya	Ya
Tabel terdistribusi	Ya	Tidak
Tabel terdistribusi hash	Ya	Tidak
Tabel yang direplikasi	Ya	Tidak
Tabel round-robin	Ya	Tidak
Metode distribusi umum untuk tabel	Ya	Tidak
Partisi	Ya	Ya
Indeks penyimpan kolom	Ya	Tidak
Statistik	Ya	Ya
Kunci primer dan kunci unik	Ya	Tidak
Perintah untuk membuat tabel	Ya	Tidak
Menyelaraskan data sumber dengan gudang data	Ya	Tidak
Fitur tabel yang tidak didukung	Ya	Tidak
Kueri ukuran tabel	Ya	Tidak

Menentukan kategori tabel

Skema bintang mengatur data menjadi tabel fakta dan dimensi. Beberapa tabel digunakan untuk data integrasi atau staging sebelum pindah ke tabel fakta atau dimensi. Saat Anda mendesain 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 dan kemudian dimuat ke dalam gudang data. Misalnya, setiap hari bisnis ritel menghasilkan transaksi penjualan dan kemudian memuat datanya ke dalam tabel fakta gudang data untuk keperluan 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. Misalnya, Anda dapat memuat data ke tabel staging, melakukan transformasi pada data dalam staging, lalu menyisipkan data ke dalam tabel produksi.

Nama skema

Skema adalah cara yang baik untuk mengelompokkan objek yang digunakan dengan cara yang sama. Kode berikut membuat skema yang ditentukan pengguna yang disebut wwi.

CREATE SCHEMA wwi;

Nama tabel

Jika Anda memigrasikan beberapa database dari solusi lokal ke kumpulan SQL khusus, praktik terbaik adalah memigrasikan semua tabel fakta, dimensi, dan integrasi ke satu skema kumpulan SQL. Misalnya, Anda dapat menyimpan semua tabel di gudang data sampel WideWorldImportersDW dalam satu skema yang disebut wwi.

Untuk menampilkan organisasi tabel di kumpulan SQL khusus, Anda dapat menggunakan fakta, dim, dan int sebagai awalan untuk nama tabel. Tabel di bawah ini memperlihatkan beberapa skema dan nama 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 Azure Storage atau di penyimpanan data di luar gudang data.

Tabel reguler

Tabel reguler menyimpan data di Azure Storage sebagai bagian dari gudang data. Tabel dan data akan tetap ada terlepas dari apakah sesi terbuka atau tidak. Contoh di bawah ini membuat tabel reguler dengan dua kolom.

CREATE TABLE MyTable (col1 int, col2 int );

Tabel sementara

Tabel sementara hanya ada selama durasi sesi. Anda bisa menggunakan tabel sementara untuk mencegah pengguna lain melihat hasil sementara. Menggunakan tabel sementara juga mengurangi kebutuhan untuk pembersihan. Tabel sementara menggunakan penyimpanan lokal dan, dalam kumpulan SQL khusus, dapat menawarkan performa yang lebih cepat.

Kumpulan SQL tanpa server mendukung tabel sementara. Akan tetapi, penggunaannya terbatas karena Anda dapat memilih dari tabel sementara, tetapi tidak dapat menggabungkannya dengan file di penyimpanan.

Untuk informasi lebih lanjut, lihat Tabel sementara.

Tabel eksternal

Tabel eksternal mengarahkan ke data yang terletak di blob Azure Storage atau Azure Data Lake Storage.

Impor data dari tabel eksternal ke kumpulan SQL khusus menggunakan pernyataan CREATE TABLE AS SELECT. Untuk tutorial pemuatan, lihat Menggunakan PolyBase untuk memuat data dari Azure blob storage.

Untuk kumpulan SQL tanpa server, Anda bisa menggunakan CETAS untuk menyimpan hasil kueri ke tabel eksternal di Azure 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 di pernyataan CREATE TABLE. Untuk informasi selengkapnya tentang penggunaan jenis data, lihat Jenis data.

Tabel terdistribusi

Fitur mendasar dari kumpulan SQL khusus adalah cara ia dapat menyimpan dan beroperasi pada tabel di seluruh distribusi. Kumpulan SQL khusus mendukung tiga metode untuk mendistribusikan data:

Round-robin (default)
Hash
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 simpul Komputasi. Kueri berjalan cepat pada tabel yang direplikasi karena bergabung pada tabel yang direplikasi tidak memerlukan perpindahan data. 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. Memuat data ke dalam tabel round-robin sangatlah cepat. Akan tetapi, kueri dapat memerlukan lebih banyak pergerakan data daripada metode distribusi lainnya.

Untuk informasi selengkapnya, lihat Panduan desain untuk tabel terdistribusi.

Metode distribusi umum untuk tabel

Kategori tabel sering kali menentukan opsi optimal untuk distribusi 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 staging, gunakan SISIPKAN...PILIH untuk memindahkan data ke tabel produksi.

Partisi

Dalam kumpulan SQL khusus, tabel berpartisi menyimpan dan menjalankan operasi pada baris tabel sesuai dengan rentang data. Misalnya, 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 khusus sudah didistribusikan, terlalu banyak partisi dapat memperlambat performa kueri. Untuk informasi selengkapnya, lihat Panduan pemartisian.

Tip

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 mengalihkan data harian yang ditransformasi menjadi partisi SalesFact dan menimpa data yang sudah ada.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);

Dalam kumpulan SQL tanpa server, Anda dapat membatasi file/folder (partisi) yang akan dibaca oleh kueri Anda. Pemartisian menurut jalur didukung menggunakan fungsi filepath dan fileinfo yang dijelaskan dalam Mengkueri file penyimpanan. Contoh berikut membaca folder dengan data untuk tahun 2017:

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

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 heap dapat sangat berguna untuk memuat data sementara, seperti tabel staging, yang diubah menjadi tabel akhir.

Untuk daftar fitur penyimpanan kolom, lihat Apa 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. Synapse SQL mendukung pembuatan statistik secara otomatis.

Pembaruan statistik tidak terjadi secara otomatis. Lakukan pembaruan statistik setelah sejumlah besar baris ditambahkan atau diubah. Misalnya, lakukan pembaruan statistik setelah pemuatan. Informasi tambahan disediakan di artikel Panduan statistik.

Kunci primer dan kunci unik

Untuk kumpulan SQL khusus, KUNCI PRIMER hanya didukung ketika NONCLUSTERED dan NOT ENFORCED keduanya digunakan. Batasan UNIQUE hanya didukung ketika NOT ENFORCED digunakan. Untuk informasi selengkapnya, lihat artikel batasan tabel kolam SQL khusus.

Perintah untuk membuat tabel

Untuk kumpulan SQL khusus, 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 Azure Blob storage atau Azure Data Lake Storage.
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 di Azure Blob storage atau Azure Data Lake Storage.

Selaraskan data sumber dengan gudang data

Tabel kumpulan SQL khusus diisi dengan memuat data dari sumber data lain. Untuk mencapai beban yang berhasil, jumlah dan jenis data kolom pada data sumber harus selaras dengan definisi tabel di gudang data.

Catatan

Menyelaraskan data mungkin merupakan bagian tersulit dalam mendesain tabel Anda.

Jika data berasal dari beberapa penyimpanan data, Anda dapat mentransfer data ke gudang data dan menyimpannya dalam tabel integrasi. Setelah data berada dalam tabel integrasi, Anda dapat menggunakan kekuatan kumpulan SQL khusus untuk menerapkan operasi transformasi. Setelah data disiapkan, Anda dapat menyisipkannya ke dalam tabel produksi.

Fitur tabel yang tidak didukung

Kumpulan SQL khusus mendukung banyak fitur tabel, tetapi tidak semua, yang ditawarkan oleh database lain. Daftar berikut ini memperlihatkan beberapa fitur tabel yang tidak didukung di kumpulan SQL khusus.

Kunci asing, periksa Batasan Tabel
Kolom Komputasi
Tampilan Terindeks
Sekuens
Kolom Jarang
Kunci Pengganti, terapkan dengan Identitas
Sinonim
Pemicu
Indeks Unik
Jenis yang Ditentukan Pengguna

Kueri ukuran tabel

Dalam kumpulan SQL khusus, salah satu cara sederhana untuk mengidentifikasi ruang dan baris yang dipakai oleh tabel pada masing-masing dari 60 distribusi adalah dengan menggunakan DBCC PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Perlu diingat bahwa menggunakan perintah DBCC bisa sangat membatasi. Tampilan manajemen dinamis (DMV) menampilkan lebih banyak detail daripada perintah DBCC. Mulailah dengan membuat tampilan di bawah 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]
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. Ringkasan ruang tabel memungkinkan Anda untuk melihat tabel mana yang merupakan tabel terbesar Anda. Anda juga akan melihat apakah mereka round-robin, direplikasi, 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 gudang data Anda, langkah berikutnya adalah memuat data ke dalam tabel. Untuk tutorial pemuatan, lihat Memuat data ke dalam kumpulan SQL khusus.