Introdução ao BI Engine

O BigQuery BI Engine é um serviço rápido de análise na memória que acelera várias consultas SQL no BigQuery armazenando em cache os dados usados com mais frequência de maneira inteligente. O BI Engine pode acelerar consultas SQL de qualquer fonte, incluindo aquelas escritas por ferramentas de visualização de dados, e pode gerenciar tabelas em cache para otimização contínua. Isso permite melhorar o desempenho da consulta sem ajuste manual ou nível de dados. É possível agrupar e particionar tabelas para otimizar ainda mais o desempenho do BI Engine em tabelas grandes.

Por exemplo, se o painel mostrar apenas os dados do último trimestre, você poderá particionar as tabelas por tempo para que apenas as partições mais recentes sejam carregadas na memória. Também é possível combinar os benefícios das visualizações materializadas e do BI Engine. Isso funciona muito bem quando as visualizações materializadas são usadas para mesclar e nivelar dados para otimizar a estrutura para o BI Engine.

O BI Engine oferece as seguintes vantagens:

  • Compatibilidade com a API BigQuery:o BI Engine se integra diretamente à API BigQuery. Qualquer solução de BI ou aplicativo personalizado que funciona com a API BigQuery por mecanismos padrão, como REST ou drivers JDBC e ODBC, pode usar o BI Engine sem nenhuma alteração.
  • Ambiente de execução vetorizado:o uso do processamento vetorial em um mecanismo de execução faz uso mais eficiente da arquitetura moderna da CPU, operando em lotes de dados por vez. O BI Engine também usa codificações avançadas de dados, especificamente a codificação de tamanho de execução do dicionário, para compactar ainda mais os dados armazenados na camada na memória.
  • Integração perfeita:o BI Engine funciona com recursos e metadados do BigQuery, incluindo visualizações autorizadas, segurança no nível da coluna e mascaramento de dados.
  • Alocações de reserva:as reservas do BI Engine gerenciam separadamente a alocação de memória para cada projeto e região. O BI Engine armazena em cache apenas as partes necessárias de colunas e partições consultadas. Você pode especificar quais tabelas usam a aceleração do BI Engine com as tabelas preferenciais.

Na maioria das organizações, o BI Engine é ativado por um administrador de faturamento, que precisa reservar capacidade para a aceleração do BI Engine. Para saber mais, consulte Reservar capacidade do BI Engine.

Casos de uso do BI Engine

O BI Engine pode acelerar significativamente muitas consultas SQL, incluindo as usadas para painéis de BI. A aceleração é mais eficaz se você identifica as tabelas essenciais para suas consultas e as marca como tabelas preferenciais. Para usar o BI Engine, crie uma reserva em uma região e especifique o tamanho dela. Você pode permitir que o BigQuery determine quais tabelas serão armazenadas em cache com base nos padrões de uso do projeto ou especificar tabelas para evitar que outro tráfego interfira na aceleração.

O BI Engine é útil nos seguintes casos de uso:

  • Você usa ferramentas de BI para analisar seus dados: o BI Engine acelera as consultas do BigQuery, independentemente de serem executadas no console do BigQuery, em uma ferramenta de BI, como o Looker Studio ou o Tableau, ou em uma biblioteca de cliente, API ou um conector ODBC ou JDBC. Isso pode melhorar significativamente o desempenho dos painéis conectados ao BigQuery por uma conexão (API) integrada ou conectores.
  • Você consulta tabelas com frequência: o BI Engine permite designar tabelas preferenciais para acelerar. Isso é útil se você tiver um subconjunto de tabelas consultadas com mais frequência ou usadas para painéis de alta visibilidade.

O BI Engine pode não atender às suas necessidades nos seguintes casos:

  • Use caracteres curingas nas suas consultas: as consultas que fazem referência a tabelas curinga não são compatíveis com o BI Engine e não se beneficiam da aceleração.
  • Você precisa de recursos do BigQuery que não são compatíveis com o BI Engine: embora o BI Engine seja compatível com a maioria das funções e operadores SQL, os recursos não compatíveis com o BI Engine incluem tabelas externas, segurança no nível da linha e funções não definidas pelo usuário do SQL.

Considerações sobre o BI Engine

Considere o seguinte ao decidir como configurar o BI Engine:

Garantir a aceleração de consultas específicas

Para garantir que um conjunto de consultas seja acelerado, crie um projeto separado com uma reserva dedicada do BI Engine. Primeiro, estimule a capacidade de computação necessária para suas consultas e, em seguida, designe essas tabelas como tabela preferencial para o BI Engine.

Minimizar as mesclagens

O BI Engine funciona melhor com dados pré-agregados ou pré-mesclados e com consultas com um pequeno número de mesclagens. Isso ocorre especialmente quando um lado da junção é grande e os outros são muito menores, como ao consultar uma grande tabela de fatos combinada com tabelas de dimensões menores. É possível combinar o BI Engine com visualizações materializadas, que executam mesclagens para produzir uma única tabela grande e plana. Dessa forma, as mesmas junções não são realizadas para cada consulta. As visualizações materializadas desatualizadas são recomendadas para um desempenho ideal da consulta.

Entender o impacto do BI Engine

Para entender seu uso do BI Engine, consulte Monitorar o BI Engine com o Cloud Monitoring ou consulte as visualizações INFORMATION_SCHEMA.BI_CAPACITIES e INFORMATION_SCHEMA.BI_CAPACITY_CHANGES. Desative a opção Usar resultados em cache no BigQuery para uma comparação mais precisa. Para mais informações, consulte Usar resultados de consulta em cache.

Tabelas preferidas

Com as tabelas preferenciais do BI Engine, você limita a aceleração do BI Engine para um conjunto especificado de tabelas. As consultas em todas as outras tabelas usam slots normais do BigQuery. Por exemplo, com as tabelas preferenciais, é possível acelerar apenas as tabelas e os painéis identificados como importantes para sua empresa.

Se não houver RAM suficiente no projeto para manter todas as tabelas preferenciais, o BI Engine descarregará partições e colunas que não foram acessadas recentemente. Esse processo libera memória para novas consultas que precisam de aceleração.

Limitações das tabelas preferidas

As tabelas preferenciais do BI Engine têm as seguintes limitações:

  • Não é possível adicionar visualizações à lista de reservas de tabelas preferenciais. As tabelas preferenciais do BI Engine são compatíveis apenas com tabelas.
  • As consultas para visualizações materializadas só serão aceleradas se as visualizações materializadas e as tabelas base estiverem na lista de tabelas preferenciais.
  • Não é possível especificar partições ou colunas para aceleração.
  • As colunas do tipo JSON não são compatíveis e não são aceleradas pelo BI Engine.
  • As consultas que acessam várias tabelas só serão aceleradas se todas as tabelas forem tabelas preferidas. Por exemplo, todas as tabelas em uma consulta com JOIN precisam estar na lista de tabelas preferenciais para que sejam aceleradas. Mesmo que uma tabela não esteja na lista de preferenciais, a consulta não poderá usar o BI Engine.
  • Os conjuntos de dados públicos não são compatíveis com o console do Google Cloud. Para adicionar uma tabela pública como preferencial, use a API ou a DDL.

Limitações

O BigQuery BI Engine tem as seguintes limitações.

Mesclagens

O BI Engine acelera alguns tipos de consultas de mesclagem. A aceleração acontece em subconsultas de nível folha com INNER e LEFT OUTER JOINS, em que uma grande tabela de fatos é mesclada com até quatro tabelas de dimensão menores. As tabelas de dimensão pequena têm as seguintes restrições:

  • Menos de 5 milhões de linhas
  • Limite de tamanho:
    • Tabelas não particionadas: 5 GiB ou menos
    • Tabelas particionadas: partições referenciadas de 1 GB ou menos

Funções de janela

Funções de janela, também conhecidas como funções analíticas, têm as seguintes limitações quando aceleradas pelo BigQuery BI Engine:

  • As etapas de entrada são aceleradas pelo BigQuery BI Engine se não tiverem funções de janela. Neste caso, INFORMATION_SCHEMA.JOBS acessa relatórios bi_engine_statistics.acceleration_mode como FULL_INPUT.
  • Os estágios de entrada das consultas com funções de janela nos estágios de entrada são acelerados pelo BI Engine, mas não têm as limitações descritas em Limitações das funções de janela do BI Engine. Nesse caso, as etapas de entrada ou a consulta completa são executadas no BI Engine. Neste caso, INFORMATION_SCHEMA.JOBS acessa relatórios bi_engine_statistics.acceleration_mode como FULL_INPUT ou FULL_QUERY.

Para mais informações sobre o campo BiEngineStatistics, consulte a referência do job.

Limitações das funções de janela do BI Engine

As consultas com funções de janela só são executadas no BI Engine se todas as seguintes condições forem verdadeiras:

  • A consulta verifica exatamente uma tabela.
    • A tabela não é particionada.
    • A tabela tem menos de 5 milhões de linhas.
  • A consulta não tem operadores JOIN.
  • O tamanho da tabela verificada vezes o número de operadores de função de janela não excede 300 MiB.

Duas funções de janela com cláusulas OVER idênticas e as mesmas entradas diretas podem compartilhar o mesmo operador de função de janela. Exemplo:

  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table tem apenas um operador de função de janela.
  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table tem dois operadores função de janela, porque as duas funções têm cláusulas OVER diferentes.
  • SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) tem dois operadores de função de janela, porque as duas funções têm entradas diretas diferentes, mesmo que as cláusulas OVER sejam as mesmas.

Funções de janela com suporte

As seguintes funções de janela referenciadas são compatíveis:

  • ANY_VALUE
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • ROW_NUMBER
  • ST_CLUSTERDBSCAN
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

Se funções de janela não forem compatíveis, você poderá receber o seguinte erro:

A função de análise é incompatível com outros operadores ou as entradas são muito grandes

Outras limitações do BI Engine

A aceleração do BI Engine não está disponível para os seguintes recursos:

  • UDFs de JavaScript
  • Tabelas externas
  • Consulta de dados JSON: mensagem de erro: o tipo nativo JSON não é compatível
  • Como gravar resultados em uma tabela permanente do BigQuery
  • Tabelas que contêm upserts que usam a captura de dados de alterações do BigQuery
  • Transações
  • Consultas que retornam mais de 1 GiB de dados. Para aplicativos sensíveis à latência, recomendamos um tamanho de resposta menor que 1 MiB.
  • Segurança no nível da linha

Solução alternativa para recursos incompatíveis

Embora alguns recursos SQL não sejam compatíveis com o BigQuery BI Engine, há uma solução alternativa disponível:

  1. Crie uma consulta no BigQuery.
  2. Salve os resultados da consulta em uma tabela.
  3. Programe sua consulta para atualizar a tabela regularmente. Uma taxa de atualização horária ou diária funciona melhor. A atualização a cada minuto pode invalidar o cache com muita frequência.
  4. Faça referência a essa tabela nas suas consultas de desempenho crítico.

Cotas e limites

Consulte Cotas e limites do BigQuery para mais informações sobre cotas e limites que se aplicam ao BI Engine.

Preços

Você incorre em custos para a reserva que cria para a capacidade do BI Engine. Para ver informações sobre preços do BI Engine, consulte a página Preços do BigQuery.

A seguir