Criar tabelas com o Synapse SQL no Azure Synapse Analytics

Este documento inclui conceitos-chave para estruturar tabelas com o conjunto de SQL dedicado e o conjunto de SQL sem servidor.

O conjunto de SQL sem servidor é um serviço de consulta sobre os dados no data lake. Não tem armazenamento local para ingestão de dados. O Conjunto de SQL dedicado representa uma coleção de recursos analíticos que estão a ser aprovisionados ao utilizar o Synapse SQL. O tamanho de um conjunto de SQL dedicado é determinado pelas Unidades de Armazenamento de Dados (DWU).

A tabela seguinte lista os tópicos relevantes para o conjunto de SQL dedicado vs. conjunto de SQL sem servidor:

Tópico	conjunto de SQL dedicado	conjunto de SQL sem servidor
Determinar a categoria da tabela	Yes	No
Nomes de esquema	Yes	Yes
Nomes de tabelas	Yes	No
Persistência da tabela	Yes	No
Tabela normal	Yes	No
Tabela temporária	Yes	Yes
Tabela externa	Yes	Yes
Tipos de dados	Yes	Yes
Tabelas distribuídas	Yes	No
Tabelas distribuídas com hash	Yes	No
Tabelas replicadas	Yes	No
Tabelas round robin	Yes	No
Métodos de distribuição comuns para tabelas	Yes	No
Partições	Yes	Yes
Índices Columnstore	Yes	No
Estatísticas	Yes	Yes
Chave primária e chave exclusiva	Yes	No
Comandos para criar tabelas	Yes	No
Alinhar os dados de origem com o armazém de dados	Yes	No
Funcionalidades de tabela não suportadas	Yes	No
Consultas de tamanho da tabela	Yes	No

Determinar a categoria da tabela

Um esquema de estrela organiza os dados em tabelas de factos e dimensões. Algumas tabelas são utilizadas para integração ou teste de dados antes de passar para uma tabela de factos ou dimensões. À medida que cria uma tabela, decida se os dados da tabela pertencem a uma tabela de factos, dimensão ou integração. Esta decisão informa a estrutura e a distribuição de tabelas adequadas.

• As tabelas de factos contêm dados quantitativos que são normalmente gerados num sistema transacional e, em seguida, carregados para o armazém de dados. 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 do armazém de dados 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 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 esquema

Os esquemas são uma boa forma de agrupar objetos que são utilizados de forma semelhante. O código seguinte cria um esquema definido pelo utilizador denominado wwi.

CREATE SCHEMA wwi;

Nomes de tabelas

Se estiver a migrar várias bases de dados de uma solução no local para um conjunto de SQL dedicado, a melhor prática é migrar todas as tabelas de factos, dimensões e integração para um esquema de conjunto de SQL. Por exemplo, pode armazenar todas as tabelas no armazém de dados de exemplo WideWorldImportersDW num esquema chamado 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 abaixo 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 armazém de dados.

Tabela normal

Uma tabela normal armazena dados no Armazenamento do Azure como parte do armazém de dados. A tabela e os dados persistem se uma sessão está ou não aberta. O exemplo abaixo 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. A utilização de tabelas temporárias também reduz a necessidade de limpeza. As tabelas temporárias utilizam o armazenamento local e, em conjuntos de SQL dedicados, podem oferecer um desempenho mais rápido.

O conjunto de SQL sem servidor suporta tabelas temporárias. No entanto, a sua utilização é limitada, uma vez que pode selecionar a partir de uma tabela temporária, mas não pode aderi-la a ficheiros no armazenamento.

Para obter mais informações, veja Tabelas temporárias.

Tabela externa

As tabelas externas apontam para dados localizados no blob do Armazenamento do Azure ou Azure Data Lake Storage.

Importe dados de tabelas externas para conjuntos de SQL dedicados com a instrução CREATE TABLE AS SELECT . Para obter um tutorial de carregamento, veja Utilizar o PolyBase para carregar dados do armazenamento de blobs do Azure.

Para o conjunto de SQL sem servidor, pode utilizar o CETAS para guardar o resultado da consulta numa tabela externa no Armazenamento 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 mais informações sobre como utilizar 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
• Replicada

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 porque 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. Contudo, 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 ideal para a distribuição de tabelas.

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.

Partições

Nos conjuntos 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. 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 num conjunto de SQL dedicado já estão distribuídos, demasiadas partições podem abrandar o desempenho das consultas. Para obter mais informações, veja Orientações de criação de partições.

Dica

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 os dados diários transformados para uma partição SalesFact e substitui quaisquer dados existentes.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);  

No conjunto de SQL sem servidor, pode limitar os ficheiros/pastas (partições) que serão lidos pela consulta. A criação de partições por caminho é suportada através das funções filepath e fileinfo descritas em Consultar ficheiros de armazenamento. O exemplo seguinte 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 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. O Synapse SQL suporta a criação automática de estatísticas.

A atualização estatística não ocorre 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. São fornecidas informações adicionais no artigo Orientação de estatísticas .

Chave primária e chave exclusiva

Para o conjunto de SQL dedicado, a CHAVE PRIMÁRIA só é suportada quando são utilizados NÃO AGRUPADOS e NÃO IMPOSTOS. A restrição EXCLUSIVA só é suportada quando não é imposta. Para obter mais informações, veja o artigo Dedicated SQL pool table constraints (Restrições de tabelas de conjuntos de SQL dedicados ).

Comandos para criar tabelas

Para o conjunto de SQL dedicado, pode criar uma tabela como uma nova tabela vazia. Também pode criar e preencher uma tabela com os resultados de uma instrução select. Seguem-se os comandos T-SQL para criar uma tabela.

Instrução T-SQL	Description
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 Azure Data Lake Storage.
CREATE TABLE AS SELECT	Preenche uma nova tabela com os resultados de uma instrução select. 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 select para uma localização externa. A localização é armazenamento de Blobs do Azure ou Azure Data Lake Storage.

Alinhar os dados de origem com o armazém de dados

As tabelas do conjunto de SQL dedicado são preenchidas ao carregar dados de outra origem de dados. Para obter 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 armazém de dados.

Nota

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, pode migrar os dados para o armazém de dados e armazená-lo 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 implementar 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 de tabela que não são suportadas no conjunto de SQL dedicado.

• Chave externa, selecione 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

No conjunto de SQL dedicado, 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');

Tenha em atenção que utilizar comandos DBCC pode ser bastante limitador. As vistas de gestão dinâmica (DMVs) mostram mais detalhes do que os comandos DBCC. Comece por criar a vista 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 devolve as linhas e o espaço por tabela. O resumo do espaço da tabela permite-lhe ver que tabelas são as suas maiores tabelas. Também verá se a distribuição é round robin, replicada ou com 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 da 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 armazém de dados, o próximo passo é carregar dados para a tabela. Para obter um tutorial de carregamento, veja Carregar dados para o conjunto de SQL dedicado.