Optimización de la combinación de rangos

Una unión de rango une dos tablas bajo una condición que comprueba si un valor está dentro de un rango, o si dos rangos se solapan, en lugar de comprobar si dos valores son iguales. Por ejemplo, una consulta que empareje cada evento del sitio web con la sesión de usuario en la que ocurrió comprueba si la marca de tiempo del evento se sitúa entre el inicio y el fin de la sesión. Una consulta que encuentra reuniones programadas al mismo tiempo comprueba si dos intervalos de tiempo se solapan. La optimización de la unión de rangos en Databricks Runtime puede mejorar considerablemente el rendimiento de estas consultas.

Por eso, una unión podría comparar muchos pares de filas que no pueden coincidir. La optimización de unión de rangos divide el rango de valores en intervalos y solo compara filas que caen en intervalos superpuestos. Consulte Tamaño de los intervalos.

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 uniones por rango se aplica a uniones que:

  • Condición de unión por punto en intervalo o solapamiento de intervalos.
  • Utiliza solo números (integral, coma flotante o decimal), DATE o TIMESTAMP valores en la condición de unión de rango.
  • Utiliza valores del mismo tipo en la condición de unión de rango. Los valores decimales también deben tener la misma escala y precisión.
  • Utiliza un tipo de unión soportado:
    • INNER JOIN, para cualquier range join
    • LEFT OUTER JOIN con el valor en puntos en el lado izquierdo, o RIGHT OUTER JOIN con el valor en puntos en el lado derecho, solo para uniones de puntos en rangos de intervalos
  • Definir un tamaño de intervalo, ya sea derivado automáticamente o especificado manualmente.

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.

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. Para un valor inferior a un segundo, se utiliza un valor fraccionario. 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 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 desactivarlo, configura esta 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 es 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, 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, Un tamaño de bin pequeño se vuelve ineficiente si los intervalos de valor son mucho mayores que el tamaño del bin, lo que hace que los intervalos se solapen con muchos bins. 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 bin de 10, el intervalo se solapa con 100.000 bins.

Si la longitud del intervalo es bastante uniforme y conocida, fija el tamaño del intervalo en la longitud típica del intervalo. Para intervalos sesgados variables, elige un tamaño de bin que filtre los intervalos cortos de forma eficiente mientras evita que los intervalos largos se solapen en demasiados bins. Para una tabla ranges con intervalos entre columnas start y end, determina los percentiles de la longitud de intervalo sesgada con esta consulta:

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.

Establece el tamaño del contenedor al mayor de estos valores:

  • El percentil 90 de las longitudes de intervalo. A este tamaño, como máximo el 10 % de los intervalos abarcan más de 2 clases.
  • El percentil 99 dividido entre 10. A este tamaño, como máximo 1% de intervalos abarcan más de 11 compartimentos.
  • El percentil 99,9 dividido entre 100. A este tamaño, como máximo el 0,1 % de los intervalos abarcan más de 101 clases.

Para limitar intervalos aún más largos, continúa el patrón con los percentiles 99,99.º y 99,999.º. Usa el resultado como punto de partida y luego ajusta el tamaño del bin según tu carga de trabajo.

  • Última actualización el