visualize.py

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import configparser
import os
import logging
from sqlalchemy import create_engine, text

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('pipeline.log'),
        logging.StreamHandler()
    ]
)

# Read the config file
config = configparser.ConfigParser()
config.read(os.path.join(os.path.dirname(__file__), 'config.ini'))
snowflake_config = config['SNOWFLAKE']

def get_snowflake_connection():
    """Create and return a Snowflake SQLAlchemy engine"""
    try:
        account = snowflake_config['account'].strip('"\'')
        engine = create_engine(
            f"snowflake://{snowflake_config['user']}:{snowflake_config['password']}@{account}/"
            f"{snowflake_config['database']}/{snowflake_config['schema']}?"
            f"warehouse={snowflake_config['warehouse']}&role={snowflake_config['role']}"
        )
        logging.info("Snowflake engine created successfully")
        return engine
    except Exception as e:
        logging.error(f"Failed to create Snowflake engine: {str(e)}")
        raise

def visualize_insights():
    """Generate visualizations from the Gold and Silver layers"""
    engine = None
    try:
        engine = get_snowflake_connection()
        logging.info("Starting visualization generation")

        os.makedirs("example", exist_ok=True)

        # Fetch Gold data
        gold_query = text("""
            SELECT 
                GENDER,
                DEPRESSION_RATE,
                SUICIDAL_THOUGHTS_RATE,
                AVG_CGPA,
                AVG_ACADEMIC_PRESSURE
            FROM GOLD_STUDENT_INSIGHTS
        """)
        with engine.connect() as conn:
            df = pd.read_sql(gold_query, conn)
        df.columns = df.columns.str.lower()

        logging.info(f"Retrieved {len(df)} records from GOLD_STUDENT_INSIGHTS")
        print("Gold DataFrame columns:", df.columns.tolist())
        print("Gold DataFrame head:\n", df.head())

        # Plot 1: Depression Rate by Gender
        plt.figure(figsize=(10, 6))
        sns.barplot(data=df, x="gender", y="depression_rate", palette="viridis")
        plt.title("Depression Rate by Gender")
        plt.ylabel("Depression Rate")
        plt.xlabel("Gender")
        plt.savefig("example/depression_rate.png", bbox_inches='tight', dpi=300)
        plt.close()
        logging.info("Generated depression_rate.png")

        # Fetch Silver data
        silver_query = text("""
            SELECT 
                CGPA, 
                ACADEMIC_PRESSURE, 
                GENDER,
                AGE as STUDENT_AGE
            FROM SILVER_STUDENT_DATA
            LIMIT 1000
        """)
        with engine.connect() as conn:
            silver_df = pd.read_sql(silver_query, conn)
        silver_df.columns = silver_df.columns.str.lower()

        logging.info(f"Retrieved {len(silver_df)} records from SILVER_STUDENT_DATA")
        print("Silver DataFrame columns:", silver_df.columns.tolist())
        print("Silver DataFrame head:\n", silver_df.head())

        # Plot 2: CGPA vs Academic Pressure by Gender
        plt.figure(figsize=(10, 6))
        sns.scatterplot(
            data=silver_df, 
            x="cgpa", 
            y="academic_pressure", 
            hue="gender",
            alpha=0.6
        )
        plt.title("CGPA vs Academic Pressure by Gender")
        plt.savefig("example/cgpa_pressure.png", bbox_inches='tight', dpi=300)
        plt.close()
        logging.info("Generated cgpa_pressure.png")

        # Plot 3: Age Distribution
        if 'student_age' in silver_df.columns:
            plt.figure(figsize=(10, 6))
            sns.histplot(data=silver_df, x="student_age", kde=True, bins=20)
            plt.title("Age Distribution of Students")
            plt.savefig("example/age_distribution.png", bbox_inches='tight', dpi=300)
            plt.close()
            logging.info("Generated age_distribution.png")
        else:
            logging.warning("student_age column not found in silver data")

        logging.info("Visualizations generated successfully in example/ folder.")
    except Exception as e:
        logging.error(f"Visualization generation failed: {str(e)}")
        raise
    finally:
        if engine:
            engine.dispose()
            logging.info("Snowflake engine disposed")

if __name__ == "__main__":
    visualize_insights()