How to Use Big Query SQL Procedure
BQ SQL - create procedure without parameter
Create Procedure has no input or output parameter
CREATE OR REPLACE PROCEDURE `Test.sp_without_parameter`() begin select 'test'; end;
Run the no input, output procedure.
BQ SQL - create procedure with input and output parameter
Below script will do a simple calculation with x plus y and return the total.
CREATE OR REPLACE PROCEDURE `Test.sp_with_input_output` ( x int64, y int64, OUT total int64 ) begin set total = x + y; end;
This's how we can call the procedure to get the result.
declare total int64; CALL `Test.sp_with_input_output`(10, 20, total); select total;
Next, how to make Procedure return Array Type data?
In order for BQ Sql to return array like type, we can use Array with Struct.
First, let's create a procedure return the array custom type, in below example we want to return list of items contains id, country, device and browser fields.
As you can see the return type is OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>
CREATE OR REPLACE PROCEDURE `Test.sp_with_output_record`(OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>) begin create TEMP TABLE my_temp_table ( id int64, country string, device string, browser string ); insert into my_temp_table select 1 as id, 'US' as country, 'mobile' as device, 'safari' as browser union all select 1 as id, 'CA' as country, 'desktop' as device, 'chrome' as browser ; set items = array(select struct(id, country, device, browser) from my_temp_table); drop table my_temp_table; end;
Next, we'll calling the stored procedure to get the list of country, device and browser.
DECLARE items ARRAY<struct<id int64, country STRING, device STRING, browser STRING>> DEFAULT NULL; CALL `Test.sp_with_output_record`(items);
Last, we want to join the items with another table to get the result.
select * from unnest(items) as i inner join `Test.TestLookup` as t on i.id = t.id
The TestLookup table is a simple lookup table contains below values.
id | name 1 | my name
Above is how you can call BQ Stored Procedure to get custom Array type, this is useful so you can move long procedure into small ones for its own responsibility.