0

Does anyone know how to invoke a Big Query Stored Procedure from GDS? When I try the following, I get an error:

CALL mydataset.get_member_value('Hi');

Sorry, we encountered an error and were unable to complete your request. Syntax error: Expected end of input but got keyword LIMIT at [1:55] Error ID: 78495f50

The same call string works in Big Query directly. Ultimately I would like to pass in a non-hard coded parameter. e.g. an @parameter from GDS into a BigQuery stored procedure.

Hrag
  • 11
  • 1
  • Hrag, [edit] with data, details & supplementary images so it's self contained ([Example](https://stackoverflow.com/q/72565176)) with 1) Data: ~9 rows of Inputs ([Markdown Table](https://meta.stackoverflow.com/a/403501)) with Sample Data set (Google: Sheets, Analytics, etc) 2) Expected output table 3) Chart: Configuration + Setup 4) Issue: Attempt at solving + Output / Error 5) Report: Publicly editable Google Data Studio with 1-4. Without a [mre] it would be difficult to test a suggestion as the issue could be [general troubleshooting](https://support.google.com/datastudio/faq/7219787) – Nimantha Oct 03 '22 at 17:19
  • 1
    I don't think this is a data issue or needing of an example. Choose any Big Query stored procedure it doesn't matter what it does and see if you can call it from Google Data Studio. I can't seem to figure that out. – Hrag Oct 03 '22 at 17:25
  • CREATE OR REPLACE procedure `reporting-302901.MyTest.get_member_value`(parm1 string) BEGIN DECLARE id STRING; SET id = GENERATE_UUID(); SELECT FORMAT("Created customer %s",parm1); END /* if I were to run this call statement in big query it will work CALL `reporting-12345.MyTest.get_member_value`('Hi'); */ – Hrag Oct 03 '22 at 17:26
  • This call in Big Query works .. but that same call statement through GDS returns a the LIMIT at [1:55] Error ID – Hrag Oct 03 '22 at 17:27

1 Answers1

0

I wasn't able to find a reference in the documentation about this topic, but I don't think this is possible. Execution of Stored Procedure are far more complex than querying tables/views (e.g, Stored Procedures don't have a schema).

However, you can make a schedule to run your Stored Procedure from time-to-time and store the results in a regular table. Then just reference it from Google Data Studio.

Other option if you do not need to run advanced code in your Store Procedure would be to turn it into a View (or a Materialized View). Google Data Studio can reference a View with no problems (however, regular tables are much more faster).

I did not test, but maybe you can reference a Table Function from Google Data Studio. If you can re-write your Stored Procedure as a Table Function, it worth a try.

Diego Queiroz
  • 3,198
  • 1
  • 24
  • 36
  • Thank you for answering. Your suggestions are great but unfortunately won't help because I need to pass in an argument into the stored procedure from GDS. So making it a cron job or MV can't be done because the resultant set would be based on the parameter that the user is selecting. Table function works I had tried that but I need the power of a stored procedure to manipulate the data. – Hrag Oct 04 '22 at 21:37
  • If you can simulate all possible parameters you intend to use, maybe you can make a giant table with all options in a column and just query it. Despite being odd, BigQuery surely supports and shine in this kind of usage. – Diego Queiroz Oct 06 '22 at 16:04