Tworzenie tabel magazynu danych
Teraz, gdy znasz podstawowe zasady architektury schematu relacyjnego magazynu danych, zobaczmy, jak utworzyć magazyn danych.
Tworzenie dedykowanej puli SQL
Aby utworzyć magazyn danych relacyjnych w usłudze Azure Synapse Analytics, musisz utworzyć dedykowaną pulę SQL. Najprostszym sposobem wykonania tej czynności w istniejącym obszarze roboczym usługi Azure Synapse Analytics jest użycie strony Zarządzanie w usłudze Azure Synapse Studio, jak pokazano poniżej:
Podczas aprowizowania dedykowanej puli SQL można określić następujące ustawienia konfiguracji:
- Unikatowa nazwa dedykowanej puli SQL.
- Poziom wydajności puli SQL, który może wahać się od DW100c do DW30000c i który określa koszt na godzinę dla puli, gdy jest uruchomiona.
- Niezależnie od tego, czy należy zacząć od pustej puli, czy przywrócić istniejącą bazę danych z kopii zapasowej.
- Sortowanie puli SQL, która określa kolejność sortowania i reguły porównania ciągów dla bazy danych. (Nie można zmienić sortowania po utworzeniu).
Po utworzeniu dedykowanej puli SQL możesz kontrolować jego stan działania na stronie Zarządzanie programu Synapse Studio; wstrzymując ją, gdy nie jest to wymagane, aby zapobiec niepotrzebnym kosztom.
Gdy pula jest uruchomiona, możesz eksplorować ją na stronie Dane i utworzyć skrypty SQL do uruchomienia.
Zagadnienia dotyczące tworzenia tabel
Aby utworzyć tabele w dedykowanej puli SQL, należy użyć CREATE TABLE instrukcji (lub czasami CREATE EXTERNAL TABLE) języka Transact-SQL. Określone opcje używane w instrukcji zależą od typu tworzonej tabeli, która może obejmować:
- Tabele faktów
- Tabele wymiarów
- Tabele przejściowe
Uwaga
Magazyn danych składa się z tabel faktów i wymiarów, jak opisano wcześniej. Tabele przejściowe są często używane w ramach procesu ładowania magazynu danych w celu pozyskiwania danych z systemów źródłowych.
Podczas projektowania modelu schematu gwiazdy dla małych lub średnich zestawów danych można użyć preferowanej bazy danych, takiej jak Azure SQL. W przypadku większych zestawów danych możesz skorzystać z implementacji magazynu danych w usłudze Azure Synapse Analytics zamiast programu SQL Server. Ważne jest, aby zrozumieć pewne kluczowe różnice podczas tworzenia tabel w usłudze Synapse Analytics.
Ograniczenia integralności danych
Dedykowane pule SQL w usłudze Synapse Analytics nie obsługują kluczy obcych i unikatowych ograniczeń, jak znaleziono w innych systemach relacyjnych baz danych, takich jak SQL Server. Oznacza to, że zadania używane do ładowania danych muszą zachować unikatowość i integralność referencyjną kluczy bez polegania na definicjach tabeli w bazie danych.
Napiwek
Aby uzyskać więcej informacji na temat ograniczeń w dedykowanych pulach SQL usługi Azure Synapse Analytics, zobacz Klucz podstawowy, klucz obcy i unikatowy klucz przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics.
Indeksy
Chociaż dedykowane pule SQL usługi Synapse Analytics obsługują indeksy klastrowane , jak znaleziono w programie SQL Server, domyślnym typem indeksu jest klasterowany magazyn kolumn. Ten typ indeksu zapewnia znaczącą przewagę wydajności podczas wykonywania zapytań dotyczących dużych ilości danych w typowym schemacie magazynu danych i powinien być używany tam, gdzie to możliwe. Jednak niektóre tabele mogą zawierać typy danych, których nie można uwzględnić w klastrowanym indeksie magazynu kolumn (na przykład VARBINARY(MAX)), w tym przypadku można użyć indeksu klastrowanego.
Napiwek
Aby uzyskać więcej informacji na temat indeksowania w dedykowanych pulach SQL usługi Azure Synapse Analytics, zobacz Indeksy w dedykowanych tabelach puli SQL w usłudze Azure Synapse Analytics.
Dystrybucja
Dedykowane pule SQL usługi Azure Synapse Analytics używają architektury masowego przetwarzania równoległego (MPP), w przeciwieństwie do architektury symetrycznego wieloprocesorowego (SMP) używanej w większości systemów baz danych OLTP. W systemie MPP dane w tabeli są dystrybuowane do przetwarzania w puli węzłów. Usługa Synapse Analytics obsługuje następujące rodzaje dystrybucji:
- Skrót: wartość skrótu deterministycznego jest obliczana dla określonej kolumny i używana do przypisywania wiersza do węzła obliczeniowego.
- Działanie okrężne: wiersze są dystrybuowane równomiernie we wszystkich węzłach obliczeniowych.
- Replikowane: kopia tabeli jest przechowywana w każdym węźle obliczeniowym.
Typ tabeli często określa, którą opcję należy wybrać do dystrybucji tabeli.
| Typ tabeli | Zalecana opcja dystrybucji |
|---|---|
| Wymiar | Użyj replikowanej dystrybucji dla mniejszych tabel, aby uniknąć mieszania danych podczas łączenia z tabelami faktów rozproszonych. Jeśli tabele są zbyt duże do przechowywania w każdym węźle obliczeniowym, użyj dystrybucji skrótów. |
| Fakt | Użyj dystrybucji skrótów z klastrowanym indeksem magazynu kolumn, aby dystrybuować tabele faktów między węzłami obliczeniowymi. |
| Przygotowanie | Użyj dystrybucji okrężnej dla tabel przejściowych, aby równomiernie dystrybuować dane między węzłami obliczeniowymi. |
Napiwek
Aby uzyskać więcej informacji na temat strategii dystrybucji tabel w usłudze Azure Synapse Analytics, zobacz Wskazówki dotyczące projektowania tabel rozproszonych przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics.
Tworzenie tabel wymiarów
Podczas tworzenia tabeli wymiarów upewnij się, że definicja tabeli zawiera klucze zastępcze i alternatywne, a także kolumny atrybutów wymiaru, które mają być używane do grupowania agregacji. Często najłatwiej jest użyć IDENTITY kolumny do automatycznego generowania przyrostowego klucza zastępczego (w przeciwnym razie należy wygenerować unikatowe klucze za każdym razem, gdy ładujesz dane). Poniższy przykład przedstawia instrukcję CREATE TABLE hipotetycznej tabeli wymiarów DimCustomer .
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Uwaga
W razie potrzeby możesz utworzyć określony schemat jako przestrzeń nazw dla tabel. W tym przykładzie jest używany domyślny schemat dbo .
Jeśli zamierzasz użyć schematu płatka śniegu, w którym tabele wymiarów są ze sobą powiązane, należy uwzględnić klucz wymiaru nadrzędnego w definicji tabeli wymiarów podrzędnych. Na przykład poniższy kod SQL może służyć do przenoszenia szczegółów adresu geograficznego z tabeli DimCustomer do oddzielnej tabeli wymiarów DimGeography :
CREATE TABLE dbo.DimGeography
(
GeographyKey INT IDENTITY NOT NULL,
GeographyAlternateKey NVARCHAR(10) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
GeographyKey INT NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Tabele wymiarów czasu
Większość magazynów danych zawiera tabelę wymiarów czasu , która umożliwia agregowanie danych według wielu hierarchicznych poziomów interwału czasu. Na przykład poniższy przykład tworzy tabelę DimDate z atrybutami, które odnoszą się do określonych dat.
CREATE TABLE dbo.DimDate
(
DateKey INT NOT NULL,
DateAltKey DATETIME NOT NULL,
DayOfMonth INT NOT NULL,
DayOfWeek INT NOT NULL,
DayName NVARCHAR(15) NOT NULL,
MonthOfYear INT NOT NULL,
MonthName NVARCHAR(15) NOT NULL,
CalendarQuarter INT NOT NULL,
CalendarYear INT NOT NULL,
FiscalQuarter INT NOT NULL,
FiscalYear INT NOT NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Napiwek
Typowy wzorzec podczas tworzenia tabeli wymiarów dla dat polega na użyciu daty liczbowej w formacie DDMMYYYYYYYY lub RRRRMMDD jako klucza zastępczego liczby całkowitej oraz daty jako DATE DATETIME lub typu danych jako klucza alternatywnego.
Tworzenie tabel faktów
Tabele faktów zawierają klucze dla każdego wymiaru, do którego są powiązane, oraz atrybuty i miary liczbowe dla określonych zdarzeń lub obserwacji, które chcesz przeanalizować.
Poniższy przykład kodu tworzy hipotetyczną tabelę faktów o nazwie FactSales , która jest powiązana z wieloma wymiarami za pomocą kluczowych kolumn (daty, klienta, produktu i sklepu)
CREATE TABLE dbo.FactSales
(
OrderDateKey INT NOT NULL,
CustomerKey INT NOT NULL,
ProductKey INT NOT NULL,
StoreKey INT NOT NULL,
OrderNumber NVARCHAR(10) NOT NULL,
OrderLineItem INT NOT NULL,
OrderQuantity SMALLINT NOT NULL,
UnitPrice DECIMAL NOT NULL,
Discount DECIMAL NOT NULL,
Tax DECIMAL NOT NULL,
SalesAmount DECIMAL NOT NULL
)
WITH
(
DISTRIBUTION = HASH(OrderNumber),
CLUSTERED COLUMNSTORE INDEX
);
Tworzenie tabel przejściowych
Tabele przejściowe są używane jako magazyn tymczasowy dla danych podczas ładowania ich do magazynu danych. Typowy wzorzec polega na utworzeniu struktury tabeli w celu zapewnienia jak największej wydajności pozyskiwania danych ze źródła zewnętrznego (często plików w usłudze Data Lake) do relacyjnej bazy danych, a następnie załadowania danych z tabel przejściowych do tabel wymiarów i faktów za pomocą instrukcji SQL.
Poniższy przykład kodu tworzy tabelę przejściową dla danych produktu, które ostatecznie zostaną załadowane do tabeli wymiarów:
CREATE TABLE dbo.StageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
);
Używanie tabel zewnętrznych
W niektórych przypadkach, jeśli dane do załadowania są w plikach z odpowiednią strukturą, bardziej skuteczne może być tworzenie tabel zewnętrznych odwołujących się do lokalizacji pliku. Dzięki temu dane mogą być odczytywane bezpośrednio z plików źródłowych, a nie ładowane do magazynu relacyjnego. W poniższym przykładzie pokazano, jak utworzyć tabelę zewnętrzną, która odwołuje się do plików w usłudze Data Lake skojarzonej z obszarem roboczym usługi Synapse:
-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO
-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DATA_SOURCE = StagedFiles,
LOCATION = 'products/*.parquet',
FILE_FORMAT = ParquetFormat
);
GO
Uwaga
Aby uzyskać więcej informacji na temat używania tabel zewnętrznych, zobacz Używanie tabel zewnętrznych z usługą Synapse SQL w dokumentacji usługi Azure Synapse Analytics.