@amandam I believe we can call this thread resolved! Thank you so much for all of your help. To summarize my final solutions for future reference…
- The JSON structure for multiple tables in an external GCP BigQuery is below[1]. I think it would be helpful if the documentation referenced in my original post outlined what needs to be done for multiple tables (or at least the JSON structure).
- I learned that even if your intent is to simply READ from a BigQuery view you still need to make it Read/Write if you want objects (e.g. a textbox) to reference it and be editable. This creates a bit of a quandry because views by definition are read only. My solution to this was to query the view on page load and drop the necessary data into the objects w/o joining the objects to the actual view.
- When using GCP BigQuery (and presumably any cloud provider) for your database you will run into record locks if you are trying to update an existing record. The way I got around it is I created a view of the data that pulls the max row for a given user. This means I only had to append new records to my BigQuery table which is not a problem at all and I completely avoid record lock (aka Streaming Buffer conflicts).
[1]
{
“collectionPermissions”: [
{
“id”: “viewSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
},
{
“id”: “view-stm-leaderboard”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
},
{
“id”: “tblSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
}
]
}