The SQL Transform allows you to write a custom SQL statement to transform data.
Edge will automatically detect the usage of any other Pipeline Node table, and therefore modify the Pipeline topology to depict the new relationships.
It is strongly recommended that you use the SQL Transform when performing complex transforms or filters instead of manipulating data outside the Edge product so that your tacit knowledge of how the data is being manipulated is saved in your backups.
The “Choose a Transform” dialog will appear, showing the available transforms in the system. Select the “SQL Transform” option. This will launch the SQL Transform Wizard:
The SQL Transform Wizard has five steps.
Name of SQL Transform
This is where administrator can enter notes for the transform.
Choose one or more datasets that are needed for the SQL Code to access on the next wizard step. You must return to this step if you decide later to access another dataset in your SQL Code, as the editor will not allow you to access anything not chosen here.
This is where SQL for the transform is entered. The input provides syntax highlighting, as well as an “Insert” button for variables, and references to other datasets in the system.
There are some pre-configured functions that ship with edgeSuite, which can be useful when working with a SQL Transform:
See “Variables” for more information.
The transform utilizes the built in H2 Database, and therefore supports only the SQL Grammar supported by H2.
Quick Source Change
To quickly change the dataset being used in the SQL Code you can click on the source token to get a popup of all the available sources. Clicking the new dataset name will choose that as the new source.
This is an optional performance optimization step that can be skipped. To enable Indexing on an attribute, click the check box next to the attribute’s name.
Refer to the Indexing section for more information.
Details about this step can found here: Satisfy Upstream Node Variables Wizard Step
Use this step to preview the resulting data. You can use the Variables sidebar to adjust the values of variables passed into the query prior to save.
edgeSuite uses its bundled H2 database in support of the SQL Transforms. SQL that uses Common Table Expression (CTE) ‘WITH’ grammar (used to create temporary tables) is known to produce lock timeouts and also memory leaks. This has been observed to potentially create the following two issues:
There is a mode edgeSuite can be configured in that will eliminated the lock timeouts, but it will not eliminate the memory leaks. It can be applied by configuring the following cache db suffix in custom.properties:
The default suffix now includes “MULTI_THREADED=1;MVCC=TRUE”, which triggers the more performant mode of H2 but leads to the timeouts when using ‘WITH’ clauses.
Migration of existing ‘WITH’ clause SQL Transforms: