Dela via

Optimering av intervallkoppling

  • Artikel

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

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

Punkt-i-intervallkoppling

En punkt i intervallkoppling ä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;

Intervallkoppling för intervall överlappning

Ett intervall överlappande intervallkoppling är en koppling där villkoret innehåller predikat som anger 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 intervallkoppling

Intervallkopplingsoptimeringen utförs för kopplingar som:

  • Ha ett villkor som kan tolkas som en punkt i intervall eller intervall överlappande intervallkoppling.
  • Alla värden som ingår i villkoret för intervallkoppling ä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, ett 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-storleken är en numerisk justeringsparameter som delar upp områdesvillkorets värdedomän i flera intervall med samma 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 lagerplatser måste betraktas som möjliga kopplingsmatchningar 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.

Kommentar

  • För DATE värden tolkas värdet för lagerplatsstorleken 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. Om ett värde under sekund krävs kan bråkvärden användas. Ett lagerplatsstorleksvärde på 60 representerar till exempel en minut och ett lagerplatsstorleksvärde på 0,1 representerar 100 millisekunder.

Du kan ange lagerplatsstorleken antingen med hjälp av en intervallkopplingstips 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 intervallkoppling med hjälp av ett tips om intervallkoppling

Om du vill aktivera intervallkopplingsoptimeringen i en SQL-fråga kan du använda ett tips för 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 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)

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 gäller för kopplingen a till med b och inte kopplingen med c.a

#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. Men en annan intervallstorlek som anges via en intervallkopplingstips åsidosätter alltid den som anges via parametern.

Välj lagerplatsstorlek

Hur effektiv intervallkopplingsoptimeringen är beror på att du väljer lämplig lagerplatsstorlek.

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 lagerplatsstorlek på 10, överlappar värdeintervallet 100 000 lagerplatser.

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 en tabell ranges, med intervall mellan kolumner start och end, kan du fastställa olika percentiler av det skeva intervallets längdvärde 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.