Calling Bigquery Stored Procedure from Google Data studio

Question

Any idea how to call bigquery stored proc from Google data studio? I've a recursive query requirement which I've accomplished using Bigquery procedure. However need to pass parameters to this procedure from datastudio report and get results for the same and display in Data studio report. I have tried calling the procedure from Custom Query in Data studio no luck. Datastudio throws error. I tried calling procedure from a function however it doesn't work. ANy idea? How do I do I call procedure from Google Data studio?

Procedure that is written

DECLARE stop INT64 DEFAULT 30;
    DECLARE v_target string;
    DECLARE v_target_column_name string;
    DECLARE max_counter INT64;
    DECLARE min_counter INT64;
    DECLARE v_exists bool;
    SET v_target = 'v_target';
    SET v_target_column_name = 'v_target_col';
    create or replace table test.poc_sttm_resp as
    select ROW_NUMBER() OVER() as counter,'N' as flag,source,source_column_name,target,target_column_name from test.test_sttm where target = v_target and target_column_name = v_target_column_name;
    LOOP
    SET max_counter = (select max(counter) from test.poc_sttm_resp);
    SET min_counter = (select min(counter) from test.poc_sttm_resp where flag = 'N');
    SET v_exists = EXISTS(select s.source from test.test_sttm s inner join
      (select source,source_column_name from test.poc_sttm_resp where counter =  min_counter
      ) r
      on s.target = r.source and
      s.target_column_name = r.source_column_name);
      IF stop = 0  OR min_counter is null THEN
        LEAVE;
      END IF;
      IF v_exists then
      insert into test.poc_sttm_resp
      select ROW_NUMBER() OVER() + max_counter as counter,'N' as flag,s.source,s.source_column_name,target,target_column_name from test.test_sttm s inner join
      (select source,source_column_name from test.poc_sttm_resp where counter = (select min(counter) from test.poc_sttm_resp where flag = 'N')) r
      on s.target = r.source and
      s.target_column_name = r.source_column_name;
      END IF;
      update test.poc_sttm_resp set flag = 'Y' where counter = min_counter;
        SET stop = stop - 1;
    END LOOP;

I tried calling the procedure from Data Studio, call functions.testproc(); which didn't work

Answer 1

I've encountered this issue recently and this is a "workaround"/solution I found that worked for me.

You can create a table function that does the same thing as your stored procedure and still accept parameters (for example a date) to be used inside the query in the function.

Afterwards (still sticking with the date parameter) in DataStudio(LookerStudio at the time of writing) you create a custom query and use the DS_START/END_DATE parameter and a date control to pass in a date range for your query (if you need that)

The reason for using a table function is that in the custom query you can just write:

SELECT * FROM yourtablefunction(@DS_START_DATE, @DS_END_DATE)

And this will work just like a View or Table in DataStudio.

Some useful links that can help anyone reading this:

Calling Big Query Stored Procedure From GDS

BigQuery Table Functions Docs