Warm tip: This article is reproduced from stackoverflow.com, please click
flask google-bigquery pandas python

Render JSON response from BigQuery using Pandas?

发布于 2020-03-27 10:18:24

I'm a Ruby dev doing a lot of data work that's decided to switch to Python. I'm enjoying making the transition so far and have been blown away by Pandas, Jupyter Notebooks etc.

My current task is to write a lightweight RESTful API that under the hood is running queries against Google BigQuery.

I have a really simple test running in Flask, this works fine, but I did have trouble rendering the BigQuery response as JSON. To get around this, I used Pandas and then converted the dataframe to JSON. While it works, this feels like an unnecessary step and I'm not even sure this is a legitimate use case for Pandas. I have also read that creating a dataframe can be slow as data volume increases.

Below is my little mock up test in Flask. It would be really helpful to hear from experienced Python Devs how you'd approach this and if there are any other libraries I should be looking at here.

from flask import Flask
from google.cloud import bigquery
import pandas

app = Flask(__name__)

@app.route("/bq_test")
def bq_test():
    client = bigquery.Client.from_service_account_json('/my_creds.json')
    sql = """select * from `my_dataset.my_table` limit 1000"""
    query_job = client.query(sql).to_dataframe()
    return query_job.to_json(orient = "records")

if __name__ == "__main__":
    app.run()
Questioner
Raoot
Viewed
73
67.1k 2019-07-04 03:20

From the BigQuery documentation-

BigQuery supports functions that help you retrieve data stored in JSON-formatted strings and functions that help you transform data into JSON-formatted strings:

JSON_EXTRACT or JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal), which returns JSON values as STRINGs.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), which returns scalar JSON values as STRINGs.

Description

The json_string_expr parameter must be a JSON-formatted string. ...

The json_path_string_literal parameter identifies the value or values you want to obtain from the JSON-formatted string. You construct this parameter using the JSONPath format.

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions