Add field to existing documents over million records

user_4383715 0
Viewed 505

Scenario

We have over 5 million document in a bucket and all of it has nested JSON with a simple uuid key. We want to add one extra field to ALL of the documents.

Example

ee6ae656-6e07-4aa2-951e-ea788e24856a
{
   "field1":"data1",
   "field2":{
      "nested_field1":"data2"
   }
}

After adding extra field

ee6ae656-6e07-4aa2-951e-ea788e24856a
{
   "field1":"data1",
   "field3":"data3",
   "field2":{
      "nested_field1":"data2"
   }
}

It has only one Primary Index: CREATE PRIMARY INDEX idx FOR bucket.

Problem

It takes ages. We tried it with n1ql, UPDATE bucket SET field3 = data3. Also sub-document mutation. But all of it takes hours. It's written in Go so we could put it into a goroutine, but it's still too much time.

Question

Is there any solution to reduce that time?

n1ql couchbase go
2 Answers

As you need to add new field, not modifying any existing field it is better to use SDKs SUBDOC API vs N1QL UPDATE (It is whole document update and require fetch the document).

The Best option will be Use N1QL get the document keys then use SDK SUBDOC API to add the field you need. You can use reactive API(asynchronously)

You have 5M documents and have primary index use following

val = ""
In loop
    SELECT RAW META().id FROM mybucket WHERE META().id > $val  LIMIT 10000;
    SDK SUBDOC update
    val = last value from the SELECT

https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

user_60805360

The Eventing Service can be quite performant for these sort of enrichment tasks. Even a low end system should be able to do 5M rows in under two (2) minutes.

// Note src_bkt is an alias to the source bucket for your handler
// in read+write mode supported for version 6.5.1+, this uses DCP 
// and can be 100X more performant than N1QL.

function OnUpdate(doc, meta) {
    // optional filter to be more selective
    // if (!doc.type && doc.type !== "mytype") return;

    // test if we already have the field we want to add
    if (doc.field3) return;

    doc.field3 = "data3";
    src_bkt[meta.id] = doc;
}

For more details on Eventing refer to https://docs.couchbase.com/server/current/eventing/eventing-overview.html I typically enrich 3/4 of a billion documents. The Eventing function will also run faster (enrich more documents per second) if you increase the number of workers in your Eventing function's setting from say 3 to 16 provided you have 8+ physical cores on your Eventing node.

I tested the above Eventing function and it enriches 5M documents (modeled on your example) on my non-MDS single node couchbase test system (12 cores at 2.2GHz) in just 72 seconds. Obviously if you have a real multi node cluster it will be faster (maybe all 5M docs in just 5 seconds).

user_124530320
Related