Optimalizace rozsahu spojení

K spojení podle rozsahu dochází, když se dvě relace spojí na základě podmínky, že bod leží v intervalu nebo že se intervaly překrývají. Použití optimalizace spojení rozsahu v Databricks Runtime může výrazně zlepšit výkon dotazů.

V Databricks SQL Azure Databricks automaticky optimalizuje spojení rozsahů bez jakékoli ruční konfigurace. Spojení podle rozsahu můžete také ručně vyladit pomocí nápověd pro spojení nebo konfigurace relace pro všechny typy výpočetních prostředků.

Spojení bodu v rozsahu intervalu

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

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ě typu decimal musí mít hodnoty také stejný rozsah desetinných míst a stejnou 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ě.
• Mají velikost koše, buď automaticky odvozenou, nebo ručně zadanou.

Spojení s číselnou rovností a podmínkami rozsahu

Pokud podmínka spojení obsahuje podmínku rovnosti u číselného sloupce i podmínky rozsahu, může optimalizátor použít binning na číselný sloupec rovnosti, protože splňuje požadavky na typ optimalizace spojení rozsahu. To může vést k tomu, že sloupec rovnosti bude přiřazen ke košům nebo vyloučen z optimalizace, což sníží výkon.

Chcete-li zajistit, aby se optimalizace spojení podle rozsahu použila pouze na požadovanou podmínku rozsahu, přetypujte sloupce číselné rovnosti na STRING. Tím jsou vyloučeny z posuzování jako sloupce podmínek rozsahu.

SELECT /*+ RANGE_JOIN(reference, 3306084) */
    reference.*, position.*
FROM position
INNER JOIN reference
    ON CAST(position.parent_index AS STRING) = CAST(reference.parent_index AS STRING)
    AND position.child_index BETWEEN reference.min_child_index AND reference.max_child_index;

Stejný vzor platí i pro jiné číselné sloupce používané jako klíče rovnosti, například DATE, celočíselné identifikátory nebo sloupce skupinových oddílů.

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í koše 10 rozdělí doménu do intervalů 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 binu 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 pomocí nápovědy pro spojení rozsahu v dotazu nebo nastavením parametru konfigurace relace. V Databricks SQL se velikost přihrádky automaticky odvozuje, když je povolená automatická optimalizace spojení rozsahu.

Automatická optimalizace intervalového spojení

V Databricks SQL služba Azure Databricks automaticky rozpozná odpovídající spojení podle rozsahu a určí optimální velikost binu vzorkováním tabulky intervalů. Tím se odstraňuje nutnost ručně určovat velikost bucketu pomocí hintů nebo konfigurace relace.

Ve výchozím nastavení je v Databricks SQL povolená automatická optimalizace spojení podle rozsahu. Pokud ho chcete zakázat, nastavte následující konfiguraci:

SET spark.databricks.optimizer.autoRangeJoin.enabled = false;

Pokud zadáte velikost přihrádky prostřednictvím nápovědy pro spojení rozsahu nebo konfigurace relace, tato hodnota přepíše automaticky odvozenou velikost přihrádky.

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

Chcete-li v dotazu SQL povolit optimalizaci range join, použijte k určení velikosti binu hint range join. Nápověda musí obsahovat název relace jedné ze spojených relací a parametr velikosti číselného koše. 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, zadejte velikost přihrádky 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 koše vede k většímu počtu košů, což pomáhá při filtrování potenciálních shod. Pokud je však velikost přihrádky výrazně menší než zjištěné intervaly hodnot a intervaly hodnot se překrývají napříč několika intervaly přihrádek, stává se to neefektivní. 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
  map_from_arrays(
    ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999),
    APPROX_PERCENTILE(
      end::DOUBLE - start::DOUBLE,
      ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)
    )
  ) AS bin_sizes
FROM
  ranges;

Přetypování každého sloupce na DOUBLE před odečtením zajistí, že dotaz bude fungovat bez ohledu na to, zda sloupce obsahují číselné hodnoty, DATE nebo TIMESTAMP hodnoty.

Doporučené nastavení šířky intervalu by mělo být rovno maximu z hodnoty na úrovni 90. percentilu, hodnoty na úrovni 99. percentilu dělené 10 nebo hodnoty na úrovni 99,9. percentilu dělené 100 atd. Důvod je následující:

• Pokud je hodnota na 90. percentilu velikost koše, pouze 10 % délek hodnotových intervalů je delších než interval koše, takže se rozprostírají přes více než 2 sousední intervaly koše.
• Pokud je hodnota v 99. percentilu velikost koše, pak pouze 1% délek intervalů hodnot přesahuje více než 11 sousedních intervalů košů.
• Pokud je hodnota na 99,9. percentilu velikost binu, pouze 0,1 % intervalů délky hodnoty přesahují více než 101 sousedních binů.
• Stejný postup lze v případě potřeby zopakovat pro hodnoty na úrovni 99,99. a 99,999. percentilu atd.

Popsaná metoda omezuje množství zkosených dlouhých intervalů hodnot, které překrývají více binových 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.