BigQuery上、SQLで動的変数を使う方法があれば教えて頂きたいです。

Question

現在以下のことを実行したく、トライアンドエラーを繰り返してます。
１、GETパラメーターから取得するluid：Uxxxxxxの値を変数に格納
２、その変数をSQLのwhere区で使用して、その動的に取得したluidと一致するデータをDBから取得する

調べてはみた所、変数に静的に値を指定して、それを使う方法はあるようなのですが、リクエストURLにあるパラメーターの値を使ってのSQLの書き方などがあればご教授頂きたいです。よろしくお願いします。

request_luidの中身はreturn request_luidでリクエストパラメーターの値が入っていることを確認できています。
また、BigQueryのコンソール画面からこちらのファイルから投げられたSQLの履歴を見ると
where区以下の動的変数で扱いたいrequest_luidがそのまま静的な文字として伝わっていました。

query = """SELECT EXISTS(SELECT 1
FROM `test-266778.conversion_log.conversion_log_2020*` as p
WHERE request_luid = p.luid AND orderid != '' limit 1000)"""
        ↑
　　　　ここが静的な変数名で伝わっている

【参照】
Google BigQuery ドキュメント

/home/user/api_dev/main.py

from flask import Flask, request, jsonify
from google.cloud import bigquery


app = Flask(__name__)


@app.route('/')
def get_request():
    request_luid = request.args.get('luid') or ''
    client = bigquery.Client()

    query = """SELECT EXISTS(SELECT 1
FROM `test-266778.conversion_log.conversion_log_2020*` as p
WHERE @request_luid = p.luid AND orderid != '' limit 1000)"""

    job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("request_luid", "STRING", request_luid)
    ]
    )


    query_job = client.query(query)
    query_res = query_job.result()
    for row in query_res:
        return jsonify({request_luid:query_res.total_rows})


if __name__ == "__main__":
    app.run()

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

[amaturePy]

失礼しました。
修正しました。

Answer 1

いまさらですが、

query_job = client.query(query)

は

query_job = client.query(query, job_config=job_config)

ではないでしょうか (試しておりません)。

Answer 2

クエリパラメータを外部的に使えるのはツールの機能な気がします。
Google データポータルのBigQuery Connecterでクエリパラメータが使えるようになったので触ってみる

質問のような使い方をする場合は動的SQLで、EXECUTE IMMEDIATEでのusingだと思います。

Comments

[amaturePy]

ありがとうございます！全く気付きませんでした。何度か使おうと試しているのですが、
[ 400 Syntax error: Unclosed string literal at [2:1]]のエラーが出てしまい。
上で記載しているSQLとの組み合わせ方がうまく掴めず、、、こちら分かる範囲でお教え頂きたいのですが、現状以下の書き方で試し他のですが、アドバイスなどあれば頂きたいです。

query ="""EXECUTE IMMEDIATE
"SELECT EXISTS(SELECT 1
FROM `test-266778.conversion_log.conversion_log_2020*` as p
WHERE p.luid = @request_luid AND orderid != '' limit 1000)"
USING request_luid as request_luid"""

[sazi]

”のエスケープが逆じゃないでしょうか。
query ="EXECUTE IMMEDIATE ""SELECT EXISTS(SELECT 1 FROM `test-266778.conversion_log.conversion_log_2020*` as p WHERE p.luid = @request_luid AND orderid != '' limit 1000)"" USING request_luid as request_luid"

[amaturePy]

ありがとうございます！頂いた物も試したのですが以下のエラーが出てしまい。再度ドキュメントなどを読みながら頂いたSQLを編集しながら試そうと思います。
google.api_core.exceptions.BadRequest: 400 Syntax error: Unexpected keyword SELECT at [1:19]