CODEWITHSUNDEEP
google-bigquerygoogle-cloud-sqlbigdata
hum_hum_pa
hum_hum_pa

Reputation: 121

Google CloudSQL or BigQuery for Big Data Actively Update Every Second

So now I'm currently using Google CloudSQL for my needs.

I'm collecting data from user activities. Every day the number of rows in my table will increase around 9-15 million rows and always updated every second. The data including several main parameters like user locations (latitude longitude), timestamp, user activities and conversations and more.

I need to constantly access a lot of insight from this user activities, like "how many users between latitude-longitude A and latitude-longitude B who use my app per hour since 30 days ago?".

Because my table become bigger every day, it's hard to manage the performance of select query in my table. (I already implemented the indexing method in my table especially for most common use parameter)

All my data insert, select, update and more is executed from API that I code in PHP.

So my question is can I get much more better benefit if I use Google BigQuery for my needs?

If yes, how can I do this? Because is Google BigQuery (forgive my if I'm wrong) designed to be used for static data? (Not a constantly update data)? How can I connect my CloudSQL data into BigQuery in real time?

Which one is better: optimizing my table in CloudSQL to maximize the select process or use BigQuery (if possible)

I also open for another alterntive or sugget to optimize my CloudSQL performance :)

Thank you

Upvotes: 2

Views: 367

Answers (1)

Graham Polley
Graham Polley

Reputation: 14791

Sounds like BigQuery would be far better suited your use case. I can think of a good solution:

  1. Migrate existing data from CloudSQL to BigQuery.
  2. Stream events directly to BigQuery (using a async queue).
  3. Use time partitioned table in BigQuery.

If you use BigQuery, you don't need to worry about performance or scaling. That's all handled for you by Google.

Upvotes: 1

Related Questions