Setting Big Query variables like mysql

Question

what is the bigquery equivalent to mysql variables like?

SET @fromdate = '2014-01-01 00:00:00',  -- dates for after 2013
@todate='2015-01-01 00:00:00',

@bfromdate = '2005-01-01 00:00:00', -- dates for before 2013
@btodate = '2005-01-01 00:00:00',

@achfromdate  = '2013-01-01 00:00:00', -- dates for ACH without submit time in 2013
@achtodate  = '2013-01-01 00:00:00',

@currency="USD";

Our [add-on for running BQ queries in Google Sheets](https://chrome.google.com/webstore/detail/owox-bi-bigquery-reports/fepofngogkjnejgcbdmmkddnpeojbbin) allows you to set parameters for your queries and re-run them editing just values of preset parameters. — Max Ostapenko, Apr 21 '15 at 16:20

score 63 · Accepted Answer · answered Oct 03 '19 at 23:09

You can now use variables in BigQuery. To run the statements that you provided, you need to use DECLARE:

DECLARE fromdate TIMESTAMP DEFAULT '2014-01-01 00:00:00';  -- dates for after 2013
DECLARE todate TIMESTAMP DEFAULT '2015-01-01 00:00:00';

DECLARE bfromdate TIMESTAMP DEFAULT '2005-01-01 00:00:00'; -- dates for before 2013
DECLARE btodate TIMESTAMP DEFAULT '2005-01-01 00:00:00';

DECLARE achfromdate TIMESTAMP DEFAULT '2013-01-01 00:00:00'; -- dates for ACH without submit time in 2013
DECLARE achtodate TIMESTAMP DEFAULT '2013-01-01 00:00:00';

DECLARE currency STRING DEFAULT "USD";

You can use variables in statements after declaring them, e.g.:

DECLARE fromdate TIMESTAMP DEFAULT '2014-01-01 00:00:00';  -- dates for after 2013
DECLARE todate TIMESTAMP DEFAULT '2015-01-01 00:00:00';

SELECT FORMAT('From %t to %t', fromdate, todate);

See also the scripting documentation.

score 59 · Answer 2 · edited Jul 06 '18 at 04:53

59

You could use a WITH clause. It's not ideal, but it gets the job done.

-- Set your variables here
WITH vars AS (
  SELECT '2018-01-01' as from_date,
         '2018-05-01' as to_date
)

-- Then use them by pulling from vars with a SELECT clause
SELECT *
FROM   your_table 
WHERE  date_column BETWEEN
          CAST((SELECT from_date FROM vars) as date)
          AND
          CAST((SELECT to_date FROM vars) as date)

Or even less wordy:

#standardSQL
-- Set your variables here
WITH vars AS (
  SELECT DATE '2018-01-01' as from_date,
         DATE '2018-05-01' as to_date
)
-- Then use them by pulling from vars with a SELECT clause
SELECT *
FROM your_table, vars 
WHERE date_column BETWEEN from_date AND to_date

edited Jul 06 '18 at 04:53

Mikhail Berlyant

165,386
8
154
230

answered Jul 06 '18 at 04:09

user1699188

589
1
4
3

this is what I was looking for! – nipunasudha Jan 21 '21 at 17:05
This is a awesome solution. I am using DataStudio with BigQuery and this solve the problem that in the query you cannot have multiple sentenses, otherwise you will get an error. – Arst Feb 10 '21 at 05:37

score 5 · Answer 3 · answered Apr 21 '15 at 00:39

5

There are no 'variables' to be set in BigQuery, but you could add a feature request: https://code.google.com/p/google-bigquery/issues/list?q=label:Feature-Request

answered Apr 21 '15 at 00:39

Felipe Hoffa

54,922
16
151
325

+1 I would use vars if they were available, too. However, I suppose if the vars are for datetimes, I could make a view, and pass the dates into the view conditions, couldn't I? – Kristian Oct 15 '15 at 22:43
3

mmh, still no vars, but Datalab has a cool interaction with BigQuery queries and variables in python: https://cloud.google.com/datalab/ – Felipe Hoffa Oct 15 '15 at 23:01
we use the python vars currently. – Kristian Oct 15 '15 at 23:28
currently you can declare variables, so this answer is not up-to-date anymore (but a good suggestion at that time) – Hedge92 Oct 20 '21 at 13:32

score 3 · Answer 4 · answered Dec 08 '17 at 08:53

You may want to consider looking into BigQuery's Parameterized Queries. BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. This feature is only available with standard SQL syntax.

https://cloud.google.com/bigquery/docs/parameterized-queries

score 3 · Answer 5 · edited Oct 25 '18 at 16:06

Here is a solution using a user defined function. Declaring variables and calling them looks more like Mysql.

You can call your variables by using function var("your variable name") this way:

#standardSQL
-- Set your variables here
CREATE TEMP FUNCTION var(str STRING)
RETURNS STRING
LANGUAGE js AS """
  var result = {
    'fromdate': '2014-01-01 00:00:00',  // dates for after 2013
    'todate': '2015-01-01 00:00:00',

    'bfromdate': '2005-01-01 00:00:00', // dates for before 2013
    'btodate': '2005-01-01 00:00:00',

    'achfromdate': '2013-01-01 00:00:00', // dates for ACH without submit time in 2013
    'achtodate': '2013-01-01 00:00:00',

    'currency': 'USD',

    'minimumamount': '3.50',

    'default': 'undefined'
  };
  return result[str] || result['default'];
""";
-- Then use them by using the function var("your variable name")
SELECT *
FROM your_table
WHERE date_column BETWEEN var("fromdate") AND var("todate")

If your variable is not a string, set it as a string, call it with var and safe_cast it to your type:

#standardSQL

CREATE TEMP FUNCTION var(str STRING)
RETURNS STRING
LANGUAGE js AS """
  var result = {
    'minimumamount': '3.50',
    'default': 'undefined'
  };
  return result[str] || result['default'];
""";

SELECT *
FROM your_table
WHERE amount > safe_cast(var("minimumamount") AS FLOAT64)

score 1 · Answer 6 · answered Jun 26 '23 at 11:31

@Elliott's answer is great. However, if someone wishes to set a variable with a value of a certain expression (as opposed to a hardcoded value), then SET should be used along with DECLARE.

Below is an example of declaring dynamic dates.

DECLARE start_date, end_date DATE;

SET (start_date, end_date) = (
 DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY), 
 DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
);

SELECT start_date, end_date;

Reference: bigquery documentation

Setting Big Query variables like mysql

6 Answers6

Linked