Compartir a través de

Optimización de la combinación de intervalos

  • Artículo

Una combinación de intervalos se produce cuando se unen dos relaciones mediante una condición de intervalo de punto o de superposición de intervalos. La compatibilidad con la optimización de combinación de intervalos de Databricks Runtime puede aportar una mejora de los órdenes de magnitud en el rendimiento de las consultas, pero requiere un ajuste manual preciso.

Databricks recomienda usar sugerencias de combinación para combinaciones de intervalo cuando el rendimiento es deficiente.

Combinación de intervalo de punto

Una combinación de intervalo de punto es una combinación en la que la condición contiene predicados que especifican que un valor de una relación se encuentra entre dos valores de la otra relación. Por ejemplo:

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

Combinación de intervalo de superposición

Una combinación de intervalo de superposición de intervalos es una combinación en la que la condición contiene predicados que especifican una superposición de intervalos entre dos valores de cada relación. Por ejemplo:

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

Optimización de la combinación de intervalos

La optimización de combinación de intervalos se realiza en aquellas combinaciones que:

  • Tienen una condición que se puede interpretar como una combinación de intervalo de punto o de superposición de intervalos.
  • Todos los valores implicados en la condición de combinación de intervalos son de tipo numérico (entero, número de punto flotante, decimal), DATE o TIMESTAMP.
  • Todos los valores implicados en la condición de combinación de intervalos son del mismo tipo. En el caso del tipo decimal, los valores también deben ser de la misma escala y precisión.
  • Se trata de una combinación INNER JOIN o, en el caso de una combinación de intervalo de punto, LEFT OUTER JOIN con un valor de punto en el lado izquierdo o RIGHT OUTER JOIN con un valor de punto en el lado derecho.
  • Tienen un parámetro de ajuste del tamaño del rango.

Tamaño del rango

El tamaño del rango es un parámetro de ajuste numérico que divide el dominio de valores de la condición de intervalo en varios rangos del mismo tamaño. Por ejemplo, con un tamaño de rango de 10, la optimización divide el dominio en rangos de longitud 10. Si tiene una condición de intervalo de punto de p BETWEEN start AND end, y start es 8 y end es 22, este intervalo de valor se superpone con tres rangos de longitud 10: el primer rango de 0 a 10, el segundo rango de 10 a 20 y el tercer rango de 20 a 30. Solo los puntos que se encuentran dentro de los tres mismos intervalos deben considerarse como posibles coincidencias de combinación para ese intervalo. Por ejemplo, si p es 32, se puede descartar que se encuentre entre start de 8 y end de 22, porque se encuentra en el rango de 30 a 40.

Nota:

  • Para los valores DATE, el valor del tamaño del intevalo se interpreta como días. Por ejemplo, el valor de un tamaño de rango de 7 representa una semana.
  • Para los valores TIMESTAMP, el valor del tamaño del intevalo se interpreta como segundos. Si se requiere un valor de subsegundos, se pueden usar valores fraccionales. Por ejemplo, el valor de un tamaño de rango de 60 representa un minuto, y el valor de un tamaño de rango de 0,1 representa 100 milisegundos.

Puede especificar el tamaño del rango mediante una indicación de combinación de intervalos en la consulta o estableciendo un parámetro de configuración de sesión. La optimización de la combinación de intervalos solo se aplica si especifica manualmente el tamaño del rango. En la sección Elección del tamaño del rango se describe cómo elegir un tamaño de rango óptimo.

Habilitación de la combinación de intervalos mediante una indicación de combinación de intervalos

Para habilitar la optimización de combinación de intervalos en una consulta SQL, puede usar una indicación de combinación de intervalos para especificar el tamaño del rango. La indicación debe contener el nombre de la relación de una de las relaciones unidas y el parámetro numérico de tamaño del rango. El nombre de la relación puede ser una tabla, una vista o una subconsulta.

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)

Nota:

En el tercer ejemplo, debe colocar la indicación en c. Esto se debe a que las combinaciones son asociativas a la izquierda, por lo que la consulta se interpreta como (a JOIN b) JOIN c, y la indicación en a se aplica a la combinación de a con b, y no a la combinación con c.

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

También puede colocar una indicación de combinación de intervalos en uno de los DataFrames unidos. En ese caso, la indicación solo contiene el parámetro numérico de tamaño del rango.

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

Habilitación de la combinación de intervalos mediante la configuración de la sesión

Si no quiere modificar la consulta, puede especificar el tamaño del rango como un parámetro de configuración.

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

Este parámetro de configuración se aplica a cualquier combinación con una condición de intervalo, pero si se establece otro tamaño de rango a través de una indicación de combinación de intervalos, este siempre invalida al establecido a través del parámetro.

Elección del tamaño del rango

La eficacia de la optimización de combinación de intervalos depende de la elección del tamaño de rango adecuado.

Un tamaño de rango pequeño da como resultado un mayor número de rangos, lo que ayuda a filtrar las posibles coincidencias, pero resulta ineficaz si el tamaño del rango es significativamente menor que los intervalos de valor encontrados y los intervalos de valor se superponen a varios intervalos de rango. Por ejemplo, con una condición p BETWEEN start AND end, donde start es 1 000 000 y end es 1 999 999 y un tamaño de rango de 10, el intervalo de valor se superpone con 100 000 rangos.

Si la longitud del intervalo es bastante uniforme y conocida, se recomienda establecer el tamaño del rango en la longitud esperada típica del intervalo de valor. Pero si la longitud del intervalo es variable y sesgada, se debe encontrar un equilibrio para establecer un tamaño de rango que filtre los intervalos cortos de forma eficaz, al tiempo que evita que los intervalos largos se superpongan a demasiados rangos. Suponiendo una tabla ranges, con intervalos que se encuentran entre las columnas start y end, puede determinar distintos percentiles del valor de longitud de intervalo sesgado con la consulta siguiente:

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

Un valor recomendado de tamaño de rango sería el máximo del valor en el percentil 90, el valor en el percentil 99 dividido entre 10, el valor en el percentil 99,9 dividido entre 100, etc. La razón es la siguiente:

  • Si el valor en el percentil 90 es el tamaño del rango, solo el 10 % de las longitudes del intervalo de valor son más largas que el intervalo del rango, por lo que abarca más de 2 intervalos de rango adyacentes.
  • Si el valor en el percentil 99 es el tamaño del rango, solo el 1 % de las longitudes del intervalo de valor abarcan más de 11 intervalos de rango adyacentes.
  • Si el valor en el percentil 99,9 es el tamaño del rango, solo el 0,1 % de las longitudes del intervalo de valor abarcan más de 101 intervalos de rango adyacentes.
  • Lo mismo se puede repetir para los valores en el percentil 99,99, el percentil 99,999, y así sucesivamente si es necesario.

El método descrito limita la cantidad de intervalos de valores largos sesgados que se superponen a varios intervalos de rango. El valor de tamaño del rango obtenido de esta manera solo es un punto de partida para realizar un ajuste preciso. Los resultados reales pueden depender de la carga de trabajo específica.