BigQuery: mistä lasku syntyy ja miten se pidetään kurissa

Mikä BigQuery on — ja mikä se ei ole

BigQuery on Googlen palvelimeton datavarasto. Palvelimeton tarkoittaa tässä kirjaimellisesti sitä, ettei ole palvelinta jota mitoittaa, käynnistää tai päivittää: luot datasetin, lataat dataa ja kirjoitat SQL:ää. Kapasiteetti tulee taustalta sitä mukaa kuin kysely sitä tarvitsee.

Se ei kuitenkaan ole tavallinen tietokanta, eikä sitä kannata kohdella sellaisena. BigQuery on sarakepohjainen ja suunniteltu lukemaan suuria määriä kerralla. Yksittäisen rivin hakeminen tunnisteella on siinä hidasta ja suhteessa kallista — se on operaatio, johon PostgreSQL vastaa millisekunneissa ja BigQuery sekunneissa.

Käytännön sääntö: BigQuery on analytiikkaa varten, ei sovelluksen tietokannaksi. Jos ratkaisu tarvitsee molemmat, ne ovat kaksi eri järjestelmää, ja data siirtyy niiden välillä putkella.

Mistä lasku syntyy

Kustannus jakautuu kahteen osaan, ja ne kannattaa pitää mielessä erikseen, koska niitä hallitaan eri keinoin.

• Tallennus — hinnoitellaan gigatavuina kuukaudessa. Taulu, jota ei ole muokattu 90 päivään, siirtyy automaattisesti pitkäaikaistallennuksen hintaan, joka on noin puolet aktiivisen hinnasta. Tämä tapahtuu itsestään, siihen ei tarvitse tehdä mitään.
• Kyselyt — oletusmallissa (on-demand) maksat kyselyn lukemasta datamäärästä, et sen kestosta tai tuloksen koosta. Tämä on se kohta, jossa yllätykset syntyvät.

Ilmaista kuukausittain on 10 gigatavua tallennusta ja ensimmäinen teratavu kyselyitä. Pienen ja keskisuuren yrityksen GA4-raportointi mahtuu näihin rajoihin yllättävän usein — ja jos ei mahdu, syy on lähes aina taulujen rakenteessa eikä datan määrässä.

Vaihtoehtona on kapasiteettihinnoittelu (Editions), jossa varaat laskentakapasiteettia tunneittain riippumatta luetusta datamäärästä. Se muuttuu edullisemmaksi vasta melko suurilla ja tasaisilla käyttömäärillä. Pk-yrityksen kannattaa aloittaa on-demandista ja vaihtaa vasta jos laskutus osoittaa toisin — ei toisin päin.

Tarkat hinnat vaihtelevat alueittain ja muuttuvat ajoittain, joten tarkista ne Googlen hinnoittelusivulta ennen budjetointia. Suuruusluokka on kuitenkin pysynyt: tallennus on halpaa, ja kyselyistä maksat luettujen tavujen mukaan.

Miksi lasku yllättää

Kun kyselyn hinta perustuu luettuun datamäärään, tavat joilla dataa luetaan turhaan muuttuvat rahaksi. Nämä ovat ne, jotka toistuvat perityissä ympäristöissä lähes poikkeuksetta:

• select * taulusta, jossa on sata saraketta, kun raportti käyttää neljää. Sarakepohjaisessa varastossa maksat jokaisesta luetusta sarakkeesta — myös niistä, joita et käytä.
• Kysely ilman partitiorajausta. Ilman sitä luetaan koko historia, vaikka raportti näyttäisi vain viime viikon.
• Raportti kytkettynä suoraan tapahtumatauluun. Jokainen dashboardin avaus on uusi kysely, ja suosittu raportti ajetaan kymmeniä kertoja päivässä.
• Ajastetut kyselyt, jotka laskevat koko historian joka yö uudelleen, vaikka muuttuneita ovat vain viimeiset päivät.
• Suoratoistokirjoitus (streaming) siellä missä eräajo riittäisi. Eräajolataus on ilmainen, suoratoisto ei.
• Näkymien ketjut. Näkymä ei säilö tulosta, joten viiden näkymän ketju laskee koko ketjun uudelleen joka kerta.

Partitiointi ja klusterointi

Tämä on selvästi tärkein yksittäinen kustannusvipu, ja se päätetään taulua luotaessa. Partitiointi jakaa taulun palasiin päivämäärän mukaan, jolloin aikaväliin rajattu kysely lukee vain tarvittavat palat. Klusterointi järjestää datan partition sisällä, jolloin yleisimmillä suodattimilla luettava määrä pienenee edelleen.

Yhdessä nämä muuttavat tyypillisen raportointikyselyn hinnan murto-osaan. Ero ei ole prosentteja vaan kertaluokkia.

create or replace table `analytics.fct_sessions`
partition by session_date
cluster by channel_group, device_category
options (
  -- Vanhat partitiot poistuvat itsestään: kolmen vuoden historia riittää
  -- lähes kaikkeen raportointiin, ja loput olisi maksullista arkistoa.
  partition_expiration_days = 1095,

  -- Tärkein rivi tässä: kysely ilman päivämäärärajausta epäonnistuu
  -- sen sijaan että lukisi vahingossa koko taulun.
  require_partition_filter = true
)
as select * from `analytics.stg_sessions`;

-- Lukee koko taulun. Ilman require_partition_filter tämä olisi mennyt läpi.
select channel_group, count(*)
  from `analytics.fct_sessions`
 group by 1;

-- Lukee yhden kuukauden partitiot ja vain kaksi saraketta.
select channel_group, count(*)
  from `analytics.fct_sessions`
 where session_date >= date_trunc(current_date(), month)
 group by 1;

require_partition_filter on aliarvostetuin asetus koko BigQueryssä. Se muuttaa kalliin vahingon selkeäksi virheilmoitukseksi, ja virheilmoituksen huomaa heti — laskun vasta kuukauden päästä.

Klusterointisarakkeet valitaan sen mukaan, millä kentillä raporteissa oikeasti suodatetaan. Järjestyksellä on merkitystä: yleisin suodatin ensin.

Kyselyn hinnan tarkistaminen etukäteen

BigQuery kertoo kyselyn hinnan ennen ajoa. Käyttöliittymässä arvio näkyy oikeassa yläkulmassa, ja komentoriviltä sen saa kuivaharjoituksella. Tämän tarkistaminen ennen raskaan kyselyn ajamista on tapa, joka kannattaa opetella heti.

# Paljonko tämä lukisi? Ei aja kyselyä eikä maksa mitään.
bq query --dry_run --use_legacy_sql=false \
  'select * from `projekti.analytics.fct_sessions`'

# Katto yksittäiselle kyselylle: yli menevä epäonnistuu ennen ajoa.
# 10 GB = 10737418240 tavua.
bq query --maximum_bytes_billed=10737418240 --use_legacy_sql=false \
  'select channel_group, count(*)
     from `projekti.analytics.fct_sessions`
    where session_date >= current_date() - 30
    group by 1'

maximum_bytes_billed kannattaa asettaa oletukseksi projektitasolle, ei vain yksittäisiin kyselyihin. Silloin karannut kysely kaatuu itsestään sen sijaan että ehtisi maksaa.

Kustannuskatot ja hälytykset

Kyselykohtainen katto suojaa yksittäiseltä virheeltä. Sen lisäksi tarvitaan kaksi laajempaa suojaa, ja molemmat kannattaa laittaa kuntoon ennen kuin ympäristöön päästetään useampi käyttäjä.

• Mukautettu kiintiö (custom quota) rajaa, kuinka paljon dataa projekti tai yksittäinen käyttäjä saa lukea vuorokaudessa. Tämä on ainoa keino, joka oikeasti pysäyttää kulun — budjettihälytys vain kertoo siitä.
• Budjetti ja hälytykset laskutuksessa kertovat, kun kuukauden kulu ylittää sovitun rajan. Aseta hälytys prosenttiosuuksille, ei vain lopulliselle summalle: 50 prosentin hälytys kuun puolivälissä on hyödyllinen, 100 prosentin hälytys kuun lopussa on jälki-ilmoitus.
• Erillinen projekti raportoinnille ja kehitykselle. Silloin näet laskusta, kumpi kuluttaa, ja voit asettaa niille eri kiintiöt.

Yksi tallennuspuolen vipu kannattaa myös tarkistaa: tallennuslaskutuksen voi vaihtaa loogisesta fyysiseen, jolloin maksat pakatusta koosta. Hyvin pakkautuvalla datalla — ja tapahtumadata pakkautuu hyvin — ero on merkittävä. Vaihto tehdään datasettikohtaisesti, joten sitä voi kokeilla yhdellä ensin.

Sijainnin valinta: EU vai Hamina

Datasetin sijainti valitaan sitä luotaessa, eikä sitä voi jälkikäteen vaihtaa ilman datan siirtoa. Tämä on yksi harvoista BigQueryn päätöksistä, jota ei voi korjata myöhemmin helposti, joten se kannattaa tehdä kerralla oikein.

Suomalaiselle organisaatiolle vaihtoehtoja on käytännössä kaksi. EU-monialue pitää datan Euroopan unionin sisällä useassa konesalissa. Yksialue europe-north1 on Googlen Haminan konesali, jolloin data pysyy Suomessa ja viive on lyhin.

Valinta riippuu vaatimuksista. Jos sopimuksissa tai toimialan sääntelyssä edellytetään datan pysymistä Suomessa, europe-north1 on selvä valinta. Jos riittää että data pysyy EU:ssa, monialue on joustavampi ja joissain palveluissa laajemmin tuettu. Kummassakin tapauksessa oleellista on, että valinta tehdään tietoisesti — ei sillä, että jätetään oletusarvo (Yhdysvallat) koskematta.

Sama sijaintisääntö koskee GA4:n BigQuery-vientiä: viennin kohdealue valitaan linkitystä luotaessa, eikä sitäkään voi vaihtaa jälkikäteen ilman uudelleenrakentamista.

Käyttöoikeudet

BigQueryn oikeudet voi antaa neljällä tasolla: organisaatio, projekti, datasetti ja taulu. Yleisin virhe on antaa oikeudet projektitasolla, koska se on helpointa — jolloin raportoinnin lukuoikeus tarkoittaa pääsyä kaikkeen mitä projektissa on, myös tulevaan dataan josta ei vielä tiedetä.

Käytännön lähtökohta: anna oikeudet datasettitasolla ja jaa datasetit sen mukaan, kuka mitäkin saa nähdä. Raakadata, mallinnettu data ja raportointikerros ovat luonteva jako, jolla pärjää pitkälle.

-- Myyntitiimi näkee vain oman maansa rivit, ilman erillistä
-- kopiotaulua jokaiselle ryhmälle.
create row access policy myynti_suomi
    on `analytics.fct_orders`
 grant to ('group:myynti-fi@yritys.fi')
filter using (country = 'FI');

Laajemmille tarpeille on kaksi muuta työkalua. Valtuutettu näkymä (authorized view) antaa pääsyn rajattuun näkymään ilman pääsyä sen taustatauluun — se on oikea tapa jakaa dataa toiselle tiimille. Sarakekohtainen suojaus taas piilottaa yksittäiset kentät, esimerkiksi henkilötiedot, ryhmiltä joilla ei ole niihin tarvetta.

Palvelutileistä sama sääntö kuin muuallakin: käytä Workload Identityä äläkä ladattua avaintiedostoa. BigQueryyn pääsevä avain versionhallinnassa on kallis tapa oppia tämä — sekä rahassa että datassa.

Milloin BigQuery ei ole oikea valinta

• Sovelluksen tietokannaksi. Yksittäisten rivien haku ja päivitys on väärä käyttötapa; siihen sopii tavallinen tietokanta.
• Kun dataa on todella vähän. Jos koko aineisto mahtuu yhteen taulukkoon eikä kasva, datavarasto on ylimitoitettu — Sheets riittää.
• Kun tarvitaan sekunnin alle jäävä vaste yksittäiselle kyselylle käyttöliittymässä. Esilaskettu tulos toiseen järjestelmään on silloin oikea ratkaisu.
• Kun dataa muokataan rivi kerrallaan jatkuvasti. BigQuery taipuu siihen, mutta se on kallista ja hidasta verrattuna siihen mihin se on tehty.

Yleisimmät virheet

• Taulut ilman partitiointia. Halvin korjaus tehdään taulua luotaessa, kallein vuoden käytön jälkeen.
• require_partition_filter jätetty pois. Yksi unohtunut where-ehto lukee koko historian.
• Raportti kytkettynä raakadataan aggregaattitaulun sijaan. Sama kysely ajetaan uudelleen jokaisella avauksella.
• Oikeudet projektitasolla, koska se oli nopeampaa kuin datasettikohtaisesti.
• Sijainniksi jäänyt oletus eli Yhdysvallat, ja se huomataan vasta kun joku kysyy missä data on.
• Ei kustannuskattoa eikä kiintiötä. Ensimmäinen karannut kysely on silloin rajaton.
• Näkymien ketjuttaminen materialisoinnin sijaan. Näkymä on ilmainen luoda ja maksullinen lukea.