Using BigQuery Data as source data for loading chart data | C2C Community
Solved

Using BigQuery Data as source data for loading chart data

  • 12 January 2022
  • 2 replies
  • 359 views

Userlevel 2
Badge

Question on using BigQuery as a data source to load chart data into web app built with React JS, hosted on Firebase.

 

Currently, can export data from BQ into Data Studio and create chart. Chart can be used to create React component hosted at Firebase.  However - 

Would like to enable additional interactivity by allowing user select portions of chart data to export into server, as new inputs that interact with other React components.

 

Can export with Google Charts, and other chart programs (high charts, fusion charts, amCharts, ChartsJS, etc).

Have not found any documentation regarding the creation of data source using BigQuery for data loading into Google Charts…

If anyone has advice to enable this functionality, would greatly appreciate a nudge in the correct direction.

Thank you!

icon

Best answer by deok 14 January 2022, 17:40

View original

2 replies

Userlevel 2

Hi Brett,

I’m not too familiar with Google Charts, but I understand you can use csv files as a data source, is that correct?

If so, you’re able to export csv files with your data from BigQuery and ingest it into Google Charts. This could be scheduled to run as often as you’d like. 

You would need two pieces to make this work:

  1. Cloud Scheduler: Schedule how often your Cloud Function will be triggered.
  2. Cloud Functions: This function will export your BigQuery table(s) data as csv.

I understand this can be a lot of work just to get data on Google Charts, so as far as data visualization and business intelligence goes, Looker is our recommendation for you to get the most insights from your data. Read more about it here.

 

Best,
Deok

Userlevel 2
Badge

Hi Deok,

Sorry for the delayed response. Your advice was extremely helpful.

As it happened, there was a conversion that took place in Google Studio shortly after I posed my question.

There was a huge improvement in Google Studio where the underlying technology became Looker, something you mentioned in your response. I found it much easier and more flexible the old Google Studio

I was able to connect my bigquery data source to Google Studio/Looker Studio Charts and on a daily basis update the data in the chart with newly imported data.

That’s the simplified explanation, the more complex aspect was to decide between the following:

  1. Run one or more daily queries and export the csv into one of several locations, each with their own advantages and disadvantages, to populate the charts, or
  2. Run one or more daily queries to populate a new bigquery table daily and connect the bigquery table to Google Studio/Looker Studio Charts, or
  3. Create a new bigquery table for the sole purpose of connecting to Google Studio/Looker Studio Charts using the same source file that is used to update the bigquery tables each day.

Option Three was the option chosen for the following reason:

  1. Wrote a python script to load the json file from its gcs storage bucket into bigquery each day. Used a combination of gsutil, bq and python to load the json newline delimited file into two tables, one a date parameterized table with eight years history. The script for this table, appends to the table.
  2. The second table is only the “latest” day, because it only holds the updated data. The script overwrites the table.
  3. When connecting bigquery as a datasource for Google Studio/Looker Studio Charts, a great deal of front end work can be done on the front end to provide consistent charts with current data.

As I said, very pleased with the upgrades and definitely recommend the use of scripts whenever possible.

Reply