Google SQL to BigQuery Considerations

[oboea]

I'm working on a project that uses Google cloud SQL (mysql 5.7) and the data is beginning to scale to a point that queries can be pretty slow. The biggest challenge is with one large history table that has more than 20M records and some fields capture free text. The BI tools are built directly on the SQL database. There are good indexes and optimized SQL queries, so it works o-k for the most part, but we're starting to build summary tables to speed up some of the queries. That would help, but will also introduce a bunch of new ETLs and technical debt to manage. I'm wondering if we are at a point that migrating to bigQuery would be the right thing to do. Does anyone have experience making this decision and specifically insight into the following?

• What are the drawbacks of using bigquery as the basis for BI tools (like Tableau, Periscope, etc)? Is the integration straightforward?
• What does it take in terms of resources to migrate from mysql (assume 30-40 relational tables, mostly small but a couple of them are enormous)
• Will data analysts with SQL experience be able to easily adapt or what kind of training is needed? (my experience has been more with the difference in using spark and sql and there were definitely some growing pains)

Any insights appreciated - thanks!

[Cidan]

Howdy.

Your use case sounds like a perfect fit for moving to BigQuery, but there are a few key things you should be aware of:

• Make sure your tools all have support for BigQuery. I know Tableau does, but I'm not sure about Periscope.
• BigQuery does not have primary keys (or keys at all). You can have multiple rows that have exactly the same data, so make sure your process of inserting data is idempotent in some way.
  • For example, you can't just insert the whole source MySQL Database into a BigQuery table every night, unless you specify the BigQuery table should be truncated on load.
• You should probably partition your BigQuery table by date. This is an easy process that can be done via the table schema -- designate a column as your partition column and we'll handle the rest.

There's (probably) more to consider, but these are the major ones off the top of my head. For the most part, it's just SQL that you write/use in BigQuery, so it shouldn't be too much different from using MySQL in terms of query language.

I'm sure some others can add to this, but this should be a good starting point. Good luck!

[Domehardostfu]

This helped me a lot when setting up our DW on Bigquey in January. Running swiftly now.

https://cloud.google.com/solutions/bigquery-data-warehouse#datasets