範囲結合の最適化

範囲結合は、2つのリレーションを区間内の点条件または区間の重複条件を用いて結合する際に発生します。 Databricks Runtime で範囲結合の最適化を使用すると、クエリのパフォーマンスを大幅に向上させることができます。

Databricks SQL では、Azure 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です。
• 範囲結合条件に関係するすべての値は、同じ型です。 10 進数型の場合、値も同じ小数点以下桁数と有効桁数を持つ必要があります。
• これは、 INNER JOIN、または間隔範囲結合のポイントの場合は、左側にポイント値を持つ LEFT OUTER JOIN 、または右側にポイント値を持つ RIGHT OUTER JOIN です。
• ビンサイズを自動的に算出するか、手動で指定します。

数値の等値条件と範囲条件による結合

結合条件に数値列の等値条件と範囲条件の両方が含まれている場合、オプティマイザーは範囲結合の最適化の型要件を満たしているため、数値の等値列にビン分割を適用できます。 これにより、等値列がビンに割り当てられるか、最適化から除外され、パフォーマンスが低下する可能性があります。

範囲結合の最適化が目的の範囲条件にのみ適用されるようにするには、数値の等値列を 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、整数識別子、クラスター化パーティション列など、等値キーとして使用される他の数値列にも適用されます。

ビンのサイズ

ビン サイズは、範囲条件の値ドメインを等しいサイズの複数のビンに分割する数値チューニング パラメーターです。 たとえば、ビン サイズが 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 までの分類では除外できます。

bin サイズを指定するには、クエリで範囲結合ヒントを使用するか、セッション構成パラメーターを設定します。 Databricks SQL では、範囲結合の自動最適化が有効になると、ビン サイズが自動的に派生します。

範囲結合の自動最適化

Databricks SQL では、Azure Databricksは、対象範囲の結合を自動的に検出し、間隔テーブルをサンプリングすることによって最適なビン サイズを導き出します。 これにより、ヒントまたはセッション構成を使用してビン サイズを手動で指定する必要がなくなります。

Databricks SQL では、自動範囲結合の最適化が既定で有効になっています。 無効にするには、次の構成を設定します。

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

範囲結合ヒントまたはセッション構成を使用してビン サイズを指定した場合、その値は自動的に派生したビン サイズをオーバーライドします。

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

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)

#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
  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 値のいずれであるかに関係なく、クエリが確実に機能します。

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

• 90 番目のパーセンタイルの値がビンサイズである場合、値間隔の長さのうち 10% のみがビン間隔より長く、したがって 2 つ以上の隣接するビン間隔を超える範囲に及びます。
• 99 番目のパーセンタイルの値がビンサイズである場合、値の区間の長さのうち、わずか1%だけが11個以上の隣接ビン区間にまたがります。
• 99.9 番目のパーセンタイルの値がビン サイズの場合、値間隔長の 0.1% だけが 101 個の隣接するビン間隔を超えた範囲に及びます。
• 必要に応じて、99.99 番目、99.999 番目のパーセンタイルなどの値に対して同じ値を繰り返すことができます。

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