Mastering the Couchbase N1QL Shell: Scripting

This is part 3 of the three-part series discussing the new and improved shell. With the previous article, we saw how to interact with the N1QL Rest API and issue prepared statements. With this article, let's dive a little deeper into scripting with the N1QL shell.

Display Specific Parts of the Query

In the previous article, we learned how to interact with the Query Rest API using query parameters. As we know the result returned from N1QL is of the following structure:

{
 "requestID": "0f27f703-3748-4006-99c9-65d35bd2c2df",
 "signature": {
  "$1": "number"
 },
 "results": [
 ],
 "status": "success",
 "metrics": {
  "elapsedTime": "361.908µs",
  "executionTime": "343.3µs",
  "resultCount": 1,
  "resultSize": 31
 }
}

It is possible for the user to change the response structure by setting the Rest API parameters for metrics and signature. Both parameters take boolean values (true/false). When metrics is set to false, the metrics are not returned with the query results. Similarly, when the signature is set to false the signature that defines the results schema in the response, is not displayed. By default, these values are true.

cbq> \SET -metrics false;
cbq> \SET -signature false;

The above will now give a response as follows:

{
 "requestID": "0f27f703-3748-4006-99c9-65d35bd2c2df",
 "results": [
 ],
 "status": "success"
}

For the detailed REST API documentation, refer here.

Scripting Using User-Defined Commands

There is a provision to create temporary commands (aliases) that map to other shell or n1ql commands. In order to create the command use  \ALIAS <command-name> <query or command>. Once the alias has been created, we can see the list of new user defined commands using \ALIAS; without any input arguments. This will display all of the aliases defined for the current session.

These allow the user to repeatedly run a particular command easily by executing the alias using a \\.

For example:

cbq> \ALIAS n1qlVersion select version();
cbq> \ALIAS;
n1qlVersionselect version()
cbq> \\n1qlVersion;
{ ….

We can also display the command that the alias represents using the \ECHO command.

cbq> \ECHO \\serverversion;
select version()

File-Based Operations

N1QL and shell commands contained in files can be executed by using the file-based commands and options.

Say we need to store a script with multiple commands (for example, in a .n1ql file) and then run it periodically. This can be done either using the --file option or the \SOURCE shell command. This will run all the commands in the input file and redirect the output to STDOUT. The user can also save all the queries and their corresponding results into a result file using the --output option and the \REDIRECT command. When the \REDIRECT command is issued with a filename, then it saves the output of all the queries run into the given file. In order to save toSTDOUT  again, we can issue \REDIRECT OFF. This will return the shell to its default status of printing the output on STDOUT.

In order to redirect a specific set of commands during a session to a specified file, the user can use:

\REDIRECT <filename>;
<command1>;<command2> …. <commandn>;
\REDIRECT OFF ;

All the commands specified after \REDIRECT and before \REDIRECT OFF are saved into the output file specified by <filename>.

If the file doesn't exist then it is created. Let's look at the following example.

Say we have two files: demo.txt and file2.txt. Both contain different query commands.

Demo.txt
create primary index on `travel-sample`;
#Find flight information, from source to destination on a leave date;
#Find schedule information for an airline that services the requested flight path;
\ALIAS airinfo SELECT a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipment FROM `travel-sample` r UNNEST r.schedule s JOIN `travel-sample` a ON KEYS r.airlineid WHERE r.sourceairport=$airport AND r.destinationairport=$1 AND s.day=$2 ORDER BY a.name;
\ALIAS;
\SET -args ["SEA", 6];
\SET -$airport "SFO";
\\airinfo;
\SOURCE examples/file2.txt;
\ECHO -args -$airport

\REDIRECT output_file.txt;
\SOURCE examples/file2.txt;
\REDIRECT off;
\QUIT;

Here, in demo.txt, the alias (user defined command) airinfo is created. The alias contains references to the named parameter $airport and the positional parameter that represents the destination airport. This is similar to the example seen in part 2 of this series.

Once values are set for both parameters, the script loads file2.txt using the \SOURCE command. At this point, the contents of the new file are read, and the commands in file2.txt  are executed.

file2.txt
\PUSH;
\SET -$airport "SJC";
\SET -args ["LAX", 6 ];
\\airinfo;
\SET;
\POP;

Once all the commands in file2.txt have been executed, we come back to demo.txt and the next command (here, the \ECHO command to echo the values of the parameters set) is executed.

Up until this point, the results of all of the queries were being directed to the terminal itself (stdout). Once the \REDIRECT output_file.txt is run, up until the \REDIRECT OFF command is run, all results will be directed to the output_file.txt. As we see in this example, between \REDIRECT and \REDIRECT OFF, only the \SOURCE command is present in the script. So finally, output_file.txt will contain the results of the commands in file2.txt.

It is possible to call multiple files in a single run. This can be seen in the examples here. In order to run the above examples, run the command ./cbq -file=demo.txt against your Couchbase cluster after adding the travel-sample bucket.

Another example can be where we prepare a query in the main file and multiple files can contain commands to execute the prepared statement with different values (for the named and positional parameters within the query), which in turn can be redirected to different output files, allowing better comparison between corresponding outputs.

This powerful ability to call multiple files containing different commands in a nested fashion, and redirect the desired output to specific files allows for better scripting.

The user can also execute a single command and exit the shell using the --script option.

Piping Commands Into Cbq

The shell also allows running commands by redirection and piping instead of using the provided file-based options and commands. Below are some examples on piping commands to cbq shell from the terminal.

$ echo "select * from \`beer-sample\` limit 10;" | ./cbq
…..
$ cat file.txt | ./cbq
$ ./cbq < file.txt

Multi-Line Input and Single-Query/Batch Mode

User input can span multiple lines; this includes N1QL statements and shell commands. The prompt indicates that the shell is in multiline mode with the command prompt and the lines are automatically word-wrapped. In the multiline mode in the shell, if a line is preceded by a # or –, it counts as a comment.

For N1QL, all queries run in single-query mode because the server handles a single query at a time. In order to connect cbq, to the Analytics service, the shell batch mode can be used. (See the previous article on Running Analytics queries with the N1QL shell.)

Exit on Error

One issue when running in a multi-node cluster environment with different configurations for each node is that we cannot predict when a node goes down. In the event this happens, the queries in our script might error out which might cause the subsequent statements/commands to error. In order to avoid these kinds of situations, we can use the --exit-on-error option, which will cause the cbq script to stop and exit after the first error is encountered. This helps during scripting when we want to run a bunch of queries contingent on the first command being run successfully. As an example, say our script first prepares a statement and then executes this statement with different values for the positional and named parameters. If the prepare returns an error, all subsequent commands will also error out. Here we can use the --exit-on-error option to make sure we prepare the statement properly before executing it.

Now that we have seen some aspects of the shell, its time to start exploring! Refer to this documentation for a comprehensive documentation of the shell commands and options.