범위 조인 최적화

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

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;

범위 조인 최적화

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

• 간격 또는 간격 겹침 범위 조인으로 해석할 수 있는 조건을 갖습니다.
• 범위 조인 조건에 관련된 모든 값은 숫자 형식(정수, 부동 소수점, 소수점), 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)과 겹칩니다. 동일한 세 개의 bin 내에 속하는 지점만 해당 간격에 대해 가능한 조인 일치 항목으로 간주해야 합니다. 예를 들어, p가 32인 경우 30에서 40 사이의 bin에 속하므로 start가 8이고 end가 22인 사이에 속하는 것으로 배제할 수 있습니다.

쿼리에서 범위 조인 힌트를 사용하거나 세션 구성 매개 변수를 설정하여 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)

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

조인된 DataFrames 중 하나에 범위 조인 힌트를 배치할 수도 있습니다. 이 경우 힌트에는 숫자 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 간격과 겹치면 비효율적입니다. 예를 들어 p BETWEEN start AND end 1,000,000이고 start 1,999,999이고 bin 크기가 10인 조건 end값 간격은 100,000개의 bin과 겹칩니다.

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

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 크기 값은 미세 튜닝을 위한 시작점일 뿐입니다. 실제 결과는 특정 워크로드에 따라 달라질 수 있습니다.