Loading data from a database

EasyMorph supports loading data from a wide range of database types. There are two main methods for connecting to a database from EasyMorph:

  • Native Database Connector - where EasyMorph has built-in connection drivers for all the most common enterprise database types. You don't need to install any additional software.
  • ODBC (64-bit only) - where a third party ODBC driver is used to connect to the database. These are generally provided by database vendors and must be installed and configured separately from EasyMorph before they can be used. Due to EasyMorph using in-memory technology and memory limitations when using 32-bit ODBC connections, only 64-bit ODBC drivers are supported.
Watch a similar video in

Currently, EasyMorph natively supports the following database types:

Amazon RedshiftMicrosoft SQL ServerMariaDB
MySQLOraclePostgreSQL
SnowflakeSQLiteTeradata
DuckDB

Some examples of databases which you can connect to using a 64-bit ODBC driver would be:

Google BigQueryIBM DB2Apache Hive
VerticaExasolSAP HANA

Hint: Even if your database type isn't specifically mentioned above, as long as there is a 64-bit ODBC driver available for it (almost all do), then EasyMorph can connect to it. Install the driver, then create a "User DSN" or "System DSN" using Windows "ODBC Data Sources (64-bit)" tool. This connection will then be available to select using the "ODBC" type when creating a connector in EasyMorph.

There are 3 main steps required to load data from a database into EasyMorph:

  1. Create a connector to the database using the EasyMorph Connector Manager.
  2. Create an EasyMorph project (or open and existing project) into which you wish to load data from the database.
  3. "Add data" to your project and use the EasyMorph Query Editor to define the data you wish to load.

Database connectors

A database connector in EasyMorph is a set of connection properties (i.e. a server address, port, database name, access credentials, etc.) that are used to connect to a particular database. The properties that are required vary depending on the type of database being used. If at all unsure, your database owner or administrator should be able to provide the required settings.

For more information on how to create a connector to your database, see the Connectors and connector repositories tutorial chapter.

Query Editor

Once a connector has been created for the database, you can begin to load data from it into EasyMorph using the Query Editor. From within the Connector Manager, the Query Editor opens automatically when you click the "Add data" button. It can also be accessed at any time from within an EasyMorph workflow by selecting the "Add Data" button, clicking on "Query Database" and selecting the relevant database connector.

Add data from database

Hint: Wherever you are in EasyMorph, pressing the F8 key will take you straight to the Query Editor also.

There are three main areas in the Query Editor:

  • The column selector1 - Allowing you to select a table from the database, see the fields in the table and the data type and select which fields to be returned from the database.
  • The filtering condition(s)2 - Where you can visually build complex filters to reduce the data returned from the database.
  • The result preview3 - Showing you a preview of the data which will be returned by the query. This preview is limited to the top 100 rows by default but this can be changed if needed. The result preview can also be used to add additional modification to the query such as reordering columns, sorting the data by a column and aggregating the data (see below for more details).
Query editor

Using the Query Editor usually follows a simple process: select the required columns in the column selector1, create/edit filtering condition(s)2, and check the results in the result preview3. When everything looks good, press the "Import" button4 to load the query result into EasyMorph. A new table containing an Import from database action will be created automatically.

The most common tasks performed in the Query Editor are:

  • Reorder columns
  • Ordering the data (i.e. SQL "ORDER BY" statement)
  • Applying filters to the data (i.e. SQL "WHERE" statement)

As well as these simple tasks, the Query Editor is capable of creating complex SQL queries to import data from a database. More examples can be found in the Topics for power users below.

Reordering columns

Columns can be reordered in both the result preview and the data returned from the database. This can be done by right-clicking the column header in the result preview, selecting "Move" and then the direction you want to move the column (i.e. left, right, to the start or to the end). Alternatively, you can reorder columns quickly by selecting the "Reorder columns" button to the top right of the result preview.

Reorder columns button

You can use the arrows to move the columns up or down the list, or alternatively you can drag and drop columns to a new position in the list.

Reorder columns

Ordering the data

Columns can be reordered in both the result preview and the data returned from the database. This can be done by right-clicking the column header in the result preview, selecting "Sort" and then the direction you want to sort the column (i.e. ascending or descending).

Filter conditions

Filters can be added to reduce the data to only that which is needed. These queries can be incredibly complex, but can be entirely built without writing any code. Filters can be added in multiple ways:

  • Dragging a column from the column selector to the filter conditions.
  • Dragging a column from the result preview to the filter conditions.
  • Right-clicking on a column heading in the results preview and select "Filter…".
  • Clicking on the "Add condition" button at the top of the filter conditions, followed by "Condition with field".

All of these methods are very similar and which you use is a matter of personal preference. Let's look at using the Add condition button as an example.

Filter select field

We need to select the field we want to apply the filter to, followed by clicking on "Add". The filter will be added to the filtering conditions ready for us to configure. Firstly, we must specify the operator to use when applying the condition to the field.

Filter operator

The operators available will depend on the data type of the field and generally match those operators available in SQL languages:

  • Equals - where the field equals a specified desired value.
  • In list - where the field contains a value from a provided list of desired values.
  • Greater than - where the field contains a value that is greater than a specified value.
  • Less than - where the field contains a value that is less than a specified value.
  • Between - where the field contains a value that falls between two specified values.
  • Is null - where the field contains a NULL value.
  • Contains - where a specified piece of text can be found anywhere within the field value.
  • Starts with - where the field contains a value that starts with a specified piece of text.
  • Ends with - where the field contains a value that ends with a specified piece of text.
  • Is like - where the field contains a value that matches a provided pattern.

Each operator has its own configuration, although all are generally simple and similar. For operators which require you to provide a value to match the field against, you can specify one of several options:

  • A fixed value
  • A parameter from the current project,
  • The first value from a column resulting from the previous actions in the workflow table
  • NULL
Filter values

For the "Greater than", "Less than" and "Between" operators, there is an additional option to make them "Inclusive". If this checkbox is selected, the operator includes the specified value (e.g. "Greater than" becomes "Greater than or equal to").

Filter values

Filters can also be negated which attaches a logical "NOT" to the condition. For example, negating a filter which uses the "Equals" operator effectively turns it into "Not equal to". To negate a filter, right-click it and select "Negate" from the drop down menu.

Filter groups

When 2 or more filters are added, they are combined within groups. These groups apply the logical operands "AND" or "OR" between the filters affecting how they are applied. The below example shows 2 filters applied within a logical "AND" group and thus, both filters must apply to a record for it to be returned from the database.

Filter values

It is also possible to negate groups in the same way as described for filters above. The type of group can be changed between "AND" and "OR" by right clicking the group and selecting "Change type to…". Filters can also be moved in and between groups by dragging and dropping them to a new location.

Examples that use a database connector: Formula1, Inc5000.

Topics for power users

Custom SQL

Besides using visual queries, it is possible to import data from databases using custom SQL queries. This can be convenient in cases that can't be accommodated using a visual query — for instance when a join is required.

Switch Query Editor to the "Custom SQL" mode in order to edit a custom SQL query. You can drag table and field names from the list on the left into the custom query text.

Hint: For databases that have a 64-bit ODBC driver but are not officially supported by EasyMorph you can configure in EasyMorph an ODBC connector with the "Other SQL" dialect. The Query Editor automatically switches to the "Custom SQL" mode when a connector with the "Other SQL" dialect is used.

Loading matching database rows

Sometimes it's necessary to load only a subset of rows from a database table, and this subset is defined by a list of identifiers or keys (primary or foreign) in an EasyMorph table. Such a list can be loaded from another data source (e.g. spreadsheet), or be the result of complex transformation logic in EasyMorph. In this case, another action can be of help — Select matching database rows. This action matches key fields in an EasyMorph table and in a database table, and imports only database rows where the key fields match.

Select matching database rows

This action can also be used for cases when the source database table is too big to be entirely loaded into EasyMorph, but only a subset of its rows is needed. In such cases, only a few fields can be selected first. Typically, a key field and one or more data fields necessary for filtering. Then filtering is done in EasyMorph using the data fields in order to obtain a resulting list of keys. Finally, the "Select matching database rows" action is used to pull full rows (i.e. all columns) for the resulting list of keys.