Tartomány-összekapcsolások optimalizálása

A tartományillesztés akkor történik, amikor két relációt egy pont-intervallum vagy intervallumátfedési feltétel alapján kapcsolnak össze. A databricks-futtatókörnyezetben a tartományillesztés optimalizálása jelentősen javíthatja a lekérdezési teljesítményt.

A Databricks SQL-ben Azure Databricks automatikusan optimalizálja a tartományillesztéseket manuális konfiguráció nélkül. A tartományalapú illesztéseket manuálisan is finomhangolhatja illesztési tippekkel vagy a munkamenet konfigurációjával minden számítási típus esetén.

Pont az intervallum-csatlakozásban

Az intervallumtartomány-illesztési pont olyan illesztés, amelynek feltétele predikátumokat tartalmaz, amelyek meghatározzák, hogy az egyik reláció értékei a másik reláció két értéke közé esnek. 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ány-összekapcsolások optimalizálása

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

• Olyan feltétellel rendelkezik, amely intervallumpontként vagy intervallumátfedési csatlakozáské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én az értékeknek azonos léptékűnek és pontosságúnak kell lenniük.
• INNER JOIN, vagy intervallumtartomány-illesztés esetén LEFT OUTER JOIN bal oldali pontértékkel, illetve RIGHT OUTER JOIN jobb oldali pontértékkel.
• A tárolóméret vagy automatikusan meghatározott, vagy manuálisan megadott.

Numerikus egyenlőség és tartományfeltételek összekapcsolása

Ha egy illesztési feltétel egyenlőségi feltételt is tartalmaz egy numerikus oszlopon és egy tartományfeltételen, az optimalizáló dobozolást alkalmazhat a numerikus egyenlőség oszlopra, mert megfelel a tartományillesztés optimalizálására vonatkozó típuskövetelményeknek. Ez azt eredményezheti, hogy az egyenlőségi oszlopot bin-ekhez rendelik, vagy kizárják az optimalizálásból, ami csökkenti a teljesítményt.

Annak érdekében, hogy a tartományillesztés optimalizálása csak a kívánt tartományfeltételre vonatkozhasson, adja meg a numerikus egyenlőségi oszlopokat STRING. Ez kizárja, hogy tartományfeltétel-oszlopokként vegyék őket figyelembe.

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;

Ugyanez a minta vonatkozik az egyenlőségi kulcsként használt más numerikus oszlopokra is, például DATEegész számazonosítókra vagy fürtözött partícióoszlopokra.

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 a p BETWEEN start AND end tartományfeltétel alatt van, és start értéke 8, valamint end értéke 22, akkor ez az értéktartomány átfedi három, 10 hosszúságú tartályt: az első tartály a 0-tól 10-ig, a második 10-től 20-ig, és a harmadik 20-tól 30-ig terjed. 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, kizárható annak lehetősége, hogy 8 és start 22 között van, mert valójában a 30 és end 40 közötti tartományba esik.

Megjegyzés

• Értékek DATE esetén a dobozméret értékét napokként értelmezik. A 7-es dobozméret például egy hetet jelöl.
• A TIMESTAMP értékek esetén a bin méretét másodpercekben értelmezi. Ha egy másodperc alatti értékre van szükség, 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 Databricks SQL-ben a binméret automatikusan kerül meghatározásra, ha engedélyezve van az automatikus tartományillesztési optimalizálás.

Az automatikus tartomány-összekapcsolás optimalizálása

A Databricks SQL-ben Azure Databricks automatikusan észleli a megfelelő tartományillesztéseket, és az intervallumtáblából mintavételezéssel nyeri ki az optimális tárolóhely méretét. Ez eltávolítja a tárolóméret manuális megadásának szükségességét tippek vagy munkamenet-konfiguráció segítségével.

A Databricks SQL-ben alapértelmezés szerint engedélyezve van az automatikus tartománybeillesztés optimalizálása. A letiltásához állítsa be a következő konfigurációt:

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

Ha tartományillesztési tipp vagy munkamenet-konfiguráció segítségével ad meg egy tárolóméretet, az az érték felülírja az automatikusan származtatott tárolóhely méretét.

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, használjon egy tartománybeillesztés-tippet a tárolóhely méretének megadásához. A tippnek tartalmaznia kell az egyik csatlakoztatott reláció nevét és a numerikus bin méret paramétert. A reláció neve lehet tábla, nézet vagy 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)

Megjegyzés

A harmadik példában a tippet a következőre kell helyeznie c: Ennek az az oka, hogy a kapcsolatok bal asszociatívak, ezért a lekérdezést a rendszer úgy értelmezi, hogy (a JOIN b) JOIN c, és a a javaslat a a és b közötti illesztésre vonatkozik, nem pedig a c-vel való illesztésre.

#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 az utalás csak a numerikus rekeszmé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, adja meg a tároló méretét konfigurációs paraméterként.

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 rekesz mérete jelentősen kisebb, mint a tapasztalt értékintervallumok, és az értékintervallumok több rekeszt fednek át. Ha például egy feltétel p BETWEEN start AND end, ahol start 1 000 000 és end 1 999 999, és a bin mérete 10, akkor az értékintervallum 100 000 bint fed át.

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 rendelkezikstartend, a ferde intervallumhossz értékének különböző percentiliseit az alábbi lekérdezéssel határozhatja meg:

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;

Az, hogy kivonás előtt minden oszlopot DOUBLE típussá alakítunk, biztosítja, hogy a lekérdezés akkor is működjön, függetlenül attól, hogy az oszlopok numerikus, DATE vagy TIMESTAMP értékeket tartalmaznak.

A bin méretének ajánlott beállítása a következő értékek közül a legnagyobb legyen: a 90. percentilisnél kapott érték, vagy a 99. percentilisnél kapott érték 10-zel osztva, vagy a 99,9. percentilisnél kapott érték 100-zal osztva, stb. Az indoklás a következő:

• Ha a 90. percentilis értéke az osztály mérete, akkor az érték intervallumának hosszai közül csak 10% túlhaladja az osztály intervallumát, tehát több mint 2 szomszédos osztály intervallumot ölel fel.
• Ha a 99. percentilis értéke a csoportméret, az értékintervallum-hosszok csak 1%-a terjedhet ki több mint 11 szomszédos csoportintervallumra.
• 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-nél, a 99,999-es percentilisnél stb.

A leírt módszer korlátozza a több intervallumot átfedő eloszlott, hosszú értékintervallumok számá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.