Save Time Executing Hive Queries Using Command Templates
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 are basically parametrized 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.
How to Use QDS Command Templates
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.
- Words enclosed in dollar ($) symbols in the text of a template command is a variable, and the value for these will be determined dynamically at run-time. As a very simplistic example, you could have a query “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.
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 make 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 macro (or form field) 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. To run the command, you only need to provide inputs for the form fields and then run it. 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.