如何根据用户输入参数从BigQuery获取结果 [英] How to get result from BigQuery based on user input parameters

问题描述

这是我以前制作api的延续，该api使用url参数，并将其传递给BigQuery，如果luid记录的orderid列中包含数据，则返回True.如何检查数据是否存在在带有Flask的BigQuery上的特定列中?

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?

我更改了sql，看来该sql在GCP控制台上运行良好，但是如您所见，如果从浏览器输入正确的参数，它将返回Flase({'f0_':0})).我需要修复此SQL吗?

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)

并且我尝试了以下这篇文章.用户输入参数在BigQuery中不可用? https://cloud.google.com/bigquery/docs/parameterized-queries

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})

推荐答案

您似乎已经解决了大部分问题，这只是使它们一起工作的问题.这是一个快速示例，可以帮助您处理BigQuery，并显示了使用公共数据集表编写查询模式的另一种方式.

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(word=@luid 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"))

但是，那是为了确保您了解BigQuery的工作原理和查询费用.您似乎正在对一系列表(原始查询中的通配符表)进行点查找，这意味着您可能在做大量表扫描来满足此请求.

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.

这篇关于如何根据用户输入参数从BigQuery获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！