Optymalizacja połączenia zakresowego

Sprzężenie zakresu występuje, gdy dwa relacje są łączone za pomocą punktu w interwale lub warunku nakładania się interwałów. Optymalizacja sprzężenia zakresu w środowisku Databricks Runtime może przynieść znaczne poprawy wydajności zapytań, ale wymaga starannego ręcznego dostrajania.

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

Łączenie zakresu interwałów punktowych

Sprzężenie typu punkt w zakresie interwałów to sprzężenie, w którym warunek zawiera predykaty wskazujące, że wartość z jednej relacji znajduje się pomiędzy dwoma wartościami z drugiej 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;

Łączenie nakładających się zakresów interwałów

Sprzężenie z zakresem nakładających się interwałów to sprzężenie, w którym warunek zawiera predykaty określające nakładanie się interwałów pomiędzy dwiema wartościami 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 połączenia zakresowego

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

• Mają warunek, który można interpretować jako punkt w przedziale lub nakładające się łączenie zakresów.
• Wszystkie wartości biorące udział w warunku sprzężenia zakresu mają typ liczbowy (całkowitoliczbowy, zmiennoprzecinkowy, dziesiętny), DATElub TIMESTAMP.
• Wszystkie wartości występujące w warunku sprzężenia zakresu są tego samego typu. W przypadku typu dziesiętnego wartości również muszą mieć taką samą skalę i precyzję.
• Jest to INNER JOIN, lub w przypadku punktu w zakresie interwałów, LEFT OUTER JOIN z wartością punktową po lewej stronie lub RIGHT OUTER JOIN z wartością punktową po prawej stronie.
• Parametr dostrajania rozmiaru pojemnika.

Rozmiar pojemnika

Rozmiar pojemnika jest parametrem dostrajania liczbowego, który dzieli domenę wartości 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 te punkty, które znajdują się w tych samych trzech przedziałach, muszą być brane pod uwagę jako możliwe dopasowania połączeń 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.

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

Włączanie sprzężenia zakresu przy użyciu wskazówek sprzężenia zakresu

Aby włączyć optymalizację sprzężenia zakresu w zapytaniu SQL, możesz użyć wskazówki dotyczącej sprzężenia zakresu w celu określenia rozmiaru pojemnika. Wskazówka musi zawierać nazwę relacji jednej ze sprzężonych relacji i parametr rozmiaru pojemnika liczbowego. Nazwa relacji może być tabelą, widokiem lub podzapytaniem.

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)

#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ć wskazówkę dotyczącą łączenia zakresu na 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łączanie dołączania zakresu 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 sprzężenia z warunkiem zakresu. Jednak inny rozmiar pojemnika ustawiony przez wskazówkę łączenia w zakresie zawsze zastępuje ustawiony za pomocą parametru.

Wybieranie rozmiaru pojemnika

Skuteczność optymalizacji sprzężenia 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, gdzie start wynosi 1000 000 i end wynosi 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 ustawienie rozmiaru pojemnika na typową oczekiwaną długość interwału wartości. Jednakże, jeśli długość interwału jest różna i niesymetryczna, trzeba znaleźć równowagę, aby ustawić rozmiar przedziału, który skutecznie filtruje krótkie interwały, jednocześnie zapobiegając nakładaniu się długich interwałów na zbyt wiele przedziałów. Zakładając tabelę ranges z interwałami między kolumnami start i end, można określić różne percentyle wartości długości niesymetrycznego interwału za pomocą 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 wartości z 99,99, 99,999. percentyla itd., 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.