Optimasi penyatuan rentang

Gabungan rentang terjadi ketika dua hubungan bergabung menggunakan titik dalam kondisi interval atau tumpang tindih interval. Menggunakan pengoptimalan gabungan rentang di Databricks Runtime dapat sangat meningkatkan performa kueri.

Di Databricks SQL, Azure Databricks secara otomatis mengoptimalkan gabungan rentang tanpa konfigurasi manual apa pun. Anda juga dapat menyetel gabungan rentang secara manual menggunakan petunjuk gabungan atau konfigurasi sesi untuk semua jenis komputasi.

Titik dalam rentang interval bergabung

Titik dalam gabungan rentang interval adalah gabungan yang kondisinya berisi predikat yang menentukan bahwa nilai dari satu hubungan berada di antara dua nilai dari hubungan lainnya. Contohnya:

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

Rentang tumpang tindih interval bergabung

Gabungan rentang tumpang tindih interval adalah gabungan yang kondisinya berisi predikat yang menetapkan tumpang tindih interval antara dua nilai dari setiap relasi. Contohnya:

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

Optimasi penyatuan rentang

Pengoptimalan gabungan rentang dilakukan untuk gabungan yang:

• Memiliki kondisi yang dapat diartikan sebagai titik dalam interval atau penggabungan rentang dengan tumpang tindih interval.
• Semua nilai yang terlibat dalam kondisi gabungan rentang bertipe numerik (integral, floating point, desimal), DATE, atau TIMESTAMP.
• Semua nilai yang terlibat dalam kondisi penggabungan rentang memiliki tipe yang sama. Dalam kasus jenis desimal, nilai juga harus memiliki skala dan presisi yang sama.
• Ini adalah INNER JOIN, atau jika titik dalam rentang interval bergabung, LEFT OUTER JOIN dengan nilai titik di sisi kiri, atau RIGHT OUTER JOIN dengan nilai titik di sisi kanan.
• Tetapkan ukuran bin, baik yang ditentukan secara otomatis maupun yang ditentukan secara manual.

Bergabung dengan kesetaraan numerik dan kondisi rentang

Saat kondisi gabungan mencakup kondisi kesetaraan pada kolom numerik dan kondisi rentang, pengoptimal dapat menerapkan pengikatan ke kolom kesetaraan numerik karena memenuhi persyaratan jenis untuk pengoptimalan gabungan rentang. Ini dapat menyebabkan kolom kesetaraan dimasukkan ke dalam bin atau dikecualikan dari pengoptimalan, sehingga kinerja menurun.

Untuk memastikan pengoptimalan gabungan rentang hanya berlaku untuk kondisi rentang yang dimaksudkan, transmisikan kolom kesetaraan numerik ke STRING. Hal ini membuatnya tidak dipertimbangkan sebagai kolom kondisi rentang.

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;

Pola yang sama berlaku untuk kolom numerik lain yang digunakan sebagai kunci kesetaraan, seperti DATE, pengidentifikasi bilangan bulat, atau kolom partisi berkluster.

Ukuran wadah

Ukuran bin adalah parameter penyetelan numerik yang membagi domain nilai dari kondisi rentang menjadi beberapa kelompok dengan ukuran yang sama. Misalnya, dengan ukuran bin 10, optimasi membagi domain menjadi bin dengan interval panjang 10. Jika Anda memiliki titik dalam kondisi jangkauan p BETWEEN start AND end, dan start adalah 8 dan end adalah 22, interval nilai ini tumpang tindih dengan tiga wadah berukuran panjang 10 – wadah pertama dari 0 hingga 10, wadah kedua dari 10 sampai 20, dan wadah ketiga dari 20 sampai 30. Hanya poin-poin yang termasuk dalam tiga bin yang sama yang perlu dipertimbangkan sebagai kemungkinan kecocokan gabungan untuk interval tersebut. Misalnya, jika p adalah 32, itu bisa dikesampingkan dari rentang nilai di antara start yang bernilai 8 dan end yang bernilai 22, karena jatuh dalam rentang nilai dari 30 hingga 40.

Anda dapat menentukan ukuran bin dengan menggunakan petunjuk gabungan rentang dalam kueri atau dengan mengatur parameter konfigurasi sesi. Di Databricks SQL, ukuran bin secara otomatis ditentukan saat pengoptimalan join rentang otomatis diaktifkan.

Pengoptimalan gabungan rentang otomatis

Di Databricks SQL, Azure Databricks secara otomatis mendeteksi gabungan rentang yang memenuhi syarat dan memperoleh ukuran bin optimal dengan mengambil sampel tabel interval. Ini menghapus kebutuhan untuk menentukan ukuran bin secara manual melalui petunjuk atau konfigurasi sesi.

Pengoptimalan gabungan rentang otomatis diaktifkan secara default di Databricks SQL. Untuk menonaktifkannya, atur konfigurasi berikut:

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

Jika Anda menentukan ukuran bin melalui petunjuk gabungan rentang atau konfigurasi sesi, nilai tersebut akan mengambil alih ukuran bin yang diturunkan secara otomatis.

Aktifkan gabungan rentang menggunakan petunjuk gabungan rentang

Untuk mengaktifkan pengoptimalan gabungan rentang dalam kueri SQL, gunakan petunjuk gabungan rentang untuk menentukan ukuran bin. Petunjuk harus berisi nama hubungan dari salah satu hubungan yang digabungkan dan parameter ukuran bin numerik. Nama relasi dapat berupa tabel, tampilan, atau subkueri.

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

Anda juga dapat menempatkan petunjuk gabungan rentang di salah satu DataFrames yang digabungkan. Dalam hal ini, petunjuk hanya berisi parameter ukuran bin numerik.

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

Aktifkan "range join" menggunakan konfigurasi sesi

Jika Anda tidak ingin mengubah kueri, tentukan ukuran bin sebagai parameter konfigurasi.

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

Parameter konfigurasi ini berlaku untuk setiap join dengan kondisi rentang. Namun, ukuran bin yang berbeda yang ditentukan melalui petunjuk gabungan rentang selalu mengesampingkan ukuran yang disetel melalui parameter.

Pilih ukuran bin

Efektivitas optimasi gabungan rentang tergantung pada pemilihan ukuran bin yang sesuai.

Ukuran bin yang kecil menghasilkan jumlah bin yang lebih besar, yang membantu dalam memfilter kecocokan potensial. Namun, menjadi tidak efisien jika ukuran bin secara signifikan lebih kecil daripada interval nilai yang ditemui, dan interval nilai tumpang tindih dengan beberapa interval bin. Misalnya, dengan kondisi p BETWEEN start AND end, di mana start adalah 1.000.000 dan end adalah 1.999.999, dan ukuran bin 10, interval nilai tumpang tindih dengan 100.000 bin.

Jika panjang interval cukup seragam dan diketahui, kami sarankan Anda mengatur ukuran bin ke panjang tipikal interval nilai yang diharapkan. Namun, jika panjang interval bervariasi dan miring, keseimbangan harus ditemukan untuk mengatur ukuran bin yang menyaring interval pendek secara efisien, sekaligus mencegah interval panjang dari tumpang tindih terlalu banyak bin. Dengan asumsi tabel ranges, dengan interval yang berada di antara kolom start dan end, Anda dapat menentukan persentil yang berbeda dari nilai panjang interval miring dengan kueri berikut:

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;

Mengonversi setiap kolom ke DOUBLE sebelum mengurangkannya memastikan kueri berfungsi baik kolom berisi nilai numerik, DATE, maupun TIMESTAMP.

Pengaturan ukuran bin yang direkomendasikan adalah nilai maksimum pada persentil ke-90, atau nilai pada persentil ke-99 dibagi 10, atau nilai pada persentil ke-99,9 dibagi dengan 100, dll. Alasannya adalah:

• Jika nilai pada persentil ke-90 adalah ukuran rentang bin, hanya 10% dari panjang interval nilai yang lebih panjang dari interval bin, sehingga mencakup lebih dari 2 interval bin yang berdekatan.
• Jika nilai pada persentil ke-99 adalah ukuran bin, hanya 1% dari panjang interval nilai yang menjangkau lebih dari 11 interval bin yang berdekatan.
• Jika nilai pada persentil ke-99,9 adalah ukuran bin, hanya 0,1% dari panjang interval nilai yang menjangkau lebih dari 101 interval bin yang berdekatan.
• Hal yang sama dapat diulang untuk nilai pada 99,99, persentil ke-99,999, dll. jika diperlukan.

Metode yang dijelaskan membatasi jumlah interval nilai panjang miring yang tumpang tindih dengan beberapa interval bin. Nilai ukuran bin yang diperoleh dengan cara ini hanyalah titik awal untuk penyempurnaan; hasil aktual mungkin bergantung pada beban kerja tertentu.