Community docs

About query templates

A query template creates a graphical interface that simplifies running a query with different parameters. This allows an end-user to easily change the values that a query uses without needing to access or understand the underlying SQL or SPARQL code.

As an example, let us take a look at the Data Professional Salary Survey and examine the most popular databases from that survey in 2018. To do that, you could use the following SQL query.

Screen_Shot_2019-07-18_at_3.38.20_PM.png

For those with a bit of SQL knowledge, modifying this query to get the results for the year 2017 is trivial. One would just need to change the year value from 2018 to 2017. But when a query involves JOINs, UNIONs, nested queries, and aliases, the number of lines in the query could go from 5 lines to 50 or more and finding the desired value buried in the query becomes far more challenging.

Creating a query template makes it easy for end users to access the information without worrying about editing a SQL or SPARQL query.

query-template-02.png
Creating SQL query templates

The steps in this section are explained using a sample dataset and query. This example only uses one variable but you can extend your query to use multiple variables making your query templates more roburst.

Steps include: Create a query, declare a variable, use the variable in the query, add comments to give instructions on using the template, and save the template.

To create a query template:

  1. Create a new SQL query.

    Screen_Shot_2019-07-18_at_3.38.20_PM.png
  2. Click the New template button in the query editor. It opens the Create a query template window with sample code. Click the Add a sample statement button to add the sample code in the query.

    window_create_query_template.png
  3. Modify the variable for use in the query or create your own variable. In this example, year is the variable name, INTEGER is the data type, and 2018 is the optional default value. If no default value is specified and the query is run without entering a value manually, the query will return an error. Allowed data types are: STRING, INTEGER, DECIMAL, BOOLEAN, DATE, TIME, and DATETIME.

    query-template-03.png
  4. Use the variable in the query. Choose where the variable is to be used in the query. This will often be in the WHERE clause of the query , although it can also be used in ORDER BY, LIMIT, or other clauses. In our example, we'll use it in the WHERE clause.

    Note

    This example is a bit complicated because the survey_year has the data type YEAR in the database, but we cannot declare a variable with that type. Instead, we declare our variable as a INTEGER and then used the CAST function to convert the value of survey_year from YEAR to INTEGER to make the comparison on Line 5.

    query-template-04.png
  5. Add comments above the Declare statement to provide instructions and present valid input values. Denote comments by adding the # or -- characters before the text. Comments added before the DECLARE statement display in the final query template form.

    query-template-05.png
  6. Click the Preview query template button to view and use your query template.

    query-template-06.png
  7. Click Save to save the query template in a project.

  8. Any time you want to make changes to the query or the template, click the Edit query link.

Creating SPARQL query templates

Steps include: Create a query, declare a variable, use the variable in the query, add comments to give instructions on using the template, and save the template.

To create a template for SPARQL query:

  1. Create a SPARQL query. Check out the SPARQL tutorial to learn about how to write SPARQL queries.

    query-template-08.png
  2. Declare a variable in the query. The SPARQL query contains atleast one variable by default. To turn that into a parameter that can be used in a query template, change the character in front of the variable name from to $. Make sure to perform this change for every occurrence of the variable within the query. Alternatively, declare a new variable by starting its name with with  the character.

    In our example, we will create a new variable $year_of_interest and replacing the value 2018 in the FILTER function.

    query-template-09.png
  3. To add a default value for a variable, add a conditional BIND statement on the first line of the WHERE clause.

    The general format of that statement is:

    BIND(IF(BOUND($variable), $variable, DEFAULT_VALUE) AS $variable)

    In our example, add a new line on line 7:

    BIND(IF(BOUND($year_of_interest), $year_of_interest, 2018) AS $year_of_interest)

    query-template-10.png

    Although this sets the default value, that value is not shown in the textbox in the query template form. Even though the value is not visible, the query will now use 2018 as the default year until another value is entered.

    Note

    Since the query template does not show any default values, it is recommended you add this information in the comment for the query so that users using the template are aware of it.

  4. Add comments to the query by using the # character. The query template shows all comments before the first SPARQL statement (typically a PREFIX statement).

    query-template-11.png
  5. Click the Preview query template link above to create the template.

    Note

    Note that the Preview query template link appears only when the SPARQL query is created properly with the variables required to create a template.

    query-template-12.png
  6. Click Save to save the query template in a project.

  7. Any time you want to make changes to the query or the template, click the Edit query link.

API

Parameterized queries can also be made through the data.world public API. Note that non-parameterized queries can be executed with a GET request to the same endpoint, but in order to specify the parameters, a POST request must be made with the parameters specified in the body of the request.

Example curl command:

curl -X POST \

https://api.data.world/v0/queries/${DW_QUERY_ID}/results \

-H 'Accept: application/json' \

-H 'Authorization: Bearer ${DW_API_TOKEN}' \

-H 'Content-Type: application/json' \

-d '{

"parameters" : {

"parameterName1" : "parameter value 1",

"parameterName2" : "parameter value 2" }

}'

See the API docs for more information.