Optymalizacja sprzężenia zakresu

Sprzężenia zakresu występują, gdy dwie relacje są połączone przy użyciu punktu w interwale lub interwału nakładających się warunków. Obsługa optymalizacji sprzężenia zakresu w środowisku Databricks Runtime może przynieść kolejność poprawy wielkości w wydajności zapytań, ale wymaga starannego dostrajania ręcznego.

Sprzężenia zakresu interwałów

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. 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ładanie się interwałów między dwiema wartościami z każdej relacji. 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:

  • Warunek, który można interpretować jako punkt w interwałach lub interwałach nakładających się na siebie sprzężenia zakresu.
  • Wszystkie wartości związane z warunkiem sprzężenia zakresu mają typ liczbowy (całkowitoliczbowy, zmiennoprzecinkowy, dziesiętny), DATElub TIMESTAMP.
  • Wszystkie wartości związane z warunkiem sprzężenia zakresu są tego samego typu. W przypadku typu dziesiętnego wartości muszą być również takie same, jak skala i precyzja.
  • Jest to INNER JOINelement , lub w przypadku sprzężenia zakresu interwałów z LEFT OUTER JOIN wartością punktową po lewej stronie lub RIGHT OUTER JOIN z wartością punktu po prawej stronie.
  • Parametr dostrajania rozmiaru pojemnika.

Rozmiar kwantu

Rozmiar pojemnika to parametr dostrajania liczbowego, który dzieli domenę wartości warunku zakresu na wiele pojemników o równym rozmiarze. Na przykład z rozmiarem pojemnika 10 optymalizacja dzieli domenę na przedziały, które są interwałami długości 10. Jeśli masz punkt w stanie p BETWEEN start AND endzakresu i start wynosi 8 i end wynosi 22, ten interwał wartości nakłada się z trzema pojemnikami o długości 10 – pierwszym pojemnikiem od 0 do 10, drugim pojemnikiem od 10 do 20, a trzecim pojemnikiem 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. Jeśli na przykład p wynosi 32, można wykluczyć spadek z zakresu od start 8 end do 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 sekunda. Jeśli wymagana jest wartość podrzędna, 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 ustawiając parametr konfiguracji sesji. Optymalizacja sprzężenia zakresu jest stosowana tylko wtedy, gdy ręcznie określisz rozmiar pojemnika. Sekcja Wybierz rozmiar 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ó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 podquerią.

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. Wynika to z faktu, że sprzężenia są pozostawione 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.sparkContext
  .parallelize(((0,  60), (60, 120)))
  .toDF(StructType([
    StructField('minute_start', IntegerType()),
    StructField('minute_end', IntegerType())
  ]))
)

#create events table
events = (spark.sparkContext
  .parallelize(((12, 33),
    (0,  120),
    (33, 72),
    (65, 178)))
  .toDF(StructType([
    StructField('event_start', IntegerType()),
    StructField('event_end', IntegerType())
    ]))
)

#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 ustawiony przez parametr .

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 pomaga w filtrowaniu 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, 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 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

Zalecane ustawienie rozmiaru pojemnika będzie maksymalną wartością 90. percentylu lub wartością 99. percentylu podzieloną przez 10, lub wartością 99,9 percentyla podzieloną przez 100 i tak dalej. Uzasadnienie jest następujące:

  • Jeśli wartość na 90. percentyl jest rozmiarem pojemnika, tylko 10% długości interwału wartości jest dłuższe niż interwał przedziału bin, 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 ponad 11 sąsiednich interwałów pojemników.
  • Jeśli wartość na 99,9 percentyl jest rozmiarem pojemnika, tylko 0,1% długości interwału wartości obejmuje ponad 101 sąsiednich interwałów pojemników.
  • To samo można powtórzyć dla wartości na poziomie 99,99, 99,999 percentylu 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.