Allow SQL Query In Separate File

The Script Snaps allow you the choice of either embedding your script into the snap as a string, or putting the script in a file object and then referencing the file object from the snap.

We would like the same facility for SQL queries in the “SQL Server - Execute” and “Generic JDBC - Execute” snap types!

Allowing this is slightly more tricky than what is offered in the Script Snaps, because it will require a way for the pipeline author to parameterize the queries… perhaps by just treating the file contents exactly as a string? (Such that the file contents would need to start and end with double quotes.)

This would have several strong advantages for us.

We are tracking revisions in Git. When scripts are embedded in snaps, the export format outputs the entire script as a single, possibly monstrously-long line in the exported JSON. This is not amenable to reviewing changes with “diff” tools. When scripts are stored as separate files, however, which are referenced by the snap, the exported JSON simply includes the file reference within the snap — also, the script file itself is exported separately with all newlines and line formatting intact. These can easily be diffed and inspected.

The SQL Execute snaps have no such option. Thus, on export, the JSON includes the query as a single long line string value containing escaped newlines and such. (i.e. the “\n” digraph.)

This feature would be a huge help to us.

Alternatively, if someone knows of a non-obvious trick that would allow us to write an expression in the SQL query field that would create a string from the contents of a file stored at Snaplogic, that would be useful, too!

You can use an expression library file to store your SQL queries for use in the Execute snap. Create and upload the expression library file sql.expr, import it into the Pipeline, make the SQL statement an expression and use lib.sql.query1 as the expression. You can still use parameterized fields within the statement, that is reference input parameters like $field1 in the SQL statement.

Obviously, we’re going to be learning more about expression libraries in our shop!

Thanks!! This sounds very much like what we’ve been looking for.