Importing Data from a Database

To load datasets coming from databases you can set a connection using the Import from Database task.

Through this task you can create connections to databases in the Custom Source panel: the connections created will become resources in the current Environment.

If the Flow Filter is switched off, you can visualize the database and filesystem connections made not only through the Factory, but also through the Studio.

Tables can be imported from the following databases:

  • SQLite

  • Oracle

  • MySQL

  • SQL Server

  • PostgreSQL

  • IBM_DB2

  • IBM DB2 AS400

  • Azure Synapse Analytics

  • Impala

  • Spark

  • Hive

  • Teradata

  • OpenText Gupta SQLBase

  • Microsoft Access

  • SAP HANA

  • Generic ODBC Connection

Rulex's technical documentation does not and cannot provide comprehensive guidelines on the use of third-party software, beyond how Rulex integrates with this software. Please consult the technical documentation of the third-party software itself for up to date information.

Prerequisites

  • You must have created a flow.

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

Procedure

  1. Drag an Import from Database task onto the stage.

  2. Double click on the task.

  3. Select whether you want to use a Saved or a Custom source.

  4. Select from the drop down list the database type.

  5. Click on the pencil button to add the database path and optional information (only if you are using a Custom source).

  6. Click Confirm.

  7. Select the tables to import and configure the general options in the table below.

  8. Save and compute the task.

You can decide to import more than one file or folder of files by clicking on the other ones to import while pressing ctrl on your keyboard.

General options

Parameter

Description

Source

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

Then, choose the database type from the ones that are listed above.

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.

Specifying a DSN in the Generic ODBC Connection

If you have already connected your machine to any database, whether it is software-supported or not, you can specify the DSN in the Generic ODBC Connection database type and the DSN name in the Additional Options pane.

This procedure allows you to quickly connect to databases without the need to insert again their details.

Search