Tartomány join optimalizálása

A tartomány join akkor fordul elő, ha két kapcsolat egy intervallum- vagy intervallumfedésű ponttal van összekapcsolva. A Databricks Futtatókörnyezetben join optimalizálási támogatás nagyságrendekkel javíthatja a lekérdezési teljesítményt, de gondos manuális finomhangolást igényel.

A Databricks join tippek használatát javasolja a tartományillesztésekhez, ha a teljesítmény gyenge.

Pont az intervallumtartományban join

A intervallumtartományban a pont egy olyan , amelyben a feltétel predikátumokat tartalmaz, meghatározva, hogy az egyik relációból származó érték a másik relációból származó két között van. Példa:

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

Intervallumfedő tartomány join

Az intervallumok átfedési tartománya join olyan join, amelyben a feltétel predikátumokat tartalmaz, amelyek két values közötti intervallumok átfedését határozzák meg az egyes kapcsolatokból. Példa:

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

Tartomány join optimalizálása

A tartomány join optimalizálása a következő illesztéseknél történik:

• Olyan feltétellel rendelkezik, amely intervallumpontként vagy intervallumfedéstartomány-pontként értelmezhető, join.
• A tartomány join feltételben érintett összes values numerikus típusú (integrál, lebegőpontos, decimális), DATEvagy TIMESTAMP.
• A join tartomány feltételében érintett összes values azonos típusú. A decimális típus esetében a values is azonos méretűnek és pontosságúnak kell lennie.
• Ez egy INNER JOINvagy az intervallumtartományban joinpont esetén egy LEFT OUTER JOIN a bal oldalon, vagy RIGHT OUTER JOIN a jobb oldalon lévő pontértékkel.
• Tárolóméret-finomhangolási paraméterrel rendelkezik.

Doboz mérete

A tárolóméret egy numerikus hangolási paraméter, amely a tartományfeltétel values tartományát több tárolóra egyenlő méretűre osztja. Például 10-es tárolómérettel az optimalizálás a tartományt 10 hosszúságú intervallumokra osztja. Ha egy tartományfeltétele p BETWEEN start AND endstart 8 és end 22, akkor ez az értékintervallum három, 10-től 10-től 10-re, a második 10-től 20-ra, a harmadik pedig 20-tól 30-ra átfedésben van. Csak azokat a pontokat kell figyelembe venni, amelyek ugyanabba a három kategóriába esnek, mint potenciális join egyezések az adott intervallumhoz. Ha például p 32, akkor kizárható, hogy 8 és 22 között esik, mert 30 és start 40 közé end esik.

A rekeszméretet megadhatja akár egy tartomány join tipp használatával a lekérdezésben, akár egy munkamenet beállítási paraméter beállításával. A tartomány join optimalizálása csak akkor lesz alkalmazva , ha manuálisan adja meg a tárolóhely méretét. Szakasz : A raktárhely méretének kiválasztása azt ismerteti, hogyan választhatja ki az optimális tárolóméretet.

Tartomány join engedélyezése egy tartomány join-tipp használatával

Ha engedélyezni szeretné a tartomány join optimalizálást egy SQL-lekérdezésben, használhat egy tartományt, join tippet a tárolóhely méretének megadásához. A tippnek tartalmaznia kell az egyik összekapcsolt kapcsolat relációs nevét és a numerikus tárolóméret paramétert. A kapcsolati név lehet table, nézet vagy al-lekérdezés.

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

join jelzőt is elhelyezhet az egyik összekapcsolt adatkereten. Ebben az esetben a tipp csak a numerikus tárolóméret paramétert tartalmazza.

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

Tartomány join engedélyezése munkamenet-konfigurációval

Ha nem szeretné módosítani a lekérdezést, konfigurációs paraméterként megadhatja a tároló méretét.

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

Ez a konfigurációs paraméter minden tartományfeltétellel rendelkező join vonatkozik. Azonban egy set méretű tároló, amely join tartományon keresztül érvényes jelzés, mindig felülírja a paraméterben megadott set-őt.

A tároló méretének kiválasztása

A join optimalizálási tartomány hatékonysága a megfelelő tárolóméret kiválasztásától függ.

A kis méretű tárolók nagyobb számú tárolót eredményeznek, ami segít a lehetséges egyezések szűrésében. Azonban nem hatékony, ha a tároló mérete jelentősen kisebb, mint a tapasztalt értékintervallumok, és az értékintervallumok több intervallumot fednek át. Ha például egy feltétel esetén p BETWEEN start AND end-nál a wherestart értéke 1 000 000, a end értéke pedig 1 999 999, és a dobozméret 10, akkor az értékintervallum 100 000 dobozzal átfedésben van.

Ha az intervallum hossza meglehetősen egységes és ismert, javasoljuk, hogy set a tároló méretét az értékintervallum jellemző várható hosszára. Ha azonban az intervallum hossza eltérő és ferde eloszlású, akkor egyensúlyt kell találni úgy, hogy set egy olyan tárolóméretet, amely hatékonyan kiszelektálja a rövid időközöket, megakadályozva, hogy a hosszú intervallumok túl sok tárolót átfedjenek. Tegyük fel, hogy egy tablerangeslétezik, ahol az intervallumok columnsstart és endközöttiek; a ferde intervallum hosszának különböző percentiliseit az alábbi lekérdezéssel határozhatja meg:

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

A dobozméret ajánlott beállítása a 90. percentilisnél az érték maximuma, vagy a 99. percentilis értéke 10-vel osztva, vagy a 99,9. percentilis értéke 100-tal osztva stb. Az indok a következő:

• Ha a 90. percentilis értéke a raktárhely mérete, az értékintervallum hosszának csak 10%-a hosszabb, mint a doboz időköze, tehát több mint 2 szomszédos intervallumra terjed ki.
• Ha a 99. percentilis értéke a tároló mérete, az értékintervallum-hosszok csak 1%-a hosszabb 11 szomszédos intervallumnál.
• Ha a 99,9. percentilis értéke a tároló mérete, az értékintervallum-hosszok csak 0,1%-a több mint 101 szomszédos intervallumra terjed ki.
• Ugyanez megismételhető a values esetében a 99,99-edik, a 99,999-edik percentilisen, és így tovább, ha szükséges.

A leírt módszer korlátozza a több intervallumot átfedő ferde hosszú értékintervallumok mennyiségét. Az így kapott bin size érték csak kiindulópont a finomhangoláshoz; a tényleges eredmények az adott számítási feladattól függhetnek.