CODEWITHSUNDEEP
python-3.xflaskgoogle-bigquerygoogle-cloud-functions
katZwat
katZwat

Reputation: 154

Connect and query from a BigQuery database via a Google Cloud Function (Python)

I have

  1. A google Cloud Function (main.py + requirements.txt)
  2. A bigQuery Database
  3. Working query

Can someone help me with a link/tutorial/code to connect to this bigquery database using my Google Cloud Function in Python and simply query some data from the database and display it.

I tried the https://cloud.google.com/bigquery/docs/reference/libraries but it was related to connecting to big query from a normal deployment and not a Google Cloud Function.

This is what I have so far. It is deploying without an error, but upon testing, it is giving a 500 error

main.py (sample public query

from google.cloud import bigquery

def query_stackoverflow(request):
 client = bigquery.Client()
 query_job = client.query(
    """
    SELECT
      CONCAT(
        'https://stackoverflow.com/questions/',
        CAST(id as STRING)) as url,
      view_count
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10"""
)

results = query_job.result()  # Waits for job to complete.
return Response("{'message':'successfully connected'}", status=200, mimetype='application/json')

requirements.txt

google-cloud-bigquery

Error log:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<title>500 Internal Server Error</title>
<h1>Internal Server Error</h1>
<p>The server encountered an internal error and was unable to complete your 
request. Either the server is overloaded or there is an error in the 
application.</p>

Upvotes: 10

Views: 14500

Answers (2)

Deirdre O&#39;Connor
Deirdre O&#39;Connor

Reputation: 120

As stated above you need to create a Service Account with the correct permissions to connect to BigQuery, but if you cannot grant the role of Owner (which is a basic role that shouldn't be used in production), the basic permissions needed by your SA or any user to query BigQuery are:

  • BigQuery Data Viewer
  • BigQuery User

You can read more about basic roles in the GCP documentation where they say

Caution: Basic roles include thousands of permissions across all Google Cloud services. In production environments, do not grant basic roles unless there is no alternative. Instead, grant the most limited predefined roles or custom roles that meet your needs.

Upvotes: 1

marian.vladoi
marian.vladoi

Reputation: 8056

  1. Create a service account and grant the necessary role.
gcloud iam service-accounts create connect-to-bigquery
gcloud projects add-iam-policy-binding your-project --member="serviceAccount:[email protected]" --role="roles/owner"

  1. Create a cloud function using using the service account you just created as identity enter image description here

  2. Edit the main.py and requirements.txt

enter image description here enter image description here

  1. Deploy and Test the function enter image description here enter image description here

SUCCESS!

Upvotes: 9

Related Questions