Progettare tabelle con pool SQL dedicato in Azure Synapse Analytics
Questo articolo fornisce concetti introduttivi chiave per la progettazione di tabelle nel pool SQL dedicato.
Determinare la categoria della tabella
Uno schema star organizza i dati in tabelle fact e tabelle delle dimensioni. Alcune tabelle vengono usate per i dati di integrazione o di staging prima di spostarli in una tabella fact o delle dimensioni. Quando si progetta una tabella, occorre decidere se i dati appartengono a una tabella fact, delle dimensioni o di integrazione. Questa decisione determina la struttura della tabella e la distribuzione appropriate.
Le tabelle dei fatti contengono dati quantitativi che vengono comunemente generati in un sistema transazionale e quindi caricati nel pool SQL dedicato. Ad esempio, un'azienda al dettaglio genera transazioni di vendita ogni giorno e quindi carica i dati in una tabella dei fatti del pool SQL dedicata per l'analisi.
Le tabelle delle dimensioni contengono dati di attributo che potrebbero cambiare, ma in genere cambiano raramente. Il nome e l'indirizzo di un cliente, ad esempio, vengono archiviati in una tabella delle dimensioni e aggiornati solo quando viene modificato il profilo del cliente. Per ridurre al minimo le dimensioni di una tabella dei fatti di grandi dimensioni, si può evitare di inserire il nome e l'indirizzo del cliente in ogni riga della tabella. La tabella fact e la tabella delle dimensioni possono invece condividere un ID cliente. Una query può creare un join tra le due tabelle per associare il profilo e le transazioni di un cliente.
Le tabelle di integrazione sono un luogo in cui integrare o gestire temporaneamente i dati. È possibile creare una tabella di integrazione come una tabella normale, una tabella esterna o una tabella temporanea. È ad esempio possibile caricare i dati in una tabella di staging, eseguire trasformazioni sui dati in gestione temporanea e quindi inserirli in una tabella di produzione.
Nomi di tabella e dello schema
Gli schemi sono un buon modo per raggruppare le tabelle, usate in modo simile, insieme. Se si esegue la migrazione di più database da una soluzione locale a un pool SQL dedicato, è consigliabile eseguire la migrazione di tutte le tabelle di fatto, dimensione e integrazione a uno schema in un pool SQL dedicato.
Ad esempio, è possibile archiviare tutte le tabelle nel pool SQL dedicato WideWorldImportersDW all'interno di uno schema denominato wwi. Il codice seguente crea uno schema definito dall'utente denominato wwi.
CREATE SCHEMA wwi;
Per visualizzare l'organizzazione delle tabelle nel pool SQL dedicato, è possibile usare fatti, dim e int come prefissi ai nomi delle tabelle. La tabella seguente mostra alcuni dei nomi dello schema e della tabella per WideWorldImportersDW.
| Tabella WideWorldImportersDW | Tipo di tabella. | Pool SQL dedicato |
|---|---|---|
| City | Dimensione | wwi.DimCity |
| Ordine | Fact | wwi.FactOrder |
Persistenza delle tabelle
Le tabelle archiviano i dati in modo permanente in Archiviazione di Azure, temporaneamente in Archiviazione di Azure o in un archivio dati esterno al pool SQL dedicato.
Tabella normale
Una tabella regolare archivia i dati in Archiviazione di Azure come parte del pool SQL dedicato. La tabella e i dati sono persistenti indipendentemente dalla presenza o meno di una sessione aperta. Nell'esempio seguente viene creata una tabella regolare con due colonne.
CREATE TABLE MyTable (col1 int, col2 int );
Tabella temporanea
Una tabella temporanea esiste solo per la durata della sessione. È possibile usare una tabella temporanea per impedire agli altri utenti di visualizzare i risultati temporanei e anche di ridurre la necessità di pulizia.
Le tabelle temporanee usano l'archiviazione locale per offrire prestazioni veloci. Per altre informazioni, vedere Tabelle temporanee.
Tabella esterna
Una tabella esterna punta ai dati che si trovano nel BLOB del servizio di archiviazione di Azure o in Azure Data Lake Store. Se usato con l'istruzione CREATE TABLE AS SELECT, selezionare da una tabella esterna importa i dati nel pool SQL dedicato.
Di conseguenza, le tabelle esterne sono utili per il caricamento dei dati. Per un'esercitazione sul caricamento, vedere Usare PolyBase per caricare i dati dall'archiviazione BLOB di Azure.
Tipi di dati
Il pool SQL dedicato supporta i tipi di dati usati più comunemente. Per un elenco dei tipi di dati supportati, vedere tipi di dati nel riferimento sull'istruzione CREATE TABLE. Per informazioni sull'uso dei tipi di dati, vedere Tipi di dati.
Tabelle con distribuzione
Una funzionalità fondamentale del pool SQL dedicato è il modo in cui può archiviare e gestire le tabelle tra distribuzioni. Il pool SQL dedicato supporta tre metodi per la distribuzione dei dati: round robin (impostazione predefinita), hash e replica.
Tabelle con distribuzione hash
Una tabella distribuita hash distribuisce righe in base al valore nella colonna di distribuzione. Una tabella distribuita hash è progettata per ottenere prestazioni elevate per le query in tabelle di grandi dimensioni. Esistono diversi fattori da considerare quando si sceglie una colonna di distribuzione.
Per altre informazioni, vedere Linee guida di progettazione per tabelle distribuite.
Tabelle replicate
Le tabelle replicate mettono a disposizione una copia completa della tabella in ogni nodo di calcolo. Le query vengono eseguite rapidamente nelle tabelle replicate perché i join nelle tabelle replicate non richiedono lo spostamento dei dati. La replica richiede tuttavia spazio di archiviazione aggiuntivo e non è pratico per le tabelle di grandi dimensioni.
Per altre informazioni, vedere Linee guida di progettazione per l'uso di tabelle replicate in Azure SQL Data Warehouse.
Tabelle round robin
Una tabella round robin distribuisce le righe della tabella in modo uniforme tra tutte le distribuzioni. Le righe vengono distribuite in modo casuale. Il caricamento dei dati in una tabella round robin è veloce. Tenere presente che le query possono richiedere più movimenti di dati rispetto agli altri metodi di distribuzione.
Per altre informazioni, vedere Linee guida di progettazione per tabelle distribuite.
Metodi di distribuzione comuni per le tabelle
La categoria della tabella spesso determina l'opzione appropriata per la distribuzione della tabella.
| Categoria di tabella | Opzione di distribuzione consigliata |
|---|---|
| Fact | Usare la distribuzione hash con indice columnstore cluster. Le prestazioni aumentano quando si crea un join tra due tabelle hash nella stessa colonna di distribuzione. |
| Dimensione | Usare le tabelle replicate per le tabelle di dimensioni più piccole. Se le tabelle sono troppo grandi per essere archiviate in ogni nodo di calcolo, usare le tabelle con distribuzione hash. |
| Staging | Usare una tabella round robin per la tabella di staging. Il carico con un'istruzione CTAS è veloce. Una volta che i dati sono presenti nella tabella di staging, usare INSERT...SELECT per spostare i dati nelle tabelle di produzione. |
Nota
Per raccomandazioni sulla strategia di distribuzione della tabella migliore da usare in base ai carichi di lavoro, vedere l'Azure Synapse SQL Distribution Advisor.
Partizioni della tabella
Una tabella partizionata archivia ed esegue operazioni sulle righe di tabella in base agli intervalli di dati. Una tabella può, ad esempio, essere partizionata in base ai giorni, ai mesi o agli anni. È possibile migliorare le prestazioni delle query tramite l'eliminazione della partizione, che limita l'analisi di una query ai dati all'interno di una partizione. È inoltre possibile gestire i dati tramite la commutazione tra partizioni. Poiché i dati nel pool SQL sono già distribuiti, troppe partizioni possono rallentare le prestazioni delle query. Per altre informazioni, vedere Indicazioni sul partizionamento. Quando si passa a partizioni di tabella non vuote, è consigliabile usare l'opzione TRUNCATE_TARGET nell'istruzione ALTER TABLE se i dati esistenti devono essere troncati. Il codice seguente passa ai dati giornalieri trasformati in SalesFact sovrascrivendo i dati esistenti.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Indici columnstore
Per impostazione predefinita, il pool SQL dedicato archivia una tabella come indice columnstore cluster. Questo modulo di archiviazione dei dati raggiunge compressione dei dati e prestazioni di query elevate su tabelle di grandi dimensioni.
L'indice columnstore cluster è in genere la scelta migliore, ma in alcuni casi un indice cluster o un heap è la struttura di archiviazione appropriata.
Suggerimento
Una tabella heap può essere particolarmente utile per caricare dati temporanei, ad esempio una tabella di staging che viene trasformata in una tabella finale.
Per un elenco delle funzionalità columnstore, vedere Indici columnstore - Novità. Per migliorare le prestazioni dell'indice columnstore, vedere Ottimizzazione della qualità di un gruppo di righe per columnstore.
Statistiche
Quando crea il piano per l'esecuzione di una query, Query Optimizer usa le statistiche a livello di colonna.
Per migliorare le prestazioni delle query, è importante avere statistiche sulle singole colonne, in particolare le colonne usate nei join di query. La creazione di statistiche viene eseguita automaticamente.
L'aggiornamento delle statistiche non viene eseguito automaticamente. Aggiornare le statistiche dopo l'aggiunta o la modifica di un numero significativo di righe. Aggiornare, ad esempio, le statistiche dopo un carico. Per altre informazioni, vedere Indicazioni sulle statistiche.
Chiave primaria e chiave univoca
PRIMARY KEY è supportato solo quando vengono usati SIA NONCLUSTERED che NOT ENFORCED. Il vincolo UNIQUE è supportato solo con NOT ENFORCED. Controllare i vincoli di tabella del pool SQL dedicati.
Comandi per la creazione di tabelle
È possibile creare una tabella come nuova tabella vuota. È inoltre possibile creare e popolare una tabella con i risultati di un'istruzione SELECT. Di seguito sono riportati i comandi T-SQL per la creazione di una tabella.
| Istruzione T-SQL | Descrizione |
|---|---|
| CREATE TABLE | Crea una tabella vuota definendo tutte le opzioni e le colonne della tabella. |
| CREATE EXTERNAL TABLE | Crea una tabella esterna. La definizione della tabella viene archiviata nel pool SQL dedicato. I dati della tabella vengono archiviati nell'archivio BLOB di Azure o in Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Popola una nuova tabella con i risultati di un'istruzione SELECT. Le colonne e i tipi di dati della tabella si basano sui risultati dell'istruzione SELECT. Per importare i dati, questa istruzione può selezionare da una tabella esterna. |
| CREATE EXTERNAL TABLE AS SELECT | Crea una nuova tabella esterna esportando i risultati di un'istruzione SELECT in una posizione esterna, vale a dire l'archivio BLOB di Azure o Azure Data Lake Store. |
Allineamento dei dati di origine con pool SQL dedicato
Le tabelle del pool SQL dedicate vengono popolate caricando i dati da un'altra origine dati. Per eseguire un carico riuscito, il numero e i tipi di dati delle colonne nei dati di origine devono essere allineati alla definizione della tabella nel pool SQL dedicato. Il recupero dei dati da allineare potrebbe risultare l'operazione più difficile della progettazione delle tabelle.
Se i dati provengono da più archivi dati, caricare i dati nel pool SQL dedicato e archiviarlo in una tabella di integrazione. Una volta che i dati si trovano nella tabella di integrazione, è possibile usare la potenza del pool SQL dedicato per eseguire operazioni di trasformazione. Dopo aver preparati i dati, è possibile inserirli nelle tabelle di produzione.
Funzionalità non supportate delle tabelle
Il pool SQL dedicato supporta molti, ma non tutti, delle funzionalità della tabella offerte da altri database. L'elenco seguente mostra alcune funzionalità della tabella che non sono supportate nel pool SQL dedicato:
- Chiave esterna, Controllare vincoli tabella
- Colonne calcolate
- Viste indicizzate
- Sequenza
- Colonne sparse
- Chiavi surrogate. Implementare con Identity.
- Sinonimi
- Trigger
- Indici univoci
- Tipi definiti dall'utente
Query di dimensioni della tabella
Nota
Per i conteggi accurati delle query in questa sezione, assicurarsi che la manutenzione dell'indice si verifichi regolarmente e dopo modifiche ai dati di grandi dimensioni.
Un modo semplice per identificare lo spazio e le righe usati da una tabella in ognuna delle 60 distribuzioni consiste nell'usare DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Tuttavia, l'utilizzo dei comandi DBCC può essere abbastanza restrittivo. Le viste a gestione dinamica (DMV) mostrano maggiori dettagli rispetto ai comandi DBCC. Iniziare creando questa visualizzazione:
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]
AND i.[index_id] = nps.[index_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
;
Riepilogo dello spazio della tabella
Questa query restituisce le righe e lo spazio per singola tabella. Consente di visualizzare quali tabelle sono le tabelle più grandi e se sono round robin, replicate o distribuite con hash. Per le tabelle con distribuzione hash, la query mostra la colonna di distribuzione.
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
;
Spazio della tabella per tipo di distribuzione
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
;
Spazio della tabella per tipo di indice
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
;
Riepilogo dello spazio di distribuzione
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
;
Passaggi successivi
Dopo aver creato le tabelle per il pool SQL dedicato, il passaggio successivo consiste nel caricare i dati nella tabella. Per un'esercitazione sul caricamento, vedere Caricamento dei dati nel pool SQL dedicato e revisione delle strategie di caricamento dei dati per il pool SQL dedicato in Azure Synapse Analytics.