BigQuery with clause in view

Question

currently I'm working on a query which looks like following

WITH subquery1 AS ( 
   SELECT ...
   FROM rawdata
   WHERE DATE(_PARTITIONTIME) = "2021-03-09"
),
subquery2 AS ( 
   SELECT ...
   FROM subquery 1
),
subquery3 AS ( 
   SELECT ...
   FROM subquery1 join subquery2 
)
SELECT * FROM subquery3

In my actually query i have 5 subqueries all building up on the previous ones. The query is working perfectly fine and I would like to have it as a view so I can use it in other queries.

Is there a way I can control the partition filter in the first subquery when calling the view? In some cases I just want to have one week, in other cases several months.

score 0 · Answer 1 · answered Mar 09 '21 at 10:47

Unfortunately you can not parameterize a View on BigQuery, but you can write a Python Script and you can parameterize you BigQuery SQL. A sample code is given below, where i am consuming several input to my SQL and processing it as per input values: -

from dateutil.parser import parse
import datetime, sys
from google.cloud import bigquery
from datetime import datetime, timedelta

Dataset_Table_name = sys.argv[1]
coll_name = sys.argv[2]
date_ip = sys.argv[3]
no_day = sys.argv[4]

arch_date = (datetime.strptime(date_ip, '%Y-%m-%d') - timedelta(int(no_day))).strftime('%Y-%m-%d')

stream_query = """DELETE FROM `ikea-itsd-ml.""" + Dataset_Table_name + """` WHERE """ + coll_name + """ < '""" + arch_date + """'"""

print (stream_query)

stream_client = bigquery.Client()
stream_Q = stream_client.query(stream_query)
stream_data_df = stream_Q.to_dataframe()

BigQuery with clause in view

1 Answers1