Optimalizace join rozsahu

Článek
12/27/2024

Oblast join nastane, když jsou dvě relace spojovány pomocí bodu v intervalu nebo podmínky překrytí intervalu. Podpora optimalizace rozsahu join v Databricks Runtime může přinést řádové zlepšení výkonu dotazů, ale vyžaduje pečlivé ruční ladění.

Databricks doporučuje používat join nápovědy pro spojení rozsahů, pokud je výkon nízký.

Bod v rozsahu intervalů join

Bod v rozsahu intervalů join je join, ve kterém podmínka obsahuje predikáty určující, že hodnota z jedné relace je mezi dvěma values z druhé relace. Příklad:

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

Rozsah překrývání intervalů join

Rozsah překrývání intervalů je join, ve kterém podmínka obsahuje predikáty, jež určují překrytí intervalů mezi dvěma values z každé relace join. Příklad:

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

Optimalizace rozsahu join

Optimalizace rozsahu join se provádí pro spojení, která:

Mít podmínku, kterou lze interpretovat jako bod v intervalu nebo překrývání intervalů join.
Všechny values zahrnuté v podmínce join rozsahu jsou číselného typu (celočíselný, plovoucí čárka, desetinný), DATEnebo TIMESTAMP.
Všechny values zapojené do podmínky rozsahu join jsou stejného typu. V případě typu decimálního musí mít values také stejné měřítko a přesnost.
Jedná se o INNER JOINnebo v případě bodu v rozsahu intervalů join, LEFT OUTER JOIN s hodnotou bodu na levé straně nebo RIGHT OUTER JOIN s hodnotou bodu na pravé straně.
Mít parametr ladění velikosti přihrádky.

Velikost přihrádky

velikost přihrádky je číselný parametr ladění, který rozdělí values doménu podmínky rozsahu na několik intervalů stejné velikosti. Optimalizace například s velikostí intervalu 10 rozdělí doménu do intervalů, které jsou intervaly délky 10. Pokud máte bod v podmínce rozsahu p BETWEEN start AND enda start je 8 a end je 22, tento interval hodnoty se překrývá se třemi intervaly délky 10 – prvním intervalem od 0 do 10, druhým intervalem od 10 do 20 a třetím intervalem od 20 do 30. Pouze body, které spadají do stejných tří kategorií, musí být považovány za možné shody join pro daný interval. Pokud je například p 32, může se vyloučit, že spadá mezi start 8 a end 22, protože spadá do intervalu od 30 do 40.

Poznámka:

Pro DATEvaluesse hodnota velikosti segmentu interpretuje jako dny. Například hodnota velikosti přihrádky 7 představuje týden.
Pro TIMESTAMPvaluesse hodnota velikosti přihrádky interpretuje jako sekundy. Pokud se vyžaduje podsekundová hodnota, dá se použít desetinná values. Například hodnota velikosti intervalu 60 představuje minutu a hodnota velikosti intervalu 0,1 představuje 100 milisekund.

Velikost intervalů můžete zadat buď pomocí nápovědy rozsahu join v dotazu, nebo nastavením parametru konfigurace relace. Optimalizace rozsahu join se použije pouze v případě, že velikost přihrádky zadáte ručně. Část Volba velikosti přihrádky popisuje, jak zvolit optimální velikost přihrádky.

Aktivujte rozsah join pomocí nápovědy pro rozsah join

Pokud chcete povolit optimalizaci rozsahu join v dotazu SQL, můžete k určení velikosti přihrádky použít oblast join nápovědy. Tip musí obsahovat název relace jedné ze spojených relací a parametr velikosti číselných intervalů. Název relace může být table, zobrazení nebo poddotaz.

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)

Poznámka:

Ve třetím příkladu musíte umístit nápovědu na c. Důvodem je to, že spojení jsou ponechána asociativní, takže dotaz je interpretován jako (a JOIN b) JOIN ca nápověda k a se vztahuje na joina s b a ne join s 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()
)

Můžete také umístit nápovědu pro rozsah join na jeden z připojených datových rámců. V takovém případě obsahuje tip pouze parametr velikosti číselných přihrádek.

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

Povolit rozsah join pomocí konfigurace relace

Pokud nechcete dotaz upravovat, můžete velikost přihrádky zadat jako parametr konfigurace.

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

Tento parametr konfigurace se vztahuje na všechny join s podmínkou rozsahu. Jiná velikost přihrádky set v rámci rozsahu join jako nápověda vždy přepíše tu set nastavenou parametrem.

Volba velikosti přihrádky

Účinnost optimalizace rozsahu join závisí na výběru vhodné velikosti binu.

Malá velikost přihrádky vede k většímu počtu intervalů, což pomáhá při filtrování potenciálních shod. Pokud je však velikost intervalu výrazně menší než zjištěné intervaly hodnot, stává se neefektivní a intervaly hodnot se překrývají více intervalů intervalů . Například při podmínce p BETWEEN start AND endmá wherestart hodnotu 1 000 000, end je 1 999 999 a pro velikost binu 10 se interval hodnot překrývá se 100 000 biny.

Pokud je délka intervalu poměrně jednotná a známá, doporučujeme set velikost přihrádky na typickou očekávanou délku intervalu hodnoty. Pokud se ale délka intervalu liší a zkosí, je potřeba najít zůstatek, aby se set velikost přihrádky, která efektivně filtruje krátké intervaly a zároveň brání překrývání příliš velkého počtu intervalů. Za předpokladu, že tableranges, s intervaly, které jsou mezi columnsstart a end, můžete určit různé percentily hodnoty zkosené délky intervalu pomocí následujícího dotazu:

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

Doporučeným nastavením velikosti přihrádky je maximální hodnota na 90. percentilu nebo hodnota v 99. percentilu dělené hodnotou 10 nebo hodnotou 99,9. percentilu děleného hodnotou 100 atd. Odůvodnění je:

Pokud je hodnota na 90. percentilu velikost intervalu, je délka intervalu intervalu delší než 10 %, takže rozsah je větší než 2 sousední intervaly intervalu.
Pokud je hodnota na 99. percentilu velikost přihrádky, bude délka intervalu hodnot přesahovat více než 11 sousedních intervalů intervalů.
Pokud je hodnota na 99,9. percentilu velikost intervalu, je velikost intervalu pouze 0,1 % intervalů hodnot větší než 101 sousedících intervalů.
Totéž lze opakovat pro values na 99,99 percentilu, 99,999 percentilu a tak dále, pokud bude potřeba.

Popsaná metoda omezuje množství zkosených dlouhých intervalů hodnot, které překrývají více intervalů intervalů. Hodnota velikosti přihrádky získaná tímto způsobem je pouze výchozím bodem pro vyladění; skutečné výsledky můžou záviset na konkrétní úloze.

Sdílet prostřednictvím