Criar tabelas de armazém de dados

• 10 minutos

Agora que você entende os princípios básicos de arquitetura para um esquema de data warehouse relacional, vamos explorar como criar um data warehouse.

Criando um pool SQL dedicado

Para criar um data warehouse relacional no Azure Synapse Analytics, você deve criar um SQL Pool dedicado. A maneira mais simples de fazer isso em um espaço de trabalho existente do Azure Synapse Analytics é usar a página Gerenciar no Azure Synapse Studio, conforme mostrado aqui:

Ao provisionar um pool SQL dedicado, você pode especificar as seguintes definições de configuração:

• Um nome exclusivo para o pool SQL dedicado.
• Um nível de desempenho para o pool SQL, que pode variar de DW100c a DW30000c e que determina o custo por hora para o pool quando ele está em execução.
• Se deve começar com um pool vazio ou restaurar um banco de dados existente a partir de um backup.
• O agrupamento do pool SQL, que determina a ordem de classificação e as regras de comparação de cadeia de caracteres para o banco de dados. (Não é possível alterar o agrupamento após a criação).

Depois de criar um pool SQL dedicado, você pode controlar seu estado de execução na página Gerenciar do Synapse Studio, pausando-o quando não for necessário para evitar custos desnecessários.

Quando o pool estiver em execução, você poderá explorá-lo na página Dados e criar scripts SQL para serem executados nele.

Considerações para a criação de tabelas

Para criar tabelas no pool SQL dedicado, use a CREATE TABLE instrução Transact-SQL (ou, às vezes CREATE EXTERNAL TABLE, a instrução Transact-SQL). As opções específicas usadas na instrução dependem do tipo de tabela que você está criando, que podem incluir:

• Tabelas de factos
• Tabelas de dimensões
• Mesas de preparo

Ao projetar um modelo de esquema em estrela para conjuntos de dados de pequeno ou médio porte, você pode usar seu banco de dados preferido, como o Azure SQL. Para conjuntos de dados maiores, você pode se beneficiar da implementação de seu data warehouse no Azure Synapse Analytics em vez do SQL Server. É importante entender algumas diferenças importantes ao criar tabelas no Synapse Analytics.

Restrições de integridade de dados

Os pools SQL dedicados no Synapse Analytics não oferecem suporte a chaves estrangeiras e restrições exclusivas , como encontrado em outros sistemas de banco de dados relacionais, como o SQL Server. Isso significa que os trabalhos usados para carregar dados devem manter a exclusividade e a integridade referencial das chaves, sem depender das definições de tabela no banco de dados para fazê-lo.

Índices

Enquanto os pools SQL dedicados do Synapse Analytics oferecem suporte a índices clusterizados conforme encontrados no SQL Server, o tipo de índice padrão é columnstore clusterizado. Esse tipo de índice oferece uma vantagem significativa de desempenho ao consultar grandes quantidades de dados em um esquema típico de data warehouse e deve ser usado sempre que possível. No entanto, algumas tabelas podem incluir tipos de dados que não podem ser incluídos em um índice columnstore clusterizado (por exemplo, VARBINARY(MAX)), caso em que um índice clusterizado pode ser usado.

Distribuição

Os pools SQL dedicados do Azure Synapse Analytics usam uma arquitetura de processamento paralelo maciço (MPP), em oposição à arquitetura de multiprocessamento simétrico (SMP) usada na maioria dos sistemas de banco de dados OLTP. Em um sistema MPP, os dados em uma tabela são distribuídos para processamento em um pool de nós. O Synapse Analytics suporta os seguintes tipos de distribuição:

• Hash: Um valor de hash determinístico é calculado para a coluna especificada e usado para atribuir a linha a um nó de computação.
• Round-robin: As linhas são distribuídas uniformemente em todos os nós de computação.
• Replicado: uma cópia da tabela é armazenada em cada nó de computação.

O tipo de tabela geralmente determina qual opção escolher para distribuir a tabela.

Tipo de tabela	Opção de distribuição recomendada
Dimensão	Use a distribuição replicada para tabelas menores para evitar o embaralhamento de dados ao unir tabelas de fatos distribuídas. Se as tabelas forem muito grandes para serem armazenadas em cada nó de computação, use a distribuição de hash.
Facto	Use a distribuição de hash com o índice columnstore clusterizado para distribuir tabelas de fatos entre nós de computação.
Processo de teste	Use a distribuição round-robin para tabelas de preparo para distribuir dados uniformemente entre nós de computação.

Criação de tabelas de dimensão

Ao criar uma tabela de dimensão, certifique-se de que a definição da tabela inclua chaves substitutas e alternativas, bem como colunas para os atributos da dimensão que você deseja usar para agrupar agregações. Muitas vezes, é mais fácil usar uma IDENTITY coluna para gerar automaticamente uma chave substituta incremental (caso contrário, você precisa gerar chaves exclusivas toda vez que carregar dados). O exemplo a seguir mostra uma CREATE TABLE instrução para uma tabela de dimensão DimCustomer hipotética.

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
);

Se você pretende usar um esquema de floco de neve no qual as tabelas de dimensão estão relacionadas entre si, você deve incluir a chave para a dimensão pai na definição da tabela de dimensão filho . Por exemplo, o seguinte código SQL pode ser usado para mover os detalhes do endereço geográfico da tabela DimCustomer para uma tabela de dimensão DimGeography separada:

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
);

Tabelas de dimensões temporais

A maioria dos armazéns de dados inclui uma tabela de dimensões de tempo que permite agregar dados por vários níveis hierárquicos de intervalo de tempo. Por exemplo, o exemplo a seguir cria uma tabela DimDate com atributos relacionados a datas específicas.

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
);

Criação de tabelas de factos

As tabelas de fatos incluem as chaves para cada dimensão à qual estão relacionadas e os atributos e medidas numéricas para eventos ou observações específicos que você deseja analisar.

O exemplo de código a seguir cria uma tabela de fatos hipotética chamada FactSales que está relacionada a várias dimensões por meio de colunas chave (data, cliente, produto e loja)

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
);

Criando tabelas de preparo

As tabelas de preparo são usadas como armazenamento temporário para dados à medida que são carregados no data warehouse. Um padrão típico é estruturar a tabela para torná-la o mais eficiente possível para ingerir os dados de sua fonte externa (geralmente arquivos em um data lake) no banco de dados relacional e, em seguida, usar instruções SQL para carregar os dados das tabelas de preparo para as tabelas de dimensão e fatos.

O exemplo de código a seguir cria uma tabela de preparo para dados do produto que serão carregados em uma tabela de dimensão:

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
);

Usando tabelas externas

Em alguns casos, se os dados a serem carregados estiverem em arquivos com uma estrutura apropriada, pode ser mais eficaz criar tabelas externas que façam referência ao local do arquivo. Dessa forma, os dados podem ser lidos diretamente dos arquivos de origem em vez de serem carregados no repositório relacional. O exemplo a seguir mostra como criar uma tabela externa que faz referência a arquivos no data lake associado ao espaço de trabalho 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