Gilson Filho
Gilson #DEV

Follow

Gilson #DEV

Follow
Consultando uma amostragem de dados em tabelas no BigQuery

Photo by Mika Baumeister on Unsplash

Consultando uma amostragem de dados em tabelas no BigQuery

Gilson Filho's photo
Gilson Filho
·Sep 22, 2022·

3 min read

Quando trabalhamos com o BigQuery precisamos construir as queries para efetuar consultas em uma massa de dados que normalmente é muito grande, então para quem já trabalhou com SQL sabe que uma forma de retornar uma amostra pequena dos dados é utilizar a palavra LIMIT <quantidade de registros> vindo em seguida com a quantidade que precisa retornar. O Google trabalha sua cobrança nas consultas a serem feitas, definido pelo tamanho de dados (MB, Gb, Tb, etc) que vai precisar percorrer para cumprir o que foi pedido.

Infelizmente se fizermos a consulta abaixo em que queremos retornar 10 registros em uma base de 1Tb por exemplo não vai economizar na cobrança:

SELECT * FROM dataset.table1 LIMIT 10

Seguindo a mesma idéia, usando uma base pública do BigQuery, que é os dados dos aluguéis de bicicletas em Nova York. A query abaixo traz pouca informação, mas olha a quanidade de dados que ele vai precisar processar para executar:

image.png

Para ficar claro que o LIMIT nesse contexto não funciona vamos fazer o seguinte: vamos selecionar todas as colunas da tabela:

image.png

Aumentou para 7.5 GiB!

Ou seja, mesmo que solicitamos somente uma pequena amostra, a cobrança vai ser dentro dos dados que ele vai percorrer. Se a ferramenta disse que precisa passar por 800Gb por exemplo, ele vai fazer isso mesmo assim. Isso é ruim porque quanto mais fazer suas consultas e elas exigirem mais dados para percorrer, mais caro vai ficar no final do mês. O BigQuery tem algumas formas de resolver isso.

Consultas em tabelas particionadas

Uma forma é fazer o particionamento das tabelas, baseado em alguma informação como a data de registro de um produto como _PARTITIONDATE em que a plataforma, ou a partir dos dados de uma coluna da tabela. Assim você pode escolher por exemplo uma data em que tem uma quantidade pequena de dados para criar suas consultas:

SELECT * FROM dataset.table1 WHERE _PARTITIONDATE = '2021-01-01'

Dessa forma, o particionamento pode ajudar inclusive em produção para tornar suas consultas mais baratas e rápidas.

Consultas em amostragem

Uma segunda forma é fazer uma consulta em cima de uma seleção menor, um subconjunto de dados através do TABLESAMPLE. Ele traz uma quantidade aleatória de dados de uma tabela a partir de um determinada porcentagem. Por exemplo, em uma tabela de 10.000 registros, eu quero consultar em uma amostra de 1%, ou seja, 100 registros:

SELECT * FROM dataset.table1 TABLESAMPLE SYSTEM(1 PERCENT)

Dessa forma ele atua algo como um LIMIT nos bancos relacionais, mas principalmente diminuir os custos fazendo com que a plataforma percorra por menos dados. No nosso exemplo da base pública, se definirmos uma amostra de 1% em cima dos 7.5 GiB de dados diminuimos para 80.5 MB na consulta e com isso fica mais barato.

Consulta em uma amostra de uma amostra

Além disso você pode consultar em uma amostra menor ainda, a partir de outra. Por exemplo, você deseja ler 20% dos blocos de dados armazenados e quer selecionar 10% das linhas desses blocos:

SELECT * FROM dataset.table1 TABLESAMPLE SYSTEM (20 PERCENT) WHERE rand() < 0.1

Referências

 
Share this