Introdução ao BI Engine

O BigQuery BI Engine é um serviço de análise na memória rápido que acelera muitas consultas SQL no BigQuery ao colocar em cache de forma inteligente os dados que usa com maior frequência. O BI Engine pode acelerar as consultas SQL de qualquer origem, incluindo as escritas por ferramentas de visualização de dados, e pode gerir tabelas em cache para otimização contínua. Isto permite-lhe melhorar o desempenho das consultas sem ajuste manual nem hierarquização de dados. Pode agrupar e criar partições nas tabelas para otimizar ainda mais o desempenho do BI Engine para tabelas grandes.

Por exemplo, se o seu painel de controlo apresentar apenas os dados do último trimestre, pode particionar as tabelas por tempo para que apenas as partições mais recentes sejam carregadas na memória. Também pode combinar as vantagens das vistas materializadas e do BI Engine. Isto funciona particularmente bem quando as vistas materializadas são usadas para juntar e simplificar dados para otimizar a respetiva estrutura para o BI Engine.

O BI Engine oferece as seguintes vantagens:

  • Compatibilidade com a API BigQuery: o BI Engine integra-se diretamente com a API BigQuery. Qualquer solução de BI ou aplicação personalizada que funcione com a API BigQuery através de mecanismos padrão, como REST ou controladores JDBC e ODBC, pode usar o BI Engine sem modificações.
  • Tempo de execução vetorizado: a utilização do processamento vetorizado num motor de execução permite uma utilização mais eficiente da arquitetura moderna da CPU, operando em lotes de dados de cada vez. O BI Engine também usa codificações de dados avançadas, especificamente a codificação de comprimento de execução do dicionário, para comprimir ainda mais os dados armazenados na camada na memória.
  • Integração perfeita: o BI Engine funciona com as funcionalidades e os metadados do BigQuery, incluindo vistas autorizadas, segurança ao nível da coluna e ocultação de dados.
  • Atribuições de reservas: as reservas do BI Engine gerem separadamente a atribuição de memória para cada projeto e região. O BI Engine apenas armazena em cache as partes consultadas e necessárias de colunas e partições. Pode especificar que tabelas usam a aceleração do BI Engine com tabelas preferenciais.

Na maioria das organizações, o BI Engine é ativado por um administrador de faturação que tem de reservar capacidade para a aceleração do BI Engine com uma edição adequada. Para saber mais, consulte o artigo Reserve capacidade do BI Engine.

Exemplos de utilização do BI Engine

O BI Engine pode acelerar significativamente muitas consultas SQL, incluindo as usadas para painéis de controlo de BI. A aceleração é mais eficaz se identificar as tabelas essenciais para as suas consultas e, em seguida, marcá-las como tabelas preferenciais. Para usar o BI Engine, cria uma reserva numa região e especifica o respetivo tamanho. Pode permitir que o BigQuery determine que tabelas colocar em cache com base nos padrões de utilização do projeto ou pode especificar tabelas para impedir que outro tráfego interfira na respetiva aceleração.

O BI Engine é útil nos seguintes exemplos de utilização:

  • Usa ferramentas de BI para analisar os seus dados: o BI Engine acelera as consultas do BigQuery, quer sejam executadas na consola do BigQuery, numa ferramenta de BI, como o Looker Studio ou o Tableau, ou numa biblioteca de cliente, numa API ou num conetor ODBC ou JDBC. Isto pode melhorar significativamente o desempenho dos painéis de controlo associados ao BigQuery através de uma associação incorporada (API) ou de conetores.
  • Consultou tabelas com frequência: o BI Engine permite-lhe designar tabelas preferenciais para acelerar. Isto é útil se tiver um subconjunto de tabelas consultadas com maior frequência ou usadas para painéis de controlo de alta visibilidade.

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

  • Usa carateres universais nas suas consultas: as consultas que fazem referência a tabelas de carateres universais não são suportadas pelo BI Engine e não beneficiam da aceleração.
  • Precisa de funcionalidades do BigQuery não suportadas pelo BI Engine: embora o BI Engine suporte a maioria das funções e dos operadores SQL, as funcionalidades não suportadas pelo BI Engine incluem tabelas externas, segurança ao nível da linha e funções definidas pelo utilizador não SQL.

Considerações para 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 é acelerado, crie um projeto separado com uma reserva do BI Engine dedicada. Primeiro, estime a capacidade de computação necessária para as suas consultas e, em seguida, designe essas tabelas como tabelas preferenciais para o BI Engine.

Minimize as suas associações

O BI Engine funciona melhor para dados pré-associados ou pré-agregados e para consultas com um pequeno número de associações. Isto é particularmente verdadeiro quando um dos lados da junção é grande e os outros são muito mais pequenos, como quando consulta uma tabela de factos grande unida a tabelas de dimensões mais pequenas. Pode combinar o BI Engine com vistas materializadas, que realizam junções para produzir uma única tabela grande e simples. Desta forma, as mesmas junções não são realizadas para cada consulta. As vistas materializadas desatualizadas são recomendadas para um desempenho ideal das consultas.

Compreenda o impacto do BI Engine

Para compreender a sua utilização do BI Engine, consulte o artigo Monitorize o BI Engine com o Cloud Monitoring ou consulte as vistas INFORMATION_SCHEMA.BI_CAPACITIES e INFORMATION_SCHEMA.BI_CAPACITY_CHANGES. Certifique-se de que desativa a opção Usar resultados em cache no BigQuery para obter a comparação mais precisa. Para mais informações, consulte o artigo Use resultados de consultas em cache.

Tabelas preferenciais

As tabelas preferidas do BI Engine permitem-lhe limitar a aceleração do BI Engine a um conjunto especificado de tabelas. As consultas a todas as outras tabelas usam slots do BigQuery normais. Por exemplo, com as tabelas preferenciais, pode acelerar apenas as tabelas e os painéis de controlo que identificar como importantes para a sua empresa.

Se não houver RAM suficiente no projeto para conter todas as tabelas preferenciais, o BI Engine transfere partições e colunas que não foram acedidas recentemente. Este processo liberta memória para novas consultas que precisam de aceleração.

Limitações das tabelas preferenciais

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

  • Não pode adicionar vistas à lista de reservas de tabelas preferidas. As tabelas preferenciais do BI Engine só suportam tabelas.
  • As consultas a vistas materializadas só são aceleradas se as vistas materializadas e as respetivas tabelas base estiverem na lista de tabelas preferenciais.
  • A especificação de partições ou colunas para aceleração não é suportada.
  • As colunas do tipo JSON não são suportadas e não são aceleradas pelo BI Engine.
  • As consultas que acedem a várias tabelas só são aceleradas se todas as tabelas forem tabelas preferenciais. Por exemplo, todas as tabelas numa consulta com um JOIN têm de estar na lista de tabelas preferenciais para serem aceleradas. Se mesmo uma tabela não estiver na lista preferencial, a consulta não pode usar o BI Engine.
  • Os conjuntos de dados públicos não são suportados na consola do Google Cloud . Para adicionar uma tabela pública como tabela preferencial, use a API ou o LDD.

Limitações

Para usar o BI Engine, a sua organização tem de comprar capacidade do BI Engine criando uma reserva do BI Engine com uma edição suportada. Para mais informações, consulte o artigo Compreender as edições do BigQuery.

Além disso, o BigQuery BI Engine tem as seguintes limitações.

Aderir

O BI Engine acelera determinados tipos de consultas de junção. A aceleração ocorre em subconsultas ao nível da folha com INNER e LEFT OUTER JOINS, em que uma tabela de factos grande é unida a até quatro tabelas de "dimensões" mais pequenas. As tabelas de dimensões pequenas 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

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

  • As fases de entrada são aceleradas pelo BigQuery BI Engine se não tiverem funções de janela. Neste caso, INFORMATION_SCHEMA.JOBS veja os relatórios bi_engine_statistics.acceleration_mode como FULL_INPUT.
  • As fases de entrada de consultas com funções de janela nas respetivas fases de entrada são aceleradas 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 fases de entrada ou a consulta completa são executadas no BI Engine. Neste caso, INFORMATION_SCHEMA.JOBS veja os relatórios bi_engine_statistics.acceleration_mode como FULL_INPUT ou FULL_QUERY.

Para mais informações sobre o campo BiEngineStatistics, consulte o artigo Referência da tarefa.

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 forem cumpridas todas as condições seguintes:

  • A consulta analisa 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 analisada multiplicado pelo número de operadores de funções de janela não excede 300 MiB.

Duas funções de janela com cláusulas OVER idênticas e as mesmas entradas diretas podem partilhar o mesmo operador de função de janela. Por 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ções de janela porque as duas funções têm entradas diretas diferentes, embora as respetivas cláusulas OVER pareçam iguais.

Funções de janela suportadas

As seguintes funções de janela referenciadas são suportadas:

  • 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 suportadas, pode ver o seguinte erro:

A função analítica é incompatível com outros operadores ou as respetivas entradas são demasiado grandes

Outras limitações do BI Engine

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

  • UDFs JavaScript
  • Tabelas externas, incluindo tabelas do BigLake
  • Consultar dados JSON – Mensagem de erro: O tipo nativo JSON não é suportado
  • Escrever resultados numa tabela permanente do BigQuery
  • Tabelas que contêm inserções/atualizações que usam a captura de dados de alterações do BigQuery
  • Transações
  • Consultas que devolvem mais de 1 GiB de dados. Para aplicações sensíveis à latência, recomenda-se um tamanho de resposta inferior a 1 MiB.
  • Segurança ao nível da linha
  • Consultas que usam a função SEARCH ou são otimizadas por índices de pesquisa

Solução alternativa para funcionalidades não suportadas

Embora algumas funcionalidades de SQL não sejam suportadas no BI Engine do BigQuery, existe uma solução alternativa disponível:

  1. Escreva uma consulta no BigQuery.
  2. Guarde os resultados da consulta numa tabela.
  3. Agende a sua consulta para atualizar a tabela regularmente. Uma taxa de atualização de hora em hora ou diária funciona melhor. A atualização a cada minuto pode invalidar a cache com demasiada frequência.
  4. Faça referência a esta tabela nas suas consultas críticas para o desempenho.

Quotas e limites

Consulte as quotas e os limites do BigQuery para ver as quotas e os limites aplicáveis ao BI Engine.

Preços

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

O que se segue?