Optimering av områdekoppling

En range join slår ihop två tabeller på ett villkor som kontrollerar om ett värde ligger inom ett intervall, eller om två intervall överlappar, istället för att kontrollera om två värden är lika. Till exempel kontrollerar en fråga som matchar varje webbplatshändelse med användarsessionen där den inträffade om händelsens tidsstämpel ligger mellan sessionens start- och sluttid. En sökning som hittar möten schemalagda samtidigt kontrollerar om två tidsintervall överlappar. Optimeringen av intervalljoin i Databricks Runtime kan avsevärt förbättra prestandan för dessa frågor.

På grund av detta kan en join jämföra många radpar som inte kan matcha. Optimering för intervalljoin delar upp värdeintervallet i bins och jämför endast rader som ligger i överlappande bins. Se Bin size.

I Databricks SQL optimerar Azure Databricks automatiskt intervallanslutningar utan någon manuell konfiguration. Du kan också justera intervallkopplingar manuellt med hjälp av kopplingstips eller sessionskonfiguration för alla beräkningstyper.

Punkt i intervalområdeanslutning

En punkt i intervallkoppling är en koppling vars villkor innehåller predikat som anger att ett värde från en relation faller mellan två värden från den andra relationen. Till exempel:

-- using BETWEEN expressions
SELECT *
FROM points JOIN ranges ON points.p BETWEEN ranges.start and ranges.end;

-- using inequality expressions
SELECT *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.end;

-- with fixed length interval
SELECT *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.start + 100;

-- join two sets of point values within a fixed distance from each other
SELECT *
FROM points1 p1 JOIN points2 p2 ON p1.p >= p2.p - 10 AND p1.p <= p2.p + 10;

-- a range condition together with other join conditions
SELECT *
FROM points, ranges
WHERE points.symbol = ranges.symbol
  AND points.p >= ranges.start
  AND points.p < ranges.end;

Intervallöverlappningssammanfogning

Ett intervallöverlappande koppling är en koppling där villkoret innehåller predikat som specificerar en överlappning av intervall mellan två värden från varje relation. Till exempel:

-- overlap of [r1.start, r1.end] with [r2.start, r2.end]
SELECT *
FROM r1 JOIN r2 ON r1.start < r2.end AND r2.start < r1.end;

-- overlap of fixed length intervals
SELECT *
FROM r1 JOIN r2 ON r1.start < r2.start + 100 AND r2.start < r1.start + 100;

-- a range condition together with other join conditions
SELECT *
FROM r1 JOIN r2 ON r1.symbol = r2.symbol
  AND r1.start <= r2.end
  AND r1.end >= r2.start;

Optimering av områdekoppling

Intervall-join-optimeringen gäller för joins som:

  • Ha ett villkor för punkt i intervall eller intervallöverlappning.
  • Använd endast numeriska värden (heltal, flyttal eller decimalvärden), DATE eller TIMESTAMP i sammanfogningsvillkoret för intervall.
  • Använd värden av samma typ i intervall-join-villkoret. Decimalvärden måste också ha samma skala och precision.
  • Använd en stödd join-typ:
    • INNER JOIN, för varje range join
    • LEFT OUTER JOIN med punktvärdet på vänster sida, eller RIGHT OUTER JOIN med punktvärdet på höger sida, endast för punkt-i-intervall-kopplingar
  • Ha en binstorlek, antingen automatiskt härledd eller manuellt specificerad.

Sammanfogar med numerisk likhet och intervallvillkor

När ett kopplingsvillkor innehåller både ett likhetsvillkor för en numerisk kolumn och ett intervallvillkor, kan optimeraren tillämpa binning på kolumnen för numerisk likhet eftersom den uppfyller typkraven för intervallkopplingsoptimering. Detta kan leda till att jämförelsekolumnen tilldelas fack eller exkluderas från optimeringen, vilket minskar prestandan.

För att säkerställa att intervallkopplingsoptimeringen endast gäller för det avsedda intervallvillkoret, omvandlar du kolumnerna för numerisk likhet till STRING. Detta utesluter dem från överväganden som intervallvillkorskolumner.

SELECT /*+ RANGE_JOIN(reference, 3306084) */
    reference.*, position.*
FROM position
INNER JOIN reference
    ON CAST(position.parent_index AS STRING) = CAST(reference.parent_index AS STRING)
    AND position.child_index BETWEEN reference.min_child_index AND reference.max_child_index;

Samma mönster gäller för andra numeriska kolumner som används som likhetsnycklar, till exempel DATE, heltalsidentifierare eller klustrade partitionskolumner.

Intervallstorlek

bin-storlek är en numerisk justeringsparameter som delar upp intervallvillkorets värdemängd i flera intervall av lika storlek. Till exempel, med en fackstorlek på 10, delar optimeringen upp området i fack som är intervall med längd 10. Om du har en punkt i intervallvillkoret p BETWEEN start AND end, och start är 8 och end är 22, överlappar det här värdeintervallet med tre intervall med längd 10 – den första lagerplatsen från 0 till 10, den andra lagerplatsen från 10 till 20 och den tredje lagerplatsen från 20 till 30. Endast de punkter som ligger inom samma tre kategorier måste betraktas som möjliga matchningar för det intervallet. Om till exempel p är 32 kan det uteslutas att det faller mellan start 8 och end 22, eftersom det faller i intervallet från 30 till 40.

Kommentar

  • För DATE-värden tolkas värdet för bin-storleken som dagar. Ett lagerplatsstorleksvärde på 7 representerar till exempel en vecka.
  • För TIMESTAMP värden tolkas värdet för lagerplatsstorleken som sekunder. För ett värde under en sekund, använd ett bråkdelsvärde. Ett lagerplatsstorleksvärde på 60 representerar till exempel en minut och ett lagerplatsstorleksvärde på 0,1 representerar 100 millisekunder.

Du kan ange binstorleken med hjälp av ett tips om intervallkoppling i frågan eller genom att ställa in en konfigurationsparameter för sessionen. I Databricks SQL härleds bin-storleken automatiskt när automatisk intervallkopplingsoptimering aktiveras.

Automatisk intervallkopplingsoptimering

I Databricks SQL identifierar Azure Databricks automatiskt kvalificerande intervallkopplingar och härleder den optimala bin-storleken genom att sampling av intervalltabellen. Detta tar bort behovet av att manuellt ange en lagerplatsstorlek via tips eller sessionskonfiguration.

Automatisk intervallkopplingsoptimering aktiveras som standard i Databricks SQL. För att inaktivera det, ställ in denna konfiguration:

SET spark.databricks.optimizer.autoRangeJoin.enabled = false;

Om du anger en binstorlek via ett tips om intervallkoppling eller en sessionkonfiguration, åsidosätter det värdet den automatiskt härledda binstorleken.

Aktivera intervallanslutning med hjälp av en intervallanslutningsanvisning

Om du vill aktivera intervallkopplingsoptimeringen i en SQL-fråga använder du ett tips om intervallkoppling för att ange intervallstorleken. Tipset måste innehålla relationsnamnet för en av de kopplade relationerna och den numeriska bin-storleksparametern. Relationsnamnet är en tabell, en vy eller en underfråga.

SELECT /*+ RANGE_JOIN(points, 10) */ *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.end;

SELECT /*+ RANGE_JOIN(r1, 0.1) */ *
FROM (SELECT * FROM ranges WHERE ranges.amount < 100) r1, ranges r2
WHERE r1.start < r2.start + 100 AND r2.start < r1.start + 100;

SELECT /*+ RANGE_JOIN(c, 500) */ *
FROM a
  JOIN b ON (a.b_key = b.id)
  JOIN c ON (a.ts BETWEEN c.start_time AND c.end_time)

Kommentar

I det tredje exemplet måste du placera tipset på c. Det beror på att kopplingar lämnas associativa, så frågan tolkas som (a JOIN b) JOIN c, och tipset om a gäller för kopplingen av a med b och inte kopplingen till c.

#create minute table
minutes = spark.createDataFrame(
    [(0, 60), (60, 120)],
    "minute_start: int, minute_end: int"
)

#create events table
events = spark.createDataFrame(
    [(12, 33), (0, 120), (33, 72), (65, 178)],
    "event_start: int, event_end: int"
)

#Range_Join with "hint" on the from table
(events.hint("range_join", 60)
  .join(minutes,
    on=[events.event_start < minutes.minute_end,
    minutes.minute_start < events.event_end])
  .orderBy(events.event_start,
    events.event_end,
    minutes.minute_start)
  .show()
)

#Range_Join with "hint" on the join table
(events.join(minutes.hint("range_join", 60),
  on=[events.event_start < minutes.minute_end,
    minutes.minute_start < events.event_end])
  .orderBy(events.event_start,
    events.event_end,
    minutes.minute_start)
  .show()
)

Du kan också placera ett tips om intervallkoppling på en av de anslutna DataFrames.You can also place a range join hint on one of the joined DataFrames. I så fall innehåller tipset bara parametern för numerisk bin-storlek.

val df1 = spark.table("ranges").as("left")
val df2 = spark.table("ranges").as("right")

val joined = df1.hint("range_join", 10)
  .join(df2, $"left.type" === $"right.type" &&
     $"left.end" > $"right.start" &&
     $"left.start" < $"right.end")

val joined2 = df1
  .join(df2.hint("range_join", 0.5), $"left.type" === $"right.type" &&
     $"left.end" > $"right.start" &&
     $"left.start" < $"right.end")

Aktivera intervallkoppling med hjälp av sessionskonfiguration

Om du inte vill ändra frågan anger du bin-storleken som en konfigurationsparameter.

SET spark.databricks.optimizer.rangeJoin.binSize=5

Den här konfigurationsparametern gäller för alla kopplingar med ett intervallvillkor. Dock åsidosätter en annan fackstorlek som anges genom en intervallanslutningshint alltid den som anges via parametern.

Välj fackstorlek

Intervallkopplingsoptimeringens effektivitet beror på valet av lämplig bin-storlek.

En liten behållarstorlek resulterar i fler behållare, vilket underlättar filtreringen av potentiella matchningar. En liten binstorlek blir ineffektiv om värdeintervallen är mycket större än binstorleken, vilket gör att intervallen överlappar många bins. Till exempel, med ett villkor p BETWEEN start AND end där start är 1 000 000 och end är 1 999 999, och en binstorlek på 10, överlappar intervallet med 100 000 bins.

Om intervalllängden är ganska jämn och känd, ställ in klassbredden till den typiska intervalllängden. För varierande, sneda intervall, välj en binstorlek som filtrerar korta intervall effektivt samtidigt som långa intervall inte överlappar för många bins. För en tabell ranges med intervall mellan kolumner start och end, bestäm percentiler av den skeva intervalllängden med denna fråga:

SELECT
  map_from_arrays(
    ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999),
    APPROX_PERCENTILE(
      end::DOUBLE - start::DOUBLE,
      ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)
    )
  ) AS bin_sizes
FROM
  ranges;

Att konvertera varje kolumn till DOUBLE innan subtrahering gör att frågan fungerar oavsett om kolumnerna är numeriska värden, DATE-värden eller TIMESTAMP-värden.

Sätt binstorleken till det största av dessa värden:

  • Den 90:e percentilen av intervalllängderna. Vid denna storlek sträcker sig högst 10 % av intervallen över mer än 2 intervallklasser.
  • 99:e percentilen delat med 10. Vid denna storlek sträcker sig högst 1 % av intervallen över mer än 11 bins.
  • 99,9-percentilen delat med 100. Vid denna storlek sträcker sig högst 0,1 % av intervallen över mer än 101 fack.

För ännu längre intervall, fortsätt mönstret med 99,99:e och 99,999:e percentilerna. Använd resultatet som utgångspunkt och justera sedan bin-storleken efter din arbetsbelastning.

  • Uppdaterades senast