最近 GCP BQ 支持動態 SQL。我想用 Cloud Functions 試試這個。我的 BQ 動態 SQL（在 UI 上運行）declare cols string;set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='tbla');EXECUTE IMMEDIATE format("""select %s from `my_db.tbla`""",cols);我想table_name從我的 python 代碼傳遞值，但問題是，它會被 Python BQ lib 支持嗎？任何示例python代碼？我試過這些代碼，但沒有運氣代碼 1：def hello_gcs(event, context): table_name='tbla' client = bigquery.Client() job_config = bigquery.QueryJobConfig(use_legacy_sql=False) sql=( '''declare cols string;set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name=?);EXECUTE IMMEDIATE format("""select @ col from `my_db.tbla`""") using cols''',(table_name)) query_job = client.query(sql, job_config=job_config) results = query_job.result() for row in results: print("{} : {} views".format(row.url, row.view_count))錯誤：, line 130, in result raise self._exception google.api_core.exceptions.BadRequest: 400 Query error: Positional parameters are not supported at [3:104]from google.cloud import bigquerydef hello_gcs(event, context): table_name='tbla' client = bigquery.Client() job_config = bigquery.QueryJobConfig(use_legacy_sql=False) sql=( '''declare cols string;set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name=%s);EXECUTE IMMEDIATE format("""select @ col from `my_db.tbla`""") using cols''',(table_name)) query_job = client.query(sql, job_config=job_config) results = query_job.result() for row in results: print("{} : {} views".format(row.url, row.view_count))錯誤：line 130, in result raise self._exception google.api_core.exceptions.BadRequest: 400 Syntax error: Illegal input character "%" at [3:104]
2 回答
嗶嗶one
TA貢獻1854條經驗 獲得超8個贊
最后，我找到了正確的語法。還注意到我的代碼中存在一些錯誤。
錯誤：
sql=('''.......''')SQL 變量 ( )中的 SQL 查詢語法錯誤最后一行的打印語句是錯誤的，我的選擇查詢沒有
url and view_count列。在動態 SQL 中，我們必須在 where 條件中傳遞單引號（如果它是一個字符串）
示例工作代碼：
代碼 1：
from google.cloud import bigquery
table_name='tbla'
client = bigquery.Client()
job_config = bigquery.QueryJobConfig(use_legacy_sql=False)
sql="declare cols string;
set cols=(select STRING_AGG (column_name,',')
from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');
EXECUTE IMMEDIATE format(\"\"\"select %s from `manan.tbla` \"\"\",cols)".format(table_name)
print(sql)
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
for row in results:
print(row)
代碼 2：
from google.cloud import bigquery
table_name='tbla'
client = bigquery.Client()
job_config = bigquery.QueryJobConfig(use_legacy_sql=False)
sql="declare cols string;
set cols=(select STRING_AGG (column_name,',')
from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');
EXECUTE IMMEDIATE format(\"\"\"select ? from `my_db.tbla` \"\"\") using cols".format(table_name)
print(sql)
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
for row in results:
print(row)
代碼 3：
from google.cloud import bigquery
table_name='tbla'
client = bigquery.Client()
job_config = bigquery.QueryJobConfig(use_legacy_sql=False)
sql="declare cols string;set cols=(select STRING_AGG (column_name,',') from
`my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');EXECUTE IMMEDIATE
format(\"\"\"select @ col from `my_db.tbla` \"\"\") using cols as col".format(table_name)
print(sql)
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
for row in results:
print(row)
慕姐4208626
TA貢獻1852條經驗 獲得超7個贊
Jinja2 SQL 模板是構建動態 SQL 的更好選擇。例子：
create or replace table {{ params.targetTable }}
as
select
{{ params.targetColumnList|join(',') }},
cast(null as timestamp) as begin_timestamp,
cast(null as timestamp) as end_timestamp
from
{{ params.sourceTable }};
添加回答
舉報
0/150
提交
取消