Создание таблиц хранилища данных
Итак, вы изучили основные принципы для разработки схемы реляционного хранилища данных. Теперь давайте рассмотрим, как создать хранилище данных.
Создание выделенного пула SQL
Чтобы создать реляционное хранилище данных в Azure Synapse Analytics, сначала необходимо создать выделенный пул SQL. В существующей рабочей области Azure Synapse Analytics для этого проще всего использовать страницу Управление в Azure Synapse Studio, как показано ниже.
При подготовке выделенного пула SQL вы можете указать следующие параметры конфигурации:
- уникальное имя для выделенного пула SQL;
- уровень производительности пула SQL, который может варьировать от DW100c до DW30000c и определяет стоимость каждого часа работы пула;
- выбор между началом работы с пустым пулом и восстановлением существующей резервной копии базы данных;
- параметры сортировки для пула SQL, в том числе порядок сортировки и правила сравнения строк для базы данных. (После создания вы не сможете изменить параметры сортировки).
После создания выделенного пула SQL вы можете управлять его состоянием выполнения на странице Управление в Synapse Studio, то есть приостанавливать его работу при отсутствии загрузки, чтобы избежать лишних затрат.
На странице Данные можно проверить состояние работающего пула и создать скрипты SQL для выполнения в нем.
Рекомендации по созданию таблиц
Чтобы создать таблицы в выделенном пуле SQL, используйте инструкцию Transact-SQL CREATE TABLE (в некоторых случаях CREATE EXTERNAL TABLE). Конкретные параметры для этой инструкции зависят от типа создаваемой таблицы, которая может быть следующим:
- Таблицы фактов
- Таблицы измерений
- Промежуточные таблицы
Примечание.
Хранилище данных состоит из таблиц для фактов и измерений, как уже обсуждалось ранее. Промежуточные таблицы часто используются в процессе заполнения хранилища данных для приема данных из источников.
При проектировании модели схемы типа "звезда" для наборов данных малого или среднего размера можно использовать предпочтительную базу данных, например Azure SQL. Работая с большими наборами данных, вы можете воспользоваться преимуществами реализации хранилища данных в Azure Synapse Analytics, а не SQL Server. При создании таблиц в Synapse Analytics важно понимать некоторые важные особенности.
Ограничения целостности данных
Выделенные пулы SQL в Synapse Analytics не поддерживают ограничения foreign key (внешний ключ) и unique (уникальный), которые используются в SQL Server и других системах реляционных баз данных. Это означает, что задания загрузки данных должны самостоятельно обеспечивать уникальность и целостность ссылок для ключей, не полагаясь на определения таблиц в базе данных.
Совет
Дополнительные сведения об ограничениях в выделенных пулах SQL для Azure Synapse Analytics см. в статье Первичный ключ, внешний ключ и уникальный ключ при использовании выделенного пула SQL в Azure Synapse Analytics.
Индексы
Выделенные пулы SQL для Synapse Analytics поддерживают кластеризованные индексы, которые вам знакомы по SQL Server, но по умолчанию используют тип индекса clustered columnstore (кластеризованный columnstore). Этот тип индекса значительно повышает производительность при запросе больших объемов данных в хранилище данных с типичной схемой, и следует всегда использовать именно его, насколько это возможно. Но некоторые таблицы могут содержать такие типы данных, которые нельзя включить в кластеризованный индекс columnstore (например, VARBINARY(MAX)). В этом случае допускается кластеризованный индекс.
Совет
Дополнительные сведения об индексировании в выделенных пулах SQL для Azure Synapse Analytics см. в статье Индексы в таблицах выделенного пула SQL в Azure Synapse Analytics.
Распределение
Выделенные пулы SQL для Azure Synapse Analytics используют архитектуру MPP (массовой параллельной обработки), в отличие от архитектуры SMP (симметричной множественной обработки), как в большинстве систем баз данных OLTP. В системе MPP данные каждой таблицы распределяются между узлами пула для параллельной обработки. Synapse Analytics поддерживает следующие типы распределения.
- Хэш: детерминированное хэш-значение вычисляется по указанному столбцу и используется для назначения строк вычислительному узлу.
- Циклический перебор: строки распределяются поровну между всеми вычислительными узлами.
- Репликация: на каждом вычислительном узле хранится полная копия таблицы.
Метод распределения данных таблицы часто зависит от типа таблицы.
| Тип таблицы | Рекомендуемый вариант распределения |
|---|---|
| Измерение | Используйте репликацию для небольших таблиц, чтобы избежать перераспределения данных при соединениях с распределенными таблицами фактов. Если таблицы слишком велики для одного вычислительного узла, используйте распределение по хэшу. |
| Факт | Распределение по хэшу с кластеризованным индексом columnstore удобно для распределения таблиц фактов между вычислительными узлами. |
| Промежуточная | Используйте циклический перебора для распределения промежуточных таблиц, чтобы вычислительные узлы были загружены равномерно. |
Совет
Дополнительные сведения о стратегиях распределения таблиц в Azure Synapse Analytics см. в руководстве по проектированию распределенных таблиц в выделенном пуле SQL для Azure Synapse Analytics.
Создание таблиц измерений
При создании таблицы измерений убедитесь, что определение таблицы содержит суррогатные и альтернативные ключи, а также столбцы атрибутов измерения, которые необходимо использовать для группировки агрегатов. Часто проще всего использовать столбец IDENTITY для автоматического создания добавочного суррогатного ключа (в противном случае придется создавать уникальные ключи при каждой загрузке данных). Следующий пример демонстрирует применение инструкции CREATE TABLE к гипотетической таблице измерений 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
);
Примечание.
При желании вы можете создать определенную схему в качестве пространства имен для таблиц. В нашем примере используется схема по умолчанию (dbo).
Если вы планируете использовать схему снежинка, в которой таблицы измерений связаны друг с другом, следует включить ключ родительского измерения в определение дочерней таблицы измерений. Например, следующий код SQL перемещает сведения о географических адресах из таблицы DimCustomer в отдельную таблицу измерений 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
);
Таблица измерения времени
Большинство хранилищ данных содержат таблицу измерения для времени, что позволяет агрегировать данные по нескольким иерархическим уровням интервалов. Например, следующий пример создает таблицу DimDate с атрибутами, которые относятся к определенным датам.
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
);
Совет
При создании таблицы измерений для дат часто применяется числовой формат DDMMYYYY или YYYYMMDD в качестве целочисленного суррогатного ключа, а дата с типом данных DATE или DATETIME — в качестве альтернативного ключа.
Создание таблиц фактов
Таблицы фактов включают ключи для каждого измерения, с которым они связаны, а также атрибуты и числовые меры для конкретных событий или наблюдений, которые требуется анализировать.
Следующий пример кода создает гипотетическую таблицу фактов с именем FactSales, которая связана через ключевые столбцы с несколькими измерениями (дата, клиент, продукт и магазин).
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
);
Создание промежуточных таблиц
Промежуточные таблицы используются в качестве временного хранилища данных при загрузке в хранилище данных. Типичным шаблоном является такая структура таблицы, которая обеспечит максимальную эффективность при приеме данных из внешнего источника (обычно это файлы в озере данных) в реляционную базу данных, с последующей загрузкой данных из промежуточных таблиц в таблицы измерений и фактов с помощью инструкции SQL.
В следующем примере кода создается промежуточная таблица для данных о продуктах, которые в конечном итоге будут загружены в таблицу измерений:
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
);
Использование внешних таблиц
В некоторых случаях, если загружаемые данные хранятся в файлах с подходящей структурой, вы более эффективным будет создание внешних таблиц со ссылкой на расположение этих файлов. Это позволит считывать данные непосредственно из исходных файлов без загрузки в реляционное хранилище. Следующий пример демонстрирует создание внешней таблицу, которая ссылается на файлы в озере данных, связанном с рабочей областью 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
Примечание.
Дополнительные сведения об использовании внешних таблиц см. в статье Использование внешних таблиц в Synapse SQL в разделе документации по Azure Synapse Analytics.