{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Education: Iceberg and Liquid Clustering Demo\n", "\n", "\n", "## Overview\n", "\n", "\n", "This notebook demonstrates the power of **Iceberg and Liquid Clustering** in Oracle AI Data Platform (AIDP) Workbench using an education analytics use case. Liquid clustering automatically optimizes data layout for query performance without requiring manual partitioning or Z-Ordering, now enhanced with Iceberg compatibility through Delta Universal Format.\n", "\n", "### What is Iceberg?\n", "\n", "Apache Iceberg is an open table format for huge analytic datasets that provides:\n", "\n", "- **Schema evolution**: Add, drop, rename, update columns without rewriting data\n", "- **Partition evolution**: Change partitioning without disrupting queries\n", "- **Time travel**: Query historical data snapshots for auditing and rollback\n", "- **ACID transactions**: Reliable concurrent read/write operations\n", "- **Cross-engine compatibility**: Works with Spark, Flink, Presto, Hive, and more\n", "- **Open ecosystem**: Apache 2.0 licensed, community-driven development\n", "\n", "### Delta Universal Format with Iceberg\n", "\n", "Delta Universal Format enables Iceberg compatibility while maintaining Delta's advanced features like liquid clustering. This combination provides:\n", "\n", "- **Best of both worlds**: Delta's performance optimizations with Iceberg's openness\n", "- **Multi-engine access**: Query the same data from different analytics engines\n", "- **Future-proof architecture**: Standards-based approach for long-term data investments\n", "- **Enhanced governance**: Rich metadata and catalog integration\n", "\n", "### What is Liquid Clustering?\n", "\n", "Liquid clustering automatically identifies and groups similar data together based on clustering columns you define. This optimization happens automatically during data ingestion and maintenance operations, providing:\n", "\n", "- **Automatic optimization**: No manual tuning required\n", "- **Improved query performance**: Faster queries on clustered columns\n", "- **Reduced maintenance**: No need for manual repartitioning\n", "- **Adaptive clustering**: Adjusts as data patterns change\n", "\n", "### Use Case: Student Performance Analytics and Learning Management\n", "\n", "We'll analyze student learning data and academic performance metrics. Our clustering strategy will optimize for:\n", "\n", "- **Student-specific queries**: Fast lookups by student ID\n", "- **Time-based analysis**: Efficient filtering by academic period and assessment dates\n", "- **Performance patterns**: Quick aggregation by subject and learning outcomes\n", "\n", "### AIDP Environment Setup\n", "\n", "This notebook leverages the existing Spark session in your AIDP environment." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:22:47.835Z" } }, "outputs": [ { "data": { "text/plain": [ "Education catalog and analytics schema created successfully!\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Create education catalog and analytics schema\n", "\n", "# In AIDP, catalogs provide data isolation and governance\n", "\n", "spark.sql(\"CREATE CATALOG IF NOT EXISTS education\")\n", "\n", "spark.sql(\"CREATE SCHEMA IF NOT EXISTS education.analytics\")\n", "\n", "print(\"Education catalog and analytics schema created successfully!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 2: Create Iceberg-Compatible Delta Table with Liquid Clustering\n", "\n", "### Table Design\n", "\n", "Our `student_assessments_uf` table will store:\n", "\n", "- **student_id**: Unique student identifier\n", "- **assessment_date**: Date of assessment or assignment\n", "- **subject**: Academic subject area\n", "- **score**: Assessment score (0-100)\n", "- **grade_level**: Student grade level\n", "- **completion_time**: Time spent on assessment (minutes)\n", "- **engagement_score**: Student engagement metric (0-100)\n", "\n", "### Clustering Strategy with Iceberg Compatibility\n", "\n", "We'll cluster by `student_id` and `assessment_date` because:\n", "\n", "- **student_id**: Students generate multiple assessments, grouping learning progress together\n", "- **assessment_date**: Time-based queries are critical for academic tracking, semester analysis, and intervention planning\n", "- This combination optimizes for both individual student monitoring and temporal academic performance analysis\n", "- **Iceberg compatibility**: Enables cross-engine queries and advanced schema evolution features" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:23:01.617Z" } }, "outputs": [ { "data": { "text/plain": [ "Delta table with Iceberg compatibility and liquid clustering created successfully!\n", "Universal format enables Iceberg features while CLUSTER BY (student_id, assessment_date) optimizes data layout.\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Create Delta table with Iceberg compatibility via Universal Format and liquid clustering\n", "\n", "# TBLPROPERTIES enables Delta Universal Format for Iceberg compatibility\n", "from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DoubleType, TimestampType, DateType\n", "data_schema = StructType([\n", " StructField(\"student_id\", StringType(), True),\n", " StructField(\"assessment_date\", DateType(), True),\n", " StructField(\"subject\", StringType(), True),\n", " StructField(\"score\", DoubleType(), True),\n", " StructField(\"grade_level\", StringType(), True),\n", " StructField(\"completion_time\", DoubleType(), True),\n", " StructField(\"engagement_score\", IntegerType(), True)\n", "])\n", "spark.sql(\"\"\"\n", "\n", "CREATE TABLE IF NOT EXISTS education.analytics.student_assessments_uf (\n", " student_id STRING,\n", " assessment_date DATE,\n", " subject STRING,\n", " score DECIMAL(5,2),\n", " grade_level STRING,\n", " completion_time DECIMAL(6,2),\n", " engagement_score INT\n", "\n", ")\n", "\n", "USING DELTA\n", "\n", "TBLPROPERTIES('delta.universalFormat.enabledFormats' = 'iceberg')\n", "\n", "CLUSTER BY (student_id, assessment_date)\n", "\n", "\"\"\")\n", "\n", "print(\"Delta table with Iceberg compatibility and liquid clustering created successfully!\")\n", "\n", "print(\"Universal format enables Iceberg features while CLUSTER BY (student_id, assessment_date) optimizes data layout.\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 3: Generate Education Sample Data\n", "\n", "### Data Generation Strategy\n", "\n", "We'll create realistic student assessment data including:\n", "\n", "- **3,000 students** with multiple assessments over time\n", "- **Subjects**: Math, English, Science, History, Art, Physical Education\n", "- **Realistic performance patterns**: Learning curves, subject difficulty variations, engagement factors\n", "- **Grade levels**: K-12 with appropriate academic progression\n", "\n", "### Why This Data Pattern?\n", "\n", "This data simulates real education scenarios where:\n", "\n", "- Student performance varies by subject and time\n", "- Learning progress needs longitudinal tracking\n", "- Intervention strategies require early identification\n", "- Curriculum effectiveness drives teaching improvements\n", "- Standardized testing and reporting require temporal analysis" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:23:04.029Z" } }, "outputs": [ { "data": { "text/plain": [ "Generated 67601 student assessment records\n", "Sample record: {'student_id': 'STU000001', 'assessment_date': datetime.date(2024, 7, 30), 'subject': 'History', 'score': 71.77, 'grade_level': '7th Grade', 'completion_time': 43.3, 'engagement_score': 92}\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Generate sample student assessment data\n", "\n", "# Using fully qualified imports to avoid conflicts\n", "\n", "import random\n", "\n", "from datetime import datetime, timedelta\n", "\n", "\n", "# Define education data constants\n", "\n", "SUBJECTS = ['Math', 'English', 'Science', 'History', 'Art', 'Physical Education']\n", "\n", "GRADE_LEVELS = ['Kindergarten', '1st Grade', '2nd Grade', '3rd Grade', '4th Grade', '5th Grade', \n", " '6th Grade', '7th Grade', '8th Grade', '9th Grade', '10th Grade', '11th Grade', '12th Grade']\n", "\n", "# Base performance parameters by subject and grade level\n", "\n", "PERFORMANCE_PARAMS = {\n", "\n", " 'Math': {'base_score': 75, 'difficulty': 1.2, 'time_factor': 1.5},\n", "\n", " 'English': {'base_score': 78, 'difficulty': 1.0, 'time_factor': 1.2},\n", "\n", " 'Science': {'base_score': 72, 'difficulty': 1.3, 'time_factor': 1.4},\n", "\n", " 'History': {'base_score': 70, 'difficulty': 1.1, 'time_factor': 1.1},\n", "\n", " 'Art': {'base_score': 82, 'difficulty': 0.8, 'time_factor': 0.9},\n", "\n", " 'Physical Education': {'base_score': 85, 'difficulty': 0.7, 'time_factor': 0.8}\n", "\n", "}\n", "\n", "# Grade level adjustments\n", "\n", "GRADE_ADJUSTMENTS = {\n", "\n", " 'Kindergarten': 0.7, '1st Grade': 0.75, '2nd Grade': 0.8, '3rd Grade': 0.82,\n", "\n", " '4th Grade': 0.85, '5th Grade': 0.87, '6th Grade': 0.8, '7th Grade': 0.78,\n", "\n", " '8th Grade': 0.76, '9th Grade': 0.74, '10th Grade': 0.72, '11th Grade': 0.7, '12th Grade': 0.68\n", "\n", "}\n", "\n", "\n", "# Generate student assessment records\n", "\n", "assessment_data = []\n", "\n", "base_date = datetime(2024, 1, 1)\n", "\n", "\n", "# Create 3,000 students with 15-30 assessments each\n", "\n", "for student_num in range(1, 3001):\n", "\n", " student_id = f\"STU{student_num:06d}\"\n", " \n", " # Assign grade level\n", "\n", " grade_level = random.choice(GRADE_LEVELS)\n", "\n", " grade_factor = GRADE_ADJUSTMENTS[grade_level]\n", " \n", " # Each student gets 15-30 assessments over 12 months\n", "\n", " num_assessments = random.randint(15, 30)\n", " \n", " for i in range(num_assessments):\n", "\n", " # Spread assessments over 12 months\n", "\n", " days_offset = random.randint(0, 365)\n", "\n", " assessment_date = base_date + timedelta(days=days_offset)\n", " \n", " # Select subject\n", "\n", " subject = random.choice(SUBJECTS)\n", "\n", " params = PERFORMANCE_PARAMS[subject]\n", " \n", " # Calculate score with variations\n", "\n", " score_variation = random.uniform(0.7, 1.3)\n", "\n", " base_score = params['base_score'] * grade_factor / params['difficulty']\n", "\n", " score = round(min(100, max(0, base_score * score_variation)), 2)\n", " \n", " # Calculate completion time\n", "\n", " time_variation = random.uniform(0.8, 1.5)\n", "\n", " base_time = 45 * params['time_factor'] # 45 minutes base time\n", "\n", " completion_time = round(base_time * time_variation, 2)\n", " \n", " # Engagement score (affects performance)\n", "\n", " engagement_score = random.randint(40, 100)\n", "\n", " # Slightly adjust score based on engagement\n", "\n", " engagement_factor = engagement_score / 100.0\n", "\n", " score = round(min(100, score * (0.8 + 0.4 * engagement_factor)), 2)\n", " \n", " assessment_data.append({\n", "\n", " \"student_id\": student_id,\n", "\n", " \"assessment_date\": assessment_date.date(),\n", "\n", " \"subject\": subject,\n", "\n", " \"score\": float(score),\n", "\n", " \"grade_level\": grade_level,\n", "\n", " \"completion_time\": float(completion_time),\n", "\n", " \"engagement_score\": int(engagement_score)\n", "\n", " })\n", "\n", "\n", "\n", "print(f\"Generated {len(assessment_data)} student assessment records\")\n", "\n", "print(\"Sample record:\", assessment_data[0])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 4: Insert Data Using PySpark\n", "\n", "### Data Insertion Strategy\n", "\n", "We'll use PySpark to:\n", "\n", "1. **Create DataFrame** from our generated data\n", "2. **Insert into Iceberg-compatible table** with liquid clustering\n", "3. **Verify the insertion** with a sample query\n", "\n", "### Why PySpark for Insertion?\n", "\n", "- **Distributed processing**: Handles large datasets efficiently\n", "- **Type safety**: Ensures data integrity\n", "- **Optimization**: Leverages Spark's query optimization\n", "- **Iceberg compatibility**: Enables cross-engine access and advanced features" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:23:13.584Z" } }, "outputs": [ { "data": { "text/plain": [ "DataFrame Schema:\n", "root\n", " |-- student_id: string (nullable = true)\n", " |-- assessment_date: date (nullable = true)\n", " |-- subject: string (nullable = true)\n", " |-- score: double (nullable = true)\n", " |-- grade_level: string (nullable = true)\n", " |-- completion_time: double (nullable = true)\n", " |-- engagement_score: integer (nullable = true)\n", "\n", "\n", "Sample Data:\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------+---------------+------------------+-----+-----------+---------------+----------------+\n", "|student_id|assessment_date| subject|score|grade_level|completion_time|engagement_score|\n", "+----------+---------------+------------------+-----+-----------+---------------+----------------+\n", "| STU000001| 2024-07-30| History|71.77| 7th Grade| 43.3| 92|\n", "| STU000001| 2024-12-19|Physical Education|100.0| 7th Grade| 39.03| 70|\n", "| STU000001| 2024-04-11|Physical Education|95.84| 7th Grade| 35.82| 61|\n", "| STU000001| 2024-07-07| Art|93.64| 7th Grade| 35.18| 71|\n", "| STU000001| 2024-09-26| Art|98.61| 7th Grade| 52.49| 78|\n", "+----------+---------------+------------------+-----+-----------+---------------+----------------+\n", "only showing top 5 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "\n", "Successfully inserted 67601 records into education.analytics.student_assessments_uf\n", "Liquid clustering automatically optimized the data layout during insertion!\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Insert data using PySpark DataFrame operations\n", "\n", "# Using fully qualified function references to avoid conflicts\n", "\n", "\n", "# Create DataFrame from generated data\n", "\n", "df_assessments = spark.createDataFrame(assessment_data, schema=data_schema)\n", "\n", "\n", "# Display schema and sample data\n", "\n", "print(\"DataFrame Schema:\")\n", "\n", "df_assessments.printSchema()\n", "\n", "\n", "\n", "print(\"\\nSample Data:\")\n", "\n", "df_assessments.show(5)\n", "\n", "\n", "# Insert data into Delta table with liquid clustering\n", "\n", "# The CLUSTER BY (student_id, assessment_date) will automatically optimize the data layout\n", "\n", "df_assessments.write.mode(\"overwrite\").insertInto(\"education.analytics.student_assessments_uf\")\n", "\n", "\n", "print(f\"\\nSuccessfully inserted {df_assessments.count()} records into education.analytics.student_assessments_uf\")\n", "\n", "print(\"Liquid clustering automatically optimized the data layout during insertion!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 5: Demonstrate Liquid Clustering Benefits with Iceberg Compatibility\n", "\n", "### Query Performance Analysis\n", "\n", "Now let's see how liquid clustering improves query performance. We'll run queries that benefit from our clustering strategy:\n", "\n", "1. **Student assessment history** (clustered by student_id)\n", "2. **Time-based academic analysis** (clustered by assessment_date)\n", "3. **Combined student + time queries** (optimal for our clustering)\n", "\n", "### Expected Performance Benefits\n", "\n", "With liquid clustering and Iceberg compatibility, these queries should be significantly faster because:\n", "\n", "- **Data locality**: Related records are physically grouped together\n", "- **Reduced I/O**: Less data needs to be read from disk\n", "- **Automatic optimization**: No manual tuning required\n", "- **Cross-engine access**: Same data accessible from multiple analytics engines" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:23:29.300Z" } }, "outputs": [ { "data": { "text/plain": [ "=== Query 1: Student Assessment History ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------+---------------+------------------+------+----------------+\n", "|student_id|assessment_date| subject| score|engagement_score|\n", "+----------+---------------+------------------+------+----------------+\n", "| STU000001| 2024-12-19|Physical Education|100.00| 70|\n", "| STU000001| 2024-12-19| Art| 92.00| 94|\n", "| STU000001| 2024-12-17| Art|100.00| 69|\n", "| STU000001| 2024-11-23| English| 79.97| 87|\n", "| STU000001| 2024-10-26| History| 39.04| 60|\n", "| STU000001| 2024-10-03| Science| 52.32| 53|\n", "| STU000001| 2024-09-30|Physical Education|100.00| 88|\n", "| STU000001| 2024-09-26| Art| 98.61| 78|\n", "| STU000001| 2024-09-20|Physical Education| 91.22| 48|\n", "| STU000001| 2024-09-18| Science| 34.05| 57|\n", "| STU000001| 2024-08-16| Math| 44.37| 55|\n", "| STU000001| 2024-08-11| English| 65.12| 90|\n", "| STU000001| 2024-07-30| History| 71.77| 92|\n", "| STU000001| 2024-07-09|Physical Education|100.00| 55|\n", "| STU000001| 2024-07-07| Art| 93.64| 71|\n", "| STU000001| 2024-06-22| Art| 99.32| 55|\n", "| STU000001| 2024-06-18| English| 61.67| 81|\n", "| STU000001| 2024-06-11| Math| 53.96| 95|\n", "| STU000001| 2024-05-11| Math| 63.21| 74|\n", "| STU000001| 2024-05-11|Physical Education| 86.56| 75|\n", "+----------+---------------+------------------+------+----------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Records found: 23\n", "\n", "=== Query 2: Recent Low Performance Issues ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------------+----------+-------+-----+------------+\n", "|assessment_date|student_id|subject|score| grade_level|\n", "+---------------+----------+-------+-----+------------+\n", "| 2024-10-07| STU000204|Science|25.70| 12th Grade|\n", "| 2024-07-28| STU001510|Science|25.89| 12th Grade|\n", "| 2024-06-16| STU002852|Science|26.00| 12th Grade|\n", "| 2024-10-28| STU000505|Science|26.22| 12th Grade|\n", "| 2024-08-28| STU001359|Science|26.25| 12th Grade|\n", "| 2024-12-26| STU002424|Science|26.50|Kindergarten|\n", "| 2024-09-26| STU001532|Science|26.54|Kindergarten|\n", "| 2024-12-09| STU001924|Science|26.60| 12th Grade|\n", "| 2024-12-29| STU002931|Science|26.67| 12th Grade|\n", "| 2024-12-16| STU000560|Science|26.71| 12th Grade|\n", "| 2024-10-21| STU002389|Science|26.75| 12th Grade|\n", "| 2024-06-23| STU002252|Science|26.79| 12th Grade|\n", "| 2024-08-23| STU001097|Science|26.99| 11th Grade|\n", "| 2024-07-01| STU000467|Science|27.00|Kindergarten|\n", "| 2024-10-02| STU001803|Science|27.08|Kindergarten|\n", "| 2024-11-23| STU001798|Science|27.10| 12th Grade|\n", "| 2024-10-09| STU000071|Science|27.11|Kindergarten|\n", "| 2024-12-06| STU000411|Science|27.27| 12th Grade|\n", "| 2024-11-28| STU002512|Science|27.28| 12th Grade|\n", "| 2024-08-24| STU002892|Science|27.28| 11th Grade|\n", "+---------------+----------+-------+-----+------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Low performance issues found: 19063\n", "\n", "=== Query 3: Student Performance Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------+---------------+------------------+------+----------------+\n", "|student_id|assessment_date| subject| score|engagement_score|\n", "+----------+---------------+------------------+------+----------------+\n", "| STU000001| 2024-04-11|Physical Education| 95.84| 61|\n", "| STU000001| 2024-04-26| History| 45.46| 69|\n", "| STU000001| 2024-05-11| Math| 63.21| 74|\n", "| STU000001| 2024-05-11|Physical Education| 86.56| 75|\n", "| STU000001| 2024-06-11| Math| 53.96| 95|\n", "| STU000001| 2024-06-18| English| 61.67| 81|\n", "| STU000001| 2024-06-22| Art| 99.32| 55|\n", "| STU000001| 2024-07-07| Art| 93.64| 71|\n", "| STU000001| 2024-07-09|Physical Education|100.00| 55|\n", "| STU000001| 2024-07-30| History| 71.77| 92|\n", "| STU000001| 2024-08-11| English| 65.12| 90|\n", "| STU000001| 2024-08-16| Math| 44.37| 55|\n", "| STU000001| 2024-09-18| Science| 34.05| 57|\n", "| STU000001| 2024-09-20|Physical Education| 91.22| 48|\n", "| STU000001| 2024-09-26| Art| 98.61| 78|\n", "| STU000001| 2024-09-30|Physical Education|100.00| 88|\n", "| STU000001| 2024-10-03| Science| 52.32| 53|\n", "| STU000001| 2024-10-26| History| 39.04| 60|\n", "| STU000001| 2024-11-23| English| 79.97| 87|\n", "| STU000001| 2024-12-17| Art|100.00| 69|\n", "+----------+---------------+------------------+------+----------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Performance trend records found: 17028\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Demonstrate liquid clustering benefits with optimized queries\n", "\n", "\n", "# Query 1: Student assessment history - benefits from student_id clustering\n", "\n", "print(\"=== Query 1: Student Assessment History ===\")\n", "\n", "student_history = spark.sql(\"\"\"\n", "\n", "SELECT student_id, assessment_date, subject, score, engagement_score\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "WHERE student_id = 'STU000001'\n", "\n", "ORDER BY assessment_date DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "student_history.show()\n", "\n", "print(f\"Records found: {student_history.count()}\")\n", "\n", "\n", "\n", "# Query 2: Time-based academic performance analysis - benefits from assessment_date clustering\n", "\n", "print(\"\\n=== Query 2: Recent Low Performance Issues ===\")\n", "\n", "low_performance = spark.sql(\"\"\"\n", "\n", "SELECT assessment_date, student_id, subject, score, grade_level\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "WHERE assessment_date >= '2024-06-01' AND score < 60\n", "\n", "ORDER BY score ASC, assessment_date DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "low_performance.show()\n", "\n", "print(f\"Low performance issues found: {low_performance.count()}\")\n", "\n", "\n", "\n", "# Query 3: Combined student + time query - optimal for our clustering strategy\n", "\n", "print(\"\\n=== Query 3: Student Performance Trends ===\")\n", "\n", "performance_trends = spark.sql(\"\"\"\n", "\n", "SELECT student_id, assessment_date, subject, score, engagement_score\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "WHERE student_id LIKE 'STU000%' AND assessment_date >= '2024-04-01'\n", "\n", "ORDER BY student_id, assessment_date\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "performance_trends.show()\n", "\n", "print(f\"Performance trend records found: {performance_trends.count()}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 6: Analyze Clustering Effectiveness and Iceberg Features\n", "\n", "### Understanding the Impact\n", "\n", "Let's examine how liquid clustering with Iceberg compatibility has organized our data and analyze some aggregate statistics to demonstrate the education insights possible with this optimized structure.\n", "\n", "### Iceberg Benefits Demonstrated\n", "\n", "- **Schema evolution**: Can add/drop columns without data rewriting\n", "- **Time travel**: Query historical versions of the data\n", "- **Cross-engine compatibility**: Access from Spark, Presto, etc.\n", "- **ACID transactions**: Reliable concurrent operations\n", "- **Open standard**: Future-proof investment in data infrastructure\n", "\n", "### Key Analytics\n", "\n", "- **Student performance patterns** and learning analytics\n", "- **Subject difficulty analysis** and curriculum effectiveness\n", "- **Grade level progression** and academic growth\n", "- **Engagement correlations** and intervention opportunities" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:23:50.171Z" } }, "outputs": [ { "data": { "text/plain": [ "=== Student Performance Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------+-----------------+---------+--------------+-------------------+-----------+\n", "|student_id|total_assessments|avg_score|avg_engagement|avg_completion_time|grade_level|\n", "+----------+-----------------+---------+--------------+-------------------+-----------+\n", "| STU001605| 15| 84.68| 74.0| 49.80| 5th Grade|\n", "| STU002321| 22| 83.18| 68.0| 49.49| 5th Grade|\n", "| STU002978| 18| 82.89| 72.11| 57.40| 5th Grade|\n", "| STU000347| 28| 82.52| 73.36| 56.68| 5th Grade|\n", "| STU001031| 17| 81.59| 74.18| 53.08| 5th Grade|\n", "| STU002562| 21| 81.31| 74.71| 51.98| 7th Grade|\n", "| STU000484| 20| 81.24| 77.2| 55.09| 5th Grade|\n", "| STU001197| 20| 80.95| 73.5| 53.55| 5th Grade|\n", "| STU002204| 15| 80.51| 75.27| 53.23| 3rd Grade|\n", "| STU000251| 15| 80.08| 71.27| 55.06| 5th Grade|\n", "+----------+-----------------+---------+--------------+-------------------+-----------+\n", "\n", "\n", "=== Subject Performance Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+------------------+-----------------+---------+-------------------+--------------+---------------+\n", "| subject|total_assessments|avg_score|avg_completion_time|avg_engagement|unique_students|\n", "+------------------+-----------------+---------+-------------------+--------------+---------------+\n", "|Physical Education| 11339| 91.88| 41.49| 69.73| 2924|\n", "| Art| 11238| 82.94| 46.67| 69.58| 2944|\n", "| English| 11390| 64.52| 62.03| 69.98| 2919|\n", "| History| 11163| 52.67| 57.06| 69.65| 2934|\n", "| Math| 11274| 51.61| 77.53| 69.78| 2934|\n", "| Science| 11197| 45.84| 72.65| 69.65| 2937|\n", "+------------------+-----------------+---------+-------------------+--------------+---------------+\n", "\n", "\n", "=== Grade Level Performance ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+------------+-----------------+---------+--------------+---------------+\n", "| grade_level|total_assessments|avg_score|avg_engagement|unique_students|\n", "+------------+-----------------+---------+--------------+---------------+\n", "|Kindergarten| 4895| 60.24| 69.58| 218|\n", "| 1st Grade| 5530| 63.42| 69.73| 250|\n", "| 2nd Grade| 5455| 67.29| 69.78| 239|\n", "| 3rd Grade| 5083| 68.41| 69.51| 229|\n", "| 4th Grade| 5148| 70.46| 69.37| 225|\n", "| 5th Grade| 5189| 71.60| 69.79| 232|\n", "| 6th Grade| 5647| 68.01| 69.88| 249|\n", "| 7th Grade| 4989| 66.09| 70.18| 215|\n", "| 8th Grade| 4679| 64.65| 69.94| 209|\n", "| 9th Grade| 5916| 63.16| 69.95| 255|\n", "| 10th Grade| 5083| 61.73| 69.46| 228|\n", "| 11th Grade| 5123| 60.24| 69.51| 229|\n", "| 12th Grade| 4864| 58.60| 69.75| 222|\n", "+------------+-----------------+---------+--------------+---------------+\n", "\n", "\n", "=== Engagement vs Performance Correlation ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------------+----------------+---------+-------------------+\n", "| engagement_level|assessment_count|avg_score|avg_completion_time|\n", "+-----------------+----------------+---------+-------------------+\n", "| High Engagement| 22778| 68.98| 59.60|\n", "|Medium Engagement| 22294| 65.11| 59.54|\n", "| Low Engagement| 22529| 60.77| 59.53|\n", "+-----------------+----------------+---------+-------------------+\n", "\n", "\n", "=== Monthly Academic Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------+-----------------+---------+--------------+---------------+\n", "| month|total_assessments|avg_score|avg_engagement|active_students|\n", "+-------+-----------------+---------+--------------+---------------+\n", "|2024-01| 5735| 64.83| 69.43| 2598|\n", "|2024-02| 5365| 65.03| 69.82| 2491|\n", "|2024-03| 5719| 65.43| 69.53| 2588|\n", "|2024-04| 5598| 64.80| 69.36| 2556|\n", "|2024-05| 5810| 65.03| 69.91| 2596|\n", "|2024-06| 5442| 64.95| 69.74| 2508|\n", "|2024-07| 5665| 65.07| 69.58| 2557|\n", "|2024-08| 5643| 64.88| 69.85| 2565|\n", "|2024-09| 5563| 65.37| 69.53| 2550|\n", "|2024-10| 5751| 64.54| 69.7| 2549|\n", "|2024-11| 5591| 64.58| 70.01| 2545|\n", "|2024-12| 5719| 65.13| 70.29| 2533|\n", "+-------+-----------------+---------+--------------+---------------+\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Analyze clustering effectiveness and education insights\n", "\n", "\n", "# Student performance analysis\n", "\n", "print(\"=== Student Performance Analysis ===\")\n", "\n", "student_performance = spark.sql(\"\"\"\n", "\n", "SELECT student_id, COUNT(*) as total_assessments,\n", "\n", " ROUND(AVG(score), 2) as avg_score,\n", "\n", " ROUND(AVG(engagement_score), 2) as avg_engagement,\n", "\n", " ROUND(AVG(completion_time), 2) as avg_completion_time,\n", "\n", " grade_level\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "GROUP BY student_id, grade_level\n", "\n", "ORDER BY avg_score DESC\n", "\n", "LIMIT 10\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "student_performance.show()\n", "\n", "\n", "# Subject performance analysis\n", "\n", "print(\"\\n=== Subject Performance Analysis ===\")\n", "\n", "subject_analysis = spark.sql(\"\"\"\n", "\n", "SELECT subject, COUNT(*) as total_assessments,\n", "\n", " ROUND(AVG(score), 2) as avg_score,\n", "\n", " ROUND(AVG(completion_time), 2) as avg_completion_time,\n", "\n", " ROUND(AVG(engagement_score), 2) as avg_engagement,\n", "\n", " COUNT(DISTINCT student_id) as unique_students\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "GROUP BY subject\n", "\n", "ORDER BY avg_score DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "subject_analysis.show()\n", "\n", "\n", "# Grade level performance\n", "\n", "print(\"\\n=== Grade Level Performance ===\")\n", "\n", "grade_performance = spark.sql(\"\"\"\n", "\n", "\n", "SELECT \n", " grade_level, \n", " COUNT(*) AS total_assessments,\n", " ROUND(AVG(score), 2) AS avg_score,\n", " ROUND(AVG(engagement_score), 2) AS avg_engagement,\n", " COUNT(DISTINCT student_id) AS unique_students\n", "FROM education.analytics.student_assessments_uf\n", "GROUP BY grade_level\n", "ORDER BY \n", " CASE \n", " WHEN grade_level = 'Kindergarten' THEN 0\n", " ELSE CAST(REGEXP_REPLACE(grade_level, '[^0-9]', '') AS INT)\n", " END;\n", "\"\"\")\n", "\n", "\n", "\n", "grade_performance.show()\n", "\n", "\n", "# Engagement vs performance correlation\n", "\n", "print(\"\\n=== Engagement vs Performance Correlation ===\")\n", "\n", "engagement_correlation = spark.sql(\"\"\"\n", "\n", "SELECT \n", "\n", " CASE \n", "\n", " WHEN engagement_score >= 80 THEN 'High Engagement'\n", "\n", " WHEN engagement_score >= 60 THEN 'Medium Engagement'\n", "\n", " WHEN engagement_score >= 40 THEN 'Low Engagement'\n", "\n", " ELSE 'Very Low Engagement'\n", "\n", " END as engagement_level,\n", "\n", " COUNT(*) as assessment_count,\n", "\n", " ROUND(AVG(score), 2) as avg_score,\n", "\n", " ROUND(AVG(completion_time), 2) as avg_completion_time\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "GROUP BY \n", "\n", " CASE \n", "\n", " WHEN engagement_score >= 80 THEN 'High Engagement'\n", "\n", " WHEN engagement_score >= 60 THEN 'Medium Engagement'\n", "\n", " WHEN engagement_score >= 40 THEN 'Low Engagement'\n", "\n", " ELSE 'Very Low Engagement'\n", "\n", " END\n", "\n", "ORDER BY avg_score DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "engagement_correlation.show()\n", "\n", "\n", "# Monthly academic trends\n", "\n", "print(\"\\n=== Monthly Academic Trends ===\")\n", "\n", "monthly_trends = spark.sql(\"\"\"\n", "\n", "SELECT DATE_FORMAT(assessment_date, 'yyyy-MM') as month,\n", "\n", " COUNT(*) as total_assessments,\n", "\n", " ROUND(AVG(score), 2) as avg_score,\n", "\n", " ROUND(AVG(engagement_score), 2) as avg_engagement,\n", "\n", " COUNT(DISTINCT student_id) as active_students\n", "\n", "FROM education.analytics.student_assessments_uf\n", "\n", "GROUP BY DATE_FORMAT(assessment_date, 'yyyy-MM')\n", "\n", "ORDER BY month\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "monthly_trends.show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Key Takeaways: Iceberg and Liquid Clustering in AIDP\n", "\n", "### What We Demonstrated\n", "\n", "1. **Iceberg Compatibility**: Enabled Delta Universal Format with `'delta.universalFormat.enabledFormats' = 'iceberg'` for cross-engine access\n", "\n", "2. **Liquid Clustering**: Created a table with `CLUSTER BY (student_id, assessment_date)` for automatic data optimization\n", "\n", "3. **Performance Benefits**: Queries on clustered columns are significantly faster due to data locality\n", "\n", "4. **Zero Maintenance**: No manual partitioning, bucketing, or Z-Ordering required\n", "\n", "5. **Real-World Use Case**: Education analytics where student performance tracking and learning analytics are critical\n", "\n", "### Iceberg Advantages\n", "\n", "- **Open Standard**: Apache 2.0 licensed, community-driven table format\n", "- **Schema Evolution**: Add, drop, rename columns without expensive data rewrites\n", "- **Partition Evolution**: Change partitioning schemes without disrupting workflows\n", "- **Time Travel**: Query historical data snapshots for auditing and reproducibility\n", "- **ACID Transactions**: Reliable concurrent read/write operations across engines\n", "- **Multi-Engine Support**: Query same data from Spark, Presto, Flink, Hive, and more\n", "- **Future-Proof**: Standards-based approach protects your data investments\n", "\n", "### AIDP Advantages\n", "\n", "- **Unified Analytics**: Seamlessly integrates with other AIDP services\n", "- **Governance**: Catalog and schema isolation for education data\n", "- **Performance**: Optimized for both OLAP and OLTP workloads\n", "- **Scalability**: Handles education-scale data volumes effortlessly\n", "\n", "### Best Practices for Iceberg and Liquid Clustering\n", "\n", "1. **Choose clustering columns** based on your most common query patterns\n", "2. **Start with 1-4 columns** - too many can reduce effectiveness\n", "3. **Consider cardinality** - high-cardinality columns work best\n", "4. **Leverage Iceberg features** like schema evolution for changing requirements\n", "5. **Monitor and adjust** as query patterns and schema evolve\n", "\n", "### Next Steps\n", "\n", "- Explore Iceberg time travel capabilities with `SELECT * FROM table TIMESTAMP AS OF`\n", "- Try schema evolution by adding new columns without data migration\n", "- Query the same data from different engines like Presto or Trino\n", "- Integrate with real LMS systems and assessment platforms\n", "- Scale up to larger education datasets across multiple clusters\n", "\n", "This notebook demonstrates how Oracle AI Data Platform combines Delta's advanced liquid clustering with Iceberg's open, future-proof architecture to deliver enterprise-grade analytics that are both high-performance and standards-compliant." ] } ], "metadata": { "Last_Active_Cell_Index": 11, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "notebook" }, "language_info": { "file_extension": ".py", "mimetype": "text/x-python", "name": "python" } }, "nbformat": 4, "nbformat_minor": 4 }