Docs portal

Using 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's take a look at the Data Professional Salary Survey and examine the most popular databases from that survey in 2018. To do that, I 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 on line 3 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.

Finding the desired value buried in the query becomes far more challenging. What if we'd like someone with no knowledge of SQL to change the year and run the query without our assistance?

Wouldn't it be nice of they saw something like this instead?

query-template-02.png

We can do that by creating a query template.

Creating a query template

Creating query templates is possible in both SQL and SPARQL queries. In both cases, we'll need to create a variable to turn our standard query into a parameterized query (though creating a such a variable requires a different process in each). Then it's just one more click to create that graphical query template form.

SQL
  1. From within a project workspace, create a new SQL query

  2. Click the New template link on the top right of the query editor

  3. This brings up a new window that shows example code for adding a variable

  4. Click on the Add a sample statement button on the bottom left to insert this into your query

  5. Change the example values as needed

Referring to the previous example above, you would modify the sample statement to read:

query-template-03.png

In this case, 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

Additional DECLARE statements can be added to create more parameters in the query.

Using the variable in the query

Now that the variable exists, choose where the variable will be used in the query - this will often be in the WHERE clause of the query - though it could also appear in an ORDER BY, LIMIT, or other clause . In our example, we'll use it in the WHERE clause on line 5:

query-template-04.png

Note: This example is a bit complicated because the survey_year has the data type YEAR in our database, but we can't 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.

Adding Comments

Comments added before the DECLARE statement will display in the final query template form. The creator of the query can provide instructions and present valid input values.

Denote comments by adding the # or -- characters before the desired text:

query-template-05.png
Creating the query template

After declaring and referencing the variable(s), click the "Preview query template" link that now shows above the query editor:

query-template-07.png

This will create the graphical form where an end-user can enter the desired value for the variable. We'll now see that friendly form originally shown in the Overview section:

query-template-06.png

Creating this query template will lock the query from further edits. To unlock it to make a change, just click the Edit query link above. If another user owns the query, then you will need to save a copy of it after making any edits.

That completes SQL query template - but keep in mind that you can create additional variables to make a more powerful, flexible query.

SPARQL

The SPARQL syntax can be a bit more difficult to understand for those who are unfamiliar with it. For a great primer, check out our SPARQL tutorial!

To get started on our SPARQL query template, create a new SPARQL query within your project. We can mimic the SQL query above with the following SPARQL query:

query-template-08.png
Declaring a variable

Due to the syntax of SPARQL, a query will contain at least 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.

A new variable can also be declared by starting its name with with $ character.

In our query, we'll do the latter by creating a new variable $year_of_interest and replacing the value 2018 in the FILTER function on line 9.

query-template-09.png
Setting default values

In the form we just created, you may have noticed we did not see a default value populate.

Setting an optional default value for a variable is more complicated in SPARQL than SQL. To do that, we need to 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 case, let's go back into the editor and 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 will set the default value, that value will not be shown in the textbox in the query template form as it does in SQL. Instead it will be hidden from the end-user. Even though that value is not visible, the query will now use 2018 as the default year until another value is entered.

Adding comments

Comments can be added to a SPARQL query template as well. Only the # character (and not the -- characters that are available in SQL) can be used to specify a comment. The query template will show all comments before the first SPARQL statement (typically a PREFIX statement).

Since the query template won't show any default values, making a note of such values in the comments is a great idea.

query-template-11.png
Creating the Query Template

Clicking the Preview query template link above once more will generate our final template:

query-template-12.png

Just like the SQL version, this query will be locked from edits until the "Edit Query" link above is clicked.

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 our API docs for more information.

Sharing

Queries that are executed with a new parameter value may be shared with others by simply copying the URL and sending it to another user. If parameters have been filled in, then these parameters will be encoded into the URL for sharing.