Tartományillesztések optimalizálása

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

A Databricks ajánlott illesztési tippeket használni a tartományillesztésekhez, ha a teljesítmény gyenge.

Pont az intervallumtartomány illesztésében

Az intervallumtartomány-illesztés egy olyan illesztés, amelyben a feltétel predikátumokat tartalmaz, amelyek meghatározzák, hogy az egyik relációból származó érték a másik reláció két értéke 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;

Intervallumok átfedése tartományhoz illesztés

Az intervallumfedő tartomány illesztése olyan illesztés, amelyben a feltétel predikátumokat tartalmaz, amelyek az egyes relációk két értéke közötti intervallumok átfedését határozzák meg. 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ányillesztések optimalizálása

A tartományillesztés optimalizálása olyan illesztések esetén történik, amelyek:

• Olyan feltétellel rendelkezik, amely intervallum- vagy intervallumátfedő pontként értelmezhető.
• A tartományillesztés feltételében szereplő összes érték numerikus típusú (integrál, lebegőpontos, decimális) DATEvagy TIMESTAMP.
• A tartományillesztés feltételében szereplő összes érték azonos típusú. A decimális típus esetében az értékeknek azonos léptékűnek és pontosságúnak kell lenniük.
• INNER JOINAz intervallumtartomány-illesztés LEFT OUTER JOIN pontértékének vagy pontértékének a bal oldalán, illetve RIGHT OUTER JOIN a jobb oldalon lévő pontérték esetén.
• Tárolóméret-finomhangolási paraméterrel rendelkezik.

Doboz mérete

A tárolóméret egy numerikus finomhangolási paraméter, amely a tartományfeltétel értéktartományát több azonos méretű tárolóra osztja fel. 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. Az adott intervallumhoz csak az ugyanazon a három tárolón belül eső pontok tekinthetők lehetséges illesztési egyezésnek. Ha például p 32, akkor kizárható, hogy 8 és 22 között esik, mert 30 és end 40 közé start esik.

Feljegyzés

• Értékek esetén DATE a dobozméret értékét napokként értelmezi a rendszer. A 7-es dobozméret például egy hetet jelöl.
• Értékek esetén TIMESTAMP a tárolóméret értékét másodpercként értelmezi a rendszer. Ha másodlagos értékre van szükség, a törtértékek használhatók. Például egy 60-os dobozméret egy percet, a 0,1-es érték pedig 100 ezredmásodpercet jelöl.

A tároló méretét a lekérdezésben található tartományillesztés-tipp használatával vagy egy munkamenetkonfigurációs paraméter beállításával adhatja meg. A tartományillesztés 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ányillesztés engedélyezése tartományillesztés-tipp használatával

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

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)

Feljegyzés

A harmadik példában a tippet a következőre kell helyeznie c: Ennek az az oka, hogy az illesztések asszociatívak, ezért a lekérdezést a rendszer a következőképpen értelmezi(a JOIN b) JOIN c, és a tipp a az illesztés és b nem az illesztés cösszekapcsolására a vonatkozik.

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

Egy tartományillesztési tippet is elhelyezhet az egyik csatlakoztatott DataFrame-hez. 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 csatlakoztatásának 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 a tartományfeltételekkel rendelkező összes illesztésre vonatkozik. A tartománybeillesztésen keresztül beállított másik tárolóméret azonban mindig felülbírálja a paraméteren keresztüli készletet.

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

A tartományillesztés optimalizálásának hatékonysága a megfelelő tárolóhely méretének 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 p BETWEEN start AND endstart 1 000 000 és end 1 999 999, és a tároló mérete 10, az értékintervallum 100 000 tárolóval átfedésben van.

Ha az intervallum hossza meglehetősen egységes és ismert, javasoljuk, hogy állítsa be a tároló méretét az értékintervallum szokásos várható hosszára. Ha azonban az intervallum hossza eltérő és ferde, akkor egy olyan tárolóméretet kell találni, amely hatékonyan szűri a rövid időközöket, és megakadályozza, hogy a hosszú intervallumok túl sok tárolót fedjenek át. Feltételezve, hogy egy tábla rangesoszlopközi intervallumokkal rendelkezikendstart, a ferde intervallumhossz értékének 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 99,99-es, a 99,999-es percentilis é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.