google-bigquery - 如何从 BigQuery API 获取列名?
问题描述
我可以使用以下代码获取列值:
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'C:\\Users\xxx\Desktop\key.json'
bq_client = Client()
query = "SELECT msts, coreuserid, spend_usd FROM `project.f_purchase` where dt = '2019-04-02' limit 5"
query_job = bq_client.query(query)
results = query_job.result()
for row in results:
print("{}, {}, {}".format(row.msts, row.uid, row.spend_amount))
但如最后一行所示,这需要直接列名。现在我有多个查询,我想运行它们并显示结果。有没有办法.format(row.column1, row.column2...)
?此外,查询的结果列数不同。
任何帮助表示赞赏。
解决方案
根据 BigQuery Python 客户端文档,您可以按如下方式遍历行对象,而无需指定确切的列名:
for row in query_job: # API request - fetches results
# Row values can be accessed by field name or index
assert row[0] == row.name == row["name"]
print(row)
此外,您始终可以使用本答案中描述的SchemaField 值
result = ["{0} {1}".format(schema.name,schema.field_type) for schema in table.schema]
这是一个使用 BigQuery 公共数据集的示例,说明如何在不指定字段名称的情况下访问字段:
from google.cloud import bigquery
from pprint import pprint
import json
client = bigquery.Client()
query = (
"SELECT state,max(gender) as gender FROM `bigquery-public-data.usa_names.usa_1910_2013` "
'GROUP BY state '
"LIMIT 10"
)
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location="US",
) # API request - starts the query
for num, row in enumerate(query_job, start=1): # API request - fetches results
# Row values can be accessed by field name or index
# assert row[0] == row.name == row["name"]
print("{} AS {}, {} AS {}".format(row[0], query_job._query_results._properties['schema']['fields'][0]['name'], row[1], query_job._query_results._properties['schema']['fields'][1]['name']))
#print(row[0], row[1])
print(json.dumps(query_job._query_results._properties['schema']['fields'][0]['name']))
print(query_job._query_results._properties)
#pprint(vars(query_job._query_results._properties))
产生以下输出:
superQuery:bin tamirklein$ python test.py
AK AS state, M AS gender
AL AS state, M AS gender
AR AS state, M AS gender
AZ AS state, M AS gender
CA AS state, M AS gender
CO AS state, M AS gender
CT AS state, M AS gender
DC AS state, M AS gender
DE AS state, M AS gender
FL AS state, M AS gender
推荐阅读
- terminal - 终端中的日期格式
- windows - 如何将 ipa 构建上传到苹果 testflight
- sql - 在 sql 中更改 NULL 日期值
- python - 如何检索关于场景上 QGraphicsPixmapItem 的 QGraphicsRectItem 坐标?
- android - Appcelerator android用户打开gps设置
- ios - 是否可以使用颤振在 Ios 中同时录制音频和播放声音?
- asp.net-core - DataAnnotations 可以破坏端点吗?
- r - 如何在R中对栅格对象进行分层
- mongodb - 具有更大数据集的 MongoDB $lookup + $sort
- python - “ORA-12154:TNS 无法解析服务名称”,python cx_Oracle 中持续存在的问题