BigQuery Remote Functions

Exam guide§2.1

A BigQuery remote function lets you call code that lives outside BigQuery from inside a Google Standard SQL query, by wiring the query to a Cloud Run function. You deploy an HTTP function in Cloud Run functions, then invoke it from SQL - BigQuery passes each row's arguments to the function and gets a value back per row.

The bridge is a BigQuery connection of type CLOUD_RESOURCE: you create the connection, create a remote function that points at the connection plus the function's URL, and then use that function in queries.

A BigQuery Standard SQL query invokes the remote function; through the CLOUD_RESOURCE connection BigQuery sends an HTTPS request carrying the row’s arguments to the HTTP Cloud Run function, which returns one value per row.

Configuring the connection

#NumbersSet up the CLOUD_RESOURCE connection

1. Enable the BigQuery Connection API.
2. Ensure you have the necessary IAM role permissions (for example, roles/bigquery.admin).
3. Create a connection of type CLOUD_RESOURCE using the Google Cloud console, the bq CLI, or the Connection API.
4. Grant the connection's service account the invoker role on the function (see the gotcha below).

⚠GotchaInvoker role differs by function generation

Grant the connection's service account:

• Cloud Functions Invoker (roles/cloudfunctions.invoker) on a 1st gen function, or
• Cloud Run Invoker (roles/run.invoker) on a 2nd gen function.

Pick the wrong one for the generation and the query fails with a permission error at invocation time.

CommandsCreate the connection with bq

bq mk --connection --display_name='friendly name' \

--connection_type=CLOUD_RESOURCE \

--project_id=my_project_id --location=US my-connection

Creating the remote function

#NumbersCreate the remote function in BigQuery

1. Ensure you have the required role permissions on the dataset where you create the remote function and on the connection it uses (for example, roles/bigquery.admin).
2. Create the remote function with the CREATE FUNCTION statement, specifying the BigQuery connection name and the function URL endpoint.

CREATE FUNCTION my_project_id.my_dataset.function_name(x INT64, y INT64) RETURNS INT64

REMOTE WITH CONNECTION 'my_project_id.us.my-connection'

OPTIONS (endpoint = 'https://us-east1-my_gcf_project.cloudfunctions.net/function_name')

Invoking the function from BigQuery

≡FactsRoles needed to invoke

• roles/bigquery.dataViewer on the dataset.
• roles/bigquery.connectionUser on the connection used by the remote function.

Then use the remote function in a query, providing any required arguments. The sample function adds the second argument to the first and returns the result for each row:

SELECT val, my_project_id.my_dataset.function_name(val, 2) FROM UNNEST([NULL,2,3,5,8]) AS val;

Val | f0_

---------

Null | 2

2 | 4

3 | 5

5 | 7

8 | 10

Related documentation

• Managing BigQuery connections
• BigQuery remote functions