Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Artikel ini menjelaskan konsep utama untuk merancang tabel dengan kumpulan SQL khusus dan kumpulan SQL tanpa server di Azure Synapse Analytics.
- Serverless SQL pool adalah layanan kueri yang beroperasi pada 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).
Topik berikut relevan dengan kumpulan SQL khusus versus kumpulan SQL tanpa server:
| Topik | Kumpulan SQL khusus | Kumpulan SQL tanpa server |
|---|---|---|
| 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 rotasi | Ya | Tidak |
| Tabel yang didistribusikan dengan hash | Ya | Tidak |
| Tabel yang direplikasi | Ya | Tidak |
| Metode distribusi umum untuk tabel | Ya | Tidak |
| Partitions | Ya | Ya |
| Panduan 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 |
Kategori tabel
Skema bintang mengatur data menjadi tabel fakta dan dimensi. Beberapa tabel digunakan untuk integrasi atau penyimpanan sementara data sebelum dipindahkan 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 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 diperbarui hanya jika 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 mengaitkan profil dan transaksi pelanggan.
Tabel integrasi menyediakan tempat untuk mengintegrasikan atau menyiapkan 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.
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 terbaiknya 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 fact, dim, dan int sebagai awalan untuk nama tabel. Tabel berikut ini memperlihatkan beberapa nama skema dan tabel untuk WideWorldImportersDW.
| Tabel WideWorldImportersDW | Jenis tabel | Kumpulan SQL khusus |
|---|---|---|
| Kota | Dimensi | wwi.DimCity |
| Pesanan | 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 berikut membuat tabel biasa 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, tetapi penggunaannya terbatas karena Anda dapat memilih dari tabel sementara tetapi tidak dapat menggabungkannya dengan file dalam 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.
Anda dapat mengimpor data dari tabel eksternal ke kumpulan SQL khusus menggunakan pernyataan CREATE TABLE AS SELECT (CTAS ). Untuk tutorial pemuatan, lihat Memuat himpunan data New York Taxicab.
Untuk kumpulan SQL tanpa server, Anda dapat menggunakan CREATE EXTERNAL TABLE AS SELECT (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. Untuk informasi selengkapnya tentang menggunakan jenis data, lihat Jenis data tabel di Synapse SQL.
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:
- Tabel round-robin (standar)
- Tabel terdistribusi hash
- Tabel yang direplikasi
Tabel round-robin
Tabel round-robin mendistribusikan baris-baris tabel secara merata ke seluruh distribusi. Distribusi baris dilakukan secara acak. Memuat data ke dalam tabel round-robin cepat, tetapi kueri dapat memerlukan lebih banyak pergerakan data daripada metode distribusi lainnya.
Untuk informasi selengkapnya, lihat Panduan desain untuk tabel terdistribusi.
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 selengkapnya, lihat Panduan desain untuk tabel terdistribusi.
Tabel yang direplikasi
Tabel yang direplikasi memiliki salinan lengkap tabel yang tersedia di setiap simpul komputasi. Kueri berjalan cepat pada tabel yang direplikasi karena bergabung pada tabel yang direplikasi tidak memerlukan perpindahan data. Namun, replikasi membutuhkan penyimpanan ekstra dan tidak praktis untuk tabel besar.
Untuk informasi selengkapnya, lihat Panduan desain untuk tabel yang direplikasi.
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. |
| Tahap | Gunakan pendekatan round-robin untuk tabel pentahapan tersebut. Beban dengan CTAS cepat. Setelah data berada dalam tabel penahapan, gunakan INSERT...SELECT untuk memindahkan data ke tabel produksi. |
Partitions
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 lebih lanjut, lihat Panduan Partisi.
Tip
Saat mengalihkan partisi ke partisi tabel yang tidak kosong, pertimbangkan untuk menggunakan opsi TRUNCATE_TARGET dalam pernyataan ALTER TABLE Anda jika data yang ada akan dipotong.
Kode berikut mengalihkan data harian yang sudah ditransformasi ke dalam partisi SalesFact dan menimpa semua data yang sudah ada.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Di kumpulan SQL tanpa server, Anda dapat membatasi file atau folder (partisi) yang dibaca oleh kueri Anda. Pemartisian menurut jalur didukung menggunakan fungsi filepath dan fileinfo yang dijelaskan dalam Melakukan kueri 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
Panduan 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 heap merupakan struktur penyimpanan yang sesuai.
Tips
Tabel heap dapat sangat berguna untuk memuat data sementara, seperti tabel staging, yang diubah menjadi tabel akhir.
Untuk daftar fitur penyimpan kolom, lihat Apa yang baru dalam 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. Anda dapat memperbarui statistik setelah sejumlah besar baris ditambahkan atau diubah. Misalnya, lakukan pembaruan statistik setelah pemuatan. Untuk informasi selengkapnya, lihat Statistik di Synapse SQL.
Kunci primer dan kunci unik
Untuk kumpulan SQL khusus, PRIMARY KEY hanya didukung saat NONCLUSTERED dan NOT ENFORCED keduanya digunakan.
UNIQUE batasan hanya didukung saat NOT ENFORCED digunakan. Untuk informasi selengkapnya, lihat Kunci primer, kunci asing, dan kunci unik menggunakan kumpulan 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. |
| BUAT TABEL DENGAN SELECT | Mengisi tabel baru dengan hasil dari pernyataan pilih. Kolom tabel dan jenis data didasarkan pada hasil perintah select. Untuk mengimpor data, pernyataan ini dapat memilih dari tabel eksternal. |
| CREATE EXTERNAL TABLE AS SELECT (membuat tabel eksternal sebagai 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 terhitung
- Tampilan terindeks
- Sekuens
- Kolom jarang terisi
- Kunci pengganti, terapkan dengan Identitas
- Sinonim
- Memicu
- Indeks Unik
- Jenis yang ditentukan pengguna
Pertanyaan 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 dapat memiliki keterbatasan. Tampilan manajemen dinamis (DMV) menampilkan lebih banyak detail daripada perintah DBCC. Mulailah dengan membuat tampilan berikut.
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 dapat melihat apakah mereka menggunakan metode penggiliran ("round-robin"), direplikasi, atau didistribusikan menggunakan pemetaan 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
;
Konten terkait
Setelah Anda membuat tabel untuk gudang data Anda, langkah selanjutnya adalah memuat data ke dalam tabel.