cbq: The Command Line Shell for SQL++

  • Capella Operational
      +
      cbq is a comprehensive command line shell for SQL++. It is a powerful, developer friendly tool that enables you to query and update data from Couchbase Capella. The cbq shell enables you to perform all the operations that are supported by the Query tab and more, such as additional scripting functionality.

      The cbq shell executable, cbq, is available in the Couchbase command line tools package beginning with version 7.6.2.

      Prerequisites

      The procedures on this page assume the following:

      You can do all of this from a single location using the Connect page in the Capella UI. See Connect To Your Cluster and follow the instructions for the CLI tools.

      Examples on this Page

      The examples on this page use the travel-sample dataset, which is supplied with Couchbase Capella. For instructions on how to install the sample data, see Import Sample Data.

      • Linux

      • macOS

      • Microsoft Windows

      • Shell Command

      In the command line examples:

      • $BASE_URL is the connection string generated by Couchbase Capella.

      • $USER is the user name for the cluster credentials.

      • $PASSWORD is the password for the cluster credentials.

      • $CACERT is the name and path of the cluster security certificate.

      In the command line examples:

      • $BASE_URL is the connection string generated by Couchbase Capella.

      • $USER is the user name for the cluster credentials.

      • $PASSWORD is the password for the cluster credentials.

      • $CACERT is the name and path of the cluster security certificate.

      In the command line examples:

      • %BASE_URL% is the connection string generated by Couchbase Capella.

      • %USER% is the user name for the cluster credentials.

      • %PASSWORD% is the password for the cluster credentials.

      • %CACERT% is the name and path of the cluster security certificate.

      In the shell command examples:

      • <BASE_URL> is the connection string generated by Couchbase Capella.

      • <USER> is the user name for the cluster credentials.

      • <PASSWORD> is the password for the cluster credentials.

      • <CACERT> is the name and path of the cluster security certificate.

      Running the cbq Shell

      When starting the cbq shell you can provide a set of command line options. If no options are present then cbq assumes default values for expected options.

      To run cbq:

      1. Open a command window.

      2. Change to the directory where you installed the Couchbase command line tools.

        • Linux

        • macOS

        • Microsoft Windows

        cd couchbase-server-tools/bin
        cd couchbase-server-tools/bin
        cd couchbase-server-tools\bin
      3. Run the following command to connect to the Couchbase Capella cluster and start the interactive query shell:

        • Linux

        • macOS

        • Microsoft Windows

        ./cbq -u $USER -p $PASSWORD -e $BASE_URL --cacert $CACERT -skip-verify
        ./cbq -u $USER -p $PASSWORD -e $BASE_URL --cacert $CACERT -skip-verify
        cbq -u %USER% -p %PASSWORD% -e %BASE_URL% --cacert %CACERT% -skip-verify

      For more information about connecting to Couchbase Capella, see Connecting to the Cluster, Providing Credentials, and Using an Encrypted Connection.

      For the complete list of command line options, see Table 3.

      Executing a Single Command

      When you start the cbq shell, the cbq shell prompt is displayed.

      cbq>

      The cbq shell interface accepts accepts shell commands as well as SQL++ commands. All the cbq shell commands start with a backslash (\). If the command does not start with a backslash (\), the cbq shell interprets the command as a SQL++ command.

      • To execute a SQL++ query at the cbq prompt, type the query. At the end of the query, type a semicolon ; and press Enter.

      • To execute a cbq command at the cbq prompt, type the command name starting with a backslash \. At the end of the command, type a semicolon ; and press Enter.

      The cbq shell enables you to manipulate query parameters. See Parameter Manipulation for details.

      For the complete list of shell commands, see Table 4.

      Support for Multi-line Queries

      The cbq shell supports multi-line queries by default, enabling you to enter a query over multiple lines. When entering a query, you can hit Enter without specifying a semicolon (;) at the end of the line to move the cursor to the next line. The prompt > indicates that the shell is in multi-line mode. For example:

      cbq> SELECT *
         > FROM `travel-sample`.inventory.airline
         > LIMIT 1;

      When you’re done, use a semicolon ; to indicate the end of the query, and then press Enter to execute the query.

      Handling Comments

      You can add comments in your query by preceding the comment with a # or --. The cbq shell interprets a line that starts with # or -- as a comment, logs the line into history, and returns a new prompt. No other action is taken.

      SELECT *
      #This is the first comment
      FROM `travel-sample`.inventory.airline
      --This is the second comment
      LIMIT 1;

      However, if a comment exists within a statement, it is considered as part of the SQL++ command. If the cbq shell encounters a block comment (enclosed between /* ... */) within a statement, it sends the block comment to the query service.

      SELECT * FROM `travel-sample`.inventory.airline /* Block comment */ LIMIT 1;

      File Based Operations

      The cbq shell can execute SQL++ and shell commands contained in files using file-based commands and options. See File Based Operations for more information.

      History

      The cbq shell stores the history for every session. All the commands executed in a session are stored in history. By default, history is stored in ~/.cbq_history. You can change the name of the file using the SET command to set the predefined parameter histfile. See Parameter Manipulation for more information.

      By default, all the commands are stored in the specified file. You can scroll through history and retrieve the commands from history using the scrolling arrow keys. Once the query is on the command prompt, you can edit it before executing the updated query.

      Help

      Help displays the help information for the shell commands and for the general usage of cbq.

      • Command Line Option

      • Shell Command

      Use the -h or --help command line option when bringing up the shell to display the information for all available options.


      Example
      ./cbq -h

      Use the \HELP shell command during a session to display information for specific shell commands. If you specify one or more commands, the shell displays the usage information for the specified commands.

      If you do not specify a command, the cbq shell lists all the commands for which syntax help is available.


      Example
      \HELP;

      Connecting to the Cluster

      To connect the cbq shell to the cluster, use the connection string generated by Couchbase Capella.

      • Command Line Option

      • Shell Command

      To establish a connection on startup, use the -e or --engine command line option, followed by the connection string.


      Example
      ./cbq -e $BASE_URL -u $USER -p $PASSWORD --cacert $CACERT -skip-verify
      Result
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.
      
      Path to history file for the shell : ~/.cbq_history

      To establish a connection during a session, use the \CONNECT shell command, followed by the connection string.


      Example
      \CONNECT <BASE_URL>;
      Result
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.

      By default, the connection string is a URL, consisting of the protocol scheme and the host.

      To connect to Couchbase Capella, you must use the couchbases:// protocol scheme. This protocol scheme is encrypted. For more details, refer to Using an Encrypted Connection.

      Note that you cannot specify the port when using the couchbases:// protocol scheme.

      If you do not specify the connection string, the default URL http://localhost:8091 is used. This does not enable you to connect to Couchbase Capella. An error is thrown if the URL is invalid.

      Disconnecting

      You can close the connection with a Couchbase Capella cluster during the session without exiting the shell using the \DISCONNECT command. If the shell is not connected to a cluster, an error with a message that the shell is not connected to any instance is thrown.

      Example
      \DISCONNECT;
      Result
      Couchbase query shell not connected to any endpoint. Use \CONNECT command to connect.

      Bringing Up an Unconnected Instance

      You can bring up the shell without connecting to a Couchbase Capella cluster by using the -ne or --no-engine option. After starting cbq without any connection, you can connect to a cluster using the \CONNECT command.

      Example
      ./cbq -ne
      Result
      Path to history file for the shell : ~/.cbq_history

      Exiting the cbq Shell

      You can exit the cbq shell by pressing Ctrl+D or by using the \EXIT or \QUIT 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.

      Example
      \EXIT;
      Result
      $

      Providing Credentials

      You can pass a single user name credential to the cbq shell on startup using the -u or --username command line option, followed by the user name. The shell then prompts you for a password.

      Example
      ./cbq -e $BASE_URL -u $USER --cacert $CACERT -skip-verify
      Result
      Enter Password:
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.

      You can also provide a single password credential using the -p or --password command line option, followed by the password. 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.

      Example
      ./cbq -e $BASE_URL -u $USER -p $PASSWORD --cacert $CACERT -skip-verify
      Result
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.

      Providing Credentials in the Connection String

      You can pass the username and password by inserting them into the connection string. This method is not recommended when starting cbq, as you must quote or URL-encode any special characters in the username or password. However, this method may be useful for specifying the username and password when connecting to Couchbase Capella within a cbq session.

      To pass credentials in the connection string:

      1. Add the username immediately after the couchbases:// protocol scheme.

      2. After the username, add a colon : followed by the password.

      3. After the password, add an at sign @ followed by the rest of the connection string.

      • Command Line Option

      • Shell Command

      To pass credentials on startup, use the -e or --engine command line option, and specify the username and password in the connection string.


      In this example, $HOST is the part of the connection string following the couchbases:// protocol scheme.

      Example
      ./cbq -engine="couchbases://$USER:$PASSWORD@$HOST/" --cacert $CACERT -skip-verify

      To pass credentials during a session, use the \CONNECT shell command, and specify the username and password in the connection string.


      In this example, <HOST> is the part of the connection string following the couchbases:// protocol scheme.

      Example
      \CONNECT couchbases://<USER>:<PASSWORD>@<HOST>;

      Switching Between Credentials

      When starting the cbq shell, you can set the credentials using a single command line option, as an alternative to specifying the username and password separately. This method is not recommended, as you must quote or escape any special characters in the username or password.

      You can also use the \SET or \PUSH shell command to set the credentials query parameter within a session. This enables you to change credentials before executing a query, for example to switch to a cluster user with access to another keyspace. Note that the credentials are set for the remainder of the shell session and not just on a per query basis.

      The list of credentials can contain one or multiple credentials. Each credential consists of an identity and a password separated by a colon :. However, if you specify multiple credentials, Couchbase Capella only recognizes the first credential.

      • Command Line Option

      • Shell Command

      To set the credentials on startup, use the -c or --credentials command line option, followed by the list of credentials.


      Example
      ./cbq -e $BASE_URL -c=$USER:$PASSWORD --cacert $CACERT -skip-verify

      To change credentials during a session, use the \SET shell command to specify the -creds query parameter, followed by the list of credentials.


      Example
      \SET -creds <USER>:<PASSWORD>;

      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).

      Example
      \ECHO -creds;
      Result
      <USER>:*

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

      Example
      \SET;
      Result
       Query Parameters :
       Parameter name : creds
       Value : [ "<USER>:*" ]
      
      
       Named Parameters :
      
       User Defined Session Parameters :
      
       Predefined Session Parameters :
       Parameter name : histfile
       Value : [".cbq_history"]
      
       Parameter name : batch
       Value : ["off"]
      
       Parameter name : quiet
       Value : [false]

      Using an Encrypted Connection

      You must use the encrypted couchbases:// protocol scheme with a domain name to connect to a cluster deployed in Couchbase Capella. To do this, you can provide the root CA certificate, the chain certificate, and the client key file using the --cacert, --cert, and --key options. You can use the --no-ssl-verify option to skip the verification of certificates.

      For more details, refer to Connecting to the Cluster.

      Parameter Manipulation

      The cbq shell categorizes parameters into the following types:

      • Query parameters

      • Named parameters

      • User-defined session variables

      • Pre-defined session variables

      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 stack (except the predefined parameters) 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.

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

      Setting Variable Values

      To set the value of a parameter, use the \SET or \PUSH shell command, followed by a parameter name and parameter value.

      The parameter name may have a prefix, depending on the type of parameter: query parameter, named parameter, user-defined session variable, or predefined session variable. The cbq shell uses the prefix to differentiate between the different types of parameters.

      Table 1. Prefixes for Parameters
      Prefix Parameter Type

      -

      Query parameter

      -$ or -@

      Named parameters

      $

      User defined session variable

      No prefix

      Predefined (built-in) session variable

      Positional parameters are set using the -args query parameter.

      For more details about the available query parameters (prefixed by -), see Request-Level Parameters. As a best practice, save the initial set of basic parameters and their default values using the \PUSH command (with no arguments).

      The following example sets the airport named parameter, pushes two positional parameters to the args query parameter stack, and then displays all parameters.

      Example
      \SET -$airport "SJC";
      \PUSH -args ["LAX", 6];
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6]]
      
      Named Parameters ::
      Parameter name : airport Value  ["SJC"]
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      The following example pushes a new value to the airport named parameter stack, duplicates the top value in each stack except the predefined session parameters, and then displays all parameters.

      Example
      \PUSH -$airport "SFO";
      \PUSH;
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6] ["LAX",6]]
      
      Named Parameters ::
      Parameter name : airport Value  ["SJC" "SFO" "SFO"]
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      The following example sets the top level of the airport named parameter stack to a new value, and then displays all parameters.

      Example
      \SET -args ["SFO", 8];
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6] ["SFO",8]]
      
      Named Parameters ::
      Parameter name : airport Value  ["SJC" "SFO" "SFO"]
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      Handling Named Parameters

      To define named parameters, use the \SET or \PUSH command. For each named parameter, prefix the variable name with -$ or -@.

      For more details about named parameters, see Named Parameters and Positional Parameters.

      The following example creates named parameters r and date with values 9.5 and "1-1-2016" respectively.

      Example
      \SET -$r 9.5;
      \SET -@date "1-1-2016";

      Handling Positional Parameters

      To define positional parameters, use the \SET or \PUSH command with the -args query parameter, followed by an array containing the different values that correspond to positions within the query.

      For more details about positional parameters, see Named Parameters and Positional Parameters.

      Example
      \SET -args [ 9.5, "1-1-2016"];

      Handling Predefined Session Variables

      The following table lists the available predefined session variables.

      Table 2. Predefined Session Variables
      Variable Name Possible Values Description

      histfile

      Valid file name

      Specifies the file name to store the command history. By default the file is saved in the user’s home directory.

      Default: .cbq_history

      batch

      String ("on", "off")

      This variable is available only with the Analytics Service. When specified, cbq sends the queries to Analytics only when you hit EOF or \ to indicate the end of the batch input.

      Default: "off"

      quiet

      Boolean

      When specified, disables the startup connection message for the cbq shell.

      Default: false

      Resetting Variable Values

      You can reset the value of a variable by either popping it or deleting it altogether.

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

      Example
      \POP;
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6]]
      
      Named Parameters ::
      Parameter name : airport Value  ["SJC" "SFO"]
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      To pop the top value from a single parameter’s stack, use the \POP command, followed by the parameter prefix and parameter name.

      Example
      \POP -$airport;
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6]]
      
      Named Parameters ::
      Parameter name : airport Value  ["SJC"]
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      To pop all the values of a parameter’s stack and then delete the parameter, use the \UNSET command, followed by the parameter prefix and parameter name.

      Example
      \UNSET -$airport;
      \SET;
      Result
      Query Parameters ::
      Parameter name : args Value  [["LAX",6]]
      
      Named Parameters ::
      
      User Defined Session Parameters ::
      
      Predefined Session Parameters ::
      Parameter name : histfile Value  [".cbq_history"]

      Using ECHO to Display Values of Parameters and More

      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.

      Echo a String or Statement

      To echo a string or a SQL++ statement, use the \ECHO command, followed by the string or statement.

      Example
      \ECHO hello;
      Result
      hello

      Echo an Alias

      To echo a command alias, use the \ECHO command, followed by two backslashes and the command alias name.

      Example
      \ECHO \\travel-alias1;
      Result
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

      Echo a Parameter

      To echo a parameter, use the \ECHO command, followed by the parameter prefix and parameter name.

      If you do not include the parameter prefix, the shell considers the parameter as a generic statement and displays the parameter as is.

      Example
      \ECHO -$r;
      Result
      9.5

      Command Alias

      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.

      Create Command Aliases

      To define an alias, use the \ALIAS command, followed by the command alias name and the query.

      Example
      \ALIAS travel-alias1 SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

      Run Command Aliases

      To run the command alias, type two backslashes \\, followed by the command alias name.

      Example
      \\travel-alias1;
      Result
      {
          "requestID": "b25c84d6-7b7b-440a-a286-5027e6ecbbb5",
          "signature": {
              "*": "*"
          },
          "results": [
          {
              "airline": {
                  "callsign": "MILE-AIR",
                  "country": "United States",
                  "iata": "Q5",
                  "icao": "MLA",
                  "id": 10,
                  "name": "40-Mile Air",
                  "type": "airline"
              }
          }
          ],
          "status": "success",
          // ...
      }

      List Command Aliases

      To list all the existing aliases, use the \ALIAS command without options.

      Example
      \ALIAS;
      Result
      serverversion  select version()
      travel-alias1  SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

      Delete Command Aliases

      You can delete a defined alias using the \UNLIAS command, followed by the alias name. This command can take multiple arguments and deletes the defined alias for every input name.

      Example
      \UNALIAS serverversion travel-alias1;
      Check aliases
      \ALIAS;
      Result
       ERROR 141 : Alias does not exist :

      Executing Prepared Statements

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

      1. Set the named and positional parameters that are present in the prepared statement.

      2. Prepare a statement using the SQL++ PREPARE statement. If you do not specify a name for the prepared statement, a unique name is assigned. You can use this auto-assigned name when executing the prepared statement. If you specify a name, you can use this name to run the prepared statement.

      3. Execute the prepared statement using the SQL++ EXECUTE statement.

      Canceling a Query

      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". An error is thrown if the timeout is invalid.

      • Command Line Option

      • Shell Command

      Use the -t or --timeout command line option, followed by the length of the timeout.


      Example
      ./cbq -e $BASE_URL -u $USER -p $PASSWORD --cacert $CACERT -skip-verify --timeout="2s"

      Use the \SET shell command to set the TIMEOUT parameter, followed by the length of the timeout.


      Example
      \SET -TIMEOUT 1ms;

      File Based Operations

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

      • Command Line Option

      • Shell Command

      Use the -f or --file command line option, followed by the input file.

      The cbq shell executes the commands present in the input file, prints them to stdout (or to a file if using redirects), and exits.


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

      sample.txt
      CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;
      SELECT * from `travel-sample`.inventory.airline LIMIT 2;
      SELECT callsign from `travel-sample`.inventory.airline LIMIT 3;
      \HELP;
      Example
      ./cbq -e $BASE_URL -u $USER -p $PASSWORD --cacert $CACERT -skip-verify -f=sample.txt
      Results
       Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.
      
       Path to history file for the shell : ~/.cbq_history
      CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;
      { ...
        "results": [ ],
        ...
      }
      SELECT * from `travel-sample`.inventory.airline LIMIT 2;
      { ...
        "results": [ ],
        ...
      }
      SELECT callsign from `travel-sample`.inventory.airline LIMIT 3;
      { ...
        "results": [ ],
        ...
      }
      \HELP;
      Help information for all shell commands.
      ...
      $

      Use the \SOURCE shell command, followed by the input file.

      The cbq shell executes the commands present in the input file and prints them to stdout, or to a file if using redirects.


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

      sample.txt
      CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;
      SELECT * from `travel-sample`.inventory.airline LIMIT 2;
      SELECT callsign from `travel-sample`.inventory.airline LIMIT 3;
      \HELP;
      Example
      \SOURCE sample.txt;
      Results
      CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;
      { ...
        "results": [ ],
       ...
      }
      SELECT * from `travel-sample`.inventory.airline LIMIT 2;
      { ...
        "results": [ ],
        ...
      }
      SELECT callsign from `travel-sample`.inventory.airline LIMIT 3;
      { ...
        "results": [ ],
        ...
      }
      \HELP;
      Help information for all shell commands.
      ...
      cbq>

      Redirecting Results to a File

      You can redirect all the output for a session or part of a session to a specified output file. If the file doesn’t exist, it is created. If the file already exists, it is overwritten.

      • Command Line Option

      • Shell Command

      Use the -o or --output command line option, followed by the output file.


      Example
      ./cbq -e $BASE_URL -u $USER -p $PASSWORD --cacert $CACERT -skip-verify -o temp_output.txt

      To start redirecting commands during a session, use \REDIRECT followed by the output file. To stop redirecting commands, use \REDIRECT OFF. All the commands specified after \REDIRECT and before \REDIRECT OFF are saved into the specified output file.

      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.

      You can append redirected output to an existing file using File Append Mode.


      Example
      \REDIRECT temp_output.txt;
      CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;
      \HELP;
      \REDIRECT OFF;

      File Append Mode

      You can use file append mode to specify that cbq should append redirected output to the end of an existing file, rather than overwriting the existing file.

      Note that file append mode is only available with the \REDIRECT command within a shell session. It is not available for the -o or --output command line option. When you use the -o or --output command line option, the specified output file is always overwritten.

      To use file append mode, include a plus sign + at the start of the output path or filename.

      Example
      \REDIRECT +temp_output.txt;
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;
      \REDIRECT OFF;

      Every time you start appending to the output file, a timestamp is added to the end of the output file, followed by any redirected commands and results.

      -- <2021-07-30T14:48:43.661+01:00> : opened in append mode
      
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1
      ...

      Cluster and Shell Information

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

      Version

      You can find the version of the client (shell) by using either the command line option to display the current version of the shell and exit, or as a shell command to print the version of the shell during the shell session.

      To display the version of the query service, use the VERSION() function in SQL++.

      • Command Line Option

      • Shell Command

      Use the -v or --version command line option.


      Example
      ./cbq -v
      Result
      GO VERSION : go1.22.2
      SHELL VERSION : 7.6.2-3721
      
      Use N1QL queries select version(); or select min_version(); to display server version.

      Use the \VERSION shell command.


      Example
      \VERSION;
      Result
      GO VERSION : go1.22.2
      SHELL VERSION : 7.6.2-3721
      
      Use N1QL queries select version(); or select min_version(); to display server version.

      You can view the copyright, attributions, and distribution terms of the command line query tool using the \COPYRIGHT shell command.

      Example
      \COPYRIGHT;
      Result
      Copyright (c) 2016 Couchbase, Inc. Licensed under the Apache License, Version 2.0 (the "License");
      you may not use this file except in compliance with the License. You may obtain a copy of the
      License at http://www.apache.org/licenses/LICENSE-2.0
      Unless required by applicable law or agreed to in writing, software distributed under the
      License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
      either express or implied. See the License for the specific language governing permissions
      and limitations under the License.

      Exiting the cbq Shell

      You can exit the cbq shell by pressing Ctrl+D or by using the \EXIT or \QUIT 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.

      Example
      \EXIT;
      Result
      $

      Executing a Script

      You can use the --script option to start cbq, execute a single SQL++ query, and exit the shell.

      Example
      ./cbq -u $USER -p $PASSWORD -e $BASE_URL --cacert $CACERT -skip-verify \
      --script="SELECT * FROM \`travel-sample\`.inventory.airline LIMIT 1;"
      Results
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.
      
      Path to history file for the shell : ~/.cbq_history
      
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;
      {
          ...
      }

      Exit On Error

      When you specify the argument --exit-on-error, the cbq shell checks the result returned after executing the query for any error and exits when the first error is encountered.

      Exit Status

      The cbq shell returns the exit status 0 for successful exit with no errors and 1 if an error was encountered before exiting.

      Available Command Line Options and Shell Commands

      The command line options are case sensitive. The cbq shell commands are case insensitive.
      Table 3. Command Line Options for cbq Shell
      Option Arguments Description and Examples

      -e

      --engine

      string (url)

      The connection string consists of a protocol scheme, followed by a host.

      For more details, refer to Connecting to the Cluster.

      Shell command: \CONNECT

      Default

      http://localhost:8091

      Examples
      ./cbq --engine $BASE_URL \
      -u $USER -p $PASSWORD -skip-verify
      ./cbq -e $BASE_URL \
      -u $USER -p $PASSWORD -skip-verify
      Result
      Connected to : https://<HOST>:18091/. Type Ctrl-D or \QUIT to exit.
      Path to history file for the shell : /Users/myuser1/.cbq_history

      -ne

      --no-engine

      boolean [1]

      When specified, the cbq shell does not connect to any query service. You must explicitly connect to a query service using the \CONNECT shell command.

      Default

      false

      Example
      ./cbq --no-engine

      -ncfg

      --networkconfig

      string (auto, default, external)

      Specifies whether to connect to a node’s principal or alternate address.

      • auto — Select the principal address or alternate address automatically, depending on the input IP.

      • default — Use the principal address.

      • external — Use the alternate addresses.

      Default

      auto

      Example
      ./cbq -ncfg default \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      -q

      --quiet

      boolean [1]

      When specified, disables the startup connection message for the cbq shell.

      Default

      false

      Example
      ./cbq -q \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify
      Result
      cbq>

      -ad

      --advise

      boolean [1]

      Runs ADVISE on all queries in the specified file, or that are read from standard input, if a file is not provided with the -file option.

      Default

      false

      Example
      ./cbq -advise -file queries.txt \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify
      queries.txt
      SELECT ADVISOR(["select * from collection1 where id = 1;",
                      "select * from collection2 where name is not missing;"])
      Result
      {
          "requestID": "15ed5c93-e5f6-4193-83fa-6fdc87847552",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": {
                  "recommended_indexes": [
                      {
                          "index": "CREATE INDEX adv_id ON `collection1`(`id`)",
                          "statements": [
                              {
                                  "run_count": 1,
                                  "statement": "select * from collection1 where id = 1;"
                              }
                          ]
                      },
                      {
                          "index": "CREATE INDEX adv_name ON `collection2`(`name`)",
                          "statements": [
                              {
                                  "run_count": 1,
                                  "statement": "select * from collection2 where name is not missing;"
                              }
                          ]
                      }
                  ]
              }
          }
          ]
      }

      -a

      --analytics

      boolean [1]

      Only applicable when connecting to the Analytics Service. If specified, when you connect to a Couchbase Capella cluster, cbq automatically discovers and connects to the Analytics service. This option also switches on batch mode.

      Default

      false

      Example
      ./cbq --analytics \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      -b

      --batch

      string (on, off) [2]

      This option is available only with the Analytics Service. When specified, cbq sends the queries to Analytics only when you hit EOF or \ to indicate the end of the batch input.

      Default

      off

      Examples
      ./cbq --batch \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      You can also set the batch mode in the interactive session using the \SET command:

      \set batch on;
      \set batch off;

      -qc

      --query_context

      string

      Sets the query context parameter. For more information, see Query Context.

      Shell command: \SET -query_context

      Default

      none

      Example
      ./cbq -qc "travel-sample.inventory" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      -t

      --timeout

      string (duration)

      Sets the query timeout parameter. For more information, see timeout.

      Shell command: \SET -timeout

      Default

      0ms

      Example
      ./cbq --timeout="1s" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      For further examples, see Connection Timeout Parameter.

      -u

      --user

      string

      Specifies a single user name to log in to Couchbase. When used by itself, without the -p option to specify the password, you will be prompted for the password.

      This option requires administration credentials and you cannot switch the credentials during a session.

      Couchbase recommends using the -u and -p option if your password contains special characters such as #, $, %, &, (,), or '.

      Default

      none

      Example
      ./cbq -u $USER \
      -e $BASE_URL -skip-verify
      Result
      Enter Password:

      -p

      --password

      string

      Specifies the password for the given user name. You cannot use this option by itself. It must be used with the -u option to specify the user name.

      This option requires administration credentials and you cannot switch the credentials during a session.

      Couchbase recommends using the -u and -p option if your password contains special characters such as #, $, %, &, (,), or '.

      Default

      none

      Example
      ./cbq -u $USER -p $PASSWORD \
      -e $BASE_URL -skip-verify

      -c

      --credentials

      string

      Specify the login credentials in the form of username:password. If you specify more than one credential with this option, Couchbase Capella only uses the first one.

      Shell command: \SET -creds

      Default

      none

      Example
      ./cbq -c=$USER:$PASSWORD \
      -e $BASE_URL -skip-verify

      -v

      --version

      boolean [1]

      When specified, provides the version of the cbq shell. To display the version of the query engine (this is not the same as the version of the Couchbase Capella cluster), use one of the following SQL++ queries:

      select version();
      select min_version();

      Shell command: \VERSION

      Default

      false

      Example
      ./cbq --version
      Result
      GO VERSION : go1.21.6
      SHELL VERSION : 7.6.0-2176
      
      Use N1QL queries select version(); or select min_version(); to display server version.

      -h

      --help

      none

      Provides help for the command line options.

      Shell command: \HELP

      Default

      none

      Example
      ./cbq --help

      -s

      -script

      string

      Provides a single command mode to execute a query from the command line.

      You can also use multiple -s options on the command line. If one of the commands is incorrect, an error is displayed for that command and cbq continues to execute the remaining commands.

      Default

      none

      Examples
      ./cbq -s="SELECT * FROM \`travel-sample\`.inventory.airline LIMIT 1;" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify
      ./cbq -s="\SET v 1" -s="\SET b 2" -s="\PUSH b3" \
      -s="\SET b 5" -s="\SET" -ne
      Result
      Path to history file for the shell : ~/.cbq_history
      
       \SET v 1
       \SET b 2
       \PUSH b3
       ERROR 139 : Too few input arguments to command.
       \SET b 5
       \SET
      
       Query Parameters :
       Named Parameters :
       User Defined Session Parameters :
       Predefined Session Parameters :
       Parameter name : histfile
       Value : [".cbq_history"]
       Parameter name : batch
       Value : ["off"]
       Parameter name : quiet
       Value : [false]
       Parameter name : v
       Value : [1]
       Parameter name : b
       Value : [5]

      -f

      --file

      string (path)

      Provides an input file which contains all the commands to be run.

      Shell command: \SOURCE

      Default

      none

      Example
      ./cbq --file="sample.txt" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      -o

      --output

      string (path)

      Specifies an output file where the commands and their results are to be written.

      If the file doesn’t exist, it is created. If the file already exists, it is overwritten.

      Shell command: \REDIRECT

      Default

      none

      Example
      ./cbq -o="results.txt" -s="SELECT * FROM \`travel-sample\`.inventory.airline LIMIT 1;" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      --pretty

      boolean [1]

      Specifies whether the output should be formatted with line breaks and indents.

      This option is set to true by default. To specify that the output should not be formatted with line breaks and indents, you must explicitly set this option to false.

      Default

      true

      Example
      ./cbq --pretty=false -s="SELECT * FROM \`travel-sample\`.inventory.airline LIMIT 1;" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      --exit-on-error

      boolean [1]

      When specified, the cbq shell must exit when it encounters the first error.

      Default

      false

      Example
      ./cbq --exit-on-error -f="sample.txt" \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      --cacert

      string (path)

      Only applicable when using an encrypted protocol scheme, such as couchbases://.

      Specifies the path to the root CA certificate to verify the identity of the cluster.

      Default

      none

      Example
      ./cbq --cacert $CACERT \
      -e $BASE_URL -u $USER -p $PASSWORD -skip-verify

      --cert

      string (path)

      Only applicable when using an encrypted protocol scheme, such as couchbases://.

      Specifies the path to the chain certificate.

      Default

      none

      Example
      ./cbq --cert ./client/client/chain.pem

      --key

      string (path)

      Only applicable when using an encrypted protocol scheme, such as couchbases://.

      Specifies the path to the client key file.

      Default

      none

      Examples
      ./cbq --key ./client/client/client.key

      --no-ssl-verify or

      -skip-verify

      boolean [1]

      Only applicable when using an encrypted protocol scheme, such as couchbases://.

      When specified, the cbq shell can skip the verification of certificates.

      Default

      false

      Examples
      ./cbq --no-ssl-verify -f="sample.txt" \
      -e $BASE_URL -u $USER -p $PASSWORD
      ./cbq -skip-verify \
      -e $BASE_URL -u $USER -p $PASSWORD
      Table 4. cbq Shell Commands
      Shell Command Arguments Description and Examples

      \CONNECT

      url

      Connects cbq shell to the specified Couchbase Capella cluster.

      The connection string consists of a protocol scheme, followed by a host.

      For more details, refer to Connecting to the Cluster.

      Command Line Option: -e or --engine

      Examples
      \CONNECT <BASE_URL>;

      \DISCONNECT

      none

      Disconnects the cbq shell from the Couchbase Capella cluster.

      Example
      \DISCONNECT;
      Result
       Couchbase query shell not connected to any endpoint.
       Use \CONNECT command to connect.

      \EXIT

      \QUIT

      none

      Exits cbq shell.

      Examples
      \EXIT;
      \QUIT;

      \SET

      parameter value

      parameter = prefix : variable name

      Sets the top most value of the stack for the given variable with the specified value.

      Variables can be of the following types:

      • Query parameters

      • Session variables

      • User-defined

      • Pre-defined and named parameters.

      When the \SET command is used without any arguments, it displays the values for all the parameters of the current session.

      Examples
      \SET -args [5, "12-14-1987"];
      \SET -args [6,7];

      \PUSH

      parameter value

      Pushes the specified value on to the given parameter stack.

      When the \PUSH command is used without any arguments, it copies the top element of every variable’s stack, and then pushes that copy to the top of the respective variable’s stack.

      While each variable stack grows by 1, the previous values are preserved.

      Examples
      \PUSH -args  [8];
      \PUSH;
      Check variable stack
      \SET;
      Result
       Query Parameters :
       Parameter name : args
       Value : [[6,7] [8] [8]]
      ...

      \UNSET

      parameter

      Deletes or resets the entire stack for the specified parameter.

      Examples
      \UNSET -args;
      \SET;
      Result
       Query Parameters :
       ...

      \POP

      parameter

      Pops the top most value from the specified parameter’s stack.

      When the \POP command is used without any arguments, it pops the top most value of every variable’s stack.

      Examples
      \POP -args;
      \SET;
      Result
       Query Parameters :
       Parameter name : args
       Value : [[6,7] [8]]

      \ALIAS

      shell-command or n1ql-statement

      Creates a command alias for the specified cbq shell command or SQL++ statement. You can then execute the alias using \\alias-name;.

      When the \ALIAS command is used without any arguments, it lists all the available aliases.

      Examples
      \ALIAS travel-limit1 SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;
      \ALIAS;
      Result
      serverversion  select version()
      travel-limit1  SELECT * FROM `travel-sample`.inventory.airline LIMIT 1
      Execute alias
      \\serverversion;
      Result
      {
          "requestID": "ef63f01b-f159-437f-a4df-28d6145fa3c2",
          "signature": {
              "$1": "string"
          },
          "results": [
              {
                  "$1": "7.0.0-N1QL"
              }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "14.54962ms",
              "executionTime": "13.164635ms",
              "resultCount": 1,
              "resultSize": 34,
              "serviceLoad": 12
          }
      }

      \UNALIAS

      alias-name

      Deletes the specified alias.

      Examples
      \UNALIAS travel-limit1;
      \ALIAS;
      Result
      serverversion  select version()

      \ECHO

      args

      where args can be parameters, aliases, or any input.

      If the input is a parameter, this command echoes (displays) the value of the parameter. The parameter must be prefixed according to its type. See Table 1 for details.

      If the input is not a parameter, the command echoes the statement as is.

      If the input is an alias, the command displays the value of an alias command.

      Examples
      \ECHO -$r;
      \ECHO \\serverversion;
      Result
      select version()

      \VERSION

      none

      Displays the version of the client shell.

      Command Line Option: -v or --version

      Example
      \VERSION;
      Result
      GO VERSION : go1.21.6
      SHELL VERSION : 7.6.0-2176
      
      Use N1QL queries select version(); or select min_version(); to display server version.

      \HELP

      command

      Displays the help information for the specified command. When used without any arguments, it lists all the commands supported by the cbq shell.

      Command Line Option: -h or --help

      Example
      \HELP ECHO;
      Result
      \ECHO args ...
      Echo the input value. args can be a name (a prefixed-parameter), an alias (command alias) or
      a value (any input statement).
      Example :
      \ECHO -$r ;
      \ECHO \\tempalias;

      \COPYRIGHT

      none

      Displays the copyright, attributions, and distribution terms.

      Example
      \COPYRIGHT;

      \SOURCE

      input-file

      Reads and executes the commands from a file. Multiple commands in the input file must be separated by ; and a new line.

      Command Line Option: -f or --file

      For example, sample.txt contains the following commands:

      sample.txt
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;
      \ECHO this;
      #This is a comment;
      Example
      \SOURCE sample.txt;

      \REDIRECT

      filename

      Redirects the output of all the commands to the specified file until the cbq shell receives the \REDIRECT OFF command. By default, the file is created in the directory that you were in when you started the cbq shell. You can specify a different location using relative paths.

      If the file doesn’t exist, it is created. If the file already exists, it is overwritten. You can append redirected output to an existing file using File Append Mode.

      Command Line Option: -o or --output

      Example
      \REDIRECT temp_out.txt;

      \REDIRECT OFF

      none

      Redirects the output of subsequent commands from a custom file to standard output (os.stdout).

      Example
      \REDIRECT OFF;

      Shortcut Keys for cbq Shell

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

      Table 5. Shortcut Keys for cbq Shell
      Keystroke Action

      Ctrl+A, Home

      Move cursor to beginning of line

      Ctrl+E, End

      Move cursor to end of line

      Ctrl+B, Left

      Move cursor one character left

      Ctrl+F, Right

      Move cursor one character right

      Ctrl+Left

      Move cursor to previous word

      Ctrl+Right

      Move cursor to next word

      Ctrl+D, Del

      (if line is not empty) Delete character under cursor

      Ctrl+D

      (if line is empty) End of File - usually quits application

      Ctrl+C

      Reset input (create new empty prompt)

      Ctrl+L

      Clear screen (line is unmodified)

      Ctrl+T

      Transpose previous character with current character

      Ctrl+H, BackSpace

      Delete character before cursor

      Ctrl+W

      Delete word leading up to cursor

      Ctrl+K

      Delete from cursor to end of line

      Ctrl+U

      Delete from start of line to cursor

      Ctrl+P, Up

      Previous match from history

      Ctrl+N, Down

      Next match from history

      Ctrl+R

      Reverse Search history (Ctrl+S forward, Ctrl+G cancel)

      Ctrl+Y

      Paste from Yank buffer (Alt+Y to paste next yank instead)

      Tab

      Next completion

      Shift+Tab

      (after Tab) Previous completion


      1. Invoking a boolean option with no value sets the value to true.
      2. Invoking this option with no value sets the value to on.