A common characteristic of many analytics queries is that they are mostly invariant in form and function. Over multiple invocations of the query or command, one would find that only the range of inputs varies in the form of a couple of inputs, while the major part of the query remains the same.
Command templates in QDS have basically parameterized commands designed to effectively use this attribute to your advantage. Until now, to run the same command again with different inputs, you had to edit it, search for the fields in the (possibly huge) command and then modify them. With command templates, you can spin out write-once-run-several-times commands where you only have to pass in a different set of parameters every time you run them. Let’s get down to how to use them.
Command templates are available as a new option in the sidebar, which leads you here. As you can imagine, the first thing we need to do is create a new command template. But first, a little digression on what makes templates, templates, and not plain old commands – variables. Any word enclosed in dollar ($) symbols in the text of a template command is variable, and the value for these will be determined dynamically at runtime. As a very simplistic example, you could have a query like this:
1 | SELECT * FROM foo LIMIT $limit$ |
and run it repeatedly with various values for the limit. We have two types of variables –form fields and macros. Form fields are the inputs that you provide when you run the query. limit in the above example was a form field. Macros are dynamically determined but do not need to be provided as input when you run the query – typically they would be evaluated using simple JavaScript code.
Now, let’s get back to template creation – the command template creation page brings us the composer window from QPAL that we all know and love, with a couple of additions.
The first of these is the Find Variables button. This automatically finds all the variables in your command (which, as you may recall, is anything inside ‘$’ symbols) and puts them in the Form Fields section. It even works for workflows with multiple commands of different types. Easy, right?
Form fields, as we mentioned before, are the values you get to fill in when running a template. Here you can specify the default value for each field (if the field is a string, you must specify the default value in single quotes). Form fields can also use JavaScript code, e.g. the default value of a field can be ‘foo’ + ‘bar’, or even ‘foo’ + baz, where baz is a previously declared form field (the order of fields in the evaluation is important – if a field uses another, it must be lower in the list than the field it uses.)
Macros don’t have default values, but just values or formulae. These can, of course, use JavaScript code just like the form fields; and they can use the form fields and other macros as well. We have included the momentjs library for easy date and time calculations. The example template above uses momentjs methods to get the current month in the macro.
Form fields and macros are the magic sauce that makes templates as useful as they are. For example, consider the following templated query:
insert overwrite directory ‘s3://dev.canopydata.com/wiki_pagecounts/tm eou p/monthly/$yesterday$‘
select cast(concat(‘$yesterday_trunc$’, cast(page_id as string)) as bigint), ‘$yesterday$’, page_id, monthly_trend, total_pageviews, ‘$yesterday_time$’, ‘$yesterday_time$’
In the templated case, you can easily create one yesterday variable (using momentjs for convenience) and derive yesterday_trunc and yesterday_time from it. So instead of filling in five values every time you want to run the command with a different yesterday, you only need to provide it once (or not at all!). Now consider the template advantage if the values were repeated not just 5 times, but 10 or more times!
Once you have created the template, all that’s left is to run it and view the results. To run the command, you only need to provide inputs for the form fields and then run it on the page below.
You can then click on the history button to view the results. That’s it!
This concludes our little introduction to command templates in QDS. We hope you enjoy this feature and find it useful.
