Optimización de la combinación de rangos

Una unión por rango se produce cuando dos relaciones se unen mediante una condición de punto en intervalo o de solapamiento entre intervalos. El uso de la optimización de combinación de intervalos en Databricks Runtime puede mejorar considerablemente el rendimiento de las consultas.

En Databricks SQL, Azure Databricks optimiza automáticamente las combinaciones de intervalo sin ninguna configuración manual. También puede ajustar manualmente las uniones por rango mediante pistas de combinación o la configuración de la sesión para todos los tipos de cómputo.

Union de intervalo de rango del punto

Una combinación de punto en intervalo es una combinación cuya condición contiene predicados que especifican que un valor de una relación está comprendido 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;

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 combinación de rangos

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.
• Tener un tamaño de cubo, ya sea derivado o manualmente especificado.

Combinaciones con condiciones de igualdad numérica y de intervalo

Cuando una condición de combinación incluye una condición de igualdad en una columna numérica y una condición de intervalo, el optimizador puede aplicar la discretización a la columna de igualdad numérica porque cumple los requisitos de tipo para la optimización de combinación de intervalos. Esto puede hacer que la columna de igualdad se asigne a bins o se excluya de la optimización, reduciendo el rendimiento.

Para garantizar que la optimización de unión por intervalo se aplique únicamente a la condición de intervalo prevista, convierta las columnas numéricas de igualdad a STRING. Esto las excluye para ser consideradas como columnas de condición de rango.

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;

El mismo patrón se aplica a otras columnas numéricas que se usan como claves de igualdad, como DATE, identificadores enteros o columnas de partición agrupadas.

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.

Puede especificar el tamaño del intervalo usando una pista de unión por rango en la consulta o estableciendo un parámetro de configuración de sesión. En Databricks SQL, el tamaño del contenedor se deriva automáticamente cuando se habilita la optimización de combinación de intervalos automática.

Optimización automática de combinación por rango

En Databricks SQL, Azure Databricks detecta automáticamente las uniones por rango válidas y calcula el tamaño óptimo de bin mediante el muestreo de la tabla de intervalos. Esto elimina la necesidad de especificar manualmente el tamaño del intervalo mediante sugerencias o la configuración de la sesión.

La optimización de combinación automática de intervalos está habilitada de forma predeterminada en Databricks SQL. Para deshabilitarlo, establezca la siguiente configuración:

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

Si se especifica un tamaño de intervalo mediante una sugerencia de combinación por rango o una configuración de sesión, ese valor sustituye al tamaño de intervalo derivado automáticamente.

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

Para habilitar la optimización de combinación de intervalos en una consulta SQL, use una sugerencia de combinación de intervalo para especificar el tamaño de la bandeja. 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)

#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, especifique 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.

Elegir el tamaño de la papelera

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

Convertir cada columna a DOUBLE antes de restar garantiza que la consulta funcione tanto si las columnas son numéricas como si son de tipo DATE o TIMESTAMP.

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

• 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, etc. 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 de cubo obtenido de esta manera es solo un punto de partida para el ajuste preciso; Los resultados reales pueden depender de la carga de trabajo específica.