Dela via

Räckviddsoptimering för join

  • Artikel

Ett intervall join inträffar när två relationer kopplas med ett punkt-i-intervall- eller intervall-överklappningsvillkor. Intervallet join optimeringsstöd i Databricks Runtime kan ge storleksförbättringar i frågeprestanda, men kräver noggrann manuell justering.

Databricks rekommenderar att du använder join-hints för range joins när prestandan är dålig.

Punkt i intervallområde join

En punkt i intervallintervallet join är en join där villkoret innehåller predikat som anger att ett värde från en relation är mellan två values 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 överlappningsområde join

Ett intervallöverlappningsintervall join är en join där villkoret innehåller predikat som specificerar överlappningar av intervall mellan två values 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;

Räckviddsoptimering för join

Optimering av zon join utförs för kopplingar som:

  • Ha ett villkor som kan tolkas som en punkt i ett intervall eller en intervallöverlappning join.
  • Alla values som ingår i villkoret för område join är av numerisk typ (integral, flyttal, decimal), DATEeller TIMESTAMP.
  • Alla values som ingår i villkoret för området join är av samma typ. När det gäller decimaltypen måste values också ha samma skala och precision.
  • Det är en INNER JOIN, eller i händelse av punkt i intervallintervallet join, 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

spannstorlek är en numerisk justeringsparameter som delar upp intervallvillkorets values domän i flera spann av 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 grupper behöver beaktas som möjliga join jämförelser 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 DATEvaluestolkas värdet för lagerplatsstorleken som dagar. Ett lagerplatsstorleksvärde på 7 representerar till exempel en vecka.
  • För TIMESTAMPvaluestolkas värdet för storleken på behållaren som sekunder. Om ett värde under en sekund krävs kan values användas i bråkform. Ett lagerplatsstorleksvärde på 60 representerar till exempel en minut och ett lagerplatsstorleksvärde på 0,1 representerar 100 millisekunder.

Du kan ange fackstorlek antingen genom att använda ett intervall med join-uppgift i frågebegäran eller genom att ange en sessionskonfigurationsparameter. Intervallet join optimering tillämpas endast om du anger lagerplatsstorleken manuellt. Avsnitt Välj lagerplatsstorlek beskriver hur du väljer en optimal lagerplatsstorlek.

Aktivera intervall join med hjälp av ett intervall join tips

Om du vill aktivera intervallet join optimering i en SQL-fråga kan du använda ett intervall join tips för att ange lagerplatsstorleken. Tipset måste innehålla relationsnamnet för en av de kopplade relationerna och den numeriska bin-storleksparametern. Relationsnamnet kan vara table, vy eller 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 join av a med b och inte join med 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 en intervall join ledtråd på en av de sammankopplade 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 intervall join med 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 join med ett intervallvillkor. Men en annan lagerplatsstorlek set genom ett intervall join ledtråd åsidosätter alltid den set genom parametern.

Välj lagerplatsstorlek

Effektiviteten hos räckvidd join optimering beror på att välja lämplig storlek på bin.

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, till exempel där wherestart är 1 000 000 och end är 1 999 999, och en fackstorlek på 10, överlappar värdeintervallet med 100 000 fack.

Om intervallets längd är ganska enhetlig och känd rekommenderar vi att du anpassar set klasstorleken till den typiskt förväntade längden på värdeintervallet. Men om intervallets längd är varierande och skev måste en balans hittas för att set 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 tableranges, med intervall mellan columnsstart och end, kan du fastställa olika percentiler för 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 göras för values vid 99,99:e, 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.