次の方法で共有

範囲結合の最適化

範囲結合は、2 つのリレーションがある間隔の点または間隔の重なり条件によって結合されるときに発生します。 Databricks Runtime での範囲結合の最適化のサポートにより、クエリのパフォーマンスが桁違いに向上する可能性がありますが、慎重に手動でチューニングする必要があります。

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です。
  • 範囲結合条件に関係するすべての値は、同じ型です。 デシマル型の場合、値は同じ桁数と精度である必要があります。
  • これは、 INNER JOIN、または間隔範囲結合のポイントの場合は、左側にポイント値を持つ LEFT OUTER JOIN 、または右側にポイント値を持つ RIGHT OUTER JOIN です。
  • ビン サイズのチューニング パラメーターがある。

ビンのサイズ

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

注意

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

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

範囲結合ヒントを使用して範囲結合を有効にする

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の結合に適用されます。

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

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

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

ビンのサイズを選択する

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

ビン サイズを小さくすると、より多くのビンが生成されます。これは、一致候補のフィルター処理に役立ちます。 ただし、ビン サイズが検出された値間隔よりも大幅に小さく、値間隔が複数の "ビン" 間隔と重なる場合は、非効率的になります。 たとえば、p BETWEEN start AND endが 1,000,000、startが 1,999,999、ビン サイズが 10 である条件endでは、値の間隔は 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 パーセンタイルの値に対して同じ値を繰り返すことができます。

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