BigQuery-opas: hinnoittelu ja kustannusten hallinta

Mikä BigQuery on ja mikä se ei ole

BigQuery on Googlen hallittu, sarakepohjainen tietovarasto analyyttisiin kyselyihin. Palvelimia ei ylläpidetä itse, mutta datan rakenne, käyttöoikeudet, kapasiteetin käyttö ja kustannukset kuuluvat silti toteutuksen suunnitteluun.

BigQuery soveltuu erityisesti laajojen aineistojen koostamiseen ja analyysiin. Sovelluksen jatkuvia yksittäisten tietueiden päivityksiä ja nopeita tapahtumakäsittelyjä varten kannattaa arvioida myös operatiivista tietokantaa. Sopiva ratkaisu riippuu vasteaika-, samanaikaisuus- ja transaktiovaatimuksista.

Mistä lasku syntyy

Keskeiset kustannuserät ovat tallennus ja laskenta. Niiden lisäksi esimerkiksi valittu kirjoitustapa, tiedonsiirrot ja muut palvelut voivat vaikuttaa kokonaisuuteen.

• BigQueryn ajantasainen hinnoittelu

• Tallennuksen hinta määräytyy muun muassa alueen, laskutusmallin ja säilytettävän datan määrän perusteella. Pitkään muuttumattomana säilyvään tauluun tai partitioon voi soveltua pitkäaikaistallennuksen hinta.
• On-demand-kyselyissä laskutus perustuu käsiteltyyn datamäärään. Editions-mallissa laskentaa hinnoitellaan slot-kapasiteetin perusteella.

BigQueryn maksuttomaan kuukausikäyttöön sisältyy 10 GiB tallennusta ja 1 TiB on-demand-kyselyissä käsiteltyä dataa. Tarkistakaa ajantasaiset ehdot ja aluekohtaiset hinnat ennen budjetointia.

Verratkaa on-demand- ja kapasiteettihinnoittelua omien kyselyiden, samanaikaisen käytön ja ennustettavuuden tarpeen perusteella. Valintaa ei kannata tehdä pelkän organisaation koon mukaan.

Miksi lasku yllättää

On-demand-laskutuksessa tarpeettomasti luettu data kasvattaa kyselykustannuksia. Kyselyhistoriasta voi selvittää, mitkä seuraavista tilanteista kuormittavat omaa ympäristöä.

• 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 voi käynnistää raskaita kyselyitä. Välimuisti voi auttaa, mutta uudet suodatin- ja ajanjaksoyhdistelmät voivat vaatia uutta laskentaa.
• Ajastetut kyselyt, jotka laskevat koko historian joka yö uudelleen, vaikka muuttuneita ovat vain viimeiset päivät.
• Streaming-kirjoituksen kustannus riippuu rajapinnasta ja sen ilmaiskäyttörajoista. Eräajolataus on usein sopiva, jos päivänsisäistä päivitystä ei tarvita; GA4:n streaming-viennillä on oma kirjoitusmaksunsa.
• Monimutkaiset näkymäketjut voivat toistaa raskasta laskentaa. Tarkistakaa suoritusplaani ja välimuistin vaikutus ennen päätöstä tulosten tallentamisesta tauluiksi.

Partitiointi ja klusterointi

Partitiointi jakaa taulun esimerkiksi päivämäärän perusteella osiin. Sopiva kyselyrajaus voi tällöin jättää muut partitiot lukematta. Klusterointi järjestää tietoa valittujen sarakkeiden mukaan ja voi vähentää luettavia lohkoja, kun kysely suodattaa näillä sarakkeilla.

Hyöty riippuu taulun koosta, suodatuksista ja kyselyistä. Seuraavassa esimerkissä käytetään tapahtumapäivään perustuvaa partitiointia; kaikki pienet taulut eivät tarvitse samaa rakennetta.

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 edellyttää kelvollista partitiorajausta taulua käyttävissä kyselyissä. Se auttaa ehkäisemään vahingossa tehtyjä koko historian lukuja, mutta ei yksin rajoita kyselyn hintaa.

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

Kyselyn käsittelemän datamäärän arviointi ennen ajoa

BigQueryn käyttöliittymä ja dry run eli kyselyn koeajo antavat arvion käsiteltävistä tavuista ilman varsinaisen kyselyn suorittamista. Arvio auttaa ennakoimaan on-demand-kustannusta, mutta se ei ole lopullinen hintalupaus. Esimerkiksi klusterointi ja ulkoiset lähteet vaikuttavat arvion tarkkuuteen.

• Googlen ohje BigQuery-kustannusten arviointiin ja rajaamiseen

# 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`
    where session_date >= current_date() - 30'

# Katto yksittäiselle kyselylle: yli menevä epäonnistuu ennen ajoa.
# 10 GiB = 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'

Aseta maximum_bytes_billed kyselyn asetuksiin tai käytetyn asiakastyökalun oletuksiin. Se ei ole BigQueryn projektitason oletuskonfiguraation asetus. Projektin päivittäistä on-demand-kyselykulutusta voi rajata mukautetulla kiintiöllä.

Kustannuskatot ja hälytykset

Kyselykohtaisen rajan lisäksi kannattaa sopia projektien kiintiöistä, kustannusseurannasta ja hälytyksiin reagoinnista.

• Mukautettu kiintiö rajoittaa projektin tai käyttäjän päivittäistä on-demand-kyselyiden datamäärää. Se ei kata tallennusta, tiedonsiirtoa tai kapasiteettilaskutusta eikä ole tarkka euromääräinen kokonaiskatto. Budjettihälytys ei itsessään pysäytä käyttöä.
• 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.

Tallennuksen loogista ja fyysistä laskutusmallia kannattaa verrata oman datan perusteella. Fyysinen malli laskuttaa pakatusta datasta, mutta myös versionhistorian ja fail-safe-säilytyksen kustannukset on huomioitava. Pienempi pakattu koko ei yksin osoita, kumpi malli tulee edullisemmaksi.

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 EU-monialue ja Haminan europe-north1 ovat mahdollisia vaihtoehtoja. EU-monialue ei tarkoita monen alueen vikasietoisuutta: data säilytetään yhdellä Googlen valitsemalla alueella EU:n sisällä. europe-north1 rajaa datasetin sijainnin Suomeen; se ei yksin takaa pienintä kyselyviivettä.

Valinnassa huomioidaan organisaation tietojen sijaintia koskevat vaatimukset, muiden palveluiden yhteensopivuus ja mahdolliset alueiden väliset siirrot. Datasetin sijainti on yksi osa kokonaisuutta; se ei yksin ratkaise kaikkia tiedon käsittelyn vaatimuksia.

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 käyttöoikeudet periytyvät ylemmiltä resurssitasoilta. Laaja projektioikeus voi siksi avata enemmän tietoa kuin raportin käyttäjä tarvitsee. Tarkistakaa sekä suoraan myönnetyt että periytyvät oikeudet.

Rajattu raportointidatasetti on usein selkeä lähtökohta lukuoikeuksille. Raakadatan, mallinnuksen ja raportoinnin erottaminen auttaa antamaan kullekin käyttäjälle ja ajolle vain tarvitut oikeudet.

-- 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');

Valtuutettu näkymä voi tarjota rajatun näkymän ilman oikeutta sen taustatauluun. Rivi- ja sarakekohtainen suojaus sekä erilliset datasetit ovat muita vaihtoehtoja. Valinta riippuu siitä, mitä eri ryhmät saavat nähdä ja miten sääntöjä ylläpidetään.

Suosikaa ajoympäristön palvelutiliä tai tuettua identiteettifederaatiota ladattavien avaintiedostojen sijaan. Esimerkiksi Cloud Runissa ajolle liitetty palvelutili voi tunnistautua ilman sovellukseen tallennettua yksityistä avainta.

Milloin BigQuery ei ole oikea valinta

• Operatiivinen sovellus, jonka pääasiallinen työ on yksittäisten tietueiden haku ja muokkaus, voi tarvita toisenlaisen tietokannan.
• Pieni ja yksinkertainen aineisto voi toimia taulukkolaskennassa, jos käyttöoikeudet, historia ja ylläpito voidaan hoitaa riittävän hyvin.
• Tiukka vasteaikavaatimus voi edellyttää esilaskentaa, välimuistia tai erillistä palvelukerrosta.
• Vertailkaa vaihtoehtoja todellisilla kyselyillä ja käyttömäärillä ennen arkkitehtuuripäätöstä.

Yleisimmät virheet

• Suurten taulujen kyselyt eivät rajaa luettavia sarakkeita tai ajanjaksoja.
• Raportoinnin tarpeeseen nähden raskas laskenta toistetaan turhaan.
• Kustannuksia seurataan ilman nimettyä vastuuta ja reagointitapaa.
• Käyttöoikeuksia annetaan laajemmin kuin tehtävä edellyttää.
• Datasetin sijaintia ja säilytysaikaa ei suunnitella muiden palveluiden kanssa.
• Laskutusmallin valintaa ei arvioida uudelleen käytön muuttuessa.