CODEWITHSUNDEEP
google-bigquery
p13rr0m
p13rr0m

Reputation: 1297

Creating a View in BigQuery from Temporary Function and Dynamic SQL

I want to create a view dynamically with a string generated by a temporary function. The code below fails with Creating views with temporary user-defined functions is not supported. How can I create the view without permanently registering the function?

CREATE TEMP FUNCTION CreateViewString()
  RETURNS STRING
  AS (("select 1 as col"));

EXECUTE IMMEDIATE "CREATE OR REPLACE VIEW `project.dataset.view` AS " || CreateViewString()

Upvotes: 0

Views: 1416

Answers (2)

Kabilan Mohanraj
Kabilan Mohanraj

Reputation: 1916

I'm posting this answer for posterity. This is a known issue that the BigQuery team is aware of and improvements have been under consideration. You can also STAR the issue to receive automatic updates and give it traction by referring to this link. Suggested workarounds are as follows.

  1. Drop the UDF before view creation, this will prevent the script from attaching the UDF in the last statement. (mentioned in the answer by @p13rr0m)
  2. If the view is not related to the UDF or temporary table execution, run them as separate queries.

Upvotes: 1

p13rr0m
p13rr0m

Reputation: 1297

As a workaround one can store the result of the function and then drop the function.

DECLARE viewString STRING;

CREATE TEMP FUNCTION CreateViewString()
  RETURNS STRING
  AS (("select 1 as col"));

SET viewString = CreateViewString();

DROP FUNCTION CreateViewString;

EXECUTE IMMEDIATE "CREATE OR REPLACE VIEW `project.dataset.view` AS " || viewString

Upvotes: 1

Related Questions