다음을 통해 공유

범위 조인 최적화

  • 아티클

범위 조인은 간격 또는 간격 겹침 조건의 포인트를 사용하여 두 관계가 조인될 때 발생합니다. Databricks Runtime의 범위 조인 최적화 지원은 쿼리 성능을 대폭 향상시킬 수 있지만 신중한 수동 튜닝이 필요합니다.

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

간격 범위 조인의 포인트

간격 범위 조인의 포인트는 한 관계의 값이 다른 관계의 두 값 사이에 있음을 지정하는 조건자를 조건에 포함하는 조인입니다. 예시:

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

간격 겹침 범위 조인

간격 겹침 범위 조인은 조건에 각 관계의 두 값 사이의 간격 겹침을 지정하는 조건자가 포함된 조인입니다. 예시:

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

범위 조인 최적화

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

  • 간격 또는 간격 겹침 범위 조인의 포인트로 해석할 수 있는 조건이 있습니다.
  • 범위 조인 조건에 관련된 모든 값은 숫자 형식(정수, 부동 소수점, 10진수), DATE 또는 TIMESTAMP입니다.
  • 범위 조인 조건에 관련된 모든 값은 동일한 형식입니다. 10진수 형식의 경우 값도 동일한 스케일링과 정밀도를 가져야 합니다.
  • INNER JOIN, 간격 범위 조인의 포인트의 경우 왼쪽에 포인트 값이 있는 LEFT OUTER JOIN 또는 오른쪽에 포인트 값이 있는 RIGHT OUTER JOIN입니다.
  • Bin 크기 튜닝 매개 변수가 있습니다.

Bin 크기

Bin 크기는 범위 조건의 값 도메인을 동일한 크기의 여러 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에 속하는 포인트만 해당 간격에 대해 가능한 조인 일치로 간주해야 합니다. 예를 들어, p가 32인 경우 30에서 40 사이의 bin에 속하므로 start가 8이고 end가 22인 사이에 속하는 것으로 배제할 수 있습니다.

참고 항목

  • DATE 값의 경우 bin 크기의 값은 일로 해석됩니다. 예를 들어, bin 크기 값이 7이면 일주일을 나타냅니다.
  • TIMESTAMP 값의 경우 bin 크기의 값은 초로 해석됩니다. 1초 미만 값이 필요한 경우 소수 값을 사용할 수 있습니다. 예를 들어, bin 크기 값이 60이면 1분을 나타내고 bin 크기 값이 0.1이면 100밀리초를 나타냅니다.

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

범위 조인 힌트를 사용하여 범위 조인 사용

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

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)

참고 항목

세 번째 예제에서는 힌트를 c에 배치해야 합니다. 이는 조인이 연관된 상태로 남아 있으므로 쿼리가 (a JOIN b) JOIN c로 해석되고 a에 대한 힌트가 a와 b의 조인에 적용되고 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()
)

조인된 DataFrame 중 하나에 범위 조인 힌트를 배치할 수도 있습니다. 이 경우 힌트에는 숫자 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")

세션 구성을 사용하여 범위 조인 사용

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

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

이 구성 매개 변수는 범위 조건이 있는 모든 조인에 적용됩니다. 그러나 범위 조인 힌트를 통해 설정된 다른 bin 크기는 항상 매개 변수를 통해 설정된 bin 크기를 재정의합니다.

bin 크기 선택

범위 조인 최적화의 효율성은 적절한 bin 크기 선택에 따라 달라집니다.

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

간격의 길이가 상당히 균일하고 알려진 경우 bin 크기를 값 간격의 일반적인 예상 길이로 설정하는 것이 좋습니다. 그러나 간격의 길이가 다양하고 불균형한 경우 긴 간격이 너무 많은 bin과 겹치지 않도록 하면서 짧은 간격을 효율적으로 필터링하는 bin 크기를 설정하도록 균형을 맞춰야 합니다. start 열과 end 열 사이에 간격이 있는 테이블 ranges를 가정하면 다음 쿼리를 사용하여 불균형한 간격 길이 값의 다른 백분위수를 결정할 수 있습니다.

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번째 백분위수 등의 값에 대해 동일한 작업을 반복할 수 있습니다.

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