调试BigQuery存储过程 [英] Debugging BigQuery Stored procedure
问题描述
有什么方法可以在BigQuery存储过程中使用打印语句吗?我有一个类似下面的存储过程,我想看看如何生成SQL语句来调试问题,或者通过其他更好的方法来调试正在生成的存储过程等.
Is there any way I can use print statements within BigQuery stored procedure? I have a stored procedure like below, I like to see how SQL statement is generated to debug the issue or any other better way to debug what stored procedure is producing etc.
CREATE OR REPLACE PROCEDURE `myproject.TEST.check_duplicated_prc`(project_name STRING, data_set_name STRING, table_name STRING, date_id DATE)
BEGIN
DECLARE sql STRING;
set sql ='Select date,col1,col2,col3,count(1) from `'||project_name||'.'||data_set_name||'.'||table_name|| '` where date='||date_id ||' GROUP BY date,col1,col2,col3 HAVING COUNT(*)>1';
--EXECUTE IMMEDIATE (sql);
print(sql)
END;
推荐答案
有许多方法可以对存储过程进行调试/故障排除
There are number of approaches for debugging / troubleshooting stored proc
最简单的方法之一-查看SQL语句的生成方式
-稍微调整存储的过程,如以下示例所示
One of the simplest - to see how SQL statement is generated
- slightly adjust your stored proc as in below example
CREATE OR REPLACE PROCEDURE `myproject.TEST.check_duplicated_prc`(project_name STRING, data_set_name STRING, table_name STRING, date_id DATE, OUT sql STRING)
BEGIN
-- DECLARE sql STRING;
set sql ='Select date,col1,col2,col3,count(1) from `'||project_name||'.'||data_set_name||'.'||table_name|| '` where date='||date_id ||' GROUP BY date,col1,col2,col3 HAVING COUNT(*)>1';
--EXECUTE IMMEDIATE (sql);
END;
然后,您可以在下面运行以查看生成的SQL
Then, you can run below to see generated SQL
DECLARE sql STRING;
CALL `myproject.TEST.check_duplicated_prc`('project_name', 'data_set_name', 'table_name', '2020-11-24', sql);
SELECT sql;
有输出
正如您在这里看到的那样-您在这里缺少撇号 where date = 2020-11-24
,因此您可以修复存储的proc
As you can see here - you are missing apostrophes here where date=2020-11-24
so you can fix your stored proc
这篇关于调试BigQuery存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!