使iPython BigQuery Magic函数SQL查询动态化 [英] Making iPython BigQuery Magic Function SQL query dynamic

问题描述

我正在Jupyter中使用bigquery魔术函数，并且希望能够动态更改项目和数据集.例如

I am using the bigquery magic function in Jupyter and would like to be able to dynamically change the project and dataset. For example

代替

%%bigquery table
SELECT * FROM `my_project.my_dataset.my_table`

我想要

project = my_project
dataset = my_dataset

%%bigquery table
'SELECT * FROM `{}.{}.my_table`'.format(project,dataset)

推荐答案

根据 IPython Magics for BigQuery 文档无法将项目或数据集作为参数传递；尽管如此，您仍可以使用BigQuery客户端库在Jupyter Notebook中执行此操作.

According to the IPython Magics for BigQuery documentation is not possible to pass the project nor the dataset as parameters; nonetheless, you can use the BigQuery client library to perform this action in Jupyter Notebook.

from google.cloud import bigquery

client = bigquery.Client()  

project = 'bigquery-public-data'
dataset = 'baseball'

sql ="""SELECT * FROM `{}.{}.games_wide` LIMIT 10"""
query=sql.format(project,dataset)

query_job = client.query(query)

print("The query data:")

for row in query_job:
# Row values can be accessed by field name or index.
print("gameId={}, seasonId={}".format(row[0], row["gameId"]))

我还建议您查看公共文档，以了解如何可视化BigQuery Jupyter笔记本中保存数据.

I also recommend you to take a look in public documentation to know how to visualize BigQuery data in a Jupyter notebooks.

这篇关于使iPython BigQuery Magic函数SQL查询动态化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！