Criar tabelas usando o pool SQL dedicado no Azure Synapse Analytics

  • Artigo

Este artigo fornece os principais conceitos introdutórios para criar tabelas no pool SQL dedicado.

Determinar categoria de tabela

Um esquema em estrela organiza os dados em tabelas de fatos e dimensões. Algumas tabelas são usadas para dados de integração ou preparo antes de serem movidos para uma tabela de fatos ou dimensões. Ao projetar uma tabela, decida se os dados da tabela pertencem a uma tabela de fato, dimensão ou integração. Esta decisão informa a estrutura e distribuição adequadas do quadro.

  • As tabelas de fatos contêm dados quantitativos que geralmente são gerados em um sistema transacional e, em seguida, carregados no pool SQL dedicado. Por exemplo, uma empresa de varejo gera transações de vendas todos os dias e, em seguida, carrega os dados em uma tabela de fatos dedicada do pool SQL para análise.

  • As tabelas de dimensão contêm dados de atributos que podem ser alterados, mas geralmente mudam com pouca frequência. Por exemplo, o nome e o endereço de um cliente são armazenados em uma tabela de dimensões e atualizados somente quando o perfil do cliente muda. Para minimizar o tamanho de uma grande tabela de fatos, o nome e o endereço do cliente não precisam estar em todas as linhas de uma tabela de fatos. Em vez disso, a tabela de fatos e a tabela de dimensões podem compartilhar uma ID de cliente. Uma consulta pode unir as duas tabelas para associar o perfil e as transações de um cliente.

  • As tabelas de integração fornecem um local para integrar ou preparar dados. Você pode criar uma tabela de integração como uma tabela regular, uma tabela externa ou uma tabela temporária. Por exemplo, você pode carregar dados em uma tabela de preparo, executar transformações nos dados em preparo e, em seguida, inserir os dados em uma tabela de produção.

Nomes de esquema e tabela

Os esquemas são uma boa maneira de agrupar tabelas, usadas de maneira semelhante, juntas. Se você estiver migrando vários bancos de dados de uma solução local para um pool SQL dedicado, é melhor migrar todas as tabelas de fato, dimensão e integração para um esquema em um pool SQL dedicado.

Por exemplo, você pode armazenar todas as tabelas no pool SQL dedicado de exemplo WideWorldImportersDW dentro de um esquema chamado wwi. O código a seguir cria um esquema definido pelo usuário chamado wwi.

CREATE SCHEMA wwi;

Para mostrar a organização das tabelas no pool SQL dedicado, você pode usar fact, dim e int como prefixos para os nomes das tabelas. A tabela a seguir mostra alguns dos nomes de esquema e tabela para WideWorldImportersDW.

Tabela WideWorldImportersDW Tipo de tabela Conjunto de SQL Dedicado
City Dimensão Primeira Guerra Mundial. DimCity
Ordenar Facto Primeira Guerra Mundial. Ordem dos Factos

Persistência da tabela

As tabelas armazenam dados permanentemente no Armazenamento do Azure, temporariamente no Armazenamento do Azure ou em um armazenamento de dados externo ao pool SQL dedicado.

Tabela regular

Uma tabela regular armazena dados no Armazenamento do Azure como parte do pool SQL dedicado. 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

Uma tabela temporária só existe durante a sessão. Você pode usar uma tabela temporária para impedir que outros usuários vejam resultados temporários e também para reduzir a necessidade de limpeza.

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

Tabela externa

Uma tabela externa aponta para dados localizados no blob de Armazenamento do Azure ou no Azure Data Lake Store. Quando usado com a instrução CREATE TABLE AS SELECT, a seleção de uma tabela externa importa dados para um pool SQL dedicado.

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

Tipos de dados

O pool SQL dedicado oferece suporte aos tipos de dados mais usados. Para obter uma lista dos tipos de dados suportados, consulte os tipos de dados na referência CREATE TABLE na instrução CREATE TABLE. Para obter orientação sobre como usar tipos de dados, consulte Tipos de dados.

Tabelas distribuídas

Um recurso fundamental do pool SQL dedicado é a maneira como ele pode armazenar e operar em tabelas entre 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 considerar ao escolher uma coluna de distribuição.

Para obter mais informações, consulte Diretrizes de design para tabelas distribuídas.

Tabelas replicadas

Uma tabela replicada tem uma cópia completa da tabela disponível em cada nó de computação. As consultas são executadas rapidamente em tabelas replicadas, uma vez que as junções em tabelas replicadas não exigem movimentação de dados. No entanto, a replicação requer armazenamento extra e não é prática para tabelas grandes.

Para obter mais informações, consulte Diretrizes de design para tabelas replicadas.

Mesas redondas

Uma tabela round-robin distribui as linhas da tabela uniformemente em todas as distribuições. As linhas são distribuídas aleatoriamente. Carregar dados em uma tabela round-robin é rápido. Lembre-se de que as consultas podem exigir mais movimentação de dados do que os outros métodos de distribuição.

Para obter mais informações, consulte Diretrizes de design para tabelas distribuídas.

Métodos comuns de distribuição de tabelas

A categoria da tabela geralmente determina qual opção escolher para distribuir a tabela.

Categoria da tabela Opção de distribuição recomendada
Facto Utilize a distribuição 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 Use replicado para tabelas menores. 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 round-robin para a mesa de preparação. A carga com CTAS é rápida. Quando os dados estiverem na tabela de preparação, use INSERT... SELECT para mover os dados para tabelas de produção.

Nota

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

Partições da tabela

Uma tabela particionada armazena e executa operações nas linhas da tabela de acordo com os intervalos de dados. Por exemplo, uma tabela pode ser particionada por dia, mês ou ano. Você pode melhorar o desempenho da consulta por meio da eliminação de partições, que limita uma verificação de consulta aos dados dentro de uma partição. Você também pode manter os dados através da comutação de partição. Como os dados no pool SQL já estão distribuídos, muitas partições podem diminuir o desempenho da consulta. Para obter mais informações, consulte Diretrizes de particionamento. Quando a partição mudar para partições de tabela que não estão vazias, considere usar a opção TRUNCATE_TARGET na instrução ALTER TABLE se os dados existentes forem truncados. O código abaixo alterna os dados diários transformados no SalesFact substituindo quaisquer dados existentes.

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

Índices Columnstore

Por padrão, o pool SQL dedicado armazena uma tabela como um índice columnstore clusterizado. Essa forma de armazenamento de dados alcança alta compactação de dados e desempenho de consulta em tabelas grandes.

O índice columnstore clusterizado geralmente é a melhor escolha, mas em alguns casos um índice clusterizado ou um heap é a estrutura de armazenamento apropriada.

Gorjeta

Uma tabela de pilha pode ser especialmente útil para carregar dados transitórios, como uma tabela de preparo que é transformada em uma tabela final.

Para obter uma lista de recursos columnstore, consulte Novidades para índices columnstore. Para melhorar o desempenho do índice columnstore, consulte Maximizando a qualidade do grupo de linhas para índices columnstore.

Estatísticas

O otimizador de consulta usa estatísticas em 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 junções de consulta. A criação de estatísticas acontece automaticamente.

A atualização de estatísticas não acontece automaticamente. Atualize as estatísticas depois que um número significativo de linhas for adicionado ou alterado. Por exemplo, atualize as estatísticas após uma carga. Para obter mais informações, consulte Diretrizes de estatísticas.

Chave primária e chave exclusiva

A CHAVE PRIMÁRIA só é suportada quando NONCLUSTERED e NOT ENFORCED são usados. A restrição UNIQUE só é suportada com NOT ENFORCED é usada. Verifique Restrições de tabela de pool SQL dedicada.

Comandos para criar tabelas

Você pode criar uma tabela como uma nova tabela vazia. Você também pode criar e preencher uma tabela com os resultados de uma instrução select. A seguir estão os comandos T-SQL para criar uma tabela.

Instrução T-SQL Descrição
CREATE TABLE Cria uma tabela vazia definindo todas as colunas e opções da tabela.
CRIAR TABELA EXTERNA Cria uma tabela externa. A definição da tabela é armazenada no pool SQL dedicado. Os dados da tabela são armazenados no armazenamento de Blob do Azure ou no Azure Data Lake Store.
CREATE TABLE AS SELECT Preenche uma nova tabela com os resultados de uma instrução select. As colunas da tabela e os tipos de dados baseiam-se nos resultados da instrução select. Para importar dados, esta instrução pode selecionar a partir de uma tabela externa.
CRIAR TABELA EXTERNA COMO SELECIONAR Cria uma nova tabela externa exportando os resultados de uma instrução select para um local externo. O local é o armazenamento de Blob do Azure ou o Azure Data Lake Store.

Alinhando dados de origem com pool SQL dedicado

As tabelas de pool SQL dedicadas são preenchidas carregando dados de outra fonte de dados. Para executar um carregamento bem-sucedido, o número e os tipos de dados das colunas nos dados de origem devem estar alinhados com a definição de tabela no pool SQL dedicado. Alinhar os dados pode ser a parte mais difícil de projetar suas tabelas.

Se os dados forem provenientes de vários armazenamentos de dados, carregue os dados no pool SQL dedicado e armazene-os em uma tabela de integração. Quando os dados estiverem na tabela de integração, você poderá usar o poder do pool SQL dedicado para executar operações de transformação. Depois que os dados estiverem preparados, você poderá inseri-los em tabelas de produção.

Recursos de tabela não suportados

O pool SQL dedicado suporta muitos, mas não todos, dos recursos de tabela oferecidos por outros bancos de dados. A lista a seguir mostra alguns dos recursos de tabela que não são suportados no pool SQL dedicado:

Consultas de tamanho de tabela

Nota

Para obter contagens precisas de consultas nesta seção, certifique-se de que a manutenção do índice ocorra regularmente e após grandes alterações de dados.

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

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

No entanto, usar comandos DBCC pode ser bastante limitante. As exibições de gerenciamento dinâmico (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 tabela

Esta consulta devolve as linhas e o espaço por tabela. Ele permite que você veja quais tabelas são suas maiores tabelas e se elas são round-robin, replicadas ou distribuídas 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 tabela 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 tabela 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
;

Conteúdos relacionados

Depois de criar as tabelas para seu pool SQL dedicado, a próxima etapa é carregar dados na tabela. Para obter um tutorial de carregamento, consulte Carregando dados para pool SQL dedicado e revise Estratégias de carregamento de dados para pool SQL dedicado no Azure Synapse Analytics.