Can create a procedure in BigQuery

[Stanislav]

Hello!

I have the error during creating procedure and can't understand why.

[ms4446 · Accepted solution]

The error message you're seeing is related to querying a partitioned table in BigQuery without specifying a filter on the partitioned column. BigQuery requires a filter on the partitioned column to ensure that only relevant partitions are scanned, which improves query performance and reduces costs.

In your procedure, the table sandbox.open_orders_minute_metrics_v2 seems to be partitioned on the column calculation_timestamp. When querying or manipulating this table, you need to include a filter on the calculation_timestamp column.

Here's a revised version of your code snippet with the necessary filter:

CREATE OR REPLACE PROCEDURE `sandbox.load_open_orders_minute_metrics_v2`(start_time_ TIMESTAMP, time_period INT64, time_interval INT64)
BEGIN
BEGIN TRANSACTION;

DELETE FROM `sandbox.open_orders_minute_metrics_v2`
WHERE calculation_timestamp >= (start_time_ + INTERVAL time_period HOUR)
AND calculation_timestamp < CURRENT_TIMESTAMP; -- This is the filter for partition elimination

INSERT INTO `sandbox.open_orders_minute_metrics_v2`
(
calculation_timestamp,
trading_db_name,
order_id,
trading_account_number,
symbol_name,
...
...
)
...
...
END;