This paper introduces a novel, extendable, no-code framework for integrating machine-learning algorithms into SQL using the Exasol database. The framework combines the strengths of the highperformance, parallel-processing analytical Exasol database with the flexible and sophisticated machine learning algorithms of the Python library Scikit-Learn, while providing a seamless integration into SQL. This paper explores the technical background, the concept, and the implementation of the framework. The CREATE MODEL command for creating a machine learning model and the PREDICT function for prediction using a pre-trained model are discussed in detail. The main contributions of the framework are its seamless integration into SQL, scalability, and leveraging of existing database infrastructure. An overview of related work is also given.
CEUR ceur-ws.org
CEUR Workshop Proceedings employee as our data source. Additionally, we specify the prediction target or label salary and the features position and the birthyear used to determine the label.
CREATE MODEL "model" ON employees PREDICT (salary) USING ("position", birthyear);
We can use the created model to predict the salaries of employees. We again use the position and birthyear of the employee table as features and predict the missing salary entries. The prediction result is shown in Table 1. This example is elaborated on further in the following. SELECT name, "position", birthyear, PREDICT "model" USING ("position", birthyear) FROM employees WHERE salary IS NULL;
Our framework facilitates eficient resource utilization by capitalizing on Exasol’s parallel processing capabilities and ETL pipeline, enabling scalability to handle large datasets and complex analytical workloads. Furthermore, in-database exploratory data analysis is simplified by the availability of no-code ML functionality. Finally, this integration eliminates the need for data movement between diferent systems, reducing latency, and enhancing the overall eficiency of the analytics workflow.
This paper provides an overview of the technical background, related work, the concept, and the implementation of the framework. It closes with a discussion and a conclusion.
Exasol is a proprietary distributed relational analytical database management system. It runs on
the Linux-based operating system (OS) ExaCluster OS, which provides a runtime environment
and a storage layer for the database. Exasol being a cluster of nodes allows it to execute queries
in parallel and makes it cloud-ready. Furthermore, Exasol uses column-oriented storage and
in-memory processing. For data unfit to be stored in the database, Exasol provides the file
system BucketFS [
The features of the Exasol database our framework relies upon are introduced in the following.
The aforementioned BucketFS is a plain file system and can be accessed using a HTTPS interface.
Data stored in BucketFS is replicated over all nodes of a cluster. Eventual consistency is
guaranteed [
We chose Python since it is a popular language for ML providing many popular libraries like
Scikit-Learn, PyTorch, and TensorFlow [
Exasol’s developers and community provide information and many examples for creating UDF
scripts for ML and data analysis tasks [
There are several other approaches to integrate ML into database systems. Among these,
our framework stands out through its focus on smooth SQL integration. The approach closest
to our framework is the Apache MADlib analytics library, which uses user-defined functions
and aggregates to implement in-database ML algorithms [
An important part of our framework is the convenient, well-integrated syntax for handling ML models. ML models are handled as database objects stored in system tables and with support for DDL commands. These commands include CREATE, RENAME, DROP, ALTER, and REPLACE. The CREATE command creates a new model and trains it. The RENAME command renames an existing model and all associated files. The DROP command deletes an existing model and all associated files. The ALTER command allows for changing the parameters of an existing model. The REPLACE command replaces an existing model with the newly trained one. In addition to these commands, we introduce three commands unique to ML models: IMPORT, RETRAIN, and PREDICT. The IMPORT command creates the metadata for an ML model that already exists in BucketFS. The RETRAIN command retrains the specified ML model with the updated data in the source table or view. An error is thrown, if the source table or view is missing columns needed for the training of the model. The PREDICT function uses a previously trained ML model and the specified input data to predict values.
In the following, we present the syntax of the CREATE and the PREDICT command. Additionally, examples are given for better understanding. These examples use the “employees” table shown in Table 2. The table contains the name, position, year of birth, and salary of diferent employees. Some of the employee salaries are NULL and thus unknown. We will create an ML model to predict these values. name
⋮ position
Sales Associate ⋮ birthyear
The syntax for creating an ML model using our framework is shown in Figure 1. The name determines the unique object identifier of the model. This identifier is needed for all further interactions with the model, like using it for predictions. The source identifier determines which table or view is used as the input for training the model. Thus, the table or view has to exist and preferably contain data. If the table or view is empty, the model has to be retrained after the data is inserted. The column specifiers in the PREDICT clause determine which columns of the source table or view are the labels of the model and thus contain the values to be predicted. The column specifiers in the USING clause determine which columns of the source table or view are the features of the model and thus contain the values that can be used to predict ( , =
, name
ON source
PREDICT USING (
) , column
WITH key value the labels. The WITH clause allows for setting additional parameters using key-value pairs. These parameters can be used to determine the output type of the model, to specify the ML algorithm to be used, and to pass additional settings to the algorithm. Examples of output types are classification and regression. In case no output type is determined using the WITH clause, regression is assumed if all labels are of the data type DOUBLE PRECISION (or its aliases DOUBLE, FLOAT, NUMBER, and REAL). Otherwise, classification is assumed as the output type.
As an example, we create a model "sal", which uses the employee table as its source. The label to predict is the salary and the features are the position and the birthyear of employees. Furthermore, we specify the model to use the 'DecisionTreeRegressor' function, which determines the output to be a regression. Additionally, we specify a maximum depth of 64 for the created decision tree. The query to create the specified model is the following: CREATE MODEL "sal" ON employees PREDICT (salary) USING ("position", birthyear) WITH 'Function' = 'DecisionTreeRegressor', 'max_depth' = 64;
The source table for this model contains some NULL values in the salary column. For training, only tuples without NULL values in labels are used. After the training, the model is stored in BucketFS for future use.
The syntax for the variadic function PREDICT is shown in Figure 2. The name corresponds to the identifier of an already existing ML model. The output of the prediction is one set of labels for each input row. These labels correspond to the trained labels, having the same name and a compatible data type. The column parameter list determines which columns serve as the features of the model. The number and position of these features have to match the number and position of the features used in the training step. The data types of the features have to be compatible with the features used for training, while the name of the features is of no importance. Furthermore, a prediction does not have to use the same table that was used for training.
As an example, we use the previously trained ML model to predict the salary of employees, for whom this information is missing. We again use the employee table as source as well as name position and birthyear as features. Since salary is a currency value, we format it to have two decimal places by casting it as a DECIMAL(14,2). Furthermore, we rename the result of the prediction to pred_salary to avoid duplicate column names. The query for this prediction is the following: SELECT name, "position", birthyear, salary AS original_salary, PREDICT "sal" USING ("position", birthyear) FROM employees;
The result of the query is shown in Table 3. For each employee, this information is predicted based on the data of employees with valid salary information. To persist the prediction, INSERT, CREATE TABLE AS, or UPDATE queries can be used. name
⋮ position
Sales Associate ⋮
The implementation of the framework works with both the single-node “Community Version”
[
Our framework currently supports five algorithms of the Python library Scikit-Learn. These supported algorithms are listed in Table 4. All parameters of the algorithms are supported by our framework. In the future, other algorithms of the Scikit-Learn framework and other frameworks, even ones written in other programming languages, will be supported by our framework.
To process commands interacting with ML models, our framework employs several layers. These layers are visualized in Figure 3. The first layer processing incoming queries consists of preprocessor scripts. This layer converts the custom SQL syntax of the framework to scripting programs, which are the second layer of the framework. The scripting programs handle the metadata of the model and call the UDF scripts, which are the third, final layer. UDF scripts handle the calling of the actual ML functionality provided by the Python library Scikit-Learn. Furthermore, the UDF scripts handle the storage and loading of models to and from BucketFS. In the following, the implementation is discussed in further detail.
Queries Preprocessor Scripts
Scripting Programs
UDF Scripts
BucketFS Metadata
Scikit-Learn
For each algorithm supported by the framework, two UDF scripts need to be created. The ifrst UDF script handles model creation and training. For this purpose, the script takes the name of the model, settings for the algorithm, a list of features, and a label. The number of labels is only restricted in the current implementation of the framework. The script passes the settings, features, and labels to the algorithm, starts the training of the model, and finally stores the model in BucketFS. Since the currently implemented algorithms cannot process character strings as input or output, it is necessary to map these to integers. This is handled by the UDF scripts and a mapping dictionary. As an example, let us assume the key 'Software Engineer' is mapped to the integer value 1. On prediction, each input instance of 'Software Engineer' would also be mapped to 1. In the case of classification, each output instance of 1 would be mapped to 'Software Engineer'. Mapping dictionaries are created before model creation and stored alongside the model in BucketFS. In a future version of the framework, this mapping functionality will be replaced by in-database mapping tables.
As an example for creating a model using UDF scripts, we use the statement created by the preprocessor when processing the following CREATE MODEL statement.
CREATE MODEL "sal" ON employees PREDICT (salary) USING ("position", birthyear) WITH 'Function' = 'DecisionTreeRegressor', 'max_depth' = 64;
Since UDF scripts are ML-function-specific, the function to be used has to be determined before the execution. In our case, the function parameter set to 'DecisionTreeRegressor' means that the decision-tree-regressor function of the Scikit-Learn library is selected. The preprocessor script rewrites the CREATE MODEL statement into the following statement. SELECT ML.sklearn_tree_DecisionTreeRegressor_train
('sal', '{"model_params":{"max_depth":64}}', "position", birthyear, salary) FROM employees WHERE salary IS NOT NULL ORDER BY RANDOM();
The second UDF script handles prediction. The parameters of the script are the name of the model, settings, the row identifier, and the features used for predicting labels. The features passed to the prediction script have to match the number, position, and data type of the features which were used to create the model. The script loads the model and all associated mapping dictionaries from BucketFS and passes the features to the model for prediction. The predicted labels are then combined with the internal row identifiers by position and the set is returned. An important restriction of Exasol is that no other expression can be present in the SELECT clause when calling a UDF script that emits a table. To solve this problem, we use common table expressions.
As an example for prediction with a pre-trained model using UDF scripts, we use the statement created by the preprocessor when processing the following statement using the PREDICT function.
SELECT name, "position", birthyear, salary AS original_salary, PREDICT "sal" USING ("position", birthyear) FROM employees;
The prediction UDF script is determined using the stored model metadata. The preprocessor script rewrites the previous statement into the following statement.
WITH pred AS (SELECT ML.sklearn_tree_DecisionTreeRegressor_predict ('sal', '', ROWID, "position", birthyear) FROM employees) SELECT e.name, e."position", e.birthyear, e.salary AS original_salary, p.label AS salary FROM employees e JOIN pred p ON e.ROWID = p.identifier GROUP BY IPROC();
As already discussed, scripting programs combine the handling of metadata with the execution of ML functionality. The metadata of the framework could theoretically also be managed inside of UDF scripts, but this would necessitate the use of a database connector. This would defeat the purpose of executing ML in-database since an external connection to the database is needed. Scripting programs take the parameters extracted by the preprocessor scripts, as input. The preprocessor script takes incoming queries containing the custom syntax of our framework, splits them into tokens, and extracts the parameters of clauses of statements. In case a new ML model is created, the scripting programs choose the ML function to be used according to the settings the user provided in the WITH clause. If multiple ML functions fit the given settings, the function with the lowest priority value is selected. When training an ML model, all settings relevant to the model are passed to the UDF script. In case an existing ML model is needed, the scripting programs determine the function used to create the model through the metadata of the model. When executing predictions, the scripting programs use either Exasol’s internal row ID of the source table ROWID or the ROW_NUMBER function as the row identifier for data passed to the prediction UDF script. The GROUP BY IPROC() clause groups the rows by the node they are stored on. Thus, each row is processed locally on the node it is stored on and only the results are transmitted over the network.
The metadata of the framework is stored in two tables. The ML.Algorithm table contains information about the algorithms integrated into the framework, The information contained about algorithms includes their algorithm type, their output type, the module or library it is contained in, and the function it references to. The ML.Model table contains information about all created ML models created by the user. The information about models includes the algorithm used to train the model, its name, the source table or view, the features used during training, the labels used during training, and the settings used during training. This information is used for PREDICT or RETRAIN statements, for example.
Our framework is an extendable, no-code integration of ML into SQL while employing Exasol’s distributed, parallel processing capabilities and ETL pipeline in addition to Scikit-Learn’s flexible and sophisticated ML algorithms. The main contributions of the proposed framework lie in its seamless integration into SQL, scalability, and leveraging of existing database infrastructure. The integration of ML into SQL also benefits users familiar with SQL, such as data scientists, analysts, and database administrators. The framework enables them to leverage their existing SQL skills, making the transition to advanced analytics and ML more accessible. Furthermore, it is also possible to export and import models, since all ML models of the framework are stored in BucketFS.
However, certain restrictions need to be addressed. Firstly, the prediction phase currently uses UDF scripts. In future work, the prediction step will be changed to use preprocessor scripts. Other future work includes replacing the mapping directories with mapping tables in the database. Furthermore, enabling more than one possible label is also future work. The WITH clause is currently restricted to exclusively textual values. Moreover, when using a model the version of the used libraries has to match the versions of the libraries used for creating the model. This can be achieved by using the same script-language container that was used for the model creation. Currently, the user of the framework has to activate the correct script-language container for each model. The automation of this process is also future work. Future work also includes the extension of the framework with additional algorithms of the Scikit-Learn library and other ML libraries written in Python or other programming languages. Future directions for our framework include distributed training, incremental model training, sample weights, model statistics, explainability functions, and data preparation.
In comparison to other approaches, our framework stands out through its smooth SQL
integration. Furthermore, our framework has the advantage of employing the well-established
ML library Scikit-Learn. However, by employing third-party libraries, our framework has a
disadvantage compared to approaches implementing ML algorithms directly in SQL. Examples
of approaches like this are Apache MADlib [
In comparison to traditional ML approaches involving data movement between databases and separate analytics platforms, the proposed framework ofers advantages in terms of reduced data transfer, improved performance, and enhanced scalability. These advantages are all achieved by using the database as the singular platform for data storage and analysis.
This paper introduced a novel, extendable, no-code framework to integrate ML into SQL with Exasol. This framework bridges the gap between traditional SQL-based analytics and ML, empowering users to perform advanced analytical tasks directly within the database environment. We introduced Exasol, a high-performance, parallel-processing analytical database, and its features relevant to the framework. These features include scripting programs, preprocessor scripts, UDF scripts, script language containers, and the file system BucketFS. Furthermore, we discussed related work in the form of other approaches for ML with Exasol and other frameworks for in-database ML. The concept for the framework was introduced while discussing the syntax of the CREATE MODEL command for creating a new ML model and the PREDICT function for prediction using a pre-trained model in detail. The implementation of the framework consists of three layers: preprocessor scripts, scripting programs, and UDF scripts. Each layer provides a part of the complete functionality to translate incoming queries and execute the required ML functionality. Additionally, metadata for ML models is tracked in tables. The current restrictions of the framework and solutions were discussed. In comparison to other frameworks, our framework stands out with its seamless integration into SQL but is probably outshone regarding eficiency by frameworks re-implementing ML directly in the database. The main contributions of our framework are its seamless integration into SQL, scalability, and leveraging of existing database infrastructure.
The framework was initially created during the master’s thesis “Extending SQL for Machine
Learning” [