接收带有变量的HTTP请求，查询BQ和返回响应 [英] Receive HTTP request with Variable, Query BQ and Return Response

问题描述

我正在寻找一种在GCP中创建云功能的方法，该功能接收带有参数的HTTP请求，获取参数并将其传递给SQL语句中的Bigquery，并返回可以传递回网站的结果.

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 result that I can pass back to website.

我在这方面很新，而且我绝不是一名工程师.我已经知道我的Cloud Function可以正确部署，并且在浏览器中收到响应"OK"

I am very new at this and I am not an engineer by any stretch. I have got to the point where my Cloud Function deploys correctly and I receive a response "OK" within the browser

当我点击它但无法获取从 BQ 返回的值时，无法在浏览器中显示.

When I hit it but can't get the values returned from BQ to show on browser.

到目前为止，这是我的职能，非常感谢您提前提供的帮助.

Here's my function so far and thanks for any help in advance.

import google.cloud.bigquery

def audience(QUERY):
   # BQ Query to get add to cart sessions
   QUERY = """select 
   visitId,
from bigquery-public-data.google_analytics_sample.ga_sessions_20170801
limit 10;
return QUERY"""

print(audience)

推荐答案

这是一个Cloud Function的示例，该函数完全运行您在帖子中提到的查询.尽管如此，它可以根据您的需求很容易地适应任何其他查询.基本上，您需要遵循此教程才能部署该功能并获取一个关于如何使用 BigQuery客户端库的基本理解

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.

这是您需要做的摘要:

1. 创建一个文件夹(例如 cloudfunctionsexample )，然后使用 cd [FOLDERNAME例如cloudfunctionsexample] 进入该文件夹，然后在该文件夹内创建两个文件: main.py 和 requirements.txt .

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:

a. main.py :

from flask import escape
from google.cloud import bigquery

client = bigquery.Client()

def bigquery_example(request):

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

    #Check if request have all the correct parameters to run the query
    if 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:

b. requirements.txt :

flask
google-cloud-bigquery

1. (假设您已安装 Cloud SDK )，请确保您确保 App Engine默认服务帐户(这是默认帐户)由Cloud Functions使用)分配了编辑者"角色，运行以下命令以在您的项目中部署该函数:

1. (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

1. 获取Cloud Function URL，并使用curl命令发出POST请求，或者简单地将参数添加到Cloud Function URL，向Cloud Function端点发出HTTP请求，并直接在浏览器上查看结果.
li>

a.卷曲:

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.云功能网址:

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

这篇关于接收带有变量的HTTP请求，查询BQ和返回响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！