Loading data from a database

Briefly, loading data from a database works as follows:
  • Create a database connector using the Connector Manager
  • Query the database using the Query Editor — pick table, columns, provide a filtering condition, etc.
  • Press the Import button in the Query Editor to load query result into EasyMorph

Database connectors

A database connector in EasyMorph is a set of connection properties (e.g. server address, port or database name) that are used to connect to particular database. See below example of database connector.

Connectors can be of of two types: shared (default type) and embedded.

Shared connectors are stored in a repository. The repository is a file that can be located anywhere (e.g. in a network folder). Therefore shared connectors in the repository can be used by multiple users in multiple projects. On the first launch EasyMorph automatically creates an empty repository (AppData\Local\EasyMorph\repo.sqlite) if no repository is present. You can switch between different repositories, although EasyMorph works with only one repository at a time. Projects refer to shared connectors by name. Therefore if you're using the same project with two different repositories (e.g. production and development) make sure that connectors referred by a project have same names in both repositories.

Embedded connectors are stored within the project that uses them. Therefore if the project is opened on a different computer it will use exactly the same connection settings. Embedded connectors can be used in cases when a project needs to be portable or shareable. Note that EasyMorph includes native drivers for some popular databases, therefore projects with embedded connectors that use native drivers work out of the box, without database client software installed.

Supported database types:

Database Driver type
Oracle ODBC / Native
SQL Server ODBC / Native
MySQL ODBC / Native
PostgreSQL ODBC / Native
SQLite ODBC / Native
Amazon Redshift ODBC only
DB2 ODBC only
Vertica ODBC only
Generic SQL:2003 dialect ODBC only
The native drivers are built into EasyMorph and don't require installing anything else. If your database type is not listed above but it has an ODBC connector then limited connectivity is still possible: create an ODBC connector and specify the "Other SQL" dialect in it, query the database by writing custom SQL in the Query Editor.

Connector Manager

Connectors and repositories can be created and edited using the Connection Manager. It can be accessed from the Start screen, or in menu Project, or in Project Overview sidebar, or by pressing F7.

Connector manager

Operations that can be performed using the Connector Manager:

  • Create, edit and delete embedded and shared connectors
  • Create new repositories
  • Switch between repositories
  • Copy connectors between repositories and projects

Hint: it is possible to protect a shared repository from accidental change by unqualified users using a write password. Note that users will still be able to use (read) all connectors in the repository even if it's protected with a password. To protect a repository from unauthorised access use standard Windows file permissions applied to the repository file.

Query Editor

Once you have created a database connector, you can start querying the database using the Query Editor. The Query Editor opens automatically when you click the "Add data" button and then select "Query database".

Add data from database

Alternatively, the Query Editor can be invoked by pressing F8.

There are three main areas in the Query Editor: column selector (1), filtering condition (2), and result preview (3).

Query editor

A typical workflow is as follows: select columns, create/edit filtering conditions, and preview query results. To create a filtering condition drag a field name onto the condition area. When everything looks good, press the "Import" button (4) to load the query result into EasyMorph. An "Import from database" action will be created automatically.

Video: Visual Query Builder

Other operations that can be performed using the Query Editor:

  • Double-click column header in the preview datagrid and see/select unique values in that column.
  • Group filtering conditions into AND and OR groups. Negate conditions.
  • Drag groups and conditions to re-arrange them. Right-click groups and conditions for more options.
  • Annotate groups and conditions.
  • Use project parameters in conditions.
  • Create SQL expressions to use database functions. Such expressions can also be used in filtering conditions.
  • Provide a filtering condition using SQL WHERE clause syntax (remember that SQL syntax depends on database type)
  • Sort by one or more columns (right-click the column header, choose Sort)
  • Re-order columns (drag, or right-click the column header and choose Move)
  • Load only Top N rows
  • Remove duplicate rows (i.e. keep only distinct rows)
  • Save queries, open saved queries
  • View auto-generated SQL, copy it into the clipboard
  • Write custom SQL queries

Examples that use a database connector: Formula1, Inc5000.

Advanced topics

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 accomodated using a visual query — for instance when a join or aggregation 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.

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). Such list can be loaded from another data source (e.g. spreadsheet), or be a 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 tables, and imports only database rows where the key fields match.

This action can also be used for the cases when the source database table is too big to be loaded into EasyMorph entirely, but only 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 in order to pull full rows (i.e. all columns) for the resulting list of keys.

Read next: Profiling data