Upsert not available on Capella?

Couchbase Capella
1

Is upsert not supported by Capella?
Just got a trial, and trying to establish data with existing n1ql upsert statements, and getting syntax error when putting a bunch in query editor (runs fine in standalone CB server).
Can’t find docs on this,;…?

Running one statement appears ok, but multiple get syntax error…they’re separated by semi-colon, is there a different syntax on Capella?

upsert INTO devtn (KEY,VALUE) values ('product-20',{'kind':'product','kindVersion':'6.0.0','productId':'20','productName':'Financial Services','productType':'meus','status':'disabled' });
upsert INTO devtn (KEY,VALUE) values ('product-2',{'kind':'product','kindVersion':'6.0.0','productId':'2','productName':'Identity','productType':'meus','status':'active' });
upsert INTO devtn (KEY,VALUE) values ('product-3',{'kind':'product','kindVersion':'6.0.0','productId':'3','productName':'Messaging','productType':'meus','status':'disabled' });
2

FYI: Query workbench treats each one separate and sends as different statement (not sure about Capella) one after other.

If you need batch insert/upsert you can use the following syntax too (workaround until Capella handles multiple statements).

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/upsert.html#values-clause

upsert INTO default values ('product-20',{'kind':'product','kindVersion':'6.0.0','productId':'20','productName':'Financial Services','productType':'meus','status':'disabled' }),
                  values ('product-2',{'kind':'product','kindVersion':'6.0.0','productId':'2','productName':'Identity','productType':'meus','status':'active' }),
                  values ('product-3',{'kind':'product','kindVersion':'6.0.0','productId':'3','productName':'Messaging','productType':'meus','status':'disabled' });
3

Thanks for that, batch doesn’t apply in this case because upsert are being generated in an excel so need to be separate.
…still need to know about the Capella difference I’d anyone can say…

4

Thank you for the feedback. Could you please confirm if you are receiving “msg”: “syntax error - at upsert” error when running multiple ‘Upsert’ statements?

5

[
{
“code”: 3000,
“msg”: “syntax error - at upsert”
}
]

6

Thank you. I have reviewed this internally and currently, we do not support the multi-statement queries feature within Couchbase Capella. As mentioned above, Querybench treats each query separately hence the multiple queries do not work.

As a workaround, you can run the batch upsert statements within the query bench.

Please let us know if you have any questions. Thank you.

7

Wow, unusable for me then, thanks

8

Thank you. I will forward your feedback to our Product team for future considerations.

Thank you again.

9

@ksully is it an option for you to use one of the other ways of running N1QL queries against your Capella cluster, such as using the REST interface from a bash script, or one of the SDKs e.g. a small Python script?

10

in this particular case, those aren’t options, as there’s a big spreadsheet that’s generating the sql statements as separate upserts. I could rework it to try to do batch statements, but that’s not worth it for this trial…disappointing that it’s not a 1:1 capability with the regular CB product.

11

@ksully Understood that it’s not ideal at the moment.

Another option would be to leverage “cbq”, which can take a file as input. Should be fairly straightforward to copy the SQL statements from the spreadsheet into that file and then run.

One thing to keep in mind, if you’re running cbq (or any script/application for that matter) from your local network, the performance will appear quite horrible as the latency getting into AWS is very high. You should see much better performance running the same command from an EC2 instance within the same region as your Capella cluster, and we will soon be offering the ability to VPC-peer directly to your Capella clusters for even higher performance.

12

Will check it, thanks!