Quando faz sentido usar o Google BigQuery?

Ao longo da minha carreira, tive que escrever consultas analíticas complexas para diferentes tipos de relatórios e gráficos. Na maioria das vezes, era algum gráfico que exibia dados agregados por data, semana, trimestre e assim por diante. Normalmente, esses relatórios são criados para ajudar os clientes a identificar tendências e ilustrar o desempenho de seus negócios em alto nível. Mas o que acontece quando os cientistas e engenheiros de dados precisam criar um relatório muito mais extenso, baseado em um grande conjunto de dados?

Caso o relatório seja baseado no pequeno conjunto de dados, a tarefa pode ser resolvida escrevendo uma consulta SQL em um banco de dados relacional. Nesta etapa, é importante conhecer o básico para escrever consultas e como torná-las mais rápidas e eficientes. No entanto, às vezes o relatório depende de um conjunto maior de dados (por exemplo, milhões e mais linhas em uma tabela), mas o relatório não depende de variáveis ​​de entrada (parâmetros), ou você pode descobrir que o número de valores é muito pequeno. Nesses cenários, uma consulta SQL pode ser lenta, portanto, não seria ideal que os usuários esperassem até que a consulta fosse executada. A prática mais comum nesses casos é executar uma consulta com antecedência – antes que o cliente solicite um relatório.

Além disso, requer a implementação de algumas funcionalidades de cache, para que o cliente possa obter dados do cache em vez de executar uma consulta em tempo real. Essa abordagem funciona perfeitamente, desde que você não precise mostrar dados em tempo real. Ele pode mostrar dados calculados uma hora ou até um dia antes. Portanto, o relatório/gráfico real é mostrado usando dados em cache, não com base em dados em tempo real.

Como usar o Google BigQuery

Enquanto eu estava trabalhando em um projeto analítico na indústria farmacêutica, eu precisava de gráficos que levassem o CEP e o nome do medicamento como parâmetros de entrada. Eu também precisava mostrar algumas comparações entre drogas em regiões específicas dos Estados Unidos.

A consulta analítica foi muito complexa e acabou rodando em torno de 50 minutos em nosso servidor Postgres (CPU quad-core com 16 GB de RAM). Não consegui executá-lo com antecedência e armazenar em cache os resultados, pois a consulta estava recebendo códigos postais e medicamentos como parâmetros de entrada, então havia milhares de combinações e era impossível prever qual cliente escolheria.

Mesmo se eu quisesse tentar executar todas as combinações de parâmetros de entrada, meu banco de dados provavelmente teria travado. Então era hora de escolher uma abordagem diferente e escolher uma solução fácil de usar. Esse gráfico era importante para o cliente, porém, o cliente não estava pronto para se comprometer a fazer grandes mudanças na arquitetura ou migrar totalmente para outro banco de dados.

Nesse projeto em particular, tentamos algumas abordagens diferentes:

• Dimensionamento vertical do servidor (adicionando RAM e CPU ao servidor Postgres)
• Usando bancos de dados alternativos, como Amazon Redshift e outros.
• Também pesquisamos uma solução NoSQL, mas a maioria delas é bastante complexa e requer muitas mudanças na arquitetura, muitas das quais seriam grandes demais para o cliente.

Por fim, tentamos o Google BigQuery. Atendeu às nossas expectativas e nos permitiu realizar o trabalho sem fazer grandes mudanças que o cliente relutaria em aprovar. Mas o que é o Google BigQuery e como ele funciona?

O BigQuery é um serviço da Web baseado em REST que permite executar consultas analíticas complexas baseadas em SQL em grandes conjuntos de dados. Depois que carregamos os dados para o BigQuery e executamos a mesma consulta que fizemos no Postgres (a sintaxe é estranhamente semelhante), nossa consulta estava sendo executada muito mais rápido e levou cerca de um minuto para ser concluída. Por fim, acabamos com um aumento de desempenho de 50x apenas usando um serviço diferente. Vale a pena notar que outros BDs não estavam entregando o mesmo ganho de desempenho, e vamos ser generosos e apenas dizer que eles não estavam nem perto. Para ser sincero, fiquei realmente impressionado com o ganho de desempenho fornecido pelo BigQuery, pois os números foram melhores do que qualquer um de nós esperava.

Apesar disso, eu não divulgaria o BigQuery como a melhor solução de banco de dados do mundo. Embora tenha funcionado bem para nosso projeto, ainda tem muitas limitações, como um número limitado de atualizações na tabela por dia, limitações no tamanho dos dados por solicitação e outras. Você precisa entender que o BigQuery não pode ser usado para substituir um banco de dados relacional e é orientado para a execução de consultas analíticas, não para operações e consultas CRUD simples.

Neste artigo, tentarei comparar o uso do Postgres (meu banco de dados relacional favorito) e do BigQuery para cenários de casos de uso do mundo real. Além disso, daria algumas sugestões ao longo do caminho, ou seja, minha opinião sobre quando realmente faz sentido usar o BigQuery.

Dados de amostra

Para comparar Postgres e Google BigQuery, peguei informações demográficas públicas de cada país agrupadas por país, idade, ano e sexo (você pode baixar os mesmos dados neste link).

Eu adicionei os dados a quatro tabelas:

1. populations
2. locations
3. age_groups
4. populations_aggregated

A última tabela é apenas dados agregados das três tabelas anteriores. Aqui está o esquema do banco de dados:

A tabela de populations que encontrei contém mais de 6,9 ​​milhões de linhas. Não é muito, mas foi o suficiente para o meu teste.

Com base em dados de amostra, tentei criar consultas que podem ser usadas para criar relatórios e gráficos analíticos da vida real. Então preparei consultas para os próximos relatórios:

1. População nos EUA agregada por anos.
2. População em 2019 para todos os países a partir dos maiores países.
3. As cinco nações “mais antigas” por ano. “Mais velhos” denota países onde a porcentagem de pessoas com mais de 60 anos em relação ao número total de pessoas é a mais alta. A consulta deve dar cinco resultados por cada ano.
4. As cinco principais nações agregadas por ano, onde a diferença entre a população masculina e feminina é a maior.
5. Obtenha a idade mediana (média) por país para cada ano, começando dos países “mais antigos” até os “mais jovens”.
6. Encontre os cinco principais países “morrendo” a cada ano. “Morrendo” significa países em que a população está diminuindo (o despovoamento é o mais alto).

As consultas #1, #2 e #6 são bastante fáceis e diretas, mas as consultas #3, #4 e #5 não foram tão fáceis de escrever – pelo menos para mim. Observe que sou um engenheiro de back-end e escrever consultas SQL complexas não é exatamente uma especialidade minha, portanto, alguém com mais experiência em SQL provavelmente poderia criar consultas mais inteligentes. No entanto, no momento precisamos verificar como o Postgres e o BigQuery estão processando as mesmas consultas com os mesmos dados.

Eu construí 24 consultas no total:

• 6 para Postgres DB, que está usando tabelas não agregadas ( populations , locations , age_groups )
• 6 para Postgres DB, que está usando a tabela populations_aggregated
• 6+6 consultas para BigQuery que usam tabelas agregadas e não agregadas.

Deixe-me compartilhar as consultas nº 1 e nº 5 do BigQuery para dados agregados para que você possa entender a complexidade de consultas simples (nº 1) e complexas nº 5.

População nos EUA agregada por anos de consulta:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Consulta de idade mediana por país por cada ano classificada do mais velho para o mais novo:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Nota: Você pode encontrar todas as consultas no meu repositório bitbucket (o link está no final do artigo).

Resultado dos testes

Para executar consultas, usei dois servidores Postgres diferentes. O primeiro tem 1 núcleo de CPU e 4 GB de RAM, apoiados por uma unidade SSD. O segundo tem 16 núcleos de CPU, 64 GB de RAM e também estava usando uma unidade SSD (o segundo servidor tem 16x o potencial de CPU e RAM).

Além disso, observe que não houve carga nos bancos de dados durante a execução do teste. Eu os criei apenas para executar consultas. Em situações da vida real, as consultas levarão mais tempo, pois outras consultas podem ser executadas ao mesmo tempo e, além disso, essas consultas executadas em paralelo podem bloquear tabelas. Para verificar a velocidade da consulta, eu estava usando o pgAdmin3 e a interface da Web do BigQuery.

No meu teste, obtive estes resultados:

Deixe-me mostrar esses resultados em um gráfico de barras para a consulta #1 e a consulta #5.

Nota: O banco de dados Postgres estava localizado em um servidor baseado nos EUA e eu estou na Europa, então houve um atraso adicional na transmissão de dados do Postgres.

Desempenho e conclusões do BigQuery

Com base nos resultados que obtive, tirei as seguintes conclusões:

• No caso de escalar o Postgres verticalmente, mesmo em 16 vezes, ele está dando apenas 10-25% de desempenho na execução de uma única consulta. Em outras palavras, um servidor Postgres com apenas um núcleo de CPU e 4 GB de RAM estava executando consultas com tempo muito semelhante ao tempo necessário para um servidor com 16 núcleos de CPU e 64 GB de RAM. É claro que servidores maiores podem processar conjuntos de dados muito maiores, no entanto, isso não melhora muito o tempo de execução da consulta.
• Para junções do Postgres com tabelas pequenas (tabela de locations tem cerca de 400 linhas e age_groups tem 100 linhas) não está gerando uma grande diferença em comparação com a execução de consultas em dados agregados localizados em uma tabela. Além disso, descobri que, para consultas que estão em execução de um a dois segundos, as consultas com associações internas são mais rápidas, mas para consultas de longa duração, a situação é diferente.
• Na situação do BigQuery com joins é totalmente diferente. O BigQuery não gosta de junções. A diferença de tempo entre as consultas, que usam dados agregados e não agregados, é bastante grande (para as consultas #3 e $5, foram cerca de dois segundos). Isso significa que, para o BigQuery, você pode fazer quantas subconsultas quiser, mas para um bom desempenho, a consulta deve usar uma tabela.
• O Postgres é mais rápido para consultas que usam agregação ou filtragem simples ou que usam um pequeno conjunto de dados. Descobri que as consultas que levam menos de cinco segundos no Postgres estão funcionando mais lentamente no BigQuery.
• O BigQuery está apresentando um desempenho muito melhor para consultas de longa duração. À medida que a diferença no tamanho do conjunto de dados aumenta, também aumenta a diferença no tempo que essas consultas levam para serem concluídas.

Quando faz sentido usar o BigQuery

Agora, vamos voltar à questão central discutida neste artigo: quando você deve realmente usar o Google BigQuery? Com base em minhas conclusões, sugiro usar o BigQuery quando as seguintes condições forem atendidas:

• Use-o quando tiver consultas que são executadas por mais de cinco segundos em um banco de dados relacional. A ideia do BigQuery é executar consultas analíticas complexas, o que significa que não faz sentido executar consultas que fazem agregação ou filtragem simples. O BigQuery é adequado para consultas “pesadas”, aquelas que operam com um grande conjunto de dados. Quanto maior o conjunto de dados, maior a probabilidade de você obter desempenho usando o BigQuery. O conjunto de dados que usei era de apenas 330 MB (megabytes, nem mesmo gigabytes).
• O BigQuery não gosta de junções, então você deve mesclar seus dados em uma tabela para obter um melhor tempo de execução. O BigQuery permite salvar os resultados da consulta em uma nova tabela, portanto, para criar uma nova tabela agregada, basta fazer upload de todos os seus dados para o BigQuery, executar uma consulta que consolidará todos os dados e salvá-los em uma nova tabela.
• O BigQuery é bom para cenários em que os dados não mudam com frequência e você deseja usar o cache, pois ele possui cache integrado. O que isto significa? Se você executar a mesma consulta e os dados nas tabelas não forem alterados (atualizados), o BigQuery usará apenas os resultados armazenados em cache e não tentará executar a consulta novamente. Além disso, o BigQuery não está cobrando dinheiro por consultas armazenadas em cache. Observação: mesmo as consultas em cache levam de 1 a 1,2 segundos para retornar os resultados.
• Você também pode usar o BigQuery quando quiser reduzir a carga em seu banco de dados relacional. As consultas analíticas são “pesadas” e o uso excessivo delas em um banco de dados relacional pode levar a problemas de desempenho. Então, você pode eventualmente ser forçado a pensar em dimensionar seu servidor. No entanto, com o BigQuery, você pode mover essas consultas em execução para um serviço de terceiros, para que elas não afetem seu banco de dados relacional principal.

Por fim, mais algumas palavras sobre como usar o BigQuery na vida real. Em nosso projeto real, os dados dos relatórios eram alterados semanalmente ou mensalmente, para que pudéssemos fazer upload de dados para o BigQuery manualmente. No entanto, se seus dados mudam com frequência, a sincronização de dados entre seu banco de dados relacional e o BigQuery pode não ser tão simples, e essa é uma advertência que vale a pena ter em mente.

Links

Você pode encontrar os dados de exemplo usados ​​neste artigo aqui, enquanto as consultas e os dados no formato CSV podem ser acessados ​​aqui.