PMML to SQL

Generates SQL queries from PMML models. The following model types are currently supported:

  • Regression (linear, polynomial, logistic)
  • SVM (no output of probabilities)
  • MLP
  • K-Means Clustering
  • Decision Trees (no output of probabilities)
  • Naive Bayes
  • Rulesets
Ensemble models can be translated but result in complicated joins that might not run efficiently.
IMPORTANT NOTICES:
  • The node only supports tables where the columns do not contain spaces. Spaces in the PMML document's fields are converted to an underscore (_).
  • Due to several missing functions such as 'POW' SQLite is currently not supported.

The generated SQL runs best on MySQL databases. Other databases may have limitations. Postgres BUG #6359 for example limits the size of the generated SQL so that some large queries will not work. Microsoft SQL Server limits the nesting depth of CASE WHEN statements to 10, so large decision tree models cannot be executed via SQL.

Options

Input table
The table with the data to score
Primary key column
The column which holds the primary key for each record
Database compatibility mode
The style of type conversions. Only necessary for a small fraction of queries with type conversions in the preprocessing (eg String to Number or Numer to String nodes).

Input Ports

Icon
The PMML model that is translated into SQL

Output Ports

Icon
The SQL select statement for the scoring
Icon
Table containing the generated SQL

Views

SQL statement
The generated SQL statement.

Workflows

Links

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.