Creating a Command Template

You can create a new command template using the QDS user interface.

Perform the following steps to create a new template:

  1. Navigate to Templates and click New Template.

    Create Command Template is as shown in the following figure.

    ../../_images/CreateTemplate.png

  2. Enter a name in the Template Name text field. A system-generated ID is assigned to a created template after it is saved.

  3. In the Command text field, select a query type from the drop-down list that contains Hive Query selected by default. The following types of queries are supported by the command template.

    ../../_images/SupportedQueryTypes.png

    See Analyze for more information on composing different types of queries.

    While entering values for variables, modify the query as shown in the following example:

    • Consider modifying a sample query as select col1, col2 from table1 where col3="$var$" and set var to hello world for the final query to be select col1, col2 from table1 where col3="hello world".

    If the query statement is in an S3 location, select S3 Script Location from the drop-down list that contains <CommandType> Statement selected by default. Enter the S3 location that contains the script. Qubole now allows setting an S3 script location for Presto queries.

  4. In the Tags text field, add one or a maximum of six tags to group commands together. Tags help in identifying commands. Each tag can contain a maximum of 20 characters. It is an optional field.

  5. Set the parameters in Form Fields. Click + to add parameters for the query. For example, if you compose a query for a table that contains Month and Country as columns to get a result limited to the two columns, then Form Fields can be as as shown in the following figure.

    ../../_images/FormFields.png

    Enter a parameter’s name in the first text field and the value in the second text field. Click + to add more parameters for a query as necessary. While entering variables in template screen, escape the double quotes. That is, escape the double-quote that must be present in a string and wrap the entire string in double-quotes. For example, “ "hello world" “ gets substituted as select col1, col2 from table1 where col3= "hello world".

    Click the cross-mark icon CrossIcon for removing a form field.

    Click the double-arrow icon DoubleArrow to change the order of a form field by dragging and dropping it.

  6. Adding Macros is optional. To use macros in the query, click the +Add Macro button available in the Macros field. Else, proceed to the next step. After you click the +Add Macro button, the macros are displayed as shown in the following figure.

    ../../_images/Macros.png

    Enter the variable and expression and click Validate Macros for validating it. See Macros in Scheduler for more information. Click +Add Macro to add another macro. Else, proceed to the next step.

  7. Click Submit to add a new command template to the existing list if any.