Azure Synapse Analytics 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

Yıldız ş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. Tablo tasarladığınızda, tablo verilerinin olgu, boyut 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ı , işlem sisteminde yaygın olarak oluşturulan ve ardından ayrılmış SQL havuzuna yüklenen nicel veriler içerir. Örneğin, 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, bir 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. Bir 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 yöntemdir.

Örneğin, tüm tabloları WideWorldImportersDW örnek ayrılmış SQL havuzunda adlı wwibir şema içinde depolayabilirsiniz. Aşağıdaki kod, kullanıcı tanımlı bir şema adlı wwi oluşturur.

CREATE SCHEMA wwi;

Ayrılmış SQL havuzundaki tabloların kuruluşunu göstermek için tablo adlarının ön ekleri olarak fact, dim ve int kullanabilirsiniz. Aşağıdaki tabloda için WideWorldImportersDWbazı şema ve tablo adları gösterilmektedir.

Tablo kalıcılığı

Tablolar verileri kalıcı olarak Azure Depolama, geçici olarak Azure Depolama veya ayrılmış SQL havuzu dışındaki bir veri deposunda depolar.

Standart tablo

Düzenli 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 blob 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. Azure blob depolama kaynaklarından veri yüklemek için PolyBase kullanımına dair yükleme öğreticisini görmek 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 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: round-robin (varsayılan), hash ve çoğaltılmış.

Hash dağıtımlı tablolar

Karma dağıtılmış tablo, dağıtım sütunundaki değere göre satırları 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 bir kopyasına sahiptir. Çoğaltılan tablolardaki birleştirmeler veri taşıma gerektirmediğinden sorgular çoğaltılan tablolarda hızlı çalışır. Çoğaltma için ek depolama gerekir, ancak 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.

Döngüsel tablolar

Round-robin tablosu, tablo satırlarını tüm dağıtımlar arasında dengeli bir şekilde dağıtır. Satırlar rastgele dağıtılır. Döngüsel bir tabloya veri yükleme işlemi 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 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. 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 Bölümleme kılavuzu bölümüne bakın. Bölme boş olmayan tablo bölmelerine geçiş yaparken, mevcut veriler silinecekse ALTER TABLE ifadenizde 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 ekleyerek mevcut verilerin yerine 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.

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.

İstatistik

Sorgu iyileştiricisi, sorgu yürütme planını oluştururken sütun düzeyinde istatistikleri kullanır.

Sorgu performansını geliştirmek için, özellikle sorgu birleştirmelerinde kullanılan sütunlar olmak üzere tek tek sütunlara ilişkin istatistiklerin olması önemlidir. İstatistik oluşturma işlemi otomatik olarak gerçekleşir.

İstatistikleri güncelleştirme 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

Birincil Anahtar yalnızca, YÖNDEKSİZ ve ZORLANMAMIŞ birlikte kullanıldığında desteklenir. UNIQUE kısıtlaması sadece NOT ENFORCED 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 bir tablo oluşturabilir ve select deyiminin sonuçlarıyla doldurabilirsiniz. Aşağıda tablo oluşturmaya yönelik T-SQL komutları yer alır.

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ı Kontrol et
• Hesaplanan Sütunlar
• İndeksli Görünümler
• Sequence
• Seyrek Sütunlar
• Vekil Anahtarlar. Kimlik ile uygulama.
• Synonyms
• Tetikleyiciler
• Benzersiz Dizinler
• Kullanıcı Tanımlı Türler

Tablo boyutu sorguları

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'ler), DBCC komutlarından daha fazla ayrıntı gösterir. Şu 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, her tabloya ait satırları ve kapladıkları alanı döndürür. Hangi tabloların sizin en büyük tablolarınız olduğunu ve bunların round-robin, ç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
;

İlgili içerik

Ayrılmış SQL havuzunuz için tabloları oluşturduktan sonra, sonraki adım tabloya veri yüklemektir. Yükleme öğreticisi için bkz. Verileri ayrılmış SQL havuzuna yükleme ve Azure Synapse Analytics içindeki ayrılmış SQL havuzu için Veri yükleme stratejilerini gözden geçirin.

• Azure Synapse SQL Dağıtım Danışmanı
• Azure Synapse Analytics'te ayrılmış SQL havuzu (eski adı SQL DW) mimarisi
• Azure Synapse Analytics'te ayrılmış SQL havuzu (eski adı SQL DW) için bilgi sayfası