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.
| WideWorldImportersDW-tabell | Tabelltyp | Dedikerad SQL-pool |
|---|---|---|
| City | Dimension | Wwi. DimCity |
| Beställning | Fakta | Wwi. FactOrder |
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.
| Tabellkategori | Rekommenderat distributionsalternativ |
|---|---|
| Fakta | Använd hash-distribution med grupperat kolumnlagringsindex. Prestanda förbättras när två hash-tabeller är kopplade till samma distributionskolumn. |
| Dimension | Använd replikerad för mindre tabeller. Om tabellerna är för stora för att lagras på varje beräkningsnod använder du hash-distribuerad. |
| Mellanlagring | Använd resursallokering för mellanlagringstabellen. Belastningen med CTAS går snabbt. När data finns i mellanlagringstabellen använder du INSERT... VÄLJ för att flytta data till produktionstabeller. |
Anteckning
Rekommendationer om den bästa tabelldistributionsstrategin som ska användas baserat på dina arbetsbelastningar finns i Azure Synapse SQL Distribution Advisor.
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.
Tips
En heap-tabell kan vara särskilt användbar för att läsa in tillfälliga data, till exempel en mellanlagringstabell som omvandlas till en sluttabell.
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.
| T-SQL-instruktion | Beskrivning |
|---|---|
| CREATE TABLE | Skapar en tom tabell genom att definiera alla tabellkolumner och alternativ. |
| SKAPA EXTERN TABELL | Skapar en extern tabell. Definitionen av tabellen lagras i en dedikerad SQL-pool. Tabelldata lagras i Azure Blob Storage eller Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Fyller i en ny tabell med resultatet av en select-instruktion. Tabellkolumnerna och datatyperna baseras på select-instruktionsresultaten. Om du vill importera data kan den här instruktionen välja från en extern tabell. |
| SKAPA EXTERN TABELL SOM SELECT | Skapar en ny extern tabell genom att exportera resultatet av en select-instruktion till en extern plats. Platsen är antingen Azure Blob Storage eller Azure Data Lake Store. |
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
Anteckning
För korrekta antal från frågor i det här avsnittet kontrollerar du att indexunderhåll sker regelbundet och efter stora dataändringar.
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.