How to get result from BigQuery based on user input parameters

This is a continuation of my previous post for making api that takes url parameter , passes it to BigQuery and if the luid record has data in orderid column, it returns True . How to check whether data exists in specific column on BigQuery with Flask?

I changed sql and it seems this sql works well on GCP console but as you can see , it returns Flase({‘f0_’: 0})) if you input correct parameter from browser. Do I need to fix this sql ??

[URL:https://test-989898.df.r.appspot.com?luid=U77777] The output of return str(row) ↓ Row((True,), {'f0_': 0})   The output of SQL with same luid above on console  ↓ row | f0_ 1   | true    SELECT EXISTS(SELECT 1 FROM `test-266110.conversion_log.conversion_log_2020*` as p WHERE luid = "U77777" AND orderid != '' limit 1000) 

and I tried this article as below . User input parameter can not be available in BigQuery ?? https://cloud.google.com/bigquery/docs/parameterized-queries

@app.route('/') def get_request():     luid = request.args.get('luid') or ''     client = bigquery.Client()     query = """SELECT EXISTS(SELECT 1 FROM `test-266110.conversion_log.conversion_log_2020*` as p WHERE @luid = p.luid AND orderid != '' limit 1000)"""     job_config = bigquery.QueryJobConfig(     query_parameters=[         bigquery.ScalarQueryParameter("luid", "STRING", luid),     ]     )     query_job = client.query(query, job_config=job_config)     query_res = query_job.result()     for row in query_res:         return str(row)   ↓ Row((True,), {'f0_': 0}) 

I’ve been stack in this problem for a while , I’m welcome to any idea . Anyone has good solutions ??

from flask import Flask, request, jsonify from google.cloud import bigquery   app = Flask(__name__)   @app.route('/') def get_request():     luid = request.args.get('luid') or ''     client = bigquery.Client()     query = """SELECT EXISTS(SELECT 1 FROM `test-266110.conversion_log.conversion_log_2020*` as p WHERE @luid = p.luid AND orderid != '' limit 1000)"""      job_config = bigquery.QueryJobConfig(     query_parameters=[         bigquery.ScalarQueryParameter("luid", "STRING", luid),     ]     )     query_job = client.query(query, job_config=job_config)     query_res = query_job.result()     #  first_row = next(iter(query_job.result()))     for row in query_res:         return str(row)         #return jsonify({luid:query_res.total_rows})     """     if query_res == :         return jsonify({luid: str(True)})     else:         return jsonify({luid: str(False)})     """ if __name__ == "__main__":     app.run()    ↓ Row((True,), {'f0_': 0}) 
Asked on September 1, 2020 in Sql.
Add Comment
1 Answer(s)

You seem to have solved most of the bits, it’s just a question of getting them working together. Here’s a quick sample that should help with the BigQuery things, and shows a different way of writing your query pattern using a public dataset table.

from google.cloud import bigquery client = bigquery.Client()  # assume you get this from your flask app's param.  this is the "luid" you're checking. value = "treason" # rewriting the sql demonstrate a similar thing with a public dataset table sql = "SELECT COUNTIF([email protected] AND corpus='sonnets') > 0 as word_is_sonnet FROM `bigquery-public-data.samples.shakespeare`"  config = bigquery.QueryJobConfig(     query_parameters=[         bigquery.ScalarQueryParameter("luid", "STRING", value),     ] )  job = client.query(sql, job_config=config)  # this is a bit odd, but in this case we know we're dealing with a single row # coming from the iterable based on the query structure. first_row = next(iter(job.result())) print(first_row.get("word_is_sonnet")) 

However, that said I’d make sure you’re understanding how BigQuery works and charges for queries. You seem to be doing point lookups for a range of tables (the wildcard table in your original query), which means you’re potentially doing a lot of table scanning to satisfy this request.

I just wanted to call that out so you’re not surprised by either the performance or the costs if the intent is that you’re issuing many requests like this.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.