3,205 Lesungen

So optimieren Sie Google Cloud BigQuery und kontrollieren die Kosten

von Daw-Chih Liou6m2023/04/18

Zu lang; Lesen

🙈 Sie erfahren, was zu den Kosten von BigQuery beiträgt. 🏛️ Wir tauchen in die Architektur von BigQuery ein. 💰 Sie lernen 3 einfache Schritte kennen, um 99,97 % bei Ihrer nächsten Google Cloud-Rechnung zu sparen.

featured image - So optimieren Sie Google Cloud BigQuery und kontrollieren die Kosten

Ich habe unwissentlich in 6 Stunden 3.000 US-Dollar für eine Abfrage in Google Cloud BigQuery verschwendet. Hier erfahren Sie, warum und 3 einfache Schritte zur Kostenoptimierung.

In diesem Artikel

🙈 Sie erfahren, was zu den Kosten von BigQuery beiträgt.
🏛️ Wir tauchen in die Architektur von BigQuery ein.
💰 Sie lernen 3 einfache Schritte kennen, um 99,97 % bei Ihrer nächsten Google Cloud-Rechnung zu sparen.

Lass uns gehen.

Wie ist es passiert?

Ich habe ein Skript entwickelt, um Datenproben für Kunden vorzubereiten, die eine Beratung in Anspruch genommen haben. Die Beispiele enthalten 100 Zeilen in jeder Datei und sind in 55 Gebietsschemas aufgeteilt. Meine Anfrage sieht so aus

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

Die Daten wurden in „Europe-West-4“ gespeichert und der Preis für die Abfrage beträgt 6 US-Dollar pro TB . Durch Ausführen des Skripts habe ich Folgendes verarbeitet:

Insgesamt 500 TB Daten

Durchschnittlich 3 TB Daten pro Land

Durchschnittlich 54 $ pro Datenbeispieldatei

Sehr teuer.

Das Drehbuch, das 3.000 Dollar kostete

Das Skript wurde in JavaScript-Modulen geschrieben.

 // 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()

Es generiert eine Beispieldatei im CSV- Format pro Gebietsschema. Der Prozess ist unkompliziert:

Abfragen der BigQuery-Tabelle mit einem lokalen Datum, einem Startdatum, einem Enddatum und einem Limit.

Umwandlung des Abfrageergebnisses in das CSV-Format.

Schreiben der CSV in das Dateisystem.

Wiederholen Sie den Vorgang für alle Gebietsschemas.

Was ist das Problem?

Es stellt sich heraus, dass ich bei meiner Abfrage mehrere Dinge falsch gemacht habe. Wenn Sie sich das Preismodell noch einmal ansehen, werden Sie feststellen, dass die Kosten nur davon abhängen, wie viele Daten Sie verarbeiten. Es ist also klar, dass meine Abfrage zu viele Daten nachgeschlagen hat, um 100 Zeilen zu erzeugen.

Mit dieser Erkenntnis optimieren wir die Abfrage Schritt für Schritt.

Nicht auswählen *

Es ist ein wenig kontraintuitiv. Warum hat meine Select-Anweisung etwas damit zu tun, wie viele Daten sie verarbeitet? Unabhängig davon, welche Spalten ich auswähle, sollte ich aus denselben Ressourcen und Daten lesen, oder?

Dies gilt nur für zeilenorientierte Datenbanken.

BigQuery ist eigentlich eine spaltenbasierte Datenbank . Es ist spaltenorientiert, das heißt, die Daten sind in Spalten strukturiert. BigQuery verwendet Dremel als zugrunde liegende Computer-Engine. Wenn die Daten vom Cold Storage in den aktiven Speicher in Dremel verschoben werden, werden die Daten in einer Baumstruktur gespeichert.

Jeder Blattknoten ist ein spaltenorientierter „Datensatz“ im Protobuf- Format.

In BigQuery ist jeder Knoten eine VM. Eine Abfrageausführung wird vom Stammserver (Knoten) über Zwischenserver an die Blattserver weitergegeben, um die ausgewählten Spalten abzurufen.

Wir können die Abfrage ändern, um einzelne Spalten auszuwählen:

 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;

Allein durch die explizite Auswahl aller Spalten konnte ich die verarbeiteten Daten von 3,08 TB auf 2,94 TB reduzieren. Das ist eine Reduzierung um 100 GB .

Verwenden Sie eine partitionierte Tabelle und fragen Sie nur Teilmengen der Daten ab

Google Cloud empfiehlt , Tabellen nach Datum zu partitionieren . Damit können wir nur eine Teilmenge der Daten abfragen.

Um die Abfrage weiter zu optimieren, können wir den Datumsbereich in der where-Anweisung eingrenzen, da die Tabelle durch die Spalte „ts“ partitioniert ist.

 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;

Ich habe den Datumsbereich auf einen Tag statt auf drei Monate eingegrenzt. Ich konnte die verarbeiteten Daten auf 37,43 GB reduzieren. Es ist nur ein Bruchteil der ursprünglichen Abfrage.

Verwenden Sie materialisierte Abfrageergebnisse in Phasen

Eine weitere Möglichkeit, die Kosten zu senken, besteht darin, den Datensatz zu reduzieren, den Sie abfragen. BigQuery bietet Zieltabellen zum Speichern von Abfrageergebnissen als kleinere Datensätze. Zieltabellen gibt es in zwei Formen: temporär und permanent.

Da temporäre Tabellen eine Lebensdauer haben und nicht für die gemeinsame Nutzung und Abfrage konzipiert sind, habe ich eine permanente Zieltabelle erstellt, um das Abfrageergebnis zu materialisieren:

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

Die Abfrageergebnisse werden in der Zieltabelle gespeichert. Es dient als Referenz für zukünftige Anfragen. Wann immer eine Abfrage aus der Zieltabelle möglich ist, verarbeitet BigQuery die Daten aus der Tabelle. Dadurch wird die Datenmenge, nach der wir suchen, erheblich reduziert.

Abschließende Gedanken

Es handelt sich um eine sehr interessante Studie zur Kostenreduzierung in BigQuery. Mit nur drei einfachen Schritten:

Nicht verwenden *

Verwenden Sie eine partitionierte Tabelle und fragen Sie nur Teilmengen der Daten ab

Verwenden Sie materialisierte Abfrageergebnisse in Phasen

Ich konnte die verarbeitete Datengröße von 3 TB auf 37,5 GB reduzieren. Dadurch werden die Gesamtkosten deutlich von 3.000 $ auf 30 $ gesenkt.

Wenn Sie mehr über die BigQuery-Architektur erfahren möchten, finden Sie hier die Referenzen, die mir geholfen haben:

BigQuery erklärt: Ein Überblick über die Architektur von BigQuery

Ein Blick auf Dremel

Koloss unter der Haube: Ein Blick in das skalierbare Speichersystem von Google

Jupiter entwickelt sich weiter: Nachdenken über die Transformation des Rechenzentrumsnetzwerks von Google

Weitere Informationen zu BigQuery-Kostenoptimierungen finden Sie in der Google Cloud-Dokumentation.

Besonderer Dank geht an Abu Nashir für die Zusammenarbeit mit mir an der Fallstudie und die Bereitstellung wertvoller Einblicke, die mir geholfen haben, die Architektur von BigQuery zu verstehen.