A quick walkthrough BigQuery Remote Functions

In a previous post, I mentioned Remote Functions—a powerful way to send data from BigQuery to an external service for processing, including a Cloud Run function.

This is especially useful when SQL lacks built-in support for your specific needs, and writing a UDF isn’t an option (for example, if you need a highly specialized Python function).

Before building your own, check out bigfunctions—many common use cases have already been solved by others!

What are remote functions in BigQuery?

These are a special type of function that delegates processing of input to an external resource, allowing us to:

• send data from BigQuery to Google Cloud Functions or other external services

• process it using a programming language

• return results to our query

Why is that important?

A Google Remote function can encapsulate any kind of logic in major programming languages.

This opens the door to vast a ecosystem of libraries such at the Python packages.

Let’s look at a step-by-step example of creating a Remote Function.

Step 1: Create the Cloud Run Function

    try:
        # Read request JSON (BigQuery sends input in "calls" array)
        request_json = request.get_json()
        calls = request_json.get("calls", [])

        responses = []  # This will store the results

        today = datetime.date.today()
        year = today.year

        for call in calls:
            try:
                country_code = call[0].upper()  # Extract country_code from input
                country_holidays = holidays.country_holidays(country_code, years=[year, year + 1])

                # Find the next upcoming holiday
                next_holiday = next(
                    ((str(date), name) for date, name in sorted(country_holidays.items()) if date >= today),
                    None
                )

                if next_holiday:
                    response = f"Next holiday is on {next_holiday[0]} and is called {next_holiday[1]}"
                else:
                    response = f"No upcoming holidays found for {country_code} in {year}."

            except KeyError:
                response = f"Error: Invalid or unsupported country code: {call[0]}"

            responses.append(response)

        return jsonify({"replies": responses})  # Send the list of replies

    except Exception as e:
        return jsonify({"errorMessage": str(e)})

Step 2: Create a connection

Step 3: Set up permissions

Step 4: Bind the connection with Cloud Run function

CREATE FUNCTION learning.get_next_public_holidays(country_code STRING) RETURNS STRING

REMOTE WITH CONNECTION `YOUR_PROJECT.eu.test-bigquery-connection`

OPTIONS (endpoint = 'https://YOUR_ENDPOINT_URL.europe-west1.run.app')

Test run

WITH input AS (

  SELECT 'RO' AS country_code UNION ALL
  SELECT 'BE' AS country_code UNION ALL
  SELECT 'VN' AS country_code
)

SELECT country_code, learning.get_next_public_holidays(country_code)


FROM input