Creare le tabelle di un data warehouse

  • 10 minuti

Ora che si conoscono i principi dell'architettura di base per uno schema di data warehouse relazionale, si vedrà come creare un data warehouse.

Creazione di un pool SQL dedicato

Per creare un data warehouse relazionale in Azure Synapse Analytics, è necessario creare un pool SQL dedicato. Il modo più semplice per eseguire questa operazione in un'area di lavoro esistente di Azure Synapse Analytics consiste nell'uso della pagina Gestione in Azure Synapse Studio, come illustrato di seguito:

A screenshot of the SQL pools tab in the Manage page of Synapse Studio.

Quando si effettua il provisioning di un pool SQL dedicato, è possibile specificare le impostazioni di configurazione seguenti:

  • Un nome univoco per il pool SQL dedicato.
  • Un livello di prestazioni per il pool SQL, che può variare da DW100c a DW30000c e che determina il costo orario per il pool quando è in esecuzione.
  • Se iniziare con un pool vuoto o ripristinare un database esistente da un backup.
  • Le regole di confronto del pool SQL, che determinano l'ordinamento e il confronto tra stringhe per il database (non è possibile modificare le regole di confronto dopo la creazione).

Dopo aver creato un pool SQL dedicato, è possibile controllarne lo stato di esecuzione nella pagina Gestione di Synapse Studio, sospendendolo quando non è necessario per evitare costi inutili.

Quando il pool è in esecuzione, è possibile esaminarlo nella pagina Dati e creare script SQL da eseguire nel pool.

Considerazioni per la creazione di tabelle

Per creare tabelle nel pool SQL dedicato, usare l'istruzione Transact-SQL CREATE TABLE (o talvolta CREATE EXTERNAL TABLE). Le opzioni specifiche usate nell'istruzione dipendono dal tipo di tabella che si sta creando, tra cui:

  • Tabelle dei fatti
  • Tabelle delle dimensioni
  • Tabelle di staging

Nota

Il data warehouse è costituito da tabelle dei fatti e delle dimensioni, come illustrato in precedenza. Le tabelle di staging vengono spesso usate come parte del processo di caricamento del data warehousing per inserire dati dai sistemi di origine.

Quando si progetta un modello di schema star per i set di dati di piccole o medie dimensioni, è possibile usare il database preferito, ad esempio Azure SQL. Per i set di dati di dimensioni maggiori è possibile trarre vantaggio dall'implementazione di un data warehouse in Azure Synapse Analytics invece che in SQL Server. Durante la creazione di tabelle in Azure Synapse Analytics è importante comprendere alcune differenze fondamentali.

Vincoli di integrità dei dati

I pool SQL dedicati in Synapse Analytics non supportano chiavi esterne e vincoli univoci, che invece sono presenti in altri sistemi di database relazionali come SQL Server. Ciò significa che i processi eseguiti per caricare i dati devono mantenere l'univocità e l'integrità referenziale per le chiavi, senza basarsi sulle definizioni di tabella nel database per tale scopo.

Suggerimento

Per altre informazioni sui vincoli nei pool SQL dedicati di Azure Synapse Analytics, vedere Chiave primaria, chiave esterna e chiave univoca usando il pool SQL dedicato in Azure Synapse Analytics.

Indici

Anche se i pool SQL dedicati di Synapse Analytics supportano gli indici cluster, come in SQL Server, il tipo di indice predefinito è columnstore cluster. Questo tipo di indice offre un vantaggio significativo in termini di prestazioni durante l'esecuzione di query su grandi quantità di dati in uno schema tipico di data warehouse e deve essere usato quando possibile. Alcune tabelle, tuttavia, possono contenere tipi di dati che non possono essere inclusi in un indice columnstore cluster, ad esempio VARBINARY(MAX). In tal caso, è possibile usare in alternativa un indice cluster.

Suggerimento

Per altre informazioni sull'indicizzazione nei pool SQL dedicati di Azure Synapse Analytics, vedere Indici nelle tabelle dei pool SQL dedicati in Azure Synapse Analytics.

Distribuzione

I pool SQL dedicati di Azure Synapse Analytics usano un'architettura MPP (Massively Parallel Processing) invece dell'architettura SMP (Symmetric MultiProcessing) usata nella maggior parte dei sistemi di database OLTP. In un sistema MPP, i dati di una tabella vengono distribuiti per l'elaborazione su un pool di nodi. Synapse Analytics supporta i tipi di distribuzione seguenti:

  • Hash: un valore hash deterministico viene calcolato per la colonna specificata e usato per assegnare la riga a un nodo di calcolo.
  • Round robin: le righe vengono distribuite uniformemente su tutti i nodi di calcolo.
  • Replicata: in ogni nodo di calcolo viene archiviata una copia della tabella.

Il tipo di tabella spesso determina l'opzione appropriata per la distribuzione della tabella.

Tipo di tabella Opzione di distribuzione consigliata
Dimensione Usare la distribuzione replicata per tabelle più piccole per evitare la sequenza casuale dei dati nel join con tabelle dei fatti distribuite. Se le tabelle sono troppo grandi per essere archiviate in ogni nodo di calcolo, usare la distribuzione hash.
Fatto Usare la distribuzione hash con l'indice columnstore cluster per distribuire le tabelle dei fatti sui nodi di calcolo.
Staging Usare la distribuzione round robin per le tabelle di staging per distribuire uniformemente i dati sui nodi di calcolo.

Suggerimento

Per altre informazioni sulle strategie di distribuzione per le tabelle in Azure Synapse Analytics, vedere Linee guida per la progettazione di tabelle distribuite usando pool SQL dedicati in Azure Synapse Analytics.

Creazione di tabelle delle dimensioni

Quando si crea una tabella delle dimensioni, verificare che la definizione di tabella includa chiavi sostitutive e alternative, nonché colonne per gli attributi della dimensione da usare per raggruppare le aggregazioni. Spesso è più semplice usare una colonna IDENTITY per generare automaticamente una chiave sostitutiva incrementale, altrimenti è necessario generare chiavi univoche ogni volta che si caricano i dati. L'esempio seguente mostra un'istruzione CREATE TABLE per un'ipotetica tabella delle dimensioni DimCustomer.

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Nota

Se lo si desidera, è possibile creare uno schema specifico come spazio dei nomi per le tabelle. In questo esempio viene usato lo schema dbo predefinito.

Se si intende usare uno schema snowflake, in cui le tabelle delle dimensioni sono correlate tra loro, è necessario includere la chiave per la dimensione padre nella definizione della tabella delle dimensioni figlio. È ad esempio possibile usare il codice SQL seguente per spostare i dettagli degli indirizzi geografici dalla tabella DimCustomer a una tabella delle dimensioni DimGeography:

CREATE TABLE dbo.DimGeography
(
    GeographyKey INT IDENTITY NOT NULL,
    GeographyAlternateKey NVARCHAR(10) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    GeographyKey INT NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Tabelle delle dimensioni temporali

La maggior parte dei data warehouse include una tabella delle dimensioni temporali che consente di aggregare i dati in base a più livelli gerarchici di intervallo di tempo. Nel codice seguente, ad esempio, viene creata una tabella DimDate con attributi correlati a date specifiche.

CREATE TABLE dbo.DimDate
( 
    DateKey INT NOT NULL,
    DateAltKey DATETIME NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(15) NOT NULL,
    MonthOfYear INT NOT NULL,
    MonthName NVARCHAR(15) NOT NULL,
    CalendarQuarter INT  NOT NULL,
    CalendarYear INT NOT NULL,
    FiscalQuarter INT NOT NULL,
    FiscalYear INT NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Suggerimento

Un modello comune per la creazione di una tabella delle dimensioni per le date consiste nell'uso della data numerica in formato GGMMAAAA o AAAAMMGG come chiave sostitutiva con valore intero e della data con tipo di dati DATE o DATETIME come chiave alternativa.

Creazione di tabelle dei fatti

Le tabelle dei fatti includono le chiavi per ogni dimensione a cui sono correlate e gli attributi e le misure numeriche per osservazioni o eventi specifici da analizzare.

Nell'esempio di codice seguente viene creata un'ipotetica tabella dei fatti denominata FactSales correlata a più dimensioni tramite colonne chiave (data, cliente, prodotto e negozio)

CREATE TABLE dbo.FactSales
(
    OrderDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    StoreKey INT NOT NULL,
    OrderNumber NVARCHAR(10) NOT NULL,
    OrderLineItem INT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice DECIMAL NOT NULL,
    Discount DECIMAL NOT NULL,
    Tax DECIMAL NOT NULL,
    SalesAmount DECIMAL NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(OrderNumber),
    CLUSTERED COLUMNSTORE INDEX
);

Creazione di tabelle di staging

Le tabelle di staging vengono usate come risorsa di archiviazione temporanea per i dati durante il caricamento nel data warehouse. Un modello tipico consiste nella strutturazione della tabella per renderla il più efficiente possibile per inserire i dati dall'origine esterna (spesso file in un data lake) nel database relazionale e quindi nell'uso di istruzioni SQL per caricare i dati dalle tabelle di staging nelle tabelle delle dimensioni e dei fatti.

L'esempio di codice seguente crea una tabella di staging per i dati dei prodotti che verranno infine caricati in una tabella delle dimensioni:

CREATE TABLE dbo.StageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

Uso di tabelle esterne

In alcuni casi, se i dati da caricare si trovano in file con una struttura appropriata, può essere più efficace creare tabelle esterne che fanno riferimento al percorso dei file. In questo modo, i dati possono essere letti direttamente dai file di origine anziché essere caricati nell'archivio relazionale. L'esempio seguente mostra come creare una tabella esterna che fa riferimento ai file nel data lake associato all'area di lavoro di Synapse:


-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO

-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DATA_SOURCE = StagedFiles,
    LOCATION = 'products/*.parquet',
    FILE_FORMAT = ParquetFormat
);
GO

Nota

Per altre informazioni sull'uso di tabelle esterne, vedere Usare tabelle esterne con Synapse SQL nella documentazione di Azure Synapse Analytics.