10

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>
Improve this question
4
  • Are there any application logs? If so, may I ask you to attach them to the question, please? Commented Jan 7, 2021 at 8:37
  • Added the 500 error code. Other than that could not find any other logs. Commented Jan 7, 2021 at 9:34
  • Do you have details in Cloud Logging? Here it's only the HTTP response. Commented Jan 7, 2021 at 12:24
  • I think I don't have permission to view those logs in this account. Thanks for the feedback, the issue has been answered. Commented Jan 7, 2021 at 14:08

2 Answers 2

Reset to default
9
  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!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I erased everything and followed your screens one by one. Worked perfectly.
hi, i have an error. IndentationError: expected an indented block
Isn't --role="roles/owner" a bit overkill?
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.