CODEWITHSUNDEEP
sqlgoogle-cloud-platformviewgoogle-bigquery
John
John

Reputation: 59

Is there a way to use scripting methods in a saved view in BigQuery?

I am currently using a view in BigQuery to aggregate data from many different large tables. I then use this view to create a materialized flat table, with a MERGE statement to update it. However, the most recent LEFT JOIN I've added to the view caused the query that instantiates the materialized table to return the error: "Resources exceeded during query execution." The view right now is estimated to churn through 60GB of data.

To try to solve this issue, I tried using scripting to create temporary tables for the different subqueries in the view, thinking that this might save on resources. However, it appears that I am not able to save a view that uses scripting. Is there some way that this can be done?

Upvotes: 0

Views: 694

Answers (1)

guillaume blaquiere
guillaume blaquiere

Reputation: 75715

I assume that is not supported today. I had the errors weeks ago and I bet that is due to beta version.

For answering the comment, a very simple query

DECLARE dummy STRING;
set dummy="not work";
select dummy

This simply answers not work. Try to create a view from this, I have an error Syntax error: Unexpected keyword DECLARE at [1:1]. Not a data error, not a query error, simply not supported!

Using stored procedure not help because you use "script" command CALL for calling your stored procedure.

For information, there is a feature request on this

Upvotes: 2

Related Questions