Skapa informationslagertabeller

10 minuter

Nu när du förstår de grundläggande arkitekturprinciperna för ett relationsdatalagerschema ska vi utforska hur du skapar ett informationslager.

Skapa en dedikerad SQL-pool

Om du vill skapa ett relationsdatalager i Azure Synapse Analytics måste du skapa en dedikerad SQL-pool. Det enklaste sättet att göra detta på en befintlig Azure Synapse Analytics-arbetsyta är att använda sidan Hantera i Azure Synapse Studio, som du ser här:

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

När du etablerar en dedikerad SQL-pool kan du ange följande konfigurationsinställningar:

Ett unikt namn för den dedikerade SQL-poolen.
En prestandanivå för SQL-poolen, som kan variera från DW100c till DW30000c och som avgör kostnaden per timme för poolen när den körs.
Om du vill börja med en tom pool eller återställa en befintlig databas från en säkerhetskopia.
Sortering av SQL-poolen, som avgör sorteringsordning och strängjämförelseregler för databasen. (Du kan inte ändra sorteringen när du har skapat den).

När du har skapat en dedikerad SQL-pool kan du styra dess körningstillstånd på sidan Hantera i Synapse Studio. Pausa den när den inte behövs för att förhindra onödiga kostnader.

När poolen körs kan du utforska den på sidan Data och skapa SQL-skript som ska köras i den.

Överväganden för att skapa tabeller

Om du vill skapa tabeller i den dedikerade SQL-poolen använder du transact-SQL-instruktionen CREATE TABLE CREATE EXTERNAL TABLE(eller ibland ) Transact-SQL. De specifika alternativ som används i -instruktionen beror på vilken typ av tabell du skapar, vilket kan vara:

Faktatabeller
Dimensionstabeller
Mellanlagringstabeller

Kommentar

Informationslagret består av fakta- och dimensionstabeller enligt beskrivningen tidigare. Mellanlagringstabeller används ofta som en del av datalagerinläsningsprocessen för att mata in data från källsystem.

När du utformar en star-schemamodell för små eller medelstora datamängder kan du använda den databas som du föredrar, till exempel Azure SQL. För större datamängder kan du ha nytta av att implementera ditt informationslager i Azure Synapse Analytics i stället för SQL Server. Det är viktigt att förstå några viktiga skillnader när du skapar tabeller i Synapse Analytics.

Begränsningar för dataintegritet

Dedikerade SQL-pooler i Synapse Analytics stöder inte sekundärnyckel och unika begränsningar som finns i andra relationsdatabassystem som SQL Server. Det innebär att jobb som används för att läsa in data måste upprätthålla unikhet och referensintegritet för nycklar, utan att behöva förlita sig på tabelldefinitionerna i databasen för att göra det.

Dricks

Mer information om begränsningar i dedikerade SQL-pooler i Azure Synapse Analytics finns i Primärnyckel, sekundärnyckel och unik nyckel med dedikerad SQL-pool i Azure Synapse Analytics.

Index

Medan synapse Analytics-dedikerade SQL-pooler stöder klustrade index som finns i SQL Server, är standardindextypen klustrad kolumnlagring. Den här indextypen ger en betydande prestandafördel när du kör frågor mot stora mängder data i ett typiskt informationslagerschema och bör användas där det är möjligt. Vissa tabeller kan dock innehålla datatyper som inte kan ingå i ett grupperat kolumnlagringsindex (till exempel VARBINARY(MAX)), i vilket fall ett klustrat index kan användas i stället.

Dricks

Mer information om indexering i dedikerade SQL-pooler i Azure Synapse Analytics finns i Index för dedikerade SQL-pooltabeller i Azure Synapse Analytics.

Distribution

Dedikerade SQL-pooler i Azure Synapse Analytics använder en MPP-arkitektur (massively parallel processing), i motsats till den symmetriska arkitekturen för multiprocessing (SMP) som används i de flesta OLTP-databassystem. I ett MPP-system distribueras data i en tabell för bearbetning över en pool med noder. Synapse Analytics stöder följande typer av distribution:

Hash: Ett deterministiskt hashvärde beräknas för den angivna kolumnen och används för att tilldela raden till en beräkningsnod.
Resursallokering: Rader fördelas jämnt över alla beräkningsnoder.
Replikerad: En kopia av tabellen lagras på varje beräkningsnod.

Tabelltypen avgör ofta vilket alternativ som ska väljas för att distribuera tabellen.

Tabelltyp	Rekommenderat distributionsalternativ
Dimension	Använd replikerad distribution för mindre tabeller för att undvika datablandning vid anslutning till distribuerade faktatabeller. Om tabellerna är för stora för att lagras på varje beräkningsnod använder du hash-distribution.
Fakta	Använd hash-distribution med klustrade kolumnlagringsindex för att distribuera faktatabeller mellan beräkningsnoder.
Mellanlagring	Använd resursallokeringsdistribution för mellanlagringstabeller för att fördela data jämnt mellan beräkningsnoder.

Dricks

Mer information om distributionsstrategier för tabeller i Azure Synapse Analytics finns i Vägledning för att utforma distribuerade tabeller med dedikerad SQL-pool i Azure Synapse Analytics.

Skapa dimensionstabeller

När du skapar en dimensionstabell kontrollerar du att tabelldefinitionen innehåller surrogatnycklar och alternativa nycklar samt kolumner för attributen för den dimension som du vill använda för att gruppera sammansättningar. Det är ofta enklast att använda en IDENTITY kolumn för att generera en inkrementell surrogatnyckel automatiskt (annars måste du generera unika nycklar varje gång du läser in data). I följande exempel visas en CREATE TABLE instruktion för en hypotetisk DimCustomer-dimensionstabell .

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

Kommentar

Om du vill kan du skapa ett specifikt schema som ett namnområde för dina tabeller. I det här exemplet används standardschemat för dbo .

Om du tänker använda ett snowflake-schema där dimensionstabeller är relaterade till varandra bör du inkludera nyckeln för den överordnade dimensionen i definitionen av den underordnade dimensionstabellen. Följande SQL-kod kan till exempel användas för att flytta den geografiska adressinformationen från tabellen DimCustomer till en separat DimGeography-dimensionstabell :

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

Tidsdimensionstabeller

De flesta informationslager innehåller en tidsdimensionstabell som gör att du kan aggregera data efter flera hierarkiska nivåer av tidsintervall. I följande exempel skapas till exempel en DimDate-tabell med attribut som relaterar till specifika datum.

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

Dricks

Ett vanligt mönster när du skapar en dimensionstabell för datum är att använda det numeriska datumet i DDMMYYYYY- eller YYYYMMDD-format som en heltals surrogatnyckel och datumet som en DATE eller DATETIME datatyp som alternativ nyckel.

Skapa faktatabeller

Faktatabeller innehåller nycklarna för varje dimension som de är relaterade till och attribut och numeriska mått för specifika händelser eller observationer som du vill analysera.

I följande kodexempel skapas en hypotetisk faktatabell med namnet FactSales som är relaterad till flera dimensioner via nyckelkolumner (datum, kund, produkt och butik)

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

Skapa mellanlagringstabeller

Mellanlagringstabeller används som tillfällig lagring för data när de läses in i informationslagret. Ett typiskt mönster är att strukturera tabellen så att den blir så effektiv som möjligt att mata in data från den externa källan (ofta filer i en datasjö) i relationsdatabasen och sedan använda SQL-instruktioner för att läsa in data från mellanlagringstabellerna i dimensions- och faktatabellerna.

I följande kodexempel skapas en mellanlagringstabell för produktdata som slutligen läses in i en dimensionstabell:

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

Använda externa tabeller

I vissa fall kan det vara effektivare att skapa externa tabeller som refererar till filplatsen om de data som ska läsas in finns i filer med en lämplig struktur. På så sätt kan data läsas direkt från källfilerna i stället för att läsas in i relationsarkivet. I följande exempel visas hur du skapar en extern tabell som refererar till filer i den datasjö som är associerad med Synapse-arbetsytan:


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

Kommentar

Mer information om hur du använder externa tabeller finns i Använda externa tabeller med Synapse SQL i dokumentationen för Azure Synapse Analytics.

Skapa informationslagertabeller

Skapa en dedikerad SQL-pool

Överväganden för att skapa tabeller

Begränsningar för dataintegritet

Index

Distribution

Skapa dimensionstabeller

Tidsdimensionstabeller

Skapa faktatabeller

Skapa mellanlagringstabeller

Använda externa tabeller

Feedback