Using the SVM Model in SQL

To facilitate this, you will create a SQL table, SVM_TEST_TABLE, mirroring the structure of your R data frame, DEMO_DF. This will allow you to seamlessly integrate the model's predictions into your database work flows. To use the trained SVM model into your SQL environment, follow the steps below:

• Create a table that mirrors the structure of your R data frame, enabling seamless prediction workflows within the database.

  Note:

  The data provided to the model through SQL queries must be prepared in the same manner as the data used to build the model in R.

  ore.drop(table = "SVM_TEST_TABLE")
  ore.create(DEMO_DF, table ="SVM_TEST_TABLE")

• Use the SQL Interface to score data and display the prediction results.

  SELECT CUST_ID,
      round(PREDICTION_YRS_RES,3) PRED_YRS_RES,
      RTRIM(TRIM(SUBSTR(OUTPRED."Attribute1",17,100)),'rank="1"/>') FIRST_ATTRIBUTE,
      RTRIM(TRIM(SUBSTR(OUTPRED."Attribute2",17,100)),'rank="2"/>') SECOND_ATTRIBUTE,
      RTRIM(TRIM(SUBSTR(OUTPRED."Attribute3",17,100)),'rank="3"/>') THIRD_ATTRIBUTE
  FROM (SELECT CUST_ID,
               PREDICTION(SVM_CLASSIFICATION_MODEL USING *) PREDICTION_YRS_RES,
               PREDICTION_DETAILS(SVM_CLASSIFICATION_MODEL USING *) PD
        FROM SVM_TEST_TABLE
        WHERE CUST_ID < 100015
        ORDER BY CUST_ID) OUT,
                 XMLTABLE('/Details'
                          PASSING OUT.PD
                          COLUMNS
                          "Attribute1" XMLType PATH 'Attribute[1]',
                          "Attribute2" XMLType PATH 'Attribute[2]',
                          "Attribute3" XMLType PATH 'Attribute[3]')  OUTPRED

  
  
  
  
  

  The SQL code demonstrates how to deploy and use a trained SVM classification model (SVM_CLASSIFICATION_MODEL) within a database environment. It showcases the process of scoring new data, extracting predicted values (PREDICTION_YRS_RES), and retrieving relevant attributes (FIRST_ATTRIBUTE, SECOND_ATTRIBUTE, THIRD_ATTRIBUTE) using the PREDICTION_DETAILS function.

Using the SVM Model in R

You can also make predictions and obtain prediction details directly from R using the following code:

z.show(predict(
    object = MOD,
    newdata = DEMO_DF.test,
    supplemental.cols = c("CUST_ID"),
    topN.attrs = 3
  )
)

The output appears as follows:

Deploying the Model to Other Databases or OML Services

To deploy the model to other databases or OML Services, follow these steps:

• Export the Model:
  • Use the DBMS_DATA_MINING.EXPORT_SERMODEL procedure to export the model to a BLOB object.
  • Save the BLOB object to a file or another storage location.
• Import the Model into Another Database:
  • In the target database, use DBMS_DATA_MINING.IMPORT_SERMODEL to import the model from the BLOB object.
• Deploy the Model to OML Services:
  • Use the OML REST API to upload the model and create a REST endpoint for scoring. Refer to the OML Services documentation for specific instructions.

For more information, see DBMS_DATA_MINING Package and OML Services Documentation.

This use case identified customers most likely to be positive responders to an Affinity Card loyalty program using a Support Vector Machine (SVM) classification model. Thus, the model can be used to predict which customers are likely to become high-value customers with the Affinity Card program, allowing the store to focus their marketing resources more effectively.