メインコンテンツまでスキップ

範囲結合の最適化

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

Databricks では、パフォーマンスが低い場合に、範囲結合に結合ヒントを使用することをお勧めします。

インターバル範囲内のポイント・ジョイン​

インターバル範囲内のポイント・ジョインは 、条件に述部が含まれているジョインであり、1 つのリレーションの値が他のリレーションの 2 つの値の間にあることを示します。例えば：

SQL
-- 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つの値の間の区間の重複を指定する述語を条件に含む結合です。 例えば：

SQL
-- 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 です。
  • ビン・サイズ・チューニング・パラメーターがあります。

ビンのサイズ​

ビン・サイズは 、範囲条件の値ドメインを同じサイズの複数の ビン に分割する数値調整パラメーターです。たとえば、ビン・サイズが 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 つのリレーション名と、数値のビン サイズ パラメーターが含まれている必要があります。リレーション名には、テーブル、ビュー、またはサブクエリを指定できます。

SQL
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 に関するヒントは、 a と b の結合に適用され、 cとの結合には適用されません。

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

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

セッション構成を使用してレンジ・ジョインを有効にする​

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

SQL
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の間の間隔があると仮定すると、次のクエリを使用して、歪んだ間隔の長さの値のさまざまなパーセンタイルを決定できます。

SQL
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パーセンタイルなどの値についても同じことを繰り返すことができます。

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

に最終更新