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()
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
Stack Overflow prefers that "answers" should not consist of just a link. More discussion here: meta.stackoverflow.com/a/265553/242457