Optymalizacja sprzężenia zakresu

Sprzężenia zakresu występuje, gdy dwa relacje są łączone przy użyciu punktu w interwale lub interwału nakładania się warunku. Obsługa optymalizacji sprzężenia zakresu w środowisku Databricks Runtime może spowodować zwiększenie wydajności zapytań o kolejność wielkości, ale wymaga dokładnego ręcznego dostrajania.

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

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

Sprzężenie zakresu nakładania się interwałów to sprzężenie, w którym warunek zawiera predykaty określające nakładanie się interwałów 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ężenia zakresu

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

• Mają warunek, który może być interpretowany jako punkt w interwałach 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 INNER JOIN, lub w przypadku sprzężenia zakresu interwałów, LEFT OUTER JOIN z wartością punktu po lewej stronie lub 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ę wartości warunku zakresu na wiele pojemników o takim samym rozmiarze. Na przykład w przypadku rozmiaru pojemnika 10 optymalizacja dzieli domenę na przedziały o długości 10. Jeśli masz punkt w stanie p BETWEEN start AND endzakresu i start wynosi 8 i end jest 22, ten interwał wartości nakłada się z trzema przedziałami o 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 mieszczą się w tych samych trzech przedziałach, należy uznać za możliwe dopasowania sprzężenia dla tego interwału. Na przykład jeśli p wartość wynosi 32, można wykluczyć, że spada z przedziału 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ść podrzędna sekundy, 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ówki dotyczącej sprzężenia zakresu w zapytaniu lub przez ustawienie parametru 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ówki dotyczącej sprzężenia zakresu

Aby włączyć optymalizację sprzężenia zakresu w zapytaniu SQL, możesz użyć wskazówek sprzężenia zakresu , aby określić rozmiar pojemnika. Wskazówka musi zawierać nazwę relacji jednego 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 c. Jest to spowodowane tym, że sprzężenia są pozostawione jako asocjacyjne, więc zapytanie jest interpretowane jako (a JOIN b) JOIN c, a wskazówka dotyczy a sprzężenia a z 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 .

Wybierz rozmiar 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 pojemnika . Na przykład z warunkiem p BETWEEN start AND end, gdzie start wynosi 1000 000 i end wynosi 1999 999 999, a rozmiar pojemnika 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źć saldo, aby ustawić rozmiar pojemnika, który skutecznie filtruje krótkie interwały, uniemożliwiając długie interwały nakładania się zbyt wielu pojemników. Przy założeniu, że tabela rangeszawiera interwały między kolumnami start i end, moż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

Zalecanym ustawieniem rozmiaru pojemnika jest wartość maksymalna na poziomie 90. percentyla lub wartość w 99. percentylu podzielona przez 10 lub wartość na poziomie 99,9 percentyla podzielona 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, dlatego 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łów wartości obejmuje więcej niż 11 sąsiednich interwałów pojemnika.
• Jeśli wartość na 99,9 percentylu jest rozmiarem pojemnika, tylko 0,1% długości interwałów wartości obejmuje więcej niż 101 sąsiednich interwałów pojemnika.
• To samo można powtórzyć dla wartości z 99,99, 99,999. percentyla i tak dalej, jeśli jest to konieczne.

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