Visual Condition Builder
This is the headliner feature of this version. Now it is possible to design filtering conditions in queries visually. Just drag a field from the list on the left or the preview datagrid, onto the filtering area.
Using a custom WHERE clauses is still possible.
For those who prefer writing SQL queries manually the Query Editor helps saving time -- design a draft query visually, then switch to Custom SQL and the visual query will be automatically converted to a SQL query which you can further modify and run right in EasyMorph.
In future releases, visual conditions won't be limited to queries. We are planning to introduce them in EasyMorph transformations as well in addition to formula-based conditions, where applicable.
Delete / update database rows
In the previous release (v3.3) we introduced the possibility to export (insert) rows into database tables. With this release we're adding two more transformations that allow deleting particular database rows, thus enabling delete/update workflows:
- "Delete database rows" transformation deletes rows specified by a filtering condition of query (column selections are ignored). Using a query in this transformation provides extra convenience as it allows previewing rows which will be actually deleted, before deleting them.
- "Delete matching database rows" transformation deletes database rows where key fields match key fields in specified EasyMorph table. Using this transformation you can calculate a list of particular IDs and then delete only database rows with these IDs.
Transformations for large data volumes
EasyMorph keeps all datasets in memory and this provides a multitude of benefits such as the ability to instantly see results of any transformation, reactive calculation, and re-calculating only transformations affected by a change. At the same time, this approach has a downside – if a dataset doesn't fit RAM entirely it would require using one of two special techniques to process it: partitioning and pre-filtering. Partitioning means that we split the source dataset into smaller parts of the same structure, which are processed one by one (typically using iterations). Pre-filtering means that instead of loading entire dataset and then filtering it, we only load a few fields that are necessary for filtering as well as IDs (primary of foreign keys). Filtering produces a subset of IDs, and for those IDs full records are fetched from the source dataset.
- "File splitter" transformation allows splitting (partitioning) large text files into smaller ones without loading them into EasyMorph. The smaller files can later be processed in EasyMorph one by one a usual way, typically using iterations.
- "Select matching database rows" transformation loads only database records which ID exists in an EasyMorph table. This transformation can be used for arranging scenarios with pre-filtering described above.
These two transformations allow EasyMorph users to work with tables larger than RAM -- tens and even hundreds of gigabytes.
Other new transformations and changes to existing ones
- New transformation: "Filter by search". Allows filtering text values by a substring, or using a regular expression.
- "Match" transformation now has Fuzzy Matching mode. In this mode text values are matched if their edit distance (as per Damerau-Levenshtein) is within a specified limit. Fuzzy Matching helps match words with typos. For instance it would match "New York" with "New Yrok" or "New Yokr".
- "Merge" transformation now has Full Join mode which is similar to SQL full join.
- "Export to Excel" transformation now allows creating new sheets in existing spreadsheets.
New Filter pop-up
- Selection – explicitly select items that should be kept (removed).
- Search – only items that contain particular substring are kept (removed).
- Condition – use expression to filter items. Only items where the expression returns TRUE are kept.
New data sources
- Added support for DB2 (LUW) SQL dialect in ODBC connections.
- Added support for Vertica SQL dialect in ODBC connections.
In the future release (3.6) we will continue improving usability and closing remaining gaps in functionality. It will include importing fixed width text, loading multiple files without iterations, and regular expression matching. Version 3.6 is planned for release in June this. However, before that there will be another, no less significant event in May – we're releasing the first ever version of EasyMorph Server!