Aller au contenu principal
Dernière mise à jour le

Optimisation des jointures de plages

Une jonction de plages permet de joindre deux tables en fonction d'une condition qui vérifie si une valeur se situe dans une plage donnée, ou si deux plages se chevauchent, au lieu de vérifier si deux valeurs sont égales. Par exemple, une query qui fait correspondre chaque événement de site web à la session utilisateur au cours de laquelle il s'est produit vérifie si le Timestamp de l'événement se situe entre l'heure de start et l'heure de fin de la session. Une query qui trouve des réunions prévues en même temps vérifie si deux plages de temps se chevauchent. L'optimisation de la jointure de plage dans Databricks Runtime peut considérablement améliorer les performances de ces requêtes.

De ce fait, une jointure peut comparer de nombreuses paires de lignes qui ne peuvent pas correspondre. L'optimisation des jointures de plages divise la plage de valeurs en compartiments et compare uniquement les lignes qui tombent dans des compartiments chevauchants. Consultez Taille du compartiment.

Dans Databricks SQL, Databricks optimise automatiquement les jointures par plage sans aucune configuration manuelle. Vous pouvez également ajuster manuellement les jointures par plage à l'aide d'indicateurs de jointure ou de la configuration de session pour tous les types de compute.

Point dans la jointure de plage d’intervalle​

Une jointure de plage d'intervalles ponctuels est une jointure dont la condition contient des prédicats spécifiant qu’une valeur d’une relation se situe entre deux valeurs de l’autre relation. Par exemple :

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

Jointure de plage de chevauchement d'intervalle​

Un join de plages de chevauchement d'intervalle est un join dans lequel la condition contient des prédicats spécifiant un chevauchement d'intervalles entre deux valeurs de chaque relation. Par exemple :

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

Optimisation des jointures de plages​

L'optimisation des opérations de jonction de plages s'applique aux jonctions qui :

  • Avoir une condition de jointure de point dans un intervalle ou de chevauchement d'intervalle.

  • N’utilisez que des valeurs numériques (entières, à virgule flottante ou décimales), DATE ou TIMESTAMP dans la condition de jointure de plage.

  • Utilisez des valeurs du même type dans la condition de jointure de plage. Les valeurs décimales doivent également avoir la même Monter en charge et la même précision.

  • Utilisez un type de jointure pris en charge :

    • INNER JOIN, pour toute jointure de plage
    • LEFT OUTER JOIN avec la valeur du point sur le côté gauche, ou RIGHT OUTER JOIN avec la valeur du point sur le côté droit, pour les jointures de plages d'intervalles de points uniquement

  • Disposer d'une taille de bin, soit dérivée automatiquement, soit spécifiée manuellement.

Jointures avec égalité numérique et conditions de plage​

Lorsqu'une condition de jointure inclut à la fois une condition d'égalité sur une colonne numérique et une condition de plage, l'optimiseur peut appliquer le binning à la colonne d'égalité numérique car elle satisfait aux exigences de type pour l'optimisation des jointures par plage. Cela peut entraîner l'affectation de la colonne d'égalité à des compartiments ou son exclusion de l'optimisation, ce qui réduit les performances.

Pour vous assurer que l'optimisation de la jointure de plage s'applique uniquement à la condition de plage prévue, convertissez les colonnes d'égalité numérique en STRING. Cela les exclut d'être considérées comme des colonnes de condition de plage.

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

Le même modèle s’applique aux autres colonnes numériques utilisées comme clés d’égalité, telles que DATE, les identifiants entiers ou les colonnes de partition clusterisées.

Taille du compartiment​

La *taille du compartiment* est un paramètre de réglage numérique qui divise le domaine des valeurs de la condition de plage en plusieurs compartiments de taille égale. Par exemple, avec une taille de bin de 10, l'optimisation divise le domaine en bins qui sont des intervalles de longueur 10. Si vous avez un point dans la condition de plage de p BETWEEN start AND end, et start est 8 et end est 22, cet intervalle de valeurs chevauche trois bins de longueur 10 – le 1er bin de 0 à 10, le 2e bin de 10 à 20 et le 3e bin de 20 à 30. Seuls les points qui tombent dans les trois mêmes bins doivent être considérés comme des correspondances de jointure possibles pour cet intervalle. Par exemple, si p est 32, il peut être exclu de se situer entre start de 8 et end de 22, car il se trouve dans l'intervalle de 30 à 40.

remarque
  • Pour DATE valeurs, la valeur de la taille des intervalles est interprétée comme des jours. Par exemple, une valeur de taille de compartiment de 7 représente une semaine.
  • Pour TIMESTAMP valeurs, la valeur de la taille du bin est interprétée en secondes. Pour une valeur inférieure à la seconde, utilisez une valeur fractionnaire. Par exemple, une valeur de taille de bin de 60 représente une minute, et une valeur de taille de bin de 0,1 représente 100 millisecondes.

Vous pouvez spécifier la taille du compartiment en utilisant une indication de jointure d'intervalle dans la query ou en définissant un parameter de configuration de session. Dans Databricks SQL, la taille du compartiment est automatiquement dérivée lorsque l'optimisation automatique des jointures de plage est activée.

Optimisation automatique des jointures de plage​

Dans Databricks SQL, Databricks détecte automatiquement les jointures de plages admissibles et déduit la taille de compartiment optimale en échantillonnant la table des intervalles. Cela supprime la nécessité de spécifier manuellement une taille de compartiment via des indicateurs ou la configuration de session.

L'optimisation automatique des jointures de plage est activée par default dans Databricks SQL. Pour la désactiver, définissez la configuration suivante :

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

Si vous spécifiez une taille de bin via un indice de jointure de plage ou une configuration de session, cette valeur remplace la taille de bin automatiquement dérivée.

Activer la jointure de plage en utilisant un conseil de jointure de plage​

Pour activer l'optimisation de la jonction de plages dans une query SQL, utilisez un indicateur de jonction de plages afin de spécifier la taille du bin. L'indicateur doit contenir le nom de l'une des relations jointes et le parameter de taille du bin numérique. Le nom de la relation est une table, une vue ou une sous-requête.

SQL
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)
remarque

Dans le troisième exemple, vous devez placer l’indication sur c. Ceci est dû au fait que les jointures sont associatives à gauche, de sorte que la query est interprétée comme (a JOIN b) JOIN c, et l'indice sur a s'applique à la jointure de a avec b et non à la jointure avec c.

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

Vous pouvez également placer une indication de jointure de plage sur l'un des DataFrames joints. Dans ce cas, l'indice contient uniquement le paramètre de taille de bac numérique.

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

Activer la jointure de plages à l’aide de la configuration de session​

Si vous ne souhaitez pas modifier la query, spécifiez la taille du compartiment en tant que paramètre de configuration.

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

Ce paramètre de configuration s'applique à toute jonction avec une condition de plage. Cependant, une taille de compartiment différente définie via une indication de jointure de plage remplace toujours celle définie via le paramètre.

Choisissez la taille du compartiment​

L'efficacité de l'optimisation des jointures de plages dépend du choix de la taille de bin appropriée.

Une petite taille de compartiment entraîne un plus grand nombre de compartiments, ce qui aide à filtrer les correspondances potentielles. Une petite taille de compartiment devient inefficace si les intervalles de valeur sont beaucoup plus grands que la taille du compartiment, ce qui fait que les intervalles chevauchent de nombreux compartiments. Par exemple, avec une condition p BETWEEN start AND end où start est 1 000 000 et end est 1 999 999, et une taille de compartiment de 10, l’intervalle chevauche 100 000 compartiments.

Si la longueur de l’intervalle est relativement uniforme et connue, définissez la taille du bin sur la longueur d’intervalle type. Pour les intervalles variés et biaisés, choisissez une taille de bin qui filtre efficacement les intervalles courts tout en évitant que les intervalles longs ne chevauchent trop de bins. Pour une table ranges avec des intervalles entre les colonnes start et end, déterminez les centiles de la longueur d’intervalle biaisée avec cette query :

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

Le cast de chaque colonne en DOUBLE avant la soustraction garantit le fonctionnement de la query, que les colonnes soient numériques, DATE ou TIMESTAMP valeurs.

Définissez la taille du bin sur la plus grande de ces valeurs :

  • Le 90e percentile des longueurs d'intervalle. À cette taille, au plus 10 % des intervalles s’étendent sur plus de 2 compartiments.
  • Le 99e centile divisé par 10. À cette taille, au plus 1 % des intervalles s'étendent sur plus de 11 compartiments.
  • Le 99,9e centile divisé par 100. À cette taille, au plus 0,1 % des intervalles s’étendent sur plus de 101 compartiments.

Pour limiter des intervalles encore plus longs, poursuivez le modèle avec les 99,99e et 99,999e centiles. Utilisez le résultat comme point de départ, puis ajustez la taille des bacs pour votre charge de travail.

Sur cette page