Not able to run N1QL queries on Ephemeral buckets

query

#1

Hi,
Our spring boot app is using two buckets one is a Couchbase buckets and one Ephemeral bucket in the same cluster. Couchbase cache has a GSI index so I am able to write N1QL queries and get results. Since Ephemeral buckets only allow MOI index and the same cluster cannot have a GSI and MOI index I am unable to run N1Ql queries on the Ephemeral bucket.

The only query that actually fetches any data is a KV query findById(“125”). Another post on this forum suggested using “use key” but even though the query runs without errors it does not return any data.

SELECT * FROM bkt_cpblending_ephemeral AS bkt USE KEYS ‘bkt.applicationId’ WHERE bkt.applicationId = ‘125’

Can anyone suggest how we can run N1QL queries on an Ephemeral bucket without creating an index?

-T


#2

SELECT * FROM bkt_cpblending_ephemeral AS bkt USE KEYS [“documentkey1”, “documentkey2”] WHERE bkt.applicationId = ‘125’

USE KEYS needs to document key


#3

I did not follow. Are you suggesting that I pass the value of the key in USE KEY Eg: USE KEYS[“125”] or the name of the key attribute Eg: USE KEYS[“applicationId”]

Note the object I saved to the Ephemeral bucket has the following defination

public class LendingApplicationCache implements Serializable {
private static final long serialVersionUID = 20180510l;
@Id
private String applicationId;
private Date applicationDate;
private BorrowingType borrowingType;
}

where BorrowingType is an enum.

My requirement is to be able to query using where condition on all 3 fields of the object.


#4

You need to provide value of document key(USE KEYS[“125”]) . That is look up. If you need to query based on field you need MOI storage which is EE only feature
Example:

INSERT INTO default VALUES("k01",{"applicationId": "12345", "applicationDate": "2018-05-01", "borrowingType": "xx"});
INSERT INTO default VALUES("k02",{"applicationId": "12346", "applicationDate": "2018-05-01", "borrowingType": "xx"});
INSERT INTO default VALUES("k03",{"applicationId": "12347", "applicationDate": "2018-05-01", "borrowingType": "xx"});
SELECT * FROM default USE KEYS ["k01","k02"] WHERE applicationId = "12345";

Query gets “k01”, “k02” documents then applies predicates.


#5

So we got an enterprise version of couchbase installed in our dev cluster(4 nodes) but when I try to change the Index Storage Mode to MOI (since I want to use 2 buckets one Ephemeral and one Couchbase bucket) it gives an error
“Changing the optimization mode of global indexes is not supported when index service nodes are present in the cluster. Please remove all index service nodes to change this option.”

My question is

  1. Can I create two clusters with 2 nodes each and then create a GSI index in one cluster and MOI index in the other cluster?
  2. Can I then store persistent data in Couchbase bucket in one cluster and query it using GSI index
  3. Can I also keep the non persistent data in the Ephemeral bucket in a separate cluster and then query it using the MOI index?

Is this architecture possible?

We have some sensitive data which as per regulations we cannot persist in Couchbase which is why we have to use Ephemeral bucket.

Please suggest.

-T


#6

Only first time indexer node added it allows to choose Index Storage Mode. cc @deepkaran.salooja.

Two clusters are independent and you can do that. Your application needs to connect to right cluster to query.


#7

Hi,
Unfortunately our DBA’s do not allow for creation of 2 separate clusters(one for ephemeral bucket with MOI index and another for Couchbase bucket with GSI index) like earlier proposed.

  1. Can we then host both buckets on the same cluster with a MOI index? Will an MOI index work for a normal couchbase buckets?
  2. What are the drawbacks if any of using an MOI index on a couchbase bucket?
  3. Can a N1QL query be run on a normal Couchbase bucket with an MOI index only?
  4. On server restart when is an MOI and GSI index recreated? At server startup or on the first query execution?

We are running Couchbase Server 5.0 Enterprise edition.

Thanks
T


#8

MOI is available only on Enterprise Edition. MOI works ephemeral and normal buckets. All indexes needs fit in memory (https://developer.couchbase.com/documentation/server/current/indexes/storage-modes.html).
N1QL queries will work on all storage modes (https://developer.couchbase.com/documentation/server/5.0/indexes/storage-modes.html).
If indexes are already created and built on restart it reads from disk. Check above links.