Allow passing parameters to external query inside stored procedure in BigQuery [154388185]

Assigned

Feature Request

Status Update

No update yet.

Description

ju...@gmail.com

created issue #1

Apr 18, 2020 09:28PM

It would be nice to be able to pass parameters to external query inside stored procedure in BigQuery.

Something along the lines

CREATE OR REPLACE PROCEDURE dataset.my_procedure(param1 STRING)
BEGIN
    SELECT * FROM EXTERNAL_QUERY("my-external-connection", """
      SELECT * FROM external_table WHERE column1=:param1
    """) as external;
END;

It's of course possible to move WHERE clause outside, but this would mean that the whole table is read into BQ temporary table. In case of big indexed external table we lose any benefit of index, and it will also incur a large cost.

Currently EXTERNAL_QUERY function requires literal string as an argument, so it's not possible to create it dynamically inside stored procedure.

Comments

bl...@google.com <bl...@google.com> Apr 18, 2020 09:35PM

Assigned to as...@google.com.

my...@google.com <my...@google.com> Apr 19, 2020 08:35AM

Reassigned to my...@google.com.

my...@google.com <my...@google.com> Apr 21, 2020 09:49PM

Reassigned to de...@google.com.

de...@google.com <de...@google.com> #2Aug 28, 2020 10:20PM

Reassigned to ar...@google.com.

th...@google.com <th...@google.com> Sep 3, 2020 08:38PM

Reassigned to em...@google.com.

bw...@google.com <bw...@google.com> Sep 12, 2021 04:31PM

Reassigned to pv...@google.com.

[Deleted User] <[Deleted User]> #3Oct 27, 2021 02:06PM

Comment has been deleted.

Message last modified on Nov 1, 2021 01:01PM

bw...@google.com <bw...@google.com> Apr 24, 2022 06:31AM

Reassigned to dd...@google.com.

dd...@google.com <dd...@google.com> Apr 26, 2022 08:23PM

Reassigned to cr...@google.com.

cr...@google.com <cr...@google.com> Nov 28, 2022 05:34PM

Reassigned to ja...@google.com.

ad...@firstdollar.com <ad...@firstdollar.com> #4Jul 11, 2023 10:13PM

Note that there is somewhat of a workaround to this posted in this Stack Overflow comment, https://stackoverflow.com/a/66011037/1075909, which involves concatenating strings together and then calling EXECUTE IMMEDIATE. Obviously that is a bit "gross", e.g. for one it makes it really easy to expose SQL injection bugs, which is the whole thing parameters are designed to prevent in the first place. Also, unfortunately that workaround doesn't work for Looker Studio, because Looker Studio can only execute a single statement in a custom query. So BigQuery folks, would just highlight that this issue makes it nearly impossible to use Looker Studio with EXTERNAL_QUERY because there is no way to pass parameters (e.g. from Looker Studio filters) down through to the underlying EXTERNAL_QUERY.

va...@google.com <va...@google.com> Sep 4, 2023 09:02AM

Reassigned to va...@google.com.

va...@google.com <va...@google.com> Oct 4, 2023 06:48AM

Reassigned to gc...@google.com.