Trying to create a materialized view in Big Query using PIVOT operator as below
CREATE MATERIALIZED VIEW ****
PARTITION BY <COLUMN1>
AS
WITH TEST AS
(
SELECT * FROM (SELECT * FROM TABLE1 WHERE COLUMN1 between 'DATE1' and 'DATE2')
PIVOT (SUM(AMOUNT) AS AMOUNT, SUM(QUANTITY) as QUANTITY FOR METRIC_NM IN ('SALES'))
)
SELECT * FROM TEST ;
Below is the error I am facing where it says subquery or UDF is not suuported.
The query contains an feature that is not supported in incremental materialized views (possibly a subquery or UDF).
Is PIVOT not supported while creating a materialized view? I couldn’t find this for sure in Big Query documentation even in limitations of materialized view
PIVOT won’t work in a materialized view. Try to make a view on top of the subset materialized view.
CREATE MATERIALIZED VIEW test_materialized_view
PARTITION BY COLUMN1 AS
SELECT COLUMN1, METRIC_NM, AMOUNT, QUANTITY
FROM TABLE1
WHERE COLUMN1 BETWEEN 'DATE1' AND 'DATE2';
CREATE VIEW test_pivot_view AS
SELECT *
FROM my_materialized_view
PIVOT (
SUM(AMOUNT) AS AMOUNT,
SUM(QUANTITY) AS QUANTITY
FOR METRIC_NM IN ('SALES')
);
3