Google BigQuery data source PREVIEW release

shaaland · 04-28-2021 12:46 PM

Hi AppSheet Community,

PLEASE NOTE: This is now released as GA and you can read about it in this announcement.

I’m very excited to announce PREVIEW availability of the Google BigQuery data source. Please read the attached PDF
Using the BigQuery Datasource - Preview User Guide-v2.pdf (2.6 MB)
for all the details, including licensing requirements (Enterprise plans required), Limitations (like Read-only and 100K rows max), and a full set of instructions with screenshots for getting your first BigQuery dataset added as an AppSheet table, including how to create a Google Cloud Service Account and Key.

This is a PREVIEW release, so please do not use this data source for production use cases. We will announce GA in 2-3 months after we have user feedback and have fixed any reported issues.

Where do I find the BigQuery data source?

Go to “My Account” / “My Account” / and select the “Sources” tab. You can add a new data source of type “Cloud Database”. When you click on the dropdown selection, you will see “BigQuery” as a new type of cloud database to choose from. Please follow the instructions in the PDF to get the required inputs to authenticate. If you don’t see it yet, please check back soon, it should be rolling out to all users today.

Please feel free to send us your use cases! I would love to hear about what you plan to do with BigQuery data in your AppSheet apps or automations. You can fill out this Google Form with your information!

Thanks,
Scott
AppSheet Product - Connectors

Steve

Austin

Might not really be a question for Appsheet, but what benefits would there be in using BigQuery instead of say a normal cloud MySQL database with 100k rows? Apart from someone that already uses BigQuery. Or is this just an expansion of feature set? I mostly ask cause it would be a google to google interaction.

shaaland

Hi @Austin_Lambeth ,

It would be primarily for folks who are already using BigQuery. In a lot of cases, there are already feeds coming from applications to BigQuery to support the business reporting use case, potentially from 10, 20 or even 50 applications. If any of that data is helpful to have in an AppSheet app as reference data to assist in filling in data gaps.

Let us say you have a product ID and name in your app backed by a sheet, but none of the product details like weight, dimensions, color, etc, because that information is locked away in an on-premises legacy application. However, there is already a nightly extract that is loading that into BigQuery. Now you can tap into that product database in BigQuery and use those additional attributes to make your sheets based app richer. And, you don’t have to create a new CloudSQL DB and figure out how to also extract and keep the info up to date in the CloudSQL DB.

Technically, if you have the ability to have the data in CloudSQL or in BigQuery, you can accomplish the same thing (given you can whiltelist access to CloudSQL, etc…), but it will require more DBA skills to do the CloudSQL route.

R,
Scott

shaaland

Hi All,

I’ve uploaded a new version of the PDF instructions in the original announcement above ^^^. This version includes steps to create a custom role in GCP for accessing Private DataSets in GCP Projects where the security policies are more locked down than average. By creating this custom role and then sharing access to your private dataset with your service account for this new role, you should be able to able to access your dataset from AppSheet.

Look for the section “Accessing a Private DataSet” on page 9 in the PDF for these new instructions.

Symptoms that you will see that indicate you may need this additional configuration:

You create your Data Source to your DataSet
You try to add a Table to your App using the data source
When you try to select a table, it just spins for a very long time and never gives you a list of tables

Let me know if you have any questions. Also, please take a few minutes to let us know about your use case in this Google Form. Thanks in advance.

R,
Scott

Josephine

Hi @Scott_Haaland
Very exciting! In regards to the difference between MySQL / BQ, we definitely have cases where we have data in BQ and use a few more steps to get it into MySQL. I’m wondering whether there would be any increase in performance if we use BQ? (similar to how MySQL is better than sheets)
Thanks!
Jo

shaaland

I think MySQL is going to be one of your most efficient options. It will all depend on the security filters you are using and the indexes on the database (if they are aligned with your security filters).

In some cases, you may see BigQuery perform better and in others MySQL will perform better…it just depends on how the MySQL DB is tuned. The great thing about BigQuery is that you don’t have to think about any of these tuning options…it just works :). Remember though that BigQuery is read-only at this point.

R,
Scott

Josephine

Hi @Scott_Haaland

Alas, there’s rarely ‘the’ answer in Appsheet world!
Interesting that you say…

The doco posted initially in this thread says there’s no plans for it to be write. Has that changed?

Thanks
Jo

shaaland

@Josephine – based on customer feedback…we are evaluating whether we can add write capabilities. In this regard, our main concern, actually, is write performance in BQ. We expect it to be quite a bit slower than Cloud SQL, since BigQuery is optimized for batch writes coming in as ETL like updates from other DB’s. We are still evaluating/analyzing, but we do have several customers who have requested write capabilities and have given us some use cases where it makes sense to have this feature.

This is exactly why we run Preview programs as it gives us an excellent opportunity to get feedback as people start to use the feature.

A long way to say: Yes, we are looking into adding Write operations, and I’ll update this thread when I have more details.

If anyone wants to share additional use cases for insert/update/delete operations in BigQuery, please send them my way. You can send me a private message here on the community or send me an email : shaaland@google.com with a subject starting with [BigQuery Use Cases]

R,
Scott

Josephine

Thanks @Scott_Haaland - if we get internal approval to be part of the testing, be sure we’ll send some feedback!

Dilip1

Wanted to know if there’s any way to connect to GCP BigQuery datasource from AppSheet without having a service account key. The reason I’m asking, we aren’t allowed to generate keys for any of our service account due to security concerns.

Thanks,
Dilip