What is the best way to manage an upsert with CloudSpanner based on some conditions?

Viewed 150

I have this (simplified) case, with 2 tables related in this way:

CREATE TABLE a (ida STRING(36) NOT NULL, name STRING(15)) PRIMARY KEY (ida);
        
CREATE TABLE b (idb INT64 NOT NULL, ida STRING(36) NOT NULL) PRIMARY KEY (ida, idb)
INTERLEAVE IN PARENT a ON DELETE CASCADE;

where my ida is a UUID4 id format that I am generating from my code (Python 3).

In my case a batch of a few thousands "tuples" (idb, name) is sent to my service.

  1. If idb does not exist in the table b then create a uuid4 and do the following inserts:

    my_uuid_1 = uuid.uuid4().__str__() # generated via Python3

    idb = 123 # received from the request

    INSERT a (ida, name) VALUES (my_uuid_1, 'John')

    INSERT b (idb, ida) VALUES (123, my_uuid_1)

  2. If idb exists in the table b then just update the table a with the eventual new name.

Now this process needs to be run in a batch way for multiple records that can generate the situation just described. In order to do this with CloudSpanner I have been looking to the functionality:

def _unit_of_work(transaction):
    try:
        transaction.insert_or_update(
            table=table,
            columns=columns,
            values=values,
        )
    except BadRequest as err:
        logging.error(f'Error: ${err.args}')
        raise

spanner_database = spanner_instance.database(database_id=my_database_id)
spanner_database.run_in_transaction(_unit_of_work)

but I cannot see a way to use it with the condition from before. I am looking in the right direction or there is a better way to do it?

1 Answers
Related