Delen via

Optimalisatie van bereik join

  • Artikel

Een bereik join treedt op wanneer twee relaties worden samengevoegd met behulp van een punt-in-interval- of intervaloverlapvoorwaarde. Het bereik join optimalisatieondersteuning in Databricks Runtime kan een verbetering van een orde van grootte in de prestaties van query's opleveren, maar vereist zorgvuldige handmatige afstemming.

Databricks raadt aan om join hints te gebruiken voor bereikverbindingen bij slechte prestaties.

Punt binnen intervalbereik join

Een punt in het intervalbereik join is een join waarin de voorwaarde predicaten bevat die specificeren dat een waarde uit de ene relatie tussen twee values uit de andere relatie ligt. Voorbeeld:

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

Interval overlapbereik join

Een interval overlap-bereik join is een join waarin de voorwaarde predicaten bevat die een overlapping van intervallen tussen twee values uit elke relatie opgeven. Voorbeeld:

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

Optimalisatie van bereik join

De optimalisatie van bereik join wordt uitgevoerd bij joins die:

  • Een voorwaarde die kan worden geïnterpreteerd als een punt in een interval of overlappend interval bereik join.
  • Alle values die betrokken zijn bij het bereik join van de voorwaarde zijn van een numeriek type (integraal, drijvende komma, decimaal), DATEof TIMESTAMP.
  • Alle values die betrokken zijn bij de voorwaarde binnen bereik join zijn van hetzelfde type. In het geval van het decimale type moet de values ook dezelfde schaal en precisie hebben.
  • Het is een INNER JOIN, of in het geval van een punt in het intervalbereik join, een LEFT OUTER JOIN met een puntwaarde aan de linkerkant, of RIGHT OUTER JOIN met een puntwaarde aan de rechterkant.
  • Een parameter voor het afstemmen van de grootte van de bin hebben.

Grootte van opslaglocatie

De bin-grootte is een numerieke afstemmingsparameter waarmee het values domein van de bereikvoorwaarde wordt gesplitst in meerdere bins van gelijke grootte. Met een bingrootte van 10 splitst de optimalisatie het domein bijvoorbeeld op in bins die intervallen van lengte 10 zijn. Als u een punt in bereikvoorwaarde hebt van p BETWEEN start AND enden start 8 is en end 22 is, overlapt dit waarde-interval met drie bins van lengte 10: de eerste bin van 0 tot 10, de tweede bin van 10 tot 20 en de derde bin van 20 tot 30. Alleen de punten die binnen dezelfde drie categorieën vallen, moeten worden beschouwd als mogelijke join overeenkomsten voor dat interval. Als p het bijvoorbeeld 32 is, kan het worden uitgesloten als vallen tussen start 8 en end 22, omdat deze in de bin van 30 tot 40 valt.

Notitie

  • Voor DATEvalueswordt de waarde van de bin-grootte geïnterpreteerd als dagen. Een bin-groottewaarde van 7 vertegenwoordigt bijvoorbeeld een week.
  • Voor TIMESTAMPvalueswordt de waarde van de grootte van de bin geïnterpreteerd als seconden. Als een sub-seconde waarde is vereist, kan fractioneel values worden gebruikt. Een bin-groottewaarde van 60 vertegenwoordigt bijvoorbeeld een minuut en een bin-groottewaarde van 0,1 vertegenwoordigt 100 milliseconden.

U kunt de grootte van de bin opgeven met behulp van een bereik join hint in de query of door een sessieconfiguratieparameter in te stellen. De join-bereikoptimalisatie wordt alleen toegepast als u de bingrootte handmatig opgeeft. In de sectie Kies de grootte van de bin wordt beschreven hoe u een optimale bin-grootte kiest.

Bereik join inschakelen met een hint voor bereik join.

Als u het bereik join optimalisatie in een SQL-query wilt inschakelen, kunt u een bereik gebruiken join hint om de grootte van de bin op te geven. De hint moet de relationele naam van een van de gekoppelde relaties en de parameter voor de grootte van de numerieke bin bevatten. De relatienaam kan een table, een view of een subquery zijn.

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)

Notitie

In het derde voorbeeld moet u de hint plaatsen.c Dit komt doordat joins associatief blijven, zodat de query wordt geïnterpreteerd als (a JOIN b) JOIN cen de hint op a van toepassing is op de join van a met b en niet de join met 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()
)

U kunt ook een hint voor het bereik join plaatsen op een van de samengevoegde DataFrames. In dat geval bevat de hint alleen de parameter voor de numerieke bin-grootte.

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")

Bereik join inschakelen met sessieconfiguratie

Als u de query niet wilt wijzigen, kunt u de grootte van de bin opgeven als een configuratieparameter.

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

Deze configuratieparameter is van toepassing op alle join met een bereikvoorwaarde. Een andere bin-grootte set via een bereik join-hint echter overschrijft altijd degene via de parameter set.

Kies de grootte van de opslaglocatie

De effectiviteit van de optimalisatie van bereik join is afhankelijk van het kiezen van de juiste bin-grootte.

Een kleine bin-grootte resulteert in een groter aantal bins, wat helpt bij het filteren van de mogelijke overeenkomsten. Het wordt echter inefficiënt als de bin-grootte aanzienlijk kleiner is dan de aangetroffen waardeintervallen en de waardeintervallen overlappen meerdere bin-intervallen . Met een voorwaarde p BETWEEN start AND endis bijvoorbeeld wherestart 1.000.000 en end 1.999.999, en met een bingrootte van 10 overlapt het waarde-interval 100.000 bins.

Als de lengte van het interval redelijk uniform en bekend is, raden we u aan om de bin-grootte te set op de gebruikelijke verwachte lengte van het waardeinterval. Als de lengte van het interval echter varieert en scheef is, moet een evenwicht worden gevonden om een bakgrootte te set die de korte intervallen efficiënt filtert, terwijl de lange intervallen niet met te veel bakken overlappen. Uitgaande van een tableranges, met intervallen tussen columnsstart en end, kunt u verschillende percentielen van de waarde van de scheve intervallengte bepalen met de volgende query:

SELECT APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges

Een aanbevolen instelling van bin-grootte is het maximum van de waarde op het 90e percentiel, of de waarde op het 99e percentiel gedeeld door 10, of de waarde op het 99,9e percentiel gedeeld door 100, enzovoort. De logica is:

  • Als de waarde bij het 90e percentiel de bin-grootte is, is slechts 10% van de lengte van het waarde-interval langer dan het bin-interval, dus beslaat u meer dan 2 aangrenzende bin-intervallen.
  • Als de waarde bij het 99e percentiel de bin-grootte is, is slechts 1% van de lengte van het waarde-interval langer dan 11 aangrenzende bin-intervallen.
  • Als de waarde bij het 99,9e percentiel de bin-grootte is, beslaat slechts 0,1% van de lengte van het waardeinterval meer dan 101 aangrenzende bin-intervallen.
  • Hetzelfde kan worden herhaald voor de values op het 99,99e, het 99,999e percentiel, enzovoort, indien nodig.

De beschreven methode beperkt de hoeveelheid scheefgetrokken lange waardeintervallen die meerdere bin-intervallen overlappen. De waarde van de bin-grootte die op deze manier is verkregen, is slechts een uitgangspunt voor het afstemmen; werkelijke resultaten kunnen afhankelijk zijn van de specifieke workload.