Criar tabelas usando o pool Synapse SQL
Este artigo explica os principais conceitos para criar tabelas com pool SQL dedicado e pool SQL sem servidor no Azure Synapse Analytics.
- O pool SQL sem servidor é um serviço de consulta que opera sobre os dados em seu data lake. Ele não tem armazenamento local para ingestão de dados.
- O pool SQL dedicado representa uma coleção de recursos analíticos que são provisionados ao usar o Synapse SQL. O tamanho de um pool SQL dedicado é determinado por unidades de armazenamento de dados (DWU).
Os tópicos a seguir são relevantes para pool SQL dedicado versus pool SQL sem servidor:
| Tópico | Conjunto de SQL dedicado | Conjunto de SQL sem servidor |
|---|---|---|
| Categoria da tabela | Sim | No |
| Nomes de esquema | Sim | Sim |
| Nomes de tabelas | Sim | No |
| Persistência da tabela | Sim | No |
| Tabela regular | Sim | No |
| Tabela temporária | Sim | Sim |
| Tabela externa | Sim | Sim |
| Tipos de dados | Sim | Sim |
| Tabelas distribuídas | Sim | No |
| Mesas redondas | Sim | No |
| Tabelas distribuídas com hash | Sim | No |
| Tabelas replicadas | Sim | No |
| Métodos comuns de distribuição de tabelas | Sim | No |
| Partições | Sim | Sim |
| Índices Columnstore | Sim | No |
| Estatísticas | Sim | Sim |
| Chave primária e chave exclusiva | Sim | No |
| Comandos para criar tabelas | Sim | No |
| Alinhar os dados de origem com o data warehouse | Sim | No |
| Recursos de tabela não suportados | Sim | No |
| Consultas de tamanho de tabela | Sim | No |
Categoria da tabela
Um esquema em estrela organiza os dados em tabelas de fatos e dimensões. Algumas tabelas são usadas para integração ou preparo de dados antes de passar 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 data warehouse. 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 de data warehouse 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
Os esquemas são uma boa maneira de agrupar objetos que são usados de maneira semelhante. O código a seguir cria um esquema definido pelo usuário chamado wwi.
CREATE SCHEMA wwi;
Nomes de tabelas
Se você estiver migrando vários bancos de dados de uma solução local para um pool SQL dedicado, a prática recomendada é migrar todas as tabelas de fato, dimensão e integração para um esquema de pool SQL. Por exemplo, você pode armazenar todas as tabelas no data warehouse de exemplo WideWorldImportersDW dentro de um esquema chamado wwi.
Para mostrar a organização das tabelas no pool SQL dedicado, você pode usar fact, dime como int 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 data warehouse.
Tabela regular
Uma tabela regular armazena dados no Armazenamento do Azure como parte do data warehouse. A tabela e os dados persistem independentemente de uma sessão estar 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 armazenamento local e, em pools SQL dedicados, podem oferecer um desempenho mais rápido.
O pool SQL sem servidor oferece suporte a tabelas temporárias, mas seu uso é limitado, pois você pode selecionar a partir de uma tabela temporária, mas não pode associá-la a arquivos no armazenamento.
Para obter mais informações, consulte Tabelas temporárias.
Tabela externa
As tabelas externas apontam para dados localizados no blob de Armazenamento do Azure ou no Armazenamento do Azure Data Lake.
Você pode importar dados de tabelas externas para pools SQL dedicados usando a instrução CREATE TABLE AS SELECT (CTAS). Para obter um tutorial de carregamento, consulte Carregar o conjunto de dados do New York Taxicab.
Para pool 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 SQL dedicado oferece suporte aos tipos de dados mais usados. Para obter uma lista de tipos de dados suportados, consulte o tipo de dados na referência CREATE TABLE. Para obter mais informações sobre como usar tipos de dados, consulte Tipos de dados de tabela no Synapse SQL.
Tabelas distribuídas
Um recurso fundamental do pool SQL dedicado é a maneira como ele pode armazenar e operar em tabelas entre distribuições. O pool SQL dedicado oferece suporte a três métodos de distribuição de dados:
- Mesas round-robin (padrão)
- Tabelas distribuídas com hash
- 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. O carregamento de 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 Diretrizes de design para tabelas distribuídas.
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 porque 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.
Métodos comuns de distribuição de tabelas
A categoria de tabela geralmente determina a opção ideal para a distribuição da 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 preparo, use INSERT...SELECT para mover os dados para tabelas de produção. |
Partições
Em pools SQL dedicados, uma tabela particionada armazena e executa operações nas linhas da tabela de acordo com 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 em um pool SQL dedicado já estão distribuídos, muitas partições podem diminuir o desempenho da consulta. Para obter mais informações, consulte Diretrizes de particionamento.
Gorjeta
Quando a partição mudar para partições de tabela que não estão vazias, considere usar a TRUNCATE_TARGET opção em 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 SQL sem servidor, você pode limitar os arquivos ou pastas (partições) que são lidos pela sua consulta. O particionamento por caminho é suportado usando as filepath funções e fileinfo descritas em Consultando arquivos de armazenamento. O exemplo a seguir lê uma pasta com dados para o 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 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 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, consulte Novidades nos índices columnstore. Para melhorar o desempenho do índice columnstore, consulte Maximizar 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. Synapse SQL suporta a criação automática de estatísticas.
A atualização estatística não acontece automaticamente. Você pode atualizar as estatísticas depois que um número significativo de linhas é adicionado ou alterado. Por exemplo, atualize as estatísticas após uma carga. Para obter mais informações, consulte Estatísticas no Synapse SQL.
Chave primária e chave exclusiva
Para pool SQL dedicado, PRIMARY KEY só é suportado quando NONCLUSTERED e NOT ENFORCED são ambos usados.
UNIQUE restrição só é suportada quando NOT ENFORCED é usada. Para obter mais informações, consulte Chave primária, chave estrangeira e chave exclusiva usando pool SQL dedicado.
Comandos para criar tabelas
Para pool SQL dedicado, 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 | Description |
|---|---|
| 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 Armazenamento do Azure Data Lake. |
| 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 Armazenamento do Azure Data Lake. |
Alinhar os dados de origem com o data warehouse
As tabelas de pool SQL dedicadas são preenchidas carregando dados de outra fonte de dados. Para obter uma carga bem-sucedida, o número e os tipos de dados das colunas nos dados de origem devem estar alinhados com a definição de tabela no data warehouse.
Nota
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, você poderá portá-los para o data warehouse e armazená-los 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 implementar 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.
- Chave estrangeira, verificar restrições da tabela
- Colunas computadas
- Visualizações indexadas
- Sequência
- Colunas esparsas
- Chaves substitutas, implementar com Identidade
- SINÔNIMOS
- Acionadores
- Índices Únicos
- Tipos definidos pelo usuário
Consultas de tamanho de tabela
No pool SQL dedicado, 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');
Tenha em mente que usar comandos DBCC pode ser bastante limitante. As exibições de gerenciamento dinâmico (DMVs) mostram mais detalhes do 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 de tabela
Esta consulta devolve as linhas e o espaço por tabela. O resumo do espaço de tabela permite que você veja quais tabelas são suas maiores tabelas. Você também pode ver se eles são round-robin, replicados ou distribuídos 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 uma tabela para o data warehouse, a próxima etapa é carregar os dados na tabela.