LAB38_GCP_Data Engineering: Pub/Sub to BigQuery Streaming Pipeline

Data Engineering: Pub/Sub to BigQuery Streaming Pipeline

☁ GCP 🔥 Intermediate Pub/Sub BigQuery Dataflow Data Pipeline

Scenario

Your IoT devices publish thousands of telemetry events to a Pub/Sub topic per second. You need to stream these events directly into a BigQuery table for analysis. Historically, you had to deploy an expensive Apache Beam / Dataflow job to act as the middleman. Now, GCP allows Pub/Sub BigQuery Subscriptions which natively stream JSON data directly from the topic into the table without any compute middleware.

Your Objectives
  • Provision a google_pubsub_topic named telemetry.
  • Provision a google_bigquery_dataset and a google_bigquery_table with a strict schema (id, temperature, timestamp).
  • Provision a google_pubsub_subscription utilizing the bigquery_config block, pointing it exactly to your table.
  • Grant the native Pub/Sub Service Identity the roles/bigquery.dataEditor role on the dataset so it has permission to insert rows.

Additional Context

Pub/Sub IAM: Pub/Sub operates using a Google-managed Service Agent (`service-[PROJECT_NUM]@gcp-sa-pubsub.iam.gserviceaccount.com`). This hidden agent is the entity that executes the data push into BigQuery. Therefore, your Terraform script MUST explicitly grant this agent IAM permissions on the BigQuery table.

Drop Unknown Fields vs Write Metadata: The subscription can be configured to drop JSON keys it receives that aren't present in the BQ schema. It can also be configured to write Pub/Sub metadata (like `message_id` and `publish_time`) directly into specific BQ columns.

variables.tf

variable "project_id" { type = string }

main.tf

provider "google" {
  project = var.project_id
  region  = "us-central1"
}

# Ensure the Project Number is accessible for creating the SA string dynamically
data "google_project" "env" {
  project_id = var.project_id
}

# 1. The BigQuery Sink
resource "google_bigquery_dataset" "analytics" {
  dataset_id  = "iot_analytics"
  description = "Dataset for IoT Telemetry"
  location    = "US" # Multi-region
}

resource "google_bigquery_table" "telemetry_table" {
  dataset_id          = google_bigquery_dataset.analytics.dataset_id
  table_id            = "raw_telemetry"
  deletion_protection = false

  # Define the schema using JSON format string
  schema = <

Workflow Commands

terraform init
terraform apply -var="project_id=my-gcp-project-123"

# Publish a mocked JSON event matching the schema
gcloud pubsub topics publish iot-telemetry-topic \
  --message='{"device_id": "sensor-01", "temperature": 72.4}'

# Query BigQuery to see the data (may take a few seconds to buffer)
bq query --use_legacy_sql=false \
  'SELECT * FROM `iot_analytics.raw_telemetry` LIMIT 10'

terraform destroy -var="project_id=my-gcp-project-123"

Related Posts