An introduction to parameterized queries and query templates.
Another piece of data.world-specific functionality is the ability to declare parameters for a query. Queries with parameters allow you to easily vary the results based on input values without changing the content of the underlying query.
Queries which have parameter values declared are often referred to as parameterized queries. In the data.world user interface, you’ll also see them referred to as query templates.
Parameters are defined at the beginning of a query using a declare statement.
Declare statements start with the keyword
DECLARE, followed by the name of
the parameter (starting with a question mark) followed by the type of the
parameter and an optional default value. The default value must be a literal
TIME. Once you’ve
declared a parameter, you can then use that parameter in your query, just as
though it were any other value.
Queries may declare multiple parameters. If a parameter is declared without
a default value, a value must be provided at run-time. If a parameter is
declared without a type, the type is assumed to be
In the CRM dataset we have been using, we have a table called
central_office_orders which lists every order taken by the central office.
A common thing to do with such data is to ask for the total orders placed by
a given account. For example, we might want to know the total orders for
"Rundofase". Here’s how we would do that with a non-parameterized
SELECT account, SUM(order_total) FROM central_office_orders WHERE account = "Rundofase" GROUP BY account
If we add a parameter for the account name we wish to know about, the query looks like this:
DECLARE ?account AS STRING = "Rundofase" SELECT account, SUM(order_total) FROM central_office_orders WHERE account = ?account GROUP BY account
Parameters with default values may be used as a way to separate out important constant values from the rest of your query structure, for easy modification.
The real value of parameterized queries isn’t just in helping to organize the constant values in your query. The real value comes from being able to override the default value of the parameter without modifying your query. See our blog post Expand your data’s reach using parameterized queries to find out more. The data.world workspace UI provides special functionality for working with query templates. If you type a parameterized query into the workspace query interface, you’ll see a button labeled “Preview query template”. This interface shows you a customized form for setting parameter values and running the query.
An introduction to the use of special columns in data.world custom SQL.