设置像Bigql这样的Big Query变量

what is the bigquery equivalent to mysql variables like?

什么是相当于mysql变量的bigquery？

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";

3 个解决方案

#1

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

BigQuery中没有要设置的“变量”，但您可以添加功能请求：https：//code.google.com/p/google-bigquery/issues/list？q = label：功能 - 请求

#2

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.

您可能需要考虑查看BigQuery的参数化查询。当使用用户输入构造查询时，BigQuery支持查询参数以帮助防止SQL注入。此功能仅适用于标准SQL语法。

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

#3

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

您可以使用WITH子句。这不是理想的，但它完成了工作。

-- 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

#1