CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Raphael Obadia
Raphael Obadia

Reputation: 445

Bigquery generate dynamic view

I want to create a view to consolidating data that are in different tables. For each year a new table appears ( I have no control over it) and the view has to fetch the data in this new table. I can't modify/update these tables

the query view for one table (2022) :

with cte as (SELECT d.MULTIDIVISION_ZONE, s.ISO_COUNTRIES[OFFSET(0)] Country, d.DIVISION_CODE, BRAND, d.MULTIDIVISION_REGION,
    SUM(CASE
      WHEN PERIOD IN ('M01', 'M02', 'M03') THEN VALUE_AVERAGE_RATE
      ELSE 0
    END) YTD03,
    SUM(CASE
      WHEN PERIOD IN ('M01', 'M02', 'M03','M04', 'M05', 'M06') THEN VALUE_AVERAGE_RATE
      ELSE 0
    END) YTD06,
    SUM(CASE
      WHEN PERIOD IN ('M01', 'M02', 'M03','M04', 'M05', 'M06','M07', 'M08', 'M09') THEN VALUE_AVERAGE_RATE
      ELSE 0
    END) YTD09,
    SUM(CASE
      WHEN PERIOD IN ('M01', 'M02', 'M03','M04', 'M05', 'M06','M07', 'M08', 'M09','M010', 'M11', 'M12') THEN VALUE_AVERAGE_RATE
      ELSE 0
    END) YTD12
  FROM `dataset_source.table_name_prefix_2022_AGGREGATED` AS d
  JOIN `master_dataset.master_data_table` AS s
    ON s.MULTIDIVISION_CLUSTER_CODE= d.MULTIDIVISION_CLUSTER_CODE
  WHERE CODE = 'XXXXX'
  GROUP BY d.MULTIDIVISION_ZONE, Country, d.DIVISION_CODE, BRAND, d.MULTIDIVISION_REGION)


select MULTIDIVISION_ZONE as perimeter, sum(sales) as value, quarter from cte  
UNPIVOT(sales for quarter in (YTD03, YTD06, YTD09, YTD12))
GROUP BY MULTIDIVISION_ZONE, quarter
UNION ALL
select brand, sum(sales), quarter from cte  
UNPIVOT(sales for quarter in (YTD03, YTD06, YTD09, YTD12))
GROUP BY brand, quarter
UNION ALL
select MULTIDIVISION_REGION, sum(sales), quarter from cte  
UNPIVOT(sales for quarter in (YTD03, YTD06, YTD09, YTD12))
GROUP BY MULTIDIVISION_REGION, quarter

my goal is to create a view from this query but dynamic.

the From clause: dataset_source.table_name_prefix_2022_AGGREGATED so for 2023, 2024,2025 ... so on but does not exist yet (dataset_source.table_name_prefix_2023_AGGREGATED does not exist yet) and I can't use * (wildcard) because there are views on the same dataset and this is bigquery limitation From documentation

The wildcard table functionality does not support views. If the wildcard table matches any view in the dataset, the query returns an error. This is true whether or not your query contains a WHERE clause on the _TABLE_SUFFIX pseudo column to filter out the view.

What is the best option(s) in order to achieve that? Thank you

Upvotes: 0

Views: 1013

Answers (1)

Shipra Sarkar
Shipra Sarkar

Reputation: 1475

For your requirement, you can use table functions or table valued functions that return a table and are similar to views. It is different from views in a way that it can take parameters. You can call a table function for any valid table. For every year, you can try to loop over the years using the Python Client Library or you can pass the year as a parameter. For second use case of creating a view, you can try that using the python client library and try to call the table functions from that view.

Upvotes: 1

Related Questions