Optimering av områdekoppling

En intervallkoppling inträffar när två relationer kopplas med hjälp av en punkt inom ett intervall eller ett intervallöverlappningsvillkor. Stöd för optimering av range join i Databricks Runtime kan ge en storskalig förbättring av frågeprestanda, men kräver noggrann manuell finjustering.

Databricks rekommenderar att du använder kopplingstips för intervallkopplingar när prestandan är dålig.

Punkt i intervalområdeanslutning

En punkt i intervallkopplingen är en koppling där villkoret innehåller predikat som anger att ett värde från en relation är 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ådessammanfogning

Intervallkopplingsoptimeringen utförs för kopplingar som:

• Ha ett villkor som kan tolkas som en punkt i ett intervall eller en intervallöverlappning.
• Alla värden som ingår i villkoret för intervallanslutning är av en numerisk typ (integral, flyttal, decimal), DATEeller TIMESTAMP.
• Alla värden som ingår i villkoret för intervallkoppling är av samma typ. När det gäller decimaltypen måste värdena också ha samma skala och precision.
• Det är en INNER JOIN, eller om det gäller punkt i intervallkoppling, en LEFT OUTER JOIN med punktvärde till vänster eller RIGHT OUTER JOIN med punktvärde till höger.
• Ha en justeringsparameter för bin-storlek.

Diskretiseringsstorlek

bin-storlek är en numerisk justeringsparameter som delar upp intervallvillkorets värdemängd i flera intervall av lika storlek. Med till exempel en lagerplatsstorlek på 10 delar optimeringen upp domänen i 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 det till exempel p är 32 kan det uteslutas att det faller mellan start 8 och end 22, eftersom det faller i lagerplatsen från 30 till 40.

Du kan ange bin-storleken antingen genom att använda en räckviddssammanfogningstips i frågan eller genom att ange en sessionskonfigurationsparameter. Intervallkopplingsoptimeringen tillämpas endast om du anger lagerplatsstorleken manuellt. Avsnitt Välj lagerplatsstorlek beskriver hur du väljer en optimal lagerplatsstorlek.

Aktivera intervallanslutning med hjälp av en intervallanslutningsanvisning

Om du vill aktivera intervallkopplingsoptimering i en SQL-fråga kan du använda en intervallkopplingshint för att ange binkstorleken. Tipset måste innehålla relationsnamnet för en av de kopplade relationerna och den numeriska bin-storleksparametern. Relationsnamnet kan vara 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)

#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 kan du ange 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 lagerplatsstorlek

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

En liten lagerplatsstorlek resulterar i ett större antal lagerplatser, vilket hjälper till att filtrera potentiella matchningar. Det blir dock ineffektivt om bin-storleken är betydligt mindre än de påträffade värdeintervallen och värdeintervallen överlappar flera intervall . 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 värdeintervallet med 100 000 bin.

Om intervallets längd är ganska enhetlig och känd rekommenderar vi att du anger intervallstorleken till den typiska förväntade längden på värdeintervallet. Men om intervallets längd är varierande och skev måste ett saldo hittas för att ange en lagerplatsstorlek som filtrerar de korta intervallen effektivt, samtidigt som de långa intervallen inte överlappar för många lagerplatser. Om du antar att en tabell ranges, med intervall mellan kolumner start och end, kan du fastställa olika percentiler av värdet för skev intervalllängd med följande fråga:

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

En rekommenderad inställning av lagerplatsstorleken skulle vara det maximala värdet vid den 90:e percentilen, eller värdet vid den 99:e percentilen dividerat med 10, eller värdet vid den 99,9:e percentilen dividerat med 100 och så vidare. Anledningen är:

• Om värdet vid den 90:e percentilen är lagerplatsstorleken är endast 10 % av värdeintervalllängderna längre än intervallintervallet, så sträcker sig över mer än 2 angränsande intervall.
• Om värdet vid den 99:e percentilen är lagerplatsstorleken sträcker sig endast 1 % av värdeintervallets längder över mer än 11 angränsande intervall.
• Om värdet vid den 99,9:e percentilen är lagerplatsstorleken sträcker sig endast 0,1 % av värdeintervallets längder över mer än 101 angränsande intervall.
• Samma sak kan upprepas för värdena den 99,99: e, den 99,999:e percentilen och så vidare om det behövs.

Den beskrivna metoden begränsar mängden skeva långa värdeintervall som överlappar flera intervall. Det värde för bin-storlek som erhålls på det här sättet är bara en startpunkt för finjustering. faktiska resultat kan bero på den specifika arbetsbelastningen.