{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Energy: 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 energy and utilities 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: Smart Grid Monitoring and Energy Consumption Analytics\n", "\n", "We'll analyze energy consumption and smart grid performance data. Our clustering strategy will optimize for:\n", "\n", "- **Meter-specific queries**: Fast lookups by meter ID\n", "- **Time-based analysis**: Efficient filtering by reading date and time\n", "- **Consumption patterns**: Quick aggregation by location and energy type\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:26:18.121Z" } }, "outputs": [ { "data": { "text/plain": [ "Energy catalog and analytics schema created successfully!\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Create energy catalog and analytics schema\n", "\n", "# In AIDP, catalogs provide data isolation and governance\n", "\n", "spark.sql(\"CREATE CATALOG IF NOT EXISTS energy\")\n", "\n", "spark.sql(\"CREATE SCHEMA IF NOT EXISTS energy.analytics\")\n", "\n", "print(\"Energy catalog and analytics schema created successfully!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 2: Create Delta Table with Liquid Clustering\n", "\n", "### Table Design\n", "\n", "Our `energy_readings_uf` table will store:\n", "\n", "- **meter_id**: Unique smart meter identifier\n", "- **reading_date**: Date and time of meter reading\n", "- **energy_type**: Type (Electricity, Gas, Water, Solar)\n", "- **consumption**: Energy consumed (kWh, therms, gallons)\n", "- **location**: Geographic location/region\n", "- **peak_demand**: Peak usage during interval\n", "- **efficiency_rating**: System efficiency (0-100)\n", "\n", "### Clustering Strategy\n", "\n", "We'll cluster by `meter_id` and `reading_date` because:\n", "\n", "- **meter_id**: Meters generate regular readings, grouping consumption history together\n", "- **reading_date**: Time-based queries are critical for billing cycles, demand analysis, and seasonal patterns\n", "- This combination optimizes for both meter monitoring and temporal energy consumption analysis" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:26:29.807Z" } }, "outputs": [ { "data": { "text/plain": [ "Delta table with Iceberg compatibility and liquid clustering created successfully!\n", "Universal format enables Iceberg features while CLUSTER BY (meter_id, reading_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(\"meter_id\", StringType(), True),\n", " StructField(\"reading_date\", TimestampType(), True),\n", " StructField(\"energy_type\", StringType(), True),\n", " StructField(\"consumption\", DoubleType(), True),\n", " StructField(\"location\", StringType(), True),\n", " StructField(\"peak_demand\", DoubleType(), True),\n", " StructField(\"efficiency_rating\", IntegerType(), True)\n", "])\n", "\n", "spark.sql(\"\"\"\n", "\n", "CREATE TABLE IF NOT EXISTS energy.analytics.energy_readings_uf (\n", " meter_id STRING,\n", " reading_date TIMESTAMP,\n", " energy_type STRING,\n", " consumption DECIMAL(10,3),\n", " location STRING,\n", " peak_demand DECIMAL(8,2),\n", " efficiency_rating INT\n", "\n", ")\n", "\n", "USING DELTA\n", "\n", "TBLPROPERTIES('delta.universalFormat.enabledFormats' = 'iceberg')\n", "\n", "CLUSTER BY (meter_id, reading_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 (meter_id, reading_date) optimizes data layout.\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 3: Generate Energy Sample Data\n", "\n", "### Data Generation Strategy\n", "\n", "We'll create realistic energy consumption data including:\n", "\n", "- **2,000 smart meters** with hourly readings over time\n", "- **Energy types**: Electricity, Natural Gas, Water, Solar generation\n", "- **Realistic consumption patterns**: Seasonal variations, peak usage times, efficiency differences\n", "- **Geographic diversity**: Different locations with varying consumption profiles\n", "\n", "### Why This Data Pattern?\n", "\n", "This data simulates real energy scenarios where:\n", "\n", "- Consumption varies by time of day and season\n", "- Peak demand impacts grid stability\n", "- Efficiency ratings affect sustainability goals\n", "- Geographic patterns drive infrastructure planning\n", "- Real-time monitoring enables demand response programs" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:26:48.475Z" } }, "outputs": [ { "data": { "text/plain": [ "Generated 4320000 energy reading records\n", "Sample record: {'meter_id': 'MTR000001', 'reading_date': datetime.datetime(2024, 1, 1, 0, 0), 'energy_type': 'Electricity', 'consumption': 22.63, 'location': 'Residential_NYC', 'peak_demand': 30.37, 'efficiency_rating': 84}\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Generate sample energy consumption 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 energy data constants\n", "\n", "ENERGY_TYPES = ['Electricity', 'Natural Gas', 'Water', 'Solar']\n", "\n", "LOCATIONS = ['Residential_NYC', 'Commercial_CHI', 'Industrial_HOU', 'Residential_LAX', 'Commercial_SFO']\n", "\n", "# Base consumption parameters by energy type and location\n", "\n", "CONSUMPTION_PARAMS = {\n", "\n", " 'Electricity': {\n", "\n", " 'Residential_NYC': {'base_consumption': 15, 'peak_factor': 2.5, 'efficiency': 85},\n", "\n", " 'Commercial_CHI': {'base_consumption': 150, 'peak_factor': 3.0, 'efficiency': 78},\n", "\n", " 'Industrial_HOU': {'base_consumption': 500, 'peak_factor': 2.2, 'efficiency': 92},\n", "\n", " 'Residential_LAX': {'base_consumption': 12, 'peak_factor': 2.8, 'efficiency': 88},\n", "\n", " 'Commercial_SFO': {'base_consumption': 180, 'peak_factor': 2.7, 'efficiency': 82}\n", "\n", " },\n", "\n", " 'Natural Gas': {\n", "\n", " 'Residential_NYC': {'base_consumption': 25, 'peak_factor': 1.8, 'efficiency': 90},\n", "\n", " 'Commercial_CHI': {'base_consumption': 80, 'peak_factor': 2.1, 'efficiency': 85},\n", "\n", " 'Industrial_HOU': {'base_consumption': 200, 'peak_factor': 1.9, 'efficiency': 95},\n", "\n", " 'Residential_LAX': {'base_consumption': 20, 'peak_factor': 2.0, 'efficiency': 87},\n", "\n", " 'Commercial_SFO': {'base_consumption': 95, 'peak_factor': 2.3, 'efficiency': 83}\n", "\n", " },\n", "\n", " 'Water': {\n", "\n", " 'Residential_NYC': {'base_consumption': 180, 'peak_factor': 1.5, 'efficiency': 88},\n", "\n", " 'Commercial_CHI': {'base_consumption': 450, 'peak_factor': 1.7, 'efficiency': 82},\n", "\n", " 'Industrial_HOU': {'base_consumption': 1200, 'peak_factor': 1.6, 'efficiency': 91},\n", "\n", " 'Residential_LAX': {'base_consumption': 160, 'peak_factor': 1.8, 'efficiency': 85},\n", "\n", " 'Commercial_SFO': {'base_consumption': 380, 'peak_factor': 1.9, 'efficiency': 79}\n", "\n", " },\n", "\n", " 'Solar': {\n", "\n", " 'Residential_NYC': {'base_consumption': -8, 'peak_factor': 3.5, 'efficiency': 78},\n", "\n", " 'Commercial_CHI': {'base_consumption': -75, 'peak_factor': 4.0, 'efficiency': 85},\n", "\n", " 'Industrial_HOU': {'base_consumption': -250, 'peak_factor': 3.8, 'efficiency': 88},\n", "\n", " 'Residential_LAX': {'base_consumption': -12, 'peak_factor': 4.2, 'efficiency': 82},\n", "\n", " 'Commercial_SFO': {'base_consumption': -95, 'peak_factor': 3.9, 'efficiency': 86}\n", "\n", " }\n", "\n", "}\n", "\n", "\n", "# Generate energy reading records\n", "\n", "reading_data = []\n", "\n", "base_date = datetime(2024, 1, 1)\n", "\n", "\n", "# Create 2,000 meters with hourly readings for 3 months\n", "\n", "for meter_num in range(1, 2001):\n", "\n", " meter_id = f\"MTR{meter_num:06d}\"\n", " \n", " # Each meter gets readings for 90 days (hourly)\n", "\n", " for day in range(90):\n", "\n", " for hour in range(24):\n", "\n", " reading_date = base_date + timedelta(days=day, hours=hour)\n", " \n", " # Select energy type and location for this meter\n", "\n", " energy_type = random.choice(ENERGY_TYPES)\n", "\n", " location = random.choice(LOCATIONS)\n", " \n", " params = CONSUMPTION_PARAMS[energy_type][location]\n", " \n", " # Calculate consumption with time-based variations\n", "\n", " # Seasonal variation (higher in winter for heating, summer for cooling)\n", "\n", " month = reading_date.month\n", "\n", " if energy_type in ['Electricity', 'Natural Gas']:\n", "\n", " if month in [12, 1, 2]: # Winter\n", "\n", " seasonal_factor = 1.4\n", "\n", " elif month in [6, 7, 8]: # Summer\n", "\n", " seasonal_factor = 1.3\n", "\n", " else:\n", "\n", " seasonal_factor = 1.0\n", "\n", " else:\n", "\n", " seasonal_factor = 1.0\n", " \n", " # Time-of-day variation\n", "\n", " hour_factor = 1.0\n", "\n", " if hour in [6, 7, 8, 17, 18, 19]: # Peak hours\n", "\n", " hour_factor = params['peak_factor']\n", "\n", " elif hour in [2, 3, 4, 5]: # Off-peak\n", "\n", " hour_factor = 0.4\n", "\n", " \n", " # Calculate consumption\n", "\n", " consumption_variation = random.uniform(0.8, 1.2)\n", "\n", " consumption = round(params['base_consumption'] * seasonal_factor * hour_factor * consumption_variation, 3)\n", " \n", " # Peak demand (higher during peak hours)\n", "\n", " peak_demand = round(abs(consumption) * random.uniform(1.1, 1.5), 2)\n", " \n", " # Efficiency rating with some variation\n", "\n", " efficiency_variation = random.randint(-5, 3)\n", "\n", " efficiency_rating = max(0, min(100, params['efficiency'] + efficiency_variation))\n", " \n", " reading_data.append({\n", "\n", " \"meter_id\": meter_id,\n", "\n", " \"reading_date\": reading_date,\n", "\n", " \"energy_type\": energy_type,\n", "\n", " \"consumption\": consumption,\n", "\n", " \"location\": location,\n", "\n", " \"peak_demand\": peak_demand,\n", "\n", " \"efficiency_rating\": efficiency_rating\n", "\n", " })\n", "\n", "\n", "\n", "print(f\"Generated {len(reading_data)} energy reading records\")\n", "\n", "print(\"Sample record:\", reading_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 Delta 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", "- **Liquid clustering**: Automatically applies clustering during insertion" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:27:47.916Z" } }, "outputs": [ { "data": { "text/plain": [ "DataFrame Schema:\n", "root\n", " |-- meter_id: string (nullable = true)\n", " |-- reading_date: timestamp (nullable = true)\n", " |-- energy_type: string (nullable = true)\n", " |-- consumption: double (nullable = true)\n", " |-- location: string (nullable = true)\n", " |-- peak_demand: double (nullable = true)\n", " |-- efficiency_rating: integer (nullable = true)\n", "\n", "\n", "Sample Data:\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------+-------------------+-----------+-----------+---------------+-----------+-----------------+\n", "| meter_id| reading_date|energy_type|consumption| location|peak_demand|efficiency_rating|\n", "+---------+-------------------+-----------+-----------+---------------+-----------+-----------------+\n", "|MTR000001|2024-01-01 00:00:00|Electricity| 22.63|Residential_NYC| 30.37| 84|\n", "|MTR000001|2024-01-01 01:00:00|Natural Gas| 32.813|Residential_LAX| 39.8| 84|\n", "|MTR000001|2024-01-01 02:00:00| Solar| -25.746| Commercial_CHI| 31.4| 86|\n", "|MTR000001|2024-01-01 03:00:00| Solar| -84.605| Industrial_HOU| 108.68| 86|\n", "|MTR000001|2024-01-01 04:00:00|Electricity| 87.225| Commercial_CHI| 100.48| 74|\n", "+---------+-------------------+-----------+-----------+---------------+-----------+-----------------+\n", "only showing top 5 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "\n", "Successfully inserted 4320000 records into energy.analytics.energy_readings_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_readings = spark.createDataFrame(reading_data, schema=data_schema)\n", "\n", "\n", "# Display schema and sample data\n", "\n", "print(\"DataFrame Schema:\")\n", "\n", "df_readings.printSchema()\n", "\n", "\n", "\n", "print(\"\\nSample Data:\")\n", "\n", "df_readings.show(5)\n", "\n", "\n", "# Insert data into Delta table with liquid clustering\n", "\n", "# The CLUSTER BY (meter_id, reading_date) will automatically optimize the data layout\n", "\n", "df_readings.write.mode(\"overwrite\").insertInto(\"energy.analytics.energy_readings_uf\")\n", "\n", "\n", "print(f\"\\nSuccessfully inserted {df_readings.count()} records into energy.analytics.energy_readings_uf\")\n", "\n", "print(\"Liquid clustering automatically optimized the data layout during insertion!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 5: Demonstrate Liquid Clustering Benefits\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. **Meter reading history** (clustered by meter_id)\n", "2. **Time-based consumption analysis** (clustered by reading_date)\n", "3. **Combined meter + time queries** (optimal for our clustering)\n", "\n", "### Expected Performance Benefits\n", "\n", "With liquid clustering, 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" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:28:05.668Z" } }, "outputs": [ { "data": { "text/plain": [ "=== Query 1: Meter Reading History ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------+-------------------+-----------+-----------+-----------+-----------------+\n", "| meter_id| reading_date|energy_type|consumption|peak_demand|efficiency_rating|\n", "+---------+-------------------+-----------+-----------+-----------+-----------------+\n", "|MTR000001|2024-03-30 23:00:00| Water| 449.370| 590.19| 79|\n", "|MTR000001|2024-03-30 22:00:00| Water| 133.854| 162.45| 88|\n", "|MTR000001|2024-03-30 21:00:00| Water| 965.564| 1381.94| 87|\n", "|MTR000001|2024-03-30 20:00:00| Solar| -69.952| 98.65| 83|\n", "|MTR000001|2024-03-30 19:00:00|Natural Gas| 426.459| 493.05| 93|\n", "|MTR000001|2024-03-30 18:00:00|Natural Gas| 150.884| 212.86| 83|\n", "|MTR000001|2024-03-30 17:00:00| Water| 238.527| 285.26| 83|\n", "|MTR000001|2024-03-30 16:00:00| Water| 510.061| 578.26| 78|\n", "|MTR000001|2024-03-30 15:00:00|Natural Gas| 101.266| 127.15| 82|\n", "|MTR000001|2024-03-30 14:00:00|Natural Gas| 187.497| 269.33| 97|\n", "|MTR000001|2024-03-30 13:00:00| Water| 195.236| 238.21| 91|\n", "|MTR000001|2024-03-30 12:00:00|Electricity| 585.852| 849.53| 90|\n", "|MTR000001|2024-03-30 11:00:00| Solar| -91.084| 122.10| 84|\n", "|MTR000001|2024-03-30 10:00:00| Water| 147.561| 168.57| 83|\n", "|MTR000001|2024-03-30 09:00:00|Natural Gas| 65.331| 86.60| 82|\n", "|MTR000001|2024-03-30 08:00:00|Electricity| 39.077| 51.33| 84|\n", "|MTR000001|2024-03-30 07:00:00|Electricity| 1051.684| 1506.99| 88|\n", "|MTR000001|2024-03-30 06:00:00| Water| 1633.937| 2223.03| 91|\n", "|MTR000001|2024-03-30 05:00:00|Natural Gas| 44.761| 50.80| 81|\n", "|MTR000001|2024-03-30 04:00:00|Natural Gas| 8.880| 11.53| 88|\n", "+---------+-------------------+-----------+-----------+-----------+-----------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Records found: 24\n", "\n", "=== Query 2: Recent Peak Demand Issues ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------------------+---------+--------------+-----------+-----------+\n", "| reading_date| meter_id| location|peak_demand|energy_type|\n", "+-------------------+---------+--------------+-----------+-----------+\n", "|2024-02-15 07:00:00|MTR000459|Industrial_HOU| 3413.45| Water|\n", "|2024-02-15 17:00:00|MTR001112|Industrial_HOU| 3379.53| Water|\n", "|2024-02-15 19:00:00|MTR000446|Industrial_HOU| 3330.99| Water|\n", "|2024-02-15 08:00:00|MTR001640|Industrial_HOU| 3328.78| Water|\n", "|2024-02-15 07:00:00|MTR001351|Industrial_HOU| 3328.77| Water|\n", "|2024-02-15 08:00:00|MTR001003|Industrial_HOU| 3304.94| Water|\n", "|2024-02-15 18:00:00|MTR000009|Industrial_HOU| 3299.35| Water|\n", "|2024-02-15 06:00:00|MTR001749|Industrial_HOU| 3294.04| Water|\n", "|2024-02-15 07:00:00|MTR001703|Industrial_HOU| 3293.23| Water|\n", "|2024-02-15 07:00:00|MTR000170|Industrial_HOU| 3291.36| Water|\n", "|2024-02-15 07:00:00|MTR000318|Industrial_HOU| 3287.42| Water|\n", "|2024-02-15 19:00:00|MTR000578|Industrial_HOU| 3286.96| Water|\n", "|2024-02-15 07:00:00|MTR001470|Industrial_HOU| 3260.13| Water|\n", "|2024-02-15 17:00:00|MTR000289|Industrial_HOU| 3259.02| Water|\n", "|2024-02-15 18:00:00|MTR001244|Industrial_HOU| 3258.93| Water|\n", "|2024-02-15 19:00:00|MTR001733|Industrial_HOU| 3258.62| Water|\n", "|2024-02-15 07:00:00|MTR001421|Industrial_HOU| 3219.77| Water|\n", "|2024-02-15 06:00:00|MTR001726|Industrial_HOU| 3210.13| Water|\n", "|2024-02-15 19:00:00|MTR000265|Industrial_HOU| 3204.42| Water|\n", "|2024-02-15 19:00:00|MTR000609|Industrial_HOU| 3202.62| Water|\n", "+-------------------+---------+--------------+-----------+-----------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Peak demand issues found: 22867\n", "\n", "=== Query 3: Meter Consumption Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------+-------------------+-----------+-----------+-----------------+\n", "| meter_id| reading_date|energy_type|consumption|efficiency_rating|\n", "+---------+-------------------+-----------+-----------+-----------------+\n", "|MTR000001|2024-02-01 00:00:00| Water| 516.579| 81|\n", "|MTR000001|2024-02-01 01:00:00| Solar| -217.083| 88|\n", "|MTR000001|2024-02-01 02:00:00| Solar| -38.785| 81|\n", "|MTR000001|2024-02-01 03:00:00|Electricity| 322.577| 92|\n", "|MTR000001|2024-02-01 04:00:00| Water| 79.454| 90|\n", "|MTR000001|2024-02-01 05:00:00|Natural Gas| 11.338| 86|\n", "|MTR000001|2024-02-01 06:00:00| Solar| -330.425| 80|\n", "|MTR000001|2024-02-01 07:00:00| Solar| -33.084| 74|\n", "|MTR000001|2024-02-01 08:00:00| Solar| -50.256| 82|\n", "|MTR000001|2024-02-01 09:00:00| Water| 481.211| 81|\n", "|MTR000001|2024-02-01 10:00:00|Natural Gas| 33.139| 87|\n", "|MTR000001|2024-02-01 11:00:00|Electricity| 723.393| 95|\n", "|MTR000001|2024-02-01 12:00:00| Solar| -6.709| 73|\n", "|MTR000001|2024-02-01 13:00:00| Solar| -229.414| 87|\n", "|MTR000001|2024-02-01 14:00:00| Solar| -13.182| 77|\n", "|MTR000001|2024-02-01 15:00:00| Water| 368.711| 80|\n", "|MTR000001|2024-02-01 16:00:00|Electricity| 17.012| 82|\n", "|MTR000001|2024-02-01 17:00:00|Natural Gas| 54.059| 88|\n", "|MTR000001|2024-02-01 18:00:00|Natural Gas| 599.854| 92|\n", "|MTR000001|2024-02-01 19:00:00| Water| 277.064| 87|\n", "+---------+-------------------+-----------+-----------+-----------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Consumption trend records found: 50\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Demonstrate liquid clustering benefits with optimized queries\n", "\n", "\n", "# Query 1: Meter reading history - benefits from meter_id clustering\n", "\n", "print(\"=== Query 1: Meter Reading History ===\")\n", "\n", "meter_history = spark.sql(\"\"\"\n", "\n", "SELECT meter_id, reading_date, energy_type, consumption, peak_demand, efficiency_rating\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "WHERE meter_id = 'MTR000001'\n", "\n", "ORDER BY reading_date DESC\n", "\n", "LIMIT 24\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "meter_history.show()\n", "\n", "print(f\"Records found: {meter_history.count()}\")\n", "\n", "\n", "\n", "# Query 2: Time-based peak demand analysis - benefits from reading_date clustering\n", "\n", "print(\"\\n=== Query 2: Recent Peak Demand Issues ===\")\n", "\n", "peak_demand = spark.sql(\"\"\"\n", "\n", "SELECT reading_date, meter_id, location, peak_demand, energy_type\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "WHERE DATE(reading_date) = '2024-02-15' AND peak_demand > 200\n", "\n", "ORDER BY peak_demand DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "peak_demand.show()\n", "\n", "print(f\"Peak demand issues found: {peak_demand.count()}\")\n", "\n", "\n", "\n", "# Query 3: Combined meter + time query - optimal for our clustering strategy\n", "\n", "print(\"\\n=== Query 3: Meter Consumption Trends ===\")\n", "\n", "consumption_trends = spark.sql(\"\"\"\n", "\n", "SELECT meter_id, reading_date, energy_type, consumption, efficiency_rating\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "WHERE meter_id LIKE 'MTR000%' AND reading_date >= '2024-02-01'\n", "\n", "ORDER BY meter_id, reading_date\n", "\n", "LIMIT 50\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "consumption_trends.show()\n", "\n", "print(f\"Consumption trend records found: {consumption_trends.count()}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 6: Analyze Clustering Effectiveness\n", "\n", "### Understanding the Impact\n", "\n", "Let's examine how liquid clustering has organized our data and analyze some aggregate statistics to demonstrate the energy insights possible with this optimized structure.\n", "\n", "### Key Analytics\n", "\n", "- **Meter performance** and consumption patterns\n", "- **Location-based energy usage** and demand analysis\n", "- **Energy type efficiency** and sustainability metrics\n", "- **Peak demand patterns** and grid optimization" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.status.busy": "2025-12-11T19:28:28.577Z" } }, "outputs": [ { "data": { "text/plain": [ "=== Meter Performance Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------+--------------+---------------+---------------+--------------+--------------------------+\n", "| meter_id|total_readings|avg_consumption|max_peak_demand|avg_efficiency|total_absolute_consumption|\n", "+---------+--------------+---------------+---------------+--------------+--------------------------+\n", "|MTR001916| 2160| 208.707| 2985.44| 84.67| 620282.246|\n", "|MTR000157| 2160| 222.600| 3256.98| 84.54| 617825.590|\n", "|MTR000565| 2160| 216.394| 3126.39| 84.58| 617018.134|\n", "|MTR000576| 2160| 222.458| 3253.59| 84.43| 616379.022|\n", "|MTR001948| 2160| 215.884| 3181.42| 84.48| 612046.301|\n", "|MTR001293| 2160| 212.167| 3383.61| 84.46| 611384.167|\n", "|MTR001205| 2160| 210.094| 3373.42| 84.8| 610484.223|\n", "|MTR000733| 2160| 210.495| 3307.72| 84.42| 610160.975|\n", "|MTR000870| 2160| 201.026| 3096.81| 84.5| 609722.688|\n", "|MTR001463| 2160| 205.971| 3140.12| 84.54| 608372.124|\n", "+---------+--------------+---------------+---------------+--------------+--------------------------+\n", "\n", "\n", "=== Location-Based Consumption Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------------+--------------+-----------------+---------------+--------------+-------------+\n", "| location|total_readings|total_consumption|avg_peak_demand|avg_efficiency|active_meters|\n", "+---------------+--------------+-----------------+---------------+--------------+-------------+\n", "| Industrial_HOU| 863742| 584055626.834| 878.91| 90.5| 2000|\n", "| Commercial_SFO| 864534| 222997383.493| 335.34| 81.5| 2000|\n", "| Commercial_CHI| 863343| 214004206.401| 322.18| 81.5| 2000|\n", "|Residential_NYC| 863822| 55232027.994| 83.12| 84.26| 2000|\n", "|Residential_LAX| 864559| 53280630.620| 80.09| 84.51| 2000|\n", "+---------------+--------------+-----------------+---------------+--------------+-------------+\n", "\n", "\n", "=== Energy Type Efficiency Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+--------------+---------------+--------------+---------------+-------------+\n", "|energy_type|total_readings|avg_consumption|avg_efficiency|max_peak_demand|unique_meters|\n", "+-----------+--------------+---------------+--------------+---------------+-------------+\n", "| Water| 1079790| 506.430| 84.0| 3450.26| 2000|\n", "|Electricity| 1079834| 274.144| 84.0| 2765.18| 2000|\n", "| Solar| 1078228| 142.394| 82.81| 1708.15| 2000|\n", "|Natural Gas| 1082148| 123.059| 87.0| 956.89| 2000|\n", "+-----------+--------------+---------------+--------------+---------------+-------------+\n", "\n", "\n", "=== Daily Consumption Patterns ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------+----+-----------------+---------------+-------------+\n", "| date|hour|total_consumption|avg_peak_demand|reading_count|\n", "+----------+----+-----------------+---------------+-------------+\n", "|2024-02-01| 0| 458563.466| 299.21| 2000|\n", "|2024-02-01| 1| 464051.363| 302.08| 2000|\n", "|2024-02-01| 2| 189948.747| 123.93| 2000|\n", "|2024-02-01| 3| 182712.027| 119.21| 2000|\n", "|2024-02-01| 4| 183636.554| 119.11| 2000|\n", "|2024-02-01| 5| 186385.233| 121.65| 2000|\n", "|2024-02-01| 6| 950079.665| 616.24| 2000|\n", "|2024-02-01| 7| 953973.543| 619.71| 2000|\n", "|2024-02-01| 8| 1013545.270| 659.98| 2000|\n", "|2024-02-01| 9| 457371.632| 296.82| 2000|\n", "|2024-02-01| 10| 458012.733| 298.76| 2000|\n", "|2024-02-01| 11| 466826.138| 304.09| 2000|\n", "|2024-02-01| 12| 474143.541| 306.98| 2000|\n", "|2024-02-01| 13| 481401.839| 314.38| 2000|\n", "|2024-02-01| 14| 458031.588| 297.82| 2000|\n", "|2024-02-01| 15| 462391.154| 300.21| 2000|\n", "|2024-02-01| 16| 468188.026| 304.26| 2000|\n", "|2024-02-01| 17| 979126.055| 637.52| 2000|\n", "|2024-02-01| 18| 1000200.507| 650.94| 2000|\n", "|2024-02-01| 19| 989611.407| 643.71| 2000|\n", "+----------+----+-----------------+---------------+-------------+\n", "only showing top 20 rows\n", "\n", "\n", "=== Monthly Consumption Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------+-------------------+---------------+--------------+-------------+\n", "| month|monthly_consumption|avg_peak_demand|avg_efficiency|active_meters|\n", "+-------+-------------------+---------------+--------------+-------------+\n", "|2024-01| 404896870.569| 353.69| 84.45| 2000|\n", "|2024-02| 378261676.011| 353.22| 84.45| 2000|\n", "|2024-03| 346411328.762| 312.71| 84.46| 2000|\n", "+-------+-------------------+---------------+--------------+-------------+\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Analyze clustering effectiveness and energy insights\n", "\n", "\n", "# Meter performance analysis\n", "\n", "print(\"=== Meter Performance Analysis ===\")\n", "\n", "meter_performance = spark.sql(\"\"\"\n", "\n", "SELECT meter_id, COUNT(*) as total_readings,\n", "\n", " ROUND(AVG(consumption), 3) as avg_consumption,\n", "\n", " ROUND(MAX(peak_demand), 2) as max_peak_demand,\n", "\n", " ROUND(AVG(efficiency_rating), 2) as avg_efficiency,\n", "\n", " ROUND(SUM(ABS(consumption)), 3) as total_absolute_consumption\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "GROUP BY meter_id\n", "\n", "ORDER BY total_absolute_consumption DESC\n", "\n", "LIMIT 10\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "meter_performance.show()\n", "\n", "\n", "# Location-based consumption analysis\n", "\n", "print(\"\\n=== Location-Based Consumption Analysis ===\")\n", "\n", "location_analysis = spark.sql(\"\"\"\n", "\n", "SELECT location, COUNT(*) as total_readings,\n", "\n", " ROUND(SUM(ABS(consumption)), 3) as total_consumption,\n", "\n", " ROUND(AVG(peak_demand), 2) as avg_peak_demand,\n", "\n", " ROUND(AVG(efficiency_rating), 2) as avg_efficiency,\n", "\n", " COUNT(DISTINCT meter_id) as active_meters\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "GROUP BY location\n", "\n", "ORDER BY total_consumption DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "location_analysis.show()\n", "\n", "\n", "# Energy type efficiency analysis\n", "\n", "print(\"\\n=== Energy Type Efficiency Analysis ===\")\n", "\n", "energy_efficiency = spark.sql(\"\"\"\n", "\n", "SELECT energy_type, COUNT(*) as total_readings,\n", "\n", " ROUND(AVG(ABS(consumption)), 3) as avg_consumption,\n", "\n", " ROUND(AVG(efficiency_rating), 2) as avg_efficiency,\n", "\n", " ROUND(MAX(peak_demand), 2) as max_peak_demand,\n", "\n", " COUNT(DISTINCT meter_id) as unique_meters\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "GROUP BY energy_type\n", "\n", "ORDER BY avg_consumption DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "energy_efficiency.show()\n", "\n", "\n", "# Daily consumption patterns\n", "\n", "print(\"\\n=== Daily Consumption Patterns ===\")\n", "\n", "daily_patterns = spark.sql(\"\"\"\n", "\n", "SELECT DATE(reading_date) as date, HOUR(reading_date) as hour,\n", "\n", " ROUND(SUM(ABS(consumption)), 3) as total_consumption,\n", "\n", " ROUND(AVG(peak_demand), 2) as avg_peak_demand,\n", "\n", " COUNT(*) as reading_count\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "WHERE DATE(reading_date) = '2024-02-01'\n", "\n", "GROUP BY DATE(reading_date), HOUR(reading_date)\n", "\n", "ORDER BY hour\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "daily_patterns.show()\n", "\n", "\n", "# Monthly consumption trends\n", "\n", "print(\"\\n=== Monthly Consumption Trends ===\")\n", "\n", "monthly_trends = spark.sql(\"\"\"\n", "\n", "SELECT DATE_FORMAT(reading_date, 'yyyy-MM') as month,\n", "\n", " ROUND(SUM(ABS(consumption)), 3) as monthly_consumption,\n", "\n", " ROUND(AVG(peak_demand), 2) as avg_peak_demand,\n", "\n", " ROUND(AVG(efficiency_rating), 2) as avg_efficiency,\n", "\n", " COUNT(DISTINCT meter_id) as active_meters\n", "\n", "FROM energy.analytics.energy_readings_uf\n", "\n", "GROUP BY DATE_FORMAT(reading_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 (meter_id, reading_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**: Energy analytics where smart grid monitoring and consumption analysis 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 energy data\n", "- **Performance**: Optimized for both OLAP and OLTP workloads\n", "- **Scalability**: Handles energy-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 smart meter and IoT sensor data\n", "- Scale up to larger energy 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": 7, "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 }