Materiál k cvičení DBM2 týden #11, sezona 2025/2026

DataWarehouse - BigQuery

Kontext

V tomto cvičení budeme pracovat s daty sbíranými z ladderu multiplayerové hry StarCraft II (API docs). Data budou uložena v Google BigQuery, což je cloudové řešení pro datové sklady a analýzu velkých dat.

Na tomto příkladu si ukážeme několik konceptů datových skladů, práci s externími daty, rozdíly mezi různými typy tabulek a také některé praktické nuance cloudových řešení.

Dataset

Pracujeme s daty v JSON formátu, kdy jeden soubor odpovídá množině až 200 hráčů spadající do skupiny s podobnou úrovní. Ex. ladders-eu-266354.json - pro rychlou orientaci:

※ I přes název favoriteRace, nejde o preferenci, ale o způsob, jak rozlišit, za jakou rasu hráč v daném ladderu hraje. Je možné najít stejné ID hráče v různých ladderech s různými rasami.

V datasetu se nachází 30 dnů, kdy každý den má 100-700 souborů s laddery. Velikost je přibližně 500 MB. Jedná se pouze o 1v1 mód na EU serveru.

V rámci vybraného období (červenec 2025) došlo k resetu sezóny SC2 (20. 7. 2025), což způsobilo úplné přeuspořádání ladderů, reset win/loss statistik a postupné opětovné zařazování hráčů. Díky tomu dataset obsahuje zajímavé stavy a nespojitosti.

Příprava dat

Založení projektu v GCP

Během práce by nám měl stačit BigQuery Sandbox režim, pro pokrytí analytických potřeb. Pro vytvoření datasetu je ale potřeba mít účet s přiřazeným fakturačním profilem (lze použít 3 měsíční trial nabídku s kredity odpovídající 300 USD).

V Google Cloud Platform (GCP) založme nový projekt s názvem dbm2-bigquery bez náležitosti k organizaci.

Následně ho vyberme v horním výběru projektů.

Nahrání dat do bucketu #1

Pokud nemáte nastavený billing, přeskočte sekci a následně použijte sdílený bucket.

V hlavním menu (ikonka ☰) zvolme sekci "Cloud Storage" → "Buckets" a vytvořme nový bucket s názvem sc2-ladder-snapshots, zvolme single region europe-west4 (Netherlands) (nejlevnější) a třídu úložiště Standard. Ostatní nastavení ponechme výchozí a vytvořme bucket.

※ BigQuery musí být později nastaveno na stejnou lokaci (např. europe-west4) jako bucket datasetu.

Po vytvoření bucketu do něj můžeme zkusit nahrát nějaký soubor z datasetu např. drag&drop způsobem v UI.

Reálně nebudeme nahrávat data ručně, ale pomocí gsutil nástroje v příkazové řádce, což je efektivnější.

Protože vám chci dataset vystavit, abyste mohli pracovat v režimu BigQuery Sandbox, nastavím vytvořený bucket jako veřejný pro čtení. V Permissions u bucketu nastavím:

Otestování přístupu k datům

K datům se nyní lze dostat přímým URL odkazem například: https://storage.googleapis.com/sc2-ladder-snapshots/ladders-eu-266354.json

Zároveň můžeme zkusit zpracovat soubor v BigQuery Sandboxu a naplnit jím novou tabulku.

V BigQuery Studiu vytvořme nové datasety s názvem ladders_raw a ladders_analytics v regionu europe-west4. Příkaz je schovaný za ikonou 3 teček vedle názvu projektu v levém menu.

Následně zavolejme SQL k vytoření tabulky snapshots_raw, která bude externí a bude číst data přímo z JSON souboru v našem bucketu.


  

CREATE OR REPLACE EXTERNAL TABLE `ladders_raw.snapshots_raw`
OPTIONS (
  format = 'JSON',
  uris = ['gs://sc2-ladder-snapshots/ladders-eu-266354.json']
);

※ Externí tabulka nemá uložená data v BigQuery, ale čte je přímo z externího zdroje (v našem případě Cloud Storage bucket). Jde v podstatě o bronze layer tabulku, která je rychlá na vytvoření a neúčtuje se za úložiště, ale může být pomalejší při dotazování a má omezené možnosti optimalizace.

Pravděpodobně dostaneme error týkající se toho, že JSON soubor nemohl být správně parsován. Je to proto, že soubor je pretty-printed JSON, ale BigQuery očekává JSON Lines formát (každý řádek je samostatný JSON objekt).

Konverze dat do JSON Lines

Pro konverzi dat do JSON Lines formátu můžeme použít jednoduchý Python skript. Zároveň s ním zploštím adresářovou strukturu a použiji názvy složek jako prefix k názvům souborů.


  

from pathlib import Path
import json

src_root = Path(r"C:\temp\bigquery\raw_snapshots") # TODO: adjust path
dst_root = Path(r"C:\temp\bigquery\raw_snapshots_nd_flat") # TODO

dst_root.mkdir(parents=True, exist_ok=True)

for path in src_root.rglob("*.json"):
    rel_parts = path.relative_to(src_root).parts
    folder_name = rel_parts[0]
    orig_filename = rel_parts[-1]

    # Prefixed filename: 20250720_ladders-eu-266354.json
    new_filename = f"{folder_name}_{orig_filename}"

    out_path = dst_root / new_filename

    # Read pretty JSON → load to Python object
    with path.open("r", encoding="utf-8") as f:
        obj = json.load(f)

    # Write NDJSON (one JSON object per line)
    with out_path.open("w", encoding="utf-8") as f:
        json.dump(obj, f, separators=(",", ":"))
        f.write("\n")

Po zpracování nahraji nově vytvořené soubory do bucketu sc2-ladder-snapshots a složky raw pomocí gsutil (instalace gsutil):


  

gcloud auth login
gcloud config set project dbm2-bigquery

gsutil -m cp -r raw_snapshots_nd_flat/ gs://sc2-ladder-snapshots/raw/

Zpracování JSON dat v BigQuery

Nyní by již pokus o načtení dat do externí tabulky měl proběhnout úspěšně. Zároveň rozšíříme rozsah načítaných souborů na všechny v raw/ složce.


  

CREATE OR REPLACE EXTERNAL TABLE `ladders_raw.snapshots_raw`
OPTIONS (
  format = 'JSON',
  uris   = ['gs://sc2-ladder-snapshots/raw/*.json']
);

Jako sanity check nebo kontrolu lze použít například tento dotaz pro počet souborů a časové rozmezí snapshotů:


  

SELECT
  COUNT(*) AS files_rows,
  MIN(dateTimeCrawled) AS min_snapshot,
  MAX(dateTimeCrawled) AS max_snapshot
FROM `ladders_raw.snapshots_raw`;

Všimněte si, že v Job information je uvedeno, že dotaz zpracoval 300 MB dat, což odpovídá velikosti našeho datasetu. Zároveň je vhodné mít na paměti limitace BigQuery Sandboxu, které zahrnují maximálně 1 TB zpracovaných dat za měsíc a 10 GB aktivních dat v tabulkách.

Případně je možné vyzkoušet, že je možné data z JSON struktury zpracovat pomocí UNNEST a získat seznam hráčů:


  

SELECT
  dateTimeCrawled,
  r.region,
  ladderId,
  lt.mmr AS player_mmr,
  tm.id AS player_id,
  tm.displayName AS display_name
FROM `ladders_raw.snapshots_raw` r,
UNNEST(ladderTeams) AS lt,
UNNEST(lt.teamMembers) AS tm
LIMIT 20;

Vytvoření faktové tabulky

Pro analytické účely je vhodné vytvořit stříbrnou (silver) tabulku, která bude obsahovat denní snapshoty hráčů s jejich statistikami a metadaty. Tato tabulka bude partitionovaná podle data snapshotu a clusterovaná podle ID hráče pro lepší výkon dotazů.

Nejprve vytvoříme nepartitionovanou tabulku fact_player_snapshot_unpart (protože partitionované tabulky mi nešlo vytvořit v režimu Sandbox, tak aby byl alespoň nějaký checkpoint):


  

CREATE OR REPLACE TABLE `ladders_analytics.fact_player_snapshot_unpart`
AS
SELECT
    TIMESTAMP(dateTimeCrawled)                  AS snapshot_ts,
    DATE(TIMESTAMP(dateTimeCrawled))            AS snapshot_date,

    r.region,
    ladderId,
    league.season_id                            AS season_id,
    league.queue_id                             AS queue_id,
    league.league_id                            AS league_id,
    league.team_type                            AS team_type,

    lt.mmr                                      AS player_mmr,
    lt.points                                   AS points,
    lt.wins                                     AS wins,
    lt.losses                                   AS losses,
    lt.previousRank                             AS previous_rank,
    lt.joinTimestamp                            AS join_ts,

    tm.id                                       AS player_id,
    tm.realm                                    AS realm,
    tm.displayName                              AS display_name,
    tm.clanTag                                  AS clan_tag,
    tm.favoriteRace                             AS favorite_race
FROM `ladders_raw.snapshots_raw` r,
    UNNEST(ladderTeams)       AS lt,
    UNNEST(lt.teamMembers)    AS tm;

Pro srovnání , vytvoříme nyní finální partitionovanou tabulku fact_player_snapshot:


  

CREATE OR REPLACE TABLE `ladders_analytics.fact_player_snapshot`
PARTITION BY snapshot_date
CLUSTER BY player_id, realm AS
SELECT *
FROM `ladders_analytics.fact_player_snapshot_unpart`;

Tato tabulka reprezentuje silver layer – již strukturovaná data, která jsou optimalizována pro analytické dotazy.

Grain faktové tabulky je jeden záznam na hráče na jeden snapshot, identifikovaný kombinací: (snapshot_ts, ladderId, player_id, realm, favorite_race)

Q1: Vyzkoušejte, jak se mění počet zpracovaných dat při dotazu na partitionovanou vs. nepartitionovanou tabulku, pokud dotazujeme pouze jeden den snapshotů (např. WHERE snapshot_date = DATE '2025-07-20'). Sledujte chování pro SELECT COUNT(*), SELECT * a SELECT player_id, player_mmr.

Jednoduché dotazy

V datech můžeme zkusit najít konkrétního hráče a vývoj jeho MMR během července 2025:


  

# varianta pro raw data
SELECT
  tm.displayName,
  lt.mmr,
  dateCrawled
FROM `ladders_raw.snapshots_raw`,
UNNEST(ladderTeams) AS lt,
UNNEST(lt.teamMembers) AS tm
WHERE tm.displayName = 'lorimbo'
ORDER BY dateCrawled;


  

# varianta pro faktovou tabulku
SELECT
  display_name,
  player_mmr,
  snapshot_date
FROM `ladders_analytics.fact_player_snapshot`
WHERE display_name = 'lorimbo'
ORDER BY snapshot_date;

Jelikož BigQuery je sloupcový databázový systém, při výběru konkrétních sloupců (např. display_name, player_mmr, snapshot_date) se zpracuje méně dat. Zároveň při používání faktové tabulky již není potřeba dělat full-scan přes JSON data.

※ Všimněte si možnosti vizulizovat výsledky do liniového grafu.

Slowly Changing Dimensions (SCD)

V datech se nám přirozeně vyskytuje situace, kdy se mění atributy hráče (např. display_name, clan_tag), ale jeho identifikátor (player_id) zůstává stejný. Změna přichází nepravidelně a většinou pouze pro malý podíl hráčů. Je několik přístupů, jak tento problém řešit:

Pro naše účely můžeme implementovat Type 1 SCD pro atributy display_name a clan_tag. Vytvoříme novou tabulku dim_player_latest s následující strukturou:


  

CREATE OR REPLACE TABLE `ladders_analytics.dim_player_latest` AS
WITH per_snapshot AS (
  SELECT
    player_id,
    realm,   
    display_name,
    clan_tag,
    snapshot_ts,
    ROW_NUMBER() OVER (
      PARTITION BY player_id, realm, snapshot_ts
      ORDER BY ladderId DESC
    ) AS rn
  FROM `ladders_analytics.fact_player_snapshot`
),
ranked AS (
  SELECT
    player_id,
    realm,   
    display_name,
    clan_tag,
    snapshot_ts,
    ROW_NUMBER() OVER (
      PARTITION BY player_id, realm
      ORDER BY snapshot_ts DESC
    ) AS rn
  FROM per_snapshot
  WHERE rn = 1
)
SELECT
  player_id,
  realm,
  display_name,
  clan_tag,
  snapshot_ts AS as_of_ts
FROM ranked
WHERE rn = 1;

Tato tabulka bude obsahovat nejnovější hodnoty atributů pro každého hráče spolu s časovým razítkem, kdy byly tyto hodnoty platné. Vnitřně je řazeno dle ladderId kvůli tomu, že se většinou crawluje postupně a k vyšším ID ladderů se dojde o několik minut později a mohou tedy mít novější hodnoty atributů.

Podobně lze vytvořit tabulku dim_player_history, která bude uchovávat historii změn atributů pro každého hráče.


  

CREATE OR REPLACE TABLE `ladders_analytics.dim_player_identity_scd2` AS
WITH per_snapshot AS (
  SELECT
    player_id,
    realm,
    display_name,
    clan_tag,
    snapshot_ts,
    ROW_NUMBER() OVER (
      PARTITION BY player_id, realm, snapshot_ts
      ORDER BY ladderId DESC
    ) AS rn
  FROM `ladders_analytics.fact_player_snapshot`
),
changes AS (
  SELECT
    player_id,
    realm,
    display_name,
    clan_tag,
    snapshot_ts,
    LAG(display_name) OVER (
      PARTITION BY player_id, realm
      ORDER BY snapshot_ts
    ) AS prev_name,
    LAG(clan_tag) OVER (
      PARTITION BY player_id, realm
      ORDER BY snapshot_ts
    ) AS prev_clan
  FROM per_snapshot
  WHERE rn = 1
),
versioned AS (
  SELECT
    player_id,
    realm,
    display_name,
    clan_tag,
    snapshot_ts AS valid_from,
    LEAD(snapshot_ts) OVER (
      PARTITION BY player_id, realm
      ORDER BY snapshot_ts
    ) AS next_from
  FROM changes
  WHERE prev_name IS NULL
     OR prev_name != display_name
     OR IFNULL(prev_clan, '') != IFNULL(clan_tag, '')
)
SELECT
    player_id,
    realm,
    display_name,
    clan_tag,
    valid_from,
    COALESCE(
            TIMESTAMP_SUB(next_from, INTERVAL 1 SECOND),
            TIMESTAMP '9999-12-31 23:59:59'
    ) AS valid_to
FROM versioned;

Pro ilustraci jak funguje SCD Type 2, můžeme zkusit najít hráče, kteří změnili své jméno i klan během sledovaného intervalu:


  

SELECT * FROM `ladders_analytics.dim_player_identity_scd2` 
where player_id = 8462348;

Případně použití při spojení s faktovou tabulkou:


  

SELECT
  f.snapshot_date,
  f.player_id,
  f.realm,
  f.player_mmr,
  d.display_name,
  d.clan_tag
FROM `ladders_analytics.fact_player_snapshot` AS f
LEFT JOIN `ladders_analytics.dim_player_identity_scd2` AS d
  ON  f.player_id = d.player_id
  AND f.realm     = d.realm                 
  AND f.snapshot_ts BETWEEN d.valid_from AND d.valid_to
WHERE f.player_id = 8462348
ORDER BY f.snapshot_ts;

Analytiky - churn

Z našich dat se můžeme podívat na situaci kolem ladder resetu a jaký to má vliv na hráčskou aktivitu.

Q2 Vypište počty hráčů umístěných na žebříčku v jednotlivých dnech (unikátní dvojice player_id + realm)

Předešlé nicméně nic neříká o tom, kolik hráčů aktivně hrálo - jsou tam započítáni i hráči, kteří hráli na začátku sezony a poté stagnovali. Abychom dostali skutečnou denní aktivitu, spočeteme počet hráčů, kteří v daný den odehráli alespoň jednu hru (tj. mají wins + losses větší jak v předchozím snapshot).


  

WITH daily AS (
  SELECT
    snapshot_date,
    player_id,
    realm,
    MAX(season_id) AS season_id,
    SUM(wins) AS wins,
    SUM(losses) AS losses,
    SUM(wins + losses) AS wl_total
  FROM `ladders_analytics.fact_player_snapshot`
  GROUP BY snapshot_date, player_id, realm
),
diffs AS (
  SELECT
    d.*,
    LAG(wl_total)  OVER (PARTITION BY player_id, realm ORDER BY snapshot_date) AS prev_wl,
    LAG(season_id) OVER (PARTITION BY player_id, realm ORDER BY snapshot_date) AS prev_season
  FROM daily d
),
activity AS (
  SELECT
    *,
    -- season reset if no previous day OR season changed
    CASE
      WHEN prev_season != season_id THEN TRUE
      ELSE FALSE
    END AS is_season_reset,

    -- played at least one game since previous snapshot
    CASE
      WHEN prev_wl IS NULL      THEN FALSE
      WHEN wl_total > prev_wl   THEN TRUE
      ELSE FALSE
    END AS is_diff_positive
  FROM diffs
),
logic AS (
  SELECT
    *,
    CASE
      WHEN is_season_reset THEN TRUE
      WHEN is_diff_positive THEN TRUE
      ELSE FALSE
    END AS is_active
  FROM activity
)

SELECT
  snapshot_date,
  COUNT(*) AS active_players
FROM logic
WHERE is_active = TRUE
GROUP BY snapshot_date
ORDER BY snapshot_date;

Q3: Jak výsledek interpretujete? Co v grafu pozorujete?

Dále můžeme sledovat, kolik neaktivních hráčů (od začátku sledování) se vrátilo k hraní po resetu sezóny a kolik her odehráli do konce sledování.


  

WITH daily AS (
  SELECT
    snapshot_date,
    player_id,
    realm,
    SUM(wins + losses) AS wl_total
  FROM `ladders_analytics.fact_player_snapshot`
  GROUP BY snapshot_date, player_id, realm
),
diffs AS (
  SELECT
    d.*,
    LAG(wl_total) OVER (
      PARTITION BY player_id, realm
      ORDER BY snapshot_date
    ) AS prev_wl
  FROM daily d
),

pre_inactive AS (
  SELECT
    player_id,
    realm
  FROM diffs
  WHERE snapshot_date < DATE '2025-07-20'
  GROUP BY player_id, realm
  HAVING MAX(CASE WHEN wl_total > prev_wl THEN 1 ELSE 0 END) = 0
),

post_active AS (
  SELECT
    player_id,
    realm,
    MAX(diffs.wl_total) AS games
  FROM diffs
  WHERE snapshot_date >= DATE '2025-07-20'
    AND wl_total > IFNULL(prev_wl, -1)
  GROUP BY player_id, realm
)

SELECT
  p.player_id,
  p.realm,
  q.games
FROM pre_inactive p
JOIN post_active q
  ON p.player_id = q.player_id
 AND p.realm     = q.realm;

Tato metrika odpovídá Customer Re-Activation KPI (Returning Users).

Závěr

V rámci cvičení jsme se dotkli konceptů bronze, silver a gold layer tabulek. V našem případě jsou:

V praxi by docházelo k pravidelnému přicházení nových snapshotů, které by musely být zpracovány. V bronzové vrstvě by došlo pouze k nahrání JSON soborů, ve stříbrné vrstvě by se přidávaly jen řádky za aktuální den (díky partitioningu by mělo být efektivní), u zlaté by se vhodně inkrementovaly vypočtené míry, aby nebylo nutné přepočítávat vše od začátku.