Materiál k cvičení DBM1 týdny #6, sezona 2024/2025

Od Dataframů k datovému skladu

🎓 V předchozích dílech jsme:

Nyní máme připravené a propoojoné DataFrame objekty, které můžeme uložit do databáze, resp. datového skladu.

DuckDB a serializace DataFrame

DuckDB je databáze, která žije v jednom souboru na disku a je schopná pracovat s DataFrame objekty z knihovny Pandas. DuckDB je optimalizován pro analýzu dat a je vhodný i pro větší objemy dat. Hlavní nevýhodou je absence concurrent přístupu, tzn. že databázi může v jednu chvíli používat pouze jeden proces.

Hlavním účelem pro nás bude ukládat zpracované DataFrame objekty, aby nebylo nutné provádět zpracování znovu při každém spuštění skriptu.


  

pip install duckdb


  

import duckdb
con = duckdb.connect('postal.db', read_only=False)
con.execute("CREATE TABLE IF NOT EXISTS cases AS SELECT * FROM df_primary")
con.close()

Pro použití v Pythonu je třeba mít nainstalovaný modul duckdb. S databází je nejprve nutné vytvořit spojení a následně lze provádět SQL dotazy. Výše je příklad vytvoření tabulky merged z DataFrame objektu df_final.

Po ukončení práce s databází je nutné spojení uzavřít, aby bylo možné k databázi přistupovat z jiného procesu.

Q1 Ověřte pomocí vašeho oblíbeného SQL klienta (např. DBeaver), že se tabulka cases vytvořila a obsahuje data z DataFrame objektu df_primary.

Modelování datového skladu - fakta a dimenze

V datovém skladu se rozlišují dvě základní typy tabulek:

Propojení faktu a dimenzí je zajištěno pomocí cizích klíčů a tvoří tzv. hvězdicové schéma.

Vytvoření jednoduchého datového skladu

Vytvořme jednoduchý sklad zpracovávající případy za využití dimenze adresní hierarchie. Faktem pro nás bude df_primary, hierarchickou dimenzí bude df_hierarchy. Dimenzi popisující vztah PSČ a obce včetně proporčního členění vytvoříme zvlášť.


  

df_obce = preprocess_zip_codes()

psc_totals = df_obce.groupby('PSČ')['total_count'].sum().reset_index(name='psc_total')

df_ratio = df_obce.merge(psc_totals, on='PSČ')
df_ratio['ratio'] = df_ratio['total_count'] / df_ratio['psc_total']

dim_psc_to_obec = df_ratio[['PSČ', 'Kód obce', 'ratio']].sort_values(by=['PSČ', 'ratio'], ascending=[True, False])

Dimenzi dim_psc_to_obec vytvoříme postupným spočtením celkového počtu adres pro každé PSČ, následně spočteme proporční členění adres v obcích a výsledný DataFrame uložíme.


  

con.execute("CREATE TABLE IF NOT EXISTS fact AS SELECT * FROM df_primary")
con.execute("CREATE TABLE IF NOT EXISTS dim_psc_to_obec AS SELECT * FROM dim_psc_to_obec")
con.execute("CREATE TABLE IF NOT EXISTS dim_hierarchy AS SELECT * FROM df_hierarchy")

Vytvoříme tabulky pro fakta a dimenze v databázi DuckDB.

※ V příkazu je použito IF NOT EXISTS, aby se tabulky vytvořily pouze v případě, že neexistují, tedy při prvním běhu.

Ověření výpočtem v SQL

Nyní můžeme pomocí SQL zkusit některé dotazy nad vytvořenými tabulkami.

Příklad 1: Výpis všech případů s členěním v obcích


  

SELECT * 
FROM fact f JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ 
;

Zde vyjadřujeme pro každý případ podílové členění vůči obcím.

V Pythonu lze výsledky dotazu získat pomocí metody fetchall(). Výsledkem bude seznam řádků, kde každý řádek je reprezentován jako n-tice.


  

con = duckdb.connect('postal.db', read_only=True)
result = con.execute("SELECT * FROM fact f JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ").fetchall()
con.close()
print(result)

Pokud bychom chtěli výsledek konvertovat na DataFrame, můžeme využít metodu fetchdf().


  

con = duckdb.connect('postal.db', read_only=True)
result = con.execute("SELECT * FROM fact f JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ").fetchdf()
con.close()
print(result)

Analogický dotaz lze sestavit přes Pandas DataFrame objekty.


  

df_result = pd.merge(df_primary, dim_psc_to_obec, on='PSČ')
print(df_result)

Příklad 2: Souhrnné počty přes okresy


  

SELECT OKRES_KOD, SUM(ratio)  
FROM fact f 
    JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ 
    JOIN dim_hierarchy h ON pto."Kód obce" = h.OBEC_KOD
GROUP BY OKRES_KOD
;

Výsledkem dotazu bude souhrnné členění podle okresů a informace o součtu případů. SQL lze zavolat i z Pythonu.


  

con = duckdb.connect('postal.db', read_only=True)
result = con.execute("SELECT OKRES_KOD, SUM(ratio) FROM fact f JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ JOIN dim_hierarchy h ON pto.\"Kód obce\" = h.OBEC_KOD GROUP BY OKRES_KOD").fetchdf()
con.close()
print(result)

Pomocí Pandas operací by dotaz mohl být sestaven následovně:


  

df_result = pd.merge(df_primary, dim_psc_to_obec, on='PSČ')
df_result = pd.merge(df_result, df_hierarchy, left_on='Kód obce', right_on='OBEC_KOD')
df_result = df_result.groupby('OKRES_KOD')['ratio'].sum().reset_index(name='sum_ratio')
print(df_result)

Příklad 3: Přidání filtrace dle dimenze pohlaví

Ve skladu technicky nemáme zpracovanou dimenzní tabulku pro pohlaví. Nicméně můžeme využít filtrace dle přirozeného cizího klíče Pohlaví v tabulce df_primary.


  

SELECT OKRES_KOD, SUM(ratio)  
FROM fact f 
    JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ 
    JOIN dim_hierarchy h ON pto."Kód obce" = h.OBEC_KOD
WHERE f.Pohlaví = 'M'
GROUP BY OKRES_KOD
;

resp. v Pythonu:


  

con = duckdb.connect('postal.db', read_only=True)
result = con.execute("SELECT OKRES_KOD, SUM(ratio) FROM fact f JOIN dim_psc_to_obec pto ON f.PSČ = pto.PSČ JOIN dim_hierarchy h ON pto.\"Kód obce\" = h.OBEC_KOD WHERE f.Pohlaví = 'M' GROUP BY OKRES_KOD").fetchdf()
con.close()
print(result)

Analogií v Pandas operacích by bylo:


  

df_result = pd.merge(df_primary, dim_psc_to_obec, on='PSČ')
df_result = pd.merge(df_result, df_hierarchy, left_on='Kód obce', right_on='OBEC_KOD')
df_result = df_result[df_result['Pohlaví'] == 'M']
df_result = df_result.groupby('OKRES_KOD')['ratio'].sum().reset_index(name='sum_ratio')
print(df_result)

Q2 Jaké jsou výhody a nevýhody použití umělých (surrogate) klíčů vs přirozených klíčů v dimenzích datového skladu?

Q3 Vyzkoušejte sestavit dotaz pro výpočet počtu proporčních případů v obcích okresu Plzeň-sever. Srovnejte SQL a Pandas variantu.

Závěr

V klasických relačních databázích jsou často používáná integritní omezování, která zajišťují konzistenci dat. V případě DuckDB ale nejsou slova jako FOREIGN KEY, PRIMARY KEY a podobně podporována a je tedy na uživateli, aby dbal na konzistenci dat. I přesto dává smysl, aby při vytváření modely byla tato slova použita jako dokumentace a pro lepší porozumění datového skladu.

Q4 Jak by vypadala dimenzní tabulka pro pohlaví/gender? Jaké atributy by obsahovala? Jak byste ji vytvořili z tabulky df_primary?