Criar tabelas usando o pool de SQL do Synapse
Este artigo explica os principais conceitos para criar tabelas com o pool de SQL dedicado e o pool SQL sem servidor no Azure Synapse Analytics.
- O Pool de SQL sem servidor é um serviço de consulta que opera sobre os dados no seu data lake. Ele não tem armazenamento local para ingestão de dados.
- O Pool de SQL dedicado representa uma coleção de recursos de análise que são provisionados quando o SQL do Synapse é usado. O tamanho do pool de SQL dedicado é determinado pelas DWU (unidades de data warehouse).
Os tópicos a seguir são relevantes para o pool de SQL dedicado versus o pool de SQL sem servidor:
| Tópico | Pool de SQL dedicado | Pool de SQL sem servidor |
|---|---|---|
| Categoria de tabela | Sim | Não |
| Nomes de esquema | Sim | Sim |
| Nomes de tabela | Sim | Não |
| Persistência da tabela | Sim | Não |
| Tabela regular | Sim | Não |
| Tabela temporária | Sim | Sim |
| Tabela externa | Sim | Sim |
| Tipos de dados | Sim | Sim |
| Tabelas distribuídas | Sim | Não |
| Tabelas round robin | Sim | Não |
| Tabelas distribuídas em hash | Sim | Não |
| Tabelas replicadas | Sim | Não |
| Métodos de distribuição comuns para tabelas | Sim | Não |
| Partições | Sim | Sim |
| Índices columnstore | Sim | Não |
| Estatísticas | Sim | Sim |
| Chave primária e chave exclusiva | Sim | Não |
| Comandos para a criação de tabelas | Sim | Não |
| Alinhar dados de origem com o data warehouse | Sim | Não |
| Recursos de tabela sem suporte | Sim | Não |
| Consultas do tamanho da tabela | Sim | Não |
Categoria de 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 irem 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 data warehouse. Por exemplo, uma empresa de varejo gera transações de vendas todos os dias e, em seguida, carrega os dados para uma tabela de fatos do data warehouse 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 esquema
Os esquemas são uma boa maneira de agrupar objetos usados de forma semelhante. O código a seguir cria um esquema definido pelo usuário chamado wwi.
CREATE SCHEMA wwi;
Nomes da tabela
Se você estiver migrando vários bancos de dados de uma solução local para um pool de SQL dedicado, é recomendado migrar todas as tabelas de fatos, dimensões e integração para um esquema de pool de SQL. Por exemplo, você pode armazenar todas as tabelas no data warehouse de exemplo WideWorldImportersDW em um esquema chamado wwi.
Para mostrar a organização das tabelas no pool de SQL dedicado, você pode usar fact, dim e int como prefixos para os nomes da tabela. A tabela a seguir mostra alguns dos nomes de esquema e tabela 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 de forma permanente ou temporária no Armazenamento do Microsoft Azure, ou em um armazenamento de dados fora do data warehouse.
Tabela regular
Uma tabela regular armazena dados no Armazenamento do Microsoft Azure como parte do data warehouse. A tabela e os dados persistem, estando uma sessão aberta ou não. 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. O uso de tabelas temporárias também reduz a necessidade de limpeza. As tabelas temporárias utilizam o armazenamento local e podem oferecer um desempenho mais rápido em pools de SQL dedicados.
O pool de SQL sem servidor dá suporte a tabelas temporárias, mas seu uso é limitado, pois você pode selecionar em uma tabela temporária, mas não pode associá-la a arquivos no armazenamento.
Para obter mais informações, confira Tabelas temporárias.
Tabela externa
Uma tabela externa aponta para dados localizados no Azure Storage Blobs ou Azure Data Lake Storage.
Você pode importar dados de tabelas externas para pools de SQL dedicados usando a instrução CREATE TABLE AS SELECT (CTAS). Para um tutorial de carregamento, confira Carregar o conjunto de dados do Táxi de Nova York.
Para o pool de SQL sem servidor, você pode usar CREATE EXTERNAL TABLE AS SELECT (CETAS) para salvar o resultado da consulta em uma tabela externa no Armazenamento do Azure.
Tipos de dados
O pool de SQL dedicado suporta os tipos de dados mais usados. Para obter uma lista de tipos de dados suportados, confira o tipo de dados na referência CREATE TABLE. Para obter mais informações sobre como usar os tipos de dados, confira Tipos de dados de tabela no SQL do Synapse.
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 de distribuição de dados:
- Tabelas round-robin (padrão)
- Tabelas distribuídas em hash
- 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, mas as consultas podem exigir mais movimentação de dados do que os outros métodos de distribuição.
Para obter mais informações, consulte Criação de diretrizes para tabelas distribuídas.
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 tem uma cópia completa da tabela disponível em cada nó de computação. Consultas são executadas de forma rápida em tabelas replicadas, pois junções não requerem 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.
Métodos de distribuição comuns para tabelas
A categoria da tabela geralmente determina a opção ideal para a distribuição.
| 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. Depois que os dados estiverem na tabela de preparo, use INSERT...SELECT para mover os dados para as tabelas de produção. |
Partições
Em pools de SQL dedicados, 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 dedicado 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.
Dica
Ao alternar partições em partições de tabela que não estejam vazias, considere usar a opção TRUNCATE_TARGET na sua instrução ALTER TABLE se os dados existentes devem ser truncados.
O código a seguir alterna os dados diários transformados em uma partição SalesFact e substitui todos os dados existentes.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
No pool de SQL sem servidor, você pode limitar os arquivos ou pastas (partições) que são lidos pela sua consulta. O particionamento por caminho tem suporte usando as funções filepath e fileinfo descritas em Consulta de arquivos de armazenamento. O exemplo a seguir lê uma pasta com dados do ano de 2017:
SELECT
nyc.filepath(1) AS [year],
payment_type,
SUM(fare_amount) AS fare_total
FROM
OPENROWSET(
BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
FORMAT='PARQUET'
) AS nyc
WHERE
nyc.filepath(1) = 2017
GROUP BY
nyc.filepath(1),
payment_type
ORDER BY
nyc.filepath(1),
payment_type
Í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 Novidades nos índices columnstore. Para melhorar o desempenho do índice columnstore, confira Maximizar a qualidade do rowgroup para os í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. O SQL do Synapse dá suporte à criação automática de estatísticas.
A atualização estatística não ocorre automaticamente. Você pode atualizar estatísticas depois que um número significativo de linhas é adicionado ou alterado. Por exemplo, atualize as estatísticas depois de uma carga. Para obter mais informações, confira Estatísticas no SQL do Synapse.
Chave primária e chave exclusiva
Para o pool de SQL dedicado, o PRIMARY KEY só tem suporte quando NONCLUSTERED e o NOT ENFORCED são usados. A restrição UNIQUE só tem suporte quando NOT ENFORCED é usado. Para obter mais informações, confira a Chave primária, a chave estrangeira e a chave exclusiva usando o pool de SQL dedicado.
Comandos para a criação de tabelas
Para o pool de SQL dedicado, 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 no Azure Data Lake Storage. |
| 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 data warehouse
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 data warehouse.
Observação
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ê pode trazer os dados para o data warehouse e armazená-los 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 dedicadopara 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 lista a seguir 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 alternativas, implementar com Identidade
- Sinônimos
- Gatilhos
- Índices Exclusivos
- Tipos definidos pelo usuário
Consultas do tamanho da tabela
Em um pool de SQL dedicado, uma maneira simples de identificar o espaço e as linhas consumidas por uma tabela em cada uma das 60 distribuições é usando 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 o modo de exibição a seguir.
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]
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. Resumo de espaço de tabela permite que você veja quais são as maiores tabelas. Você também pode ver se eles são round-robin, replicados ou distribuídos por hash. 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
;
Conteúdo relacionado
Depois de criar uma tabela para o data warehouse, a próxima etapa é carregar os dados na tabela.