Criar tabelas com o conjunto de SQL dedicado no Azure Synapse Analytics
Este artigo fornece conceitos introdutórios fundamentais para a conceção de tabelas no conjunto de SQL dedicado.
Determinar a categoria da tabela
Um esquema de star organiza os dados em tabelas de factos e dimensões. Algumas tabelas são utilizadas para dados de integração ou teste antes de serem movidas para uma tabela de factos ou dimensões. À medida que cria uma tabela, decida se os dados da tabela pertencem a um facto, dimensão ou tabela de integração. Esta decisão informa a estrutura e a distribuição da tabela adequadas.
As tabelas de factos contêm dados quantitativos que são normalmente gerados num sistema transacional e, em seguida, carregados para o conjunto de SQL dedicado. Por exemplo, uma empresa de retalho gera transações de vendas todos os dias e, em seguida, carrega os dados para uma tabela de factos de conjunto de SQL dedicada para análise.
As tabelas de dimensões contêm dados de atributos que podem ser alterados, mas que normalmente são alterados com pouca frequência. Por exemplo, o nome e o endereço de um cliente são armazenados numa tabela de dimensões e atualizados apenas quando o perfil do cliente é alterado. Para minimizar o tamanho de uma tabela de factos grande, o nome e o endereço do cliente não precisam de estar em todas as linhas de uma tabela de factos. Em vez disso, a tabela de factos e a tabela de dimensões podem partilhar um ID de cliente. Uma consulta pode associar 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 testar dados. Pode criar uma tabela de integração como uma tabela normal, uma tabela externa ou uma tabela temporária. Por exemplo, pode carregar dados para uma tabela de teste, realizar transformações nos dados em teste e, em seguida, inserir os dados numa tabela de produção.
Nomes de esquemas e tabelas
Os esquemas são uma boa forma de agrupar tabelas, utilizadas de forma semelhante, em conjunto. Se estiver a migrar várias bases de dados de uma solução no local para um conjunto de SQL dedicado, é melhor migrar todas as tabelas de factos, dimensões e integração para um esquema num conjunto de SQL dedicado.
Por exemplo, pode armazenar todas as tabelas no conjunto de SQL dedicado de exemplo WideWorldImportersDW num esquema chamado wwi. O código seguinte cria um esquema definido pelo utilizador chamadowwi.
CREATE SCHEMA wwi;
Para mostrar a organização das tabelas no conjunto de SQL dedicado, pode utilizar factos, dim e int como prefixos para os nomes das tabelas. A tabela seguinte mostra alguns dos nomes de esquema e tabela para WideWorldImportersDW.
| Tabela WideWorldImportersDW | Tipo de tabela | Conjunto de SQL Dedicado |
|---|---|---|
| City | Dimensão | wwi. DimCity |
| Encomenda | Fact | wwi. FactOrder |
Persistência da tabela
As tabelas armazenam dados permanentemente no Armazenamento do Azure, temporariamente no Armazenamento do Azure ou num arquivo de dados externo ao conjunto de SQL dedicado.
Tabela normal
Uma tabela normal armazena dados no Armazenamento do Azure como parte do conjunto de SQL dedicado. A tabela e os dados persistem independentemente de uma sessão estar aberta. O exemplo seguinte cria uma tabela normal com duas colunas.
CREATE TABLE MyTable (col1 int, col2 int );
Tabela temporária
Existe apenas uma tabela temporária durante a sessão. Pode utilizar uma tabela temporária para impedir que outros utilizadores vejam resultados temporários e também para reduzir a necessidade de limpeza.
As tabelas temporárias utilizam o armazenamento local para oferecer um desempenho rápido. Para obter mais informações, veja Tabelas temporárias.
Tabela externa
Uma tabela externa aponta para dados localizados no blob do Armazenamento do Azure ou no Azure Data Lake Store. Quando utilizado com a instrução CREATE TABLE AS SELECT, selecionar a partir de uma tabela externa importa dados para o conjunto de SQL dedicado.
Como tal, as tabelas externas são úteis para carregar dados. Para obter um tutorial de carregamento, veja Utilizar o PolyBase para carregar dados do armazenamento de blobs do Azure.
Tipos de dados
O conjunto de SQL dedicado suporta os tipos de dados mais utilizados. Para obter uma lista dos tipos de dados suportados, veja tipos de dados na referência CREATE TABLE na instrução CREATE TABLE. Para obter orientações sobre a utilização de tipos de dados, veja Tipos de dados.
Tabelas distribuídas
Uma funcionalidade fundamental do conjunto de SQL dedicado é a forma como 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. Existem vários fatores a considerar ao escolher uma coluna de distribuição.
Para obter mais informações, veja Orientações de estrutura para tabelas distribuídas.
Tabelas replicadas
Uma tabela replicada tem uma cópia completa da tabela disponível em todos os nós de Computação. As consultas são executadas rapidamente em tabelas replicadas, uma vez que as associações em tabelas replicadas não requerem movimento de dados. No entanto, a replicação requer armazenamento extra e não é prática para tabelas grandes.
Para obter mais informações, veja Orientações de estrutura 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 para uma tabela round robin é rápido. Tenha em atenção que as consultas podem exigir mais movimento de dados do que os outros métodos de distribuição.
Para obter mais informações, veja Orientações de estrutura para tabelas distribuídas.
Métodos de distribuição comuns para tabelas
A categoria de tabela determina frequentemente 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 tabelas 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 | Utilize round robin para a tabela de teste. A carga com CTAS é rápida. Assim que os dados estiverem na tabela de teste, utilize INSERT... SELECIONE para mover os dados para tabelas de produção. |
Nota
Para obter recomendações sobre a melhor estratégia de distribuição de tabelas a utilizar com base nas suas cargas de trabalho, consulte o Assistente de Distribuição do SQL 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. Pode melhorar o desempenho das consultas através da eliminação de partições, o que limita a análise de consultas aos dados numa partição. Também pode manter os dados através da mudança de partições. Uma vez que os dados no conjunto de SQL já estão distribuídos, demasiadas partições podem abrandar o desempenho das consultas. Para obter mais informações, veja Documentação de orientação sobre a criação de partições. Quando a partição mudar para partições de tabela que não estão vazias, considere utilizar a opção TRUNCATE_TARGET na instrução ALTER TABLE se os dados existentes forem truncados. O código abaixo muda nos dados diários transformados para o SalesFact substituindo quaisquer dados existentes.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Índices Columnstore
Por predefinição, o conjunto de SQL dedicado armazena uma tabela como um índice columnstore em cluster. Esta forma de armazenamento de dados obtém um elevado desempenho de consultas e compressão de dados em tabelas grandes.
Normalmente, o índice columnstore em cluster é a melhor opção, mas, em alguns casos, um índice em cluster ou uma área dinâmica é a estrutura de armazenamento adequada.
Dica
Uma tabela de área dinâmica pode ser especialmente útil para carregar dados transitórios, como uma tabela de teste que é transformada numa tabela final.
Para obter uma lista de funcionalidades columnstore, veja Novidades para índices columnstore. Para melhorar o desempenho do índice columnstore, veja Maximizar a qualidade do grupo de linhas para índices columnstore.
Estatísticas
O otimizador de consultas utiliza estatísticas ao nível da coluna quando cria o plano para executar uma consulta.
Para melhorar o desempenho das consultas, é importante ter estatísticas em colunas individuais, especialmente colunas utilizadas em associações de consultas. A criação de estatísticas ocorre automaticamente.
A atualização das estatísticas não acontece automaticamente. Atualize as estatísticas após a adição ou alteração de um número significativo de linhas. Por exemplo, atualize as estatísticas após uma carga. Para obter mais informações, veja Documentação de orientação sobre estatísticas.
Chave primária e chave exclusiva
A CHAVE PRIMÁRIA só é suportada quando são utilizadas as teclas NONCLUSTERED e NOT ENFORCED. A restrição UNIQUE só é suportada com NÃO IMPOSTA. Verifique as restrições de tabelas de conjuntos de SQL dedicadas.
Comandos para criar tabelas
Pode criar uma tabela como uma nova tabela vazia. Também pode criar e preencher uma tabela com os resultados de uma instrução selecionada. Seguem-se os comandos T-SQL para criar uma tabela.
| Instrução T-SQL | Descrição |
|---|---|
| CREATE TABLE | Cria uma tabela vazia ao definir todas as colunas e opções da tabela. |
| CRIAR TABELA EXTERNA | Cria uma tabela externa. A definição da tabela é armazenada no conjunto de SQL dedicado. Os dados da tabela são armazenados no armazenamento de Blobs do Azure ou no Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Preenche uma nova tabela com os resultados de uma instrução selecionada. As colunas de 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 ao exportar os resultados de uma instrução selecionada para uma localização externa. A localização é o Armazenamento de Blobs do Azure ou o Azure Data Lake Store. |
Alinhar dados de origem com o conjunto de SQL dedicado
As tabelas de conjuntos de SQL dedicadas são preenchidas ao carregar dados de outra origem de dados. Para efetuar uma carga bem-sucedida, o número e os tipos de dados das colunas nos dados de origem têm de estar alinhados com a definição da tabela no conjunto de SQL dedicado. Conseguir alinhar os dados pode ser a parte mais difícil de estruturar as suas tabelas.
Se os dados forem provenientes de vários arquivos de dados, carregue os dados para o conjunto de SQL dedicado e armazene-os numa tabela de integração. Assim que os dados estiverem na tabela de integração, pode utilizar o poder do conjunto de SQL dedicado para realizar operações de transformação. Assim que os dados estiverem preparados, pode inseri-los em tabelas de produção.
Funcionalidades de tabela não suportadas
O conjunto de SQL dedicado suporta muitas, mas não todas, das funcionalidades de tabela oferecidas por outras bases de dados. A lista seguinte mostra algumas das funcionalidades da tabela que não são suportadas no conjunto de SQL dedicado:
- Chave externa, Verificar Restrições de Tabela
- Colunas Calculadas
- Vistas Indexadas
- Sequence
- Colunas Dispersas
- Chaves de Substituição. Implementar com Identidade.
- Sinónimos
- Acionadores
- Índices Exclusivos
- Tipos Definidos pelo Utilizador
Consultas de tamanho da tabela
Nota
Para contagens precisas de consultas nesta secção, certifique-se de que a manutenção do índice ocorre regularmente e após grandes alterações de dados.
Uma forma simples de identificar o espaço e as linhas consumidas por uma tabela em cada uma das 60 distribuições é utilizar o DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
No entanto, a utilização de comandos DBCC pode ser bastante limitada. As vistas de gestão dinâmica (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 da tabela
Esta consulta devolve as linhas e o espaço por tabela. 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 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
;
Passos seguintes
Depois de criar as tabelas para o conjunto de SQL dedicado, o próximo passo é carregar dados para a tabela. Para obter um tutorial de carregamento, veja Carregar dados para o conjunto de SQL dedicado e veja Estratégias de carregamento de dados para o conjunto de SQL dedicado no Azure Synapse Analytics.