Útmutató elosztott táblák tervezéséhez dedikált SQL-készlet használatával az Azure Synapse Analyticsben

  • Cikk

Ez a cikk a kivonatolt és ciklikus időszeleteléses elosztott táblák dedikált SQL-készletekben való tervezésére vonatkozó javaslatokat tartalmaz.

Ez a cikk feltételezi, hogy ismeri a dedikált SQL-készlet adatelosztási és adatáthelyezési fogalmait. További információ: Azure Synapse Analytics-architektúra.

Mi az az elosztott tábla?

Az elosztott táblák egyetlen táblaként jelennek meg, de a sorok valójában 60 eloszlásban vannak tárolva. A sorok kivonat- vagy ciklikus időszeleteléses algoritmussal vannak elosztva.

A kivonat-elosztás javítja a nagy ténytáblák lekérdezési teljesítményét, és ez a cikk középpontjában áll. A ciklikus időszeleteléses eloszlás hasznos a betöltési sebesség javításához. Ezek a kialakítási lehetőségek jelentős hatással vannak a lekérdezési és betöltési teljesítmény javítására.

Egy másik táblatárolási lehetőség egy kis tábla replikálása az összes számítási csomóponton. További információ: Tervezési útmutató replikált táblákhoz. A három lehetőség közül gyorsan választhat: Elosztott táblák a táblák áttekintésében.

A táblatervezés részeként a lehető legnagyobb mértékben ismerje meg az adatokat és az adatok lekérdezésének módját.  Vegyük például az alábbi kérdéseket:

  • Mekkora az asztal?
  • Milyen gyakran frissül a tábla?
  • Vannak tény- és dimenziótáblák egy dedikált SQL-készletben?

Elosztott kivonat

Egy kivonatelosztott tábla egy determinisztikus kivonatfüggvénnyel osztja el a tábla sorait a számítási csomópontok között, hogy minden sort egy-egy elosztáshoz rendeljen.

Elosztott tábla

Mivel az azonos értékek mindig ugyanazt az eloszlást kivonatként használják, az SQL Analytics beépített ismeretekkel rendelkezik a sorhelyekről. A dedikált SQL-készletben ezt a tudást arra használjuk, hogy minimalizáljuk a lekérdezések során történő adatmozgást, ami javítja a lekérdezési teljesítményt.

A kivonatelosztott táblák jól működnek a csillagséma nagy ténytábláihoz. Nagyon sok sort tartalmazhatnak, és továbbra is magas teljesítményt érhetnek el. Vannak olyan tervezési szempontok, amelyek segítenek abban, hogy az elosztott rendszer által nyújtott teljesítmény elérhető legyen. A megfelelő terjesztési oszlop vagy oszlopok kiválasztása az egyik ilyen szempont, amelyet ebben a cikkben ismertetünk.

Érdemes lehet kivonatelosztott táblát használni, ha:

  • A lemezen lévő tábla mérete meghaladja a 2 GB-ot.
  • A táblázat gyakori beszúrási, frissítési és törlési műveleteket tartalmaz.

Ciklikus időszeletelés elosztott

A ciklikus időszeleteléses elosztott tábla egyenletesen osztja el a táblázat sorait az összes eloszlásban. A sorok eloszlásokhoz való hozzárendelése véletlenszerű. A kivonatelosztott tábláktól eltérően az egyenlő értékeket tartalmazó sorok nem garantáltan ugyanahhoz az eloszláshoz lesznek hozzárendelve.

Ennek eredményeképpen a rendszernek néha meg kell hívnia egy adatáthelyezési műveletet, hogy jobban rendszerezze az adatokat, mielőtt feloldhat egy lekérdezést. Ez a további lépés lelassíthatja a lekérdezéseket. Például egy ciklikus időszeleteléses táblázathoz való csatlakozáshoz általában újra kell ciklikus időszeleteléses táblázatokat létrehozni, ami teljesítménybeli találat.

Fontolja meg a táblázat ciklikus időszeleteléses eloszlásának használatát a következő forgatókönyvekben:

  • Egyszerű kiindulási pontként való kezdéskor, mivel ez az alapértelmezett
  • Ha nincs egyértelmű csatlakozási kulcs
  • Ha nincs megfelelő jelölt oszlop a tábla kivonatelosztásához
  • Ha a tábla nem oszt meg közös illesztőkulcsot más táblákkal
  • Ha az illesztés kevésbé jelentős, mint a lekérdezés többi illesztése
  • Ha a tábla átmeneti átmeneti tábla

A Load New York taxicab data (New York-i taxicab-adatok betöltése ) című oktatóanyag egy példát mutat be az adatok ciklikus időszeleteléses előkészítési táblába való betöltésére.

Elosztási oszlop kiválasztása

A kivonatelosztott tábláknak van egy terjesztési oszlopa vagy oszlopkészlete, amely a kivonatkulcs. Az alábbi kód például létrehoz egy kivonatelosztott táblát, amelynek ProductKey a terjesztési oszlopa.

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

A kivonateloszlás több oszlopra is alkalmazható az alaptábla egyenletesebb eloszlása érdekében. A többoszlopos elosztással legfeljebb nyolc oszlopot választhat ki az eloszláshoz. Ez nemcsak csökkenti az adatok időbeli eltérését, hanem javítja a lekérdezési teljesítményt is. Például:

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

Megjegyzés

A Azure Synapse Analyticsben a többoszlopos elosztás engedélyezéséhez módosítsa az adatbázis kompatibilitási szintjét erre a parancsra50. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Az adatbázis-kompatibilitási szint beállításáról további információt az ALTER DATABASE SCOPED CONFIGURATION (ADATBÁZIS-HATÓKÖRÖN BELÜLI KONFIGURÁCIÓ MÓDOSÍTÁSA) című témakörben talál. A többoszlopos disztribúciókkal kapcsolatos további információkért lásd: MATERIALIZÁLT NÉZET LÉTREHOZÁSA, CREATE TABLE vagy CREATE TABLE AS SELECT.

A terjesztési oszlop(ok)ban tárolt adatok frissíthetők. Frissítések a terjesztési oszlop(ok) adataihoz adateloszlási művelet vezethet.

A terjesztési oszlop(ok) kiválasztása fontos tervezési döntés, mivel a kivonatoszlop(ok) értékei határozzák meg a sorok eloszlását. A legjobb választás több tényezőtől függ, és általában kompromisszumokkal jár. Miután kiválasztott egy terjesztési oszlopot vagy oszlopkészletet, nem módosíthatja azt. Ha első alkalommal nem a legjobb oszlop(oka)t választotta ki, a CREATE TABLE AS SELECT (CTAS) paranccsal újra létrehozhatja a táblát a kívánt terjesztési kivonatkulccsal.

Válasszon ki egy egyenletesen elosztott adatokat tartalmazó terjesztési oszlopot

A legjobb teljesítmény érdekében az összes eloszlásnak körülbelül ugyanannyi sorból kell rendelkeznie. Ha egy vagy több disztribúcióhoz aránytalan számú sor tartozik, egyes disztribúciók befejezik a párhuzamos lekérdezések részét mások előtt. Mivel a lekérdezés csak akkor fejeződik be, ha az összes disztribúció feldolgozása befejeződött, minden lekérdezés csak olyan gyors, mint a leglassabb eloszlás.

  • Az adateltérés azt jelenti, hogy az adatok nem egyenletesen oszlanak el a disztribúciók között
  • A ferdeség feldolgozása azt jelenti, hogy egyes disztribúciók hosszabb időt vesznek igénybe, mint mások párhuzamos lekérdezések futtatásakor. Ez akkor fordulhat elő, ha az adatok ferdeek.

A párhuzamos feldolgozás kiegyensúlyozásához válasszon ki egy olyan terjesztési oszlopot vagy oszlopkészletet, amely:

  • Számos egyedi értékkel rendelkezik. A terjesztési oszlop(ok) ismétlődő értékekkel rendelkezhetnek. Az azonos értékkel rendelkező összes sor ugyanahhoz az eloszláshoz van rendelve. Mivel 60 eloszlás létezik, egyes disztribúciók 1 egyedi értékkel rendelkezhetnek > , míg mások nulla értékkel végződhetnek.
  • Nincsenek NULL-jei, vagy csak néhány NULL-juk van. Szélsőséges példaként, ha a terjesztési oszlop(ok) összes értéke NULL, az összes sor ugyanahhoz az eloszláshoz lesz rendelve. Ennek eredményeképpen a lekérdezések feldolgozása egy disztribúcióra van elvarrva, és nem jár a párhuzamos feldolgozás előnyeivel.
  • Nem dátumoszlop. Ugyanannak a dátumnak az összes adata ugyanabba az eloszlásba kerül, vagy dátum szerint csoportosítja a rekordokat. Ha több felhasználó is ugyanahhoz a dátumhoz (például a mai dátumhoz) szűr, akkor a 60 disztribúcióból csak 1 hajtja végre az összes feldolgozási feladatot.

Válasszon ki egy olyan terjesztési oszlopot, amely minimalizálja az adatáthelyezési lehetőségeket

A megfelelő lekérdezési eredmény lekéréséhez előfordulhat, hogy a lekérdezések adatokat helyeznek át az egyik számítási csomópontról a másikra. Az adatáthelyezés általában akkor fordul elő, ha a lekérdezések összekapcsolásokkal és összesítésekkel rendelkeznek az elosztott táblákon. A dedikált SQL-készlet teljesítményének optimalizálásához az egyik legfontosabb stratégia az, ha olyan terjesztési oszlopot vagy oszlopkészletet választ, amely segít minimalizálni az adatáthelyezést.

Az adatáthelyezési méret minimalizálása érdekében válasszon ki egy terjesztési oszlopot vagy oszlopkészletet, amely:

  • A , GROUP BY, DISTINCT, OVERés HAVING záradékokban használatosJOIN. Ha két nagy ténytábla gyakori illesztésekkel rendelkezik, a lekérdezési teljesítmény javul, ha mindkét táblát az egyik illesztési oszlopban osztja el. Ha nem használ táblát illesztésekben, érdemes lehet a táblát egy olyan oszlop- vagy oszlopkészleten terjeszteni, amely gyakran szerepel a GROUP BY záradékban.
  • A záradékokban WHEREnem használatos. Ha egy lekérdezés záradéka WHERE és a tábla terjesztési oszlopai ugyanazon az oszlopon találhatók, a lekérdezés nagy adateltérésbe ütközhet, ami a feldolgozási terhelés csak néhány disztribúcióra esik. Ez hatással van a lekérdezési teljesítményre, ideális esetben számos disztribúció osztozik a feldolgozási terhelésen.
  • Nem dátumoszlop. WHERE záradékok gyakran dátum szerint szűrnek. Ha ez történik, az összes feldolgozás csak néhány, a lekérdezési teljesítményt befolyásoló disztribúción futhat. Ideális esetben számos disztribúció osztozik a feldolgozási terhelésen.

Miután megtervezett egy kivonatolt táblát, a következő lépés az adatok betöltése a táblába. A betöltéssel kapcsolatos útmutatásért lásd: A betöltés áttekintése.

Hogyan állapíthatja meg, hogy a disztribúció jó választás-e?

Miután betöltötte az adatokat egy kivonatelosztott táblába, ellenőrizze, hogy a sorok egyenletesen vannak-e elosztva a 60 eloszlásban. Az eloszlásonkénti sorok akár 10%-ig is változhatnak anélkül, hogy észrevehető hatással lenne a teljesítményre. A terjesztési oszlop(ok) kiértékeléséhez vegye figyelembe az alábbi témaköröket.

Annak megállapítása, hogy a tábla adateltérésben van-e

Az adateltérés ellenőrzésének gyors módja a DBCC PDW_SHOWSPACEUSED használata. Az alábbi SQL-kód a 60 eloszlásban tárolt táblasorok számát adja vissza. A kiegyensúlyozott teljesítmény érdekében az elosztott táblában lévő sorokat egyenletesen kell elosztani az összes eloszlásban.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Annak megállapításához, hogy mely táblákban van több mint 10%-os adateltérés:

  1. Hozza létre a Táblák áttekintési cikkben látható nézetetdbo.vTableSizes.
  2. Futtassa az alábbi lekérdezést:
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;

Lekérdezéstervek ellenőrzése adatáthelyezési célokra

A jó terjesztési oszlopkészlet lehetővé teszi az illesztések és az összesítések minimális adatáthelyezését. Ez hatással van az illesztések írási módjára. Ahhoz, hogy egy illesztés minimális adatáthelyezést kapjon két kivonatelosztott táblán, az egyik illesztési oszlopnak terjesztési oszlopban vagy oszlop(ok)ban kell lennie. Ha két hash-elosztott tábla csatlakozik egy azonos adattípusú terjesztési oszlophoz, az illesztés nem igényel adatáthelyezést. Az illesztések további oszlopokat is használhatnak adatáthelyezési folyamat nélkül.

Az adatáthelyezési folyamat elkerülése az illesztés során:

  • Az illesztésben részt vevő táblákat az illesztésben részt vevő oszlopok egyikén kell elosztani.
  • Az illesztési oszlopok adattípusainak mindkét tábla között meg kell egyeznie.
  • Az oszlopokat egyenlő operátorral kell összekapcsolni.
  • Előfordulhat, hogy az illesztés típusa nem .CROSS JOIN

Ha meg szeretné nézni, hogy a lekérdezések adatáthelyezést tapasztalnak-e, tekintse meg a lekérdezéstervet.

Terjesztési oszloptal kapcsolatos probléma megoldása

Nem szükséges az adateltérés minden esetét feloldani. Az adatok elosztása a megfelelő egyensúly megtalálásának kérdése az adateltérés és az adatáthelyezés minimalizálása között. Az adateltérés és az adatáthelyezési lehetőségek nem mindig minimalizálhatóak. Előfordulhat, hogy a minimális adatáthelyezési előny meghaladja az adateltérés hatását.

Annak eldöntéséhez, hogy meg kell-e oldania az adateltéréseket egy táblában, a lehető legnagyobb mértékben tisztában kell lennie a számítási feladat adatmennyiségével és lekérdezésével. A Lekérdezésfigyelés cikkben található lépéseket követve monitorozhatja a ferdeségnek a lekérdezési teljesítményre gyakorolt hatását. Pontosabban keresse meg, hogy mennyi ideig tart a nagy lekérdezések végrehajtása az egyes disztribúciókon.

Mivel egy meglévő táblában nem lehet módosítani a terjesztési oszlop(ok)t, az adateltérés feloldásának tipikus módja, ha újra létrehozza a táblát egy másik terjesztési oszlop(ok) használatával.

A tábla újbóli létrehozása új terjesztési oszlopkészlettel

Ez a példa a CREATE TABLE AS SELECT függvényt használja egy másik kivonatelosztó oszlopot vagy oszlopot tartalmazó tábla újbóli létrehozásához.

Először használja CREATE TABLE AS SELECT az új táblát (CTAS) az új kulccsal. Ezután hozza létre újra a statisztikákat, és végül cserélje fel a táblákat az elnevezésük újbóli elnevezésével.

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

Következő lépések

Elosztott tábla létrehozásához használja az alábbi utasítások egyikét: