How To Connect Google BigQuery To Amazon SageMaker Studio Using Data Wrangler For Real-Time Machine Learning

|

In today’s data-driven landscape, organizations often use a mix of cloud services across providers. Google BigQuery is renowned for its high-performance data warehousing, while Amazon SageMaker Studio provides a powerful environment for developing, training, and deploying machine learning models. A common enterprise requirement is the ability to pull data from BigQuery directly into SageMaker for real-time machine learning workflows. In this guide, we will walk through how to achieve this integration using Amazon SageMaker Data Wrangler.

What is SageMaker Data Wrangler?

SageMaker Data Wrangler is a visual interface within SageMaker Studio that simplifies the process of importing, cleaning, transforming, and analyzing data for machine learning. It supports direct connections to various data sources, including Amazon S3, Athena, Redshift, and external JDBC sources, which we will leverage to connect with BigQuery.

Prerequisites

Before we dive into the steps, ensure you have the following:

  • An AWS account with SageMaker Studio and Data Wrangler enabled.

  • Access to Google Cloud Platform (GCP) with a BigQuery dataset.

  • A Google Cloud service account key in JSON format with BigQuery access.

  • A SageMaker Studio user profile with IAM permissions to use SageMaker Data Wrangler.

  • IAM Role for SageMaker with access to required services.

  • An S3 bucket for intermediate storage (if needed).

High-Level Architecture

Here’s a quick overview of the architecture:

  1. BigQuery holds structured analytics-ready data.

  2. SageMaker Studio accesses BigQuery using a JDBC connection from Data Wrangler.

  3. Data is transformed, visualized, and exported directly to a SageMaker training job or model endpoint.

Prepare the Google BigQuery Environment

Create a GCP Service Account

  1. Go to GCP Console → IAM & Admin → Service Accounts.

  2. Create a service account and grant the BigQuery Data Viewer and BigQuery Job User roles.

  3. Generate a JSON key and download it.

Create a BigQuery Dataset

Ensure your dataset and tables exist. You can use the following sample SQL to create a test table:

sql
CREATE OR REPLACE TABLE `your_project.dataset.customer_data` AS
SELECT
'cust-001' AS customer_id,
45 AS age,
'NY' AS location,
300.00 AS monthly_spend;

Store the GCP Credentials in SageMaker Studio

  1. Go to your SageMaker Studio terminal.

  2. Save the BigQuery service account key JSON securely, e.g.:

bash
mkdir -p ~/.bigquery
nano ~/.bigquery/bq-service-key.json

Paste the JSON content and save the file.

  1. Set the environment variable inside the terminal or notebook:

python
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "/home/sagemaker-user/.bigquery/bq-service-key.json"

Create JDBC Connection in SageMaker Studio

Install JDBC Driver for BigQuery

SageMaker Studio requires the Simba JDBC driver for BigQuery. Here’s how to install it:

bash
cd /home/sagemaker-user/
wget https://storage.googleapis.com/simba-bq-release/jdbc/SimbaJDBCDriverforGoogleBigQuery42_1.2.24.1020.zip
unzip SimbaJDBCDriverforGoogleBigQuery42_1.2.24.1020.zip

Take note of the driver JAR file path (e.g., GoogleBigQueryJDBC42.jar).

Configure SageMaker Data Wrangler Connection

  1. Open SageMaker Studio, and launch Data Wrangler.

  2. Create a new flow.

  3. Click Import Data → Connect to data source → Select JDBC.

  4. Use the following configuration:

text
Data source name: BigQueryConnection
JDBC URL: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=your_project;OAuthType=0;OAuthServiceAcctEmail=service_account_email;OAuthPvtKeyPath=/home/sagemaker-user/.bigquery/bq-service-key.json
Driver Class: com.simba.googlebigquery.jdbc42.Driver

  1. Select your BigQuery table (e.g., dataset.customer_data) and load it into Data Wrangler.

Transform Data in SageMaker Data Wrangler

Once the data is imported:

  • Apply transformations such as one-hot encoding, normalization, date parsing, or feature selection using the UI.

  • You can also use Python pandas scripts in the custom code transform option:

python
df["monthly_spend_scaled"] = (df["monthly_spend"] - df["monthly_spend"].mean()) / df["monthly_spend"].std()

Analyze the Data

Use the Data Insights tab in Data Wrangler to:

  • Visualize distributions and correlations.

  • Identify missing values.

  • Understand feature importance with built-in analysis tools.

Export to SageMaker Model Training

After the data is ready:

  1. Click Export → Choose SageMaker training job.

  2. Select an existing S3 bucket and IAM role.

  3. Choose the XGBoost, Linear Learner, or custom algorithm.

  4. Define model output path and initiate training.

You can also export to:

  • SageMaker Feature Store for feature reuse.

  • Real-time endpoint for deployment.

Real-Time Inference with SageMaker Endpoint

Once the model is trained:

Deploy the Model

python

from sagemaker.model import Model

model = Model(
model_data=“s3://your-bucket/model.tar.gz”,
image_uri=“YOUR_IMAGE_URI”,
role=“SageMakerRole”
)

predictor = model.deploy(
initial_instance_count=1,
instance_type=“ml.m5.large”
)

Use for Inference

python
response = predictor.predict({
"age": 45,
"location": "NY",
"monthly_spend": 300.00
})
print(response)

This enables real-time predictions based on incoming data from your BigQuery source.

Automate with Pipelines (Optional)

You can automate this entire process using SageMaker Pipelines:

  • Define steps: Data Wrangler → Training → Model Evaluation → Deployment.

  • Use sagemaker.workflow.steps and ProcessingStep with Data Wrangler’s output.

  • Schedule pipelines to refresh ML models based on BigQuery data changes.

IAM Permissions Required

Ensure the SageMaker execution role has access to:

json
{
"Effect": "Allow",
"Action": [
"s3:*",
"sagemaker:*",
"logs:*",
"cloudwatch:*"
],
"Resource": "*"
}

Additionally, for BigQuery access via JDBC, SageMaker needs access to read the local credentials file and make external HTTPS requests.

Final Checklist

Task Status
Google Cloud service account with BigQuery access ✔️
JDBC driver installed in SageMaker Studio ✔️
BigQuery dataset and table created ✔️
SageMaker Studio with Data Wrangler enabled ✔️
Data transformed and analyzed in Data Wrangler ✔️
Model trained and deployed for inference ✔️

In a hybrid multi-cloud world, enabling interoperability between systems like Google BigQuery and Amazon SageMaker empowers organizations to build robust, scalable, and real-time ML solutions. By leveraging SageMaker Data Wrangler’s JDBC capabilities, you can seamlessly import analytics-grade data from BigQuery into your AWS ML pipeline without intermediate exports or duplication.

This approach ensures:

  • Real-time ingestion from BigQuery.

  • Centralized feature engineering using Data Wrangler.

  • Seamless transition to training and inference on SageMaker.

By automating the pipeline, incorporating regular updates from BigQuery, and deploying real-time endpoints, your models remain current, relevant, and production-ready. This setup exemplifies how cloud-native tools can work in harmony across providers to drive intelligent business decisions.