Criar tabelas de data warehouse

10 minutos

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

Criando um pool de SQL dedicado

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

A screenshot of the SQL pools tab in the Manage page of Synapse Studio.

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

Um nome exclusivo para o pool de SQL dedicado.
Um nível de desempenho para o pool de SQL, que pode variar de DW100c a DW30000c e que determina o custo por hora do pool quando ele está em execução.
Se deseja começar com um pool vazio ou restaurar um banco de dados existente de um backup.
A ordenação do pool de 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 a ordenação após a criação).

Após criar um pool de SQL dedicado, você pode controlar seu estado de execução na página Gerenciar do Synapse Studio, pausando-o quando ele 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 de SQL para execução nele.

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

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

Tabela de fatos
Tabelas de dimensões
Tabelas de preparo

Observação

O data warehouse é composto por tabelas de fatos e de dimensões, conforme discutido anteriormente. As tabelas de preparo geralmente são usadas como parte do processo de carregamento do data warehouse para ingerir dados dos sistemas de origem.

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

Restrições de integridade dos dados

Os pools de SQL dedicados no Synapse Analytics não dão suporte a restrições de chaves estrangeiras e exclusivas, conforme ocorre 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 fazer isso.

Dica

Para obter mais informações sobre as restrições nos pools de SQL dedicados do Azure Synapse Analytics, confira Chave primária, chave estrangeira e chave exclusiva usando o pool de SQL dedicado no Azure Synapse Analytics.

Índices

Embora os pools de SQL dedicados do Synapse Analytics tenham suporte para índices clusterizados, conforme ocorre no SQL Server, o tipo de índice padrão é columnstore clusterizado. Esse tipo de índice oferece vantagem significativa de desempenho ao consultar grandes quantidades de dados em um esquema de data warehouse típico 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)); nesse caso, um índice clusterizado pode ser usado.

Dica

Para obter mais informações sobre a indexação em pools de SQL dedicados do Azure Synapse Analytics, consulte Índices em tabelas de pools de SQL dedicados no Azure Synapse Analytics.

Distribuição

Os pools de SQL dedicados do Azure Synapse Analytics usam a arquitetura de MPP (processamento paralelo massivo) em vez da arquitetura de SMP (multiprocessamento simétrico) usada na maioria dos sistemas de banco de dados OLTP. Em um sistema de MPP, os dados em uma tabela são distribuídos para processamento em um pool de nós. O Synapse Analytics dá suporte aos 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 de maneira uniforme entre 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 a distribuição da 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 ingressar em tabelas de fatos distribuídas. Se as tabelas forem grandes demais para serem armazenadas em cada nó de computação, use a distribuição de hash.
Fato	Use a distribuição de hash com o índice columnstore clusterizado para distribuir tabelas de fatos entre nós de computação.
Preparo	Use a distribuição round robin para tabelas de preparo para distribuir os dados de modo uniforme entre os nós de computação.

Dica

Para saber mais sobre as estratégias de distribuição de tabelas no Azure Synapse Analytics, consulte Diretrizes para criar tabelas distribuídas usando o pool de SQL dedicado no Azure Synapse Analytics.

Criando tabelas de dimensões

Ao criar uma tabela de dimensões, verifique se a definição da tabela inclui chaves alternadas e alternativas, bem como colunas para os atributos da dimensão que você deseja usar para agrupar agregações. Geralmente, é mais fácil usar uma coluna IDENTITY para gerar automaticamente uma chave alternativa incremental (caso contrário, você precisará gerar chaves exclusivas sempre que carregar dados). O exemplo a seguir mostra uma instrução CREATE TABLE para uma tabela de dimensões hipotética 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
);

Observação

Se desejar, crie um esquema específico como um namespace para suas tabelas. Neste exemplo, o esquema dbo padrão é usado.

Se você pretende usar um esquema floco de neve em que as tabelas de dimensões estão relacionadas umas às outras, inclua a chave para a dimensão pai na definição da tabela de dimensões filho. Por exemplo, o seguinte código de SQL pode ser usado para mover os detalhes do endereço geográfico da tabela DimCustomer para uma tabela de dimensões 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
);

Tabela de dimensões temporais

A maioria dos data warehouses inclui uma tabela de dimensões temporais que permite agregar dados segundo vários níveis hierárquicos de intervalo de tempo. 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
);

Dica

Um padrão comum ao criar uma tabela de dimensões para datas é usar a data numérica no formato DDMMAAAA ou AAAAMMDDD como uma chave alternativa inteira e a data como um tipo de dados DATE ou DATETIME como a chave alternada.

Criando tabelas de fatos

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íficas que você deseja analisar.

O exemplo de código a seguir cria uma tabela de fatos hipotética chamada FactSales relacionada a várias dimensões por meio de colunas de 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 os dados enquanto eles são carregadas 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 nas tabelas de dimensões e de fatos.

O seguinte exemplo de código cria uma tabela de preparo para dados do produto que, em última análise, serão carregados em uma tabela de dimensões:

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, poderá ser mais eficaz criar tabelas externas que referenciam o 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 seguinte exemplo mostra como criar uma tabela externa que referencia arquivos no data lake associado ao workspace do 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

Observação

Para saber mais sobre como usar tabelas externas, consulte Usar tabelas externas com o SQL do Synapse na documentação do Azure Synapse Analytics.

Criar tabelas de data warehouse

Criando um pool de SQL dedicado

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

Restrições de integridade dos dados

Índices

Distribuição

Criando tabelas de dimensões

Tabela de dimensões temporais

Criando tabelas de fatos

Criando tabelas de preparo

Usando tabelas externas

Comentários