3,205 lecturas

Cómo optimizar Google Cloud BigQuery y controlar el costo

por Daw-Chih Liou6m2023/04/18

Demasiado Largo; Para Leer

🙈 Aprenderá qué contribuye a los costos de BigQuery. 🏛️ Nos sumergiremos en la arquitectura de BigQuery. 💰 Aprenderá 3 sencillos pasos para ahorrar un 99,97 % en su próxima factura de Google Cloud.

featured image - Cómo optimizar Google Cloud BigQuery y controlar el costo

Sin saberlo, gasté USD 3000 en 6 horas en una consulta en Google Cloud BigQuery. He aquí por qué y 3 sencillos pasos para optimizar el costo.

En este articulo

🙈 Aprenderá qué contribuye a los costos de BigQuery.
🏛️ Nos sumergiremos en la arquitectura de BigQuery.
💰 Aprenderá 3 sencillos pasos para ahorrar un 99,97 % en su próxima factura de Google Cloud.

Vamos.

¿Cómo ha ocurrido?

Estaba desarrollando un script para preparar muestras de datos para los clientes que se acercaron para realizar consultas. Las muestras tienen 100 filas en cada archivo y se dividen en 55 configuraciones regionales. Mi consulta se ve así

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

Los datos se almacenaron en “europe-west-4”, y el precio de consulta es de $6 por TB . Entonces, al ejecutar el script, procesé:

500 TB de datos en total

3 TB de datos por país en promedio

$54 por archivo de muestra de datos en promedio

Muy caro.

El guión que costó $ 3,000

El script fue escrito en módulos de 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()

Genera un archivo de muestra en formato CSV por configuración regional. El proceso es sencillo:

Consultar la tabla de BigQuery con un local, fecha de inicio, fecha de finalización y límite.

Transformar el resultado de la consulta en formato CSV.

Escribir el CSV en el sistema de archivos.

Repetir el proceso para todas las localidades.

¿Cuál es el problema?

Resulta que hice varias cosas mal en mi consulta. Si observa nuevamente el modelo de precios, notará que el costo solo está relacionado con la cantidad de datos que procesa. Entonces, está claro que mi consulta buscó demasiados datos para producir 100 filas.

Con esta información, optimicemos la consulta paso a paso.

No seleccionar *

Es un poco contradictorio. ¿Por qué mi declaración de selección tiene algo que ver con la cantidad de datos que procesa? Independientemente de las columnas que seleccione, debería estar leyendo de los mismos recursos y datos, ¿verdad?

Solo es cierto para bases de datos orientadas a filas.

BigQuery es en realidad una base de datos en columnas . Está orientado a columnas, lo que significa que los datos están estructurados en columnas. BigQuery usa Dremel como su motor informático subyacente. Cuando los datos se mueven del almacenamiento en frío al almacenamiento activo en Dremel, almacena los datos en una estructura de árbol.

Cada nodo hoja es un "registro" orientado a columnas en formato Protobuf .

En BigQuery, cada nodo es una VM. Una ejecución de consulta se propaga desde el servidor raíz (nodo) a través de servidores intermedios a los servidores hoja para recuperar las columnas seleccionadas.

Podemos modificar la consulta para seleccionar columnas individuales:

 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;

Con solo seleccionar todas las columnas de forma explícita, pude reducir los datos procesados de 3,08 TB a 2,94 TB. Eso es una reducción de 100 GB .

Usar tablas particionadas y subconjuntos de datos de solo consulta

Google Cloud recomienda dividir las tablas por fecha . Nos permite consultar solo un subconjunto de datos.

Para optimizar aún más la consulta, podemos reducir el rango de fechas en la instrucción where porque la tabla está dividida por la columna "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;

Reduje el intervalo de fechas a un día en lugar de tres meses. Pude reducir los datos procesados a 37,43 GB . Es solo una fracción de la consulta original.

Usar resultados de consulta materializados en etapas

Otra forma de reducir los costos es reducir el conjunto de datos desde el que realiza la consulta. BigQuery ofrece tablas de destino para almacenar los resultados de las consultas como conjuntos de datos más pequeños. Las tablas de destino vienen en dos formas: temporales y permanentes.

Debido a que las tablas temporales tienen una vida útil y no están diseñadas para compartirse y consultarse, creé una tabla de destino permanente para materializar el resultado de la 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, })

Los resultados de la consulta se almacenarán en la tabla de destino. Te servirá de referencia para futuras consultas. Siempre que sea posible consultar desde la tabla de destino, BigQuery procesará los datos de la tabla. Reducirá en gran medida el tamaño de los datos que buscamos.

Pensamientos finales

Es un estudio muy interesante para reducir el costo en BigQuery. Con solo tres sencillos pasos:

No use *

Usar tablas particionadas y subconjuntos de datos de solo consulta

Usar resultados de consulta materializados en etapas

Pude reducir el tamaño de los datos procesados de 3 TB a 37,5 GB . Reduce significativamente el costo total de $3,000 a $30.

Si está interesado en obtener más información sobre la arquitectura de BigQuery, estas son las referencias que me ayudaron:

Explicación de BigQuery: descripción general de la arquitectura de BigQuery

Una mirada a Dremel

Colossus bajo el capó: un vistazo al sistema de almacenamiento escalable de Google

Júpiter en evolución: reflexión sobre la transformación de la red del centro de datos de Google

Puede obtener más información sobre las optimizaciones de costos de BigQuery en la documentación de Google Cloud.

Un agradecimiento especial a Abu Nashir por colaborar conmigo en el estudio de caso y proporcionar información valiosa que me ayudó a comprender la arquitectura de BigQuery.