Vägledning för att utforma distribuerade tabeller med hjälp av en dedikerad SQL-pool i Azure Synapse Analytics
Den här artikeln innehåller rekommendationer för att utforma hash-distribuerade och resursallokerade distribuerade tabeller i dedikerade SQL-pooler.
Den här artikeln förutsätter att du är bekant med begreppen för datadistribution och dataflytt i en dedikerad SQL-pool. Mer information finns i Azure Synapse Analytics-arkitektur.
Vad är en distribuerad tabell?
En distribuerad tabell visas som en enda tabell, men raderna lagras faktiskt i 60 distributioner. Raderna distribueras med en hash- eller resursallokeringsalgoritm.
Hash-distribution förbättrar frågeprestanda för stora faktatabeller och står i fokus för den här artikeln. Resursallokeringsdistribution är användbart för att förbättra inläsningshastigheten. De här designvalen har en betydande inverkan på att förbättra fråge- och inläsningsprestanda.
Ett annat alternativ för tabellagring är att replikera en liten tabell över alla beräkningsnoder. Mer information finns i Designvägledning för replikerade tabeller. Information om hur du snabbt väljer bland de tre alternativen finns i Distribuerade tabeller i tabellöversikten.
Som en del av tabelldesignen kan du förstå så mycket som möjligt om dina data och hur data efterfrågas. Tänk dig till exempel följande frågor:
- Hur stor är tabellen?
- Hur ofta uppdateras tabellen?
- Har jag fakta- och dimensionstabeller i en dedikerad SQL-pool?
Hash-distribuerad
En hash-distribuerad tabell distribuerar tabellrader över beräkningsnoderna med hjälp av en deterministisk hash-funktion för att tilldela varje rad till en distribution.
Eftersom identiska värden alltid hashas till samma distribution har SQL Analytics inbyggd kunskap om radplatserna. I den dedikerade SQL-poolen används den här kunskapen för att minimera dataflytt under frågor, vilket förbättrar frågeprestanda.
Hash-distribuerade tabeller fungerar bra för stora faktatabeller i ett star-schema. De kan ha ett mycket stort antal rader och ändå uppnå höga prestanda. Det finns några designöverväganden som hjälper dig att få den prestanda som det distribuerade systemet är utformat för att tillhandahålla. Att välja en bra distributionskolumn eller -kolumner är ett sådant övervägande som beskrivs i den här artikeln.
Överväg att använda en hash-distribuerad tabell när:
- Tabellstorleken på disken är mer än 2 GB.
- Tabellen innehåller ofta åtgärder för att infoga, uppdatera och ta bort.
Resursallokering distribuerad
En distribuerad tabell med resursallokering distribuerar tabellrader jämnt över alla distributioner. Tilldelningen av rader till distributioner är slumpmässig. Till skillnad från hash-distribuerade tabeller är det inte säkert att rader med lika värden tilldelas till samma distribution.
Därför behöver systemet ibland anropa en dataförflyttningsåtgärd för att organisera dina data bättre innan de kan lösa en fråga. Det här extra steget kan göra dina frågor långsammare. Om du till exempel ansluter till en resursallokeringstabell måste du vanligtvis omdelar raderna, vilket är en prestandaträff.
Överväg att använda resursallokeringsdistributionen för tabellen i följande scenarier:
- När du kommer igång som en enkel startpunkt eftersom det är standard
- Om det inte finns någon uppenbar kopplingsnyckel
- Om det inte finns någon bra kandidatkolumn för hash-distribution av tabellen
- Om tabellen inte delar en gemensam kopplingsnyckel med andra tabeller
- Om kopplingen är mindre viktig än andra kopplingar i frågan
- När tabellen är en tillfällig mellanlagringstabell
Självstudien Läsa in data från New York-taxicab är ett exempel på hur du läser in data i en mellanlagringstabell med resursallokering.
Välja en distributionskolumn
En hash-distribuerad tabell har en distributionskolumn eller en uppsättning kolumner som är hash-nyckeln. Följande kod skapar till exempel en hash-distribuerad tabell med ProductKey som distributionskolumn.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
);
Hash-distribution kan tillämpas på flera kolumner för en jämnare fördelning av bastabellen. Med distribution med flera kolumner kan du välja upp till åtta kolumner för distribution. Detta minskar inte bara dataskevhet över tid utan förbättrar även frågeprestanda. Exempel:
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey], [OrderDateKey], [CustomerKey] , [PromotionKey])
);
Anteckning
Distribution med flera kolumner i Azure Synapse Analytics kan aktiveras genom att ändra databasens kompatibilitetsnivå till 50 med det här kommandot.
ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;Mer information om hur du anger databasens kompatibilitetsnivå finns i ALTER DATABASE SCOPED CONFIGURATION (ÄNDRA DATABASOMFATTNINGSKONFIGURATION). Mer information om distributioner med flera kolumner finns i CREATE MATERIALIZED VIEW, CREATE TABLE eller CREATE TABLE AS SELECT.
Data som lagras i distributionskolumnerna kan uppdateras. Uppdateringar till data i distributionskolumner kan resultera i datablandningsåtgärd.
Att välja distributionskolumner är ett viktigt designbeslut eftersom värdena i hash-kolumnerna avgör hur raderna distribueras. Det bästa valet beror på flera faktorer och innebär vanligtvis kompromisser. När du har valt en distributionskolumn eller kolumnuppsättning kan du inte ändra den. Om du inte valde de bästa kolumnerna första gången kan du använda CREATE TABLE AS SELECT (CTAS) för att återskapa tabellen med önskad distributionshashnyckel.
Välj en distributionskolumn med data som distribueras jämnt
För bästa prestanda bör alla distributioner ha ungefär samma antal rader. När en eller flera distributioner har ett oproportionerligt antal rader slutför vissa distributioner sin del av en parallell fråga före andra. Eftersom frågan inte kan slutföras förrän alla distributioner har slutfört bearbetningen är varje fråga bara lika snabb som den långsammaste distributionen.
- Dataskevhet innebär att data inte är jämnt fördelade över distributionerna
- Bearbetning av snedställning innebär att vissa distributioner tar längre tid än andra när parallella frågor körs. Detta kan inträffa när data är skeva.
Om du vill balansera den parallella bearbetningen väljer du en distributionskolumn eller en uppsättning kolumner som:
- Har många unika värden. Distributionskolumnerna kan ha dubblettvärden. Alla rader med samma värde tilldelas till samma distribution. Eftersom det finns 60 distributioner kan vissa distributioner ha > 1 unika värden medan andra kan sluta med noll värden.
- Har inte NULL:er eller har bara några få NULL:er. I ett extremt exempel, om alla värden i distributionskolumnerna är NULL, tilldelas alla rader till samma distribution. Därför är frågebearbetning skev till en distribution och drar inte nytta av parallell bearbetning.
- Är inte en datumkolumn. Alla data för samma datum hamnar i samma distribution, eller kommer att gruppera poster efter datum. Om flera användare alla filtrerar på samma datum (till exempel dagens datum) utför endast 1 av de 60 distributionerna allt bearbetningsarbete.
Välj en distributionskolumn som minimerar dataflytten
För att få rätt frågeresultatfrågor kan du flytta data från en beräkningsnod till en annan. Dataflytt sker ofta när frågor har kopplingar och aggregeringar i distribuerade tabeller. Att välja en distributionskolumn eller kolumnuppsättning som hjälper till att minimera dataflytten är en av de viktigaste strategierna för att optimera prestanda för din dedikerade SQL-pool.
Om du vill minimera dataflytten väljer du en distributionskolumn eller en uppsättning kolumner som:
- Används i
JOINsatserna ,GROUP BY,OVERDISTINCT, ochHAVING. När två stora faktatabeller har frekventa kopplingar förbättras frågeprestanda när du distribuerar båda tabellerna på en av kopplingskolumnerna. När en tabell inte används i kopplingar bör du överväga att distribuera tabellen på en kolumn- eller kolumnuppsättning som ofta finns i -GROUP BYsatsen. - Används inte i
WHEREsatser. När en frågas -sats och tabellens distributionskolumner finns i samma kolumn kan frågan stöta på hög datasnedställningWHERE, vilket leder till att bearbetningsbelastningen bara faller på ett fåtal distributioner. Detta påverkar frågeprestanda, helst delar många distributioner bearbetningsbelastningen. - Är inte en datumkolumn.
WHEREsatser filtrerar ofta efter datum. När detta inträffar kan all bearbetning endast köras på ett fåtal distributioner som påverkar frågeprestanda. Vi rekommenderar att många distributioner delar bearbetningsbelastningen.
När du har skapat en hash-distribuerad tabell är nästa steg att läsa in data i tabellen. Läs in vägledning i Läs in översikt.
Så här ser du om distributionen är ett bra val
När data har lästs in i en hash-distribuerad tabell kontrollerar du hur jämnt raderna fördelas över de 60 distributionerna. Raderna per distribution kan variera upp till 10 % utan märkbar påverkan på prestanda. Överväg följande avsnitt för att utvärdera dina distributionskolumner.
Kontrollera om tabellen har dataförskjutning
Ett snabbt sätt att söka efter datasnedställning är att använda DBCC-PDW_SHOWSPACEUSED. Följande SQL-kod returnerar antalet tabellrader som lagras i var och en av de 60 distributionerna. För balanserade prestanda bör raderna i den distribuerade tabellen fördelas jämnt över alla distributioner.
-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Så här identifierar du vilka tabeller som har mer än 10 % dataförskjutning:
- Skapa vyn
dbo.vTableSizessom visas i översiktsartikeln Tabeller . - Kör följande fråga:
select *
from dbo.vTableSizes
where two_part_name in
(
select two_part_name
from dbo.vTableSizes
where row_count > 0
group by two_part_name
having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
)
order by two_part_name, row_count;
Kontrollera frågeplaner för dataflytt
Med en bra distributionskolumnuppsättning kan kopplingar och sammansättningar ha minimal dataförflyttning. Detta påverkar hur kopplingar ska skrivas. För att få minimal dataförflyttning för en koppling på två hash-distribuerade tabeller måste en av kopplingskolumnerna finnas i distributionskolumnen eller kolumnerna. När två hash-distribuerade tabeller kopplas till en distributionskolumn av samma datatyp kräver inte kopplingen dataflytt. Kopplingar kan använda ytterligare kolumner utan att medföra dataförflyttning.
Så här undviker du dataförflyttning under en koppling:
- Tabellerna som ingår i kopplingen måste vara hash-distribuerade på en av kolumnerna som deltar i kopplingen.
- Datatyperna för kopplingskolumnerna måste matcha mellan båda tabellerna.
- Kolumnerna måste vara kopplade till en lika med-operator.
- Kopplingstypen kanske inte är en
CROSS JOIN.
Om du vill se om frågor har dataflytt kan du titta på frågeplanen.
Lösa ett problem med distributionskolumner
Det är inte nödvändigt att lösa alla fall av dataförskjutning. Att distribuera data handlar om att hitta rätt balans mellan att minimera dataförskjutning och dataförflyttning. Det är inte alltid möjligt att minimera både dataförskjutning och dataförflyttning. Ibland kan fördelen med att ha minimal dataförflyttning uppväga effekten av att ha dataförskjutning.
För att avgöra om du ska lösa datasnedställning i en tabell bör du förstå så mycket som möjligt om datavolymerna och frågorna i din arbetsbelastning. Du kan använda stegen i artikeln Frågeövervakning för att övervaka effekten av snedställning på frågeprestanda. Mer specifikt letar du efter hur lång tid det tar för stora frågor att slutföra enskilda distributioner.
Eftersom du inte kan ändra distributionskolumnerna i en befintlig tabell är det vanliga sättet att lösa datasnedvridning att återskapa tabellen med olika distributionskolumner.
Återskapa tabellen med en ny distributionskolumnuppsättning
I det här exemplet används CREATE TABLE AS SELECT för att återskapa en tabell med en annan hashdistributionskolumn eller kolumn(ar).
CREATE TABLE AS SELECT Använd först (CTAS) den nya tabellen med den nya nyckeln. Återskapa sedan statistiken och växla sedan tabellerna genom att namnge dem igen.
CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH ( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([CustomerKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES ( 20000101, 20010101, 20020101, 20030101
, 20040101, 20050101, 20060101, 20070101
, 20080101, 20090101, 20100101, 20110101
, 20120101, 20130101, 20140101, 20150101
, 20160101, 20170101, 20180101, 20190101
, 20200101, 20210101, 20220101, 20230101
, 20240101, 20250101, 20260101, 20270101
, 20280101, 20290101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
OPTION (LABEL = 'CTAS : FactInternetSales_CustomerKey')
;
--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);
--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];
Nästa steg
Om du vill skapa en distribuerad tabell använder du någon av följande instruktioner: