Hello.
I have a case where I need to filter data using a view and a partition column.
Table:
CREATE TABLE `testing-442412.test_dataset.Product`(
Id INTEGER NOT NULL,
UserId INTEGER NOT NULL,
CompanyId INTEGER NOT NULL,
DateCreated DATETIME NOT NULL
) PARTITION BY DATE_TRUNC(DateCreated, MONTH) CLUSTER BY UserId, CompanyId;
View:
CREATE VIEW `testing-442412.test_dataset.ProductView` AS
SELECT
UserId,
CompanyId,
EXTRACT(YEAR FROM DateCreated) AS Year,
EXTRACT(MONTH FROM DateCreated) AS Month,
COUNT(DISTINCT Id) AS Total
FROM `testing-442412.test_dataset.Product`
GROUP BY
UserId,
CompanyId,
Year,
Month
I WANT TO FILTER MY AGGREGATED DATA USING QUERY LIKE THIS:
SELECT * FROM `testing-442412.test_dataset.ProductView` WHERE Year = 2024 AND Month = 1;
However, when I do this, I notice that partition pruning doesn't seem to be working, as many records are read in the execution details.
How can I improve my view in this case? Does partition pruning even work with the EXTRACT function? Or do I need to use the DateCreated column directly without any manipulation, with a query like this:
SELECT * FROM `testing-442412.test_dataset.Product` WHERE DateCreated BETWEEN DATETIME("2024-01-01") AND DATETIME("2024-01-30");
I don't really want to force my clients to use table Product directly, so I hope you will be able to help me.