Conditionally Importing Data from Databases

At times you may not want to import all the data found in a particular database query, but only a subset of it.

Rulex makes this possible through the Conditional Import task.

This task is the only Rulex import task that accepts input data. This data contains the filters you want to apply in the import operation.

Prerequisites

  • You must have created a flow;

  • You must have already imported the required data.

  • you must have chosen if you need a Saved or Custom source:

    • If you are creating a Saved source, go to the Explorer, switch the Flow filter off and select Add new Database by hovering over the plus button.

    • If you are using a Custom source, open the Import from Database task or the Conditional Import task.

  • You must have installed the ODBC drivers (64-bit) corresponding to the database you want to import from.

Procedure

  1. Drag the Conditional Import task onto the central stage.

  2. Double click the task.

  3. In the Database parameters section, configure the required parameters as described in the table below.

  4. Click Connect: a connection is established with the database and the Table list box is filled with the list of all the available tables which are presented in the database. 

  5. Configure the general options as described in the table below.

  6. Configure the advanced options, as described in the table below, to specify any filters, or define which attributes will be imported.

  7. Save and compute the task.

Conditional Import options

Parameter

Description

Source

Choose if you want to use a Saved or a Custom source.

Choose the database platform type. The following platforms are currently supported:

  • SQLite

  • Oracle

  • MySQL

  • SQL Server

  • PostegreSQL

  • IBM_DB2

  • IBM DB2 AS400

  • Azure Synapse Analytics

  • Impala

  • Spark

  • Hive

  • Teradata

  • OpenText Gupta SQLBase

  • Microsoft Access

  • SAP HANA

  • Generic ODBC Connection

Unicode is also supported, if its corresponding driver is installed.

Bulk size for prefetch

Retrieves blocks of data of the size specified.

Case sensitive

If selected, the search operation will be performed in case sensitive mode. 

Strip spaces

Select to remove spaces from strings. For example, the string ” age ” is then imported as “age”.

Use old computation data if the source table is not available

Select to use data from the previous computation if the source table is not available.

Repeat query execution every

Select to repeat the query until the required number of records are retrieved (checkdata).

Subsequently set the frequency in seconds (sleeptime) with which the query must be performed and the number of records (nres) that must be retrieved. 

Table preview

The preview of the imported table. You can choose how many rows to display in the Number of records to preview section.

Advanced options

Available attributes

The left-hand list displays all the available attributes, which can be used to filter results. These attributes can be ordered in the corresponding option below to narrow down the list when there are many attributes. It is also possible to search for specific attributes. 

Filtering attributes

Drag and drop the attributes you want to use to filter the imported results onto this pane from the Available Attributes list on the left.

The attribute names in Rulex must perfectly correspond to the attribute names in the resulting query.

 

Imported attributes

Drag and drop the attributes you want to import onto this pane from the Available Attributes list on the left.

If no attributes are specified, all attributes will be imported.

Connection Parameters

Parameters in bold are mandatory.

  • SQLite: Database Path, ODBC Driver, Additional Options.

  • Oracle: Host, Port, Service Name, Authentication (one of the following: Username/Password, Windows Authentication), Username, Password, Schema, Driver, Additional Options.

  • MySQL: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • SQLServer: Username, Password, Host, Port, Database Name, Schema.

  • PostegreSQL: Host, Port, Database Name, Authentication (one of the following: Username/Password, Postgre Pass File), Username, Password, Schema, Driver Additional Options.

  • IBM_DB2: Host, Port, Database Name, ,Authentication, Username, Password, Schema, Driver, Additional Options.

  • IBM DB2 AS400: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • Azure Synapse Analytics: Tenant, Port, Database Name, Authentication (one of the following: Username/Password, Windows Authentication), Username, Password, Schema, Driver, Additional Options.

  • Impala: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • Spark: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • Hive: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • Teradata: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • OpenText Gupta SQLBase: Host, Port, Database Name, Authentication, Username, Password, Schema, Driver, Additional Options.

  • Microsoft Access: Database Path, ODBC Driver, Authentication, Username, Password, Additional Options.

  • SAP HANA: Host, Port, Database Name, Authentication (one of the following: Username/Password, Windows Authentication), Username, Password, Schema, Driver, Additional Options.

  • Generic ODBC Connection: Host, Port, Database Name, Authentication (one of the following: Username/Password, Windows Authentication), Username, Password, Schema, Driver, Additional Options.

Search