Tabelas de design usando piscina DE SQL dedicada em Azure Synapse Analytics

Este artigo fornece conceitos introdutórios fundamentais para a conceção de mesas em piscinas SQL dedicadas.

Determinar categoria de tabela

Um esquema estelar organiza dados em tabelas de fatos e dimensões. Algumas tabelas são usadas para integração ou paragem de dados antes de passar para uma tabela de factos ou dimensões. Ao desenhar uma tabela, decida se os dados da tabela pertencem a uma tabela de factos, dimensão ou integração. Esta decisão informa a estrutura e distribuição de mesa adequadas.

  • As tabelas de factos contêm dados quantitativos que são geralmente gerados num sistema transacional e depois carregados no pool de SQL dedicado. Por exemplo, um negócio de retalho gera transações de vendas todos os dias, e depois carrega os dados numa tabela de fatos de piscina de SQL dedicada para análise.

  • As tabelas de dimensão contêm dados de atributos que podem mudar, mas geralmente mudam de forma pouco frequente. Por exemplo, o nome e endereço de um cliente são armazenados numa tabela de dimensão e atualizados apenas quando o perfil do cliente muda. Para minimizar o tamanho de uma grande tabela de factos, o nome e endereço do cliente não precisam de estar em todas as filas de uma tabela de factos. Em vez disso, a tabela de factos e a tabela de dimensões podem partilhar uma identificação do cliente. Uma consulta pode juntar-se às duas tabelas para associar o perfil e transações de um cliente.

  • As tabelas de integração fornecem um local para integrar ou encenar dados. Pode criar uma mesa de integração como mesa regular, uma mesa externa ou uma tabela temporária. Por exemplo, pode carregar dados para uma tabela de encenação, realizar transformações nos dados em encenação e, em seguida, inserir os dados numa tabela de produção.

Schema e nomes de mesa

Os esquemas são uma boa forma de agrupar mesas, usadas de forma semelhante, juntas. Se você está migrando várias bases de dados de uma solução on-prem para uma piscina de SQL dedicada, ele funciona melhor para migrar todas as tabelas de fato, dimensão e integração para um esquema em uma piscina de SQL dedicada.

Por exemplo, você pode armazenar todas as tabelas na amostra wideWorldImportersDW piscina de SQL dedicada dentro de um esquema chamado wwi. O código a seguir cria um esquema definido pelo utilizador chamado wwi.

CREATE SCHEMA wwi;

Para mostrar a organização das mesas na piscina dedicada SQL, você poderia usar fato, dim, e int como prefixos para os nomes de mesa. A tabela a seguir mostra alguns dos nomes de esquema e de mesa para WideWorldImportersDW.

Tabela WideWorldImportersDW Tipo de mesa Conjunto de SQL Dedicado
City Dimensão wwi. DimCity
Encomenda Fact wwi. Ordem de Factos

Persistência da tabela

As tabelas armazenam dados permanentemente no Azure Storage, temporariamente no Azure Storage, ou numa loja de dados externa a um pool DE SQL dedicado.

Tabela regular

Uma mesa regular armazena dados no Azure Storage como parte de uma piscina de SQL dedicada. A tabela e os dados persistem independentemente de uma sessão estar aberta. O exemplo a seguir cria uma tabela regular com duas colunas.

CREATE TABLE MyTable (col1 int, col2 int );  

Tabela temporária

Existe uma tabela temporária apenas durante a duração da sessão. Pode utilizar uma tabela temporária para evitar que outros utilizadores vejam resultados temporários e também para reduzir a necessidade de limpeza.

As mesas temporárias utilizam o armazenamento local para oferecer um desempenho rápido. Para mais informações, consulte tabelas temporárias.

Tabela externa

Uma tabela externa aponta para dados localizados na mancha de armazenamento Azure ou na Azure Data Lake Store. Quando utilizado com a declaração CREATE TABLE AS SELECT, selecionando de uma tabela externa os dados de importação para piscina de SQL dedicada.

Como tal, as tabelas externas são úteis para o carregamento de dados. Para um tutorial de carregamento, consulte Use PolyBase para carregar dados do armazenamento de bolhas Azure.

Tipos de dados

O pool de SQL dedicado suporta os tipos de dados mais utilizados. Para obter uma lista dos tipos de dados suportados, consulte os tipos de dados na referência CREATE TABLE na declaração CREATE TABLE. Para obter orientações sobre a utilização de tipos de dados, consulte os tipos de Dados.

Tabelas distribuídas

Uma característica fundamental da piscina de SQL dedicada é a forma como pode armazenar e operar em mesas através de distribuições. O conjunto de SQL dedicado suporta três métodos de distribuição de dados: round robin (predefinição), hash e replicado.

Tabelas distribuídas com hash

Uma tabela distribuída com hash distribui as linhas com base no valor da coluna de distribuição. Uma tabela distribuída com hash é criada para alcançar um elevado desempenho das consultas em tabelas grandes. Há vários fatores a ter em conta na escolha de uma coluna de distribuição.

Para obter mais informações, consulte a orientação do Design para tabelas distribuídas.

Tabelas replicadas

Uma tabela replicada tem uma cópia completa da tabela disponível em cada nó computacional. As consultas são rápidas em tabelas replicadas, uma vez que as junções em tabelas replicadas não requerem movimento de dados. A replicação requer armazenamento extra, no entanto, e não é prático para mesas grandes.

Para obter mais informações, consulte a orientação do Design para tabelas replicadas.

Mesas de rodapé

Uma mesa de rodapé distribui linhas de mesa uniformemente em todas as distribuições. As linhas são distribuídas aleatoriamente. Carregar dados numa mesa de rodapé é rápido. Tenha em mente que as consultas podem exigir mais movimento de dados do que os outros métodos de distribuição.

Para obter mais informações, consulte a orientação do Design para tabelas distribuídas.

Métodos comuns de distribuição de tabelas

A categoria de tabela determina frequentemente qual a opção a escolher para distribuir a tabela.

Categoria de tabela Opção de distribuição recomendada
Fact Utilize a distribuição com hash com o índice columnstore em cluster. O desempenho melhora quando duas tabelas hash são associadas na mesma coluna de distribuição.
Dimensão Utilize replicado para mesas mais pequenas. Se as tabelas forem demasiado grandes para serem armazenadas em cada Nó de computação, utilize a distribuição com hash.
Processo de teste Use o rodapé redondo para a mesa de preparação. A carga com CTAS é rápida. Uma vez que os dados estão na tabela de preparação, use INSERT... SELECIONE mover os dados para as tabelas de produção.

Nota

Para obter recomendações sobre a melhor estratégia de distribuição de mesa a utilizar com base nas suas cargas de trabalho, consulte o Azure Synapse SQL Distribution Advisor.

Divisórias de mesa

Uma mesa dividida armazena e realiza operações nas linhas de tabela de acordo com as gamas de dados. Por exemplo, uma mesa pode ser dividida por dia, mês ou ano. Pode melhorar o desempenho da consulta através da eliminação da partição, o que limita uma consulta aos dados dentro de uma partição. Também pode manter os dados através da comutação de divisórias. Uma vez que os dados no pool SQL já estão distribuídos, muitas divisórias podem atrasar o desempenho da consulta. Para mais informações, consulte a orientação de partição. Quando a partição mudar para divisórias que não estão vazias, considere utilizar a opção TRUNCATE_TARGET na sua declaração ALTER TABLE se os dados existentes forem truncados. O código abaixo comuta nos dados diários transformados no SalesFact, sobressaltando quaisquer dados existentes.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);  

Índices Columnstore

Por padrão, a piscina sql dedicada armazena uma tabela como um índice de loja de colunas agrupado. Esta forma de armazenamento de dados obtém alta compressão de dados e desempenho de consulta em grandes tabelas.

O índice de loja de colunas agrupado é geralmente a melhor escolha, mas em alguns casos um índice agrupado ou uma pilha é a estrutura de armazenamento apropriada.

Dica

Uma tabela de pilhas pode ser especialmente útil para o carregamento de dados transitórios, como uma mesa de preparação que é transformada em uma mesa final.

Para obter uma lista de funcionalidades de loja de colunas, consulte o que há de novo para índices de loja de colunas. Para melhorar o desempenho do índice de loja de colunas, consulte maximizar a qualidade do grupo de linha para índices de loja de colunas.

Estatísticas

O otimizador de consulta utiliza estatísticas de nível de coluna quando cria o plano para executar uma consulta.

Para melhorar o desempenho da consulta, é importante ter estatísticas sobre colunas individuais, especialmente colunas usadas em juntas de consulta. A criação de estatísticas acontece automaticamente.

Atualizar estatísticas não acontece automaticamente. Atualizar as estatísticas após um número significativo de linhas são adicionadas ou alteradas. Por exemplo, atualize as estatísticas após uma carga. Para mais informações, consulte a orientação estatística.

Chave primária e chave única

A CHAVE PRIMÁRIA só é suportada quando não é aplicada e não executada. É utilizada uma restrição única apenas com NÃO EXECUTADA. Verifique as restrições de mesa de bilhar SQL dedicadas.

Comandos para criar tabelas

Pode criar uma mesa como uma nova mesa vazia. Também pode criar e povoar uma tabela com os resultados de uma declaração selecionada. Seguem-se os comandos T-SQL para a criação de uma tabela.

Declaração T-SQL Descrição
CREATE TABLE Cria uma mesa vazia definindo todas as colunas e opções de mesa.
CRIAR TABELA EXTERNA Cria uma mesa externa. A definição da tabela é armazenada em piscina SQL dedicada. Os dados da tabela são armazenados no armazenamento Azure Blob ou na Azure Data Lake Store.
CREATE TABLE AS SELECT Povoa uma nova tabela com os resultados de uma declaração selecionada. As colunas de tabela e os tipos de dados baseiam-se nos resultados da declaração selecionada. Para importar dados, esta declaração pode selecionar a partir de uma tabela externa.
CRIAR TABELA EXTERNA COMO SELEÇÃO Cria uma nova tabela externa exportando os resultados de uma declaração selecionada para um local externo. A localização é o armazenamento Azure Blob ou a Azure Data Lake Store.

Alinhamento de dados de origem com piscina SQL dedicada

As mesas de bilhar SQL dedicadas são povoadas carregando dados de outra fonte de dados. Para efetuar uma carga bem sucedida, os tipos de número e dados das colunas nos dados de origem devem alinhar-se com a definição de tabela no pool DE SQL dedicado. Conseguir que os dados se alinhem pode ser a parte mais difícil de desenhar as suas tabelas.

Se os dados forem provenientes de várias lojas de dados, você carrega os dados no pool de SQL dedicado e armazene-os numa tabela de integração. Uma vez que os dados estão na tabela de integração, você pode usar o poder de pool SQL dedicado para realizar operações de transformação. Uma vez preparados os dados, pode inseri-lo em tabelas de produção.

Características da tabela não suportadas

A piscina dedicada SQL suporta muitas, mas não todas, funcionalidades de tabela oferecidas por outras bases de dados. A lista a seguir mostra algumas das funcionalidades da tabela que não são suportadas em piscinas SQL dedicadas:

Consultas de tamanho de mesa

Uma forma simples de identificar o espaço e as linhas consumidas por uma mesa em cada uma das 60 distribuições, é usar o DBCC PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

No entanto, a utilização de comandos DBCC pode ser bastante limitante. As vistas dinâmicas de gestão (DMVs) mostram mais detalhes do que os comandos DBCC. Comece por criar esta vista:

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
;

Resumo do espaço de mesa

Esta consulta devolve as linhas e o espaço por mesa. Permite-lhe ver as tabelas de maiores dimensões e se a distribuição é round robin, replicada ou por hash. Para tabelas distribuídas com hash, a consulta mostra a coluna de distribuição.

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
;

Espaço de mesa por tipo de distribuição

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
;

Espaço de mesa por tipo de índice

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
;

Resumo do espaço de distribuição

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
;

Passos seguintes

Depois de criar as tabelas para a sua piscina SQL dedicada, o próximo passo é carregar dados na tabela. Para um tutorial de carregamento, consulte os dados de Loading para o pool de SQL dedicado e reveja as estratégias de carregamento de dados para piscina de SQL dedicada em Azure Synapse Analytics.