Optymalizacja join zakresu

Zakres join występuje, gdy dwa relacje są łączone przy użyciu warunku, w którym punkt nakłada się na interwał lub nakładają się interwały. Wsparcie dla optymalizacji zakresu join w środowisku Databricks Runtime może przynieść znaczące poprawy wydajności zapytań, ale wymaga ostrożnego, ręcznego dopasowania.

Usługa Databricks zaleca używanie wskazówek join dla sprzężeń zakresu, gdy wydajność jest niska.

Punkt w zakresie interwału join

Punkt w zakresie interwału join to join, gdzie warunek obejmuje predykaty określające, że wartość z jednej relacji znajduje się między dwoma values z innej relacji. Na przykład:

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

Zakres nakładających się interwałów join

Zakres nakładających się interwałów jest joinjoin, w którym warunek zawiera predykaty określające nakładające się interwały między dwoma values z każdej relacji. Na przykład:

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

Optymalizacja join zakresu

Optymalizacja zakresu join jest wykonywana dla sprzężeń, które:

• Mieć warunek, który można interpretować jako punkt w przedziale lub w zakresie nakładania się przedziałów join.
• Wszystkie values związane z warunkiem join zakresu mają typ liczbowy (całkowity, zmiennoprzecinkowy, dziesiętny), DATElub TIMESTAMP.
• Wszystkie values biorące udział w warunku join zakresu są tego samego typu. W przypadku typu dziesiętnego values również musi mieć taką samą skalę i precyzję.
• Jest to INNER JOINlub, jeśli chodzi o punkt w zakresie interwału join, LEFT OUTER JOIN z wartością punktu po lewej stronie albo RIGHT OUTER JOIN z wartością punktu po prawej stronie.
• Parametr dostrajania rozmiaru pojemnika.

Rozmiar pojemnika

Rozmiar pojemnika jest parametrem dostrajania liczbowego, który dzieli domenę values warunku zakresu na wiele pojemników o równym rozmiarze. Na przykład z rozmiarem pojemnika wynoszącym 10 optymalizacja dzieli domenę na przedziały o długości 10. Jeśli masz punkt w stanie p BETWEEN start AND endzakresu , i wynosi 8 i startend wynosi 22, ten interwał wartości nakłada się z trzema przedziałami długości 10 — pierwszym pojemnikiem z zakresu od 0 do 10, drugim pojemnikiem z zakresu od 10 do 20, a trzecim przedziałem od 20 do 30. Tylko punkty, które należą do tych samych trzech pojemników, należy uznać za możliwe, join dopasowania dla tego interwału. Na przykład, jeśli p ma wartość 32, można wykluczyć spadek z zakresu od start 8 do end 22, ponieważ mieści się w pojemniku z zakresu od 30 do 40.

Uwaga

• W przypadku DATEvalueswartość rozmiaru pojemnika jest interpretowana jako dni. Na przykład wartość rozmiaru pojemnika 7 reprezentuje tydzień.
• W przypadku TIMESTAMPvalueswartość rozmiaru pojemnika jest interpretowana jako sekundy. Jeśli wymagana jest wartość poniżej sekundy, można użyć ułamków values. Na przykład wartość rozmiaru pojemnika 60 reprezentuje minutę, a wartość rozmiaru pojemnika 0,1 reprezentuje 100 milisekund.

Rozmiar pojemnika można określić, używając wskazówki zakresu join w zapytaniu lub ustawiając parametr konfiguracji sesji. Optymalizacja join zakresu jest stosowana tylko wtedy, gdy ręcznie określisz rozmiar pojemnika. Sekcja Wybieranie rozmiaru pojemnika opisuje sposób wybierania optymalnego rozmiaru pojemnika.

Włącz zakres join przy użyciu wskazówki zakresu join

Aby włączyć optymalizację join zakresu w zapytaniu SQL, możesz użyć zakresu join wskazówek, aby określić rozmiar pojemnika. Wskazówka musi zawierać nazwę relacji jednej ze sprzężonych relacji i parametr rozmiaru pojemnika liczbowego. Nazwa relacji może być table, widok lub podzapytywanie.

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)

Uwaga

W trzecim przykładzie należy umieścić wskazówkę na stronie c. Dzieje się tak dlatego, że sprzężenia pozostają asocjacyjne, więc zapytanie jest interpretowane jako (a JOIN b) JOIN c, a wskazówka dotycząca a dotyczy joina z b, a nie join z 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()
)

Możesz również umieścić zakres join podpowiedź dotyczącą jednej z połączonych ramek danych. W takim przypadku wskazówka zawiera tylko parametr rozmiaru pojemnika liczbowego.

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

Włącz zakres join przy użyciu konfiguracji sesji

Jeśli nie chcesz modyfikować zapytania, możesz określić rozmiar pojemnika jako parametr konfiguracji.

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

Ten parametr konfiguracji ma zastosowanie do dowolnego join z parametrem zakresu. Jednak inny rozmiar pojemnika set w zakresie join jako podpowiedź zawsze zastępuje ten set przez parametr.

Wybieranie rozmiaru pojemnika

Skuteczność optymalizacji join zakresu zależy od wyboru odpowiedniego rozmiaru pojemnika.

Mały rozmiar pojemnika powoduje większą liczbę pojemników, co ułatwia filtrowanie potencjalnych dopasowań. Jednak staje się nieefektywny, jeśli rozmiar pojemnika jest znacznie mniejszy niż napotkane interwały wartości, a interwały wartości nakładają się na wiele interwałów pojemników . Na przykład z warunkiem p BETWEEN start AND end, wherestart wynosi 1000 000, a end to 1999 999 999, a rozmiar pojemnika wynosi 10, interwał wartości nakłada się na 100 000 pojemników.

Jeśli długość interwału jest dość jednolita i znana, zalecamy set rozmiar pojemnika do typowej oczekiwanej długości interwału wartości. Jeśli jednak długość interwału jest różna i niesymetryczna, należy znaleźć równowagę, aby set rozmiar pojemnika, który skutecznie filtruje krótkie interwały, jednocześnie uniemożliwiając długie interwały nakładania się zbyt wielu pojemników. Przy założeniu tablerangesz interwałami między columnsstart a endmożna określić różne percentyle wartości niesymetrycznej długości interwału przy użyciu następującego zapytania:

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

Zalecane ustawienie rozmiaru pojemnika będzie maksymalną wartością 90. percentyla lub wartością 99. percentyla podzieloną przez 10 lub wartością na poziomie 99,9 percentyla podzieloną przez 100 itd. Uzasadnieniem jest:

• Jeśli wartość na 90. percentyl jest rozmiarem pojemnika, tylko 10% długości interwału wartości jest dłuższe niż interwał pojemnika, więc obejmuje więcej niż 2 sąsiadujące interwały pojemników.
• Jeśli wartość na 99. percentyl jest rozmiarem pojemnika, tylko 1% długości interwału wartości obejmuje więcej niż 11 sąsiednich interwałów pojemników.
• Jeśli wartość na 99,9 percentyl jest rozmiarem pojemnika, tylko 0,1% długości interwałów wartości obejmuje więcej niż 101 sąsiednich interwałów pojemników.
• To samo można powtórzyć dla values na poziomie 99,99-tego oraz 99,999-tego percentyla, jeśli jest to konieczne.

Opisana metoda ogranicza ilość niesymetrycznych interwałów wartości długich, które nakładają się na wiele interwałów pojemników. Wartość rozmiaru pojemnika uzyskana w ten sposób jest tylko punktem wyjścia do precyzyjnego dostrajania; rzeczywiste wyniki mogą zależeć od określonego obciążenia.