範囲結合の最適化

範囲結合は、間隔内の点または間隔のオーバーラップ条件を使用して 2 つのリレーションが結合される場合に発生します。 Databricks ランタイムの範囲結合最適化のサポートにより、クエリーのパフォーマンスが大幅に向上する可能性がありますが、慎重な手動チューニングが必要です。

間隔範囲のポイント結合

間隔範囲のポイント結合 は、一方のリレーションの値がもう一方のリレーションの 2 つの値の間にあることを指定する述部が条件に含まれているジョインです。例えば：

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

間隔オーバーラップ範囲結合

間隔重複範囲結合は、各リレーションの 2 つの値間の 間隔の重複 を指定する述部が条件に含まれている結合です。例えば：

-- 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です。
範囲結合条件に含まれるすべての値は、同じ型です。 decimal 型の場合、値も同じスケールと精度である必要があります。
これは INNER JOIN、または間隔範囲のポイント結合の場合は、左側にポイント値を持つ LEFT OUTER JOIN 、または右側にポイント値を持つ RIGHT OUTER JOIN です。
ビン・サイズ調整パラメーターを用意します。

ビンサイズ

ビン・サイズは、範囲条件の値ドメインを同じサイズの複数のビンに分割する数値チューニング・パラメーターです。たとえば、ビンサイズが 10 の場合、最適化ではドメインが長さ 10 の間隔のビンに分割されます。ポイント・イン・レンジ条件が p BETWEEN start AND endで、 start が 8 で end が 22 の場合、この値間隔は長さ 10 の 3 つのビン (最初のビンは 0 から 10、2 番目のビンは 10 から 20、3 番目のビンは 20 から 30) と重なります。同じ 3 つのビン内にあるポイントのみを、その間隔の結合一致と見なす必要があります。たとえば、 p が32の場合、30から40のビンに入るため、8の start から22の end の間にあると除外できます。

注

DATE 値の場合、ビンサイズの値は日数として解釈されます。たとえば、ビンサイズの値 7 は週を表します。
TIMESTAMP 値の場合、ビンサイズの値は秒として解釈されます。秒未満の値が必要な場合は、小数値を使用できます。たとえば、ビンサイズの値 60 は分を表し、ビンサイズの値 0.1 は 100 ミリ秒を表します。

ビン・サイズを指定するには、クエリーで範囲結合ヒントを使用するか、セッション構成パラメーターを設定します。範囲結合の最適化は、ビンサイズを手動で指定した 場合にのみ適用されます 。セクションビンサイズの選択では、最適なビンサイズを選択する方法について説明します。

範囲結合ヒントを使用した範囲結合の有効化

SQL クエリーで範囲結合の最適化を有効にするには、 範囲結合ヒント を使用してビンのサイズを指定します。ヒントには、結合されたリレーションの 1 つのリレーション名と数値のビンサイズパラメーターが含まれている必要があります。リレーション名には、テーブル、ビュー、またはサブクエリを指定できます。

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)

注

3 番目の例では、ヒントを cに配置する 必要があります 。これは、結合が連想されたままであるため、クエリーは (a JOIN b) JOIN cと解釈され、 a に関するヒントは cとの結合ではなく、 a と b の結合に適用されます。

#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の 1 つに範囲結合ヒントを配置することもできます。その場合、ヒントには数値のビンサイズパラメーターのみが含まれます。

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

セッション構成を使用した範囲参加の有効化

クエリーを変更しない場合は、構成パラメーターとしてビンサイズを指定できます。

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

この構成パラメーターは、範囲条件を持つすべての結合に適用されます。ただし、範囲結合ヒントによって設定された別のビンサイズは、常にパラメーターによって設定されたビンサイズをオーバーライドします。

ビンのサイズを選択してください

範囲結合の最適化の効果は、適切なビンサイズの選択によって異なります。

ビンサイズが小さいとビンの数が多くなり、潜在的な一致をフィルタリングするのに役立ちます。ただし、ビンサイズが検出された値間隔よりも大幅に小さく、値間隔が複数のビン間隔と重なっている場合は非効率的になります。たとえば、条件 p BETWEEN start AND endで、 start が 1,000,000、 end が 1,999,999 で、ビンサイズが 10 の場合、値の間隔は 100,000 ビンと重複します。

間隔の長さがかなり均一でわかっている場合は、ビンサイズを値間隔の一般的な予想される長さに設定することをお奨めします。ただし、間隔の長さが変化して歪んでいる場合は、長い間隔がビンと重なりすぎないようにしながら、短い間隔を効率的にフィルター処理するビンサイズを設定するためのバランスを見つける必要があります。テーブル rangesを想定し、列 start と endの間の間隔を持つと、次のクエリーを使用して、歪んだ間隔の長さ値の異なるパーセンタイルを決定できます。

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

ビンサイズの推奨設定は、90 パーセンタイルの値の最大値、99 パーセンタイルの値を 10 で割った値、または 99.9 パーセンタイルの値を 100 で割った値などです。理論的根拠は次のとおりです。

90 パーセンタイルの値がビンサイズの場合、値間隔の長さの 10% のみがビン間隔より長いため、隣接する 2 つ以上のビン間隔にまたがります。
99 パーセンタイルの値がビンサイズである場合、値間隔の長さの 1% のみが 11 を超える隣接するビン間隔にまたがっています。
99.9 パーセンタイルの値がビンサイズである場合、値間隔の長さの 0.1% のみが 101 を超える隣接するビン間隔にまたがっています。
必要に応じて、99.99 パーセンタイル、99.999 パーセンタイルなどの値についても同じことを繰り返すことができます。

説明した方法では、複数のビン間隔と重なるスキューされた長い値間隔の量を制限します。この方法で取得したビンサイズの値は、微調整の開始点にすぎません。実際の結果は、特定のワークロードによって異なる場合があります。

範囲結合の最適化

間隔範囲のポイント結合

間隔オーバーラップ範囲結合

範囲結合の最適化

ビンサイズ

範囲結合ヒントを使用した範囲結合の 有効化

セッション構成 を使用した範囲参加の有効化

ビンのサイズ を選択してください

範囲結合ヒントを使用した範囲結合の有効化

セッション構成を使用した範囲参加の有効化

ビンのサイズを選択してください