Export InterSystems Cache to Couchbase

[martino]

I am still new and inexperienced in using Couchbase?
But I’m already in love with this database.

I am trying to export data from InterSystems Cache to Couchbase. Only I notice that this is not going too well with regard to the Ram consumption that Couchbase picks up. Key is too long. Cache has Keys in a tree and I can’t get them into Couchbase myself because I can only store 1 key here. So I thought I’d line up the key and put it in here. example: Key1|Key2|Key3 and then I place that in Coucbase as a Key value. I just notice that this is not really very fast. What should I do to get this right and fast?

With InterSystems, the key is stored as a tree. So if you search for Key1 you will see all Key2 and if you search for Key2 you will see all Key3. Unfortunately I don’t have this in Couchbase. To get this working again I have to put Key1, Key2 and Key3 in the Json document. To get only Key1 I have to give Couchbase the command where “Key2 is missing” but then I have to place an index on it otherwise this will take a very long time.

I just want to use Couchbase for the N1sql because it is really nice to make analysis. So can someone tell me what I need to do now to set this up efficiently and quickly?

Is there a way to search for Key1 and then jump to the next document until it has gone through them all? I only get this working from N1sql but then I have to create an index for it, because I can’t use the Key index for this.

[matthew.groves]

Hi @martino,

It’s great that you’re enjoying Couchbase and N1QL (aka SQL++).

I’ve been thinking about your problem a little bit. It sounds like the main issue is that each “record” in intersystems has a potentially very long key. In addition, there’s a tree, so a document with a key of “key1” and “key2” should return when you’re querying for key1, key2, or key1|key2.

Your efforts with putting the keys into JSON probably looked like this:

{
  "key1" : "key1value",
  "key2" : "key2value",
  ... etc ...
}

Does that sound right? If so, I can see why indexing might be an issue.

Have you considered putting them into an array? Something like:

{
  "interSystemsKeys" : ["key1value", "key2value", ... etc ... ]
}

Then you can write queries along these lines:

select i.*
from intersystems i
where ARRAY_CONTAINS(i.interSystemsKeys,"key1value")
and ARRAY_CONTAINS(i.interSystemsKeys, "key2value")

Generally, the idea is to put that “tree” into an array.

There is probably a better way to query that array, and I’m not sure what the best index would be for it. But if you like this approach, you might ask those questions in N1QL - Couchbase Forums

[martino]

Thank you matthew,

Unfortunately that solution doesn’t work but this isn’t the biggest problem I have either

I have a lot of records, think about 64,000,000 documents.
What I notice is that if I have 1 TB of data I also need 1TB of ram memory, if indexes also need to be added I need even more ram memory.

Can you set Couchbase to store only the index in RAM and the documents on disk?

Because this is the biggest problem I’ve run into.
I’m testing this with real big data, and then I don’t think it’s a good idea if you also store all documents in ram.
The question is whether you can set up Couchbase in such a way that it does not load all the documents completely into ram?

[matthew.groves]

@martino, you might want to check out the new Magma storage engine. Storage Engines | Couchbase Docs

It’s designed for large datasets that do not fit in memory.

As far as using memory for indexes, you might also want to check out Memory-Optimized Index (MOI) Storage - Storage Settings | Couchbase Docs which can improve indexes in-memory.

MOI must be configured at the cluster level; Magma must be configured on the bucket level.