BigQuery kosten monitoren

Wat is BigQuery?

BigQuery is een schaalbaar datawarehouse van Google. Het is een plek waar je als Data Analist al je data samen kunt brengen op een toegankelijke manier en tegen relatief lage kosten. BigQuery is geschikt voor grote datasets. Het biedt de mogelijkheid om samengevoegde datasets te analyseren aan de hand van SQL. Daarom is het een populaire oplossing om inzichten te halen uit meerdere databronnen.

BigQuery is een betaalde service waarbij je gebruikerskosten betaalt wanneer je direct een query draait in de SQL interface (on-deman), maar ook wanneer je gebruik maakt van BigQuery via Google Data Studio. Er zit een minimum aan de gemaakte kosten (minimaal per query ongeveer: 0.000052 dollarcent) en verder kost het 5 dollar per verwerkte TB ( 1 TB = 1000 GB = 1000.000 MB).

Als je Data Studio gekoppeld hebt aan BigQuery, draait deze query’s tijdens het aanpassen van een rapport, tijdens rapport caching, of in sommige gevallen gedurende het bekijken/filteren van een rapport. Standaard haalt Data Studio elke 12 uur de data opnieuw op bij BigQuery. Je betaalt dus ook elke keer dat Data Studio de onderliggende BigQuery data verwerkt.

Kortom, query’s draaien in BigQuery kost geld en dat geldt ook voor query’s die gedraaid worden via Google Data Studio als daar een BigQuery dataset onder ligt. Omdat je ‘slechts’ 5 dollar betaalt voor elke verwerkte TB aan data, hoef je je hier bij kleinere datasets over het algemeen niet gelijk zorgen over te maken. Werk je met grote datasets? Dan kunnen de kosten behoorlijk oplopen en vraag je jezelf opeens af waar je BigQuery budget gebleven is. Zo kan je gratis tegoed van $ 300 (elke nieuwe klant bij Google Cloud Platform (https://cloud.google.com/free) ook sneller verdampen dan de bedoeling is.

Hoe monitor je BigQuery kosten?

Er zijn een aantal manieren om BigQuery kosten in de gaten te houden. Denk hierbij aan het beheren van de query kosten (limieten instellen per project of gebruiker), overstappen naar een flat-rate model voor querykosten (betaalmodel met vaste bedragen per periode) en/of het overzichtelijk maken waar de kosten naartoe gaan via BigQuery zelf.

Een kostenoverzicht helpt je te zien welke query’s (veel) geld kosten, zowel in BigQuery als via Google Data Studio, en in welke mate gebruikers of geplande query’s hieraan bijdragen. Dit biedt mogelijkheden om kostenbesparingen door te voeren:

• Maak (dure) geplande query’s efficiënter, dus kijk of de SQL code beter geschreven kan worden qua hoeveelheid verwerkte data.
• Optimaliseer de koppeling tussen Google Data Studio en BigQuery. Zo kun je de onderliggende datasets verbeteren. Hierdoor zijn er minder query’s nodig als het dashboard wordt gebruikt.
• Bespreek het verbruik met gebruikers. Waarom draaien zij deze query’s? Is er misschien behoefte aan een meer gestructureerde vorm van het ophalen van deze data waardoor de query’s niet meer nodig zijn?
• Maak afspraken met gebruikers en beperk daarmee onnodige ad-hoc query’s. 

Aan de slag!

Klinkt het maken van een BiqQuery kostenoverzicht ingewikkeld? Valt best mee! We helpen je graag op weg met een stappenplan wat je helpt om zelf een overzicht op te zetten.

Het overzicht ziet er zo uit: timestamp van de query, gebruiker, de query zelf, de gefactureerde bytes, de verwerkte bytes en de uiteindelijke prijs in dollar.

Voorbeeld kostenoverzicht. Van zowel query’s via Data Studio als direct in BQ worden de kosten bijgehouden.

Stappenplan kostenoverzicht BigQuery

Stap 1: Ga naar het Google Cloud Platform en maak in BigQuery een nieuwe tabel aan (create table, source: empty table) en geef deze een naam (bv: bigquery_logs)

Stap 2: Kies in het navigatie menu van het Google Cloud Platform voor “Logging” (onder de kop Operations).

Stap 3: In de “Logs Viewer” selecteer BigQuery als resource, zoals hieronder:

Stap 4: Klik op “Create Sink” en geef de Sink een naam (bv bigquery_sink_logs). Kies als Sink service: BigQuery en als destinatie de tabelnaam die je in stap 1 hebt aangemaakt. Optioneel kun je de partitie tabel optie aanvinken.

Stap 5: In de “Logs Router” kun je nu de aangemaakte Sink vinden. Kopieer daarbij de waarde uit het “Writer Identity” veld: (serviceAccount:”waarde”)

Stap 6: Ga terug naar de aangemaakte tabel in BigQuery uit stap 1 en klik op “Share Dataset”. Vul daar de zojuist gekopieerde waarde in bij “Add members” en selecteer “editor” als rol. Klik ten slot op “Add”.

Stap 7: Er zouden nu 2 tabellen moeten staan onder de in stap 1 aangemaakte tabel:

“cloudaudit_googleapis_com_data_access” en “cloudaudit_googleapis_com_data_activity”

Stap 8: In de _data_access tabel kun je de volgende query runnen om het overzicht te krijgen van de kosten per user en per query:

SELECT

 timestamp,

protopayload_auditlog.authenticationInfo.principalEmail as account,

protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query as query,

protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes as billedbytes,

protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes as processedbytes,

— calculate cost based on 5usd per TB processed

 CAST(5.0 * protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000000 as numeric) as cost_usd,

FROM

`YOUR_OWN_DATASET`

WHERE

protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes IS NOT NULL

Hulp of tips nodig?

Kom je er niet uit of wil je graag de hulp van een specialist? Onze Data Analytics Consultants denken graag met je mee! Neem contact met ons op.

1x per maand data insights, praktijkcases en een kijkje achter de schermen ontvangen?

Meld je aan voor onze maillijst en blijf 'up to data':