What is SQL Cursor Alternative in BigQuery?

SQL Cursor is a database object to retrieve data from a result set one row at a time. The cursor are useful in many scenarios such as updating records using loops, inserting only required records, etc. You can think cursor as a temporary workspace provisioned in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is SQL cursor alternative in Google BigQuery with an example.

Cursors are usually written using SQL procedural language, such as Oracle PL/SQL, Netezza NZPL/SQL, PL/pgSQL in Amazon Redshift, or T-SQL in SQL Server.

Does BigQuery Support SQL Cursor Variable?

Similar to PL/SQL procedure language, BigQuery support scripting. But, it does not support declaring and using cursor variables.

Before going into alternative approach, let us check sample PL/SQL procedure.

Sample SQL Cursor Example

Following piece of code is the sample Oracle PL/SQL procedure with cursor.

CREATE OR replace PROCEDURE Sample_proc 
IS 
  str1 VARCHAR2(20); 
  str2 INT; 
  str3 INT; 
  CURSOR sample_cur IS 
    SELECT empid, 
           ename, 
           deptid 
    FROM   emp1; 
BEGIN 
    OPEN sample_cur; 

    FETCH sample_cur INTO str2, str1, str3; 

    CLOSE sample_cur; 
END;

We will refer this sample cursor for subsequent examples.

What is SQL Cursor Alternative in BigQuery?

One of the easiest way is to create a temporary table and iterate through rows in temp table using looping structures.

Note that, temp table is just an alternative. It does not update the pointers when data is read.

Following is the example of using a temporary table as a SQL cursor alternative with the help of BigQuery looping statements.

DECLARE var1 INT64 DEFAULT 1;
DECLARE var2 INT64 DEFAULT 0;

DECLARE str1 string DEFAULT '';
DECLARE str2 string DEFAULT '';
DECLARE str3 string DEFAULT '';

CREATE TEMP TABLE temp_emp AS
SELECT empid, 
	ename, 
	deptid,
	RANK() OVER(ORDER BY empid) rownum
FROM   td.emp1; 


SET var2= (SELECT COUNT(*) FROM temp_emp);

WHILE var2<=var1 DO
  SET str1 = (SELECT empid FROM temp_emp WHERE rownum = x);
  SET str2 = (SELECT empid FROM temp_emp WHERE rownum = x);
  SET str3 = (SELECT empid FROM temp_emp WHERE rownum = x);

  SET var1=var1+1;

END WHILE;

As you can see in the above example, it is easy to loop through temp table.

Following are some of points to be noted.

We are using the SET command to assign value to a variable. It is SELECT..INTO in the original example.
We are not using open and close cursor.
We are creating a TEMPORARY table in place of cursor declaration.