Rentang optimasi gabungan

Sebuah range join terjadi ketika dua relasi digabungkan menggunakan sebuah titik dalam kondisi interval atau interval overlap. Rentang bergabung dengan dukungan pengoptimalan di Databricks Runtime dapat membawa peningkatan besar dalam kinerja kueri, tetapi memerlukan penyetelan manual yang cermat.

Databricks merekomendasikan penggunaan petunjuk gabungan untuk gabungan rentang saat performa buruk.

Titik dalam rentang interval bergabung

Penggabungan titik dalam rentang interval adalah gabungan yang kondisinya berisi predikat yang menetapkan bahwa nilai dari satu relasi berada di antara dua nilai dari relasi 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;

Rentang optimasi gabungan

Pengoptimalan gabungan rentang dilakukan untuk gabungan yang:

• Memiliki kondisi yang dapat diartikan sebagai titik dalam interval atau interval tumpang tindih range join.
• Semua nilai yang terlibat dalam kondisi gabungan rentang bertipe numerik (integral, floating point, desimal), DATE, atau TIMESTAMP.
• Semua nilai yang terlibat dalam kondisi gabungan rentang memiliki tipe yang sama. Dalam kasus jenis desimal, nilainya 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.
• Memiliki parameter penyetelan ukuran bin.

Ukuran bin

Ukuran bin adalah parameter penyetelan numerik yang membagi domain nilai dari kondisi rentang menjadi beberapa tempat sampah 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 bin dengan panjang 10 – bin pertama dari 0 hingga 10, bin kedua dari 10 sampai 20, dan tempat sampah 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 dapat dikesampingkan sebagai jatuh di antara start dari 8 dan end dari 22, karena jatuh di tempat sampah 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. Jika nilai sub-detik diperlukan, nilai pecahan dapat digunakan. 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. Pengoptimalan gabungan rentang diterapkan hanya jika Anda menentukan ukuran bin secara manual. Bagian Memilih ukuran bin menjelaskan cara memilih ukuran bin yang optimal.

Aktifkan gabungan rentang menggunakan petunjuk gabungan rentang

Untuk mengaktifkan pengoptimalan gabungan rentang dalam kueri SQL, Anda dapat menggunakan petunjuk penggabungan rentang untuk menentukan ukuran bin. Petunjuk harus berisi nama relasi dari salah satu relasi 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)

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 gabungan rentang menggunakan konfigurasi sesi

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

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

Parameter konfigurasi ini berlaku untuk semua gabungan dengan kondisi rentang. Namun, ukuran bin berbeda yang disetel melalui petunjuk gabungan rentang selalu mengesampingkan satu set melalui parameter.

Memilih 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 APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges

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 100 dan seterusnya. Alasannya adalah:

• Jika nilai pada persentil ke-90 adalah ukuran bin, hanya 10% dari panjang interval nilai yang lebih panjang dari interval bin, jadi rentangkan 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 di 99,99, persentil 99,999, dan seterusnya 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 penyetelan; hasil aktual mungkin tergantung pada beban kerja tertentu.