3,205 lectures

Comment optimiser Google Cloud BigQuery et contrôler les coûts ?

par Daw-Chih Liou6m2023/04/18

Trop long; Pour lire

🙈 Vous découvrirez ce qui contribue aux coûts de BigQuery. 🏛️ Nous allons plonger dans l'architecture de BigQuery. 💰 Vous apprendrez 3 étapes simples pour économiser 99,97 % sur votre prochaine facture Google Cloud.

featured image - Comment optimiser Google Cloud BigQuery et contrôler les coûts ?

J'ai dépensé sans le savoir 3 000 $ en 6 heures en une seule requête dans Google Cloud BigQuery. Voici pourquoi et 3 étapes faciles pour optimiser les coûts.

Dans cet article

🙈 Vous découvrirez ce qui contribue aux coûts de BigQuery.
🏛️ Nous allons plonger dans l'architecture de BigQuery.
💰 Vous apprendrez 3 étapes faciles pour économiser 99,97 % sur votre prochaine facture Google Cloud.

Allons-y.

Comment est-ce arrivé?

Je développais un script pour préparer des échantillons de données pour les clients qui demandaient des consultations. Les exemples ont 100 lignes dans chaque fichier, et ils sont divisés en 55 paramètres régionaux. Ma requête ressemble à ceci

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

Les données ont été stockées dans « europe-west-4 », et le prix de l'interrogation est de 6 $ par To . Ainsi, en exécutant le script, j'ai traité :

500 To de données au total

3 To de données par pays en moyenne

54 $ par fichier d'échantillon de données en moyenne

Très cher.

Le script qui a coûté 3 000 $

Le script a été écrit en modules 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()

Il génère un exemple de fichier au format CSV par locale. Le processus est simple :

Interroger la table BigQuery avec une heure locale, une date de début, une date de fin et une limite.

Transformer le résultat de la requête au format CSV.

Ecriture du CSV dans le système de fichiers.

Répéter le processus pour tous les paramètres régionaux.

Quel est le problème?

Il s'avère que j'ai fait plusieurs erreurs dans ma requête. Si vous examinez à nouveau le modèle de tarification, vous remarquerez que le coût n'est lié qu'à la quantité de données que vous traitez. Il est donc clair que ma requête a recherché trop de données pour produire 100 lignes.

Avec cet aperçu, optimisons la requête étape par étape.

Ne sélectionnez pas *

C'est un peu contre-intuitif. Pourquoi mon instruction select a-t-elle quelque chose à voir avec la quantité de données qu'elle traite ? Quelles que soient les colonnes que je sélectionne, je devrais lire à partir des mêmes ressources et données, n'est-ce pas ?

Cela n'est vrai que pour les bases de données orientées lignes.

BigQuery est en fait une base de données en colonnes . Il est orienté colonne, ce qui signifie que les données sont structurées en colonnes. BigQuery utilise Dremel comme moteur de calcul sous-jacent. Lorsque les données sont déplacées du stockage à froid vers le stockage actif dans Dremel, il stocke les données dans une structure arborescente.

Chaque nœud feuille est un « enregistrement » orienté colonne au format Protobuf .

Dans BigQuery, chaque nœud est une VM. Une exécution de requête se propage depuis le serveur racine (nœud) via des serveurs intermédiaires vers les serveurs feuilles pour récupérer les colonnes sélectionnées.

Nous pouvons modifier la requête pour sélectionner des colonnes individuelles :

 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;

En sélectionnant explicitement toutes les colonnes, j'ai pu réduire les données traitées de 3,08 To à 2,94 To. C'est une réduction de 100 Go .

Utiliser une table partitionnée et interroger uniquement des sous-ensembles de données

Google Cloud recommande de partitionner les tables par date . Il nous permet d'interroger uniquement un sous-ensemble de données.

Pour optimiser davantage la requête, nous pouvons réduire la plage de dates dans l'instruction where, car la table est partitionnée par la colonne "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;

J'ai réduit la plage de dates à un jour au lieu de trois mois. J'ai pu réduire les données traitées à 37,43 Go . C'est juste une fraction de la requête d'origine.

Utiliser les résultats de requête matérialisés par étapes

Une autre façon de réduire les coûts consiste à réduire le jeu de données à partir duquel vous interrogez. BigQuery propose des tables de destination pour stocker les résultats des requêtes sous forme d'ensembles de données plus petits. Les tables de destination se présentent sous deux formes : temporaire et permanente.

Étant donné que les tables temporaires ont une durée de vie et qu'elles ne sont pas conçues pour être partagées et interrogées, j'ai créé une table de destination permanente pour matérialiser le résultat de la requête :

 // 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, })

Les résultats de la requête seront stockés dans la table de destination. Il servira de référence pour les futures requêtes. Chaque fois qu'il est possible d'interroger à partir de la table de destination, BigQuery traite les données de la table. Cela réduira considérablement la taille des données que nous recherchons.

Dernières pensées

C'est une étude très intéressante pour réduire les coûts dans BigQuery. En seulement trois étapes faciles :

Ne pas utiliser *

Utiliser une table partitionnée et interroger uniquement des sous-ensembles de données

Utiliser les résultats de requête matérialisés par étapes

J'ai pu réduire la taille des données traitées de 3 To à 37,5 Go . Il réduit considérablement le coût total de 3 000 $ à 30 $.

Si vous souhaitez en savoir plus sur l'architecture BigQuery, voici les références qui m'ont aidé :

BigQuery expliqué : présentation de l'architecture de BigQuery

Un regard sur Dremel

Colossus sous le capot : un aperçu du système de stockage évolutif de Google

Jupiter en évolution : réflexion sur la transformation du réseau de centres de données de Google

Vous pouvez en savoir plus sur les optimisations des coûts de BigQuery dans la documentation Google Cloud.

Un merci spécial à Abu Nashir pour avoir collaboré avec moi sur l'étude de cas et fourni des informations précieuses qui m'ont aidé à comprendre l'architecture de BigQuery.