Optymalizacja sprzężeń w zakresach

Sprzężenia zakresu występują, gdy dwa relacje są łączone przy użyciu warunku interwału lub interwału nakładania się. Obsługa optymalizacji sprzężenia zakresu w środowisku Databricks Runtime może przynieść szereg wielkości poprawy wydajności zapytań, ale wymaga starannego dostrajania ręcznego.

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

Sprzężenia zakresu interwałów punkt-interwał

Sprzężenie zakresu interwałów to sprzężenie, w którym warunek zawiera predykaty określające, że wartość z jednej relacji znajduje się między dwiema 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;

Sprzężenia zakresu nakładających się interwałów

Interwał nakładający się sprzężenia zakresu to sprzężenie , w którym warunek zawiera predykaty określające nakładające się interwały mię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 sprzężeń w zakresach

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

• Mają warunek, który można interpretować jako punkt w interwale lub interwałach nakładających się sprzężenia zakresu.
• Wszystkie wartości biorące udział w warunku sprzężenia zakresu mają typ liczbowy (całkowitoliczbowy, zmiennoprzecinkowy, dziesiętny), DATElub TIMESTAMP.
• Wszystkie wartości biorące udział 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 sprzężenia INNER JOINtypu , lub w przypadku sprzężenia zakresu interwałów, wartości LEFT OUTER JOIN z wartością punktu po lewej stronie lub RIGHT OUTER JOIN wartością punktu 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 start end 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, muszą być brane pod uwagę jako możliwe dopasowania sprzężenia 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 DATE wartości wartość rozmiaru pojemnika jest interpretowana jako dni. Na przykład wartość rozmiaru pojemnika 7 reprezentuje tydzień.
• W przypadku TIMESTAMP wartości wartość rozmiaru pojemnika jest interpretowana jako sekundy. Jeśli wymagana jest wartość sekundy podrzędnej, można użyć wartości ułamkowych. Na przykład wartość rozmiaru pojemnika 60 reprezentuje minutę, a wartość rozmiaru pojemnika 0,1 reprezentuje 100 milisekund.

Rozmiar pojemnika można określić przy użyciu wskazówek sprzężenia zakresu 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)

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 dotyczy a sprzężenia z a elementem b , a nie sprzężenia z elementem 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ć wskazówkę sprzężenia zakresu na jednej ze sprzężonych 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ę sprzężenia zakresu zawsze zastępuje ten zestaw 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. Jeśli jednak długość interwału jest różna i niesymetryczna, należy znaleźć równowagę, aby ustawić rozmiar pojemnika, który filtruje krótkie interwały wydajnie, jednocześnie uniemożliwiając długie interwały nakładania się zbyt wielu pojemników. Przy założeniu, że tabela ranges, z interwałami między kolumnami start i end, można określić różne percentyle wartości niesymetrycznej długości 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.