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.