BigQuery stored procedure access through simba jdbc driver

Question

All, I am trying to execute a stored procedure in google bigquery using a jdbc connection but having no luck with it.

I followed the example here https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures.

I am able to create and call the stored procedure from bigquery web ui but when i set up a data source in intellij using the simba driver as explained here https://blog.jetbrains.com/datagrip/2018/07/10/using-bigquery-from-intellij-based-ide/

Then when i run the call to execute the stored procedure

DECLARE target_employee_id INT64 DEFAULT 9;
DECLARE employee_hierarchy ARRAY<INT64>;
dataset.GetEmployeeHierarchy(target_employee_id, employee_hierarchy);
SELECT target_employee_id, employee_hierarchy;

i get an error

[HY000][100032] [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Unrecognized name: target_employee_id at [1:8] com.simba.googlebigquery.support.exceptions.GeneralException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Unrecognized name: target_employee_id at [1:8]

As the stored procedure support is in pre-release beta, I am wondering if the Simba driver supports it as of now? If not are there any other jdbc drivers that do currently?

I'm guessing it simply doesn't support it yet. The release notes show the last release being in March 2019: cdn.simba.com/products/BigQuery/doc/… — Graham Polley
Cool, seems like it. Ended up reporting an issue in Google issues tracker — sramalingam24
Hi, if this is your google issue please can you update this post? — Gonzalo Pérez Fernández

sramalingam24 sramalingam24 · Accepted Answer · 2020-03-05T03:05:55

So turns out all the 4 statements need to be executed as a single Bloc not as individual statements. In intellij you can highlight all those statements and hit run to execute it and return the results. In Java just pass them as a single string in statement.execute.

BigQuery stored procedure access through simba jdbc driver

1 Answers