最近 GCP BQ 支持動(dòng)態(tài) SQL。我想用 Cloud Functions 試試這個(gè)。我的 BQ 動(dòng)態(tài) SQL（在 UI 上運(yùn)行）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 代碼傳遞值，但問題是，它會(huì)被 Python BQ lib 支持嗎？任何示例python代碼？我試過這些代碼，但沒有運(yùn)氣代碼 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))錯(cuò)誤：, 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))錯(cuò)誤：line 130, in result raise self._exception google.api_core.exceptions.BadRequest: 400 Syntax error: Illegal input character "%" at [3:104]
2 回答
嗶嗶one
TA貢獻(xiàn)1854條經(jīng)驗(yàn) 獲得超8個(gè)贊
最后，我找到了正確的語(yǔ)法。還注意到我的代碼中存在一些錯(cuò)誤。
錯(cuò)誤：
sql=('''.......''')SQL 變量 ( )中的 SQL 查詢語(yǔ)法錯(cuò)誤最后一行的打印語(yǔ)句是錯(cuò)誤的，我的選擇查詢沒有
url and view_count列。在動(dòng)態(tài) SQL 中，我們必須在 where 條件中傳遞單引號(hào)（如果它是一個(gè)字符串）
示例工作代碼：
代碼 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貢獻(xiàn)1852條經(jīng)驗(yàn) 獲得超7個(gè)贊
Jinja2 SQL 模板是構(gòu)建動(dòng)態(tài) 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 }};
添加回答
舉報(bào)
0/150
提交
取消