Wskazówki dotyczące projektowania tabel rozproszonych przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics

Ten artykuł zawiera zalecenia dotyczące projektowania tabel rozproszonych skrótów i rozproszonych okrężnie w dedykowanych pulach SQL.

W tym artykule założono, że znasz pojęcia dotyczące dystrybucji danych i przenoszenia danych w dedykowanej puli SQL. Aby uzyskać więcej informacji, zobacz architektura usługi Azure Synapse Analytics.

Co to jest tabela rozproszona?

Tabela rozproszona jest wyświetlana jako pojedyncza tabela, ale wiersze są rzeczywiście przechowywane w 60 dystrybucjach. Wiersze są dystrybuowane za pomocą algorytmu skrótu lub działania okrężnego.

Dystrybucja skrótów zwiększa wydajność zapytań w dużych tabelach faktów i koncentruje się na tym artykule. Dystrybucja działania okrężnego jest przydatna do poprawy szybkości ładowania. Te wybory projektowe mają znaczący wpływ na poprawę wydajności zapytań i ładowania.

Inną opcją magazynu tabel jest replikowanie małej tabeli we wszystkich węzłach obliczeniowych. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące projektowania replikowanych tabel. Aby szybko wybrać jedną z trzech opcji, zobacz Tabele rozproszone w przeglądzie tabel.

W ramach projektowania tabel należy zrozumieć jak najwięcej danych i sposób wykonywania zapytań dotyczących danych.  Rozważmy na przykład następujące pytania:

• Jak duża jest tabela?
• Jak często jest odświeżona tabela?
• Czy mam tabele faktów i wymiarów w dedykowanej puli SQL?

Rozproszony skrót

Tabela rozproszona skrótem dystrybuuje wiersze tabeli w węzłach obliczeniowych przy użyciu funkcji skrótu deterministycznego w celu przypisania każdego wiersza do jednej dystrybucji.

Ponieważ identyczne wartości są zawsze skrótem do tej samej dystrybucji, usługa SQL Analytics ma wbudowaną wiedzę na temat lokalizacji wierszy. W dedykowanej puli SQL ta wiedza służy do minimalizowania przenoszenia danych podczas zapytań, co zwiększa wydajność zapytań.

Tabele rozproszone skrótami działają dobrze w przypadku dużych tabel faktów w schemacie gwiazdy. Mogą mieć bardzo dużą liczbę wierszy i nadal osiągnąć wysoką wydajność. Istnieją pewne zagadnienia projektowe, które ułatwiają uzyskanie wydajności systemu rozproszonego, który został zaprojektowany w celu zapewnienia. Wybór dobrej kolumny dystrybucji lub kolumn jest jednym z takich zagadnień, które opisano w tym artykule.

Rozważ użycie tabeli rozproszonej skrótów, gdy:

• Rozmiar tabeli na dysku wynosi ponad 2 GB.
• Tabela ma częste operacje wstawiania, aktualizowania i usuwania.

Rozproszone działanie okrężne

Tabela rozproszona z działaniem okrężnym dystrybuuje wiersze tabeli równomiernie we wszystkich dystrybucjach. Przypisanie wierszy do dystrybucji jest losowe. W przeciwieństwie do tabel rozproszonych skrótami wiersze o równych wartościach nie mają gwarancji przypisania do tej samej dystrybucji.

W rezultacie system czasami musi wywołać operację przenoszenia danych, aby lepiej zorganizować dane przed rozwiązaniem zapytania. Ten dodatkowy krok może spowolnić zapytania. Na przykład łączenie tabeli działania okrężnego zwykle wymaga przetasowania wierszy, co jest trafieniem wydajności.

Rozważ użycie dystrybucji działania okrężnego dla tabeli w następujących scenariuszach:

• Gdy rozpoczynasz pracę jako prosty punkt początkowy, ponieważ jest to wartość domyślna
• Jeśli nie ma oczywistego klucza dołączania
• Jeśli nie ma dobrej kolumny kandydata na skrót rozdzielając tabelę
• Jeśli tabela nie ma wspólnego klucza sprzężenia z innymi tabelami
• Jeśli sprzężenia są mniej znaczące niż inne sprzężenia w zapytaniu
• Gdy tabela jest tymczasową tabelą przejściową

Samouczek Load New York taxicab data (Ładowanie danych podatkowych w Nowym Jorku ) przedstawia przykład ładowania danych do tabeli przejściowej działania okrężnego.

Wybieranie kolumny dystrybucji

Tabela rozproszona skrótem zawiera kolumnę rozkładu lub zestaw kolumn, które są kluczem skrótu. Na przykład poniższy kod tworzy tabelę rozproszoną skrótem z ProductKey kolumną dystrybucji.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

Rozkład skrótów można zastosować na wielu kolumnach w celu bardziej równomiernego rozkładu tabeli podstawowej. Dystrybucja wielokolumna pozwala wybrać maksymalnie osiem kolumn do dystrybucji. Zmniejsza to nie tylko niesymetryczność danych w czasie, ale także poprawia wydajność zapytań. Przykład:

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Uwaga

Rozkład wielokolumny w usłudze Azure Synapse Analytics można włączyć, zmieniając poziom zgodności bazy danych na 50 za pomocą tego polecenia. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Aby uzyskać więcej informacji na temat ustawiania poziomu zgodności bazy danych, zobacz ALTER DATABASE SCOPED CONFIGURATION (ALTER DATABASE SCOPED CONFIGURATION). Aby uzyskać więcej informacji na temat dystrybucji wielokolumnach, zobacz CREATE MATERIALIZED VIEW, CREATE TABLE lub CREATE TABLEAS SELECT( CREATE TABLE AS SELECT).

Dane przechowywane w kolumnach dystrybucji można zaktualizować. Aktualizacje do danych w kolumnach dystrybucji może spowodować operację mieszania danych.

Wybór kolumn dystrybucji jest ważną decyzją projektową, ponieważ wartości w kolumnach skrótów określają sposób dystrybucji wierszy. Najlepszy wybór zależy od kilku czynników i zwykle wiąże się z kompromisami. Po wybraniu kolumny dystrybucji lub zestawu kolumn nie można go zmienić. Jeśli nie wybrano najlepszych kolumn po raz pierwszy, możesz użyć polecenia CREATE TABLE AS SELECT (CTAS), aby ponownie utworzyć tabelę z żądanym kluczem skrótu dystrybucji.

Wybieranie kolumny dystrybucji z danymi, które są dystrybuowane równomiernie

Aby uzyskać najlepszą wydajność, wszystkie dystrybucje powinny mieć mniej więcej taką samą liczbę wierszy. Gdy co najmniej jedna dystrybucja ma nieproporcjonalną liczbę wierszy, niektóre dystrybucje zakończą część zapytania równoległego przed innymi. Ponieważ zapytanie nie może zakończyć się, dopóki wszystkie dystrybucje nie zakończą przetwarzania, każde zapytanie jest tylko tak szybkie, jak najwolniejsza dystrybucja.

• Niesymetryczność danych oznacza, że dane nie są równomiernie dystrybuowane w dystrybucjach
• Niesymetryczność przetwarzania oznacza, że niektóre dystrybucje trwa dłużej niż inne podczas uruchamiania zapytań równoległych. Może się to zdarzyć, gdy dane są niesymetryczne.

Aby zrównoważyć przetwarzanie równoległe, wybierz kolumnę dystrybucji lub zestaw kolumn, które:

• Ma wiele unikatowych wartości. Kolumny dystrybucji mogą mieć zduplikowane wartości. Wszystkie wiersze o tej samej wartości są przypisywane do tej samej dystrybucji. Ponieważ istnieje 60 dystrybucji, niektóre dystrybucje mogą mieć > 1 unikatowe wartości, podczas gdy inne mogą kończyć się wartościami zerowymi.
• Nie ma nUL lub ma tylko kilka NULLs. W skrajnym przykładzie, jeśli wszystkie wartości w kolumnach dystrybucji mają wartość NULL, wszystkie wiersze są przypisywane do tej samej dystrybucji. W związku z tym przetwarzanie zapytań jest niesymetryczne do jednej dystrybucji i nie korzysta z przetwarzania równoległego.
• Nie jest kolumną daty. Wszystkie dane dla tej samej daty lądują w tej samej dystrybucji lub będą rejestrowane według daty. Jeśli kilku użytkowników filtruje tę samą datę (na przykład bieżącą datę), tylko 1 z 60 dystrybucji wykonuje wszystkie zadania przetwarzania.

Wybieranie kolumny dystrybucji, która minimalizuje przenoszenie danych

Aby uzyskać poprawne zapytania, zapytania mogą przenosić dane z jednego węzła obliczeniowego do innego. Przenoszenie danych występuje często, gdy zapytania mają sprzężenia i agregacje w tabelach rozproszonych. Wybór kolumny dystrybucji lub zestawu kolumn, który pomaga zminimalizować przenoszenie danych, jest jedną z najważniejszych strategii optymalizacji wydajności dedykowanej puli SQL.

Aby zminimalizować przenoszenie danych, wybierz kolumnę dystrybucji lub zestaw kolumn, które:

• Jest używany w JOINklauzulach , GROUP BY, DISTINCT, OVERi HAVING . Gdy dwie duże tabele faktów mają częste sprzężenia, wydajność zapytań poprawia się podczas dystrybucji obu tabel w jednej z kolumn sprzężenia. Jeśli tabela nie jest używana w sprzężeniach, rozważ dystrybucję tabeli w zestawie kolumn lub kolumn, który jest często w klauzuli GROUP BY .
• Nie jest używany w WHERE klauzulach. Jeśli klauzula WHERE zapytania i kolumny dystrybucji tabeli znajdują się w tej samej kolumnie, zapytanie może napotkać duże niesymetryczność danych, co prowadzi do obciążenia przetwarzania spadającego tylko na kilka dystrybucji. Ma to wpływ na wydajność zapytań, w idealnym przypadku wiele dystrybucji współdzieli obciążenie przetwarzania.
• Nie jest kolumną daty. WHERE klauzule często filtrowane według daty. W takim przypadku wszystkie operacje przetwarzania mogą być uruchamiane tylko w kilku dystrybucjach wpływających na wydajność zapytań. W idealnym przypadku wiele dystrybucji współdzieli obciążenie przetwarzania.

Po zaprojektowaniu tabeli rozproszonej skrótów następnym krokiem jest załadowanie danych do tabeli. Aby uzyskać wskazówki dotyczące ładowania, zobacz Omówienie ładowania.

Jak sprawdzić, czy dystrybucja jest dobrym wyborem

Po załadowaniu danych do tabeli rozproszonej przy użyciu skrótów sprawdź, jak równomiernie wiersze są dystrybuowane w 60 dystrybucjach. Wiersze na dystrybucję mogą się różnić do 10% bez zauważalnego wpływu na wydajność. Rozważ następujące tematy, aby ocenić kolumny dystrybucji.

Ustal, czy tabela ma niesymetryczność danych

Szybkim sposobem sprawdzenia niesymetryczności danych jest użycie PDW_SHOWSPACEUSED DBCC. Poniższy kod SQL zwraca liczbę wierszy tabeli przechowywanych w każdym z 60 dystrybucji. W celu zapewnienia zrównoważonej wydajności wiersze w tabeli rozproszonej powinny być rozłożone równomiernie we wszystkich dystrybucjach.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Aby określić, które tabele mają ponad 10% niesymetryczności danych:

1. Utwórz widok dbo.vTableSizes wyświetlany w artykule Omówienie tabel .
2. Uruchom poniższe zapytanie:

select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

Sprawdzanie planów zapytań dotyczących przenoszenia danych

Dobry zestaw kolumn dystrybucji umożliwia sprzężenia i agregacje w celu minimalnego przenoszenia danych. Ma to wpływ na sposób pisania sprzężeń. Aby uzyskać minimalny ruch danych dla sprzężenia w dwóch tabelach rozproszonych przy użyciu skrótu, jedna z kolumn sprzężeń musi znajdować się w kolumnach lub kolumnach dystrybucji. Gdy dwie tabele rozproszone przy użyciu skrótu są łączone w kolumnie dystrybucji tego samego typu danych, sprzężenia nie wymagają przenoszenia danych. Sprzężenia mogą używać dodatkowych kolumn bez ponoszenia ruchu danych.

Aby uniknąć przenoszenia danych podczas sprzężenia:

• Tabele biorące udział w sprzężeniu muszą być rozdzielone skrótami w jednej z kolumn uczestniczących w sprzężeniu.
• Typy danych kolumn sprzężenia muszą być zgodne z obydwoma tabelami.
• Kolumny muszą być połączone z operatorem równości.
• Typ sprzężenia może nie być typem CROSS JOIN.

Aby sprawdzić, czy w zapytaniach występują przenoszenie danych, możesz przyjrzeć się planowi zapytań.

Rozwiązywanie problemu z kolumną dystrybucji

Nie jest konieczne rozwiązanie wszystkich przypadków niesymetryczności danych. Dystrybucja danych polega na znalezieniu odpowiedniej równowagi między minimalizacją niesymetryczności danych a przenoszeniem danych. Nie zawsze jest możliwe zminimalizowanie zarówno niesymetryczności danych, jak i przenoszenia danych. Czasami korzyści wynikające z minimalnego przenoszenia danych mogą przeważyć nad wpływem niesymetryczności danych.

Aby zdecydować, czy należy rozwiązać niesymetryczność danych w tabeli, należy poznać jak najwięcej woluminów danych i zapytań w obciążeniu. Kroki opisane w artykule Monitorowanie zapytań umożliwiają monitorowanie wpływu niesymetryczności zapytań na wydajność zapytań. W szczególności sprawdź, jak długo trwa wykonywanie dużych zapytań w poszczególnych dystrybucjach.

Ponieważ nie można zmienić kolumn dystrybucji w istniejącej tabeli, typowym sposobem rozwiązania niesymetryczności danych jest ponowne utworzenie tabeli z różnymi kolumnami dystrybucji.

Utwórz ponownie tabelę przy użyciu nowego zestawu kolumn dystrybucji

W tym przykładzie użyto polecenia CREATE TABLE AS SELECT , aby ponownie utworzyć tabelę z inną kolumną lub kolumnami dystrybucji skrótów.

Najpierw użyj CREATE TABLE AS SELECT (CTAS) nowej tabeli z nowym kluczem. Następnie ponownie utwórz statystyki i na koniec zamień tabele przez ponowne nadanie im nazwy.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Następne kroki

Aby utworzyć tabelę rozproszoną, użyj jednej z następujących instrukcji:

• CREATE TABLE (dedykowana pula SQL)
• CREATE TABLE AS SELECT (dedykowana pula SQL)