Spaces:
Sleeping
Sleeping
File size: 4,857 Bytes
1d0150f 85be28c 1d0150f 85be28c 1d0150f 85be28c 1d0150f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 | import os
from sqlalchemy import create_engine, text
from dotenv import load_dotenv, find_dotenv
load_dotenv(find_dotenv())
DATABASE_URL = os.getenv("DATABASE_URL")
if not DATABASE_URL:
raise RuntimeError("DATABASE_URL manquant dans .env")
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
# DDL pour créer les schemas et tables de la base de données
DDL = """
-- SCHEMAS
CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS clean;
CREATE SCHEMA IF NOT EXISTS app;
-- RAW TABLES
CREATE TABLE IF NOT EXISTS raw.employees (
id BIGSERIAL PRIMARY KEY,
employee_external_id INTEGER NOT NULL UNIQUE,
age INTEGER,
genre VARCHAR(20),
statut_marital VARCHAR(50),
ayant_enfants VARCHAR(10),
niveau_education INTEGER,
domaine_etude VARCHAR(120),
departement VARCHAR(120),
poste VARCHAR(120),
distance_domicile_travail INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_raw_employees_employee_external_id
ON raw.employees(employee_external_id);
CREATE TABLE IF NOT EXISTS raw.employee_snapshots (
id BIGSERIAL PRIMARY KEY,
employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,
nombre_experiences_precedentes INTEGER,
nombre_heures_travaillees INTEGER,
annee_experience_totale INTEGER,
annees_dans_l_entreprise INTEGER,
annees_dans_le_poste_actuel INTEGER,
annees_sous_responsable_actuel INTEGER,
niveau_hierarchique_poste INTEGER,
revenu_mensuel INTEGER,
augmentation_salaire_precedente VARCHAR(50),
heures_supplementaires VARCHAR(50),
nombre_participation_pee INTEGER,
nb_formations_suivies INTEGER,
nombre_employee_sous_responsabilite INTEGER,
frequence_deplacement VARCHAR(50),
annees_depuis_la_derniere_promotion INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_raw_snapshots_employee_id_created_at
ON raw.employee_snapshots(employee_id, created_at);
CREATE TABLE IF NOT EXISTS raw.surveys (
id BIGSERIAL PRIMARY KEY,
employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,
code_sondage INTEGER,
eval_number VARCHAR(50),
note_evaluation_precedente INTEGER,
note_evaluation_actuelle INTEGER,
satisfaction_employee_environnement INTEGER,
satisfaction_employee_nature_travail INTEGER,
satisfaction_employee_equipe INTEGER,
satisfaction_employee_equilibre_pro_perso INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_raw_surveys_employee_id_created_at
ON raw.surveys(employee_id, created_at);
CREATE TABLE IF NOT EXISTS raw.ground_truth (
id BIGSERIAL PRIMARY KEY,
employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,
date_event TIMESTAMPTZ NOT NULL DEFAULT now(),
a_quitte_l_entreprise INTEGER NOT NULL CHECK (a_quitte_l_entreprise IN (0,1)),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_ground_truth_employee_id_date_event
ON raw.ground_truth(employee_id, date_event);
-- APP LOGGING TABLES
CREATE TABLE IF NOT EXISTS app.prediction_requests (
id BIGSERIAL PRIMARY KEY,
employee_id BIGINT REFERENCES raw.employees(id) ON DELETE SET NULL,
snapshot_id BIGINT REFERENCES raw.employee_snapshots(id) ON DELETE SET NULL,
survey_id BIGINT REFERENCES raw.surveys(id) ON DELETE SET NULL,
payload_json JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_prediction_requests_created_at
ON app.prediction_requests(created_at);
CREATE TABLE IF NOT EXISTS app.predictions (
id BIGSERIAL PRIMARY KEY,
request_id BIGINT NOT NULL REFERENCES app.prediction_requests(id) ON DELETE CASCADE,
model_version VARCHAR(50) NOT NULL,
predicted_class INTEGER NOT NULL,
predicted_proba DOUBLE PRECISION NOT NULL,
threshold_used DOUBLE PRECISION NOT NULL,
latency_ms INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_predictions_request_id_created_at
ON app.predictions(request_id, created_at);
CREATE INDEX IF NOT EXISTS ix_predictions_created_at
ON app.predictions(created_at);
CREATE INDEX IF NOT EXISTS ix_predictions_model_version
ON app.predictions(model_version);
"""
# # Fonction utilitaire pour exécuter du DDL de manière robuste
# def _exec_ddl(conn, ddl: str) -> None:
# # robuste: exécute statement par statement
# parts = [p.strip() for p in ddl.split(";") if p.strip()]
# for stmt in parts:
# conn.execute(text(stmt))
# Point d'entrée du script
def main():
with engine.begin() as conn:
conn.exec_driver_sql(DDL)
print("Schemas + tables créés / vérifiés (raw / clean / app)")
# with engine.begin() as conn:
# _exec_ddl(conn, DDL)
# print("Schemas + tables créés / vérifiés (raw / clean / app)")
if __name__ == "__main__":
main()
|