Work with remote functions

A BigQuery remote function allows you to implement your function in other languages than SQL and JavaScript or with the libraries or services which are not allowed in BigQuery user-defined functions.

Overview

A BigQuery remote function lets you incorporate GoogleSQL functionality with software outside of BigQuery by providing a direct integration with Cloud Run functions and Cloud Run. With BigQuery remote functions, you can deploy your functions in Cloud Run functions or Cloud Run implemented with any supported language, and then invoke them from GoogleSQL queries.

Workflow

  1. Create the HTTP endpoint in Cloud Run functions or Cloud Run.
  2. Create a remote function in BigQuery.
    1. Create a connection of type CLOUD_RESOURCE.
    2. Create a remote function.
  3. Use the remote function in a query just like any other user-defined functions.

Limitations

  • Remote functions only support one of the following data types as argument type or return type:

    • Boolean
    • Bytes
    • Numeric
    • String
    • Date
    • Datetime
    • Time
    • Timestamp
    • JSON

    Remote functions do not support ARRAY, STRUCT, INTERVAL, or GEOGRAPHY types.

  • You cannot create table-valued remote functions.

  • You cannot use remote functions when creating materialized views.

  • The return value of a remote function is always assumed to be non-deterministic so the result of a query calling a remote function is not cached.

  • You might see repeated requests with the same data to your endpoint, even after successful responses, due to transient network errors or BigQuery internal errors.

  • When a remote function evaluation is skipped for some rows due to short-circuiting, for example, in conditional expressions or a MERGE statement with WHEN [NOT] MATCHED, batching is not used with the remote function. In this case, the calls field in the HTTP request body has exactly one element.

  • If the dataset associated with the remote function is replicated to a destination region through cross-region dataset replication, the remote function can only be queried in the region that it was created in.

  • Remote functions support a custom path in the endpoint URL. The custom paths have the following limitations:

    • Only uppercase letters (A-Z), lowercase letters (a-z), digits (0-9), hyphens (-), and underscores (_) are allowed in the path.
    • Fragment identifiers (#), query parameters (?), and tildes (~) aren't allowed.
    • Paths starting with /_ah/, the specific path /eventlog, and any path ending in the letter z aren't allowed.

Create an endpoint

To create a remote function that can implement business logic, you must create an HTTP endpoint by using either Cloud Run functions or Cloud Run. The endpoint must be able to process a batch of rows in a single HTTP POST request and return the results for the batch as an HTTP response. Remote functions support a custom path in the endpoint URL (see limitations).

If you are creating the remote function by using BigQuery DataFrames, you don't have to manually create the HTTP endpoint; the service does that for you automatically.

See the Cloud Run functions tutorial and other Cloud Run functions documentation on how to write, deploy, test and maintain a Cloud Run function.

See the Cloud Run quick start and other Cloud Run documentation on how to write, deploy, test and maintain a Cloud Run service.

It's recommended that you keep the default authentication instead of allowing unauthenticated invocation of your Cloud Run function or Cloud Run service.

Input format

BigQuery sends HTTP POST requests with JSON body in the following format:

Field name Description Field type
requestId Id of the request. Unique over multiple requests sent to this endpoint in a GoogleSQL query. Always provided. String.
caller Job full resource name for the GoogleSQL query calling the remote function. Always provided. String.
sessionUser Email of the user executing the GoogleSQL query. Always provided. String.
userDefinedContext The user defined context that was used when creating the remote function in BigQuery. Optional. A JSON object with key-value pairs.
calls A batch of input data. Always provided. A JSON array.

Each element itself is a JSON array, which is a JSON encoded argument list of one remote function call.

An example of a request:

{
 "requestId": "124ab1c",
 "caller": "//bigquery.googleapis.com/projects/myproject/jobs/myproject:US.bquxjob_5b4c112c_17961fafeaf",
 "sessionUser": "test-user@test-company.com",
 "userDefinedContext": {
  "key1": "value1",
  "key2": "v2"
 },
 "calls": [
  [null, 1, "", "abc"],
  ["abc", "9007199254740993", null, null]
 ]
}

Output format

BigQuery expects the endpoint should return a HTTP response in the following format, otherwise BigQuery can't consume it and will fail the query calling the remote function.

Field name Description Value Range
replies A batch of return values. Required for a successful response. A JSON array.

Each element corresponds to a JSON encoded return value of the external function.

Size of the array must match the size of the JSON array of calls in the HTTP request. For example, if the JSON array in calls has 4 elements, this JSON array needs to have 4 elements as well.

errorMessage Error message when the HTTP response code other than 200 is returned. For non-retryable errors, we return this as part of the BigQuery job's error message to the user. Optional. String. Size should be less than 1KB.

An example of a successful response:

{
  "replies": [
    1,
    0
  ]
}

An example of a failed response:

{
  "errorMessage": "Received but not expected that the argument 0 be null".
}

HTTP response code

Your endpoint should return the HTTP response code 200 for a successful response. When BigQuery receives any other value, BigQuery considers the response as a failure, and retries when the HTTP response code is 408, 429, 500, 503 or 504 until some internal limit.