Progettare tabelle con Synapse SQL in Azure Synapse Analytics
Questo documento include i concetti fondamentali per la progettazione di tabelle con il pool SQL dedicato e il pool SQL serverless.
Il pool SQL serverless è un servizio di query sui dati del data lake. Non dispone di risorse di archiviazione locali per l'inserimento dei dati. Il pool SQL dedicato rappresenta una raccolta di risorse di analisi di cui viene effettuato il provisioning durante l'uso di Synapse SQL. Le dimensioni di un pool SQL dedicato sono determinate dalle unità Data Warehouse (DWU).
La tabella seguente elenca gli argomenti pertinenti per il pool SQL dedicato e il pool SQL serverless:
| Argomento | Pool SQL dedicato | Pool SQL serverless |
|---|---|---|
| Determinare la categoria della tabella | Sì | No |
| Nomi di schemi | Sì | Sì |
| Nomi di tabella | Sì | No |
| Persistenza delle tabelle | Sì | No |
| Tabella normale | Sì | No |
| Tabella temporanea | Sì | Sì |
| Tabella esterna | Sì | Sì |
| Tipo di dati | Sì | Sì |
| Tabelle con distribuzione | Sì | No |
| Tabelle con distribuzione hash | Sì | No |
| Tabelle replicate | Sì | No |
| Tabelle round robin | Sì | No |
| Metodi di distribuzione comuni per le tabelle | Sì | No |
| Partitions | Sì | Sì |
| Indici columnstore | Sì | No |
| Statistica | Sì | Sì |
| Chiave primaria e chiave univoca | Sì | No |
| Comandi per la creazione di tabelle | Sì | No |
| Allineamento dei dati di origine con il data warehouse | Sì | No |
| Funzionalità non supportate delle tabelle | Sì | No |
| Query per le dimensioni delle tabelle | Sì | No |
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 dei fatti 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 fact contengono i dati quantitativi che vengono comunemente generati in un sistema transazionale e successivamente caricati nel data warehouse. Un'azienda di vendita al dettaglio, ad esempio, genera quotidianamente transazioni di vendita e successivamente carica i dati in una tabella fact di data warehouse per analizzarli.
Le tabelle delle dimensioni contengono i dati degli attributi che possono cambiare, ma che 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 schema
Gli schemi sono un modo valido per raggruppare le tabelle usate in modo simile. Il codice seguente crea uno schema definito dall'utente denominato wwi.
CREATE SCHEMA wwi;
Nomi di tabella
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 dei fatti, delle dimensioni e di integrazione in un unico schema del pool SQL dedicato. Si può, ad esempio, archiviare tutte le tabelle nel data warehouse di esempio WideWorldImportersDW all'interno di un unico schema denominato wwi.
Per visualizzare l'organizzazione delle tabelle nel pool SQL dedicato, è possibile usare fact, dim e int come prefissi dei nomi delle tabelle. La tabella seguente include alcuni dei nomi di tabella e dello schema per WideWorldImportersDW.
| Tabella WideWorldImportersDW | Tipo di tabella | Pool SQL dedicato |
|---|---|---|
| Città | Dimensione | wwi.DimCity |
| Ordinamento | Fatto | wwi.FactOrder |
Persistenza delle tabelle
Le tabelle archiviano i dati in modo permanente in Archiviazione di Azure, temporaneamente in Archiviazione di Azure oppure in un archivio dati esterno al data warehouse.
Tabella normale
Una tabella normale archivia i dati in Archiviazione di Azure come parte del data warehouse. La tabella e i dati sono persistenti indipendentemente dalla presenza o meno di una sessione aperta. L'esempio seguente crea una tabella normale con due colonne.
CREATE TABLE MyTable (col1 int, col2 int );
Tabella temporanea
Una tabella temporanea esiste solo per la durata della sessione. Si può usare questo tipo di tabella per impedire ad altri utenti di visualizzare i risultati temporanei e anche per ridurre la necessità di eseguire la pulizia. Le tabelle temporanee usano l'archiviazione locale e, nei pool SQL dedicati, possono offrire prestazioni più veloci.
Il pool SQL serverless supporta le tabelle temporanee. Tuttavia, l'utilizzo è limitato perché è possibile selezionare da una tabella temporanea, ma non è possibile unirla tramite join ai file nelle risorse di archiviazione.
Per altre informazioni, vedere Tabelle temporanee.
Tabella esterna
Le tabelle esterne puntano ai dati che risiedono nel BLOB del servizio di archiviazione di Azure o in Azure Data Lake Store.
Importare i dati dalle tabelle esterne in pool SQL dedicati usando l'istruzione CREATE TABLE AS SELECT (CETAS). Per un'esercitazione sul caricamento, vedere Usare PolyBase per caricare dati dall'archivio BLOB di Azure a SQL Data Warehouse.
Per il pool SQL serverless, è possibile usare CETAS per salvare il risultato della query in una tabella esterna in Archiviazione di Azure.
Tipo di dati
Il pool SQL dedicato supporta i tipi di dati più diffusi. Per un elenco dei tipi di dati supportati, vedere tipi di dati nel riferimento sull'istruzione CREATE TABLE. Per altre informazioni sull'utilizzo 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 operare sulle tabelle nelle distribuzioni. Il pool SQL dedicato supporta tre metodi per la distribuzione dei dati:
- Round robin (impostazione predefinita)
- Hash
- Tabella replicata
Tabelle con distribuzione hash
Una tabella con distribuzione hash distribuisce le righe in base al valore nella colonna di distribuzione. Una tabella con distribuzione hash è progettata per ottenere prestazioni elevate per le query su 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 sulle tabelle replicate sono veloci poiché i join nelle tabelle replicate non richiedono lo spostamento dei dati. La replica esige tuttavia uno spazio di archiviazione aggiuntivo e non risulta pratica 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. Tuttavia, le query possono richiedere un maggiore spostamento dei 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 di tabella determina spesso l'opzione ottimale per la distribuzione delle tabelle.
| Categoria di tabella | Opzione di distribuzione consigliata |
|---|---|
| Fatto | 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. |
Partizioni
Nei pool SQL dedicati, 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 in un pool SQL dedicato sono già distribuiti, un numero eccessivo di partizioni può rallentare le prestazioni delle query. Per altre informazioni, vedere Indicazioni sul partizionamento.
Suggerimento
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 sposta i dati giornalieri trasformati in una partizione SalesFact e sovrascrive tutti i dati esistenti.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Nel pool SQL serverless è possibile limitare i file/cartelle (partizioni) che verranno letti dalla query. Il partizionamento in base al percorso è supportato usando le funzioni filepath e fileinfo descritte in Esecuzione di query sui file di archiviazione. L'esempio seguente legge una cartella con dati per l'anno 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
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 il caricamento di 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 su singole colonne, in particolare sulle colonne usate nei join delle query. Synapse SQL supporta la creazione automatica di statistiche.
L'aggiornamento delle statistiche non è automatico. Aggiornare le statistiche dopo l'aggiunta o la modifica di un numero significativo di righe. Aggiornare, ad esempio, le statistiche dopo un caricamento. Per informazioni aggiuntive, vedere l'articolo Indicazioni sulle statistiche.
Chiave primaria e chiave univoca
Per i pool SQL dedicati, il vincolo PRIMARY KEY è supportato solo se vengono usati sia NONCLUSTERED che NOT ENFORCED. Il vincolo UNIQUE è supportato solo quando viene usato NOT ENFORCED. Per altre informazioni, vedere l'articolo Vincoli di tabella del pool SQL dedicato.
Comandi per la creazione di tabelle
Per il pool SQL dedicato, è 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'archiviazione 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, La posizione è quindi l'archiviazione BLOB di Azure o Azure Data Lake Store. |
Allineare i dati di origine con il data warehouse
Le tabelle del pool SQL dedicato vengono popolate caricando i dati da un'altra origine dati. Per eseguire un caricamento corretto, il numero e i tipi di dati delle colonne nei dati di origine devono essere allineati con la definizione della tabella nel data warehouse.
Nota
Il recupero dei dati da allineare potrebbe risultare l'operazione più difficile della progettazione delle tabelle.
Se i dati provengono da più archivi dati, è possibile trasferirli nel data warehouse e archiviarli in una tabella di integrazione. Dopo che i dati sono stati inseriti nella tabella di integrazione, è possibile sfruttare la potenza del pool SQL dedicato per implementare operazioni di trasformazione. Dopo aver preparati i dati, è possibile inserirli nelle tabelle di produzione.
Funzionalità non supportate delle tabelle
Il pool SQL dedicato supporta molte delle funzionalità per tabelle offerte da altri database, ma non tutte. Di seguito sono elencate alcune delle funzionalità per tabelle che non sono supportate nel pool SQL dedicato.
- Chiave esterna, consultare Vincoli di tabella
- Colonne calcolate
- Viste indicizzate
- Sequenza
- Colonne di tipo sparse
- Chiavi sostitutive, implementare con Identity
- Sinonimi
- Trigger
- Indici univoci
- Tipi definiti dall'utente
Query di dimensioni della tabella
In un pool SQL dedicato, un modo semplice per identificare lo spazio e le righe utilizzati da una tabella in ognuna delle 60 distribuzioni consiste nell'usare DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Tuttavia, tenere presente che l'utilizzo dei comandi DBCC può essere abbastanza restrittivo. Le viste a gestione dinamica (DMV) mostrano maggiori dettagli rispetto ai comandi DBCC. Per iniziare, creare la vista seguente.
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
;
Riepilogo dello spazio della tabella
Questa query restituisce le righe e lo spazio per singola tabella. Il riepilogo dello spazio tabella consente di visualizzare quali sono le tabelle più grandi. Si noterà anche se sono round robin, replicate o con distribuzione 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 avere creato le tabelle per il data warehouse, il passaggio successivo consiste nel caricare i dati nella tabella. Per un'esercitazione sul caricamento, vedere Caricamento di dati in un pool SQL dedicato.