BigQuery table keeps changing key to _RowNumber

[Scott_Nesbitt]

I just started using BigQuery as a data source in AppSheet, and I cannot specify the key to be a regular field.  When I save, it changes it to _RowNumber.

The field is a String with an initial value of UNIQUEID().  I have done this exact thing many times in AppSheet connected to Google Sheets; is this something strange about the BigQuery data source?

[WillVerkaik · Accepted solution]

@Scott_Nesbitt Note that using Bigquery, the data is read only as you are just reading and not writing back.  Having an initial value of anysort, is likely not going to work.   The only value you can control will be creating virtual columns.

As for the key going back to _RowNumber.  Check the data type in Appsheet.  An anomoly occurs with the first import from Bigquery being all strings  turn into LongText.  If you try having that as a key, it will be rejected.  You have to change the LongText into just Text.   I do this for ALL columns that fall into LongText.  I don't know if it matters, but ususally data handling and performance are impacted in most data systems for varchar or blobs.   Better safe in my opinion.

If someone from Appsheet team can answer this, it would help better understand.

[Scott_Nesbitt]

I did notice that all the text fields were LongText, and set them all back, except for one.  The one I missed was, of course, the key field.  

Thanks for the heads up!

[WillVerkaik]

@ScoW I was pleasantly suprised that Bigquery allowed Appsheet to provide CRUD commands against it.  There are Use Cases that justify this.  However, as my instincts where originally correct.  Biquery is designed to be an OLAP system for Data Warehousing ( and now data lakes ).  Running CRUD commands against it as an OLTP is highly not recommended.   I am now playing with it to better understand and I do experience Synch issues from time to time.  I am not sure why, but it may be impacted by data colissions of attempting to engage as an OLTP as high volume transactions if not managed by complex systems properly will create data corruption.     The following link to Google cloud outlines that DML statements should be batch focus only.  Bigquery works with quotas and can frequently timeout.  This means your ACID transaction would be comprimised.   

If you can find out more, I would be curious as it does simplify some of my solutions.  https://cloud.google.com/bigquery/docs/best-practices-performance-compute#:~:text=If%20you%20need%20.... 

[WillVerkaik]

This is confirming my understanding from GCP documentation.

The 'D' in ACID test is definately out of question from the above statement.  A transaction can fail that is queued.  The following transactions could possibly be committed in absense of understanding the sequence of committing transactions.  Not sure if Appsheet has the intelligence  to know this, but I doubt it manages multiple concurrent users across varying sessions.  A DB is designed to manage this.   Best case Appsheet fails all users in sequence of the transactions being FIFO.  

Appsheet team if available to comment that would be helpful. 

[WillVerkaik]

I just discovered that writing back to Bigquery is possible from Appsheet, so ignore the 'read only' statement.   This opens up a lot more options.