Utforma tabeller med hjälp av en dedikerad SQL-pool i Azure Synapse Analytics

Den här artikeln innehåller viktiga introduktionskoncept för att utforma tabeller i en dedikerad SQL-pool.

Fastställa tabellkategori

Ett star schema organiserar data i fakta- och dimensionstabeller. Vissa tabeller används för integrering eller mellanlagring av data innan de flyttas till en fakta- eller dimensionstabell. När du utformar en tabell ska du bestämma om tabelldata ska tillhöra en fakta-, dimensions- eller integrationstabell. Detta beslut informerar lämplig tabellstruktur och fördelning.

• Faktatabeller innehåller kvantitativa data som vanligtvis genereras i ett transaktionssystem och sedan läses in i den dedikerade SQL-poolen. Ett detaljhandelsföretag genererar till exempel försäljningstransaktioner varje dag och läser sedan in data i en dedikerad faktatabell för SQL-pool för analys.

• Dimensionstabeller innehåller attributdata som kan ändras men vanligtvis ändras sällan. Till exempel lagras en kunds namn och adress i en dimensionstabell och uppdateras bara när kundens profil ändras. För att minimera storleken på en stor faktatabell behöver kundens namn och adress inte finnas i varje rad i en faktatabell. Faktatabellen och dimensionstabellen kan i stället dela ett kund-ID. En fråga kan koppla de två tabellerna för att associera en kunds profil och transaktioner.

• Integreringstabeller är en plats för integrering eller mellanlagring av data. Du kan skapa en integrationstabell som en vanlig tabell, en extern tabell eller en tillfällig tabell. Du kan till exempel läsa in data till en mellanlagringstabell, utföra transformeringar på data i mellanlagringen och sedan infoga data i en produktionstabell.

Schema- och tabellnamn

Scheman är ett bra sätt att gruppera tabeller, som används på liknande sätt, tillsammans. Om du migrerar flera databaser från en lokal lösning till en dedikerad SQL-pool fungerar det bäst att migrera alla fakta-, dimensions- och integrationstabeller till ett schema i en dedikerad SQL-pool.

Du kan till exempel lagra alla tabeller i den dedikerade SQL-poolen WideWorldImportersDW i ett schema med namnet wwi. Följande kod skapar ett användardefinierat schema med namnet wwi.

CREATE SCHEMA wwi;

Om du vill visa organisationen av tabellerna i en dedikerad SQL-pool kan du använda fakta, dim och int som prefix för tabellnamnen. I följande tabell visas några av schema- och tabellnamnen för WideWorldImportersDW.

Tabellpersistens

Tabeller lagrar data antingen permanent i Azure Storage, tillfälligt i Azure Storage eller i ett datalager utanför en dedikerad SQL-pool.

Vanlig tabell

En vanlig tabell lagrar data i Azure Storage som en del av en dedikerad SQL-pool. Tabellen och data bevaras oavsett om en session är öppen eller inte. I följande exempel skapas en vanlig tabell med två kolumner.

CREATE TABLE MyTable (col1 int, col2 int );  

Tillfällig tabell

Det finns bara en tillfällig tabell under hela sessionen. Du kan använda en tillfällig tabell för att förhindra att andra användare ser tillfälliga resultat och minska behovet av rensning.

Temporära tabeller använder lokal lagring för att erbjuda snabba prestanda. Mer information finns i Temporära tabeller.

Extern tabell

En extern tabell pekar på data som finns i Azure Storage-bloben eller Azure Data Lake Store. När den används med instruktionen CREATE TABLE AS SELECT importerar valet från en extern tabell data till en dedikerad SQL-pool.

Därför är externa tabeller användbara för inläsning av data. En självstudiekurs om inläsning finns i Använda PolyBase för att läsa in data från Azure Blob Storage.

Datatyper

Dedikerad SQL-pool stöder de vanligaste datatyperna. En lista över de datatyper som stöds finns i datatyper i CREATE TABLE-referensen i CREATE TABLE-instruktionen. Vägledning om hur du använder datatyper finns i Datatyper.

Distribuerade tabeller

En grundläggande funktion i en dedikerad SQL-pool är hur den kan lagra och arbeta med tabeller mellan distributioner. Dedikerad SQL-pool stöder tre metoder för att distribuera data: resursallokering (standard), hash och replikerad.

Hash-distribuerade tabeller

En hash-distribuerad tabell distribuerar rader baserat på värdet i distributionskolumnen. En hash-distribuerad tabell är utformad för att uppnå höga prestanda för frågor på stora tabeller. Det finns flera faktorer att tänka på när du väljer en distributionskolumn.

Mer information finns i Designvägledning för distribuerade tabeller.

Replikerade tabeller

En replikerad tabell har en fullständig kopia av tabellen som är tillgänglig på varje beräkningsnod. Frågor körs snabbt på replikerade tabeller eftersom kopplingar i replikerade tabeller inte kräver dataflytt. Replikering kräver dock extra lagringsutrymme och är inte praktiskt för stora tabeller.

Mer information finns i Designvägledning för replikerade tabeller.

Resursallokeringstabeller

En resursallokeringstabell distribuerar tabellrader jämnt över alla distributioner. Raderna distribueras slumpmässigt. Det går snabbt att läsa in data i en resursallokeringstabell. Tänk på att frågor kan kräva mer dataflytt än de andra distributionsmetoderna.

Mer information finns i Designvägledning för distribuerade tabeller.

Vanliga distributionsmetoder för tabeller

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

Tabellpartitioner

En partitionerad tabell lagrar och utför åtgärder på tabellraderna enligt dataintervall. En tabell kan till exempel partitioneras efter dag, månad eller år. Du kan förbättra frågeprestandan genom partitionseliminering, vilket begränsar en frågegenomsökning till data i en partition. Du kan också underhålla data via partitionsväxling. Eftersom data i SQL-poolen redan är distribuerade kan för många partitioner göra frågeprestanda långsammare. Mer information finns i Partitioneringsvägledning. När partitionen växlar till tabellpartitioner som inte är tomma bör du överväga att använda alternativet TRUNCATE_TARGET i ALTER TABLE-instruktionen om befintliga data ska trunkeras. Koden nedan växlar i omvandlade dagliga data till SalesFact som skriver över befintliga data.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);  

Columnstore-index

Som standard lagrar den dedikerade SQL-poolen en tabell som ett grupperat columnstore-index. Den här typen av datalagring ger hög datakomprimering och frågeprestanda i stora tabeller.

Det klustrade kolumnlagringsindexet är vanligtvis det bästa valet, men i vissa fall är ett grupperat index eller en heap lämplig lagringsstruktur.

En lista över columnstore-funktioner finns i Nyheter för columnstore-index. Information om hur du förbättrar prestanda för columnstore-index finns i Maximera radgruppskvaliteten för kolumnlagringsindex.

Statistik

Frågeoptimeraren använder statistik på kolumnnivå när den skapar planen för att köra en fråga.

För att förbättra frågeprestanda är det viktigt att ha statistik om enskilda kolumner, särskilt kolumner som används i frågekopplingar. Att skapa statistik sker automatiskt.

Uppdatering av statistik sker inte automatiskt. Uppdatera statistik när ett stort antal rader har lagts till eller ändrats. Du kan till exempel uppdatera statistik efter en inläsning. Mer information finns i Statistikvägledning.

Primärnyckel och unik nyckel

PRIMÄRNYCKEL stöds endast när både NONCLUSTERED och NOT ENFORCED används. UNIK begränsning stöds endast med NOT ENFORCED används. Kontrollera begränsningar för dedikerad SQL-pooltabell.

Kommandon för att skapa tabeller

Du kan skapa en tabell som en ny tom tabell. Du kan också skapa och fylla i en tabell med resultatet av en select-instruktion. Följande är T-SQL-kommandona för att skapa en tabell.

Justera källdata med dedikerad SQL-pool

Dedikerade SQL-pooltabeller fylls i genom att data läses in från en annan datakälla. För att kunna utföra en lyckad inläsning måste antalet och datatyperna för kolumnerna i källdata anpassas till tabelldefinitionen i den dedikerade SQL-poolen. Att få data att justera kan vara den svåraste delen av att utforma dina tabeller.

Om data kommer från flera datalager läser du in data i den dedikerade SQL-poolen och lagrar dem i en integrationstabell. När data finns i integrationstabellen kan du använda kraften i den dedikerade SQL-poolen för att utföra transformeringsåtgärder. När data har förberetts kan du infoga dem i produktionstabeller.

Tabellfunktioner som inte stöds

Den dedikerade SQL-poolen stöder många, men inte alla, tabellfunktioner som erbjuds av andra databaser. I följande lista visas några av tabellfunktionerna som inte stöds i en dedikerad SQL-pool:

• Sekundärnyckel, Kontrollera tabellbegränsningar
• Beräknade kolumner
• Indexerade vyer
• Sequence
• Glesa kolumner
• Surrogatnycklar. Implementera med identitet.
• Synonymer
• Utlösare
• Unika index
• Användardefinierade typer

Frågor om tabellstorlek

Ett enkelt sätt att identifiera utrymme och rader som används av en tabell i var och en av de 60 distributionerna är att använda DBCC-PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Det kan dock vara ganska begränsande att använda DBCC-kommandon. Dynamiska hanteringsvyer (DMV:er) visar mer information än DBCC-kommandon. Börja med att skapa den här vyn:

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
;

Sammanfattning av tabellutrymme

Den här frågan returnerar raderna och utrymmet efter tabell. Det gör att du kan se vilka tabeller som är dina största tabeller och om de är resursallokering, replikerade eller hash-distribuerade. För hash-distribuerade tabeller visar frågan distributionskolumnen.

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
;

Tabellutrymme efter distributionstyp

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
;

Tabellutrymme efter indextyp

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
;

Sammanfattning av distributionsutrymme

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
;

Nästa steg

När du har skapat tabellerna för din dedikerade SQL-pool är nästa steg att läsa in data i tabellen. En inläsningsguide finns i Läsa in data till en dedikerad SQL-pool och granska strategier för datainläsning för dedikerad SQL-pool i Azure Synapse Analytics.

• Azure Synapse SQL Distribution Advisor
• Dedikerad SQL-poolarkitektur (tidigare SQL DW) i Azure Synapse Analytics
• Lathund för dedikerad SQL-pool (tidigare SQL DW) i Azure Synapse Analytics