When i move document betwen couchbase collections, how do i add time of moving?

user_17643099 0
Viewed 80

I have collection with some documents. Sometimes i need to move certain document to another collection (aka archive) and i also need to add archiving time to it. Now i do it in two queries, but want to reduce to one.

Due to lack of experiense in NoSQL, it`s hard to me to make any assumption, so i am asking for help.

query = f"""INSERT INTO bucket.scope.second_collection (KEY key_id, VALUE document)
            SELECT META(doc).id AS key_id, doc AS document
            FROM bucket.scope.first_collection AS doc
            WHERE id = {};"""
cluster.query(query, QueryOptions(positional_parameters=[]))

time = datetime.now(tz)
query = f"""UPDATE bucket.scope.second_collection
            SET date_of_moving = "{time}"
            WHERE id = "{}";"""
cluster.query(query, QueryOptions(positional_parameters=[]))
n1ql couchbase nosql python database
2 Answers

You can use OBJECT_ADD() to add additional fields to an existing JSON object.

I'm no Python dev, so pardon if I get some syntax wrong, but here's an example of OBJECT_ADD:

query = f"""INSERT INTO bucket.scope.second_collection (KEY key_id, VALUE document)
            SELECT META(doc).id AS key_id, OBJECT_ADD(doc, ""date_of_moving"", {time}) AS document
            FROM bucket.scope.first_collection AS doc
            WHERE id = {};"""
user_400150

Just for completeness say you have a lot of data 1B+ docs, and you don't have (or want) an Index on your collection there is another "low-code" in server alternative.

The Couchbase Eventing Service can be used to easily do this as a point tool (or a full time lambda) assume you have two collections b01._default._default and b02._default._default and you need to move some documents from b01._default._default to b02._default._default based upon some filter (and of course add a time stamp).

We create a Function, say archive_and_enrich, would have the following settings (note under the expandable [advanced] settings if you can increase the number of workers you can get more performance if needed)

enter image description here

Then you add the Function's JavaScript code and Save your Function.

function OnUpdate(doc, meta) {
    // apply some filtering or business logic to only move the required documents
    // here we just ensure the property "state" eixists and is equal to "ready_to_move"
    // the filtering can be any complex business logic you need.
    if (!(doc.state && doc.state === "ready_to_move")) return;
    
    // update the local copy we received via DCP with a date stampe or Date
    doc.archiving_time = Date.now();    // this is a # millis since epoch 
    // doc.archiving_time = new Date(); // this is a date string
    
    // optional log what is going on to the Eventing Function's application log
    log("key " + meta.id + " copied to b0, added property .archiving_time " + doc.archiving_time);
    
    
    try {
        // write to the destination collection via the alias dst_col
        dst_col[meta.id] = doc;
        try {
            // delete from source collection via the alias src_col
            delete src_col[meta.id];
        } catch (e2) {
            log("archived key " + meta.id + " but failed to remove it from the source, issue: " + e1);
        }
    } catch (e1) {
        log("failed to archive and remove key " + meta.id + "issue: " + e1);
    }
}

Now you can Deploy you function to active and enrich your data for example in b01._default._default I make the following document with key "test:0000001"

{
  "state": "not_ready_to_move",
  "type": "test",
  "id": "0000001",
  "date": "Lorem ipsum dolor sit amet, consectetur adipiscing elit"
}

It will be ignored, now change the "state" of this document to "ready_to_move" (I used the UI).

Now we see that the document is copied from the source collection to the target collection and then it is deleted from the source. You are left with the following in b02._default._default - with the same key:

{
  "state": "ready_to_move",
  "type": "test",
  "id": "0000001",
  "date": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
  "archiving_time": 1644953704961
}

In addition the first log(...) statement in the JavaScript will emit something like:

2022-02-15T11:35:04.961-08:00 [INFO] "key test:0000001 copied to b0, added property .archiving_time 1644953704961"

In actual production you will not want to emit this messages - only those log(...) statements on exceptions - as such you would remove and comment out the first log(...) statement.

A very modest cluster 3 x r52xlarge can easily archive over 50K docs per second. But a large well tuned Couchbase cluster can archive about 1/2 a million docs per second via this style of lambda (but your workers in your Eventing function will need to be high - say 24 and you should have a lot of cores).

You might also want to look at a toolkit to move information from a bucket paridigm into collections paridigm https://github.com/jon-strabala/cb-buckets-to-collections

user_124530320
Related