Optimalizace spojování pro rozsahy

Spojení rozsahu nastane, když jsou dvě relace spojeny pomocí bodu v intervalu nebo intervalu překrývající podmínky. Podpora optimalizace spojení rozsahu 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 nápovědy pro spojení oblastí, pokud je výkon nízký.

Bod v intervalu spojení

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ů

Spojení rozsahu překrývání intervalů 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 spojování pro rozsahy

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

• Musí mít podmínku, která se dá interpretovat jako bod v intervalu nebo intervalu, který se spojování rozsahu intervalu překrývají.
• Všechny hodnoty, které jsou součástí podmínky spojení rozsahu, jsou číselného typu (integrální, plovoucí desetinná čárka, desetinná čárka) DATEnebo TIMESTAMP.
• Všechny hodnoty zahrnuté v podmínce spojení rozsahu jsou stejného typu. V případě desetinného typu musí být hodnoty také stejné měřítko a přesnost.
• Jedná se o INNER JOINbod v intervalovém spojení, LEFT OUTER JOIN hodnotu bodu na levé straně nebo RIGHT OUTER JOIN bodovou hodnotu 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 do několika 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ří 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 DATE hodnot se hodnota velikosti přihrádky interpretuje jako dny. Například hodnota velikosti přihrádky 7 představuje týden.
• U TIMESTAMP hodnot se hodnota velikosti přihrádky interpretuje jako sekundy. Pokud je požadována dílčí 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ápovědy pro spojení rozsahu v dotazu, nebo nastavením parametru konfigurace relace. Optimalizace spojení rozsahu se použije jenom 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.

Povolení spojení rozsahu pomocí nápovědy pro spojení rozsahu

Pokud chcete v dotazu SQL povolit optimalizaci spojení rozsahu, můžete k určení velikosti přihrádky použít nápovědu 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. Je to proto, že spojení jsou ponechána asociativní, takže dotaz je interpretován jako (a JOIN b) JOIN ca nápověda 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")

Povolení připojení k rozsahu 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

Efektivita optimalizace spojení rozsahu závisí na výběru vhodné velikosti přihrádky.

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 hodnoty překrývají s 100 000 intervaly.

Pokud je délka intervalu poměrně jednotná a známá, doporučujeme nastavit velikost intervalu 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 nastavila velikost intervalu, 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 tabulka rangess intervaly, které jsou mezi sloupci start 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 hodnoty 99,99th, 99,999th percentil a tak dále v případě potřeby.

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.