Parameters

Syntax

You can specify a parameter instead of any expression, value, or name in a query. First, define a parameter that can be used in various parts of the query by specifying its name, such as {date}. After that, you only need to reference the parameter by its name in subsequent calls. For example:

SELECT COUNT(*) number
FROM (
	TABLE_DATE_RANGE([githubarchive:day.events_],
	TIMESTAMP("{dateFrom default="2015-01-01" type="datetime"}"),
	TIMESTAMP("{dateTo default="2015-01-02" type="datetime"}")
))
WHERE type ="{event default='PushEvent' values='PushEvent,WatchEvent,ForkEvent' type='select'}"
LIMIT {limit_12 default="10" pattern="^[0-9]+$" type="input"}

The Types of Parameters

In the OWOX BI BigQuery Reports extension, you can use three types of Parameters. Required properties are marked with an asterisk (*) symbol.

Input field (default type)

Syntax example: {parameterName default="100" type="input" pattern="[0-9]*"}

• default * — the default value
• type — input
• pattern — a regular expression to check the value

Date and time

Syntax example: {parameterName default="2015-01-02" type="datetime"}

• default * — the default value
• type * — datetime

Select from the list

Syntax example: {parameterName default="PushEvent" type="select" values​="PushEvent,WatchEvent,ForkEvent"}

• default * — the default value
• type * — select
• values * — list of values separated by commas

Example of Using the Parameter as a Regular Expression Value

SELECT
  actor_attributes_location,
  COUNT(*) count
FROM `bigquery-public-data.samples.github_timeline`
WHERE
  TIMESTAMP(repository_created_at)
    BETWEEN TIMESTAMP('{DateFrom default="2012-04-01" type="datetime"}')
    AND TIMESTAMP('{DateTo default="2014-04-10" type="datetime"}')
  AND repository_language  LIKE '%{Language default='' values=',Java,PHP,Ruby,C++' type='select'}%'
  AND actor_attributes_location IS NOT NULL
  AND actor_attributes_location != ""
GROUP BY 
    actor_attributes_location
ORDER BY 
    count DESC
LIMIT {Limit default="10" pattern="^[0-9]+$" type="input"}

With the provided query example, you will receive four parameters, as shown on the screen below: