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).

Views

SQL statement

The generated SQL statement.

Workflows

• 02_Model_Deployment_SQL_Java_recodingKNIME Hub
• Adv_Trees_Chap6_PMMLKNIME Hub
• Beginners - Discover the KNIME PossibilitiesKNIME Hub
• kn_example_decision_treeKNIME Hub
• kn_example_decision_treeKNIME Hub
• Show all 7 workflows

Links

• Output developed model to SQL code?by izaychik63 on 2019-02-27

  forum.knime.com/p/55350
• Pulling a polynomial regression model out of a polynomial regression learner node.by mlauber71 on 2019-06-19

  forum.knime.com/p/62554
• Decision Tree to Ruleset errorby mlauber71 on 2018-11-04

  forum.knime.com/p/49528

Developers