|
<< Click to Display Table of Contents >> ETL > 14.0 > Implementation Guide > Tutorial and implementation guide > Programmability > SQL environment Creating reusable scripts |
To promote usability and enable easier testing of SQL expressions, it is possible to encapsulate SQL snippets and store them separately from the JSON definition.
Once the ETL engine loads the ETL definition, it looks for the file which has the same file name as your JSON definition but with extension .SQL. If it finds it, it uses its content in all sessions created for evaluation of SQL expressions. Writing scripts in that file is much easier than inline expressions in the JSON file, because of support for new lines, lack of necessity to escape special characters.
The format of the .SQL file is not a standard SQL though. Its content should be of the following syntax, where each function is defined like:
def <MacroName>(@<param1>, @<param2>, ...)
<macro-body>
For example, the following is a custom file which contains two macros
def capitalize(@val):
UPPER(substr(@val, 1, 1)) + LOWER(substr(@val, 2))
def isNullOrEmpty(@text):
@text IS NULL
Parameter names must always start with ‘@’ sign. The body can span over several lines, and it must be a valid SQLite syntax. You can reference the parameters, also using the ‘@’ syntax. In this example, macro capitalize takes a value (@val) and returns a capitalized version of it. The other one isNullOrEmpty returns 1 or 0, depending on whether the parameter is null.
In order to use the macro in your JSON code, prepend its name with ‘!’ and followed by the list of arguments, for example:
{
'sql': !capitalize(name)'
}
Macros support column names and expressions. Bear in mind, that commas (,) have a special meaning, they separate the arguments. This is why the following will fail:
{
'sql': !capitalize(SELECT col1, col2 FROM table)'
}
Instead, enclose the expression with braces to indicate that the commas belong to the argument.
Macros can be called recursively (a macro calls another macro or even itself). Bear in mind that ETL engine limits the number of recursion levels.