HeatWave ML brings machine learning training and inference inside MySQL, running on the same in-memory HeatWave cluster that accelerates your analytics queries. You train a model by calling a stored procedure. You make predictions by running a SQL SELECT. No Python runtime, no data export to a separate ML platform, no pipeline to keep in sync. This post covers enabling HeatWave ML, training a churn prediction model, making predictions in SQL, and understanding model explanations.

Step 1: Enable HeatWave ML on the DB System

resource "oci_mysql_mysql_db_system" "heatwave_ml" {
  compartment_id      = var.compartment_id
  display_name        = "heatwave-ml-production"
  shape_name          = "MySQL.HeatWave.VM.Standard"
  availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
  subnet_id           = var.private_subnet_id

  admin_username = "admin"
  admin_password = var.db_admin_password

  data_storage_size_in_gb = 500

  backup_policy {
    is_enabled         = true
    retention_in_days  = 14
    window_start_time  = "03:00"
  }

  heat_wave_cluster {
    cluster_size  = 2
    shape_name    = "MySQL.HeatWave.VM.Standard"
    is_lakehouse_enabled = false
  }

  defined_tags = {
    "Operations.Environment" = "production"
    "Operations.ManagedBy"   = "terraform"
  }
}

output "db_endpoint" {
  value = oci_mysql_mysql_db_system.heatwave_ml.endpoints[0].hostname
}

Step 2: Load Training Data into HeatWave

-- Create the training table with customer features
CREATE TABLE ml_data.customer_features (
  customer_id   INT NOT NULL,
  tenure_months INT,
  monthly_spend DECIMAL(10,2),
  support_calls INT,
  contract_type VARCHAR(20),
  payment_method VARCHAR(30),
  churned        TINYINT  -- target: 1 = churned, 0 = retained
) ENGINE=InnoDB;

-- Load data from Object Storage via PAR URL
LOAD DATA INFILE 'https://objectstorage.me-jeddah-1.oraclecloud.com/p/PAR_TOKEN/n/NAMESPACE/b/ml-data/o/customer_features.csv'
INTO TABLE ml_data.customer_features
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- Load training data into HeatWave in-memory cluster
CALL sys.heatwave_load(JSON_ARRAY('ml_data'), NULL);

-- Verify data is in HeatWave
SELECT TABLE_NAME, LOAD_STATUS
FROM performance_schema.rpd_tables
WHERE SCHEMA_NAME = 'ml_data';

Step 3: Train the Churn Prediction Model

-- Train AutoML model - HeatWave selects the best algorithm automatically
CALL sys.ML_TRAIN(
  'ml_data.customer_features',  -- training table
  'churned',                     -- target column
  JSON_OBJECT(
    'task', 'classification',
    'output_column', 'churned',
    'model_list', JSON_ARRAY(
      'classification_dt',        -- Decision Tree
      'classification_rf',        -- Random Forest
      'classification_xgb',       -- XGBoost
      'classification_mlp'        -- Neural Network
    )
  ),
  @churn_model_handle
);

SELECT @churn_model_handle;
-- Output: ml_data.customer_churn_model_20260922

-- Load the trained model into HeatWave for fast inference
CALL sys.ML_MODEL_LOAD(@churn_model_handle, NULL);

-- Check model training metrics
CALL sys.ML_MODEL_EXPLAIN(
  @churn_model_handle,
  NULL,
  @metrics
);

SELECT JSON_PRETTY(@metrics);

Step 4: Make Predictions in SQL

-- Predict churn probability for all customers in the scoring table
CALL sys.ML_PREDICT_TABLE(
  'ml_data.scoring_customers',
  @churn_model_handle,
  'ml_data.churn_predictions',
  JSON_OBJECT('prediction_interval', 0.95)
);

-- Review predictions with confidence scores
SELECT
  s.customer_id,
  s.tenure_months,
  s.monthly_spend,
  p.prediction          AS churn_predicted,
  p.probability_0       AS prob_retain,
  p.probability_1       AS prob_churn
FROM ml_data.churn_predictions p
JOIN ml_data.scoring_customers s ON s.customer_id = p.customer_id
WHERE p.probability_1 > 0.80
ORDER BY p.probability_1 DESC
LIMIT 100;

-- Single-row prediction for a new customer
SELECT sys.ML_PREDICT_ROW(
  JSON_OBJECT(
    'tenure_months',  12,
    'monthly_spend',  49.99,
    'support_calls',  8,
    'contract_type',  'month-to-month',
    'payment_method', 'electronic check'
  ),
  @churn_model_handle,
  NULL
) AS prediction_result;

Step 5: Feature Importance and Explanations

-- Get global feature importance
CALL sys.ML_EXPLAIN(
  'ml_data.customer_features',
  @churn_model_handle,
  JSON_OBJECT('type', 'GLOBAL'),
  @explanation
);

SELECT
  feature,
  importance_score
FROM
  JSON_TABLE(@explanation, '$.features[*]'
    COLUMNS(
      feature          VARCHAR(64) PATH '$.feature',
      importance_score DOUBLE      PATH '$.importance'
    )
  ) AS feature_scores
ORDER BY importance_score DESC;

-- Explain why a specific customer was predicted to churn
CALL sys.ML_EXPLAIN_ROW(
  JSON_OBJECT(
    'tenure_months',  12,
    'monthly_spend',  49.99,
    'support_calls',  8,
    'contract_type',  'month-to-month',
    'payment_method', 'electronic check'
  ),
  @churn_model_handle,
  JSON_OBJECT('type', 'SHAP'),
  @row_explanation
);

SELECT JSON_PRETTY(@row_explanation);

Operational Notes

HeatWave AutoML evaluates multiple algorithms and selects the best one based on cross-validation performance automatically. The model selection process runs inside the HeatWave cluster without any data leaving your database. Review the metrics output from ML_MODEL_EXPLAIN to understand which algorithm was selected and what accuracy, precision, and recall it achieved before deploying predictions to production workflows.

HeatWave ML models are stored in the database. When you restart the HeatWave cluster, models are automatically reloaded from persistent storage. You do not need to retrain after a cluster restart. Retrain when your training data distribution changes significantly, which typically means when business conditions change enough that historical patterns no longer predict current behavior.

Regards,
Osama

#OCI #OracleCloud #MySQLHeatWave #MachineLearning #AutoML #Terraform #TechBlog #Oracle #DataPlatform #CloudDatabase #HeatWaveML #InDatabaseML #Churn #Classification #PredictiveAnalytics #OracleCloudInfrastructure #DataScience #MLOps #SQLAnalytics #BusinessIntelligence