Optimalizace rozsahu spojení

range join spojuje dvě tabulky za podmínky, která kontroluje, zda hodnota spadá do určitého rozsahu, nebo zda se dva rozsahy překrývají, místo aby kontrolovala, zda jsou dvě hodnoty stejné. Například dotaz, který přiřazuje každou událost webu k uživatelské relaci, ve které se stala, kontroluje, zda časové razítko události leží mezi začátkem a koncem relace. Dotaz, který najde schůzky naplánované ve stejnou dobu, kontroluje, zda se dva časové rozmezí překrývají. Optimalizace spojení podle rozsahu v Databricks Runtime může výrazně zlepšit výkon těchto dotazů.

Kvůli tomu může join porovnávat mnoho párů řádků, které se nemohou shodovat. Optimalizace pro propojení rozsahu rozděluje rozsah hodnot do binů a porovnává pouze řádky, které spadají do překrývajících se binů. Viz velikost binu.

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 pro spojení rozsahů se vztahuje na spojení, která:

  • Mít bod v intervalu nebo podmínku spojení na základě překrývání intervalů.
  • Používejte v podmínce spojení rozsahu pouze číselné hodnoty (integrální, s plovoucí řádovou čárkou nebo desetinné), DATE nebo TIMESTAMP.
  • Použijte hodnoty stejného typu v podmínce range join. Desetinné hodnoty musí mít také stejný počet desetinných míst a přesnost.
  • Použijte podporovaný typ spojení:
    • INNER JOIN, pro libovolný range join
    • LEFT OUTER JOIN s bodovou hodnotou na levé straně nebo RIGHT OUTER JOIN s bodovou hodnotou na pravé straně, pouze pro spojení bodu s intervalem
  • Velikost lze odvodit automaticky nebo zadat ručně.

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. Pro hodnotu kratší než jedna sekunda použijte desetinnou hodnotu. 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. Pro deaktivaci nastavte tuto 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 je název tabulky, pohledu nebo poddotazu.

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. Malá velikost binu se stává neefektivní, pokud jsou intervaly hodnot mnohem větší než velikost binu, což způsobuje, že intervaly se překrývají s mnoha biny. Například za podmínky p BETWEEN start AND end, kde start je 1 000 000 a end je 1 999 999, a velikosti binu 10, se interval překrývá se 100 000 biny.

Pokud je délka intervalu poměrně jednotná a známá, nastavte šířku třídy na typickou délku intervalu. Pro proměnné, asymetricky rozložené intervaly zvolte velikost binu, která efektivně filtruje krátké intervaly a zároveň zabraňuje překrývání dlouhých intervalů s příliš mnoha biny. Pro tabulku ranges s intervaly mezi sloupci start a end určete percentily asymetricky rozložené délky intervalů pomocí tohoto 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.

Nastavte velikost binu na největší z těchto hodnot:

  • 90. percentil délek intervalů. Při této velikosti maximálně 10 % intervalů pokrývají více než 2 biny.
  • 99. percentil děleno 10. Při této velikosti maximálně 1 % intervalů přesahuje 11 košů.
  • 99,9. percentil dělený 100. Při této velikosti nejvýše 0,1 % intervalů zasahuje do více než 101 binů.

Pro omezení ještě delších intervalů pokračujte v tomto vzoru s 99,99. a 99,999. percentily. Použijte výsledek jako výchozí bod a pak upravte velikost přihrádky podle svého zatížení.

  • Poslední aktualizace v