Skip to content

export_to_pubsub

export_to_pubsub(project, topic, data, attributes)

Description

Exports data and attributes to Pub/Sub topic.

💡 For this to work, 749389685934-compute@developer.gserviceaccount.com must have publish permission on your topic.

Usage

Call or Deploy export_to_pubsub ?
Call export_to_pubsub directly

The easiest way to use bigfunctions

  • export_to_pubsub function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy export_to_pubsub in your project

Why deploy?

  • You may prefer to deploy export_to_pubsub in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

export_to_pubsub function can be deployed with:

pip install bigfunctions
bigfun get export_to_pubsub
bigfun deploy export_to_pubsub

Examples

select bigfunctions.eu.export_to_pubsub("your-project", "your_topic", "Your message data", {"attribute1": "value1", "attribute2": "value2"})

select bigfunctions.us.export_to_pubsub("your-project", "your_topic", "Your message data", {"attribute1": "value1", "attribute2": "value2"})

select bigfunctions.europe_west1.export_to_pubsub("your-project", "your_topic", "Your message data", {"attribute1": "value1", "attribute2": "value2"})

+----------------------+
| published_message_id |
+----------------------+
| 1123432546           |
+----------------------+

Use cases

Let's imagine you have a BigQuery table that tracks real-time sensor data from a manufacturing plant. You want to trigger a downstream process whenever a sensor reading exceeds a certain threshold. This downstream process might be an alert system, an automated adjustment to the machinery, or a data pipeline for further analysis. Instead of constantly polling the BigQuery table, you can use the export_to_pubsub function to push relevant data to a Pub/Sub topic in real-time.

Here's how it would work:

  1. BigQuery Streaming Inserts: Sensor data is streamed into a BigQuery table.

  2. BigQuery Scheduled Query: A scheduled query runs periodically (e.g., every minute) to check for sensor readings exceeding the threshold. The query might look something like this:

SELECT sensor_id, timestamp, reading
FROM `your-project.your_dataset.sensor_data`
WHERE reading > 1000 AND _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MINUTE)
  1. Call export_to_pubsub within the query: If the query returns any results, you integrate the export_to_pubsub function within the query itself:
SELECT bigfunctions.your_region.export_to_pubsub(
    'your-project',
    'sensor_alerts_topic',
    TO_JSON_STRING(t),  -- Send the entire row as the message data
    '{"sensor_type": "temperature"}' -- Add attributes for context
)
FROM (
    SELECT sensor_id, timestamp, reading
    FROM `your-project.your_dataset.sensor_data`
    WHERE reading > 1000 AND _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MINUTE)
) AS t;
  1. Pub/Sub triggers downstream process: The sensor_alerts_topic is subscribed to by a service that handles the alerts or automated actions. When a message arrives on the topic, the subscriber is triggered, and the downstream process is initiated.

Benefits:

  • Real-time responsiveness: Alerts and actions are triggered as soon as the threshold is crossed, without needing to constantly poll BigQuery.
  • Scalability: Pub/Sub handles the message distribution, ensuring that the downstream systems can scale to handle a large volume of alerts.
  • Decoupling: BigQuery is decoupled from the downstream processes, making the system more flexible and maintainable.
  • Reduced cost: Avoids the cost and latency of repeatedly querying BigQuery for new data.

This is just one example. The export_to_pubsub function can be used in various scenarios where you need to push data from BigQuery to other systems in a real-time or near real-time fashion. Other examples include:

  • New user registration: Push new user data to a Pub/Sub topic for welcome email processing.
  • Order fulfillment: Notify downstream systems about new orders placed.
  • Fraud detection: Push suspicious transactions to a Pub/Sub topic for real-time analysis.
  • Data synchronization: Keep other systems in sync with changes happening in BigQuery.
Need help or Found a bug?
Get help using export_to_pubsub

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about export_to_pubsub

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on