조인 범위 최적화

범위 조인은 두 관계를 점-구간 포함 조건 또는 구간 겹침 조건으로 조인할 때 발생합니다. Databricks 런타임에서 범위 조인 최적화를 사용하면 쿼리 성능이 크게 향상될 수 있습니다.

Databricks SQL에서 Azure 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에 할당되거나 최적화에서 제외되어 성능이 저하될 수 있습니다.

범위 조인 최적화가 의도한 범위 조건에만 적용되도록 하려면 숫자 같음 열을 .로 캐스팅합니다 STRING. 이렇게 하면 범위 조건 열로 고려할 때 제외됩니다.

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;

같은 패턴은 같음 키로 사용되는 다른 숫자 열(예: DATE정수 식별자 또는 클러스터형 파티션 열)에 적용됩니다.

상자 크기

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 크기를 지정할 수 있습니다. Databricks SQL에서 bin 크기는 자동 범위 조인 최적화를 사용하도록 설정하면 자동으로 파생됩니다.

자동 범위 조인 최적화

Databricks SQL에서 Azure Databricks 자동으로 적격 범위 조인을 검색하고 간격 테이블을 샘플링하여 최적의 bin 크기를 파생합니다. 이렇게 하면 힌트 또는 세션 구성을 통해 bin 크기를 수동으로 지정할 필요가 없습니다.

자동 범위 조인 최적화는 기본적으로 Databricks SQL에서 사용하도록 설정됩니다. 사용하지 않도록 설정하려면 다음 구성을 설정합니다.

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

범위 조인 힌트 또는 세션 구성을 통해 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
  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;

빼기 전에 각 열을 DOUBLE로 캐스팅하면 열 값이 숫자이든, DATE 값이든, TIMESTAMP 값이든 관계없이 쿼리가 작동합니다.

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