Datawarehouse-tabellen maken

  • 10 minuten

Nu u de basisprincipes voor architectuur voor een relationeel datawarehouseschema begrijpt, gaan we eens kijken hoe u een datawarehouse maakt.

Een toegewezen SQL-pool maken

Als u een relationeel datawarehouse wilt maken in Azure Synapse Analytics, moet u een toegewezen SQL-pool maken. De eenvoudigste manier om dit te doen in een bestaande Azure Synapse Analytics-werkruimte is het gebruik van de pagina Beheren in Azure Synapse Studio, zoals hier wordt weergegeven:

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

Bij het inrichten van een toegewezen SQL-pool kunt u de volgende configuratie-instellingen opgeven:

  • Een unieke naam voor de toegewezen SQL-pool.
  • Een prestatieniveau voor de SQL-pool, dat kan variëren van DW100c tot DW30000c en waarmee de kosten per uur voor de pool worden bepaald wanneer deze wordt uitgevoerd.
  • Of u nu met een lege pool begint of een bestaande database herstelt vanuit een back-up.
  • De sortering van de SQL-pool, waarmee de sorteervolgorde en tekenreeksvergelijkingsregels voor de database worden bepaald. (U kunt de sortering niet wijzigen na het maken).

Nadat u een toegewezen SQL-pool hebt gemaakt, kunt u de uitvoeringsstatus beheren op de pagina Beheren van Synapse Studio. Als u deze niet nodig hebt om onnodige kosten te voorkomen, kunt u deze onderbreken.

Wanneer de pool wordt uitgevoerd, kunt u deze verkennen op de pagina Gegevens en SQL-scripts maken om hierin uit te voeren.

Overwegingen voor het maken van tabellen

Als u tabellen in de toegewezen SQL-pool wilt maken, gebruikt u de CREATE TABLE Transact-SQL-instructie (of soms).CREATE EXTERNAL TABLE De specifieke opties die in de instructie worden gebruikt, zijn afhankelijk van het type tabel dat u maakt, waaronder:

  • Feitentabellen
  • Dimensietabellen
  • Faseringstabellen

Notitie

Het datawarehouse bestaat uit feiten - en dimensietabellen zoals eerder is besproken. Faseringstabellen worden vaak gebruikt als onderdeel van het laadproces voor datawarehousing om gegevens op te nemen uit bronsystemen.

Wanneer u een stervormig schemamodel ontwerpt voor kleine of middelgrote gegevenssets, kunt u uw voorkeursdatabase gebruiken, zoals Azure SQL. Voor grotere gegevenssets profiteert u mogelijk van het implementeren van uw datawarehouse in Azure Synapse Analytics in plaats van SQL Server. Het is belangrijk om inzicht te hebben in enkele belangrijke verschillen bij het maken van tabellen in Synapse Analytics.

Beperkingen voor gegevensintegriteit

Toegewezen SQL-pools in Synapse Analytics bieden geen ondersteuning voor refererende sleutels en unieke beperkingen, zoals in andere relationele databasesystemen zoals SQL Server. Dit betekent dat taken die worden gebruikt voor het laden van gegevens uniekheid en referentiële integriteit voor sleutels moeten behouden, zonder dat hiervoor de tabeldefinities in de database worden gebruikt.

Tip

Zie Primaire sleutel, refererende sleutel en unieke sleutel met behulp van een toegewezen SQL-pool in Azure Synapse Analytics voor meer informatie over beperkingen in toegewezen SQL-pools.

Indexen

Hoewel toegewezen SQL-pools van Synapse Analytics geclusterde indexen ondersteunen zoals gevonden in SQL Server, is het standaardindextype geclusterde columnstore. Dit indextype biedt een aanzienlijk prestatievoordeel bij het uitvoeren van query's op grote hoeveelheden gegevens in een typisch datawarehouseschema en moet waar mogelijk worden gebruikt. Sommige tabellen kunnen echter gegevenstypen bevatten die niet kunnen worden opgenomen in een geclusterde columnstore-index (bijvoorbeeld VARBINARY(MAX)), in welk geval een geclusterde index kan worden gebruikt.

Tip

Zie Indexen voor toegewezen SQL-pooltabellen in Azure Synapse Analytics voor meer informatie over indexering in toegewezen SQL-pooltabellen in Azure Synapse Analytics.

Distributie

Toegewezen SQL-pools van Azure Synapse Analytics gebruiken een MPP-architectuur (Massively Parallel Processing) in plaats van de SMP-architectuur (symmetrische multiprocessing) die wordt gebruikt in de meeste OLTP-databasesystemen. In een MPP-systeem worden de gegevens in een tabel gedistribueerd voor verwerking in een groep knooppunten. Synapse Analytics ondersteunt de volgende soorten distributie:

  • Hash: Een deterministische hashwaarde wordt berekend voor de opgegeven kolom en wordt gebruikt om de rij toe te wijzen aan een rekenknooppunt.
  • Round robin: rijen worden gelijkmatig verdeeld over alle rekenknooppunten.
  • Gerepliceerd: er wordt een kopie van de tabel opgeslagen op elk rekenknooppunt.

Het tabeltype bepaalt vaak welke optie u wilt kiezen voor het distribueren van de tabel.

Tabeltype Aanbevolen distributieoptie
Dimensie Gebruik gerepliceerde distributie voor kleinere tabellen om te voorkomen dat gegevens worden gesnipperd bij het samenvoegen met gedistribueerde feitentabellen. Als tabellen te groot zijn om op elk rekenknooppunt op te slaan, gebruikt u hashdistributie.
Feit Gebruik hashdistributie met geclusterde columnstore-index om feitentabellen over rekenknooppunten te verdelen.
Staging Gebruik round robin-distributie voor faseringstabellen om gegevens gelijkmatig over rekenknooppunten te verdelen.

Tip

Zie Richtlijnen voor het ontwerpen van gedistribueerde tabellen met behulp van een toegewezen SQL-pool in Azure Synapse Analytics voor meer informatie over distributiestrategieën voor tabellen in Azure Synapse Analytics.

Dimensietabellen maken

Wanneer u een dimensietabel maakt, moet u ervoor zorgen dat de tabeldefinitie surrogaat- en alternatieve sleutels bevat, evenals kolommen voor de kenmerken van de dimensie die u wilt gebruiken om aggregaties te groeperen. Het is vaak het eenvoudigst om een IDENTITY kolom te gebruiken om automatisch een incrementele surrogaatsleutel te genereren (anders moet u unieke sleutels genereren telkens wanneer u gegevens laadt). In het volgende voorbeeld ziet u een CREATE TABLE instructie voor een hypothetische dimensietabel 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
);

Notitie

Desgewenst kunt u een specifiek schema maken als een naamruimte voor uw tabellen. In dit voorbeeld wordt het standaard-dbo-schema gebruikt.

Als u een snowflake-schema wilt gebruiken waarin dimensietabellen aan elkaar zijn gerelateerd, moet u de sleutel voor de bovenliggende dimensie opnemen in de definitie van de onderliggende dimensietabel. De volgende SQL-code kan bijvoorbeeld worden gebruikt om de geografische adresgegevens van de tabel DimCustomer te verplaatsen naar een afzonderlijke dimensietabel 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
);

Tijddimensietabellen

De meeste datawarehouses bevatten een tijddimensietabel waarmee u gegevens kunt aggregeren op meerdere hiërarchische niveaus van tijdsinterval. In het volgende voorbeeld wordt bijvoorbeeld een DimDate-tabel gemaakt met kenmerken die betrekking hebben op specifieke datums.

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
);

Tip

Een veelvoorkomend patroon bij het maken van een dimensietabel voor datums is het gebruik van de numerieke datum in de notatie DDMMYYYYY of JJJJMMDD als een surrogaatsleutel voor gehele getallen en de datum als een DATE of DATETIME gegevenstype als alternatieve sleutel.

Feitentabellen maken

Feitentabellen bevatten de sleutels voor elke dimensie waaraan ze zijn gerelateerd, en de kenmerken en numerieke metingen voor specifieke gebeurtenissen of waarnemingen die u wilt analyseren.

In het volgende codevoorbeeld wordt een hypothetische feitentabel gemaakt met de naam FactSales die is gerelateerd aan meerdere dimensies via sleutelkolommen (datum, klant, product en winkel)

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
);

Faseringstabellen maken

Faseringstabellen worden gebruikt als tijdelijke opslag voor gegevens omdat deze in het datawarehouse worden geladen. Een typisch patroon is om de tabel zo efficiënt mogelijk te structureren om de gegevens op te nemen uit de externe bron (vaak bestanden in een data lake) in de relationele database en vervolgens SQL-instructies te gebruiken om de gegevens uit de faseringstabellen in de dimensie- en feitentabellen te laden.

In het volgende codevoorbeeld wordt een faseringstabel gemaakt voor productgegevens die uiteindelijk in een dimensietabel worden geladen:

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
);

Externe tabellen gebruiken

Als de gegevens die moeten worden geladen zich in bestanden met een geschikte structuur bevinden, kan het in sommige gevallen effectiever zijn om externe tabellen te maken die verwijzen naar de bestandslocatie. Op deze manier kunnen de gegevens rechtstreeks vanuit de bronbestanden worden gelezen in plaats van in het relationele archief te worden geladen. In het volgende voorbeeld ziet u hoe u een externe tabel maakt die verwijst naar bestanden in de data lake die is gekoppeld aan de Synapse-werkruimte:


-- 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

Notitie

Zie Externe tabellen gebruiken met Synapse SQL in de documentatie van Azure Synapse Analytics voor meer informatie over het gebruik van externe tabellen.