แผนการเรียนรู้
/
AMMS 302 | Week 12

Finding Insights from Big Data (I)

Google BigQuery · DuckDB · PySynthea synthetic cohort

Columnar DW · serverless SQL · Python-native patient generation · offline analytics

🎯 เป้าหมาย (CLO2/CLO4)

  • อธิบายสถาปัตยกรรม BigQuery + เทียบกับ local DuckDB
  • generate synthetic patients ด้วย PySynthea (seed reproducible)
  • query prevalence insights ด้วย SQL บนไฟล์โดยตรง
Lab: 🧪 week12-bigdata-part1.ipynb — install: uv add tietai-synthea duckdb

สารบัญ

01 · Google BigQuery — serverless DW

Your browser (console)
↓ HTTPS SQL
Query engine (Dremel)
↓ columnar scan, parallel 1000s slots
Storage (Capacitor) — petabytes
compute ⬄ storage แยกกัน scale อิสระ
  • Serverless: ไม่ต้องจัดการ server/index
  • Columnar: SELECT 2 cols จาก 100 = อ่านเฉพาะ 2
  • Pricing: จ่ายตาม bytes scanned (~$6.25/TB) — sandbox ฟรี 10GB storage/1TB query/เดือน
  • SQL 2011 standard — ใช้ skill เดิมได้หมด
💡 wk10 tie-in: upload synthetic only — raw PHI ผิด policy!
📚 BigQuery intro · Free sandbox

02 · Console tour — 5 ขั้นเริ่มงาน

1. Sandbox
cloud.google.com/bigquery/docs/sandbox — login Google ไม่ต้องบัตร
2. Dataset
console → ⋮ next to project → Create dataset → amms302 (region asia-southeast1)
3. Table
Create table → Upload `synthetic_cohort.csv` → auto-detect schema
4. Query
Compose new → SQL editor → Run (Ctrl+Enter)
5. Dry-run ✓
validator แสดง bytes before run — เช็ค cost เสมอ
-- BigQuery SQL — เหมือนที่เรียนมาทั้งหมด
SELECT gender, COUNT(*) AS n, AVG(hba1c) AS avg_a1c
FROM `your-project.amms302.synthetic_cohort`
GROUP BY gender ORDER BY gender;

03 · DuckDB — BigQuery จิ๋วแบบออฟไลน์

“SQLite for analytics” — columnar vectorized engine · query CSV/Parquet in-place ไม่ต้อง import

# uv add duckdb
import duckdb
con = duckdb.connect()   # in-memory

con.execute("""
SELECT gender, COUNT(*) n,
       ROUND(AVG(hba1c),2) avg_a1c
FROM read_csv_auto('synthetic_cohort.csv')
GROUP BY gender""").df()

.df() → pandas DataFrame ทันที

ประเด็นBigQueryDuckDB
ScalePBGB–TB (single node)
Setupaccount+projectuv add จบ!
Costpay/queryฟรี local
Offline lab✗✓ เราใช้ตัวนี้
📚 DuckDB Python guide · docs home

04 · PySynthea — generate cohort

uv add tietai-synthea → import name = synthea — Python-native, ไม่ต้อง Java/JVM

from synthea import Generator,
                    GeneratorOptions

opts = GeneratorOptions()
opts.population_size = 50
opts.seed = 42        # reproducible!
gen = Generator(opts)
gen.run()
print(gen.stats)
# {'total_generated': 50, ...}

CLI: uv run synthea -p 50 --state California

Output & why seed matters

  • FHIR R4 bundles + pandas-friendly exports
  • disease modules ครบ (DM, HT, AF…) bundled ใน package
  • seed เดิม = dataset เดิม → homework ตรวจซ้ำได้
🔗 Quick Start · PyPI · Paper · upstream Java: synthea™

05 · Load & Query — in-place analytics

-- CASE + GROUP BY บนไฟล์ตรง ๆ
SELECT
  CASE WHEN age<40 THEN '18-39'
       WHEN age<60 THEN '40-59'
       ELSE '60+' END AS band,
  COUNT(*) n,
  SUM(dm_dx::INT) dm_n
FROM read_csv_auto('synthetic_cohort.csv')
GROUP BY band ORDER BY band;

syntax เดียวกับ wk6–7 — แค่ FROM เปลี่ยนเป็น reader

Pipeline diagram

synthetic_cohort.csv (disk)
↓ read_csv_auto (scan เฉพาะ col ที่ใช้)
vectorized filter/agg
↓ .df()
pandas → matplotlib
RAM ใช้เท่าที่จำเป็น — ไฟล์ 10GB ก็ query ได้บนเครื่องธรรมดา (wk14 เจาะต่อ)

06 · Insight: DM prevalence by decade

SELECT age/10*10 AS decade,
 COUNT(*) n,
 ROUND(AVG(sbp),0) avg_sbp,
 ROUND(100.0*SUM(CASE
   WHEN hba1c>7 THEN 1 ELSE 0 END)
   /COUNT(*),1) pct_uncontrolled
FROM read_csv_auto('synthetic_cohort.csv')
GROUP BY decade ORDER BY decade;

+ matplotlib bar chart ใน ipynb §4

อ่านผลแบบ epidemiologist

  • %uncontrolled ควรเพิ่มตาม decade — sanity check ว่า generator สมจริง
  • avg_sbp trend ขึ้น = comorbidity pattern
  • n ต่อ cell ≥ 30 ถึงเชื่อได้ (small-N caution)
จำ: insight จาก synthetic data ใช้ฝึก method ไม่ใช่สรุปสู่ประชากรจริง

07 · BigQuery cost & performance tips

Dry-run first
Validator บอก bytes ที่จะ scan ก่อน Run — ประมาณ cost ทุกครั้ง
SELECT columns
columnar = อย่า SELECT * — 100 cols→2 cols = ลด bill ~50×
Partition/Cluster
แบ่งตาม date → WHERE date กรอง physical blocks
DuckDB equivalents: LIMIT + projection pushdown อัตโนมัติ · Parquet (wk14) = partition-like pruning
📚 Best practices: controlling costs

08 · Lab สัปดาห์ที่ 12 🧪

  1. §1 Generate 50 patients seed=42 (หรือ fallback)
  2. §3 DuckDB: group-by-gender + pct_dm
  3. §3 CASE age-bands 3 groups
  4. §4 prevalence by decade + matplotlib chart
ipynb §5: BigQuery sandbox walkthrough (optional, cloud)

📝 Homework 12

  • Generate 500 คน — seed = เลขรหัสนักศึกษา
  • Top-3 decades by DM prevalence + bar chart
  • ส่ง .ipynb + csv — อาจารย์รันซ้ำด้วย seed เดิมต้องได้ผลตรง

09 · แหล่งอ้างอิงทางการ

☁️ Google BigQuery

🦆 DuckDB

🧬 PySynthea

📬 ติดต่อ

ดร.วิชิต สมบัติ — wichit.s@ubu.ac.th

Next: Week 13 — OMOP Insights · notebook