Exporting Data to a Database

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.