Alcance join otimização
Um intervalo join ocorre quando duas relações são unidas usando uma condição de ponto no intervalo ou de sobreposição de intervalo. O suporte à otimização do intervalo join em Databricks Runtime pode melhorar o desempenho da consulta em ordens de magnitude, mas requer um ajuste manual cuidadoso.
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
Um ponto no intervalo de intervalo join é um join no qual a condição contém predicados que especificam que um valor de uma relação está 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;
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:
-- 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
ouTIMESTAMP
. - Todos os valores envolvidos na condição de intervalo join são do mesmo tipo. No caso do tipo decimal, os valores também precisam ter a mesma escala e precisão.
- É um
INNER JOIN
ou, no caso de um ponto no intervalo join, umLEFT OUTER JOIN
com o valor do ponto no lado esquerdo ouRIGHT OUTER JOIN
com o valor do ponto no lado direito. - Tenha um parâmetro de ajuste do tamanho do compartimento.
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.
- 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 ativar a otimização do intervalo join em uma consulta SQL, o senhor pode usar uma dica de intervalo join 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 numérico de tamanho do compartimento. O nome da relação pode ser uma tabela, um view 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)
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
.
#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.
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, você pode especificar o tamanho do compartimento como um parâmetro de configuração.
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:
SELECT APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges
Uma configuração recomendada do tamanho do compartimento seria o máximo do valor no percentil 90, ou o valor no 99º percentil dividido por 10, ou o valor no percentil 99,9 dividido por 100 e assim por diante. 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 pode ser repetido para os valores no percentil 99,99, 99,999 e assim por diante, se necessário.
O método descrito limita a quantidade de intervalos de valores longos distorcidos que se sobrepõem a vários intervalos de compartimento. O valor do tamanho do compartimento obtido dessa forma é apenas um ponto de partida para o ajuste fino; os resultados reais podem depender da carga de trabalho específica.