Aralık birleştirme iyileştirmesi

Aralık birleştirmesi, iki ilişkinin bir aralıktaki bir nokta ya da aralık çakışması koşulu kullanılarak birleştirilmesiyle gerçekleşir. Databricks Runtime'da aralık birleştirme iyileştirmesini kullanmak sorgu performansını büyük ölçüde geliştirebilir.

Databricks SQL'de Azure Databricks, aralık birleştirmelerini el ile yapılandırma olmadan otomatik olarak iyileştirir. Ayrıca tüm işlem türleri için birleştirme ipuçlarını veya oturum yapılandırmasını kullanarak aralık birleştirmelerini el ile ayarlayabilirsiniz.

Aralık aralığı birleştirme noktası

Aralık aralığı birleşimindeki bir nokta, koşulu, bir ilişkiden gelen bir değerin diğer ilişkideki iki değer arasında yer aldığını belirten yüklemler içeren bir birleşimdir. Örneğin:

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

Aralık çakışma aralığı birleştirme

Bir aralık çakışması aralığı birleştirme, koşulun her ilişkideki iki değer arasındaki aralıkların çakışmasını belirten önermeler içerdiği bir birleşimdir. Örneğin:

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

Aralık birleştirme iyileştirmesi

Aralık birleştirme iyileştirmesi, aşağıdaki birleşimler için gerçekleştirilir:

• Aralık veya aralık çakışma aralığı birleşiminde bir nokta olarak yorumlanabilecek bir duruma sahip olmak.
• Aralık birleştirme koşulunda yer alan tüm değerler sayısal türdedir (integral, kayan nokta, ondalık), DATEveya TIMESTAMP.
• Aralık birleştirme koşulunda yer alan tüm değerler aynı türdedir. Ondalık türü söz konusu olduğunda, değerlerin de aynı ölçek ve duyarlıkta olması gerekir.
• Bir INNER JOINveya aralık aralığındaki nokta birleşimi durumunda, sol tarafta nokta değeri olan bir LEFT OUTER JOIN veya sağ tarafta nokta değeri olan bir RIGHT OUTER JOIN.
• Otomatik olarak türetilen veya el ile belirtilen bir bölme boyutuna sahip olun.

Sayısal eşitlik ve aralık koşullarına dayalı birleştirme

Birleştirme koşulu hem sayısal sütunda eşitlik koşulu hem de aralık koşulu içerdiğinde, aralık birleştirme iyileştirmesi için tür gereksinimlerini karşıladığı için iyileştirici sayısal eşitlik sütununa gruplama uygulayabilir. Bu, eşitlik sütununun bölmelere atanması veya iyileştirmenin dışında tutulmasına neden olarak performansı düşürebilir.

Aralık birleştirme optimizasyonunun yalnızca amaçlanan aralık koşuluna uygulanmasını sağlamak için sayısal eşitlik sütunlarını STRING türüne dönüştürün. Bu, bunların aralık koşulu sütunları olarak değerlendirmeye alınmasını engeller.

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;

Aynı desen, eşitlik anahtarları olarak kullanılan , tamsayı tanımlayıcıları veya kümelenmiş bölüm sütunları gibi DATEdiğer sayısal sütunlar için de geçerlidir.

Kutu boyutu

kutu boyutu, aralık koşulunun değer etki alanını eşit boyutta birden çok kutuya bölen sayısal bir ayar parametresidir. Örneğin, bölme boyutu 10 olan iyileştirme, etki alanını uzunluk 10 aralıkları olan bölmelere böler. aralık koşulunda p BETWEEN start AND endbir noktanız varsa ve 8 ve startend 22 ise, bu değer aralığı 10 uzunluklu üç bölmeyle çakışıyor: 0'dan 10'a ilk bölme, 10'dan 20'ye ikinci bölme ve 20'den 30'a üçüncü bölme. Yalnızca aynı üç kategorideki noktalar bu aralıkta muhtemel birleştirme eşleşmeleri olarak değerlendirilmelidir. 32, p 30 ile 40 arasındaki bir bölmede yer aldığından dolayı, start 8 ile end 22 arasında olmadığından elenebilir.

Sorguda bir aralık birleştirme ipucu kullanarak veya bir oturum yapılandırma parametresi belirleyerek bölme boyutunu belirtebilirsiniz. Databricks SQL'de, otomatik aralık birleştirme iyileştirmesi etkinleştirildiğinde kutu boyutu otomatik olarak belirlenir.

Otomatik aralık birleştirme optimizasyonu

Databricks SQL'de Azure Databricks uygun aralık birleşimlerini otomatik olarak algılar ve aralıklar tablosunu örnekleme yoluyla en uygun bölme boyutunu türetir. Bu, ipuçları veya oturum yapılandırması aracılığıyla bölme boyutunu el ile belirtme gereksinimini ortadan kaldırır.

Databricks SQL'de otomatik aralık birleştirme iyileştirmesi varsayılan olarak etkindir. Devre dışı bırakmak için aşağıdaki yapılandırmayı ayarlayın:

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

Aralık birleştirme ipucu veya oturum yapılandırması aracılığıyla bir bölme boyutu belirtirseniz, bu değer otomatik olarak belirlenen bölme boyutunu geçersiz kılar.

Aralık birleştirme ipucu kullanarak aralık birleştirmeyi etkinleştirin

Bir SQL sorgusunda aralık birleştirme iyileştirmesini etkinleştirmek için, kutu boyutunu belirtmek üzere aralık birleştirme yönergesi kullanın. İpucu, birleştirilmiş ilişkilerden birinin ilişki adını ve sayısal bölme boyutu parametresini içermelidir. İlişki adı bir tablo, görünüm veya alt sorgu olabilir.

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

Ayrıca, birleştirilmiş DataFrame'lerden birine bir aralık birleştirme ipucu yerleştirebilirsiniz. Bu durumda, ipucu yalnızca sayısal bölme boyutu parametresini içerir.

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

Oturum yapılandırmasını kullanarak aralık birleştirmeyi etkinleştirme

Sorguyu değiştirmek istemiyorsanız, bölme boyutunu yapılandırma parametresi olarak belirtin.

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

Bu yapılandırma parametresi, aralık koşulu olan tüm birleşimler için geçerlidir. Ancak, aralık birleştirme ipucu aracılığıyla ayarlanan farklı bir bölme boyutu her zaman parametresi aracılığıyla ayarlananı geçersiz kılar.

Kutu boyutunu seçin

Aralık birleştirme optimizasyonunun etkinliği, uygun bölme boyutunun seçilmesine bağlıdır.

Küçük bir bölme boyutu, olası eşleşmelerin filtrelenmesine yardımcı olan daha fazla sayıda bölmeye neden olur. Ancak, bölme boyutu karşılaşılan değer aralıklarından önemli ölçüde daha küçükse ve değer aralıkları birden çok bölme aralığıyla çakışıyorsa verimsiz hale gelir. Örneğin, p BETWEEN start AND endkoşuluna göre, start 1.000.000, end 1.999.999 ve bölme boyutu 10 olduğunda, değer aralığı 100.000 bölme ile çakışıyor.

Aralığın uzunluğu oldukça tekdüzense ve biliniyorsa, bölme boyutunu değer aralığının beklenen tipik uzunluğuna ayarlamanızı öneririz. Ancak, aralığın uzunluğu farklılık gösterir ve eğrilirse, kısa aralıkları verimli bir şekilde filtreleyen ve uzun aralıkların çok fazla bölmeyle çakışmasını önleyen bir bölme boyutu ayarlamak için bir bakiye bulunmalıdır. ranges ve startsütunları arasındaki aralıklarla endbir tablo olduğunu varsayarsak, aşağıdaki sorguyla çarpık aralık uzunluğu değerinin farklı yüzdebirlik dilimlerini belirleyebilirsiniz:

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;

Çıkarma işleminden önce her sütunun DOUBLE türüne dönüştürülmesi, sütunlar sayısal, DATE veya TIMESTAMP değerler içerse de sorgunun çalışmasını sağlar.

Önerilen bölme boyutu ayarı, 90. yüzdebirlik dilimdeki değerin maksimum değeri veya 99. yüzdebirlik dilimdeki değerin 10'a bölünmesi veya 99,9. yüzdebirlik dilimdeki değerin 100'e bölünmesi vb. olabilir. Gerekçe şudur:

• 90. yüzdebirlik dilimdeki değer bölme boyutuysa, değer aralığı uzunluklarının yalnızca %10'u bölme aralığından daha uzundur ve böylece 2'den fazla bitişik bölme aralığını kapsar.
• 99. yüzdebirlik dilimdeki değer bölme boyutu olarak belirlenirse, değer aralıklarının uzunluklarının yalnızca %1'i 11'den fazla bitişik olan bölme aralığına yayılır.
• Eğer 99,9. yüzdelik dilimdeki değer bölme boyutuysa, değer aralığı uzunluklarının yalnızca %0,1'i 101'den fazla bitişik bölme aralığını kapsar.
• Gerekirse aynı işlem, 99,99. ve 99,999. persentildeki değerler vb. için de tekrarlanabilir.

Açıklanan yöntem, birden çok dilim aralığıyla çakışan eğik uzun değer aralıklarının miktarını sınırlar. Bu şekilde elde edilen bölme boyutu değeri yalnızca ince ayar için bir başlangıç noktasıdır; gerçek sonuçlar belirli bir iş yüküne bağlı olabilir.