Otimização de associações de intervalos

Uma junção de intervalo une duas tabelas numa condição que verifica se um valor se enquadra num intervalo, ou se dois intervalos se sobrepõem, em vez de verificar se dois valores são iguais. Por exemplo, uma consulta que associa cada evento do site à sessão do utilizador em que ocorreu verifica se o carimbo temporal do evento se situa entre o início e o fim da sessão. Uma consulta que encontra reuniões agendadas ao mesmo tempo verifica se dois intervalos de tempo se sobrepõem. A otimização da junção por intervalo no Databricks Runtime pode melhorar significativamente o desempenho destas consultas.

Por causa disso, uma junção pode comparar muitos pares de linhas que não podem corresponder. A otimização de junção de intervalo divide o intervalo de valores em bins e compara apenas as linhas que se encontram em bins sobrepostos. Ver Tamanho do bin.

No Databricks SQL, o Azure Databricks otimiza automaticamente as uniões de intervalo sem qualquer configuração manual. Também pode ajustar manualmente as junções de intervalo utilizando sugestões de junção ou a configuração da sessão para todos os tipos de computação.

Ponto na junção de intervalo

Um ponto na junção de intervalos é um join cuja condição contém predicados que especificam que um valor de uma relação fica entre dois valores da outra relação. Por exemplo:

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

Junção de intervalos sobrepostos

Uma junção de sobreposição de intervalos é uma junção na qual a condição contém predicados que especificam uma sobreposição de intervalos entre dois valores de cada relação. Por exemplo:

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

Otimização de associações de intervalos

A otimização da junção por intervalo aplica-se a junções que:

  • Ter um ponto no intervalo ou uma condição de junção por sobreposição de intervalos.
  • Utilize apenas números (integral, vírgula flutuante ou decimal), DATE ou valores TIMESTAMP na condição de junção de intervalo.
  • Utilize valores do mesmo tipo na condição de junção de intervalos. Os valores decimais também devem ter a mesma escala e precisão.
  • Utilize um tipo de junção suportado:
    • INNER JOIN, para qualquer junção de intervalo
    • LEFT OUTER JOIN com o valor de pontos no lado esquerdo, ou RIGHT OUTER JOIN com o valor de pontos no lado direito, apenas para junções por intervalo de pontos
  • Ter um tamanho do intervalo, seja derivado automaticamente ou especificado manualmente.

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

Quando uma condição de junção inclui tanto uma condição de igualdade numa coluna numérica como uma condição de intervalo, o otimizador pode aplicar binning à coluna de igualdade numérica porque esta cumpre os requisitos de tipo para otimização de junção de intervalo. Isto pode fazer com que a coluna de igualdade seja atribuída a bins ou excluída da otimização, reduzindo o desempenho.

Para garantir que a otimização da junção de intervalo se aplica apenas à condição de intervalo pretendida, conjure as colunas de igualdade numérica para STRING. Isto exclui-as da consideração como colunas de condição de alcance.

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 aplica-se a outras colunas numéricas usadas como chaves de igualdade, como DATEidentificadores de inteiros ou colunas de partição agrupadas.

Tamanho do intervalo

O tamanho do compartimento é um parâmetro de ajuste numérico que divide o domínio de valores da condição de intervalo em vários compartimentos de tamanho igual. Por exemplo, com um tamanho de compartimento de 10, a otimização divide o domínio em compartimentos que são intervalos de comprimento 10. Se tiver uma condição de ponto no intervalo de p BETWEEN start AND end, e start for 8 e end for 22, esse intervalo de valores sobrepõe-se a três binários de comprimento 10 – o primeiro binário de 0 a 10, o segundo de 10 a 20, e o terceiro de 20 a 30. Apenas os pontos que se enquadram nos mesmos três compartimentos precisam ser considerados como possíveis partidas de junção para esse intervalo. Por exemplo, se p for 32, pode ser descartado como estando no intervalo entre start de 8 e end de 22, porque está no intervalo de 30 a 40.

Nota

  • Para os valores de DATE, o tamanho do compartimento é interpretado como dias. Por exemplo, um valor de tamanho de compartimento de 7 representa uma semana.
  • Para TIMESTAMP valores, o valor do tamanho do compartimento é interpretado como segundos. Para um valor inferior a um 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.

Pode especificar o tamanho do bin usando uma dica de junta de intervalo na consulta ou definindo um parâmetro de configuração de sessão. No Databricks SQL, o tamanho do bin é automaticamente obtido quando a otimização automática da junção por intervalo está ativada.

Otimização automática da junção de alcances

No Databricks SQL, o Azure Databricks deteta automaticamente as uniões de intervalo qualificadas e deriva o tamanho ótimo do bin amostrando a tabela de intervalos. Isto elimina a necessidade de especificar manualmente o tamanho do bin através de dicas ou configuração da sessão.

A otimização automática de range join está ativada por defeito no Databricks SQL. Para o desativar, defina esta configuração:

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

Se especificar o tamanho do bin através de uma sugestão de junção por intervalo ou da configuração da sessão, esse valor substitui o tamanho do bin derivado automaticamente.

Ativar junção de intervalo usando uma sugestão de junção de intervalo

Para ativar a otimização da junção por intervalo numa consulta SQL, use uma sugestão de junção por 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 bin size. O nome da relação é uma tabela, uma vista ou uma 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

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

Você também pode colocar uma dica de junção por intervalo em um dos DataFrames associados. Nesse caso, a dica contém apenas o parâmetro numérico do tamanho do compartimento.

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 a junção de intervalos usando a configuração da sessão

Se não quiseres modificar a consulta, especifica o tamanho do bin como um parâmetro de configuração.

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

Este parâmetro de configuração aplica-se a qualquer junção com uma condição de intervalo. No entanto, um tamanho de compartimento diferente configurado através de uma indicação de junção de intervalo sempre sobrepõe-se ao configurado através do parâmetro.

Escolha o tamanho da caixa

A eficácia da otimização da junção de gama depende da escolha do tamanho adequado do caixote.

Um tamanho pequeno do compartimento resulta em um número maior de compartimentos, o que ajuda a filtrar as possíveis correspondências. Um tamanho pequeno do compartimento torna-se ineficiente se os intervalos de valor 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 em que start é 1.000.000 e end é 1.999.999, e um tamanho de compartimento de 10, o intervalo sobrepõe-se a 100.000 compartimentos.

Se o comprimento do intervalo for relativamente uniforme e conhecido, defina o tamanho do intervalo para o comprimento típico do intervalo. Para intervalos variáveis e assimétricos, escolha um tamanho de bin que filtre intervalos curtos de forma eficiente, evitando que intervalos longos se sobreponham a demasiados bins. Para uma tabela ranges com intervalos entre colunas start e end, determine percentis do comprimento do intervalo assimétrico com 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;

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

Defina o tamanho da caixa para o maior destes valores:

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

Para limitar intervalos ainda mais longos, continue o padrão com os percentis 99,99.º e 99,999.º. Usa o resultado como ponto de partida e depois ajusta o tamanho do bin para a tua carga de trabalho.

  • Última atualização a