Skip to content

Data Source SQL Fields

The sql and sql-file fields offer methods for injecting inline SQL statements or loading SQL files during node execution.

  • sql field: Use for SQL queries and importing SQL files. The value is pre-compiled as the first step in data source execution during node processing.
  • sql-file field: Use for referencing SQL resource files. The value is compiled when the step containing this field begins execution, after previous steps have been completed.

These fields are supported within step definitions in source.json files for database sources in action nodes, DataMapper nodes, and container nodes. They are not supported in file-based or system sources and are not supported for data sinks.

sql-file example

Tip

As this field is not compiled until the moment of step execution, it's useful for using runtime variables that were generated during the processing of a previous step within a node.

This example uses an action node with a Redshift data source and an SQL file in the recipe's resources directory.

  1. During execution, step1 sets the runtime variable result_of_first_step to the resulting value.
  2. step2 executes the inline SQL query embedded in the source.json file.
  3. step3 uses the load_text option to access the test.sql resource file.
  4. step4 uses the sql-file method to access the test.sql resource file.

source.json

{
    "name": "source",
    "type": "DKDataSource_PostgreSQL",
    "config-ref": "redshift_connection_variable",
    "keys": {
        "step1": {
            "sql": "SELECT COUNT(*) FROM xyz",
            "query-type": "execute_scalar",
            "set-runtime-vars": {
                "result": "result_of_first_step"
            }
        },
        "step2": {
            "sql": "SELECT {{result_of_first_step}}",
            "query-type": "execute_scalar"
        },
        "step3": {
            "sql": "{{load_text('test.sql')}}",
            "query-type": "execute_scalar"
        },
        "step4": {
            "sql-file": "test.sql",
            "query-type": "execute_scalar"
        }
    }
}
 

/resources/test.sql

SELECT {{result_of_first_step}}
 

pre-compiled source.json

{
    "name": "source",
    "type": "DKDataSource_PostgreSQL",
    "config-ref": "redshift_connection_variable",
    "keys": {
        "step1": {
            "sql": "SELECT COUNT(*) FROM xyz",
            "query-type": "execute_scalar",
            "set-runtime-vars": {
                "result": "result_of_first_step"
            }
        },
        "step2": {
            "sql": "SELECT null",
            "query-type": "execute_scalar"
        },
        "step3": {
            "sql": "SELECT null",
            "query-type": "execute_scalar"
        },
        "step4": {
            "sql-file": "test.sql",
            "query-type": "execute_scalar"
        }
    }
}

When the data source is executed, source.json gets pre-compiled and step2 and step3 compile before step1 can generate its variable value. As step processing continues, step2 and step3 execute as SELECT null rather than calling the variable.

step4 compiles at the time of its execution after step1 has created the variable result, so it compiles as expected and uses that value.