docs / data / mysql
MySQL Database (pidb)
Static reference tables from the school’s MySQL database. Contains building metadata, room details, sensor mappings, device inventories, and energy profiles. Imported into Silver as TEXT columns (type casting done in Gold).
connection
| Host | 10.130.25.152 |
| Port | 3306 |
| Database | pidb |
| User | student (read-only) |
| Access | School network only (VPN required) |
| Frequency | Static — queried on-demand for snapshots |
import method
The script import_mysql_to_silver.py connects to both MySQL (source) and PostgreSQL (target). For each table in the mapping, it reads all rows from MySQL, drops and recreates the Silver table (all TEXT columns for safe import), and inserts all rows. Idempotent: safe to re-run at any time.
All TEXT columns—Every column is imported as TEXT to avoid type conversion errors. Proper types (INTEGER, FLOAT, BOOLEAN) are applied in the Gold layer during dimension population. This is intentional.
imported tables (10)
| MySQL table | Silver table | description | rows |
|---|---|---|---|
| buildings | dim_buildings | Apartment metadata — name, location, building year, type | 2 |
| buildingtype | dim_building_types | Building type lookup (Maison / Appartement) | 2 |
| rooms | dim_rooms | Room details — name, sensor count, orientation, surface (m²) | ~20 |
| sensors | dim_sensors | Sensor-to-room mapping — IP address, room ID, type | ~30 |
| devices | dim_devices | Appliance inventory — name, room, type (fridge, washer, etc.) | ~40 |
| profilereference | ref_energy_profiles | Reference energy consumption kWh/year by appliance type | small |
| profile | ref_power_snapshots | Historical power consumption snapshots over time | ~500 |
| parameters | ref_parameters | Threshold configurations per building (temp, humidity limits) | small |
| parameterstype | ref_parameters_type | Parameter type lookup table | small |
| dierrors | log_sensor_errors | Sensor error logs — null readings, communication failures | varies |
skipped tables (GDPR / irrelevant)
GDPR—Tables containing personal data (names, emails, passwords, phone numbers) are explicitly excluded from the import. This decision is documented and intentional.
| table(s) | reason for skipping |
|---|---|
| users | GDPR — contains names, emails, passwords, phone numbers |
| actions, achievements, badges | Gamification features — not relevant for sensor analytics |
| events, eventsgeneric, eventsignore | App-generated alerts — not raw sensor data |
| categories | Only useful with the events table (skipped) |
| userrelationships | App configuration — no analytical value |
usage in Gold layer
These reference tables enrich the Gold star schema:
dim_buildings → enriches gold.dim_apartment with building_name and building_id
dim_rooms → could enrich gold.dim_room with room_id, orientation, surface area (future)
dim_sensors → provides sensor IP-to-room mapping for device identification
dim_devices → appliance inventory for device-level analytics
ref_energy_profiles → reference kWh/year for energy benchmarking
ref_parameters → threshold configs for anomaly detection
log_sensor_errors → feeds gold.fact_device_health_day (error_count column)