3,205 leituras

Como otimizar o Google Cloud BigQuery e controlar o custo

por Daw-Chih Liou6m2023/04/18

Muito longo; Para ler

🙈 Você aprenderá o que contribui para os custos do BigQuery. 🏛️ Vamos mergulhar na arquitetura do BigQuery. 💰 Você aprenderá 3 etapas fáceis para economizar 99,97% em sua próxima fatura do Google Cloud.

featured image - Como otimizar o Google Cloud BigQuery e controlar o custo

Gastei US$ 3.000, sem saber, em 6 horas com uma consulta no Google Cloud BigQuery. Aqui está o porquê e 3 etapas fáceis para otimizar o custo.

Neste artigo

🙈 Você aprenderá o que contribui para os custos do BigQuery.
🏛️ Vamos mergulhar na arquitetura do BigQuery.
💰 Você aprenderá 3 etapas fáceis para economizar 99,97% em sua próxima fatura do Google Cloud.

Vamos.

Como isso aconteceu?

Eu estava desenvolvendo um script para preparar amostras de dados para clientes que buscavam consultas. As amostras têm 100 linhas em cada arquivo e são divididas em 55 localidades. Minha consulta se parece com isso

 SELECT * FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;

Os dados foram armazenados em “europe-west-4” e o preço da consulta é de US$ 6 por TB . Então, ao executar o script, processei:

500 TB de dados no total

3 TB de dados por país, em média

$ 54 por arquivo de amostra de dados em média

Muito caro.

O roteiro que custou US$ 3.000

O script foi escrito em módulos JavaScript .

 // bq-samples.mjs import { BigQuery } from "@google-cloud/bigquery"; import { Parser } from "@json2csv/plainjs"; import makeDir from "make-dir"; import { write } from "./write.mjs"; import { locales } from "./locales.mjs"; import { perf } from "./performance.mjs"; const q = (locale, start, end, limit) => `SELECT * FROM \`project.dataset.table\` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "${locale}" LIMIT ${limit}` async function main() { const timer = perf() const dir = await makeDir('samples') const bigquery = new BigQuery() const csvParser = new Parser({}) try { const jobs = locales.map((locale) => async () => { // get query result from BigQuery const [job] = await bigquery.createQueryJob({ query: q(locale, "2022-12-01", "2023-02-28", 100), }) const [rows] = await job.getQueryResults() // parse rows into csv format const csv = parse(csvParser, rows) // write data into csv files and store in the file system await write(csv, dir, locale, "2022-12-01", "2023-02-28", 100) }) await Promise.all(jobs.map((job) => job())) console.log(`✨ Done in ${timer.stop()} seconds.`) } catch (error) { console.error('❌ Failed to create sample file', error) } } await main()

Ele gera um arquivo de amostra no formato CSV por localidade. O processo é direto:

Consultar a tabela do BigQuery com local, data de início, data de término e limite.

Transformando o resultado da consulta em formato CSV.

Escrevendo o CSV no sistema de arquivos.

Repetindo o processo para todos os locais.

Qual é o problema?

Acontece que fiz várias coisas erradas na minha consulta. Se você observar o modelo de preços novamente, perceberá que o custo está relacionado apenas à quantidade de dados que você processa. Portanto, está claro que minha consulta pesquisou muitos dados para produzir 100 linhas.

Com esse insight, vamos otimizar a consulta passo a passo.

Não selecione *

É um pouco contra-intuitivo. Por que minha instrução select tem algo a ver com a quantidade de dados que ela processa? Independentemente das colunas selecionadas, devo ler os mesmos recursos e dados, certo?

É verdade apenas para bancos de dados orientados a linhas.

O BigQuery é, na verdade, um banco de dados colunar . É orientado a colunas, o que significa que os dados são estruturados em colunas. O BigQuery usa Dremel como seu mecanismo de computação subjacente. Quando os dados são movidos do armazenamento frio para o armazenamento ativo na Dremel, ele armazena os dados em uma estrutura de árvore.

Cada nó folha é um “registro” orientado a colunas no formato Protobuf .

No BigQuery, cada nó é uma VM. Uma execução de consulta se propaga do servidor raiz (nó) por meio de servidores intermediários até os servidores folha para recuperar as colunas selecionadas.

Podemos modificar a consulta para selecionar colunas individuais:

 SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;

Apenas selecionando todas as colunas explicitamente, consegui reduzir os dados processados de 3,08 TB para 2,94 TB. Isso é uma redução de 100 GB .

Use tabelas particionadas e consulte apenas subconjuntos de dados

O Google Cloud recomenda particionar tabelas por data . Ele nos permite consultar apenas um subconjunto de dados.

Para otimizar ainda mais a consulta, podemos restringir o intervalo de datas na instrução where porque a tabela é particionada pela coluna “ts”.

 SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts = TIMESTAMP("2022-12-01") AND locale = "US" LIMIT 100;

Reduzi o intervalo de datas para um dia em vez de três meses. Consegui reduzir os dados processados para 37,43 GB . É apenas uma fração da consulta original.

Use os resultados da consulta materializada em estágios

Outra maneira de reduzir custos é reduzir o conjunto de dados do qual você está consultando. O BigQuery oferece tabelas de destino para armazenar os resultados da consulta como conjuntos de dados menores. As tabelas de destino vêm em duas formas: temporárias e permanentes.

Como as tabelas temporárias têm vida útil e não foram projetadas para serem compartilhadas e consultadas, criei uma tabela de destino permanente para materializar o resultado da consulta:

 // bq-samples.mjs const dataset = bigquery.dataset('materialized_dataset') const materialzedTable = dataset.table('materialized_table') // ... const [job] = await bigquery.createQueryJob({ query: q(locale, '2022-12-01', '2023-02-28', 100), destination: materialzedTable, })

Os resultados da consulta serão armazenados na tabela de destino. Servirá de referência para consultas futuras. Sempre que for possível consultar a tabela de destino, o BigQuery processará os dados da tabela. Isso reduzirá muito o tamanho dos dados que procuramos.

Pensamentos finais

É um estudo muito interessante para reduzir o custo no BigQuery. Com apenas três passos fáceis:

Não use *

Use tabelas particionadas e consulte apenas subconjuntos de dados

Use os resultados da consulta materializada em estágios

Consegui reduzir o tamanho dos dados processados de 3 TB para 37,5 GB . Reduz significativamente o custo total de US$ 3.000 para US$ 30.

Se você estiver interessado em aprender mais sobre a arquitetura do BigQuery, aqui estão as referências que me ajudaram:

BigQuery explicado: uma visão geral da arquitetura do BigQuery

Uma olhada na Dremel

Colossus sob o capô: uma espiada no sistema de armazenamento escalável do Google

Júpiter evoluindo: refletindo sobre a transformação da rede de data centers do Google

Você pode ler mais sobre as otimizações de custo do BigQuery na documentação do Google Cloud.

Agradecimentos especiais a Abu Nashir por colaborar comigo no estudo de caso e fornecer insights valiosos que me ajudaram a entender a arquitetura do BigQuery.