Criação de tabelas usando o pool de SQL dedicado no Azure Synapse Analytics
Este artigo fornece os principais conceitos introdutórios para a criação de tabelas no pool de SQL dedicado.
Determinar a categoria da tabela
Um esquema em estrela organiza dados em tabelas de fatos e dimensões. Algumas tabelas são usadas para dados de integração ou de preparo antes de movê-las para uma tabela de fatos ou dimensões. Ao criar uma tabela, decida se os dados da tabela pertencem a uma tabela de integração, de dimensão ou de fato. Essa decisão informa a distribuição e a estrutura da tabela apropriadas.
As tabelas de fatos contêm dados quantitativos normalmente gerados em um sistema transacional e, depois, carregados no pool de SQL dedicado. Por exemplo, uma empresa de varejo gera transações de vendas todos os dias e carrega os dados para uma tabela de fatos do pool de SQL dedicado para análise.
As Tabelas de dimensões contêm dados de atributo que podem ser alterados, mas essas alterações são raras. Por exemplo, um nome e endereço do cliente são armazenados em uma tabela de dimensões e atualizados somente quando o perfil do cliente é alterado. Uma tabela de fatos grande pode ser minimizada: não é preciso deixar o nome e o endereço do cliente em todas as linhas. Em vez disso, a tabela de fatos e a tabela de dimensões podem compartilhar uma ID do cliente. Uma consulta pode unir as duas tabelas para associar o perfil e as transações de um cliente.
As Tabelas de integrações oferecem um local para dados de preparo ou integração. Você pode criar uma tabela de integração como uma tabela regular, uma tabela externa ou uma tabela temporária. Por exemplo, é possível carregar dados em uma tabela de preparo, executar transformações nos dados de preparo e, em seguida, inserir os dados em uma tabela de produção.
Nomes de tabelas e esquemas
Os esquemas são uma boa maneira de agrupar tabelas usadas de maneira semelhante. Se você estiver migrando vários bancos de dados de uma solução local para um pool de SQL dedicado, é melhor migrar todas as tabelas de fatos, dimensões e integração para um esquema em um pool de SQL dedicado.
Por exemplo, você pode armazenar todas as tabelas do pool de SQL dedicado de exemplo WideWorldImportersDW em 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 nos pools de SQL dedicados, você pode usar fact, dim e int como prefixos dos nomes delas. A tabela a seguir mostra alguns dos nomes de tabelas e esquema para WideWorldImportersDW.
| WideWorldImportersDW table | Tipo de tabela | Pool de SQL dedicado |
|---|---|---|
| City | Dimensão | wwi.DimCity |
| Ordem | Fato | wwi.FactOrder |
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 para pool de SQL dedicado.
Tabela regular
Uma tabela regular armazena dados no Armazenamento do Azure como parte do pool de SQL dedicado. A tabela e os dados persistem independentemente se há uma sessão 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. As tabelas temporárias são boas opções para evitar que outros usuários vejam os resultados temporários, além de reduzirem a necessidade de limpeza.
As tabelas temporárias utilizam o armazenamento local para oferecer um desempenho rápido. Para obter mais informações, confira Tabelas temporárias.
Tabela externa
Uma tabela externa aponta para dados localizados no Azure Storage Blob ou Azure Data Lake Store. Quando usada com a instrução CREATE TABLE AS SELECT, selecionar a partir de uma tabela externa importa dados no pool de SQL dedicado.
Assim, as tabelas externas são úteis para carregar dados. Para ver um tutorial de carregamento, confira Usar o PolyBase para carregar dados do Armazenamento de Blobs do Azure.
Tipos de dados
O pool de SQL dedicado suporta os tipos de dados mais usados. Para obter uma lista dos tipos de dados com suporte, consulte tipos de dados na referência CREATE TABLE na instrução CREATE TABLE. Para obter diretrizes sobre o uso dos tipos de dados, consulte Tipos de dados.
Tabelas distribuídas
Um recurso fundamental do pool de SQL dedicado é a possibilidade de armazenar e operar em tabelas entre distribuições. O pool de SQL dedicado dá suporte a três métodos para distribuição de dados: distribuição equilibrada (padrão), em hash e replicado.
Tabelas distribuídas em hash
Uma tabela distribuída em hash distribui linhas com base no valor na coluna de distribuição. Uma tabela distribuída em hash visa o alto desempenho de consultas em tabelas grandes. Há vários fatores a serem considerados ao escolher uma coluna de distribuição.
Para obter mais informações, consulte Criação de diretrizes para tabelas distribuídas.
Tabelas replicadas
Uma tabela replicada possui uma cópia completa da tabela disponível em cada nó de Computação. Consultas de execução rápida em tabelas replicadas como junções em tabelas replicadas não exigem a movimentação de dados. No entanto, a replicação exige armazenamento extra e não é prática para tabelas grandes.
Para obter mais informações, confira Criação de diretrizes para tabelas replicadas.
Tabelas round robin
Uma tabela round robin distribui linhas de 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 que os outros métodos de distribuição.
Para obter mais informações, consulte Criação de diretrizes para tabelas distribuídas.
Métodos de distribuição comuns para tabelas
A categoria da tabela geralmente determina qual opção escolher para a distribuição da tabela.
| Categoria de tabela | Opção de distribuição recomendada |
|---|---|
| Fato | Use a distribuição de hash com índice columnstore clusterizado. O desempenho melhora quando duas tabelas de hash são unidas na mesma coluna de distribuição. |
| Dimensão | Use a replicada para tabelas menores. Se as tabelas forem grandes demais para serem armazenadas em cada nó de computação, use a distribuição de hash. |
| Staging | Use um round robin para a tabela de preparo. A carga com CTAS é rápida. Quando os dados estiverem na tabela de preparo, use INSERT...SELECT para mover os dados para uma tabela de produção. |
Observação
Para obter recomendações sobre a melhor estratégia de distribuição de tabela a ser usada com base em suas cargas de trabalho, confira o Assistente de Distribuição do SQL do Azure Synapse.
Partições de 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 de consultas através da eliminação da partição, o que limita a verificação de uma consulta para dados dentro de uma partição. Você também pode manter os dados por meio de alternância de partição. Como os dados em um pool de SQL já foram distribuídos, um número excessivo de partições pode diminuir o desempenho da consulta. Para saber mais informações, confira Diretrizes de particionamento. Quando fizer a alternância em 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 para o SalesFact, substituindo os dados existentes.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Índices columnstore
Por padrão, o pool de SQL dedicado armazena uma tabela como um índice columnstore clusterizado. Essa forma de armazenamento de dados atinge a alta compactação de dados e o desempenho de consultas em tabelas grandes.
Normalmente, o índice columnstore clusterizado é a melhor opção, mas existem alguns casos onde um índice clusterizado ou um heap são estruturas de armazenamento mais adequadas.
Dica
Uma tabela de heap 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, confira Quais são as novidades dos índices columnstores. Para melhorar o desempenho do índice columnstore, confira Como maximizar a qualidade do rowgroup para índices columnstore.
Estatísticas
O otimizador de consulta usa estatísticas de nível de coluna quando cria o plano para executar uma consulta.
Para melhorar o desempenho de consulta, é importante criar estatísticas em colunas individuais, especialmente nas colunas usadas em junções de consulta. A criação de estatísticas ocorre automaticamente.
A atualização de estatísticas não ocorre automaticamente. Atualize as estatísticas depois que um número significativo de linhas for adicionado ou alterado. Por exemplo, atualize as estatísticas depois de uma carga. Para obter mais informações, confira Diretrizes sobre estatísticas.
Chave primária e chave exclusiva
Há suporte para PRIMARY KEY somente quando NONCLUSTERED e NOT ENFORCED são usados. Há suporte para a restrição UNIQUE somente quando NOT ENFORCED é usado. Verifique Restrições de tabela do pool de SQL dedicado.
Comandos para a criação de tabelas
Você pode criar uma tabela como uma nova tabela vazia. Você também pode criar e popular uma tabela com os resultados de uma instrução de seleção. A seguir estão os comandos T-SQL para criar uma tabela.
| Instrução T-SQL | Descrição |
|---|---|
| CREATE TABLE | Cria uma tabela vazia com a definição de todas as opções e colunas da tabela. |
| CREATE EXTERNAL TABLE | Cria uma tabela externa. A definição da tabela é armazenada no pool de SQL dedicado. Os dados da tabela são armazenados no Armazenamento de Blobs do Azure ou do Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Popula uma tabela nova com os resultados de uma instrução selecionada. Os tipos de dados e colunas de tabela baseiam-se nos resultados da instrução selecionada. Para importar dados, essa instrução pode selecionar de uma tabela externa. |
| CREATE EXTERNAL TABLE AS SELECT | Cria uma tabela externa nova exportando os resultados de uma instrução selecionada para um local externo. O local é o armazenamento de Blobs do Azure ou o Azure Data Lake Store. |
Alinhar dados de origem com o pool de SQL dedicado
As tabelas do pool de SQL dedicado são preenchidas pelo carregamento de dados de outra fonte de dados. Para executar um carregamento bem-sucedido, os números e os tipos de dados das colunas na fonte de dados devem se alinhar com a definição da tabela no pool de SQL dedicado. Obter os dados para alinhar pode ser a parte mais difícil da criação de tabelas.
Se os dados forem provenientes de vários armazenamentos de dados, você carrega os dados para o pool de SQL dedicado e armazena-os em uma tabela de integração. Quando os dados estiverem na tabela de integração, você pode usar a potência do pool de SQL dedicado para executar operações de transformação. Quando os dados estiverem preparados, será possível inseri-los nas tabelas de produção.
Recursos da tabela sem suporte
O pool de SQL dedicado dá suporte a muitos, mas não todos, os recursos de tabela oferecidos por outros bancos de dados. A seguinte lista mostra algumas das funcionalidades de tabela não suportados no pool de SQL dedicado:
- Chave estrangeira, verificar Restrições de tabela
- Colunas computadas
- Exibições indexadas
- Sequência
- Colunas esparsas
- Chaves substitutas. Implementar com identidade.
- Sinônimos
- Gatilhos
- Índices Exclusivos
- Tipos definidos pelo usuário
Consultas do tamanho da tabela
Observação
Para obter contagens precisas das consultas nesta seção, certifique-se de que a manutenção do índice ocorra regularmente e após grandes alterações nos dados.
Uma maneira simples de identificar o espaço e as linhas consumidas por uma tabela em cada uma das 60 distribuições é usar DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
No entanto, usar comandos DBCC pode ser bastante limitado. Exibições de gerenciamento dinâmico (DMVs) mostram mais detalhes que os comandos DBCC. Comece criando esta exibição:
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 da tabela
Esta consulta retorna as linhas e o espaço por tabela. E permite que você veja quais são as tabelas maiores e se elas são distribuídas em hash, round robin ou replicadas. Nas tabelas distribuídas em hash, a consulta exibe 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 da tabela pelo 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 da tabela pelo 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
;
Próximas etapas
Depois de criar as tabelas para o pool de SQL dedicado, a próxima etapa é carregar dados na tabela. Para ver um tutorial de carregamento, consulte Carregar dados para o pool de SQL dedicado e examinar estratégias de carregamento de dados para o pool de SQL dedicado no Azure Synapse Analytics.