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

Alcance join otimização

Uma joinpor intervalo ocorre quando duas relações join usando um ponto em um intervalo ou uma condição de sobreposição de intervalos. O suporte à otimização join intervalo no Databricks Runtime pode trazer melhorias significativas no desempenho das consultas, mas requer ajustes manuais cuidadosos.

Databricks recomenda o uso das dicas do site join para a união de intervalos quando o desempenho for baixo.

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.
  • Tenha um parâmetro de ajuste do tamanho do compartimento.

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.

O senhor pode especificar o tamanho do compartimento usando um intervalo join hint na consulta ou definindo um parâmetro de configuração de sessão. A otimização do intervalo join é aplicada somente se o senhor especificar manualmente o tamanho do compartimento. A seção Escolha o tamanho do compartimento descreve como escolher um tamanho de compartimento ideal.

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