Projektowanie tabel przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics
Ten artykuł zawiera kluczowe pojęcia wprowadzające dotyczące projektowania tabel w dedykowanej puli SQL.
Określanie kategorii tabel
Schemat star organizuje dane w tabelach faktów i wymiarów. Niektóre tabele są używane do integracji lub przemieszczania danych przed przejściem do tabeli faktów lub wymiarów. Podczas projektowania tabeli zdecyduj, czy dane tabeli należą do tabeli faktów, wymiarów czy tabeli integracji. Ta decyzja informuje o odpowiedniej strukturze tabeli i dystrybucji.
Tabele faktów zawierają dane ilościowe, które są często generowane w systemie transakcyjnym, a następnie ładowane do dedykowanej puli SQL. Na przykład firma detaliczna generuje transakcje sprzedaży codziennie, a następnie ładuje dane do dedykowanej tabeli faktów puli SQL na potrzeby analizy.
Tabele wymiarów zawierają dane atrybutów, które mogą ulec zmianie, ale zwykle zmieniają się rzadko. Na przykład nazwa i adres klienta są przechowywane w tabeli wymiarów i aktualizowane tylko wtedy, gdy profil klienta ulegnie zmianie. Aby zminimalizować rozmiar dużej tabeli faktów, nazwa i adres klienta nie muszą znajdować się w każdym wierszu tabeli faktów. Zamiast tego tabela faktów i tabela wymiarów mogą udostępniać identyfikator klienta. Zapytanie może łączyć dwie tabele w celu skojarzenia profilu i transakcji klienta.
Tabele integracji zapewniają miejsce na integrowanie lub przemieszczanie danych. Tabelę integracji można utworzyć jako zwykłą tabelę, tabelę zewnętrzną lub tabelę tymczasową. Na przykład można załadować dane do tabeli przejściowej, wykonać przekształcenia na danych w środowisku przejściowym, a następnie wstawić dane do tabeli produkcyjnej.
Nazwy schematów i tabel
Schematy są dobrym sposobem grupowania tabel używanych w podobny sposób. Jeśli migrujesz wiele baz danych z lokalnego rozwiązania do dedykowanej puli SQL, najlepiej migrować wszystkie tabele faktów, wymiarów i integracji do jednego schematu w dedykowanej puli SQL.
Na przykład można przechowywać wszystkie tabele w przykładowej dedykowanej puli SQL WideWorldImportersDW w jednym schemacie o nazwie wwi. Poniższy kod tworzy schemat zdefiniowany przez użytkownika o nazwie wwi.
CREATE SCHEMA wwi;
Aby pokazać organizację tabel w dedykowanej puli SQL, można użyć faktów, dim i int jako prefiksów do nazw tabel. W poniższej tabeli przedstawiono niektóre nazwy schematu i tabeli dla elementu WideWorldImportersDW.
| Tabela WideWorldImportersDW | Typ tabeli | Dedykowana pula SQL |
|---|---|---|
| City (Miasto) | Wymiar | Wwi. DimCity |
| Zamówienie | Fact | Wwi. FactOrder |
Trwałość tabeli
Tabele przechowują dane na stałe w usłudze Azure Storage, tymczasowo w usłudze Azure Storage lub w magazynie danych zewnętrznym dla dedykowanej puli SQL.
Zwykła tabela
Zwykła tabela przechowuje dane w usłudze Azure Storage w ramach dedykowanej puli SQL. Tabela i dane są utrwalane niezależnie od tego, czy sesja jest otwarta. Poniższy przykład tworzy zwykłą tabelę z dwiema kolumnami.
CREATE TABLE MyTable (col1 int, col2 int );
Tabela tymczasowa
Tabela tymczasowa istnieje tylko przez czas trwania sesji. Możesz użyć tabeli tymczasowej, aby uniemożliwić innym użytkownikom wyświetlanie tymczasowych wyników, a także zmniejszyć konieczność czyszczenia.
Tabele tymczasowe korzystają z magazynu lokalnego, aby zapewnić szybką wydajność. Aby uzyskać więcej informacji, zobacz Tabele tymczasowe.
Tabela zewnętrzna
Tabela zewnętrzna wskazuje dane znajdujące się w obiekcie blob usługi Azure Storage lub usłudze Azure Data Lake Store. W przypadku użycia z instrukcją CREATE TABLE AS SELECT wybranie z tabeli zewnętrznej importuje dane do dedykowanej puli SQL.
W związku z tym tabele zewnętrzne są przydatne do ładowania danych. Aby zapoznać się z samouczkiem dotyczącym ładowania, zobacz Ładowanie danych z usługi Azure Blob Storage przy użyciu technologii PolyBase.
Typy danych
Dedykowana pula SQL obsługuje najczęściej używane typy danych. Aby uzyskać listę obsługiwanych typów danych, zobacz typy danych w dokumentacji CREATE TABLE w instrukcji CREATE TABLE. Aby uzyskać wskazówki dotyczące używania typów danych, zobacz Typy danych.
Rozproszone tabele
Podstawową funkcją dedykowanej puli SQL jest sposób przechowywania i obsługi tabel w różnych dystrybucjach. Dedykowana pula SQL obsługuje trzy metody dystrybucji danych: działanie okrężne (domyślne), skrót i replikowane.
Tabele dystrybuowane przy użyciu skrótu
Tabela rozproszona skrótu dystrybuuje wiersze na podstawie wartości w kolumnie dystrybucji. Tabela rozproszona skrótu została zaprojektowana w celu osiągnięcia wysokiej wydajności zapytań w dużych tabelach. Podczas wybierania kolumny dystrybucji należy wziąć pod uwagę kilka czynników.
Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące projektowania tabel rozproszonych.
Zreplikowane tabele
Replikowana tabela zawiera pełną kopię tabeli dostępnej w każdym węźle obliczeniowym. Zapytania działają szybko na replikowanych tabelach, ponieważ sprzężenia w replikowanych tabelach nie wymagają przenoszenia danych. Jednak replikacja wymaga dodatkowego magazynu i nie jest praktyczna w przypadku dużych tabel.
Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące projektowania replikowanych tabel.
Tabele działania okrężnego
Tabela okrężna dystrybuuje wiersze tabeli równomiernie we wszystkich dystrybucjach. Wiersze są dystrybuowane losowo. Ładowanie danych do tabeli działania okrężnego jest szybkie. Należy pamiętać, że zapytania mogą wymagać większego przenoszenia danych niż inne metody dystrybucji.
Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące projektowania tabel rozproszonych.
Typowe metody dystrybucji tabel
Kategoria tabeli często określa, która opcja ma być wybrana do dystrybucji tabeli.
| Kategoria tabeli | Zalecana opcja dystrybucji |
|---|---|
| Fact | Użyj dystrybucji skrótów z klastrowanym indeksem magazynu kolumn. Wydajność zwiększa się, gdy dwie tabele skrótów są sprzężone w tej samej kolumnie dystrybucji. |
| Wymiar | Użyj replikowanej dla mniejszych tabel. Jeśli tabele są zbyt duże do przechowywania w każdym węźle obliczeniowym, użyj skrótu rozproszonego. |
| Przygotowanie | Użyj działania okrężnego dla tabeli przejściowej. Ładowanie za pomocą usługi CTAS jest szybkie. Gdy dane są w tabeli przejściowej, użyj polecenia INSERT... WYBIERZ, aby przenieść dane do tabel produkcyjnych. |
Uwaga
Aby uzyskać zalecenia dotyczące najlepszej strategii dystrybucji tabel do użycia na podstawie obciążeń, zobacz Azure Synapse SQL Distribution Advisor.
Partycje tabeli
Partycjonowana tabela przechowuje i wykonuje operacje na wierszach tabeli zgodnie z zakresami danych. Na przykład tabela może być podzielona na partycje według dnia, miesiąca lub roku. Wydajność zapytań można poprawić poprzez eliminację partycji, co ogranicza skanowanie zapytań do danych w ramach partycji. Dane można również przechowywać za pomocą przełączania partycji. Ponieważ dane w puli SQL są już dystrybuowane, zbyt wiele partycji może spowolnić wydajność zapytań. Aby uzyskać więcej informacji, zobacz Partitioning guidance (Wskazówki dotyczące partycjonowania). Podczas przełączania partycji na partycje tabeli, które nie są puste, rozważ użycie opcji TRUNCATE_TARGET w instrukcji ALTER TABLE , jeśli istniejące dane mają zostać obcięte. Poniższy kod przełącza się w przekształconych danych dziennych do elementu SalesFact zastępując wszystkie istniejące dane.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Indeksy magazynu kolumn
Domyślnie dedykowana pula SQL przechowuje tabelę jako indeks klastrowanego magazynu kolumn. Ta forma magazynu danych zapewnia wysoką kompresję danych i wydajność zapytań w dużych tabelach.
Indeks klastrowanego magazynu kolumn jest zazwyczaj najlepszym wyborem, ale w niektórych przypadkach indeks klastrowany lub stertę jest odpowiednią strukturą magazynu.
Porada
Tabela sterty może być szczególnie przydatna do ładowania danych przejściowych, takich jak tabela przejściowa, która jest przekształcana w końcową tabelę.
Aby uzyskać listę funkcji magazynu kolumn, zobacz Co nowego w przypadku indeksów magazynu kolumn. Aby zwiększyć wydajność indeksu magazynu kolumn, zobacz Maksymalizacja jakości grup wierszy dla indeksów magazynu kolumn.
Statystyki
Optymalizator zapytań używa statystyk na poziomie kolumny podczas tworzenia planu wykonywania zapytania.
Aby zwiększyć wydajność zapytań, ważne jest, aby statystyki dotyczące poszczególnych kolumn, zwłaszcza kolumn używanych w sprzężeniach zapytań. Tworzenie statystyk odbywa się automatycznie.
Aktualizowanie statystyk nie jest wykonywane automatycznie. Aktualizowanie statystyk po dodaniu lub zmianie dużej liczby wierszy. Na przykład zaktualizuj statystyki po załadowaniu. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące statystyk.
Klucz podstawowy i unikatowy klucz
Klucz PODSTAWOWY jest obsługiwany tylko wtedy, gdy są używane wartości NONCLUSTERED i NOT ENFORCED. Ograniczenie UNIQUE jest obsługiwane tylko w przypadku funkcji NOT ENFORCED. Sprawdź ograniczenia tabeli dedykowanej puli SQL.
Polecenia służące do tworzenia tabel
Tabelę można utworzyć jako nową pustą tabelę. Możesz również utworzyć i wypełnić tabelę wynikami instrukcji select. Poniżej przedstawiono polecenia języka T-SQL służące do tworzenia tabeli.
| T-SQL, instrukcja | Opis |
|---|---|
| CREATE TABLE | Tworzy pustą tabelę, definiując wszystkie kolumny i opcje tabeli. |
| TWORZENIE TABELI ZEWNĘTRZNEJ | Tworzy tabelę zewnętrzną. Definicja tabeli jest przechowywana w dedykowanej puli SQL. Dane tabeli są przechowywane w usłudze Azure Blob Storage lub Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Wypełnia nową tabelę wynikami instrukcji select. Kolumny tabeli i typy danych są oparte na wynikach instrukcji select. Aby zaimportować dane, ta instrukcja może wybrać z tabeli zewnętrznej. |
| CREATE EXTERNAL TABLE AS SELECT | Tworzy nową tabelę zewnętrzną, eksportując wyniki instrukcji select do lokalizacji zewnętrznej. Lokalizacja to Azure Blob Storage lub Azure Data Lake Store. |
Dopasowywanie danych źródłowych do dedykowanej puli SQL
Dedykowane tabele puli SQL są wypełniane przez ładowanie danych z innego źródła danych. Aby wykonać pomyślne ładowanie, liczba i typy danych kolumn w danych źródłowych muszą być zgodne z definicją tabeli w dedykowanej puli SQL. Dostosowanie danych może być najtrudniejszą częścią projektowania tabel.
Jeśli dane pochodzą z wielu magazynów danych, dane są ładowane do dedykowanej puli SQL i przechowywane w tabeli integracji. Gdy dane są w tabeli integracji, możesz użyć możliwości dedykowanej puli SQL do wykonywania operacji przekształcania. Po przygotowaniu danych można je wstawić do tabel produkcyjnych.
Nieobsługiwane funkcje tabeli
Dedykowana pula SQL obsługuje wiele funkcji tabeli oferowanych przez inne bazy danych, ale nie wszystkie. Na poniższej liście przedstawiono niektóre funkcje tabeli, które nie są obsługiwane w dedykowanej puli SQL:
- Klucz obcy, Sprawdzanie ograniczeń tabeli
- Kolumny obliczane
- Indeksowane widoki
- Sequence
- Kolumny rozrzedne
- Klucze zastępcze. Zaimplementuj za pomocą tożsamości.
- Synonimy
- Wyzwalacze
- Indeksy unikatowe
- Typy zdefiniowane przez użytkownika
Zapytania dotyczące rozmiaru tabeli
Uwaga
Aby uzyskać dokładne liczby zapytań w tej sekcji, upewnij się, że konserwacja indeksu odbywa się regularnie i po wprowadzeniu dużych zmian danych.
Jednym z prostych sposobów identyfikowania miejsca i wierszy używanych przez tabelę w każdej z 60 dystrybucji jest użycie PDW_SHOWSPACEUSED DBCC.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Jednak użycie poleceń DBCC może być dość ograniczające. Dynamiczne widoki zarządzania (DMV) pokazują więcej szczegółów niż polecenia DBCC. Zacznij od utworzenia tego widoku:
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
;
Podsumowanie obszaru tabeli
To zapytanie zwraca wiersze i spację według tabeli. Umożliwia ono sprawdzenie, które tabele są największymi tabelami i czy są one działaniem okrężnym, replikowanym lub rozproszonymi skrótami. W przypadku tabel rozproszonych przy użyciu skrótu zapytanie wyświetla kolumnę dystrybucji.
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
;
Odstęp w tabeli według typu dystrybucji
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
;
Odstęp w tabeli według typu indeksu
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
;
Podsumowanie obszaru dystrybucji
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
;
Następne kroki
Po utworzeniu tabel dla dedykowanej puli SQL następnym krokiem jest załadowanie danych do tabeli. Aby zapoznać się z samouczkiem ładowania, zobacz Ładowanie danych do dedykowanej puli SQL i zapoznaj się ze strategiami ładowania danych dla dedykowanej puli SQL w usłudze Azure Synapse Analytics.