Create/Query different tables based on conditional statement in BQ SQL

Question

To illustrate with an example, say I have two tables in BigQuery called "fruits" and "vegetables" respectively.

I want to have a variable at the start that lets me change the names of the destination table and the source table. Something like:

food_type = 'fruit';

Then based on this, the query will pull data from a matching table and store it in a matching table:

IF food_type = 'fruit' 
THEN destination_table = 'round_fruits' AND source_table = 'fruits'
ELIF food_type = 'veg' 
THEN destination_table = 'round_veg' AND source_table = 'vegetables';

CREATE TABLE `project.dataset`.destination_table AS
SELECT * FROM `project.dataset`.source_table
WHERE shape = 'round';

In this case, I am looking to only have to assign the food_type at the beginning and not change anything elsewhere when I want to run this query. I realise that IF statements in BQ SQL don't work like this but I hope this illustrates my point and what I want to accomplish.

Answer 1

If you want to assign food_type value one time and want use that then you can try below code -

declare food_type string default 'fruit' ;
declare destination_table string;
declare source_table string;
IF food_type = 'fruit' 
THEN set destination_table = 'round_fruits' ;
set source_table = 'fruits';
end if;
IF  food_type = 'veg' 
THEN set destination_table = 'round_veg' ;
set source_table = 'vegetables';
end if;
EXECUTE IMMEDIATE concat("CREATE TABLE `project.dataset`.",destination_table," AS SELECT * FROM `project.dataset`.",source_table
," WHERE shape = 'round'");

If you want use this query multiple time you can create a store procedure in Big Query with all the conditions and call that store procedure whenever it is needed -

CREATE OR REPLACE PROCEDURE `project.dataset.conditional_store_proc_test`(food_type string)
begin
declare destination_table string;
declare source_table string;
IF food_type = 'fruit' 
THEN set destination_table = 'round_fruits' ;
set source_table = 'fruits';
end if;
IF  food_type = 'veg' 
THEN set destination_table = 'round_veg' ;
set source_table = 'vegetables';
end if;
EXECUTE IMMEDIATE concat("CREATE TABLE `project.dataset`.",destination_table," AS SELECT * FROM `project.dataset`.",source_table
," WHERE shape = 'round'"); 
end; 

And then you can call the store procedure with the value -

DECLARE food_type STRING DEFAULT NULL;
set food_type='fruit';
CALL `project.dataset.conditional_store_proc_test`(food_type);
set food_type='veg';
CALL `project.dataset.conditional_store_proc_test`(food_type);