Stored procedures in N1QL

SQL++
1

Hi,

In Couchbase Server 4.0, will there the equivalent of stored procedures in N1QL, or will all queries have to be parsed completely each time they are sent to the query services?

Thanks,

Marcus.

2

There is a feature that comes close to that, which is called “prepared queries”. This is not really server-side, but allows you to skip the parsing phase of a query. It works this way:

  • submit a normal query, but prefix the statement with PREPARE like this: PREPARE SELECT * FROM default
  • execute the query, the result will be a single JSON object that represent the execution plan for SELECT * FROM default.
  • you can store the plan on the client side and reuse it to skip the parsing phase.
  • for this submit a query with a prepared parameter instead of classical statement. The value of prepared is the plan you got earlier.

That’s it!
In the Java SDK, this is supported by calling Bucket's prepare method (prefixing your statement for you) and the result is wrapped in a QueryPlan object that you can cache and reuse.
Execute it through a PreparedQuery:

myBucket.query(Query.prepared(plan, parameters))

.Net has also a very similar API.

3

Thanks for your response. A few more questions:

  1. I won’t be using the Java SDK or .Net. If I’m accessing the N1QL interface using HTTP, will that work as well?

  2. Does the produced query plan include all the indexes that it will use?

  3. Is it possible to update the query plan by using the same syntax?

Thanks again.

4

yes, all of this is possible. In the documentation, you can find a pdf with the N1QL reference. http querying is described there in more details that what I provided above, including how to do prepared queries.

IIRC the query plan (quite verbose) includes the indexes to use, and it can be updated by just redoing the PREPARE statement and replacing the old plan by the new one (eg. in case you create an index in the meantime).

5

Just after your previous reply I found the ‘prepared’ information in the documentation. However, I couldn’t find any examples of prepared queries, either in the online docs or the 100+page PDF download. Maybe they are hidden somewhere, but they didn’t appear to be in the obvious place with the other REST API calls.

Any chance you could post a simple example or point me to a page where it’s fully documented?

Thanks.

6

Hi Eugaia,

please see here for an example of using a prepared statement:

Hope this helps.

Thanks,
Colm.

7

@colm Perfect, that’s just what I needed. Thanks.

I see that the prepared statement that’s provided is the full JSON, and not just a named query. Are there any plans to store named queries on the server-side that are basically cached versions of the prepared query?

Thanks.

8

Hi @Eugaia,

do you mean named query per Java Persistence API? There is no plan to implement named queries per se, but there is awareness (and a plan) for adding to prepared statements:
https://issues.couchbase.com/browse/MB-13643
In addition, prepared statements are cached server-side (using a hash of the JSON).
Thanks,
Colm.

9

@colm

I was not referring so much to caching named queries per Java Persistence API, but more the latter of what you said (i.e. server-side cached prepared statements). I was initially thinking of the user atrributing a name, but a hash I think would work better in practice as you suggest.

What you talk of with there sounds very much how Lua embedded in Redis works (from what I remember).

Are you planning on doing something like this:

  1. client sends hashed version of the prepared statement to the server by default
  2. if the server has the cached, hashed prepared statement, then it uses that
  3. if the server doesn’t have the cached, hashed prepared statement, then it sends a message back to the client to send the un-hashed version
  4. the hash of the prepared statement is sent back to the client (if the hash is generated on the server-side)

As a matter of interest, what hashing method are you intending on using to avoid any possible hash conflicts?

Thanks,

Marcus.

1 Like
10

Hi @Eugaia,

thank you for the great suggestions; we’ll make a point of investigating Redis (as well as other systems that offer a similar feature, I believe Aerospike supports Lua stored procedures).

Currently with N1QL, the client sends the prepared statement, the server hashes this and uses the hashed value to retrieve the prepared object; if it is not present then the server creates a prepared object from the client’s input. Per Loading... we intend to streamline this process and will implement something along the lines of what you describe. Really appreciate you taking the time to share your ideas.

Thanks,
Colm.

1 Like
11

@colm You’re welcome. :slight_smile:

12

Hi @Eugaia,

We now have named prepared statements in N1QL, as well as named + encoded prepared statements. Please see the latest documentation.

13

@geraldss Thanks for your reply to this (which I’ve just seen). I shall check this out.

1 Like
14

@simonbasle - Need your help here…
In the above explanation point 4.
“” for this submit a query with a prepared parameter instead of classical statement. The value of prepared is the plan you got earlier. “”

1.) Have created plan using the prepared statement.
prepare select a.function_section.* from ace_supplier a where a.type = $function;
2. ) I got the below plan name.
68aad199-f7b5-4b67-867d-bb6398ccbc67
3.) Then I would like to use this plan in my JAVA code without REST API.
4.) Kindly advice me how I can do this using.
It would be really great if you can able to share the code…

15

myBucket.query(Query.prepared(plan, parameters))…
myBucket - mybucket.
query - which method we have to select. have tens of methods.
Query - What it is actually…?

16

Hi simonbasle,
i have query here, i am inserting data from a java application to the database(bucket) whenever a specific value triggered i want to do an action based on that triggering point , we have implemented the same in postgres using stored procedure. how can we implement this in couchbase , please suggest a solution. any help would be appreciated.
Thank You

17

Hey @kmurali, it’d be best if you post a new thread rather than pick up a 10 month old one!

Have a look at the functions feature in Couchbase Server 5.5.