CODEWITHSUNDEEP
google-sheetsgoogle-bigquerybigdata
ASP YOK
ASP YOK

Reputation: 131

BigQuery GSheet Upload

Connecting a GSheet to BigQuery is easy and a very cool feature, but it is very slow. It's seems like the data which is stored through an ETL process etc. is really stored in BigQuery, while G-Sheets are only "connected". Is there somehow a way to store data more efficient or does others experience the same and knowing a work around?

Upvotes: 1

Views: 437

Answers (1)

Alexey Maloletkin
Alexey Maloletkin

Reputation: 1099

Problem with Google Sheet is not only slowness - but also potential human involvement into editing process. So somebody just come and edit (by removing column :) ) - and all your queries will stop working. Idea we implemented:

  1. Create federated table T1 on Spreadsheet
  2. Periodically dump (depends on need of freshness) data from T1 to real bigquery table T2 with overriding everything.
  3. Use T2 everywhere

Result: you got fast queries (no federated object involved) - and if somebody break spreadsheet - it will not go from T1 to T2 - and you will know about issue but in the same time all queries will continue to work without issue with last known good data.

As idea you may initiate load from modify event on spreadsheet - but I will not go this route - too many moving parts would be involved and reliability of Gscripts still not very high

Upvotes: 3

Related Questions