Search

Exporting Data to a Database

The Export to Database task exports datasets or other Rulex structures to the specified database.

All export operations are performed in bulk mode, and are consequently extremely fast.

Prerequisites

a Rulex process has been created
the required datasets have been imported into the process.
the OBDC drivers (64-bit) corresponding to the database you want to export to, have been installed. Rulex supplies the required drivers for SQLite, SQL Server and oracle.

Additional tabs

The following additional tabs are provided:

Documentation tab where you can document your task,
Parametric options tab where you can configure process variables instead of fixed values. Parametric equivalents are expressed in italics in this page (PO).

Procedure

Drag and drop the Export to Database task onto the stage.
Connect a task, which contains the data you want to export, to the new task.
Double click the Export to Database task.
Configure the Database parameters as described in the table below.
Configure the Advanced options, as described in the table below, to specify any filters, or define which attributes will be exported.
Save and compute the task.

Parameter Name	PO	Description
Export to Database options
Database parameters
Platform	type	The database platform type. The following platforms are currently supported: Teradata MySQL Oracle SQLBase Impala SQLite PostegreSQL SQL Server Azure Data Warehouse IBM_DB2 Spark Unicode is also supported, if its corresponding driver is installed.
Server	server	The target database server IP address.
Port	port	The number of the specific process to connect to.
Database name	name	The name of the database you want to use (or the database file name if using SQLite).
Authentication	winauth	The method of authentication used to access the database. Possible values are: O (User Name/Password) 1 (Windows Authentication)
User name	usr	The login user name. This parameter is mandatory only when User Name/Password has been selected as the authentication mode
Password	pwd	The login password. This parameter is mandatory only when User Name/Password has been selected as the authentication mode.
Schema	schema	The database schema.
Driver	driver	ODBC driver.
Service name	service	Database service name of the Oracle database.
Bulk size for storing	bulksize	Retrieves blocks of data of the size specified.
Additional options	connparam	Additional parameters which may be required by ODBC drivers (according to database used) can be expressed through this edit box with the following format: option1=value1;option2=value2. The DSN value can also be inserted here, if present, with the following format: DSN=value.
Data to be exported	reflist	Select what type data you want to export from the list of options. Possible options are: Association rules (produced by Hierarchical Basket Analysis and Similar Items Detector tasks) Auto regressive models (produced by Auto Regressive task) Clusters (produced by Label Clustering, Projection Clustering and Standard Clustering tasks) Cluster Labels (produced by Label Clustering and Projection Clustering tasks) Datasets (all import, data preparation and data pre-processing tasks) Discretization cutoffs (produced by Discretize task) Frequent itemsets (produced by Frequent Itemsets Mining task) Frequent sequences (produced by Sequence Analysis task) Results (produced by all data analysis tasks) Rules (produced by LLM tasks (Classification, Regression & One-Class), along with Decision Tree and Regression Tree tasks) Models (produced by Logistic, Linear, Neural Networks (Classification and Regression) and SVM (Classification and Regression). PCA eigenvectors (produced by the Principal Component Analysis task) Relevances (produced by classification and regression tasks).
Table basename	basename	Enter a name for the database table that you want to create. If no name is specified, the name of the table will correspond to the upstream task in your process.
Advanced options
Insert mode	appendata	Specify how to manage data export operations when there is a data table with the same name already present. If there is a table with the same name, possible behavior can be: Insert (with drop) (0) - delete the existing table and recreate it with the data I am exporting Insert (append) (1) - add rows to the existing table containing the data I am exporting Update (2) - use data I'm exporting to update existing data, based on column matches specified by key attributes Insert/Update (3) - if there is a column match update the data in the existing table with the data I am exporting. If there is no match add rows to the existing table with the data I am exporting Delete (4) - the data in the existing table that matches the imported data, according to the specified key attributes, will be deleted.
Add increment primary key	autoinc, autoincname	If selected, this option adds a column which contains an incremental number, which can be used as a key attribute, when a table is created. If required you can define a name (autoincname) for the column in the corresponding edit box, otherwise a default name will be provided. This option is only available with Insert (drop). If the Add increment primary key option has been selected, the Key attributes edit box is disabled.
Use also missing values in conditions on key variables	usemissing	If selected, missing values in key attributes in the dataset lead to conditions on NULL. Basically this means that "missing" is a condition to be met, and not simply the absence of a value. Likewise, if not selected, any missing values will simply not be considered as conditions for any successive operations performed on the dataset.
Use input rules to determine conditions	userules	If selected, input rules are used to determine query conditions instead of key attribute values. These rules can be manually inserted in a Rule Manager task, which is then connected to the Export to Database task. This is particularly useful, for example, to define a range of values for a date or a range of continuous values. This option can be used with all Insert Modes which require conditions (Update, Delete, Insert/Update).
Key attributes	wherenames	Drag and drop the attributes you want to use as key attributes. This means that conditions for operations such as update or delete, will be applied to these attributes. If the Add increment primary key option has been selected, this edit box is disabled.
Exported attributes	valuenames	Drag and drop the attributes to be exported from the Available Attributes list on the left. If left empty, all attributes are exported.