Notes on a slightly more elaborate use of BigQuery (programming)

I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL.

However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put together a brief description of how to use it for myself.

Here we have compiled a list of programming-related

function

Create user-defined functions
Return value
- Tables cannot be returned. Tables are returned by a separate table function.
Complex SQL statements can be written for return value generation.
(value) after AS
- Enclose the returned value in ()
- If the value is generated in SQL, the value is (SQL statement)
- Therefore, AS and later becomes AS ((SQL statement))
transient function
- Valid only during statement execution
- Assumed to be used in multiple statements

transient function

/* temp function */
CREATE TEMP FUNCTION
test_function(
  param_01 INT64,
  param_02 STRING
)
AS
(
(
SELECT
  param_01
)
);

/* use temp function */
SELECT
  test_function(123, "ABC")
;

External Functions

External function if used in a single statement

/* function */
CREATE OR REPLACE FUNCTION
`project_name.dataset_name`.test_function(
  param_01 INT64,
  param_02 STRING
)
AS
(
(
SELECT param_01
)
);

/* check function */
SELECT
  `project_name.dataset_name`.test_function(123, "ABC")
;

Return multiple values

Return STRUCT if you want to return multiple values

/* temp function */
CREATE TEMP FUNCTION
test_function(
  param_01 INT64,
  param_02 STRING
)
AS
(
(
SELECT
  STRUCT<val_01 INT64, val_02 STRING>(param_01, param_02)
)
);

/* use temp function */
SELECT
  test_function(123, "ABC").val_01,
  test_function(123, "ABC").val_02
;

Variable, BEGIN..END

The DECLARE must be written at the beginning of a sentence.
Declaration and assignment at the same time with DEFAULT
DEFAULT can be omitted.
Type can be omitted if DEFAULT is present
The value of a variable can be set in the result of a SELECT statement.
The value of a variable can be set by the return value of a function.
Tables cannot be variables.
- Use a temporary table to turn a table into a variable
BEGIN..END creates a local scope for a variable
- You can write DECLARE directly under BEGIN.

Example

/* var */
DECLARE x INT64 DEFAULT 0;
DECLARE y STRING;
DECLARE z DEFAULT 123;
SET y=CONCAT("A", "B", "C");
SET z=(SELECT 123);

/* use var */
SELECT x, y, z;

/* declare in begin-end */
BEGIN
  DECLARE a DEFAULT 123;
  SELECT a;
END;

procedure

Can call multiple statement statements
Can pass parameters
Call with CALL
Cannot return table

Example

/* procedure */
CREATE OR REPLACE PROCEDURE
`project_name.dataset_name`.test_procedure(
  param_01 INT64,
  param_02 STRING
)
BEGIN
  DECLARE x INT64;
  DECLARE y STRING;
  SET x=param_01;
  SET y=param_02;
END
;

/* call */
CALL `project_name.dataset_name`.test_procedure(123, "ABC");

temporary table

Like a table as a variable.
Valid only during statement execution
Used to pass a table to another statement

Example

/* temp table */
CREATE TEMP TABLE
temp_table
AS
(
SELECT 123 AS val_01, "ABC" AS val_02
)
;

/* use temp table */
SELECT * FROM temp_table
;

Table loop processing

Able to perform processing on every single row of the table
Table rows come in STRUCT
Processing order undecided
To clarify the processing order, use ORDER BY to specify the sequence.

Example

/* temp table */
CREATE TEMP TABLE
temp_table
AS
(
SELECT
  val_01,
  1 AS val_02,
FROM UNNEST(GENERATE_DATE_ARRAY("2022-01-01", "2022-01-3", INTERVAL 1 DAY)) AS val_01
)
;

/* for loop */
FOR line IN(SELECT * FROM temp_table ORDER BY val_01)
DO
BEGIN
  DECLARE x DEFAULT line.val_01;
  DECLARE y DEFAULT line.val_02;
  SELECT x, y;
END;
END FOR
;

Difficult to specify table columns with variables

Specify a table name in a variable and get the value of that table
Specify the column name in a variable and get the filtered value in that column

There are times when we want to write queries like the above, but it is difficult to achieve such dynamic query generation using only BigQuery's functionality.

With EXECUTE IMMEDIATE, it is possible to execute any SQL string as an SQL statement, but the return value is limited to a variable and cannot return a table.

Debugging displays is difficult.

Sometimes I want to check the progress of a long-running query by outputting it to the console, but this is difficult because the SELECT output is displayed only after the query execution is completed.

An alternative is to output logs to a table, but this is not convenient.

Welcome to new things

[Technical] [Electronic work] [Gadget] [Game] memo writing

Notes on a slightly more elaborate use of BigQuery (programming)

function

transient function

External Functions

Return multiple values

Variable, BEGIN..END

Example

procedure

Example

temporary table

Example

Table loop processing

Example

Difficult to specify table columns with variables

Debugging displays is difficult.

function

transient function

External Functions

Return multiple values

Variable, BEGIN..END

Example

procedure

Example

temporary table

Example

Table loop processing

Example

Difficult to specify table columns with variables

Debugging displays is difficult.

Related Category Articles