Sdílet prostřednictvím

Clustering dat v datovém skladu infrastruktury

platí pro:✅ Warehouse v Microsoft Fabric

Clustering dat je technika, která se používá k uspořádání a ukládání dat na základě podobnosti. Clustering dat zlepšuje výkon dotazů a snižuje náklady na přístup k výpočetním prostředkům a úložišti pro dotazy seskupením podobných záznamů.

Jak to funguje

Clustering dat funguje tak, že během příjmu dat ukládá řádky s podobnými hodnotami v sousedních umístěních v úložišti. Shlukování dat používá křivku vyplnění prostoru k uspořádání dat způsobem, který zachovává umístění v několika dimenzích, což znamená, že řádky s podobnými hodnotami napříč sloupci clusteringu se ukládají fyzicky blízko sebe. Tento přístup výrazně zlepšuje výkon dotazů tím, že provádí přeskočení souboru a snižuje počet naskenovaných souborů.

Na rozdíl od konvenčního lexikografického řazení používá clustering dat sofistikovaný algoritmus pro zpracování, při kterém jsou řádky s podobnými hodnotami sloupců blízko sebe, i když je tabulka seskupená podle několika sloupců. Díky tomu je clustering dat ideální pro dotazy na rozsah, filtry s vysokou kardinalitou a velké tabulky se zkosenými distribucemi, což vede k rychlejšímu čtení, snížení vstupně-výstupních operací a efektivnějšímu využití prostředků.

Tady je zjednodušený koncepční obrázek clusteringu dat:

Diagram znázorňující koncept clusteringu dat v datovém skladu

V tomto diagramu tabulka označená Source data zobrazuje řádky smíšené a zvýrazněné v různých barvách, aby představovaly seskupení u cíle. Uspořádaná tabulka je rozdělená do tří segmentů souborů, přičemž každý seskupuje řádky podle podobných barev a ukazuje, jak clustering uspořádá data do optimalizovaných segmentů úložiště na základě hodnot sloupců.

Metadata clusteringu dat jsou vložena do manifestu během příjmu dat, což modulu skladu umožňuje inteligentní rozhodování o tom, ke kterým souborům se má přistupovat během uživatelských dotazů. Tato metadata v kombinaci s ukládáním řádků s podobnými hodnotami zajišťují, že dotazy s predikáty filtru můžou přeskočit celé soubory a skupiny řádků, které spadají mimo obor predikátu. Například: Pokud dotaz cílí pouze na 10% dat tabulky, clustering zajistí, že se prohledávají jenom soubory obsahující data v rozsahu filtru, což snižuje spotřebu vstupně-výstupních operací a výpočetních prostředků. Větší tabulky více těží z clusteringu dat, protože výhody přeskakování souborů rostou s objemem dat.

Kdy použít shlukování dat

Při rozhodování, jestli může být clustering dat přínosný, prozkoumejte vzory dotazů a charakteristiky tabulek ve skladu. Clustering dat je nejúčinnější, když dotazy opakovaně filtrují konkrétní sloupce a pokud jsou podkladové tabulky velké a obsahují data kardinality střední až vysoké. Mezi běžné scénáře patří:

  • Opakované dotazy s WHERE filtry: Pokud úloha obsahuje časté dotazy filtrující konkrétní sloupce, clustering dat zajišťuje, že se během dotazů pro čtení prohledávají jenom relevantní soubory. To platí také v případě, že se filtry opakovaně používají v řídicích panelech, sestavách nebo plánovaných úlohách a odsadí se do modulu skladu jako příkazy SQL.
  • Větší tabulky: Clustering dat je nejúčinnější při použití u velkých tabulek, kde kontrola úplné datové sady je nákladná. Uspořádáním řádků pomocí clusteringu dat může modul skladu přeskočit celé soubory a skupiny řádků, které neodpovídají filtru dotazů, což může snížit využití vstupně-výstupních operací a výpočetních prostředků.
  • Sloupce se středně až vysokou kardinalitou: Sloupce s vyšší kardinalitou (například sloupce, které mají mnoho jedinečných hodnot, jako je ID nebo datum) mají větší výhodu ze shlukování dat, protože umožňují nástroji izolovat a seskupovat podobné hodnoty. To umožňuje efektivní přeskočení souborů, zejména u selektivních dotazů. Sloupce s nízkou kardinalitou (například pohlaví, oblast) mají své hodnoty rozložené mezi více souborů, a proto nabízejí omezené možnosti pro přeskočení souborů.
  • Selektivní dotazy s úzkým oborem: pokud dotazy obvykle cílí na malou podmnožinu dat a jsou kombinovány s filtrem WHERE, clustering dat zajišťuje, aby se načítaly pouze soubory obsahující příslušné řádky.

Clustering dat probíhá automaticky během příjmu dat bez ohledu na to, jak se řádky ingestovaly. Po ingestování dat k použití clusteringu dat nejsou potřeba žádné uživatelské operace.

Syntaxe CLUSTER BY

Clustering dat se definuje během vytváření tabulky pomocí CLUSTER BY klauzule. Syntaxe je následující:

Syntaxe CREATE TABLE (Transact-SQL):

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
 [ ,... n ] –- Column list
) WITH (CLUSTER BY [ ,... n ]);

Syntaxe CREATE TABLE AS SELECT (Transact-SQL):

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
) WITH (CLUSTER BY[ ,... n ])
AS <select_statement>;

Klauzule CLUSTER BY vyžaduje, aby byl pro clustering dat zadán alespoň jeden sloupec a maximálně čtyři sloupce.

Vytvoření tabulky, ve které se používá clustering SELECT INTO dat, není podporováno.

Podpora datových typů

Následující tabulka shrnuje typy sloupců, které lze použít v CLUSTER BY klauzuli:

Kategorie Datový typ Podpora clusteringu dat
Přesné numeriky bit Ne
Přesné numeriky bigint, int, smallint, decimal2, numeric Ano
Přibližné numeriky Float, Real Ano
Datum a čas date, datetime2, time Ano
Řetězce znaků1 znak Ano
Řetězce znaků1 varchar Ano
Obchodní typy varchar(max), varbinary(max) Ne
Binární řetězce varbinary, uniqueidentifer Ne

1 Pro typy řetězců (char/varchar) se při vytváření statistiky sloupců používají pouze prvních 32 znaků. V důsledku toho můžou mít sloupce s hodnotami, které obsahují dlouhé předpony, omezené výhody clusteringu dat.

2 Pro desetinné typy s přesností větší než 18 nejsou predikáty během provádění dotazu posunuty do úložiště. Pokud používáte desetinné typy se shlukováním dat, upřednostněte sloupce s menší přesností.

Sloupce s nepodporovanými datovými typy mohou stále existovat v tabulce, která používá clustering dat, ale nelze je použít s CLUSTER BY.

Osvědčené postupy s clusteringem dat

Clustering dat je efektivnější, když jsou sloupce clusteringu vybrány na základě skutečných vzorů dotazů, zejména těch, které mají střední až vysokou kardinalitu a kdy se během dotazů používají predikáty rozsahu.

Při používání clusteringu dat zvažte následující osvědčené postupy:

  • Clustering dat je efektivnější u velkých tabulek.
  • Kdykoli je to možné, použijte dávkový příjem dat a aktualizace ke zpracování většího počtu řádků najednou, místo abyste používali menší úlohy. Pro zajištění optimálního výkonu by operace DML měly mít alespoň 1 milion řádků, aby bylo možné využívat clustering dat. Po po sobě jdoucích vloženích, aktualizacích a odstraněních může komprimace dat konsolidovat řádky z menších souborů do optimální velikosti.
  • Zvolte sloupce s kardinalitou střední až vysoké pro clustering dat, protože z důvodu jejich odlišného rozdělení hodnot poskytují lepší výsledky. Sloupce s nízkou kardinalitou můžou nabízet omezené možnosti pro vyřazení souborů.
  • Vyberte sloupce na základě častého WHERE používání predikátů na řídicích panelech, sestavách, naplánovaných úlohách nebo uživatelských dotazech. Podmínky rovnosti spojení nemají prospěch z clusteringu dat. Přehled použití Query Insights k výběru sloupců pro clustering dat na základě aktuální úlohy najdete v kurzu Použití clusteringu dat v datovém skladu Fabric.
  • Nepoužívejte clustering dat podle více sloupců, než je nezbytně nutné. Clustering s více sloupci zvyšuje složitost úložiště, přidává režijní náklady a nemusí nabízet výhody, pokud se všechny sloupce nepoužívají společně v dotazech s predikáty.
  • Pořadí sloupců použité v CLUSTER BY není důležité a nemění způsob ukládání řádků.
  • Při vytváření tabulky s clusteringem dat pomocí CREATE TABLE AS SELECT (CTAS) nebo ingestování dat s INSERT INTO ... SELECT, ponechte výběrovou část těchto příkazů co nejjednodušší pro optimální kvalitu clusteringu dat.

Clustering dat může během dotazů výrazně snížit náklady, pokud jsou dobře v souladu s predikáty dotazů. Příjem dat však vyžaduje více času a jednotek kapacity v tabulce, která používá seskupování dat, ve srovnání s ekvivalentní tabulkou se stejnými daty bez seskupování. K tomu dochází, protože modul skladu potřebuje během příjmu dat objednat data. Vzhledem k tomu, že se data, která se ingestují, se čtou vícekrát, může clustering dat snížit celkovou spotřebu výpočetních prostředků dané úlohy.

Systémová zobrazení

Metadata clusterování dat lze dotazovat pomocí sys.index_columns. Zobrazuje všechny sloupce používané v clusteringu dat, včetně řadového sloupce použitého v klauzuli CLUSTER BY .

Následující dotaz obsahuje seznam všech sloupců používaných v clusteringu dat v aktuálním skladu a jejich tabulkách:

SELECT
    t.name AS table_name,
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE ic.data_clustering_ordinal > 0
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

Poznámka:

Pořadí sloupců se zobrazí pouze jako odkaz na pořadí použité v CLUSTER BY, když byla tabulka definována. Jak je popsáno v osvědčených postupech, pořadí sloupců nemá vliv na výkon.

Omezení a poznámky

  • Výkon příjmu dat se může zhoršit, když tabulky obsahují velké sloupce varchar s vysoce proměnlivými velikostmi.
    • Představte si například tabulku se sloupcem varchar(200 ): Pokud některé řádky obsahují jenom několik znaků, zatímco jiné přistupují k maximální délce, může významná odchylka velikosti dat negativně ovlivnit rychlost příjmu dat.
    • Tento problém je známý a bude vyřešen v nadcházející verzi.
  • IDENTITY sloupce nelze použít s CLUSTER BY. Tabulky, které obsahují IDENTITY sloupec, lze stále použít pro clustering dat vzhledem k tomu, že používá různé sloupce s CLUSTER BY.
  • Clustering dat musí být definován při vytváření tabulky. Převod běžné tabulky na tabulku s CLUSTER BY není podporován. Podobně není povolená úprava sloupců clusteringu po vytvoření tabulky. Pokud jsou potřeba různé sloupce clusteringu, volitelně použijte CREATE TABLE AS SELECT (CTAS) k vytvoření nové tabulky s požadovanými sloupci clusteringu.
  • V některých případech je možné clustering dat použít asynchronně. V takových případech se data přeorganizují pomocí úlohy na pozadí a po dokončení příjmu dat nemusí být tabulka plně optimalizovaná. K tomu může dojít za následujících podmínek:
    • Při použití INSERT INTO ... SELECT nebo CREATE TABLE AS SELECT (CTAS), když se kolace zdrojových a cílových tabulek liší.
    • Při ingestování z externích dat, která mají komprimovaný formát CSV.
    • Pokud má příkaz pro zpracování dat méně než 1 milion řádků.
  • Příjem dat v tabulkách se seskupováním dat způsobuje dodatečné zatížení ve srovnání s tabulkou se stejným schématem, která seskupování dat nepoužívá. K tomu dochází kvůli dodatečným výpočtům potřebným k optimalizaci úložiště. Pokud má klastrovací sloupec kolaci nerozlišující velká a malá písmena, očekávají se také vyšší režijní náklady.
  • Seskupování dat může přinést výhody pro dobu odezvy dotazů, spotřebu jednotek kapacity (CU) nebo obojí.

Examples

A. Vytvořte shlukovou tabulku pro prodejní data

Tento příklad vytvoří jednoduchou Sales tabulku a použije CustomerIDSaleDate sloupce pro clustering dat.

CREATE TABLE Sales (
    SaleID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
) WITH (CLUSTER BY (CustomerID, SaleDate))

B. Vytvoření clusterované tabulky pomocí příkazu CREATE TABLE AS SELECT

Tento příklad používá CREATE TABLE AS SELECT k vytvoření kopie Sales existující tabulky se sloupcem CLUSTER BYSaleDate .

CREATE TABLE Sales_CTAS 
WITH (CLUSTER BY (SaleDate)) 
AS SELECT * FROM Sales

C. Zobrazení sloupců používaných pro clustering dat v dané tabulce

Tento příklad uvádí sloupce používané pro clustering dat v Sales tabulce.

SELECT
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE 
    ic.data_clustering_ordinal > 0
   AND t.name = 'Sales'
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

Výsledky:

Tabulka zobrazující skupinové sloupce a jejich pořadí. První řádek obsahuje ID zákazníka s pořadovým pořadím 1. Druhý řádek obsahuje saleDate s pořadovým pořadím 2.

D. Kontrola efektivity voleb sloupců pro clustering dat

Query Insights vám může pomoct vyhodnotit vliv clusteringu dat na vaši úlohu porovnáním času procesoru a dat kontrolovaných mezi daným dotazem a jeho ekvivalentním spuštěním v clusterované kopii původní tabulky. Následující příklad ukazuje, jak načíst přidělený čas procesoru a objem dat kontrolovaných napříč diskem, pamětí a vzdáleným úložištěm pro konkrétní dotaz.

SELECT 
    allocated_cpu_time_ms, 
    data_scanned_disk_mb, 
    data_scanned_memory_mb, 
    data_scanned_remote_storage_mb
FROM 
    queryinsights.exec_requests_history 
WHERE 
     distriubted_statement_id = '<Query_Statement_ID>'

Kde <Query_Statement_ID> je ID distribuovaného příkazu dotazu, který chcete vyhodnotit.

Další krok

Návod: Použití shlukování dat v datovém skladu Fabric

Související obsah

  • Last updated on