Optimización de la combinación de rangos

Una unión de rango se produce cuando se unen dos relaciones mediante una condición de punto en el intervalo o de superposición de intervalos. La compatibilidad con la optimización de uniones por intervalos en Databricks Runtime puede aportar una mejora de órdenes de magnitud en el rendimiento de las consultas, pero requiere una afinación manual cuidadosa.

Databricks recomienda usar indicaciones de uniones para uniones de rango cuando el rendimiento es deficiente.

Union de intervalo de rango del punto

Una unión de punto en intervalo es una unión en la que la condición contiene predicados que especifican que un valor de una relación está entre dos valores en 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;

Unión de intervalos con rango de superposición

Una unión de superposición de intervalos es una unió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 unión por intervalos

La optimización de unión de rangos se realiza en aquellas uniones 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 un INNER JOIN, o, en el caso de una unión de intervalo de punto, un LEFT OUTER JOIN con el valor del punto en el lado izquierdo, o un RIGHT OUTER JOIN con el valor del punto en el lado derecho.
• Tienen un parámetro de ajuste del tamaño del bin.

Tamaño del intervalo

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 contenedor de 10, la optimización divide el dominio en contenedores que son intervalos 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 intervalo se interpreta como días. Por ejemplo, un valor del tamaño del bin de 7 representa una semana.
• Para los valores TIMESTAMP, el valor del tamaño del intervalo 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 contenedor de 60 representa un minuto (60 segundos), y el valor de un tamaño de contenedor de 0,1 representa 100 milisegundos.

Puede especificar el tamaño del contenedor mediante una sugerencia de unión por rango 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.

Habilita la combinación de intervalos mediante una sugerencia de combinación de intervalos

Para activar la optimización de unión de rangos en una consulta SQL, puede usar una sugerencia de combinación de intervalos para especificar el tamaño del bin. La indicación debe incluir el nombre de una de las relaciones integradas y el parámetro numérico de tamaño de celda. 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 sugerencia de combinación por rango en uno de los DataFrames unidos. En ese caso, la indicación solo contiene el parámetro numérico de tamaño del contenedor.

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

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

Si no desea modificar la consulta, puede especificar el tamaño de la bandeja como 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, Sin embargo, si se establece un tamaño de agrupación diferente a través de una sugerencia de combinación de rangos, este siempre invalida al establecido a través del parámetro.

Elija el tamaño del contenedor

La efectividad de la optimización de combinación de rangos depende de la elección del tamaño de intervalo 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, Sin embargo, resulta ineficaz si el tamaño del bin es significativamente menor que los intervalos de valor encontrados y los intervalos de valor se superponen a varios intervalos de bin. 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 ajustar el tamaño de la agrupación a la longitud esperada típica del intervalo de valores. 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 bin, solo el 10% de las longitudes del intervalo de valor son más largas que el intervalo del bin, por lo que abarcan más de 2 intervalos de bin adyacentes.
• Si el valor en el percentil 99 es el tamaño del contenedor, solo el 1 % de las longitudes del intervalo de valor abarcan más de 11 intervalos de contenedor adyacentes.
• Si el valor en el percentil 99,9 es el tamaño del contenedor, solo el 0,1 % de las longitudes del intervalo de valores abarcan más de 101 intervalos de contenedores 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 del tamaño de bin obtenido de esta manera es solo un punto de partida para realizar un ajuste preciso; los resultados reales pueden depender de la carga de trabajo específica.