How to update data in google cloud storage/bigquery for google data studio?

0
votes

For context, we would like to visualize our data in google data studio - this dataset receives more entries each week. I have tried hosting our data sets in google drive, but it seems that they're too large and this slows down google data studio (the file is only 50 mb, am I doing something wrong?).

I have loaded our data into google cloud storage --> google bigquery, and connected my google data studio to my bigquery table. This has allowed me to use the google data studio dashboard much quicker!

I'm not sure what is the best way to update our data weekly in google cloud/bigquery. I have found a slow way to do this by uploading the new weekly data to google cloud, then appending the data to my table manually in bigquery, but I'm wondering if there's a better way to do this (or at least a more automated way)?

I'm open to any suggestions, and if you think that bigquery/google cloud storage is not the answer for me, please let me know!

1
google-cloud-platformgoogle-bigquerygoogle-data-studio
Hi Ethan. Do you know any programming languages? BigQuery has some APIs which can be used to upload / update datasets. This is a good place to start cloud.google.com/bigquery/docs/loading-data-local – Bobbylank
Hi Ethan - where is your data stored now? There are several connectors to BigQuery, to may be able to use one of these to automatically pull your new data into BigQuery as required. – Ben P
How your dataset is created and where? – guillaume blaquiere
At least you may leverage BigQuery GCS transfer to automate part of your work: cloud.google.com/bigquery-transfer/docs/cloud-storage-transfer – Yun Zhang

1 Answers

0
votes

If I understand your question correctly, you want to automate the query that populate your table, which is connected to Data Studio.

If this is the case, then you can use Scheduled Query from BigQuery. Scheduled query allow you to define a query which results can be inserted in a new table. Particularly you can specify different rules for repetition (minimum each 15 minutes) and execution, as well as destination writing options (destination table, writing mode: append, truncate).

In order to use Scheduled Queries your account must have the right permissions. You can have a look at the following documentation to better understand how to use Scheduled Query [1].

Also, please note that at the front end the updated data in the BigQuery table will be seen updated in Datastudio at each refresh (click on refresh button in Datastudio). To automatically refresh the front-end visualization you can use the following plugin [2] or automate the click on the refresh button through Browser console commands.

[1] https://cloud.google.com/bigquery/docs/scheduling-queries

[2] https://chrome.google.com/webstore/detail/data-studio-auto-refresh/inkgahcdacjcejipadnndepfllmbgoag?hl=en