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 a variable, and the value for these will be determined dynamically at runtime. As a very simplistic example, you could have a query like this:
SELECT * FROM foo LIMIT $limit$
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 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.