There are a ton of new features that have been introduced in newly available Couchbase Server 4.5. Query Workbench, sub-document APIs, and full text search are only some of the great additions to an already great NoSQL database.
For this article we’re going to focus on some of the advanced features wrapped around an already existing, but heavily improved Couchbase Shell – CBQ.
What is the Couchbase Query Client (CBQ)?
In case this is the first time you’ve come across the Couchbase Query Client, otherwise known as CBQ, it is a great command line utility for executing queries against Couchbase Server. It was first introduced in Couchbase Server 4.0 with the release of Couchbase’s N1QL technology. If you’re new to Couchbase or NoSQL in general, CBQ is similar to tools such as Oracle’s SQLPlus, or the MySQL command line interface. With the release of Couchbase Server 4.5, CBQ has evolved into what can be better described as a shell.
To use the Couchbase Shell you would launch one of the following depending on your operating system. On a Mac, you would use the Terminal and execute:
|
1 2 3 |
./Applications/Couchbase Server.app/Contents/Resources/couchbase-core/bin/cbq |
On Microsoft Windows you would execute the following:
|
1 2 3 |
C:/Program Files/Couchbase/Server/bin/cbq.exe |
Of course in both scenarios you would need to have Couchbase Server 4.0 or greater installed on your computer. However, in Couchbase Server 4.5, things have changed, and when I say changed, I mean have gotten even better.
Let’s go over some of the things you were able to do prior to version 4.5 which introduces advanced CBQ features. With every CBQ request you’re returned with certain data as well as metric information about the query you ran. For example let’s say we run the following simple command:
|
1 2 3 |
SELECT 1=1; |
The above command will leave us with the following results in the CBQ output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "requestID": "879a196e-8de4-400b-a6b6-278a66ad1e0b", "signature": { "$1": "boolean" }, "results": [ { "$1": true } ], "status": "success", "metrics": { "elapsedTime": "30.360043ms", "executionTime": "26.927386ms", "resultCount": 1, "resultSize": 34 } } |
Of course it tells us that our result is true, but it also gives us information such as how long it took to execute as well as others. You should ignore the long 30ms time because in my local environment I have low specs and minimal indexes created.
CBQ can run DML queries in addition to being able to select data. Inserting, updating, upserting, deleting, and retrieving can all be done with CBQ’s core features.
Advanced Shell Capabilities
Chances are you’re here because you want to know what makes CBQ different between Couchbase Server 4.5 and the previous Couchbase Server 4.1. The advanced features can be split into a few general categories. What you’re about to see is just a taste of what you can accomplish. Full documentation on the subject can be found in the Couchbase developer resources.
Connection Management
With Couchbase 4.5 you can connect to Couchbase clusters with CBQ or you can connect to individual query nodes. This is great when it comes to troubleshooting the performance of your database.
To connect to a cluster you can use the default way and run ./cbq from the command line, or you can pass in an optional tag like this:
|
1 2 3 |
./cbq -e host:8091 |
Notice in the above I specified a port of 8091 to signify a cluster. You can use port 8093 to specify a particular query service instead. If you’d prefer to connect to a cluster or service only after first launching CBQ, you can run the following:
|
1 2 3 |
CONNECT host; |
If going down that road, use DISCONNECT; to terminate the connection.
Security Management
It is very common and typically recommended that your Couchbase data is locked down through the use of credentials. You wouldn’t want just anyone accessing your data.
Prior to Couchbase 4.5, specifying passwords for your database or buckets in CBQ wasn’t a thing. This is where things changed. You can now specify usernames and passwords through CBQ.
Say you want to connect to your Couchbase cluster using the admin credentials. You could pass in an optional tag like so:
|
1 2 3 |
./cbq -u=admin -p=password; |
If you’d prefer not to expose your password in the shell, you could leave off the password tag and it will prompt you for one while it connects.
Say maybe you want to connect to your database with more than one set of credentials. You can pass in a list of credentials like so:
|
1 2 3 |
./cbq -c=beer-sample:pwd1,travel-sample:pwd2 |
Like with the connection commands, maybe you don’t want to specify credentials at the launch time of CBQ. By using the SET and UNSET commands with CBQ running, you can specify the credentials. For example, with CBQ running, execute the following:
|
1 2 3 |
SET -creds beer-sample:b1, session:s1; |
To clear the credentials, just call the UNSET command instead.
Scripting Support
Previously with CBQ you were limited to running queries within the tool and not in a scripted fashion. In Couchbase Server 4.5, you can now create scripts for running against CBQ. Maybe you have cleanup scripts that you want to run via a cron, or maybe some other reason. You can do something like this:
|
1 2 3 4 5 |
$ echo "select * from `beer-sample` limit 10;" | ./cbq $ cat file.txt | ./cbq $ ./cbq < file.txt |
Of course above are three different ways you can do things.
It is common to want to store your query results to an output file. Maybe your analysts pick up the data, or maybe you have other scripts that check to make sure the appropriate data was generated. To specify an output file at launch time, add the following parameters:
|
1 2 3 |
$ echo "select * from `beer-sample` limit 10;" | ./cbq -o filename.txt |
Notice the -o filename.txt in the above snippet. The results of that query will be saved in a file.
Query Management
With Couchbase 4.5 you can use CBQ to monitor the operations of individual queries and query service nodes, as well as interact with the query service itself. For example, say you experience heavy load on your cluster. One of the logical things to do would be to see what kind of queries are currently being executed. To do this with CBQ, you can execute the following:
|
1 2 3 |
SELECT * FROM system:active_requests; |
Executing the above command would give you results similar to the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
{ "requestID": "7796392a-df14-4687-a885-5db355ba22b9", "signature": { "*": "*" }, "results": [ { "active_requests": { "ElapsedTime": "1.160625ms", "ExecutionTime": "1.131825ms", "PhaseCounts": { "PrimaryScan": 2 }, "PhaseOperators": { "Fetch": 1, "PrimaryScan": 1 }, "RequestId": "7796392a-df14-4687-a885-5db355ba22b9", "RequestTime": "2016-05-03 08:42:07.733283136 -0400 EDT", "State": "running", "Statement": "SELECT * FROM system:active_requests" } } ], "status": "success", "metrics": { "elapsedTime": "1.25837ms", "executionTime": "1.229031ms", "resultCount": 1, "resultSize": 610 } } |
Of course on my local machine, the only query that was running was the query for finding out the active requests against the cluster. In a production environment it will be much greater.
Now maybe you notice a lot of the same query being executed in your search for active requests. It might be a good idea to make a prepared statement out of them. However, maybe they are already prepared statements. You can check by executing the following:
|
1 2 3 |
SELECT * FROM system:prepareds; |
The above query will tell you information about all the known prepared statements and their state in the query engine’s prepared statement cache.
What if you’ve made prepared statements, but occassionaly your server performance still spikes. With the CBQ features for query management you can examine slow, but completed queries. You can check out past queries by executing the following:
|
1 2 3 |
SELECT * FROM system:completed_requests; |
Remember I mentioned slow. You can determine slowness based on a threshold that you define in the cbq-engine. By default, any query that runs longer than 1 second will fall into this category, but maybe you only care about longer queries. This value can be changed.
More information on query management can be found here.
Maybe you’re more interested in executing prepared statements than monitoring the query activities that are happening. From within CBQ you can make use of the EXECUTE command to run your prepared statement. Something like this:
|
1 2 3 |
EXECUTE name-of-prepared-stmt; |
Many of your prepared statements will be parameterized queries. After all, you’re being preventative against SQL injection. To pass in named parameters you can do the following:
|
1 2 3 4 |
SET -$r 9.5; SET -$date "1-1-2016"; |
This isn’t the first time you saw SET. We used it for defining credentials, but this time by prefixing something with -$ we’re saying it is a named parameter.
If you’d rather use positional parameters instead, you can call SET on an array of data instead:
|
1 2 3 |
SET -args [ 9.5, "1-1-2016"]; |
Should EXECUTION of your query run out of control and you need to stop it. Simply press control+c and it will stop.
Session Management
Session management isn’t too different from what I already described when it comes to querying and adding user credentials. As part of the advanced CBQ features you can manage different shell sessions or set various user preferences.
Previously you saw how to SET and UNSET parameters in a session, but you can also make use of PUSH and POP. Instead of setting a top level bunch of parameters, you are creating a stack of parameters for your session. This could be useful for scripts or something else.
Let’s say you have many crazy queries that you plan to use continuously with CBQ. Instead of having to remember each query, you can set an alias for them. For example:
|
1 2 3 |
ALIAS my_query SELECT * FROM `default`; |
Yes the above query is not very complex, but it could be. If you wanted to run this query you could then run the following from CBQ:
|
1 2 3 |
\my_query; |
Not a whole lot to it, but being able to alias queries is huge when thinking about competing databases. Query aliasing is a unique feature to Couchbase and could save you a ton of time when querying.
Conclusion
You just got a taste of some of the new CBQ features that made an appearance starting in Couchbase Server 4.5. You can fiddle with the connection management, security, scripting, query management and monitoring, and session management features to improve your productivity with Couchbase Server.
To get a more thorough list of commands and features, visit the official documentation in the developer portal.
