Google BigQuery is een datawarehouse dat op het Google Cloud Platform draait. Het is een populair platform onder data-analisten voor het opslaan, verwerken en analyseren van grote hoeveelheden data, waarbij rekenkracht van Googles computers wordt benut. De eerste terabyte (1.000 gigabyte) aan verwerkte querygegevens is gratis. Hoewel dat ruim lijkt, kunnen de kosten snel oplopen bij grote datasets als je niet goed let op het verbruik. Een potentieel risico is dat een script per ongeluk veel vaker draait dan bedoeld, wat kan leiden tot onverwacht hoge kosten—soms oplopend tot duizenden euro’s. Reden genoeg om zorgvuldig om te gaan met query-optimalisatie en kostenbeheer.
In dit blog geven we 7 tips om grip te houden op je BigQuery-kosten. We leggen onder meer uit hoe je inzage krijgt in je kosten, hoe je budgetalerts instelt en hoe je je queries zo efficiënt mogelijk inricht.
Om je kosten in Google Cloud bij te houden, kun je gebruik maken van de exportfunctie voor facturen. Hiermee kun je gedetailleerde kostenspecificaties exporteren naar een tabel en deze vervolgens analyseren en visualiseren in tools zoals Looker Studio. Volg de onderstaande stappen:
1. Ga naar het menu Billing:
2. Klik aan de linkerzijde op Billing export:
3. In het volgende scherm worden drie verschillende kostensoorten weergegeven waarvoor BigQuery een dataset aanmaakt:
Standard usage cost
Detailed usage cost
Pricing
4. Om de export in te stellen, heb je de juiste rechten nodig om betalingen te beheren. Kies voor Detailed usage cost, aangezien deze voldoende gegevens bevat voor het bijhouden van kosten:
5. Kies een project en dataset voor de tabel en klik op Save. BigQuery maakt nu de tabel aan, wat enkele minuten kan duren. Zodra de export is voltooid, wordt de tabel weergegeven in het overzicht van je queries en tabellen.
Wanneer de export voltooid is, wordt de tabel weergegeven in het overzicht met queries en tabellen:
In de Preview van de tabel staan veel kolommen en rijen met alle benodigde data voor een kostenanalyse. In de officiële kostendata documentatie wordt uitgelegd wat de kolommen betekenen en hoe je de data kunt opvragen met queries.
Voor het groeperen van de kosten over een specifieke periode kun je bijvoorbeeld de onderstaande query gebruiken:
SELECT
SUM(cost)
+ SUM(IFNULL((SELECT SUM(c.amount)
FROM UNNEST(credits) c), 0))
AS total
FROM `project.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY
invoice.month
Uiteraard is deze query geheel naar eigen wens aan te passen. Zo zou je de kosten verder kunnen uitsplitsen op project-id, munteenheid en locatie.
De query levert relevante data op, maar de presentatie van de data is in deze vorm niet optimaal. Het telkens uitvoeren van een nieuwe query is niet efficiënt en het delen van de gegevens, bijvoorbeeld met collega’s, is moeilijker. Het fijne aan BigQuery is de integratie met Looker Studio. Looker Studio is een rapportagetool van Google, waarmee je eenvoudig databronnen inlaadt en presenteert in een dashboard. Om dit te doen voer je eerst de query uit, waarna je rechtsonderin klikt op Open In > Looker Studio:
Looker Studio wordt nu geopend, waarin automatisch een diagram met de kosten per maand wordt weergegeven. Dit diagram is naar wens aan te passen en eenvoudig deelbaar.
2. Activeer budgetwaarschuwingen
Als je werkt met grote datasets, is het handig om een melding te ontvangen wanneer een bepaalde limiet wordt overschreden. Dit is zeker aan te raden als er meerdere mensen aan dezelfde dataset werken. Een automatische melding is in dit geval wenselijk als aanvulling op de kostenmonitoring, zoals beschreven in de eerste stap. Om budgetmeldingen in te schakelen volg je de onderstaande stappen:
1. Klik in het menu op Billing:
2. Vervolgens klik je op Budgets & alerts:
3. Klik op Create budget om een nieuw budget aan te maken. Stel bijvoorbeeld in dat je niet meer dan €100 per maand wilt uitgeven. Kies voor een maandelijkse budgetinstelling.
4. Ten slotte kun je in de laatste stap drempelregels instellen. Dit zijn meldingen die geactiveerd worden zodra specifieke drempels van het uitgegeven budget zijn bereikt. Hieronder stellen we in dat we meldingen willen ontvangen bij respectievelijk 50%, 75% en 90% van het uitgegeven budget:
Met deze instellingen zorg je ervoor dat je op tijd wordt gewaarschuwd wanneer je budgetplafond bijna wordt bereikt.
Let op! Het instellen van een budget beperkt de uitgaven van BigQuery niet. Wanneer het budgetplafond wordt bereikt, blijven de kosten doorlopen. De waarschuwingen dienen alleen ter informatie en hebben geen invloed op de verdere kosten.
3. Stel een quotum in
In tegenstelling tot budgetmeldingen kun je met een quotum een harde limiet instellen voor Google Cloud-uitgaven. Dit is handig om te voorkomen dat een query per ongeluk te vaak draait en zo zorgt voor onvoorziene hoge kosten. Je kunt een quotum instellen op zowel project- als gebruikersniveau.
Een quotum stel je als volgt in:
1. Klik op IAM & Admin > Quotas & System Limits:
2. Zorg ervoor dat het juiste project is geselecteerd bovenin het scherm. Filter nu op BigQuery API:
3. Om een limiet op projectniveau in te stellen, zoek je naar Query usage per day in de lijst met services. Klik dan op Edit:
4. Vul hier de gewenste limiet in TiB in (1 TiB is ongeveer 1,1 TB) en klik op Submit Request:
Wanneer je het quotum hebt ingesteld, duurt het enkele minuten voordat de wijziging wordt toegepast.
4. Optimaliseer de koppeling met Looker Studio
BigQuery wordt vaak gekoppeld aan Looker Studio om data te visualiseren in overzichtelijke dashboards. Een veelvoorkomende valkuil is dat de query zelf wordt gebruikt als databron. In dit geval wordt de query elke keer opnieuw uitgevoerd op de onderliggende dataset wanneer de data in het dashboard wordt ververst. Aangezien de onderliggende dataset meestal alle data bevat, kan dit leiden tot onnodig hoog dataverbruik.
Een voorbeeldquery waarmee het aantal sessies per week uit Google Analytics 4 wordt opgehaald.
Een betere aanpak is om het resultaat van een query op te slaan in een aparte tabel en deze tabel vervolgens als databron in Looker Studio te gebruiken. Maak hiervoor een Scheduled Query, zodat de query periodiek wordt uitgevoerd en de resultaten automatisch worden bijgewerkt:
Bij het instellen van de Scheduled Query spreken de meeste opties voor zich. Wel is het belangrijk om te kiezen hoe de data naar de nieuwe tabel wordt geschreven. Er zijn twee mogelijkheden: append en overwrite. Bij append worden nieuwe gegevens toegevoegd aan de bestaande tabel, en bij overwrite wordt de oude dataset volledig vervangen door de nieuwe gegevens. Als je niet verwacht dat historische data verandert, kun je het best voor de append-optie kiezen.
Wanneer je de instellingen hebt opgeslagen, kun je die later terugvinden in het menu Scheduled Queries.
In het overzicht met tabellen zie je nu als het goed is een nieuwe tabel met de kolommen uit jouw geplande query. De grootte van deze tabel zal veel kleiner zijn dan de oorspronkelijke dataset, omdat de tabel uitsluitend het resultaat bevat van de query die je hebt gedraaid op de oorspronkelijke data. Je kunt vervolgens deze tabel als databron gebruiken in Looker Studio:
Door op deze manier je data te gebruiken zorg je voor snellere dashboards en minder kosten.
5. Query alleen de data die je nodig hebt
Het klinkt logisch, maar het wordt vaak vergeten: query alleen de data die je daadwerkelijk nodig hebt. Hoe meer data je opvraagt, hoe meer rekenkracht vereist is. Het gebruik van een SELECT *-query is de meest kostbare, omdat deze alle rijen en kolommen ophaalt. Het is veel efficiënter om alleen de relevante kolommen en rijen te selecteren die je daadwerkelijk nodig hebt.
6. Partitioneer en cluster je data
Wanneer je data filtert met een WHERE-clausule, bijvoorbeeld op basis van een datum, wordt zonder partitionering nog steeds de hele dataset doorzocht. Dit komt doordat de query elke keer opnieuw wordt uitgevoerd op de volledige dataset, inclusief het filteren van de data. Hierdoor worden alle records opgehaald, zelfs als je slechts een klein deel nodig hebt. Partitionering voorkomt dit door alleen de relevante delen van de dataset te scannen, wat de prestaties verbetert en kosten verlaagt.
Zelfs als je BigQuery-data in Looker Studio filtert op datum, wordt de hele dataset doorgenomen.
Stel dat je een tabel met verkoopdata van de afgelopen 36 maanden partitioneert, dan maakt BigQuery één tabel aan voor elke dag aan data:
Een voorbeeld van gepartitioneerde verkoopdata. Bron: Google.
Als je nu de data van een maand wilt analyseren, query je alleen de tabellen van die maand. Je zult dan ook alleen de querykosten van de data in die tabellen betalen, in plaats van alle 36 maanden.
Sommige databronnen worden automatisch gepartitioneerd. Zo slaat Google Analytics 4 eventdata op in tabellen met de naam events_YYYYMMDD.
Na het aanmaken van partities is het nog mogelijk om je tabellen te clusteren. Door te clusteren groepeert BigQuery automatisch de data binnen elke partitie op basis van de gespecificeerde kolommen (maximaal 4). De bovenstaande gepartitioneerde verkooptabel zou je bijvoorbeeld kunnen clusteren op naam van de accountmanager. De namen Bob en Tom uit de query worden gescand in de groene clusters, alle andere clusters worden genegeerd, waardoor de kosten verder worden beperkt:
Een voorbeeld van gepartitioneerde en geclusterde verkoopdata. Bron: Google.
7. Controleer van tevoren de querykosten
Voordat je een query uitvoert, wordt er rechtsbovenin het scherm een indicatie gegeven van de hoeveelheid data die wordt verwerkt. Loopt de hoeveelheid data hoog op? Dan is het waarschijnlijk mogelijk om de query te verbeteren.
Conclusie
Grip houden op BigQuery-kosten is belangrijk om onverwachte uitgaven te voorkomen. Vooral als je werkt met grote datasets is dat een noodzaak. Door gebruik te maken van de tips die we in dit blog hebben besproken, kun je je kosten monitoren en beperken. Begin vandaag nog met het implementeren van deze tips en voorkom dat je een onverwacht hoge rekening krijgt. Hulp nodig? Neem dan contact met ons op.
Paul werkt sinds eind 2019 bij SDIM. Hij studeerde geschiedenis en internationale betrekkingen en heeft een brede achtergrond in marketing en techniek.
Reacties (0)