Práticas recomendadas para funções

Este documento descreve como otimizar consultas que usam funções SQL.

Otimize a comparação de strings

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

No BigQuery, pode usar a função REGEXP_CONTAINS ou o operador LIKE para comparar strings. REGEXP_CONTAINS oferece mais funcionalidades, mas também tem um tempo de execução mais lento. A utilização de LIKE em vez de REGEXP_CONTAINS é mais rápida, especialmente se não precisar de toda a capacidade das expressões regulares que REGEXP_CONTAINS oferece, por exemplo, a correspondência de carateres universais.

Considere a seguinte utilização da função REGEXP_CONTAINS:

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

Pode otimizar esta consulta da seguinte forma:

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

Otimize as funções de agregação

Prática recomendada: se o seu exemplo de utilização o permitir, use uma função de agregação aproximada.

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

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 a seguinte utilização da função COUNT:

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

Pode otimizar esta consulta da seguinte forma:

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

Otimize as funções de quantil

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

A execução de uma consulta que contenha a função NTILE pode falhar com um erro Resources exceeded se existirem demasiados elementos a ORDER BY numa única partição, o que faz com que o volume de dados aumente. A janela analítica não está dividida, pelo que o cálculo NTILE requer um ORDER BY global para todas as linhas na tabela a serem processadas por um único trabalhador/ranhura.

Experimente usar APPROX_QUANTILES em alternativa. Esta função permite que a consulta seja executada de forma mais eficiente porque não requer um ORDER BY global para todas as linhas na tabela.

Considere a seguinte utilização da função NTILE:

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

Pode otimizar esta consulta da seguinte forma:

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 dá resultados semelhantes, mas não idênticos, à consulta original, porque APPROX_QUANTILES:

1. Fornece uma agregação aproximada.
2. Coloca os valores restantes (o resto do número de linhas dividido por grupos) de forma diferente.

Otimize as FDU

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

Chamar uma FDU JavaScript requer a instanciação de um subprocesso. A ativação deste processo e a execução da FDU afetam diretamente o desempenho das consultas. Se possível, use uma UDF nativa (SQL) em alternativa.

FDUs persistentes

É melhor criar funções SQL e JavaScript definidas pelo utilizador persistentes num conjunto de dados do BigQuery centralizado que pode ser invocado em consultas e em vistas lógicas, em vez de criar e chamar uma FDU no código sempre que necessário. A criação de bibliotecas de lógica empresarial ao nível da organização em conjuntos de dados partilhados ajuda a otimizar o desempenho e a usar menos recursos.

O exemplo seguinte mostra como uma FDU temporária é invocada numa 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;

Pode otimizar esta consulta substituindo a FDU temporária por uma FDU persistente:

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;