In this blog post, we outline how to use a MySQL database as a client to the ADAPA Scoring Engine by leveraging the ADAPA REST API to execute a predictive analytics model based on the Predictive Model Markup Language (PMML) industry standard.
We assume that:
- MySQL and cURL are installed
- Necessary MySQL tables are already created
- A PMML model for the data is deployed and available through the ADAPA
- REST API.
One option to make API calls from MySQL is by using the MySQL-UDF-HTTP package, which enables creation of user defined functions for HTTP REST Operations in a database. This package is available on Google Code and will be installed on top of MySQL. We can leverage the User Defined Functions (UDFs) created with this package to make REST API calls to ADAPA from MySQL. Specifically, we use HTTP GET requests to the ADAPA engine to score one record at a time. An advantage of using these functions is that we can easily write the scores back to the database.
In addition, the scoring process can be automated with database triggers. Triggers automatically execute database queries when specified events occur. In this case, we can write functions to score and update or insert records, and set triggers to execute these functions on update and insert events. The HTTP UDF is called by the scoring function to send a GET request to the ADAPA REST API.
Simply using SQL and UDFs, the above enables us to easily execute complex predictive analytics models directly from one of the most commonly used databases, score the records, and write the results back into a database table.
A step-by-step tutorial, including installing MySQL-UDF-HTTP and writing functions and triggers, is available in this video.