This section documents how to use variables in apps. We start with the basics of using variables, followed by determining when to use bound or raw variables.
Recapping what we've learning from the Write SQL queries section: we can use
variables by encapsulating them in
}}. For example the following
query has a single variable
To add a variable to the app you can use the "Add Variable" button at the bottom
right of the screen as shown by the image below. In the query body it will
my_variable grey as it expects it to be a valid variable, although the
app has none!
Once the variable is added you see that the highlighting of
changes to green, indicating it's a variable. On the right side of the screen
you can fill in a number of properties of the variable (most of which are
optional). The properties are briefly discussed below, but first let's see how
Variables have the following properties.
- Name: the name of the variable to use when refering to them in the query body.
- Type: the type of the variable, see the Variable types section below for more information.
- Bind: whether or not to use SQL bound parameters, see the Bound or Raw? section below for more information.
- Label: friendly name for the variable. This will be shown to the user of the app instead of the variable name (which is limited by e.g. not allowing spacing). By default it's generated based on the name of the variable, but it can be modified if you can give it a better value.
- Default: The default value for the variable when running, essentially making the variable optional (as the default value will be used if the user provides no value for it).
- Example: an example value. This can be used to show how the value should be used. For example when the variable filters the results the default value could be no filter, but the example could filter based on the date.
- Description: short form description of the variable.
- Documentation: long form documentation of the variable, optional and supports Markdown.
- Type specific properties: additional properties that are specific to the type. See the Variable types section below for information about these properties.
All variable have a type so that we know how to process the value passed to it. Most types, such as text or number, speak for themselves, but not all of them do. In the following sections each variable type is discussed, where they can be used and what additional type specific properties they have (see variable properties in the previous section).
A boolean can be "true" (positive) or "false" (negative), the user has the option of one or the other. A boolean variable has no type specific properties.
A category variable allows the users of the app to select from a list of category options. As with variables the category options must also have a type, it has the same options as a variable except for the category type.
An example of using a category variable can be the sales report for a business that is active in different markets. By default the data would include all markets, but people can select a market (from a list of category options) to limit it to a certain market.
Why a category variable versusus a text variable? The benefit of a category variable over a text variable in this setting is that it prevents the user from selecting a non-existent market.
To edit the category options click on the "Edit options" button on the right side of the screen below the variable's name and type.
Once clicked you'll see the following screen appear which allows you to add and edit the category options. On the left side, under "Current Options" all existing options will be listed which be edited or delted. On the right new options can be added as highlighted by the red box in the image below.
A variable that consists of a year, month and day, colloquially known as a date.
As optional type specific properties it supports a lower and upper bound, forcing the value to be in a certain date range. This can be used to for example ensure that data is collected for the variable date input, no point in querying for data we don't have. You can set none, one, or both bounds.
A variable that consists of an hour, minute and second, colloquially known as a time variable.
Similar to a date a time variable has a lower and upper bound as type specific properties.
A combination of a date and a time (hence the name!). Different to date and time this type can optionally consider timezones by enabling the "Timezone-aware Datetime" option. Whether or not to enable timezone awareness depends on the data used.
As type specific properties datetime also support a lower and upper bound.
A number type can be an integer or a real number (also known as floating-point number). By default numbers will be considered real numbers, unless they explicited restricted to integers by enabling "Restrict to whole number" (below the variable name).
Similar to the date/time types number variables have an optional lower and upper bound as type specific properties.
A text variable is a sequence of characters (UTF-8) that make up readable text, such as the sequence of characeters you're reading right now.
As for type specific properties, you can set the minimum and maximum input length.
Let's start with the most important advice first: when in doubt use "Bound" as it's the safest option. When using "Raw" it's a lot easier to break stuff. Now, let's learn what variable binding is and when to use what kind.
When using "Bound" variable binding it means that the variable's value will be used as a query parameter, or a "bound parameter", to the SQL query. Generally, it's considered best-practice to do this as it allows the SQL database (BigQuery in our case) to cache the query plan and optimise it further on repeat runs. Additionally, because "Bound" inputs are not actually treated as part of the query itself, a whole class of malicious SQL injection attacks on the database is prevented.
However, bound variables have limitations as to where they can be used. For example they can't be as identifiers, such as column or table name. To use a variable column name see the Using variables as columns section below. For more limitations see the BigQuery documentation on query parameters.
If you encounter a situation where a "Bound" variable doesn't work, such as using
it as a column name, then you can opt to use a "Raw" variable. Raw variables are
injected without modifications (i.e. "raw") into the query. The content of
the variable input is simply substituted in the query without modifications.
This allows us to do very powerful things, such as having complex
clauses or dynamicly choosing the columns to return, but also has it's pitfalls.
For example if you have a text variable then the contents likely needs to be
encapsulated in qoutes (
'), but you can't be sure the input doesn't contain a
qoute character itself! When opting to use a "Raw" variable always be extra
careful and let the user know how to use the variable properly through a good
description and documentation. Better yet, you can balance the power of "Raw"
variable with the safety of a Category variable with a list of "Raw"
options (coming soon!) that you've tested yourself.
Finally, in-between "Bound" and "Raw" we have "Bound or Raw", which allows a variable to "Bound", but when needed allows a "Raw" input from the user (when running the app). The option allows us to choose the safe option by default ("Bound"), but provides an escape hatch for situations that don't work with "Bound" variables.
Some app might want to return different columns depending on the user input,
i.e. a variable. In those cases it's not possible to use a "Bound" variable as
it will consider it a text string as shown by the app below. You can set the
bound_col value to any string that you like and the app will return a column with
that input repeated for each row.
Instead, if we want to choose the column to return
we need to use a "Raw" variable that is interpreted as an identifier and used as a
column name. In the app,
raw_col has a default value of
country. Try running the app
to see what happens. What happens if you try to change
raw_col to a name that isn't a
column in the toy dataset (
country; see the Body tab for more details on how
we created the table)? You can check out the Documentation tab in the app below for more
details and exercises to help you understand the differences in behaviour and functionality
between "Bound" and "Raw" variables.
As mentioned in Bound or Raw? above, you will soon able to support column selection with variables by creating a "Raw" category variable with valid, selectable columns as "Raw" category options.