Práticas recomendadas para funções

Neste documento, descrevemos como otimizar consultas que usam funções SQL.

Comparação de strings do Optimize

Prática recomendada: quando possível, use LIKE em vez de REGEXP_CONTAINS.

No BigQuery, é possível usar a função REGEXP_CONTAINS ou o operador LIKE para comparar strings. REGEXP_CONTAINS fornece mais funcionalidades, mas também tem um tempo de execução mais lento. O uso de LIKE em vez de REGEXP_CONTAINS é mais rápido, especialmente se você não precisar de todo o poder das expressões regulares fornecidas por REGEXP_CONTAINS, como a correspondência de caracteres curinga.

Considere o seguinte uso da função REGEXP_CONTAINS:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

É possível otimizar essa consulta da seguinte maneira:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

Otimizar funções de agregação

Prática recomendada: se for compatível com o caso de uso, use uma função de agregação aproximada.

Se a função de agregação SQL que você está usando tiver uma função de aproximação equivalente, a função de aproximação produzirá um desempenho de consulta mais rápido. Por exemplo, em vez de usar COUNT(DISTINCT), use APPROX_COUNT_DISTINCT. Para mais informações, consulte funções de agregação aproximadas.

Você também pode usar funções HyperLogLog++ para fazer aproximações, incluindo agregações aproximadas personalizadas. Para mais informações, consulte as funções HyperLogLog++ na referência do GoogleSQL.

Considere o seguinte uso da função COUNT:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

É possível otimizar essa consulta da seguinte maneira:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

Otimizar funções de quantil

Prática recomendada: quando possível, use APPROX_QUANTILE em vez de NTILE.

Uma consulta que contém a função NTILE pode falhar com um erro Resources exceeded se também houver muitos elementos para ORDER BY em uma única partição, o que faz com que o volume de dados aumente. A janela analítica não é particionada, portanto, a computação NTILE requer um ORDER BY global para que todas as linhas na tabela sejam processadas por um único worker/slot.

Tente usar APPROX_QUANTILES. Essa função permite que a consulta seja executada com mais eficiência, porque não requer um ORDER BY global para todas as linhas na tabela.

Considere o seguinte uso da função NTILE:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

É possível otimizar essa consulta da seguinte maneira:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

A versão otimizada oferece resultados semelhantes, mas não idênticos à consulta original, porque APPROX_QUANTILES:

1. Fornece uma agregação aproximada.
2. Posiciona os valores restantes (o restante do número de linhas divididos por buckets) de maneira diferente.

Otimizar as UDFs

Prática recomendada: use UDFs de SQL para cálculos simples, porque o otimizador de consultas pode aplicar otimizações a definições de UDF em SQL. Use UDFs JavaScript para cálculos complexos que não são compatíveis com a UDF SQL.

Chamar uma UDF JavaScript requer a instanciação de um subprocesso. Acionar esse processo e executar a UDF afeta diretamente o desempenho da consulta. Se possível, use uma UDF nativa (SQL).

UDFs permanentes

É melhor criar funções SQL e JavaScript permanentes definidas pelo usuário em um conjunto de dados centralizado do BigQuery que pode ser invocado em consultas e visualizações lógicas, em vez de criar e chamar uma UDF no código sempre. Criar bibliotecas de toda a organização da lógica de negócios em conjuntos de dados compartilhados ajuda a otimizar o desempenho e usar menos recursos.

O exemplo a seguir mostra como uma UDF temporária é invocada em uma consulta:

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

É possível otimizar essa consulta substituindo a UDF temporária por uma permanente:

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;