Pular para o conteúdo principal
Última atualização em

Alcance join otimização

Uma junção de intervalo ocorre quando duas relações se unem usando um ponto em um intervalo ou uma condição de sobreposição de intervalo. O uso da otimização de range join no Databricks Runtime pode melhorar significativamente o desempenho das queries.

No Databricks SQL, o Databricks otimiza automaticamente joins de intervalo sem nenhuma configuração manual. Também é possível ajustar manualmente as junções de intervalo usando dicas de junção ou configuração de sessão para todos os tipos de compute.

Ponto na faixa de intervalo join​

Uma joinde ponto em intervalo é uma join cuja condição contém predicados que especificam que um valor de uma relação está entre dois valores da outra relação. Por exemplo:

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;

Faixa de sobreposição de intervalo join​

Um intervalo de sobreposição de intervalo join é um join no qual a condição contém predicados que especificam uma sobreposição de intervalos entre dois valores de cada relação. Por exemplo:

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;

Alcance join otimização​

A otimização do intervalo join é realizada para essa união:

  • Ter uma condição que possa ser interpretada como um ponto no intervalo ou intervalo de sobreposição de intervalo join.
  • Todos os valores envolvidos na condição de intervalo join são do tipo numérico (integral, ponto flutuante, decimal), DATE ou TIMESTAMP.
  • Todos os valores envolvidos na condição join de intervalo são do mesmo tipo. No caso do tipo decimal, os valores também devem ser da mesma escala e precisão.
  • É um INNER JOIN ou, no caso de um ponto no intervalo join, um LEFT OUTER JOIN com o valor do ponto no lado esquerdo ou RIGHT OUTER JOIN com o valor do ponto no lado direito.
  • O tamanho do compartimento pode ser automaticamente derivado ou especificado manualmente.

unir com condições de igualdade numérica e intervalo​

Quando uma condição join inclui tanto uma condição de igualdade em uma coluna numérica quanto uma condição de intervalo, o otimizador pode aplicar o agrupamento à coluna de igualdade numérica, pois ela atende aos requisitos de tipo para otimização join por intervalo. Isso pode resultar na atribuição da coluna de igualdade a intervalos ou na sua exclusão da otimização, reduzindo o desempenho.

Para garantir que a otimização join intervalo se aplique apenas à condição de intervalo pretendida, converta as colunas de igualdade numérica para STRING. Isso as exclui da consideração como colunas de condição de intervalo.

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;

O mesmo padrão se aplica a outras colunas numéricas usadas como chave de igualdade, como DATE, identificadores inteiros ou colunas de partição agrupadas.

Tamanho do compartimento​

O tamanho do compartimento é um parâmetro de ajuste numérico que divide o domínio de valores da condição do intervalo em vários compartimentos do mesmo tamanho. Por exemplo, com um tamanho de compartimento de 10, a otimização divide o domínio em compartimentos com intervalos de comprimento 10. Se você tiver um ponto na condição de intervalo de p BETWEEN start AND end, start for 8 e end for 22, esse intervalo de valores se sobreporá a três compartimentos de comprimento 10 — o primeiro compartimento de 0 a 10, o segundo compartimento de 10 a 20 e o terceiro compartimento de 20 a 30. Somente os pontos que se enquadram nos mesmos três compartimentos precisam ser considerados como possíveis correspondências join para esse intervalo. Por exemplo, se p for 32, ela pode ser descartada como estando entre start de 8 e end de 22, porque ela cai na lixeira de 30 a 40.

nota
  • Para valores DATE, o valor do tamanho do compartimento é interpretado como dias. Por exemplo, um valor de tamanho de compartimento de 7 representa uma semana.
  • Para valores TIMESTAMP, o valor do tamanho do compartimento é interpretado como segundos. Se for necessário um valor inferior a um segundo, valores fracionários podem ser usados. Por exemplo, um valor de tamanho de compartimento de 60 representa um minuto e um valor de tamanho de compartimento de 0,1 representa 100 milissegundos.

É possível especificar o tamanho do bin usando uma dica de join de intervalo na consulta ou definindo um parâmetro de configuração de sessão. No Databricks SQL, o tamanho do bin é derivado automaticamente quando a otimização automática de join de intervalo estiver ativada.

Otimização automática da join de intervalos​

No Databricks SQL, o Databricks detecta automaticamente joins de intervalo qualificados e deriva o tamanho ideal do bin amostrando a tabela de intervalos. Elimina-se a necessidade de especificar manualmente um tamanho de compartimento através de dicas ou configuração de sessão.

A otimização automática de join de intervalos está ativada por default no Databricks SQL. Para desativá-lo, defina a seguinte configuração:

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

Se você especificar um tamanho de bin por meio de uma dica de join de intervalo ou configuração de sessão, esse valor sobrescreve o tamanho de bin derivado automaticamente.

Habilite o intervalo join usando uma dica de intervalo join​

Para habilitar a otimização join por intervalo em uma consulta SQL , use uma dica join intervalo para especificar o tamanho do intervalo. A dica deve conter o nome da relação de uma das relações unidas e o parâmetro numérico de tamanho do intervalo. O nome da relação pode ser uma tabela, uma view ou uma subconsulta.

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

No terceiro exemplo, você deve colocar a dica em c. Isso ocorre porque as junções são associativas à esquerda, portanto, a consulta é interpretada como (a JOIN b) JOIN c e a dica sobre a se aplica a join de a com b e não a join com 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()
)

O senhor também pode colocar um intervalo join hint em um dos DataFrames unidos. Nesse caso, a dica contém apenas o parâmetro numérico de tamanho do compartimento.

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

Habilitar o intervalo join usando a configuração de sessão​

Se não quiser modificar a consulta, especifique o tamanho do intervalo como um parâmetro de configuração.

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

Esse parâmetro de configuração se aplica a qualquer join com uma condição de intervalo. No entanto, um tamanho de bin diferente definido por meio de um intervalo join hint sempre substitui o definido por meio do parâmetro.

Escolha o tamanho do compartimento.​

A eficácia da otimização do intervalo join depende da escolha do tamanho adequado do compartimento.

Um tamanho pequeno de compartimento resulta em um número maior de compartimentos, o que ajuda a filtrar as possíveis correspondências. No entanto, ele se torna ineficiente se o tamanho do compartimento for significativamente menor do que os intervalos de valores encontrados e os intervalos de valores se sobrepõem a vários intervalos do compartimento. Por exemplo, com uma condição p BETWEEN start AND end, em que start é 1.000.000 e end é 1.999.999, e um tamanho de compartimento de 10, o intervalo de valores se sobrepõe a 100.000 compartimentos.

Se o comprimento do intervalo for bastante uniforme e conhecido, recomendamos que você defina o tamanho do compartimento para o tamanho típico esperado do intervalo de valores. No entanto, se a duração do intervalo for variável e distorcida, é necessário encontrar um equilíbrio para definir um tamanho de compartimento que filtre os intervalos curtos de forma eficiente, evitando que os intervalos longos se sobreponham a muitos compartimentos. Supondo uma tabela ranges, com intervalos entre as colunas start e end, você pode determinar diferentes percentis do valor do comprimento do intervalo distorcido com a seguinte consulta:

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;

Converter cada coluna para DOUBLE antes de subtrair garante que a consulta funcione independentemente de as colunas serem valores numéricos, DATE ou TIMESTAMP .

Uma configuração recomendada para o tamanho do intervalo seria o valor máximo no percentil 90, ou o valor no percentil 99 dividido por 10, ou o valor no percentil 99,9 dividido por 100, etc. A justificativa é:

  • Se o valor no 90º percentil for o tamanho do compartimento, somente 10% dos comprimentos do intervalo de valores são maiores do que o intervalo do compartimento, portanto, abrangem mais de 2 intervalos de compartimentos adjacentes.
  • Se o valor no 99º percentil for o tamanho do compartimento, somente 1% dos comprimentos do intervalo de valores abrangem mais de 11 intervalos de compartimentos adjacentes.
  • Se o valor no percentil 99,9 for o tamanho do compartimento, somente 0,1% dos comprimentos do intervalo de valores abrangem mais de 101 intervalos de compartimentos adjacentes.
  • O mesmo procedimento pode ser repetido para os valores no percentil 99,99, no percentil 99,999, etc., se necessário.

O método descrito limita a quantidade de intervalos de valores longos e assimétricos que se sobrepõem a múltiplos intervalos de classes. O valor do tamanho do intervalo obtido dessa forma é apenas um ponto de partida para o ajuste fino; os resultados reais podem depender da carga de trabalho específica.

Nesta página