Iterate over rows in Stored Procedure BigQuery

Question

Is there a way to fetch a query result inside a stored procedure and then iterate over the rows in BigQuery? Having something like a cursor with each row.

This is my stored procedure, it takes 6 parameters and I take those parameters from a table. I would like to call the procedure X times (X being the number of rows of my input table). So something like :

FOR device_id, nb_measures, delta_t_min, delta_t_last_rec, date_cr, frame IN (SELECT device_id, nb_measures, delta_t_min, delta_t_last_rec, date_cr, frame FROM my_project.my_dataset.my_table)

BEGIN
  DECLARE count INT64 DEFAULT 0;
  SET temp_list = [];
  WHILE count < nb_measures DO
    SET temperature = `bdz-dts-datascience-dev.fonctions.hexStringToInt`(frame, 5 + count, 1, 0, 8);
    IF temperature != 127 THEN
      IF count = 0 THEN
        SET measure_time = TIMESTAMP_SUB(date_cr, INTERVAL delta_t_last_rec MINUTE);
      ELSE
        SET measure_time = TIMESTAMP_SUB(date_cr, INTERVAL delta_t_last_rec + count * delta_t_min MINUTE);
      END IF;
      INSERT `20191218_temperature_repeteurs.step_2`(device_id, measure_time, temperature)
        VALUES(measure_time, temperature);
  END IF;
  END WHILE;
END;

Or on the other hand is there a way to execute a stored procedure inside a SELECT query to iterate through the results ?

SELECT 
  device_id, nb_mesures, delta_t_min, delta_t_last_rec, date_cr, frame
  CALL `my-dataset.my_procedure`(device_id, nb_mesures, delta_t_min, delta_t_last_rec, date_cr, frame)
FROM `my_project.my_dataset.my_table`)

Tamir Klein Tamir Klein · Accepted Answer · 2019-12-18T17:51:02

Here is an example of how you can iterate over rows using store procedure taken from here

-- The input variable is employee’s employee_id (target_employee_id)
  -- The output variable (OUT) is employee_hierarchy which lists
  --      the employee_id of the employee’s manager
CREATE PROCEDURE dataset.GetEmployeeHierarchy(
  target_employee_id INT64, OUT employee_hierarchy ARRAY<INT64>)
BEGIN
  -- Iteratively search for this employee's manager, then the manager's
  -- manager, etc. until reaching the CEO, who has no manager.
  DECLARE current_employee_id INT64 DEFAULT target_employee_id;
  SET employee_hierarchy = [];
  WHILE current_employee_id IS NOT NULL DO
    -- Add the current ID to the array.
    SET employee_hierarchy =
      ARRAY_CONCAT(employee_hierarchy, [current_employee_id]);
    -- Get the next employee ID by querying the Employees table.
    SET current_employee_id = (
      SELECT manager_id FROM dataset.Employees
      WHERE employee_id = current_employee_id
    );
  END WHILE;
END;

Iterate over rows in Stored Procedure BigQuery

2 Answers