Debugging BigQuery Stored procedure

user_10430120 0
Viewed 1376

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;
google-bigquery stored-procedures sql
1 Answers

There are number of approaches for debugging / troubleshooting stored proc

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;

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;

with output

enter image description here

As you can see here - you are missing apostrophes here where date=2020-11-24 so you can fix your stored proc

user_52219440
Related