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. Esse armazenamento em cache permite melhorar o desempenho da consulta sem ajuste manual ou nivelamento de dados. É possível agrupar e particionar tabelas para otimizar ainda mais a performance do BI Engine em tabelas grandes.

Por exemplo, se o painel mostrar apenas dados do último trimestre, você poderá particionar as tabelas por tempo para que apenas as partições mais recentes sejam carregadas na memória. Em seguida, use visualizações materializadas para mesclar e nivelar os dados e marque a visualização e a tabela base resultantes como preferenciais para garantir que a aceleração do BI Engine seja aplicada apenas aos dados necessários.

O BI Engine oferece as seguintes vantagens:

  • Compatibilidade com a API BigQuery: o BI Engine se integra diretamente com a API BigQuery. Qualquer solução de BI ou aplicativo personalizado que funcione com a API BigQuery usando mecanismos padrão, como a API REST ou drivers JDBC e ODBC, pode usar o BI Engine sem modificação.
  • Tempo de execução vetorizado: o uso do processamento vetorizado em um mecanismo de execução faz uso mais eficiente da arquitetura de CPU moderna, 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 trabalha com recursos e metadados do BigQuery, incluindo visualizações autorizadas, segurança de nível de 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 consultadas de colunas e partições. É possível especificar quais tabelas usam a aceleração do BI Engine com tabelas preferenciais.

Na maioria das organizações, o BI Engine é ativado por um administrador de faturamento que reserva capacidade para a aceleração do BI Engine com uma edição adequada. Para mais informações, consulte Reservar capacidade do BI Engine.

Arquitetura do BI Engine

O BI Engine se integra a ferramentas de BI, como Looker, Data Studio, Tableau e Power BI, além de aplicativos personalizados pela API BigQuery para acelerar a exploração e a análise de dados:

Componentes da arquitetura 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 designa como 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 delas.

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

  • Você usa ferramentas de BI para analisar os dados: o BI Engine acelera as consultas do BigQuery, sejam elas executadas no console do BigQuery, em uma ferramenta de BI, como Data Studio ou Tableau, em uma biblioteca de cliente, em uma API ou em 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ê tem tabelas consultadas 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:

  • Você usa caracteres curinga 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 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, estime a capacidade de computação necessária para suas consultas, em seguida, designe essas tabelas como tabelas preferenciais para o BI Engine.

Minimizar mesclagens

O BI Engine funciona melhor para dados pré-mesclados ou pré-agregados e para consultas com um pequeno número de mesclagens. Esse comportamento é especialmente verdadeiro quando um lado da mesclagem é grande e os outros são muito menores, como ao consultar uma grande tabela de fatos mesclada 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. Essa abordagem evita a execução das mesmas mesclagens para cada consulta. As visualizações materializadas desatualizadas são recomendadas para uma performance 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 memória 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 lógicas à 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. Se uma tabela não estiver na lista preferencial, a consulta não poderá usar o BI Engine.
  • Os conjuntos de dados públicos não são compatíveis com o Google Cloud console. Para adicionar uma tabela pública como preferencial, use a API ou a DDL.

Otimização e aceleração de consultas

O BigQuery, e por extensão do BI Engine, divide um plano de consulta em várias subconsultas. Uma subconsulta consiste em operações como verificação, filtragem, computação ou agregação de dados e serve como unidade de execução.

Embora todas as consultas SQL compatíveis com o BigQuery sejam executadas corretamente com o BI Engine, ele otimiza seletivamente estágios específicos:

  • Subconsultas de nível folha: o BI Engine é mais otimizado para subconsultas de nível folha que verificam dados do armazenamento e realizam operações como filtragem, computação, agregação, classificação (ORDER BY) e mesclagens compatíveis.
  • Execução de fallback: os estágios de consulta ou subconsultas que não podem ser acelerados pelo BI Engine fazem o fallback automático para slots de execução padrão do BigQuery sem falhar na consulta.

Devido a essa otimização seletiva, business intelligence ou consultas no tipo de painel mais simples se beneficiam mais do BI Engine, já que a maior parte do tempo de execução é gasto no processamento de dados brutos em subconsultas de nível folha.

Limitações

Para usar o BI Engine, sua organização precisa reservar capacidade do BI Engine com uma edição compatível. Para mais informações, consulte Entender as edições do BigQuery.

Além disso, o BI Engine tem limitações que as seções a seguir descrevem.

Mesclagens

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

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

Funções de janela

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

  • As etapas de entrada sem funções de janela são aceleradas pelo BI Engine. Nesse caso, a visualização INFORMATION_SCHEMA.JOBS informa bi_engine_statistics.acceleration_mode como FULL_INPUT.
  • As etapas de entrada de consultas com funções de janela são aceleradas pelo BI Engine se estiverem em conformidade com as 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, e a visualização INFORMATION_SCHEMA.JOBS informa 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 da janela do BI Engine

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

  • A consulta verifica exatamente uma tabela.
    • A tabela não está particionada.
    • A tabela tem menos de 5 milhões de linhas.
  • A consulta não tem operadores JOIN.
  • O tamanho da tabela verificada multiplicado pelo 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 de 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 , embora as cláusulas OVER pareçam as mesmas.

Funções de janela compatíveis

Há suporte para as seguintes funções de janela:

  • 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 as funções de janela não forem compatíveis, você poderá receber a seguinte mensagem de erro:

Analytic function is incompatible with other operators or its inputs are too large

Limitações incompatíveis do BI Engine

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

  • UDFs JavaScript e funções remotas.
  • Tabelas externas, incluindo tabelas do BigLake.
  • Consultar dados JSON (mensagem de erro: JSON native type is not supported.).
  • Consultar dados RANGE (mensagem de erro: RANGE native type is not supported.).
  • Gravar resultados em uma tabela permanente do BigQuery.
  • Tabelas que contêm upserts que usam a ingestão de captura de dados alterados 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.
  • Consultas que usam funções de pesquisa e pesquisa de vetor (como a SEARCH função ou VECTOR_SEARCH função) ou são otimizadas por índices de pesquisa ou índices de vetor.
  • Consultas recursivas usando RECURSIVE.
  • Consultas do BigQuery ML.

Solução alternativa para recursos incompatíveis

Se a consulta usar recursos SQL incompatíveis, você poderá usar a seguinte solução alternativa:

  1. Crie uma consulta no BigQuery.
  2. Salve os resultados da consulta em uma tabela.
  3. Programe a 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

Para cotas e limites que se aplicam ao BI Engine, consulte Cotas e limites do BigQuery.

Preços

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

A seguir