Azure Synapse Analytics'te ayrılmış SQL havuzunu kullanarak tablolar tasarlama
Bu makalede, ayrılmış SQL havuzunda tablo tasarlamaya yönelik önemli giriş kavramları sağlanır.
Tablo kategorilerini belirleme
star şeması verileri olgu ve boyut tabloları halinde düzenler. Bazı tablolar, olgu veya boyut tablosuna geçmeden önce tümleştirme veya hazırlama verileri için kullanılır. Bir tabloyu tasarladığınızda, tablo verilerinin olguya, boyuta veya tümleştirme tablosuna ait olup olmadığına karar verin. Bu karar, uygun tablo yapısını ve dağılımını bilgilendirmektedir.
Olgu tabloları , bir işlem sisteminde yaygın olarak oluşturulan ve ardından ayrılmış SQL havuzuna yüklenen nicel veriler içerir. Örneğin, bir perakende işletmesi her gün satış işlemleri oluşturur ve ardından verileri analiz için ayrılmış bir SQL havuzu olgu tablosuna yükler.
Boyut tabloları değişebilen ancak genellikle seyrek değişen öznitelik verileri içerir. Örneğin, müşterinin adı ve adresi bir boyut tablosunda depolanır ve yalnızca müşterinin profili değiştiğinde güncelleştirilir. Büyük bir olgu tablosunun boyutunu en aza indirmek için müşterinin adının ve adresinin olgu tablosunun her satırında olması gerekmez. Bunun yerine olgu tablosu ve boyut tablosu müşteri kimliğini paylaşabilir. Sorgu, müşterinin profilini ve işlemlerini ilişkilendirmek için iki tabloyu birleştirebilir.
Tümleştirme tabloları , verileri tümleştirmek veya hazırlamak için bir yer sağlar. Tümleştirme tablosunu normal tablo, dış tablo veya geçici tablo olarak oluşturabilirsiniz. Örneğin, bir hazırlama tablosuna veri yükleyebilir, hazırlamadaki veriler üzerinde dönüştürmeler yapabilir ve ardından verileri bir üretim tablosuna ekleyebilirsiniz.
Şema ve tablo adları
Şemalar, benzer şekilde kullanılan tabloları birlikte gruplandırmak için iyi bir yoldur. Birden çok veritabanını şirket içi çözümden ayrılmış bir SQL havuzuna geçiriyorsanız tüm olgu, boyut ve tümleştirme tablolarını ayrılmış SQL havuzundaki bir şemaya geçirmek en iyi sonucu verir.
Örneğin, tüm tabloları WideWorldImportersDW örnek ayrılmış SQL havuzunda adlı wwibir şema içinde depolayabilirsiniz. Aşağıdaki kod adlı wwikullanıcı tanımlı bir şema oluşturur.
CREATE SCHEMA wwi;
Ayrılmış SQL havuzundaki tabloların kuruluşunu göstermek için tablo adlarına önek olarak fact, dim ve int kullanabilirsiniz. Aşağıdaki tabloda için WideWorldImportersDWbazı şema ve tablo adları gösterilmektedir.
| WideWorldImportersDW tablosu | Tablo türü | Ayrılmış SQL havuzu |
|---|---|---|
| Şehir | Boyut | Wwı. DimCity |
| Sipariş | Fact | Wwı. FactOrder |
Tablo kalıcılığı
Tablolar verileri kalıcı olarak Azure Depolama'da, geçici olarak Azure Depolama'da veya ayrılmış SQL havuzu dışında bir veri deposunda depolar.
Normal tablo
Normal bir tablo, verileri ayrılmış SQL havuzunun bir parçası olarak Azure Depolama'da depolar. Bir oturumun açık olup olmamasına bakılmaksızın tablo ve veriler kalıcıdır. Aşağıdaki örnek, iki sütunlu normal bir tablo oluşturur.
CREATE TABLE MyTable (col1 int, col2 int );
Geçici tablo
Geçici bir tablo yalnızca oturum süresi boyunca var olur. Diğer kullanıcıların geçici sonuçlar görmesini önlemek ve temizleme gereksinimini azaltmak için geçici bir tablo kullanabilirsiniz.
Geçici tablolar, hızlı performans sunmak için yerel depolamayı kullanır. Daha fazla bilgi için bkz . Geçici tablolar.
Dış tablo
Dış tablo, Azure Depolama blobunda veya Azure Data Lake Store'da bulunan verileri gösterir. CREATE TABLE AS SELECT deyimiyle kullanıldığında, dış tablodan seçim yapıldığında veriler ayrılmış SQL havuzuna aktarılır.
Bu nedenle, dış tablolar verileri yüklemek için yararlıdır. Yükleme öğreticisi için bkz. Azure blob depolamadan veri yüklemek için PolyBase kullanma.
Veri türleri
Ayrılmış SQL havuzu en yaygın kullanılan veri türlerini destekler. Desteklenen veri türlerinin listesi için CREATE TABLE deyimindeki CREATE TABLE başvurusu'ndaki veri türlerine bakın. Veri türlerini kullanma yönergeleri için bkz. Veri türleri.
Dağıtılmış tablolar
Ayrılmış SQL havuzunun temel özelliklerinden biri , dağıtımlar arasında tablolar üzerinde depolama ve çalışma şeklidir. Ayrılmış SQL havuzu, verileri dağıtmak için üç yöntemi destekler: hepsini bir kez deneme (varsayılan), karma ve çoğaltılmış.
Karma dağıtılmış tablolar
Karma dağıtılmış tablo, satırları dağıtım sütunundaki değere göre dağıtır. Karma dağıtılmış tablo, büyük tablolardaki sorgular için yüksek performans elde etmek üzere tasarlanmıştır. Dağıtım sütunu seçerken dikkate alınması gereken birkaç faktör vardır.
Daha fazla bilgi için bkz. Dağıtılmış tablolar için tasarım kılavuzu.
Çoğaltılmış tablolar
Çoğaltılan tablo, her İşlem düğümünde tablonun tam kopyasına sahiptir. Çoğaltılan tablolardaki birleştirmeler veri taşıma gerektirmediğinden sorgular çoğaltılan tablolarda hızlı çalışır. Yine de çoğaltma için ek depolama gerekir ve büyük tablolar için pratik değildir.
Daha fazla bilgi için bkz . Çoğaltılan tablolar için tasarım kılavuzu.
Hepsini bir kez deneme tabloları
Hepsini bir kez deneme tablosu, tablo satırlarını tüm dağıtımlar arasında eşit olarak dağıtır. Satırlar rastgele dağıtılır. Verileri hepsini bir kez deneme tablosuna yüklemek hızlıdır. Sorguların diğer dağıtım yöntemlerinden daha fazla veri taşıması gerektirebileceğini unutmayın.
Daha fazla bilgi için bkz. Dağıtılmış tablolar için tasarım kılavuzu.
Tablolar için yaygın dağıtım yöntemleri
Tablo kategorisi genellikle tabloyu dağıtmak için hangi seçeneğin seçileceğini belirler.
| Tablo kategorisi | Önerilen dağıtım seçeneği |
|---|---|
| Fact | Kümelenmiş columnstore diziniyle karma dağıtım kullanın. Aynı dağıtım sütununda iki karma tablo birleştirildiğinde performans artar. |
| Boyut | Daha küçük tablolar için çoğaltılmış kullanın. Tablolar her bir İşlem düğümünde depolamayacak kadar büyükse karma dağıtılmış seçeneğini kullanın. |
| Hazırlama | Hazırlama tablosu için hepsini bir kez deneme kullanın. CTAS ile yük hızlıdır. Veriler hazırlama tablosuna eklendikten sonra INSERT... Verileri üretim tablolarına taşımak için SELECT. |
Not
İş yüklerinize göre kullanılacak en iyi tablo dağıtım stratejisine ilişkin öneriler için bkz. sql dağıtım danışmanı Azure Synapse.
Tablo bölümleri
Bölümlenmiş tablo, veri aralıklarına göre tablo satırlarında işlemleri depolar ve gerçekleştirir. Örneğin, bir tablo güne, aya veya yıla göre bölümlenebilir. Bir sorgu taramasını bir bölüm içindeki verilerle sınırlayan bölüm eleme yoluyla sorgu performansını geliştirebilirsiniz. Verileri bölüm değiştirme yoluyla da koruyabilirsiniz. SQL havuzundaki veriler zaten dağıtılmış olduğundan, çok fazla bölüm sorgu performansını yavaşlatabilir. Daha fazla bilgi için bkz . Bölümleme kılavuzu. Bölüm boş olmayan tablo bölümlerine geçerken, var olan veriler kesilecekse ALTER TABLE deyiminizde TRUNCATE_TARGET seçeneğini kullanmayı göz önünde bulundurun. Aşağıdaki kod, dönüştürülen günlük verileri SalesFact'e geçirerek mevcut verilerin üzerine yazar.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Columnstore dizinleri
Varsayılan olarak, ayrılmış SQL havuzu bir tabloyu kümelenmiş columnstore dizini olarak depolar. Bu veri depolama biçimi, büyük tablolarda yüksek veri sıkıştırma ve sorgu performansı sağlar.
Kümelenmiş columnstore dizini genellikle en iyi seçenektir, ancak bazı durumlarda kümelenmiş dizin veya yığın uygun depolama yapısıdır.
İpucu
Yığın tablosu, son tabloya dönüştürülen hazırlama tablosu gibi geçici verileri yüklemek için özellikle yararlı olabilir.
Columnstore özelliklerinin listesi için bkz. Columnstore dizinleri için yenilikler. columnstore dizin performansını geliştirmek için bkz. Columnstore dizinleri için satır grubu kalitesini en üst düzeye çıkarma.
İstatistikler
Sorgu iyileştiricisi, sorgu yürütme planını oluştururken sütun düzeyinde istatistikler kullanır.
Sorgu performansını geliştirmek için, özellikle sorgu birleştirmelerinde kullanılan sütunlar olmak üzere tek tek sütunlarla ilgili istatistiklerin olması önemlidir. İstatistik oluşturma işlemi otomatik olarak gerçekleşir.
İstatistiklerin güncelleştirilmesi otomatik olarak gerçekleşmez. Önemli sayıda satır eklendikten veya değiştirildikten sonra istatistikleri güncelleştirin. Örneğin, bir yüklemeden sonra istatistikleri güncelleştirin. Daha fazla bilgi için bkz. İstatistik kılavuzu.
Birincil anahtar ve benzersiz anahtar
BİRİnCİl ANAHTAR yalnızca HEM KÜMELENDİ HEM DE ZORLANMADI olarak kullanıldığında desteklenir. BENZERSİz kısıtlaması yalnızca ZORLANMADI kullanıldığında desteklenir. Ayrılmış SQL havuzu tablosu kısıtlamalarını denetleyin.
Tablo oluşturmaya yönelik komutlar
Tabloyu yeni bir boş tablo olarak oluşturabilirsiniz. Ayrıca tablo oluşturabilir ve seçme deyiminin sonuçlarıyla doldurabilirsiniz. Aşağıda tablo oluşturmaya yönelik T-SQL komutları yer alır.
| T-SQL Deyimi | Açıklama |
|---|---|
| CREATE TABLE | Tüm tablo sütunlarını ve seçeneklerini tanımlayarak boş bir tablo oluşturur. |
| DıŞ TABLO OLUŞTURMA | Dış tablo oluşturur. Tablonun tanımı ayrılmış SQL havuzunda depolanır. Tablo verileri Azure Blob depolama alanında veya Azure Data Lake Store'da depolanır. |
| CREATE TABLE AS SELECT | Yeni bir tabloyu select deyiminin sonuçlarıyla doldurur. Tablo sütunları ve veri türleri select deyimi sonuçlarını temel alır. Verileri içeri aktarmak için bu deyim dış tablodan seçim yapabilir. |
| SELECT OLARAK DıŞ TABLO OLUŞTURMA | Select deyiminin sonuçlarını dış konuma aktararak yeni bir dış tablo oluşturur. Konum, Azure Blob depolama veya Azure Data Lake Store'dur. |
Kaynak verileri ayrılmış SQL havuzuyla hizalama
Ayrılmış SQL havuzu tabloları, başka bir veri kaynağından veri yüklenerek doldurulur. Başarılı bir yük gerçekleştirmek için kaynak verilerdeki sütunların sayısı ve veri türleri ayrılmış SQL havuzundaki tablo tanımıyla uyumlu olmalıdır. Verileri hizalamak, tablolarınızı tasarlamanın en zor kısmı olabilir.
Veriler birden çok veri deposundan geliyorsa, verileri ayrılmış SQL havuzuna yükler ve bir tümleştirme tablosunda depolarsınız. Veriler tümleştirme tablosuna eklendikten sonra, dönüştürme işlemlerini gerçekleştirmek için ayrılmış SQL havuzunun gücünü kullanabilirsiniz. Veriler hazırlandıktan sonra üretim tablolarına ekleyebilirsiniz.
Desteklenmeyen tablo özellikleri
Ayrılmış SQL havuzu, diğer veritabanları tarafından sunulan tablo özelliklerinin çoğunu destekler ancak tümünü desteklemez. Aşağıdaki listede ayrılmış SQL havuzunda desteklenmeyen bazı tablo özellikleri gösterilmektedir:
- Yabancı anahtar, Tablo Kısıtlamalarını Denetle
- Hesaplanan Sütunlar
- Dizinli Görünümler
- Sequence
- Seyrek Sütunlar
- Vekil Anahtarlar. Identity ile uygulama.
- Eş Anlamlı Sözcükler
- Tetikleyiciler
- Benzersiz Dizinler
- Kullanıcı Tanımlı Türler
Tablo boyutu sorguları
Not
Bu bölümdeki sorgulardan doğru sayımlar için dizin bakımının düzenli aralıklarla ve büyük veri değişikliklerinden sonra gerçekleştiğinden emin olun.
60 dağıtımın her birinde bir tablo tarafından kullanılan alanı ve satırları tanımlamanın basit yollarından biri DBCC PDW_SHOWSPACEUSED kullanmaktır.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Ancak DBCC komutlarını kullanmak oldukça sınırlayıcı olabilir. Dinamik yönetim görünümleri (DMV) DBCC komutlarından daha fazla ayrıntı gösterir. Bu görünümü oluşturarak başlayın:
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
;
Tablo alanı özeti
Bu sorgu tabloya göre satırları ve boşluğu döndürür. Hangi tabloların en büyük tablolarınız olduğunu ve bunların hepsini bir kez deneme, çoğaltılmış veya karma dağıtılmış olup olmadığını görmenizi sağlar. Karma dağıtılmış tablolar için sorgu dağıtım sütununu gösterir.
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
;
Dağıtım türüne göre tablo alanı
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
;
Dizin türüne göre tablo alanı
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
;
Dağıtım alanı özeti
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
;
Sonraki adımlar
Ayrılmış SQL havuzunuzun tablolarını oluşturduktan sonra, sonraki adım tabloya veri yüklemektir. Yükleme öğreticisi için bkz. Ayrılmış SQL havuzuna veri yükleme ve Azure Synapse Analytics'te ayrılmış SQL havuzu için veri yükleme stratejileri.