Optimalizace rozsahu spojení

Spojení rozsahu nastane, když jsou dvě relace spojeny pomocí bodu v intervalu nebo podmínky překryvu intervalu. Podpora optimalizace rozsahového spojení v Databricks Runtime může přinést výrazné zlepšení výkonu dotazů, ale vyžaduje důkladné ruční ladění.

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

Spojení bodu v rozsahu intervalu

Bod v intervalovém rozsahu spojení je spojení, ve kterém podmínka obsahuje predikáty určující, že hodnota z jedné relace je mezi dvěma hodnotami 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;

Spojení rozsahu překrývání intervalů

interval překrývající se rozsah spojení je spojení, ve kterém podmínka obsahuje predikáty určující překrývání intervalů mezi dvěma hodnotami z každé relace. 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 spojení rozsahu

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

• Mít podmínku, kterou lze interpretovat jako bod v intervalu nebo jako překryv intervalu v rozsahu spojení.
• Všechny hodnoty spojené s podmínkou spojení rozsahu jsou číselného typu (integrální, plovoucí, desetinný), DATEnebo TIMESTAMP.
• Všechny hodnoty zahrnuté v podmínce spojení rozsahu jsou stejného typu. V případě desetinného typu musí mít hodnoty také stejnou škálu a přesnost.
• Jedná se o INNER JOIN, nebo v případě spojení bodu v rámci intervalu o 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 parametr číselného ladění, který rozdělí doménu hodnot podmínky rozsahu na několik přihrádek 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ří intervalů, musí být v daném intervalu považovány za možné shody spojení. 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:

• U hodnot DATE se velikost přihrádky interpretuje jako dny. Například hodnota velikosti přihrádky 7 představuje týden.
• Pro hodnoty TIMESTAMP je hodnota velikosti binu interpretována jako sekundy. Pokud je vyžadována hodnota kratší než jedna sekunda, je možné použít desetinné hodnoty. Například hodnota velikosti intervalu 60 představuje minutu a hodnota velikosti intervalu 0,1 představuje 100 milisekund.

Velikost přihrádky můžete zadat buď pomocí náznaku pro spojení rozsahu v dotazu, nebo nastavením parametru konfigurace relace. Optimalizace spojení rozsahu 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.

Povolit rozsahové spojení pomocí tipu pro rozsahové spojení

Pokud chcete v dotazu SQL povolit optimalizaci spojení rozsahu, můžete k určení velikosti segmentu použít hint pro spojení rozsahu . Tip musí obsahovat název relace jedné ze spojených relací a parametr velikosti číselných intervalů. Název relace může být tabulka, 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 tip na a platí pro spojení a s b a nikoli spojení 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()
)

Nápovědu ke spojení rozsahu můžete umístit také na některý 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")

Umožněte spojení rozsahů 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 jakékoli spojení s podmínkou rozsahu. Jiná velikost přihrádky nastavená prostřednictvím nápovědy pro spojení rozsahu ale vždy přepíše tu, která je nastavena parametrem.

Volba velikosti přihrádky

Účinnost optimalizace spojení rozsahu závisí na výběru vhodné velikosti koše.

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 s podmínkou p BETWEEN start AND end, kde start je 1 000 000 a end je 1 999 999, a velikostí intervalu 10, se interval hodnot překrývá se 100 000 intervaly.

Pokud je délka intervalu poměrně jednotná a známá, doporučujeme nastavit velikost skupiny na typickou očekávanou délku intervalu hodnoty. Pokud se ale délka intervalu liší a je nesymetrická, je potřeba najít rovnováhu, aby byla nastavena velikost binu, která efektivně filtruje krátké intervaly a zároveň zabraňuje, aby se dlouhé intervaly nepřekrývaly s příliš mnoha biny. Za předpokladu, že tabulka rangesmá intervaly mezi sloupci start a end, můžete určit různé percentily hodnoty nesymetrické 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 hodnoty na 99,99. percentilu, 99,999. percentilu, a tak dále, pokud je to 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.