Aracılığıyla paylaş

Aralık birleştirme iyileştirmesi

  • Makale

Aralık veya aralık çakışma koşulundaki bir nokta kullanılarak iki ilişki birleştirildiğinde aralık birleştirme gerçekleşir. Databricks Runtime'daki aralık birleştirme iyileştirme desteği sorgu performansında büyük iyileştirme siparişleri getirebilir, ancak dikkatli bir şekilde el ile ayarlama gerektirir.

Databricks, performans düşük olduğunda aralık birleştirmeleri için birleştirme ipuçlarının kullanılmasını önerir.

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

Aralık aralığı birleştirmesindeki nokta, koşulun bir ilişkideki bir değerin diğer ilişkideki iki değer arasında olduğunu belirten koşullar içerdiği 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

Aralık çakışması aralığı birleşimi , koşulun her ilişkideki iki değer arasındaki aralıkların çakışmasını belirten koşulları içerdiği 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ştirmesinde bir nokta olarak yorumlanabilir bir koşula sahip olun.
  • 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.
  • INNER JOINBir veya aralık aralığı birleşimindeki nokta durumunda, sol tarafta nokta değeri olan veya RIGHT OUTER JOIN sağ tarafta nokta değeri olan bir LEFT OUTER JOIN değeridir.
  • Bölme boyutu ayarlama parametresine sahip olun.

Bölme boyutu

Bölme boyutu , aralık koşulunun değer etki alanını eşit boyutta birden çok bölmeye bölen sayısal bir ayarlama 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 start end 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ı üç bölmenin içindeki noktaların bu aralık için olası birleştirme eşleşmeleri olarak düşünülmesi gerekir. Örneğin, 32 isep, 30 ile 40 arasında bir bölmeye düştüğünden, 8 end ile 22 arasında start bir düşüş olarak elenebilir.

Not

  • Değerler için DATE , bölme boyutunun değeri gün olarak yorumlanır. Örneğin, 7 bölme boyutu değeri bir haftayı temsil eder.
  • Değerler için TIMESTAMP , bölme boyutunun değeri saniye olarak yorumlanır. Bir alt saniye değeri gerekiyorsa kesirli değerler kullanılabilir. Örneğin, 60 bölme boyutu değeri bir dakikayı, 0,1 bölme boyutu değeri ise 100 milisaniyeyi temsil eder.

Bölme boyutunu, sorguda bir aralık birleştirme ipucu kullanarak veya bir oturum yapılandırma parametresi ayarlayarak belirtebilirsiniz. Aralık birleştirme iyileştirmesi yalnızca bölme boyutunu el ile belirtirseniz uygulanır. Bölüm Bölme boyutunu seçin, en uygun bölme boyutunun nasıl seçileceğini açıklar.

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

BIR SQL sorgusunda aralık birleştirme iyileştirmesini etkinleştirmek için, bölme boyutunu belirtmek için bir aralık birleştirme ipucu kullanabilirsiniz. İ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)

Not

Üçüncü örnekte ipucunu üzerine cyerleştirmeniz gerekir. Bunun nedeni birleşimlerin ilişkilendirici olarak bırakılmasıdır, bu nedenle sorgu olarak yorumlanır (a JOIN b) JOIN cve üzerindeki a ipucu ile cbirleştirmeye değil ile b birleştirmesine a uygulanır.

#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 belirtebilirsiniz.

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.

Bölme boyutunu seçin

Aralık birleştirme iyileştirmesinin 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 endstart 1.000.000 ve end 1.999.999 olan koşulu ve 10 bölme boyutu ile 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. Sütunlarla start endarasında aralıkları olan bir tablo rangesvarsayarsak, aşağıdaki sorguyla çarpık aralık uzunluğu değerinin farklı yüzdebirlik dilimlerini belirleyebilirsiniz:

SELECT APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges

Ö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, dolayısıyla 2'den fazla bitişik bölme aralığına yayılır.
  • 99. yüzdebirlik dilimdeki değer bölme boyutuysa, değer aralığı uzunluklarının yalnızca %1'i 11'den fazla bitişik bölme aralığına yayılır.
  • 99,9. yüzdebirlik 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ığına yayılır.
  • Aynı değer 99,99th, 99,999th yüzdebirlik değeri vb. değerler için de yinelenebilir.

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