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

Alcance join otimização

Um range join junta duas tabelas em uma condição que verifica se um valor está dentro de um intervalo, ou se dois intervalos se sobrepõem, em vez de verificar se dois valores são iguais. Por exemplo, uma query que corresponde a cada evento do site à sessão do usuário em que ocorreu verifica se o Timestamp do evento está entre os horários de começar e fim da sessão. Uma query que encontra reuniões agendadas ao mesmo tempo verifica se dois intervalos de tempo se sobrepõem. A otimização de join de intervalos no Databricks Runtime pode melhorar muito o desempenho dessas queries.

Por causa disso, um join pode comparar muitos pares de linhas que não podem coincidir. A otimização do join de intervalos divide o intervalo de valores em bins e compara apenas as linhas que caem em bins sobrepostos. Veja Tamanho do compartimento.

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 da join de intervalos se aplica a joins que:

  • Ter uma condição de join de intervalo de ponto em intervalo ou sobreposição de intervalo.

  • Use apenas valores numéricos (inteiros, ponto flutuante ou decimais), DATE ou TIMESTAMP na condição de join de intervalo.

  • Use valores do mesmo tipo na condição de join de intervalo. Valores decimais também devem ter a mesma escala e precisão.

  • Use um tipo de join compatível:

    • INNER JOIN, para qualquer intervalo de join
    • LEFT OUTER JOIN com o valor do ponto no lado esquerdo, ou RIGHT OUTER JOIN com o valor do ponto no lado direito, apenas para joins de intervalo de pontos

  • Ter um tamanho de compartimento derivado automaticamente 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. Para um valor de sub-segundo, use um valor fracionário. 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 da join de intervalos é habilitada por default no Databricks SQL. Para desabilitá-la, defina essa 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 de join de intervalos em uma query SQL, use uma dica de join de intervalos para especificar o tamanho do compartimento. A dica deve conter o nome da relação de uma das relações unidas e o parâmetro de tamanho do compartimento numérico. O nome da relação é 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 de compartimento pequeno resulta em um maior número de compartimentos, o que ajuda a filtrar as possíveis correspondências. Um tamanho de compartimento pequeno torna-se ineficiente se os intervalos de valores forem muito maiores do que o tamanho do compartimento, fazendo com que os intervalos se sobreponham a muitos compartimentos. Por exemplo, com uma condição p BETWEEN start AND end onde start é 1.000.000 e end é 1.999.999, e um tamanho de compartimento de 10, o intervalo se sobrepõe a 100.000 compartimentos.

Se o comprimento do intervalo for relativamente uniforme e conhecido, defina o tamanho do bin como o comprimento típico do intervalo. Para intervalos variáveis e distorcidos, escolha um tamanho de bin que filtre intervalos curtos com eficiência, evitando que intervalos longos se sobreponham a muitos bins. Para uma tabela ranges com intervalos entre as colunas start e end, determine os percentis do comprimento do intervalo distorcido com esta query:

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 .

Defina o tamanho do compartimento como o maior desses valores:

  • O 90º percentil dos comprimentos de intervalo. Nesse tamanho, no máximo 10% dos intervalos abrangem mais de 2 compartimentos.
  • O 99º percentil dividido por 10. Neste tamanho, no máximo 1% dos intervalos abrangem mais de 11 compartimentos.
  • O 99,9º percentil dividido por 100. Neste tamanho, no máximo 0,1% dos intervalos abrangem mais de 101 compartimentos.

Para limitar intervalos ainda maiores, continue o padrão com os percentis de 99,99 e 99,999. Use o resultado como um ponto de partida e, em seguida, ajuste o tamanho da bin para sua carga de trabalho.

Nesta página