google-cloud-platformgoogle-bigquerypivot

Is PIVOT Operator supported in Big Query Materialized view?


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


Solution

  • 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')
    );