Google BigQuery Integration with Python

Problem

A Publisher wants to keep track of the latest information added to a public dataset in Google Cloud’s BigQuery service.

Solution

Using the Google OAuth integration configured by their Connect Administrator, the Publisher is able to pull in the latest table information from BigQuery directly into their published content.

requirements.txt
numpy==1.26.4
streamlit
google-cloud-bigquery
pandas==1.3.4
posit-sdk==0.5.0
protobuf==5.28.3
app.py
# -*- coding: utf-8 -*-
# mypy: ignore-errors

import os
import streamlit as st
from posit import connect
import pandas as pd
import google.oauth2.credentials as creds
from google.cloud import bigquery as bq

# initialize the Connect python sdk client if running on Connect
if os.getenv("RSTUDIO_PRODUCT") == "CONNECT":
    client = connect.Client()

    # grab the user session token from headers
    user_session_token = st.context.headers.get("Posit-Connect-User-Session-Token")

    try:
        # fetch the access token using the session token
        access_token = client.oauth.get_credentials(user_session_token).get(
            "access_token"
        )
    except connect.errors.ClientError as e:
        if e.error_code == 219:
            st.write(
                "Your Google refresh token has expired. Please log out and log in to the integration."
            )
else:
    access_token = os.getenv("GOOGLE_TOKEN")

tables = []
project = os.getenv("GOOGLE_PROJECT")
credentials = creds.Credentials(access_token)
dataset = os.getenv("BIGQUERY_DATASET")
bq_client = bq.Client(credentials=credentials, project=project)

# get tables from provided dataset
for table in bq_client.list_tables(dataset):
    tables.append(table.full_table_id)
df = pd.DataFrame({"tables": tables})

st.write("I found the following tables in your provided dataset:")
st.write(df)

Running the app locally

Terminal
export CONNECT_SERVER=<connect-server>
export CONNECT_API_KEY=<connect-api-key>
BIGQUERY_DATASET=<bigquery-dataset> GOOGLE_PROJECT=<google-project> GOOGLE_TOKEN=<google-token> streamlit run app.py