Migration pathway for apps currently using CloudSQL backend to get them using BigQuery

[Michael_Luscomb]

Thanks to @MultiTech_Visions for their post My 2020 retrospective

It was great to hear from @praveen and @Scott_Haaland.
We were very interested to hear Scott mention that in the first couple of quarters of 2021 BigQuery might be offered as a datasource.

We are very busy writing business apps using CloudSQL as the datasource. We script the move of that data into BigQuery. We are wondering how hard is it going to be to get our existing apps migrated over to BigQuery when that time comes?

Will there be an ‘easy’ migration pathway available?

Thanks
Michael.

[Mayur_Vaikar]

Hi Michael,

Thanks for posting this question as lot of people may be needing this option to migrate existing Apps from other datasource.

This is something that Appsheet needs to consider when they give an option to connect to Big Query as a Datasource

Mayur

[MultiTech]

Michael_Luscombe:

We are wondering how hard is it going to be to get our existing apps migrated over to BigQuery when that time comes?

I have no information into how the BigQuery integration will work, but I can give you some insight based on how the SQL integration works.

One of the easiest ways to migrate the app over from (let’s say) Google Sheets to a SQL server - is to copy the app and select the different data source.

AppSheet then automatically creates all the tables, copies the data, etc. inside the SQL server for you.

I would imagine any other data source integrations would function similarly - at least I hope they do. (^_^)

[Mayur_Vaikar]

Hi @MultiTech_Visions ,

Can you please let me know in detail how this is achieved. When I try to copy the app, it does not give me an option to select different data source. Is this done in some other way which I am unaware of.

Thanks,
Mayur

[MultiTech]

Hey @Mayur_Vaikar

You’ll first have to have different data sources added to your account;

help.appsheet.com

Using Multiple Data Sources

Build an AppSheet app with data from multiple sources and providers.

For instance: these are the data sources I’ve connected to my work account…

…and when I make a copy of an app, there’s a dropdown where I can select the “Target” - the data source where I want a copy of the data made.

[Markus_Malessa]

I am just wondering what advantages/disadvantages you see migrating your data from Cloud SQL to BigQuery? I had not considered this myself so I am just interested.

[Michael_Luscomb]

Hi Markus,

after reading Scott’s erudite reply it has made me realise I am still very much feeling my way forward. As background, we started using BigQuery and developing in AppSheets less than 6 months ago.

I was under the misconception (was hoping) BigQuery could work as our OLTP (online transaction processing) database. I have since reread
https://cloud.google.com/solutions/bigquery-data-warehouse
and it is clear we should stick with our collect data via an app, then store into Cloud SQL, copy to BigQuery, then present in Data Studio.

Thanks
Michael

[shaaland]

Hi @Michael_Luscombe

Thanks for your interest in the BigQuery Data Source, and for your question.

Before I answer directly on the migration question, I’d like to understand your use case a bit more, and give a little bit of background on how we would expect BigQuery to work in terms of Cloud Database Use Cases.

First: Are you truly “Migrating” from Cloud SQL to BigQuery, or are you “Copying data regularly from Cloud SQL to BigQuery”.

My understanding of the primary use case for BigQuery is the latter. It should be used as “Read-only” copy of data from some other Database, which can then be used for Reporting and Data Analytics types of use cases (ie: a data warehouse). It is NOT expected to be used as an OLTP (online transaction processing) DB.

Therefore, our plan is to provide a Read-only capability with the BigQuery data source. If your apps are using read/write capabilities (adding rows and/or updating existing rows), then I would recommend you re-evaluate your decision to move from CloudSQL to BigQuery.

If you do have read/write use cases for Big Query that you would want to use an AppSheet app for, I would be very interested to understand the use case and we can also re-evaluate our decision to do read-only. However, this opens up a “can of worms” which may be hard to unwind later, so we want to be cautious here.

As stated in some of the other comments below, we would expect a migration (if you have read-only use cases) to be done in the same way as moving from Sheets to Cloud SQL…copy the app and point it to the new data source.

Let me know your thoughts. I’d love to hear about your use cases and plans for BigQuery.

Thanks,
Scott
AppSheet Product

[Michael_Luscomb]

Hi Scott,

thanks for your reply. I have done some further reading following the points you raised. We need the DB to be transactional so Cloud SQL is the solution for us.

To give you an overview of our current setup:
AppSheet uses Cloud SQL as the datasource,
Apache Airflow regularly pushes reference data (read only) from BigQuery into Cloud SQL,
BigQuery pulls data from Cloud SQL via an external connector,
views are then made in BigQuery which are used in Data Studio.

Thanks,
Michael

[shaaland]

Hi @Michael_Luscombe

This makes sense…and yes, I think staying with Cloud SQL will make the most sense for your transactional data. It is a very common misconception that you “could” use BigQuery (BQ) in this way, because it’s a DB in the cloud and very easy to use, but it gets really messy. For example, I don’t believe you can just update a row in BQ. You have to delete the row and then add it with the modified data. I would imagine this could cause all kinds of data integrity issues. The best practice is to update in the DB that is upstream to BQ—the one that is the transactional DB, and then on the next sync from that DB to BQ, the data will get the updated information.

I’m sure the reference data that you are pulling out of BQ is also being synced from some system of record database into BQ on a regular basis as well? Or is BQ the master data source for this reference data?

Thanks,
Scott

[Michael_Luscomb]

HI @Scott_Haaland,

we have a mix of BQ reference data, some created from scratch in BQ with others being pulled in from other systems like payroll, HR, CRM etc.

Cheers Michael