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

user_15704676 · Answer

Have you try make the connection between Google Data Studio and BigQuery using the community connectors?
Using the community connectors you can trigger a stored procedure with parameters.

user_6085193 · Answer

Double check you connected correctly

You need to `create/add the parameter` in the `data source` or inside `Data Studio` via add parameter

In a data source

1. Sign in to Data Studio.
2. Edit your data source.
3. In the upper right, click ADD A PARAMETER.
4. You can also create a parameter while editing a report by selecting a component, then in the bottom right, clicking ADD A PARAMETER. Be sure to select a component based on the desired data source.
5. Configure the parameter's options and default value (see below).

Also, scrolling down on the page, you will see an example on how to do this with a sample calculation.

Calling Bigquery Stored Procedure from Google Data studio

2 Answers

You need to create/add the parameter in the data source or inside Data Studio via add parameter

You need to `create/add the parameter` in the `data source` or inside `Data Studio` via add parameter