HTTPrequest to GCP BigQuery via Cloud Function with Multiple Tables

Question

I'm looking to create a HTTPrequest to BigQuery via a Cloud Function within GCP where the request passes a value, that value is passed to the query and another value is returned from a joined table. The SQL works in BQ but having issues getting a value returned when I apply it to the Cloud Function.

Here's the curl I'm using as well.

https://us-central1-something.cloudfunctions.net/something/something2?client_Id=823754783.2

Thanks in advance.

from flask import escape
from google.cloud import bigquery

def cors_enabled_function(request):

    if request.method == 'OPTIONS':

        headers = {
            'Access-Control-Allow-Origin': '*',
            'Access-Control-Allow-Methods': 'GET',
            'Access-Control-Allow-Headers': 'Content-Type',
            'Access-Control-Max-Age': '3600'
        }

        return ('', 204, headers)


client = bigquery.Client()

def something2(request):

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

   
    table = ['`audience-cookie.ecommerce.traffic` as traffic JOIN `audience-cookie.ecommerce.cardholder` as cardholder ON traffic.customerId = cardholder.customerId']

    QUERY = ('SELECT '+cardholder+' from `'+table+'` WHERE client_Id='+client_Id)

    try:
        query_job = client.query(QUERY)
        rows = query_job.result()
        row_list = []
        for row in rows:
            row_list.append(str(row[cardholder]))
        return("<p>"+"</p><p>".join(row(row_list) + "</p>"))
    except e:
        return(e)

guillaume blaquiere guillaume blaquiere · Accepted Answer · 2020-07-29T18:58:03

I recommend you to print your query. There is several mistakes

Why are you using [] in the table var definition?
you use backquote ` in table and when your insert table into the query. So, at the end, you have a double backquote
client_Id isn't defined in your code

HTTPrequest to GCP BigQuery via Cloud Function with Multiple Tables

1 Answers