Optymalizacja połączenia zakresowego

Złączenie zakresowe łączy dwie tabele pod warunkiem, który sprawdza, czy dana wartość mieści się w danym zakresie, czy też dwa zakresy się nakładają, zamiast sprawdzać, czy dwie wartości są równe. Na przykład zapytanie, które dopasowuje każde zdarzenie strony do sesji użytkownika, w której się wydarzyło, sprawdza, czy znacznik czasu zdarzenia mieści się pomiędzy godzinami rozpoczęcia i zakończenia sesji. Zapytanie, które znajduje spotkania zaplanowane w tym samym czasie, sprawdza, czy dwa przedziały czasowe się nakładają. Optymalizacja złączenia zakresowego w Databricks Runtime może znacznie poprawić wydajność tych zapytań.

Z tego powodu łączenie może porównywać wiele par wierszy, które nie pasują. Optymalizacja łączenia zakresu dzieli zakres wartości na kosze i porównuje tylko wiersze, które mieszczą się w nakładających się koszach. Zobacz rozmiar kosza.

W usłudze Databricks SQL platforma Azure Databricks automatycznie optymalizuje złączenia zakresowe bez konieczności ręcznej konfiguracji. Można również ręcznie dostrajać łączenia zakresowe przy użyciu wskazówek dotyczących łączenia lub konfiguracji sesji dla wszystkich typów zasobów obliczeniowych.

Łączenie zakresu interwałów punktowych

Złączenie punktu z zakresem przedziału to złączenie, którego 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;

Łą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 łączenia zakresu dotyczy połączeń, które:

• Mieć punkt w przedziale lub warunek złączenia nakładających się przedziałów.
• Używaj wyłącznie wartości liczbowych (całkowitych, zmiennoprzecinkowych lub dziesiętnych), DATE lub wartości TIMESTAMP w warunku łączenia zakresu.
• Użyj wartości tego samego typu w warunku złączenia zakresowego. Wartości dziesiętne muszą mieć tę samą skalę i precyzję.
• Użyj obsługiwanego typu połączenia:
  • INNER JOIN, dla dowolnego range join
  • LEFT OUTER JOIN z wartością punktową po lewej stronie lub RIGHT OUTER JOIN z wartością punktową po prawej stronie, tylko w przypadku sprzężeń punkt‑w‑przedziale
• Ustaw rozmiar pojemnika, automatycznie wyznaczony lub ręcznie określony.

Sprzężenia z równością liczbową i warunkami zakresu

Gdy warunek sprzężenia zawiera zarówno warunek równości w kolumnie liczbowej, jak i warunek zakresu, optymalizator może zastosować kwantowanie do kolumny równości liczbowej, ponieważ spełnia wymagania dotyczące typu optymalizacji sprzężenia zakresu. Może to spowodować przypisanie kolumny równości do pojemników lub wykluczenie jej z optymalizacji, co zmniejsza wydajność.

Aby mieć pewność, że optymalizacja łączenia zakresowego będzie stosowana tylko do docelowego warunku zakresowego, rzutuj kolumny numeryczne używane w warunku równości na STRING. To wyklucza je z uwzględniania jako kolumn warunku zakresowego.

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;

Ten sam wzorzec ma zastosowanie do innych kolumn liczbowych używanych jako klucze równości, takie jak DATE, identyfikatory liczb całkowitych lub kolumny partycji klastrowanej.

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 end zakresu, i start wynosi 8, a end wynosi 22, ten interwał wartości nakłada się z trzema przedziałami o długości 10 — pierwszy przedział od 0 do 10, drugi przedział od 10 do 20, a trzeci przedział 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ć jako należący do zakresu od start 8 do end 22, ponieważ znajduje się w przedziale od 30 do 40.

Rozmiar pojemnika można określić przy użyciu wskazówki dotyczącej sprzężenia zakresu w zapytaniu lub ustawiając parametr konfiguracji sesji. W usłudze Databricks SQL rozmiar przedziału jest automatycznie wyznaczany po włączeniu automatycznej optymalizacji złączenia zakresowego.

Automatyczna optymalizacja łączenia zakresów

W usłudze Databricks SQL Azure Databricks automatycznie wykrywa sprzężenia zakresu kwalifikującego i uzyskuje optymalny rozmiar pojemnika przez próbkowanie tabeli interwałów. Spowoduje to usunięcie konieczności ręcznego określenia rozmiaru pojemnika za pomocą wskazówek lub konfiguracji sesji.

Optymalizacja złączeń zakresowych jest domyślnie włączona w usłudze Databricks SQL. Aby ją wyłączyć, ustaw taką konfigurację:

SET spark.databricks.optimizer.autoRangeJoin.enabled = false;

Jeśli określisz wielkość przedziału za pomocą wskazówki dla złączenia zakresowego lub konfiguracji sesji, ta wartość zastępuje wielkość przedziału automatycznie wyznaczoną.

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

Aby włączyć optymalizację złączenia zakresowego w zapytaniu SQL, użyj wskazówki złączenia zakresowego, aby określić rozmiar przedziału. Wskazówka musi zawierać nazwę relacji jednej ze sprzężonych relacji i parametr rozmiaru pojemnika liczbowego. Nazwa relacji to tabela, widok lub podzapytanie.

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, określ 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ń. Mały rozmiar binu staje się nieefektywny, jeśli przedziały wartości są znacznie większe niż rozmiar binu, powodując, że przedziały nakładają się na wiele binów. Na przykład przy warunku p BETWEEN start AND end, gdzie start wynosi 1 000 000 i end wynosi 1 999 999, oraz przy rozmiarze binu 10, przedział nakłada się na 100 000 binów.

Jeśli długość przedziału jest dość jednorodna i znana, ustaw szerokość koszyka na typową długość przedziału. Dla zmiennych, skośnych przedziałów wybierz szerokość koszyka, która efektywnie filtruje krótkie przedziały, jednocześnie zapobiegając nakładaniu się długich przedziałów na zbyt wiele koszyków. Dla tabeli ranges z przedziałami między kolumnami start a end, określ percentyle skośnej długości przedziału za pomocą tego zapytania:

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;

Rzutowanie każdej kolumny do typu DOUBLE przed odjęciem gwarantuje, że zapytanie działa niezależnie od tego, czy kolumny zawierają wartości liczbowe, DATE lub TIMESTAMP.

Ustaw rozmiar kosza na największą z tych wartości:

• 90. percentyl długości przedziałów. Przy tym rozmiarze maksymalnie 10% przedziałów obejmuje więcej niż 2 przedziały.
• Percentyl 99 podzielony przez 10. Przy tym rozmiarze co najwyżej 1% przedziałów obejmuje ponad 11 przedziałów.
• 99,9. percentyl podzielony przez 100. Przy tym rozmiarze maksymalnie 0,1% przedziałów obejmuje ponad 101 koszyków.

Aby ograniczyć występowanie jeszcze dłuższych interwałów, kontynuuj ten schemat z percentylami 99,99. i 99,999. Wykorzystaj wynik jako punkt wyjścia, a potem dostosuj rozmiar koszyka do swojego obciążenia.