设置 Big Query 变量，如 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29759628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting Big Query variables like mysql
提问by Louisa Scheinost
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";
采纳答案by Elliott Brossard
You can now use variables in BigQuery. To run the statements that you provided, you need to use DECLARE:
您现在可以在 BigQuery 中使用变量。要运行您提供的语句，您需要使用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.
另请参阅脚本文档。
回答by user1699188
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
回答by Felipe Hoffa
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:Feature-Request
回答by thsr
Here is a solution using a user defined function. Declaring variables and calling them looks more like Mysql.
这是使用用户定义函数的解决方案。声明变量并调用它们看起来更像Mysql。
You can call your variables by using function var("your variable name")this way:
您可以通过var("your variable name")这种方式使用函数来调用变量：
#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:
如果您的变量不是字符串，请将其设置为字符串，使用 var 调用它并将其安全转换为您的类型：
#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)
回答by Bongsky
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
https://cloud.google.com/bigquery/docs/parameterized-queries
