범위 join 최적화

두 관계를 구간 내 특정 지점 또는 구간 겹침 조건을 사용하여 조인할 때 범위 join가 발생합니다. Databricks 런타임의 범위 join 최적화 지원으로 쿼리 성능이 대폭 향상될 수 있지만 신중한 수동 튜닝이 필요합니다.

Databricks는 성능이 저하되는 경우 범위 조인에 join 힌트를 사용하는 것이 좋습니다.

간격 범위 내의 지점 join

간격 범위 내의 지점은 한 관계의 값이 다른 관계의 두 사이에 있음을 설명하는 조건자가 포함된 입니다. 예시:

-- 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;

간격 겹침 범위 join

간격 겹침 범위 join는 두 관계의 각 values 사이의 간격이 중첩된다고 지정하는 조건자를 포함하는 join입니다. 예시:

-- 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;

범위 join 최적화

범위 join 최적화는 다음과 같은 조인에 대해 수행됩니다.

• 간격 또는 간격 겹침 범위로 해석될 수 있는 조건 join.
• 범위 join 조건에 관련된 모든 values는 숫자 형식(정수, 부동 소수점, 소수) 또는 DATE, TIMESTAMP에 속합니다.
• 범위 join 조건에 관련된 모든 values는 동일한 형식입니다. 소수점 형식의 경우, values 또한 동일한 배율과 정밀도를 가져야 합니다.
• 간격 범위 join의 경우, 이는 INNER JOIN이고, 왼쪽에 점 값이 있는 LEFT OUTER JOIN이거나 오른쪽에 점 값이 있는 RIGHT OUTER JOIN입니다.
• Bin 크기 튜닝 매개 변수가 있습니다.

Bin 크기

bin 크기은 범위 조건의 values 도메인을 동일한 크기의 여러 bin으로 분할하는 숫자 튜닝 매개 변수입니다. 예를 들어, bin 크기가 10인 경우 최적화는 도메인을 길이가 10인 간격인 bin으로 분할합니다. p BETWEEN start AND end의 범위 조건에 포인트가 있고 start가 8이고 end가 22인 경우 이 값 간격은 길이가 10인 3개의 bin(0에서 10까지의 첫 번째 bin, 10에서 20까지의 두 번째 bin, 20에서 30까지의 세 번째 bin)과 겹칩니다. 동일한 3개의 bin 내에 속하는 지점만 해당 간격에 대한 일치를 join 가능한 것으로 간주해야 합니다. 예를 들어, p가 32인 경우 30에서 40 사이의 bin에 속하므로 start가 8이고 end가 22인 사이에 속하는 것으로 배제할 수 있습니다.

쿼리에서 범위 join 힌트를 사용하거나 세션 구성 매개 변수를 설정하여 bin 크기를 지정할 수 있습니다. 범위 최적화는 bin 크기를 수동으로 지정하는 경우에만 적용됩니다. 섹션 bin 크기 선택에서는 최적의 bin 크기를 선택하는 방법을 설명합니다.

범위 join을(를) 범위 join 힌트를 사용하여 활성화하세요.

SQL 쿼리에서 범위 join 최적화를 사용하도록 설정하려면 범위 join 힌트 사용하여 bin 크기를 지정할 수 있습니다. 힌트는 조인된 관계 중 하나의 관계 이름과 숫자 bin 크기 매개 변수를 포함해야 합니다. 관계 이름은 table, 뷰 또는 하위 쿼리일 수 있습니다.

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()
)

조인된 DataFrame 중 하나에 범위 join 힌트를 배치할 수도 있습니다. 이 경우 힌트에는 숫자 bin 크기 매개 변수만 포함됩니다.

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")

세션 구성을 통해 범위 join 활성화

쿼리를 수정하지 않으려면 bin 크기를 구성 매개 변수로 지정할 수 있습니다.

SET spark.databricks.optimizer.rangeJoin.binSize=5

이 구성 매개 변수는 범위 조건이 있는 모든 join 적용됩니다. 그러나 범위 join 힌트를 통하여 설정된 set 다른 bin 크기는 항상 매개 변수로 설정한 set 값을 재정의합니다.

bin 크기 선택

범위 join 최적화의 효과는 적절한 bin 크기를 선택하는 데 따라 달라집니다.

Bin 크기가 작으면 더 많은 수의 bin이 생성되어 잠재적인 일치 항목을 필터링하는 데 도움이 됩니다. 그러나 bin 크기가 발생한 값 간격보다 훨씬 작고 값 간격이 여러 bin 간격과 겹치면 비효율적입니다. 예를 들어 조건 p BETWEEN start AND endwherestart 1,000,000이고 end 1,999,999이고 bin 크기가 10이면 값 간격이 100,000개의 bin과 겹칩니다.

간격의 길이가 상당히 균일하고 알려진 경우, bin 크기를 값 간격의 전형적인 예상 길이에 맞추어 set 하는 것이 좋습니다. 그러나 간격의 길이가 다양하고 기울어져 있는 경우, 짧은 간격을 효율적으로 필터링하는 bin 크기를 set하여, 긴 간격이 너무 많은 bin과 겹치지 않도록 하는 균형을 찾아야 합니다. columnsstart 간격과 end사이의 간격이 있는 tableranges를 가정하면, 다음 쿼리를 사용하여 기울어진 간격 길이 값의 백분위수를 확인할 수 있습니다.

SELECT APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges

권장되는 bin 크기 설정은 90번째 백분위수 값의 최댓값 또는 99번째 백분위수 값을 10으로 나눈 값 또는 99.9번째 백분위수 값을 100으로 나눈 값 등입니다. 근거는 다음과 같습니다.

• 90번째 백분위수 값이 bin 크기인 경우 값 간격 길이의 10%만 bin 간격보다 길기 때문에 2개 넘게 인접한 bin 간격에 걸쳐 있어야 합니다.
• 99번째 백분위수 값이 bin 크기인 경우 값 간격 길이의 1%만이 11개 넘게 인접한 bin 간격에 걸쳐 있습니다.
• 99.9번째 백분위수 값이 bin 크기인 경우 값 간격 길이의 0.1%만이 101개 넘게 인접한 bin 간격에 걸쳐 있습니다.
• 99.99번째 백분위수, 99.999번째 백분위수 등의 경우 values에 대해 동일한 작업을 반복할 수 있습니다.

설명된 방법은 여러 bin 간격과 겹치는 불균형한 긴 값 간격의 양을 제한합니다. 이 방법으로 얻은 bin 크기 값은 미세 튜닝을 위한 시작점일 뿐입니다. 실제 결과는 특정 워크로드에 따라 달라질 수 있습니다.