我需要查詢由必需的transactionDate欄位磁區的表。
我想只在多個日期過濾我的資料以避免恢復大量資料，但我有這個錯誤：Query error: Cannot query over table t without a filter over column(s) 'transactionDate' that can be used for partition elimination
我的代碼：
DECLARE dates ARRAY < DATE >;
SET
dates = (
SELECT
ARRAY_AGG(DISTINCT(DATE(transactionDate)))
FROM
t
WHERE
transactionDate >= "1900-01-01"
AND analyticsUpdateDate BETWEEN "2022-05-01"
AND "2022-05-10"
);
SELECT * FROM t WHERE transactionDate IN UNNEST(dates)
如何在不查詢整個表的情況下管理此錯誤？
謝謝你的幫助。
uj5u.com熱心網友回復：
dates為空時，transactionDate IN UNNEST(dates)條件為false，查詢將回傳錯誤：
如果沒有對可用于磁區消除的列“transactionDate”的過濾器，則無法查詢表“bqml_tutorial.t”
DECLARE dates ARRAY <DATE>;
-- Sample table
CREATE SCHEMA IF NOT EXISTS testset;
CREATE OR REPLACE TABLE testset.t PARTITION BY (transactionDate) OPTIONS (require_partition_filter = true) AS
SELECT n, p AS transactionDate, p AS analyticsUpdateDate
FROM UNNEST(GENERATE_ARRAY(0, 9)) n WITH OFFSET
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY('2022-04-01', '2022-04-10')) p WITH OFFSET USING(OFFSET)
;
-- Your Query will retrun an error because `dates` is empty.
SET dates = (
SELECT
ARRAY_AGG(DISTINCT(DATE(transactionDate)))
FROM
testset.t
WHERE
transactionDate >= "1900-01-01"
AND analyticsUpdateDate BETWEEN "2022-05-01"
AND "2022-05-10"
);
SELECT * FROM bqml_tutorial.t WHERE transactionDate IN UNNEST(dates);
簡單的解決方法是IS NOT NULL在磁區列上添加條件，如下所示。
SELECT * FROM bqml_tutorial.t
WHERE transactionDate IS NOT NULL AND transactionDate IN UNNEST(dates);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/475220.html