AWS QLDB ion_document update

Question

I have code that insert data into AWS QLDB using partial SQL and ion documents. Now want to update a document inside QLDB, and I can't find any example on it. Please help!

statement = 'INSERT INTO MY_TABLE'
ion_documents = loads(dumps(MY_JSON_DATA))

def query_lambda(tx_executor, query=statement, docs=ion_documents):
    return tx_executor.execute_statement(query, [docs])

def retry_lambda():
    print ('Retrying')

cursor = session.execute_lambda(query_lambda, retry_lambda)

score 2 · Accepted Answer · answered Feb 25 '20 at 18:28

As you note, you need to use PartiQL statements to update documents. The code snippet you have to insert a document is most of what you need to update it: the only change you need to make is the statement that you're executing.

The documentation has a Python tutorial which includes examples of updating documents: https://docs.aws.amazon.com/qldb/latest/developerguide/getting-started.python.step-5.html.

For example (from the above link), the following would update the owner of a vehicle in the sample application:

def update_vehicle_registration(transaction_executor, vin, document_id):
    statement = "UPDATE VehicleRegistration AS r SET r.Owners.PrimaryOwner.PersonId = ? WHERE r.VIN = ?"
    parameters = [document_id, convert_object_to_ion(vin)]
    cursor = transaction_executor.execute_statement(statement, parameters)
    try:
        print_result(cursor)
        logger.info('Successfully transferred vehicle with VIN: {} to new owner.'.format(vin))
    except StopIteration:
        raise RuntimeError('Unable to transfer vehicle, could not find registration.')

Note the use of ? as bind parameters. These will be bound to the values passed into the second argument of execute_statement (in corresponding order).

Here is some information on PartiQL update statements: https://docs.aws.amazon.com/qldb/latest/developerguide/ql-reference.update.html. The syntax is:

UPDATE table [ AS table_alias ] [ BY id_alias ]
SET element = data [, element = data, ... ]
[ WHERE condition ]

The results of running an update statement will be the document id(s) that were affected by the update.

AWS QLDB ion_document update

1 Answers1