Táblák tervezése dedikált SQL-készlet használatával a Azure Synapse Analyticsben
Ez a cikk a táblák dedikált SQL-készletben való tervezésének legfontosabb bevezető fogalmait ismerteti.
Táblakategória meghatározása
A star séma tény- és dimenziótáblákba rendezi az adatokat. Egyes táblákat integrációs vagy előkészítési adatokhoz használnak, mielőtt azok egy tény- vagy dimenziótáblára kerülnek. Táblázat tervezésekor döntse el, hogy a táblaadatok tény-, dimenzió- vagy integrációs táblába tartoznak-e. Ez a döntés tájékoztatja a megfelelő táblastruktúrát és -eloszlást.
A ténytáblák olyan mennyiségi adatokat tartalmaznak, amelyek általában egy tranzakciós rendszerben jönnek létre, majd betöltődnek a dedikált SQL-készletbe. Egy kiskereskedelmi üzlet például naponta hoz létre értékesítési tranzakciókat, majd egy dedikált SQL-készlet ténytáblájába tölti be az adatokat elemzés céljából.
A dimenziótáblák olyan attribútumadatokat tartalmaznak, amelyek változhatnak, de általában ritkán változnak. Az ügyfél neve és címe például egy dimenziótáblában van tárolva, és csak akkor frissül, ha az ügyfél profilja megváltozik. A nagy ténytáblák méretének minimalizálásához az ügyfél nevének és címének nem kell egy ténytábla minden sorában lennie. Ehelyett a ténytábla és a dimenziótábla megoszthat egy ügyfél-azonosítót. A lekérdezések összekapcsolhatják a két táblát az ügyfél profiljának és tranzakcióinak társításához.
Az integrációs táblák helyet biztosítanak az adatok integrálásához vagy előkészítéséhez. Az integrációs táblát normál, külső vagy ideiglenes táblaként is létrehozhatja. Betöltheti például az adatokat egy előkészítési táblába, átalakításokat hajthat végre az előkészítésben lévő adatokon, majd beszúrhatja az adatokat egy éles táblába.
Séma- és táblanevek
A sémák jó módszer táblák csoportosítására, hasonló módon, együtt használva. Ha több adatbázist migrál egy helyszíni megoldásból egy dedikált SQL-készletbe, a legjobb, ha az összes tény-, dimenzió- és integrációs táblát egy dedikált SQL-készlet egyik sémájára migrálja.
Tárolhatja például az összes táblát a WideWorldImportersDW minta dedikált SQL-készletében egy nevű wwisémában. Az alábbi kód létrehoz egy felhasználó által definiált sémát , a következőt: wwi.
CREATE SCHEMA wwi;
A táblák dedikált SQL-készletben való szervezésének megjelenítéséhez használhatja a tény, a dim és a int előtagját a táblanevekhez. Az alábbi táblázat néhány sémát és táblanevet mutat be a következőhöz WideWorldImportersDW: .
| WideWorldImportersDW tábla | Táblatípus | Dedikált SQL-készlet |
|---|---|---|
| City | Dimenzió | wwi. DimCity |
| Sorrend | Fact | wwi. FactOrder |
Táblamegőrzés
A táblák véglegesen az Azure Storage-ban, ideiglenesen az Azure Storage-ban vagy egy dedikált SQL-készleten kívüli adattárban tárolják az adatokat.
Normál táblázat
Egy normál tábla a dedikált SQL-készlet részeként tárolja az adatokat az Azure Storage-ban. A tábla és az adatok attól függetlenül megmaradnak, hogy egy munkamenet nyitva van-e. Az alábbi példa egy normál táblát hoz létre két oszlopból.
CREATE TABLE MyTable (col1 int, col2 int );
Ideiglenes tábla
Ideiglenes tábla csak a munkamenet időtartamára létezik. Ideiglenes táblával megakadályozhatja, hogy más felhasználók is láthassák az ideiglenes eredményeket, valamint hogy csökkentse a törlés szükségességét.
Az ideiglenes táblák helyi tárolót használnak a gyors teljesítmény érdekében. További információ: Ideiglenes táblák.
Külső tábla
Egy külső tábla az Azure Storage-blobban vagy az Azure Data Lake Store-ban található adatokra mutat. Ha a CREATE TABLE AS SELECT utasítással használja, a külső tábla kiválasztása adatokat importál dedikált SQL-készletbe.
Ezért a külső táblák hasznosak az adatok betöltéséhez. Betöltési oktatóanyagért lásd: Adatok betöltése a PolyBase használatával az Azure Blob Storage-ból.
Adattípusok
A dedikált SQL-készlet a leggyakrabban használt adattípusokat támogatja. A támogatott adattípusok listáját a CREATE TABLE utasítás CREATE TABLE referencia adattípusai című témakörben találja. Az adattípusok használatával kapcsolatos útmutatásért lásd: Adattípusok.
Elosztott táblák
A dedikált SQL-készlet egyik alapvető funkciója, hogy hogyan tárolhatja és kezelheti a táblákat a különböző disztribúciókban. A dedikált SQL-készlet három módszert támogat az adatok elosztásához: ciklikus időszeletelés (alapértelmezett), kivonatolás és replikálás.
Kivonat alapján elosztott táblák
Az elosztott kivonattáblák a terjesztési oszlopban lévő érték alapján osztják el a sorokat. A kivonatok elosztott táblái nagy teljesítményű lekérdezések esetén nagy teljesítményt érhetnek el. A terjesztési oszlop kiválasztásakor több tényezőt is figyelembe kell venni.
További információ: Tervezési útmutató elosztott táblákhoz.
Replikált táblák
A replikált táblákban minden számítási csomóponton elérhető a tábla teljes példánya. A lekérdezések gyorsan futnak a replikált táblákon, mivel a replikált táblák illesztései nem igényelnek adatáthelyezési elemet. A replikáció azonban további tárterületet igényel, és nagy táblák esetében nem praktikus.
További információ: Tervezési útmutató replikált táblákhoz.
Ciklikus időszeleteléses táblák
A ciklikus időszeleteléses tábla egyenletesen osztja el a táblázat sorait az összes eloszlásban. A sorok véletlenszerűen vannak elosztva. Az adatok ciklikus időszeleteléses táblába való betöltése gyors. Ne feledje, hogy a lekérdezések több adatáthelyezési elemet igényelhetnek, mint a többi terjesztési módszer.
További információ: Tervezési útmutató elosztott táblákhoz.
Táblák gyakori terjesztési módszerei
A táblakategória gyakran meghatározza, hogy melyik lehetőséget válassza a tábla terjesztéséhez.
| Táblakategória | Ajánlott terjesztési lehetőség |
|---|---|
| Fact | Hash-disztribúció használata fürtözött oszlopcentrikus indexekkel. A teljesítmény javul, ha két kivonattáblát csatlakoztatnak ugyanazon a terjesztési oszlopon. |
| Dimenzió | Használjon replikált elemet kisebb táblákhoz. Ha a táblák túl nagyok az egyes számítási csomópontokon való tároláshoz, használjon kivonatelosztott elemet. |
| Előkészítés | Használja a ciklikus időszeletelést az előkészítési táblához. A CTAS-sel történő terhelés gyors. Miután az adatok bekerültek az előkészítési táblába, használja az INSERT... A SELECT lehetőséget választva áthelyezheti az adatokat az éles táblákba. |
Megjegyzés
A számítási feladatok alapján használandó legjobb táblázatterjesztési stratégiával kapcsolatos javaslatokért lásd: Azure Synapse SQL Distribution Advisor.
Táblapartíciók
A particionált táblák adattartományok szerint tárolják és hajtják végre a műveleteket a táblasorokon. Egy tábla például nap, hónap vagy év szerint particionálható. A lekérdezési teljesítményt a partíciók eltávolításával javíthatja, ami egy partíción belüli adatokra korlátozza a lekérdezések vizsgálatát. Az adatokat partícióváltással is karbantarthatja. Mivel az SQL-készletben lévő adatok már elosztottak, a túl sok partíció lelassíthatja a lekérdezési teljesítményt. További információ: Particionálási útmutató. Ha a partíció nem üres táblapartíciókra vált, fontolja meg az ALTER TABLE utasítás TRUNCATE_TARGET beállításának használatát, ha a meglévő adatokat csonkolni szeretné. Az alábbi kód átkapcsolja az átalakított napi adatokat a SalesFact-ba, és felülírja a meglévő adatokat.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Oszlopcentrikus indexek
Alapértelmezés szerint a dedikált SQL-készlet fürtözött oszlopcentrikus indexként tárol egy táblát. Az adattárolás ezen formája nagy méretű táblákon magas adattömörítést és lekérdezési teljesítményt eredményez.
A fürtözött oszlopcentrikus index általában a legjobb választás, de bizonyos esetekben a fürtözött index vagy a heap a megfelelő tárolási struktúra.
Tipp
A heaptáblák különösen hasznosak lehetnek átmeneti adatok betöltéséhez, például egy átmeneti táblához, amely végső táblává alakul át.
Az oszlopcentrikus funkciók listájáért lásd : Az oszlopcentrikus indexek újdonságai. Az oszlopcentrikus indexek teljesítményének javításához lásd: A sorcsoport minőségének maximalizálása az oszlopcentrikus indexek esetében.
Statisztika
A lekérdezésoptimalizáló oszlopszintű statisztikákat használ, amikor létrehozza a lekérdezés végrehajtására szolgáló tervet.
A lekérdezési teljesítmény javítása érdekében fontos, hogy az egyes oszlopokra, különösen a lekérdezési illesztésekben használt oszlopokra vonatkozó statisztikákat használjon. A statisztikák létrehozása automatikusan megtörténik.
A statisztikák frissítése nem történik meg automatikusan. Jelentős számú sor hozzáadása vagy módosítása után frissítse a statisztikákat. Például egy terhelés után frissítse a statisztikákat. További információt a Statisztika útmutatóban talál.
Elsődleges kulcs és egyedi kulcs
Az ELSŐDLEGES KULCS csak akkor támogatott, ha a NEMCLUSTERED és a NOT ENFORCED is használatos. Az EGYEDI kényszer csak a NEM KÉNYSZERÍTve lehetőséggel támogatott. Ellenőrizze a dedikált SQL-készlet táblakorlátozásait.
Táblák létrehozására szolgáló parancsok
Új üres táblaként létrehozhat egy táblát. Létrehozhat és feltölthet egy táblát egy select utasítás eredményeivel. A következő T-SQL-parancsok használhatók tábla létrehozásához.
| T-SQL-utasítás | Leírás |
|---|---|
| CREATE TABLE | Üres táblát hoz létre az összes táblaoszlop és beállítás definiálásával. |
| KÜLSŐ TÁBLA LÉTREHOZÁSA | Létrehoz egy külső táblát. A tábla definíciója dedikált SQL-készletben van tárolva. A táblaadatok tárolása az Azure Blob Storage-ban vagy az Azure Data Lake Store-ban történik. |
| CREATE TABLE AS SELECT | Új táblát tölt fel egy select utasítás eredményeivel. A táblaoszlopok és adattípusok a select utasítás eredményein alapulnak. Az adatok importálásához ez az utasítás kiválasztható egy külső táblából. |
| KÜLSŐ TÁBLA LÉTREHOZÁSA KIJELÖLÉSKÉNT | Új külső táblát hoz létre a select utasítás eredményeinek külső helyre való exportálásával. A hely az Azure Blob Storage vagy az Azure Data Lake Store. |
Forrásadatok igazítása dedikált SQL-készlethez
A dedikált SQL-készlettáblákat egy másik adatforrásból származó adatok betöltése tölti ki. A sikeres terhelés végrehajtásához a forrásadatok oszlopainak számának és adattípusainak igazodniuk kell a dedikált SQL-készlet tábladefinícióihoz. Az adatok igazítása a táblák tervezésének legnehezebb része lehet.
Ha az adatok több adattárból származnak, betölti az adatokat a dedikált SQL-készletbe, és egy integrációs táblában tárolja. Miután az adatok bekerültek az integrációs táblába, a dedikált SQL-készlet erejével átalakítási műveleteket hajthat végre. Az adatok előkészítése után beszúrhatja őket az éles táblákba.
Nem támogatott táblafunkciók
A dedikált SQL-készlet a többi adatbázis által kínált táblafunkciók közül számosat támogat, de nem mindet. Az alábbi lista néhány olyan táblafunkciót mutat be, amelyek nem támogatottak a dedikált SQL-készletben:
- Idegen kulcs, Táblakorlátozások ellenőrzése
- Számított oszlopok
- Indexelt nézetek
- Sequence
- Ritka oszlopok
- Helyettesítő kulcsok. Implementálás identitással.
- Szinonimák
- Eseményindítók
- Egyedi indexek
- Felhasználó által definiált típusok
Táblaméret-lekérdezések
Megjegyzés
Az ebben a szakaszban szereplő lekérdezésekből származó pontos számok érdekében győződjön meg arról, hogy az indexkarbantartás rendszeresen és nagy adatváltozások után történik.
A táblák által a 60 eloszlásban felhasznált tér és sorok azonosításának egyik egyszerű módja a DBCC PDW_SHOWSPACEUSED használata.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
A DBCC-parancsok használata azonban meglehetősen korlátozott lehet. A dinamikus felügyeleti nézetek (DMV-k) részletesebben jelennek meg, mint a DBCC-parancsok. Először hozza létre ezt a nézetet:
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
;
Táblázatterület összegzése
Ez a lekérdezés tábla szerint adja vissza a sorokat és a szóközöket. Így megtekintheti, hogy mely táblák a legnagyobb táblák, és hogy ciklikus időszeleteléses, replikált vagy kivonatolt táblákról van-e szó. Kivonatolt táblák esetén a lekérdezés a terjesztési oszlopot jeleníti meg.
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
;
Táblatér eloszlástípus szerint
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
;
Táblatér indextípus szerint
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
;
Terjesztési terület összegzése
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
;
Következő lépések
A dedikált SQL-készlet tábláinak létrehozása után a következő lépés az adatok betöltése a táblába. A betöltési oktatóanyagért lásd: Adatok betöltése dedikált SQL-készletbe, és tekintse át a dedikált SQL-készlet adatbetöltési stratégiáit az Azure Synapse Analyticsben.