Share via

Gegevensclustering in Fabric Data Warehouse

Van toepassing op:✅ Warehouse in Microsoft Fabric

Gegevensclustering is een techniek die wordt gebruikt om gegevens te ordenen en op te slaan op basis van overeenkomsten. Gegevensclustering verbetert de queryprestaties en vermindert de toegangskosten voor reken- en opslagtoegang voor query's door vergelijkbare records te groeperen.

Hoe het werkt

Gegevensclustering werkt door rijen met vergelijkbare waarden op te slaan op aangrenzende locaties in opslag tijdens opname. Gegevensclustering maakt gebruik van een ruimtevullende curve om gegevens te ordenen op een manier die de lokaliteit in meerdere dimensies behoudt, wat betekent dat rijen met vergelijkbare waarden in clusterkolommen fysiek dicht bij elkaar worden opgeslagen. Deze aanpak verbetert de queryprestaties aanzienlijk door het overslaan van bestanden uit te voeren en het aantal gescande bestanden te verminderen.

In tegenstelling tot conventionele lexicografische volgorde gebruikt gegevensclustering een geavanceerd algoritme om op te nemen, waarbij rijen met vergelijkbare kolomwaarden dicht bij elkaar worden geplaatst, zelfs wanneer een tabel wordt geclusterd door meerdere kolommen. Hierdoor is gegevensclustering ideaal voor bereikquery's, filters voor hoge kardinaliteit en grote tabellen met scheve distributies, wat resulteert in snellere leesbewerkingen, verminderde I/O en efficiënter resourcegebruik.

Hier volgt een vereenvoudigde conceptuele illustratie van gegevensclustering:

Diagram waarin het concept van gegevensclustering in een datawarehouse wordt geïllustreerd.

In dit diagram toont een tabel met het label Source data rijen die gemengd en in verschillende kleuren gemarkeerd zijn om clustergroeperingen per bestemming weer te geven. Een geordende tabel wordt gesplitst in drie bestandssegmenten, waarbij elke groepering van rijen met vergelijkbare kleuren wordt gedemonstreerd hoe clustering gegevens in geoptimaliseerde opslagsegmenten organiseert op basis van kolomwaarden.

Metagegevens van gegevensclustering worden tijdens opname in het manifest ingesloten, waardoor de magazijnengine intelligente beslissingen kan nemen over welke bestanden tijdens gebruikersquery's moeten worden geopend. Deze metagegevens, gecombineerd met de manier waarop rijen met vergelijkbare waarden samen worden opgeslagen, zorgen ervoor dat query's met filterpredicaten volledige bestanden en rijgroepen kunnen overslaan die buiten het predicaatbereik vallen. Als een query bijvoorbeeld slechts 10% van de gegevens van een tabel beslaat, zorgt clustering ervoor dat alleen bestanden die de gegevens binnen het bereik van het filter bevatten, worden gescand, waardoor het I/O- en rekenverbruik wordt verminderd. Grotere tabellen profiteren meer van gegevensclustering, omdat de voordelen van het overslaan van bestandendelen toenemen naarmate het gegevensvolume toeneemt.

Wanneer gebruikt u gegevensclustering?

Bij het bepalen of gegevensclustering nuttig kan zijn, onderzoekt u querypatronen en tabelkenmerken in het magazijn. Gegevensclustering is het meest effectief wanneer query's herhaaldelijk filteren op specifieke kolommen en wanneer de onderliggende tabellen groot zijn en middelgrote tot hoge kardinaliteit bevatten. Enkele veelvoorkomende scenario's zijn:

  • Herhaalde query's met WHERE filters: als de workload frequente query's bevat die specifieke kolommen filteren, zorgt gegevensclustering ervoor dat alleen relevante bestanden worden gescand tijdens leesquery's. Dit geldt ook wanneer de filters herhaaldelijk worden gebruikt in dashboards, rapporten of geplande taken en naar de magazijnengine worden gepusht als SQL-instructies.
  • Grotere tabellen: gegevensclustering is het meest effectief wanneer ze worden toegepast op grote tabellen waarbij het scannen van de volledige gegevensset kostbaar is. Door rijen te ordenen met gegevensclustering, kan de magazijnengine hele bestanden en rijgroepen overslaan die niet overeenkomen met het queryfilter, waardoor het I/O- en rekengebruik kan worden verminderd.
  • Kolommen met een gemiddelde tot hoge kardinaliteit: kolommen met een hogere kardinaliteit (bijvoorbeeld kolommen met veel afzonderlijke waarden, zoals een id of een datum), profiteren meer van gegevensclustering omdat de engine vergelijkbare waarden kan isoleren en colocateiseren. Dit maakt het efficiënt overslaan van bestanden mogelijk, met name voor selectieve query's. Kolommen met een lage kardinaliteit (bijvoorbeeld geslacht, regio) hebben hun waarden verspreid over meer bestanden, waardoor er beperkte mogelijkheden zijn voor het overslaan van bestanden.
  • Selectieve query's met een beperkt bereik: wanneer query's doorgaans een kleine subset van gegevens richten en worden gecombineerd met een WHERE-filter, zorgt gegevensclustering ervoor dat alleen bestanden die de relevante rijen bevatten, worden gelezen.

Gegevensclustering vindt automatisch plaats tijdens gegevensopname, ongeacht hoe rijen zijn opgenomen. Er zijn geen gebruikersbewerkingen vereist nadat gegevens zijn opgenomen om gegevensclustering toe te passen.

CLUSTER BY syntaxis

Gegevensclustering wordt gedefinieerd tijdens het maken van de tabel met behulp van de CLUSTER BY component. De syntaxis is als volgt:

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

SYNTAXIS VOOR 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>;

Voor de CLUSTER BY component moet ten minste één kolom worden opgegeven voor gegevensclustering en maximaal vier kolommen.

Het maken van een tabel waarvoor SELECT INTO gegevensclustering wordt gebruikt, wordt niet ondersteund.

Ondersteuning voor gegevenstypen

De volgende tabel bevat een overzicht van kolomtypen die kunnen worden gebruikt in de CLUSTER BY component:

Categorie Gegevenstype Ondersteunde gegevensclustering
Exacte numerieken bit Nee.
Exacte numerieken grootint, int, kleineint, decimaal2, numeriek Yes
Benaderende numerieke Float, echt Yes
Datum en tijd datum, datum/tijd2, tijd Yes
Tekenreeksen1 teken Yes
Tekenreeksen1 varchar Yes
LOB-typen varchar(max), varbinary(max) Nee.
Binaire strings varbinary, uniqueidentifier Nee.

1 Voor tekenreekstypen (teken/varchar) worden alleen de eerste 32 tekens gebruikt wanneer kolomstatistieken worden geproduceerd. Als gevolg hiervan kunnen kolommen met waarden met lange voorvoegsels beperkte voordelen hebben met gegevensclustering.

2 Voor decimale typen met precisie die groter zijn dan 18, worden predicaten niet naar de opslag gepusht tijdens het uitvoeren van de query. Als u decimale typen met gegevensclustering gebruikt, kunt u kolommen met kleinere precisie gebruiken.

Kolommen met niet-ondersteunde gegevenstypen kunnen nog steeds bestaan in een tabel die gebruikmaakt van gegevensclustering, maar kan niet worden gebruikt met CLUSTER BY.

Best practices voor gegevensclustering

Gegevensclustering is effectiever wanneer clusteringkolommen worden gekozen op basis van werkelijke querypatronen, met name de kolommen met gemiddelde tot hoge kardinaliteit en wanneer bereikpredicaten worden gebruikt tijdens query's.

Houd rekening met de volgende aanbevolen procedures bij het gebruik van gegevensclustering:

  • Gegevensclustering is effectiever voor grote tabellen.
  • Indien mogelijk, voer batchopname en updates uit om een groter aantal rijen tegelijk te verwerken, in plaats van kleinere taken te gebruiken. Voor optimale prestaties moeten DML-bewerkingen ten minste 1 miljoen rijen hebben om te profiteren van gegevensclustering. Na opeenvolgende invoegingen, updates en verwijderingen kan gegevenscompressie rijen uit kleinere bestanden samenvoegen tot optimaal formaat.
  • Kies kolommen met een gemiddelde tot hoge kardinaliteit voor gegevensclustering, omdat ze betere resultaten opleveren vanwege hun afzonderlijke waardeverdeling. Kolommen met lage kardinaliteit kunnen beperkte mogelijkheden bieden voor het snoeien van bestanden.
  • Selecteer kolommen op basis van frequent gebruik van WHERE predicaten in dashboards, rapporten, geplande taken of gebruikersquery's. Voorwaarden voor gelijkheidsdeelname profiteren niet van gegevensclustering. Raadpleeg de zelfstudie: Gegevensclustering gebruiken in Fabric Data Warehouse voor een overzicht van het gebruik van Query Insights om kolommen te kiezen voor gegevensclustering op basis van uw huidige workload.
  • Gebruik geen gegevensclustering door meer kolommen dan strikt noodzakelijk is. Clustering met meerdere kolommen voegt complexiteit toe aan opslag, voegt overhead toe en biedt mogelijk geen voordelen, tenzij alle kolommen samen worden gebruikt in query's met predicaten.
  • De kolomvolgorde waarin CLUSTER BY wordt gebruikt, is niet belangrijk en verandert niet hoe rijen worden opgeslagen.
  • Bij het maken van een tabel met gegevensclustering met behulp van CREATE TABLE AS SELECT (CTAS) of het opnemen van gegevens met INSERT INTO ... SELECT, houdt u het geselecteerde gedeelte van deze instructies zo eenvoudig mogelijk voor optimale kwaliteit van gegevensclustering.

Gegevensclustering kan de kosten tijdens query's aanzienlijk verlagen, indien goed afgestemd op querypredicaten. Gegevensopname brengt echter meer tijd en capaciteitseenheden (CU) in rekening in een tabel die gebruikmaakt van gegevensclustering in vergelijking met een equivalente tabel met dezelfde gegevens zonder gegevensclustering. Dit gebeurt omdat de datawarehouse engine gegevens moet sorteren tijdens de invoer. Omdat gegevens die worden opgenomen meerdere keren worden gelezen, kan gegevensclustering het totale rekenverbruik van een bepaalde workload verminderen.

Systeemweergaven

Metagegevens van gegevensclustering kunnen worden opgevraagd met behulp van sys.index_columns. Hier ziet u alle kolommen die worden gebruikt in gegevensclustering, inclusief de kolomordinaal die in de CLUSTER BY component wordt gebruikt.

De volgende query bevat alle kolommen die worden gebruikt in gegevensclustering in het huidige magazijn en de bijbehorende tabellen:

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;

Opmerking

De kolomordinaal wordt alleen ter referentie weergegeven volgens de volgorde die gebruikt is in CLUSTER BY toen de tabel werd gedefinieerd. Zoals besproken in best practices, heeft de kolomvolgorde geen invloed op de prestaties.

Beperkingen en opmerkingen

  • De prestaties van gegevensopname kunnen afnemen wanneer tabellen grote varchar-kolommen bevatten met zeer variabele gegevensgrootten.
    • Denk bijvoorbeeld aan een tabel met een varchar(200) -kolom: als sommige rijen slechts een paar tekens bevatten terwijl andere de maximale lengte naderen, kan de significante variantie van de gegevensgrootte een negatieve invloed hebben op de opnamesnelheid.
    • Dit probleem is bekend en wordt opgelost in een toekomstige release.
  • IDENTITY kolommen kunnen niet worden gebruikt met CLUSTER BY. Tabellen die een IDENTITY kolom bevatten, kunnen nog steeds worden gebruikt voor gegevensclustering, gezien het feit dat er verschillende kolommen met CLUSTER BYworden gebruikt.
  • Gegevensclustering moet worden gedefinieerd bij het maken van een tabel. Het converteren van een gewone tabel naar een tabel met CLUSTER BY wordt niet ondersteund. Het wijzigen van de clusterkolommen nadat een tabel is gemaakt, is ook niet toegestaan. Als er verschillende clusterkolommen nodig zijn, kunt u eventueel (CTAS) gebruiken CREATE TABLE AS SELECT om een nieuwe tabel te maken met de gewenste clusteringkolommen.
  • In sommige gevallen kan gegevensclustering asynchroon worden toegepast. In dergelijke gevallen worden gegevens opnieuw geordend met een achtergrondtaak en is de tabel mogelijk niet volledig geoptimaliseerd wanneer de opname is voltooid. Dit kan gebeuren onder de volgende omstandigheden:
    • Wanneer het gebruik van INSERT INTO ... SELECT of CREATE TABLE AS SELECT (CTAS) en de sortering van de bron- en doeltabellen verschillend is.
    • Wanneer u gegevens opneemt van externe gegevens die een gecomprimeerde CSV-indeling hebben.
    • Wanneer een gegevensinvoerinstructie minder dan 1 miljoen rijen bevat.
  • Bij gegevensopname van gegevensclustertabellen wordt een overhead in rekening gebracht in vergelijking met een tabel met hetzelfde schema dat geen gegevensclustering gebruikt. Dit gebeurt door extra berekeningen die nodig zijn om de opslag te optimaliseren. Wanneer de clusterkolom een niet hoofdlettergevoelige sortering heeft, wordt ook meer overhead verwacht.
  • Gegevensclustering kan baat hebben bij de reactietijd van query's, het verbruik van de capaciteitseenheid (CU) of beide.

Voorbeelden

Eén. Maak een geclusterde tabel voor verkoopdata aan

In dit voorbeeld wordt een eenvoudige Sales tabel gemaakt en worden de CustomerID en SaleDate kolommen voor gegevensclustering gebruikt.

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

B. Een gegroepeerde tabel maken met CREATE TABLE AS SELECT

In dit voorbeeld wordt CREATE TABLE AS SELECT gebruikt om een kopie te maken van de Sales bestaande tabel, met CLUSTER BY de SaleDate kolom.

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

C. De kolommen weergeven die worden gebruikt voor gegevensclustering in een bepaalde tabel

In dit voorbeeld worden de kolommen vermeld die worden gebruikt voor gegevensclustering in de Sales tabel.

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;

Resultaten:

Tabel met clusterkolommen en de bijbehorende rangschikkingsposities. De eerste rij bevat CustomerID met clustering ordinaal 1. De tweede rij bevat SaleDate met clustering ordinaal 2.

D. Controleer de effectiviteit van de kolomkeuzes voor gegevensclustering

Query Insights kan helpen bij het evalueren van het effect van gegevensclustering op uw workload door de CPU-tijd en gegevens te vergelijken die zijn gescand tussen een bepaalde query en de equivalente uitvoering ervan op een geclusterde kopie van de oorspronkelijke tabel. In het volgende voorbeeld ziet u hoe u de toegewezen CPU-tijd en het volume aan gegevens kunt ophalen dat is gescand op schijf, geheugen en externe opslag voor een specifieke query.

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

Waar <Query_Statement_ID> is de id van de gedistribueerde instructie van de query die u wilt evalueren.

Volgende stap

Zelfstudie: Gegevensclustering gebruiken in Fabric Data Warehouse

Verwante inhoud

  • Last updated on