Receive Http Request With Variable, Query Bq And Return Response

I'm looking to create a Cloud Function in GCP that receives a HTTP request with parameters, takes the parameters and passes them to Bigquery within SQL statement and returns a resu

Solution 1:

This is an example of a Cloud Function that runs exactly the query that you mention on your post. Nonetheless, it could be adapted to any other query very easily according to your needs. You basically need to follow this tutorial in order to deploy the function and get a basic understanding as to how to query data using the Client Library for BigQuery.

Here is the summary of what you need to do:

1. Create a folder (e.g. cloudfunctionsexample) and use cd [FOLDERNAME e.g. cloudfunctionsexample] to get inside the folder and inside the folder create two files: main.py and requirements.txt.

a. main.py :

from flask import escape
from google.cloud import bigquery

client = bigquery.Client()

defbigquery_example(request):

    request_json = request.get_json(silent=True)
    request_args = request.args

    #Check if request have all the correct parameters to run the queryif request_json and'column'in request_json:
        column = request_json['column']
    elif request_args and'column'in request_args:
        column = request_args['column']
    else:
        return('You are missing the column parameter on the request.')

    if request_json and'name'in request_json:
        name = request_json['name']
    elif request_args and'name'in request_args:
        name = request_args['name']
    else:
        return('You are missing the name of the dataset parameter on the request.')

    if request_json and'limit'in request_json:
        limit = request_json['limit']
    elif request_args and'limit'in request_args:
        limit = request_args['limit']
    else:
        return('You are missing the limit parameter on the request.')

    #Construct the query based on the parameters
    QUERY = ('SELECT '+column+' FROM `'+name+'` LIMIT '+limit)
    #print(QUERY)try:
        query_job = client.query(QUERY)  # API request
        rows = query_job.result()  # Waits for query to finish# Create a list and make the results HTML compatible to be able to be displayed on the browser.
        row_list = []
        for row in rows:
            row_list.append(str(row[column]))
        return("<p>" + "</p><p>".join(row_list) + "</p>")
    except e:
        return(e)

b. requirements.txt :

flask
google-cloud-bigquery

2. (Assuming you have the Cloud SDK installed) and that your make sure that the App Engine default service account (which is the default account used by Cloud Functions) has the Editor role assigned run the following command to deploy the function on your project:

gcloud functions deploy bigquery_http_example --runtime python37 --trigger-http --allow-unauthenticated --entry-point=bigquery_example --timeout=540

3. Get the Cloud Function URL and use either the curl command to make a POST request or simply add the parameters to the Cloud Function URL to make an HTTP request to the Cloud Function endpoint and see the results directly on your browser.

a. curl :

curl -X POST https://[REGION-FUNCTIONS_PROJECT_ID].cloudfunctions.net/bigquery_http_example -H "Content-Type:application/json"  -d '{"column":"visitId","name":"bigquery-public-data.google_analytics_sample.ga_sessions_20170801","limit":"10"}'

b. Cloud Function URL :

https://[REGION-FUNCTIONS_PROJECT_ID].cloudfunctions.net/bigquery_http_example?column=visitId&name=bigquery-public-data.google_analytics_sample.ga_sessions_20170801&limit=10