Optimasi penyatuan rentang

Range join menggabungkan dua tabel pada kondisi yang memeriksa apakah suatu nilai berada dalam rentang tertentu, atau apakah dua rentang tumpang tindih, alih-alih memeriksa apakah dua nilai sama. Misalnya, kueri yang mencocokkan setiap event situs web dengan sesi pengguna tempat peristiwa tersebut terjadi memeriksa apakah timestamp event tersebut berada di antara waktu mulai dan berakhirnya sesi. Query yang menemukan rapat yang dijadwalkan secara bersamaan memeriksa apakah dua rentang waktu tumpang tindih. Optimasi range join di Databricks Runtime dapat sangat meningkatkan performa kueri ini.

Karena itu, sebuah join dapat membandingkan banyak pasangan baris yang tidak dapat cocok. Optimasi range join membagi rentang nilai ke dalam bin-bins dan hanya membandingkan baris yang berada dalam bin-bins yang tumpang tindih. Lihat Ukuran bin.

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

Optimasi range join berlaku untuk join yang:

  • Memiliki titik pada interval atau kondisi join rentang tumpang tindih interval.
  • Gunakan hanya nilai numerik (integral, floating point, atau desimal), DATE, atau TIMESTAMP dalam kondisi range join.
  • Gunakan nilai dengan tipe yang sama dalam kondisi range join. Nilai desimal juga harus memiliki skala dan presisi yang sama.
  • Gunakan tipe join yang didukung:
    • INNER JOIN, untuk setiap range join
    • LEFT OUTER JOIN dengan nilai poin di sisi kiri, atau RIGHT OUTER JOIN dengan nilai poin di sisi kanan, hanya untuk penggabungan rentang interval point-in-time
  • Memiliki ukuran bin, baik yang diturunkan secara otomatis maupun 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.

Catatan

  • Untuk nilai DATE, nilai ukuran bin diinterpretasikan sebagai hari. Misalnya, nilai ukuran bin 7 mewakili satu minggu.
  • Untuk nilai TIMESTAMP, nilai ukuran bin ditafsirkan sebagai detik. Untuk nilai sub-detik, gunakan nilai pecahan. Misalnya, nilai ukuran bin 60 mewakili satu menit, dan nilai ukuran bin 0,1 mewakili 100 milidetik.

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 ini:

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 adalah tabel, tampilan, atau subquery.

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)

Catatan

Pada contoh ketiga, Anda harus menempatkan petunjuk pada c. Ini karena gabungan dibiarkan asosiatif, sehingga kueri ditafsirkan sebagai (a JOIN b) JOIN c, dan petunjuk pada a berlaku untuk penggabungan a dengan b dan bukan penggabungan dengan 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()
)

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. Ukuran bin yang kecil menjadi tidak efisien jika interval nilai jauh lebih besar daripada ukuran bin, menyebabkan interval tumpang tindih di banyak bin. Misalnya, dengan kondisi p BETWEEN start AND end di mana start adalah 1.000.000 dan end adalah 1.999.999, serta ukuran bin 10, interval tersebut tumpang tindih dengan 100.000 bin.

Jika panjang interval cukup seragam dan diketahui, atur ukuran bin ke panjang interval yang tipikal. Untuk interval yang bervariasi dan menceng, pilih ukuran bin yang memfilter interval pendek secara efisien sekaligus mencegah interval panjang tumpang tindih dengan terlalu banyak bin. Untuk tabel ranges dengan interval antar kolom start dan end, tentukan persentil panjang interval yang menceng dengan kueri ini:

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.

Atur ukuran bin ke nilai terbesar dari nilai-nilai berikut:

  • Persentil ke-90 dari panjang interval. Pada ukuran ini, paling banyak 10% interval mencakup lebih dari 2 bin.
  • Persentil ke-99 dibagi 10. Pada ukuran ini, paling banyak 1% interval dapat membentang lebih dari 11 bin.
  • Persentil ke-99,9 dibagi 100. Pada ukuran ini, paling banyak 0,1% interval mencakup lebih dari 101 bin.

Untuk membatasi interval yang lebih panjang lagi, lanjutkan pola dengan persentil ke-99,99 dan ke-99,999. Gunakan hasilnya sebagai titik awal, lalu sesuaikan ukuran bin sesuai beban kerja Anda.

  • Terakhir diperbarui pada