Configure Queries
- Capella Operational
You can configure the Query service using request-level query parameters.
Request-Level Parameters
To set a request-level parameter, do one of the following:
-
Use the Query Options window in the Query tab.
-
Use the cbq shell at the command line.
-
Use an SDK client program.
Generally, use the cbq
shell or the Query tab as a sandbox to test queries in your Capella cluster.
Use
an SDK client program for your production queries.
-
Capella UI
-
Command Line
To set request-level preferences in the Query tab:
-
Go to
and click Query Options to display the Query Options window. -
Specify the preferences — if a preference is not explicitly listed, click + next to Named Parameters and add its name and value.
-
Click Save.
![The Query Options window.](../../clusters/_images/query-workbench-settings.png)
For more examples, see the Couchbase Transactions guide.
To set request-level parameters in cbq
, use the \SET
command.
The parameter name must be prefixed by a hyphen.
The following example sets the request-level timeout, pretty-print, and max parallelism parameters, and runs a query:
\SET -timeout "30m";
\SET -pretty true;
\SET -max_parallelism 3;
SELECT * FROM "world" AS hello;
For more examples, see Parameter Manipulation in the cbq documentation.
The table below contains details of all request-level parameters, along with examples.
Name | Description | Schema |
---|---|---|
args |
Supplies the values for positional parameters in the statement. Applicable if the statement or prepared statement contains 1 or more positional parameters. The value is an array of JSON values, one for each positional parameter in the statement. Refer to Named Parameters and Positional Parameters for details. |
< object > array |
atrcollection |
Specifies the collection where the active transaction record (ATR) is stored. The collection must be present. If not specified, the ATR is stored in the default collection in the default scope in the bucket containing the first mutated document within the transaction. The value must be a string in the form |
string |
auto_execute |
Specifies that prepared statements should be executed automatically as soon as they are created. This saves you from having to make two separate requests in cases where you want to prepare a statement and execute it immediately. Refer to Auto-Execute for more information. |
boolean |
client_context_id |
A piece of data supplied by the client that is echoed in the response, if present. SQL++ is agnostic about the content of this parameter; it is just echoed in the response.
|
string |
compression |
Compression format to use for response data on the wire. Values are case-insensitive. |
enum (ZIP, RLE, LZMA, LZO, NONE) |
controls |
Specifies if there should be a controls section returned with the request results. When set to If the request qualifies for caching, these values will also be cached in the |
boolean |
durability_level |
The level of durability for mutations produced by the request. If the request contains a Durability is also supported for non-transactional DML statements.
In this case, the If not specified, the default durability level is |
enum ("", none, majority, majorityAndPersistActive, persistToMajority) |
encoded_plan |
In databases running Couchbase Server 6.5 and later, this parameter is ignored and has no effect. It is included for compatibility with previous versions of Couchbase Server. |
string |
encoding |
Desired character encoding for the query results. Only possible value is |
string |
format |
Desired format for the query results. Values are case-insensitive. |
enum (JSON, XML, CSV, TSV) |
kvtimeout |
The approximate time to wait for a KV get operation before timing out.
This applies to statements within a transaction, and to non-transactional statements, whether If The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of |
string |
max_parallelism |
Specifies the maximum parallelism for the query. The default value is the same as the number of partitions of the index selected for the query. |
integer (int32) |
memory_quota |
Specifies the maximum amount of memory the request may use, in MB. Specify This parameter enforces a ceiling on the memory used for the tracked documents required for processing a request. It does not take into account any other memory that might be used to process a request, such as the stack, the operators, or some intermediate values. Within a transaction, this setting enforces the memory quota for the transaction by tracking the
delta table and the transaction log (approximately). |
integer (int32) |
metrics |
Specifies that metrics should be returned with query results. |
boolean |
namespace |
Specifies the namespace to use. Currently, only the |
string |
numatrs |
Specifies the total number of active transaction records.
Must be a positive integer. |
integer (int32) |
pipeline_batch |
Controls the number of items execution operators can batch for Fetch from the KV. |
integer (int32) |
pipeline_cap |
Maximum number of items each execution operator can buffer between various operators. |
integer (int32) |
prepared |
Required if The name of the prepared SQL++ statement to be executed. Refer to EXECUTE for examples. If both |
string |
preserve_expiry |
Specifies whether documents should keep their current expiration setting when modified by a DML statement. If If Not supported for statements in a transaction. |
boolean |
pretty |
Specifies the query results returned in pretty format. |
boolean |
profile |
Specifies if there should be a profile section returned with the request results. The valid values are:
If |
enum (off, phases, timings) |
query_context |
Specifies the namespace, bucket, and scope used to resolve partial keyspace references within the request. The query context may be a full path, containing namespace, bucket, and scope; or a relative path, containing just the bucket and scope.
Currently, only the |
string |
readonly |
Controls whether a query can change a resulting recordset. If
When using GET requests, it’s best to set |
boolean |
scan_cap |
Maximum buffered channel size between the indexer client and the query service for index scans. This parameter controls when to use scan backfill. Use |
integer (int32) |
scan_consistency |
Specifies the consistency guarantee or constraint for index scanning. The valid values are:
Values are case-insensitive. For multi-statement requests, the default behavior is RYOW within each request.
If you want to disable RYOW within a request, add a separate If the request contains a |
enum (not_bounded, at_plus, request_plus, statement_plus) |
scan_vector |
Required if Specify the lower bound vector timestamp for one keyspace when using Scan vectors are built of two-element [
Scan vectors have two forms:
Note that For queries referencing multiple keyspaces, use |
object |
scan_vectors |
Required if A map from keyspace names to scan vectors.
See The scan vectors can be Full or Sparse. |
object |
scan_wait |
Can be supplied with Specifies the maximum time the client is willing to wait for an index to catch up to the vector timestamp in the request. Specifies how much time the client is willing to wait for the indexer to satisfy the required Its format includes an amount and a mandatory unit, e.g.
Specify |
string (duration) |
signature |
Include a header for the results schema in the response. |
boolean |
sort_projection |
If If |
boolean |
statement |
Required if Any valid SQL statement for a POST request, or a read-only SQL statement (SELECT, EXPLAIN) for a GET request. If both When specifying the request parameters as form data, the statement may not contain an unescaped semicolon ( This restriction does not apply when specifying the request parameters in JSON format. |
string |
timeout |
Maximum time to spend on the request before timing out. The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of If tximplicit or txid is set, this parameter is ignored.
The request inherits the remaining time of the transaction as timeout. |
string (duration) |
txdata |
Transaction data. For internal use only. |
object |
txid |
Required for statements within a transaction. Transaction ID. Specifies the transaction to which a statement belongs. For use with DML statements within a transaction, rollbacks, and commits. The transaction ID should be the same as the transaction ID generated by the |
string (UUID) |
tximplicit |
Specifies that a DML statement is a singleton transaction. When this parameter is true, the Query service starts a transaction and executes the statement. If execution is successful, the Query service commits the transaction; otherwise the transaction is rolled back. The statement may not be part of an ongoing transaction.
If the txid request-level parameter is set, the |
boolean |
txstmtnum |
Transaction statement number.
The transaction statement number must be a positive integer, and must be higher than any previous transaction statement numbers in the transaction.
If the transaction statement number is lower than the transaction statement number for any previous statement, an error is generated. |
integer (int32) |
txtimeout |
Maximum time to spend on a transaction before timing out.
Only applies to Within a transaction, the request-level timeout parameter is ignored.
The transaction timeout clock starts when the The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of The default is |
string (duration) |
use_cbo |
Specifies whether the cost-based optimizer is enabled. |
boolean |
use_fts |
Specifies that the query should use a full-text index. If the query contains a If the query does not contain a Refer to Flex Indexes for more information. |
boolean |
use_replica |
Specifies whether a query can fetch data from a replica vBucket if active vBuckets are inaccessible. The possible values are:
Do not enable read from replica when you require consistent results. Only SELECT queries that are not within a transaction can read from replica. Reading from replica is only possible with databases running Couchbase Server 7.6.0 or later. Note that KV range scans cannot currently be started on a replica vBucket.
If a query uses sequential scan and a data node becomes unavailable, the query might return an error, even if read from replica is enabled for the request. |
enum (off, on, unset) |
<symbol><identifier> |
Supplies the value for a named parameter in the statement. Applicable if the statement or prepared statement contains 1 or more named parameters. The name of this property consists of two parts:
The value of the named parameter can be any JSON value. Refer to Named Parameters and Positional Parameters for details. |
string (any JSON value) |
Transactional Scan Consistency
If the request contains a BEGIN TRANSACTION
statement, or a DML statement with the tximplicit parameter set to true
, then the scan_consistency parameter sets the transactional scan consistency.
If you specify a transactional scan consistency of request_plus
, statement_plus
, or at_plus
, or if you specify no transactional scan consistency, the transactional scan consistency is set to request_plus
; otherwise, the transactional scan consistency is set as specified.
Scan consistency at start of transaction | Transactional scan consistency |
---|---|
Not set |
|
|
|
|
|
Any DML statements within the transaction that have no scan consistency set will inherit from the transactional scan consistency.
Individual DML statements within the transaction may override the transactional scan consistency.
If you specify a scan consistency of not_bounded
for a statement within the transaction, the scan consistency for the statement is set as specified.
When you specify a scan consistency of request_plus
, statement_plus
, or at_plus
for a statement within the transaction, the scan consistency for the statement is set to request_plus
.
However, request_plus
consistency is not supported for statements using a full-text index.
If any statement within the transaction uses a full-text index, by means of the SEARCH function or the Flex Index feature, the scan consistency is set to not_bounded
for the duration of the full-text search.
Scan consistency for statement within transaction | Inherited scan consistency |
---|---|
Not set |
Transactional scan consistency |
|
|
|
|
Named Parameters and Positional Parameters
You can add placeholder parameters to a statement, so that you can safely supply variable values when you run the statement. A placeholder parameter may be a named parameter or a positional parameter.
-
To add a named parameter to a query, enter a dollar sign
$
or an at sign@
followed by the parameter name. -
To add a positional parameter to a query, enter a dollar sign
$
or an at sign@
followed by the number of the parameter, or enter a question mark?
.
To run a query containing placeholder parameters, you must supply values for the parameters.
-
You can use an additional request-level parameter to supply the value for a named parameter. The name of this property is a dollar sign
$
or an at sign@
followed by the parameter name. -
The args request-level parameter enables you to supply a list of values for positional parameters.
You can supply the values for placeholder parameters using any of the methods used to specify request-level parameters.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
-
Capella UI
-
Command Line
The following query uses named parameter placeholders. The parameter values are supplied using the Query Options window.
name |
|
value |
|
name |
|
value |
|
SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;
The named parameters and named parameter placeholders in this example use a mixture of @
and $
symbol prefixes.
In clusters running Couchbase Server 7.6.0 and later, you can use either of these symbols as preferred.
In previous versions of Couchbase Server, you must use the $
symbol prefix.
The following query uses named parameter placeholders. The parameter values are supplied using the cbq shell.
\SET -@country "France";
\SET -$altitude 500;
SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;
The named parameters and named parameter placeholders in this example use a mixture of @
and $
symbol prefixes.
In clusters running Couchbase Server 7.6.0 and later, you can use either of these symbols as preferred.
In previous versions of Couchbase Server, you must use the $
symbol prefix.
-
Capella UI
-
Command Line
The following query uses numbered positional parameter placeholders. The parameter values are supplied using the Query Options window.
$1 |
|
$2 |
|
SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;
In this case, the first positional parameter value is used for the placeholder numbered $1
, the second positional parameter value is used for the placeholder numbered @2
, and so on.
The numbered positional parameter placeholders in this example use a mixture of @
and $
symbol prefixes.
In clusters running Couchbase Server 7.6.0 and later, you can use either of these symbols as preferred.
In previous versions of Couchbase Server, you must use the $
symbol prefix.
The following query uses numbered positional parameter placeholders. The parameter values are supplied using the cbq shell.
\SET -args ["France", 500];
SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;
In this case, the first positional parameter value is used for the placeholder numbered $1
, the second positional parameter value is used for the placeholder numbered @2
, and so on.
The numbered positional parameter placeholders in this example use a mixture of @
and $
symbol prefixes.
In clusters running Couchbase Server 7.6.0 and later, you can use either of these symbols as preferred.
In previous versions of Couchbase Server, you must use the $
symbol prefix.
-
Capella UI
-
Command Line
The following query uses unnumbered positional parameter placeholders. The parameter values are supplied using the Query Options window.
$1 |
|
$2 |
|
SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;
In this case, the first positional parameter value is used for the first ?
placeholder, the second positional parameter value is used for the second ?
placeholder, and so on.
The following query uses unnumbered positional parameter placeholders. The parameter values are supplied using the cbq shell.
\SET -args ["France", 500];
SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;
In this case, the first positional parameter value is used for the first ?
placeholder, the second positional parameter value is used for the second ?
placeholder, and so on.
For more details and examples, including examples using SDKs, see the Prepare Statements for Reuse guide.