Táblák tervezése Synapse SQL-készlet használatával

Ez a cikk a dedikált SQL-készlettel és kiszolgáló nélküli SQL-készlettel rendelkező táblák azure Synapse Analyticsben történő tervezésének legfontosabb fogalmait ismerteti.

• A kiszolgáló nélküli SQL-készlet egy lekérdezési szolgáltatás, amely a data lake-ben lévő adatokon működik. Nincs helyi tárolója az adatbetöltéshez.
• A dedikált SQL-készlet a Synapse SQL használatakor kiosztott elemzési erőforrások gyűjteménye. A dedikált SQL-készlet méretét Adattárolás Egységek (DWU) határozzák meg.

A következő témakörök a dedikált SQL-készlet és a kiszolgáló nélküli SQL-készlet szempontjából relevánsak:

Téma	Dedikált SQL-készlet	Kiszolgáló nélküli SQL-készlet
Táblakategória	Igen	Nem
Séma nevek	Igen	Igen
Táblanevek	Igen	Nem
Táblattartósság	Igen	Nem
Reguláris táblázat	Igen	Nem
Ideiglenes tábla	Igen	Igen
Külső tábla	Igen	Igen
Adattípusok	Igen	Igen
Elosztott táblák	Igen	Nem
Körkörös táblák	Igen	Nem
Kivonatolt táblák	Igen	Nem
Replikált táblák	Igen	Nem
Táblák gyakori terjesztési módszerei	Igen	Nem
Partíciók	Igen	Igen
Oszlopcentrikus indexek	Igen	Nem
Statisztika	Igen	Igen
Elsődleges kulcs és egyedi kulcs	Igen	Nem
Parancsok táblák létrehozásához	Igen	Nem
Forrásadatok igazítása az adattárházhoz	Igen	Nem
Nem támogatott táblafunkciók	Igen	Nem
Táblaméret-lekérdezések	Igen	Nem

Táblakategória

A csillagséma tény- és dimenziótáblákba rendezi az adatokat. Egyes táblákat az integrációhoz vagy az adatok átmeneti tárolásához használunk, mielőtt egy tény- vagy dimenziótáblára lépnénk. Táblázat tervezésekor döntse el, hogy a tábla adatai tény-, dimenzió- vagy integrációs táblába tartoznak-e. Ez a döntés hatással van a megfelelő táblázat szerkezetére és eloszlására.

• A ténytáblák olyan mennyiségi adatokat tartalmaznak, amelyeket általában egy tranzakciós rendszerben hoznak létre, majd betöltik az adattárházba. Egy kiskereskedelmi vállalat például naponta hoz létre értékesítési tranzakciókat, majd betölti az adatokat egy adattárház ténytáblájába 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 átmeneti tárolásához. Az integrációs táblákat normál, külső vagy ideiglenes táblaként is létrehozhatja. Betöltheti például az adatokat egy átmeneti 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 nevek

A sémák jól csoportosítják a hasonló módon használt objektumokat. Az alábbi kód létrehoz egy wwi nevű, felhasználó által definiált sémát.

CREATE SCHEMA wwi;

Táblanevek

Ha több adatbázist migrál egy helyszíni megoldásból dedikált SQL-készletbe, az ajánlott eljárás az, hogy az összes tény-, dimenzió- és integrációs táblát egy SQL-készletsémába migrálja. Az összes táblát például a WideWorldImportersDW mintaadatraktárban tárolhatja egy wwi nevű sémában.

Ha meg szeretné jeleníteni a dedikált SQL-készlet tábláinak rendszerezését, használhatja a fact, dim és int elemeket táblanevek előtagjaként. Az alábbi táblázat a WideWorldImportersDW séma- és táblanevét mutatja be.

WideWorldImportersDW tábla	Táblatípus	Dedikált SQL-készlet
Város	Dimenzió	wwi. DimCity
Rendelés	Tény	wwi. FactOrder

Táblamegőrzés

A táblák véglegesen az Azure Storage-ban, ideiglenesen az Azure Storage-ban vagy az adattárházon kívüli adattárban tárolják az adatokat.

Reguláris táblázat

Egy normál tábla az Azure Storage-ban tárolja az adatokat az adattárház részeként. A tábla és az adatok megmaradnak, függetlenül attól, hogy egy munkamenet meg van-e nyitva. 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 idejére létezik. Ideiglenes táblával megakadályozhatja, hogy más felhasználók is láthassák az ideiglenes eredményeket. Az ideiglenes táblák használata szintén csökkenti a törlés szükségességét. Az ideiglenes táblák helyi tárolót használnak, és dedikált SQL-készletekben gyorsabb teljesítményt nyújtanak.

A kiszolgáló nélküli SQL-készlet támogatja az ideiglenes táblákat, de használatuk korlátozott, mivel egy ideiglenes táblából választhat, de nem csatlakoztathatja a tárolóban lévő fájlokkal.

További információ: Ideiglenes táblák.

Külső tábla

A külső táblák az Azure Storage-blobban vagy az Azure Data Lake Storage-ban található adatokra mutatnak.

Külső táblákból adatokat importálhat dedikált SQL-készletekbe a CREATE TABLE AS SELECT (CTAS) utasítással. A betöltési oktatóanyagot a New York Taxicab-adathalmaz betöltése című témakörben talál.

Kiszolgáló nélküli SQL-készlet esetén a CREATE EXTERNAL TABLE AS SELECT (CETAS) használatával mentheti a lekérdezés eredményét egy külső táblába az Azure Storage-ban.

Adattípusok

A dedikált SQL-készlet támogatja a leggyakrabban használt adattípusokat. A támogatott adattípusok listáját a CREATE TABLE referencia adattípusában találja. Az adattípusok használatáról további információt a Synapse SQL Táblázat adattípusai című témakörben talál.

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 disztribúciók között. A dedikált SQL-készlet három módszert támogat az adatok elosztásához:

• Körkörös megosztású táblák (alapértelmezett)
• Hash alapján elosztott táblák
• Replikált táblák

Körkörös táblák

A körkörös táblázat egyenletesen osztja el a táblázat sorait az összes eloszlás között. A sorok véletlenszerűen vannak elosztva. Az adatok ciklikus időszeleteléses táblába való betöltése gyors, de a lekérdezések több adatáthelyezést igényelhetnek, mint a többi terjesztési módszer.

További információ: Tervezési útmutató elosztott táblákhoz.

Hash-alapú elosztott táblák

A kivonatelosztott táblák a terjesztési oszlopban lévő érték alapján osztják el a sorokat. A kivonatelosztott táblákat úgy tervezték, hogy nagy teljesítményű lekérdezéseket érjenek el nagy táblákon. 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ák minden számítási csomóponton elérhetők a tábla teljes másolatával. A lekérdezések gyorsan futnak a replikált táblákon, mert a replikált táblák illesztése nem igényel adatáthelyezést. A replikációhoz azonban extra tárhelyre van szükség, és nagy táblák esetében nem praktikus.

További információ: Tervezési útmutató replikált táblákhoz.

Táblák gyakori terjesztési módszerei

A táblakategória gyakran meghatározza a táblaeloszlás optimális beállítását.

Táblakategória	Ajánlott terjesztési lehetőség
Tény	Hash-distribúció használata oszlopcentrikus fürtindexekkel. A teljesítmény akkor javul, ha két kivonattábla csatlakozik ugyanazon a terjesztési oszlopon.
Dimenzió	Használjon replikált kisebb táblákat. Ha a táblák túl nagyok ahhoz, hogy az egyes számítási csomópontokon tárolhatók legyenek, használja a hash-elosztott megoldást.
Előkészítés	Az előkészítési táblázathoz használjon körkörös elosztást. A CTAS segítségével a terhelés gyors. Miután az adatok az előkészítési táblában találhatók, az adatok éles táblákba való áthelyezésére használható INSERT...SELECT .

Partíciók

Dedikált SQL-készletekben 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 partíciók eltávolításával javíthatja a lekérdezési teljesítményt, ami a 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 a dedikált SQL-készlet adatai 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ó.

Tipp.

Amikor nem üres táblapartíciókra vált, fontolja meg az TRUNCATE_TARGET opció használatát az ALTER TABLE utasításban, ha a meglévő adatokat csonkolni szeretné.

Az alábbi kód egy SalesFact-partícióra váltja az átalakított napi adatokat, és felülírja a meglévő adatokat.

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

A kiszolgáló nélküli SQL-készletben korlátozhatja a lekérdezés által beolvasott fájlokat vagy mappákat (partíciókat). Az elérési út szerinti particionálást a filepath tárfájlokfileinfoleírt függvények és függvények támogatják. Az alábbi példa egy 2017-es évre vonatkozó adatokat tartalmazó mappát olvas be:

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

Oszloptárolós 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ési és lekérdezési teljesítményt biztosít. A fürtözött oszlop-adattárházas index általában a legjobb választás, de bizonyos esetekben a fürtözött index vagy a halom lehet a megfelelő tárolási struktúra.

Tipp.

A halomtábla különösen hasznos lehet á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át az oszlopcentrikus indexek újdonságai című témakörben találja. Az oszlopcentrikus index teljesítményének javításához tekintse meg az oszlopcentrikus indexek sorcsoportminőségének maximalizálása című témakört.

Statisztika

A lekérdezésoptimalizáló oszlopszintű statisztikákat használ, amikor létrehozza a lekérdezés végrehajtásához szükséges 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ák rendelkezzenek. A Synapse SQL támogatja a statisztikák automatikus létrehozását.

A statisztikai frissítés nem történik meg automatikusan. Jelentős számú sor hozzáadása vagy módosítása után frissítheti a statisztikákat. Például a terhelés után frissítse a statisztikákat. További információ: Statisztika a Synapse SQL-ben.

Elsődleges kulcs és egyedi kulcs

Csak akkor támogatott a dedikált SQL-készlet, ha mind a PRIMARY KEY, mind a NONCLUSTERED és NOT ENFORCED használva van. UNIQUE a korlátozás csak akkor támogatott, ha NOT ENFORCED használatban van. További információ: Elsődleges kulcs, idegen kulcs és egyedi kulcs dedikált SQL-készlet használatával.

Parancsok táblák létrehozásához

Dedikált SQL-készlet esetén létrehozhat egy táblát új üres táblaként. Emellett létrehozhat és feltölthet egy táblázatot egy kijelölési utasítás eredményeivel. A táblázat létrehozásához a következő T-SQL-parancsok tartoznak.

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 megadá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 Storage-ban történik.
CREATE TABLE AS SELECT	Létrehoz egy új táblát egy SELECT utasítás eredményével. 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 select utasítással	Új külső táblát hoz létre egy kiválasztási utasítás eredményeinek külső helyre való exportálásával. A hely az Azure Blob Storage vagy az Azure Data Lake Storage.

Forrásadatok igazítása az adattárházhoz

A dedikált SQL-készlettáblák feltöltése egy másik adatforrásból származó adatok betöltésével lehetséges. A sikeres terhelés eléréséhez a forrásadatok oszlopainak számának és adattípusainak összhangban kell lenniük az adattárházban lévő tábladefinícióval.

Feljegyzés

Az adatok igazítása lehet a táblák tervezésének legnehezebb része.

Ha az adatok több adattárból származnak, akkor az adatokat az adattárházba is átküldheti, és egy integrációs táblában tárolhatja. Miután az adatok az integrációs táblában szerepelnek, a dedikált SQL-készlet erejével implementálhatja az átalakítási műveleteket. Az adatok előkészítése után beillesztheti őket az éles táblákba.

Nem támogatott táblafunkciók

A dedikált SQL-készlet számos, de nem az összes táblafunkciót támogatja, amelyeket más adatbázisok kínálnak. 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
• Következés
• Takarékos 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

A dedikált SQL-készletben a táblák által a 60 eloszlásban felhasznált lemezterület és sorok azonosításának egyik egyszerű módja a DBCC PDW_SHOWSPACEUSED használata.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Ne feledje, hogy a DBCC-parancsok használata 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 a következő 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]
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. A táblaterület összegzése lehetővé teszi, hogy lássa, mely táblák a legnagyobb táblák. Azt is láthatja, hogy körkörös, replikált vagy hash-alapú elosztású. 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
;

Kapcsolódó tartalom

Miután létrehozott egy táblát az adattárházhoz, a következő lépés az adatok betöltése a táblába.

• Oktatóanyag: Adatok betöltése AZ SQL-készletbe