cbq: The Command Line Shell for N1QL

You can connect the cbq shell to Couchbase Server either through the query service or through the cluster endpoint. There are two ways to establish a connection:

The url is made up of two components: the URL and a port number. The URL can be any valid IP address or URL. The URL is optional and if it is not specified, the default URL http://localhost:8091 is used. An error is thrown if the URL is invalid.

The port number to connect to the query service is 8093 and to the Couchbase cluster is 8091.

The cbq shell supports http://, https://, couchbase:// and couchbases:// protocol schemes. When using the couchbase:// or couchbases:// protocol schemes, you need not specify the port when connecting to the Couchbase cluster.

When connecting to the query service, use the query port 8093. When connecting to the cluster, you don’t need to specify the port as the connection uses round robin to find a query service to connect to. If you want to specify a port, use the admin port 8091.

You can close the connection with an existing node or cluster without exiting the shell at any given time during the session using the \DISCONNECT; command. If the shell is not connected to any endpoint, an error with a message that the shell is not connected to any instance is thrown.

Bringing Up an Unconnected Instance

You can bring up the shell without connecting to any query service or cluster endpoint by using the -ne or --no-engine option. After starting cbq without any service, you can connect to a specific endpoint using the CONNECT command.

Exiting the cbq Shell

You can exit the cbq shell using one of the following commands:

When you run the exit command, the cbq shell first saves the history, closes existing connections, saves the current session in a session file, resets all environment variables, and then closes the shell liner interface.

You can pass a single user name credential to the cbq shell on startup using the command line options:

The shell then prompts you for a password. You can also provide a single password credential using the -p option. You cannot use this option by itself. It must be used with the -u option to specify the user name that the password is associated with.

For information on passing the user name and password credentials using other mechanisms, see Passing Credentials Using the SET Shell Command and Passing Credentials Using REST API.

The cbq shell supports self-signed certificates for encrypting communication between clusters.

Using the cbq shell, you can set the credentials for different users on startup or by using the SET shell commands to set the credentials query parameter. You can also use this to provide authentication credentials for multiple SASL buckets per session. Depending on the type of credential being set, there are multiple possible values for the credentials query parameter.

To set the credentials for different users on startup, use one of the following options:

The list-of-creds can take either one or multiple credentials. The credentials consist of an identity and a password separated by a colon ":". To specify multiple credentials, append all the user names and passwords to the same credentials array. For example:

For information on passing a single user name credential to the cbq shell, see Providing Single User Credentials.

Passing Credentials Using the SET Shell Command

You can provide the credential types using the SET command.

To pass authentication credentials per query, set the query parameter to a new value using the SET shell command before executing the query.

You can also switch between users and change credentials during a session. To do so, set the -creds query parameter for the session using the following command:

Passing Credentials Using Query REST API

You can use query REST API to pass credentials from clients.

For SASL buckets, you can pass the credentials as:

If you are using the Administrator credentials:

For multiple SASL protected buckets, you can pass an array of authentication credentials:

Displaying the Credentials

You can display the credentials for the current session using the ECHO shell command. This command displays only the user names (and not the passwords).

You can also display a full list of variables using the SET command specified without any arguments.

The cbq shell categorizes parameters into the following types:

Parameter Configuration

When using parameters, you can set a stack of values for each parameter. You can either push a new value onto the stack using the PUSH command, or set the current value for a parameter using the SET command. The SET command always modifies the top of a variable’s stack while the PUSH command adds to the stack. When you use PUSH with no arguments, it copies the top element of every parameter’s (except the predefined parameters) stack and pushes that copy to the top of its respective stack. As a result, each stack grows by 1, but the values are preserved. You can then use the SET command to modify the top value.

To unset the values from a parameter’s stack, you can use the UNSET command to remove all the values from the stack and delete the corresponding parameter stack. However, if you want to delete a single value from the settings, use the POP command. When you use the POP command with no arguments, it pops the one value from the top of each parameter’s stack.

Setting Variable Values

Each variable has a separate stack associated with it and the prefix name argument helps distinguish between the stacks.

The SET command always modifies the top value of a variable. You can use the SET command to set different kinds of parameters: query parameter, predefined session variables, user-defined session variables and named parameters.

where name is the name of the parameter, value is the value to be set, and prefix is one of the following depending on the parameter type. The cbq shell uses the prefix to differentiate between the different types of parameters.

You can use the cbq shell to set all the REST API settings by specifying the settings as query parameters prefixed by '-'. As a best practice, we recommend that you save the initial set of basic parameters and their default values using the \PUSH command (with no arguments).

To display all the parameters defined in a session, use the SET command with no arguments.

The following table lists the available predefined session variables.

Handling Named Parameters

Use the \SET command to define named parameters. For each named parameter, prefix the variable name with '-$'. The following example creates named parameters 'r' and 'date' with values 9.5 and "1-1-2016" respectively.

Handling Positional Parameters

Use the SET shell command with the -args query parameter to define positional parameters:

The value contains the different values that correspond to positions within the query. For example,

Resetting Variable Values

You can reset the value of a variable by either popping it or deleting it altogether. To pop the top of a parameter’s stack use:

To pop the top of every parameter’s stack once, use the POP command without any arguments:

To pop all the values of a parameter’s stack and then delete the parameter, use:

The ECHO command displays the current values of the parameters set for a session. You can use it to display any input string or command aliases that have been created using the ALIAS shell command. To display parameters, you must include their prefixes. If not, the shell considers the parameters as generic statements and displays the parameter as is.

where input can be a parameter with prefix (<prefix><parameter-name>), an alias (\[.var]command-alias), a N1QL statement, or a string.

Using the ALIAS shell command, you can define and store aliases for commands. This is useful when you have lengthy queries that need to be executed often. Run the following command to define an alias:

To run the command alias, use \\command-alias.

To list all the existing aliases, use:

You can delete a defined alias using the \UNLIAS command.

This command can take multiple arguments and deletes the defined alias for every input name.

You can use the shell command to execute prepared statements. As a pre-requisite, you must first prepare a statement using the N1QL PREPARE statement. To execute prepared statements, follow these steps:

You can cancel a running query by using the Ctrl+C keys.

Connection Timeout Parameter

You can use the timeout parameter to limit the running time of a query. This parameter specifies the time to wait before returning an error when executing a query.

Timeout can be specified in the following units: "ns" for nanoseconds, "μs" for microseconds, "ms" for milliseconds, "s" for seconds, "m" for minutes, and "h" for hours. Examples of valid values include "0.5s", "10ms", or "1m".

You can also the SET shell command to set this parameter. An error is thrown if the timeout is invalid.

Using the file based commands and options, the cbq shell can execute N1QL and shell commands contained in files. There are two ways to accomplish this:

Consider the input file, sample.txt, containing the following commands:

To execute the commands contained in sample.txt using the -f option, run $./cbq -f=sample.txt

To execute the commands contained in sample.txt using the shell command, run cbq> \SOURCE sample.txt;

Redirecting Results to a File

You can redirect all the output for a session or part of a session to a specified file by using the following option:

To redirect a specific set of commands during a session, you must specify the commands between REDIRECT and REDIRECT OFF as shown:

All the commands specified after \REDIRECT and before \REDIRECT OFF are saved into the specified output file. If the file doesn’t exist then it is created.

You can specify multiple REDIRECT commands. When you do so, the output file changes to the specified files and switches back to stdout only when you specify "\REDIRECT OFF;".

The cbq shell provides commands that convey information about the shell or cluster endpoints.

The following table lists the shortcut keys used by the cbq shell.

Source: https://github.com/peterh/liner