Crie tabelas usando o SQL do Synapse no Azure Synapse Analytics
Este documento apresenta os principais conceitos para a criação de tabelas com o pool de SQL dedicado e o pool de SQL sem servidor.
O pool de SQL sem servidoré um serviço de consulta aos dados no 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 se usa o SQL do Synapse. O tamanho do pool de SQL dedicado é determinado pelas DWU (unidades de data warehouse).
A tabela a seguir lista os as diferenças entre o pool de SQL dedicado e o pool de SQL sem servidor:
| Tópico | Pool de SQL dedicado | Pool de SQL sem servidor |
|---|---|---|
| Determinar a categoria da 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 distribuídas em hash | Sim | Não |
| Tabelas replicadas | Sim | Não |
| Tabelas round robin | 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 |
| Como 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 |
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 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 o 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 do data warehouse de exemplo WideWorldImportersDW em um esquema chamado 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 esquemas 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, já que você pode selecionar uma tabela temporária, mas não pode associá-la a arquivos armazenados.
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.
Importe dados de tabelas externas para pools de SQL dedicados usando a instrução CREATE TABLE AS SELECT. Para ver um tutorial de carregamento, confira Usar o PolyBase para carregar dados do Armazenamento de Blobs do Azure.
Para pools de SQL sem servidor, você pode usar CTAS para salvar o resultado da consulta em uma tabela externa no Armazenamento do Microsoft 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 mais informações sobre 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 de distribuição de dados:
- Round robin (padrão)
- Hash
- Replicada
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 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.
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. No entanto, 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 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. Quando os dados estiverem na tabela de preparo, use INSERT...SELECT para mover os dados para uma tabela 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
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 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/pastas (partições) que serão lidos pela sua consulta. O particionamento por caminho tem suporte usando as funções FilePath e FileInfo descritas em Consultando Arquivos de Armazenamento. O exemplo a seguir lê uma pasta com dados para o ano 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 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. O SQL do Synapse dá suporte à criação automática de estatísticas.
A atualização estatística 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. Informações adicionais são fornecidas no artigo Diretrizes de estatísticas.
Chave primária e chave exclusiva
Para o pool de SQL dedicado, PRIMARY KEY tem suporte somente quando NONCLUSTERED e NOT ENFORCED são usados. Há suporte para a restrição UNIQUE somente quando NOT ENFORCED é usado. Para obter mais informações, consulte o artigo restrições de tabela do 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 abaixo.
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 verá se elas são de round-robin, replicados ou distribuídas em 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
;
Próximas etapas
Depois de criar as tabelas para o data warehouse, a próxima etapa é carregar dados na tabela. Para obter um tutorial de carregamento, consulte Carregando Dados no Pool de SQL Dedicado.