Mastering the Couchbase N1QL Shell: N1QL Rest API and Prepared Statements
Learn how the design consideration to represent parameters using stacks can allow for better scripting and better ease of use.
Join the DZone community and get the full member experience.
Join For FreeThis is part 2 of the 3 part series discussing the new and improved shell. Be sure to check out Part 1 first!
In the previous article, we saw how to connect to a Couchbase cluster/query endpoint and how to manage secure connections. With this article, let's dive a little deeper into interacting with N1QL.
Interacting With the N1QL REST API Using Cbq
N1QL provides a REST API that allows clients to execute N1QL statements. This is done by setting various request parameters, named and positional parameters. In order to interact with the query REST API/named and positional parameters and allow for better scripting, cbq allows three kinds of parameters, with each type differentiated by a prefix:
Query parameters represent the request and positional parameters as defined by the query REST API. In order to manipulate the value of any of the given query parameters, prefix it with a -.
Session parameters can be of two types: Predefined parameters that do not contain a prefix and user-defined parameters that are temporary variables defined on a per session basis. histfile, batch, and quiet are the predefined parameters. histfile represents the name of the file where history of the shell is stored. By default, this file is named .cbq_history and is found under ~/.
The batch parameter takes two values: on and off. When set to on, we treat the session as a batch mode session. This means all of the queries are stored and sent to the server in batch when the user issues a \. This resets the buffer. Setting the batch parameter to off makes cbq switch back to single query mode. The quiet parameter takes boolean values and allows the user to decide if they want to display the error/warning/help messages within the shell session. By default, it is set to false.
The third kind of parameters are named named parameters. These are prefixed by a -$ and represent named variables as referred to within any N1QL query. These will be discussed in greater detail in the following section.
All of the above parameters can be assigned different values using the set command. Use the SET or PUSH command to assign a value to it, and the UNSET or POP command to reset the value. We shall discuss these commands in more detail in the following sections.
A table specifying the current list of request parameters as supported by the REST API is given in the N1QL REST API documentation.
Parameter Configuration: How to Set Parameter Values
Now that we know what the different kinds of parameters are, let’s look at how to set them. Internally, every parameter is defined as a stack. And each parameter has a separate stack associated with it. It can contain multiple values.
The user can either push a new value onto the stack using the \PUSH command or set the named stack with a value using the \SET command. The difference between \SET and \PUSH is that \SET always modifies the top of a variable’s stack rather than resetting the whole parameter stack or pushing onto the stack, and \PUSH adds to the stack.
\SET <prefix><name> <value>;
\PUSH <prefix><name> <value>;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.
Both \SET and \PUSH can also be issued without any arguments. If \PUSH has no arguments then it copies the top element of every variable’s stack, and then pushes that copy to the top of its respective stack. So each stack grows by 1, but the values are preserved. The user can then use \SET to set the top value. This ensures that the stack does not grow indefinitely and the values on the stack are preserved. \SET with no input arguments displays all the parameters defined and their values for the shell current session.
In order to delete the values from the parameter stacks, we can either use the \UNSET or\POP commands. The difference between \POP and \UNSET is that the former pops the top of the stack for the corresponding parameter and the latter pops all the values and then deletes the parameter.
\POP <prefix><name>;
\UNSET <prefix><name>;If the \POP command has no arguments, then every variable’s stack is popped once. This allows the following pattern:
\PUSH
\SET …
\SET …
<n1ql commands> ...
\POP
Now, all previous settings are restored. All the settings were applied to the N1QL statements between \PUSH and \POP. Empty variable stacks are the same as the variable not being defined, so these can be removed after a \POP or \UNSET.
Running Prepared Statements With Named and Positional Query Parameters
In order to execute prepared statements we first need to prepare the statement. This is done using the N1QL PREPARE command. The command executes and displays the encoded_plan, name, operator which is the plan (explain), signature, and text (statement issued). This can be used to create either a prepared statement or a named preparedstatement.
Prepared Statement
Created using the N1QL Prepare command as PREPARE ; . If the name is not specified in the N1QL prepare statement, a unique one is assigned. This can then be used when executing the \PREPARED shell command.
Named Prepared Statement
Created using the N1QL prepare command by specifying a name as follows: PREPARE <name> FROM <query>;.
The name is explicitly specified in the N1QL prepare statement and the name can be used to run the prepared statement.
Running a Prepared Statement
In order to define and execute prepared statements the following steps need to be followed.
Set the named and positional parameters that are present in the prepare statement.
PREPARE n1ql <query>;(prepared statement or a named prepared statement — see below).EXECUTE <name>;.
Named and Positional Parameters
When creating prepared statements, we can refer to the parameters within the query either by name or by position. These are named and positional parameters, respectively.
In order to define named or positional parameters, we use the \SET or \PUSH shell commands as defined in the documentation for cbq.
For each named parameter, we prefix the variable name with -$. This is done as follows:
\PUSH -$r 9.5;
\SET -$date "1-1-2014";In the above examples, we create a named parameter r and date with values 9.5 and “1-1-2014”.
The -args query parameter is used to set positional parameters. The value for this parameter is an input array containing the different values that correspond to positions within the query: \SET -args [<comma separated values>] ;.
For example: \SET -args [ 9.5, "1-1-2014"];.
These will represent $1 and $2 within the query.
This can be better understood with the example below.
Executing Prepared Statements
Let us assume that we want to prepare a query that retrieves flight information for flights going from a source airport to a destination airport on a particular day.
cbq > prepare airinfo > FROMSELECT 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;
This gives the following results:
{
"requestID": "7ba73e2b-e465-488f-91bf-23fe2926ec32",
"signature": "json",
"results": [{
"encoded_plan": "H4sIAAAAAAAA/7RVb286Nwz+KpHXFyBlaDBNlW7iRbeC1q6DClpN1YS4LGeOtCG5OrkOVt0++xTuD3+ltfvp9w7bj+3nsX3hHdBIm2Ayz7QwEAFwMGKFEIFQpMzCAgebIQlvCaJ3+GZnwBRfczQSgcM/cql0Qmgg+uMQdJX7pSX1d0BlpN6UxhRdKHWnnN/CHwSl6CGCBBci1z7yJN5Qf+vEKtMh757UG0Tdgn8Ye1nMiprV/6L9IdCNSXA9lcL0gIMKRkls7mxOEoWizJKvY3OVQAQ/yG5vgRIlXiZ/dr+7bKIZ2WeUXlkTCGekVoI28xfcQOQpx4LDC25cJmRYzrHqsLM6Vk0GOLhMGLdljWshfVmIAwmT4ta9VOkSIrjYZyp17rYkvueg7V/74WJWzDjkTpkUIkidgrCS/YkM0cslcBAOIiD4NOfjeveChNao4YuW+WgMOl/TcsAB11kItGKKO7GTS0xyjXE7KDymcGuVqVPF5xVxsCZs0TXthCKtDKok9PsqeodKewzTl9YkqjwpaLValdz944zbrM/qBbeZMAmrYAk6r4wI2QfYbruBuQATm9Lfa5/KuTHKK6Hvy9MGDoQu137ukVb1YVaLEHEnDvOrhtL4Q4+FVunSn4vkXh67z0g8BZwTd4rC11xlKzS+vIxZEV6VQ4VjSraTdpb+U9ZJ8lCZ3XDOnN7UE4rVNhGcSo3wOWG4iFP6EMGzs+FQG9I7Vzm/nV298JV1+Fg17tzL2ig4eFyHWEaYCUJW/TmwBdkVmw7uBj8/MNEJdTlznbJf+JV7yRl1DloEx6mA4G2os+Fk/BuLD76vmBF7HI0G04dQsPpkmWO345vRCVSw8Yj9OniaMuo03xv7/ZfBZHBMp19fP7saXZ+l1r/oMraNuk4iNv2LHhtPrgcT9tNTJfpHKP4NAAD//45F4qpKBwAA",
"name": "airinfo",
"operator": {
"#operator": "Sequence",
"~children": [{
"#operator": "Authorize",
"privileges": {
"List": [{
"Priv": 1,
"Target": "default:travel-sample"
}, {
"Priv": 7,
"Target": "default:travel-sample"
}]
},
"~child": {
"#operator": "Sequence",
"~children": [{
"#operator": "Sequence",
"~children": [{
"#operator": "IndexScan2",
"index": "def_sourceairport",
"index_id": "5c12fecece7db107",
"index_projection": {
"primary_key": true
},
"keyspace": "travel-sample",
"namespace": "default",
"spans": [{
"exact": true,
"range": [{
"high": "$airport",
"inclusion": 3,
"low": "$airport"
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "r",
"keyspace": "travel-sample",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [{
"#operator": "Unnest",
"as": "s",
"expr": "(`r`.`schedule`)"
}]
}
},
{
"#operator": "Join",
"as": "a",
"keyspace": "travel-sample",
"namespace": "default",
"on_keys": "(`r`.`airlineid`)"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [{
"#operator": "Filter",
"condition": "((((`r`.`sourceairport`) = $airport) and ((`r`.`destinationairport`) = $1)) and ((`s`.`day`) = $2))"
}, {
"#operator": "InitialProject",
"result_terms": [{
"expr": "(`a`.`name`)"
}, {
"expr": "(`s`.`flight`)"
}, {
"expr": "(`s`.`utc`)"
}, {
"expr": "(`r`.`sourceairport`)"
}, {
"expr": "(`r`.`destinationairport`)"
}, {
"expr": "(`r`.`equipment`)"
}]
}]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [{
"expr": "(`a`.`name`)"
}]
},
{
"#operator": "FinalProject"
}
]
}
},
{
"#operator": "Stream"
}
]
},
"signature": {
"destinationairport": "json",
"equipment": "json",
"flight": "json",
"name": "json",
"sourceairport": "json",
"utc": "json"
},
"text": "prepare airinfo from 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;"
}
],
"status": "success",
"metrics": {
"elapsedTime": "13.714593ms",
"executionTime": "13.697482ms",
"resultCount": 1,
"resultSize": 8708
}
}The destination airport and the day can be positional parameters ($1 and $2), and the source airport can be named parameter $airport. This is a named prepared statement airinfo.
cbq > \SET -args ["LAX", 6];
cbq > \PUSH -$airport "SJC";Now, when we execute the above prepared statement, $airport for sourceairport in the query takes the value "SJC", $1 for destination airport takes the value "LAX" and $2 for day takes the value 6.
cbq > execute airinfo;
{
"requestID": "892826d7-2c00-4038-b447-99c1a1b87ba4",
"signature": null,
"results": [
{
"destinationairport": "LAX",
"equipment": "73W",
"flight": "FL449",
"name": "AirTran Airways",
"sourceairport": "SJC",
"utc": "06:43:00"
},
{
"destinationairport": "LAX",
"equipment": "CRJ CR7",
"flight": "AA097",
"name": "American Airlines",
"sourceairport": "SJC",
"utc": "09:25:00"
},
{
"destinationairport": "LAX",
"equipment": "CR9 CR7",
"flight": "DL704",
"name": "Delta Air Lines",
"sourceairport": "SJC",
"utc": "20:56:00"
},
{
"destinationairport": "LAX",
"equipment": "CR9 CR7",
"flight": "DL634",
"name": "Delta Air Lines",
"sourceairport": "SJC",
"utc": "03:30:00"
},
{
"destinationairport": "LAX",
"equipment": "73W 733 73C 73H 738",
"flight": "WN483",
"name": "Southwest Airlines",
"sourceairport": "SJC",
"utc": "19:37:00"
},
{
"destinationairport": "LAX",
"equipment": "73W 733 73C 73H 738",
"flight": "WN505",
"name": "Southwest Airlines",
"sourceairport": "SJC",
"utc": "20:39:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "US884",
"name": "US Airways",
"sourceairport": "SJC",
"utc": "22:33:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "US005",
"name": "US Airways",
"sourceairport": "SJC",
"utc": "23:04:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "US859",
"name": "US Airways",
"sourceairport": "SJC",
"utc": "01:00:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "US842",
"name": "US Airways",
"sourceairport": "SJC",
"utc": "00:35:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "US417",
"name": "US Airways",
"sourceairport": "SJC",
"utc": "18:56:00"
},
{
"destinationairport": "LAX",
"equipment": "CR7",
"flight": "UA282",
"name": "United Airlines",
"sourceairport": "SJC",
"utc": "21:02:00"
},
{
"destinationairport": "LAX",
"equipment": "320",
"flight": "VX703",
"name": "Virgin America",
"sourceairport": "SJC",
"utc": "09:43:00"
},
{
"destinationairport": "LAX",
"equipment": "320",
"flight": "VX456",
"name": "Virgin America",
"sourceairport": "SJC",
"utc": "13:38:00"
},
{
"destinationairport": "LAX",
"equipment": "320",
"flight": "VX849",
"name": "Virgin America",
"sourceairport": "SJC",
"utc": "04:33:00"
},
{
"destinationairport": "LAX",
"equipment": "320",
"flight": "VX629",
"name": "Virgin America",
"sourceairport": "SJC",
"utc": "03:56:00"
}
],
"status": "success",
"metrics": {
"elapsedTime": "32.608589ms",
"executionTime": "32.593337ms",
"resultCount": 16,
"resultSize": 3543,
"sortCount": 16
}
}
Now, let's say we want to execute the same prepared statements with different values for source and destination airports on a different day: cbq > \PUSH;.
Issuing this command allows us to save the current state (values) of the parameters. Let's verify this using the \SET command.
cbq> \SET;
Query Parameters :
Parameter name : args
Value : [["LAX",6] ["LAX",6]]
Named Parameters :
Parameter name : airport
Value : ["SJC" "SJC"]
User Defined Session Parameters :
Predefined Session Parameters :
Parameter name : histfile
Value : [".cbq_history"]
Parameter name : batch
Value : ["off"]
Parameter name : quiet
Value : [false]
As you can see above, the named parameter airport and the args query parameter stacks have increased in size by 1 with the top of the stack being copied and pushed onto the top of the stack. We can now use \SET to set these values.
It is important to note here that we can also directly push the new values of the corresponding parameters onto the stack directly.
cbq > \SET -args ["SJC", 3 ];
cbq > \SET -$airport "LAX";
The above statements set the values at the top of the stack. Now, when we check the values, we can see the new values:
cbq> \SET;
Query Parameters :
Parameter name : args
Value : [["LAX",6] ["SJC",3]]
Named Parameters :
Parameter name : airport
Value : ["SJC" "LAX"]
User Defined Session Parameters :
Predefined Session Parameters :
Parameter name : histfile
Value : [".cbq_history"]
Parameter name : batch
Value : ["off"]
Parameter name : quiet
Value : [false]
When we execute the prepared statement airinfo, it uses the values from the top of the respective parameter stacks and provides results accordingly.
cbq > execute airinfo; {
"requestID": "6fe1ac9-f161-4f79-a558-2d2230b6ba89",
"signature": null,
"results": [{
"destinationairport": "SJC",
"equipment": "CR7 CRJ",
"flight": "AA680",
"name": "American Airlines",
"sourceairport": "LAX",
"utc": "03:22:00"
}, {
"destinationairport": "SJC",
"equipment": "CR7 CRJ",
"flight": "AA632",
"name": "American Airlines",
"sourceairport": "LAX",
"utc": "15:22:00"
}, {
"destinationairport": "SJC",
"equipment": "CR9 CR7",
"flight": "DL859",
"name": "Delta Air Lines",
"sourceairport": "LAX",
"utc": "00:25:00"
}, {
"destinationairport": "SJC",
"equipment": "CR9 CR7",
"flight": "DL779",
"name": "Delta Air Lines",
"sourceairport": "LAX",
"utc": "12:50:00"
}, {
"destinationairport": "SJC",
"equipment": "CR9 CR7",
"flight": "DL272",
"name": "Delta Air Lines",
"sourceairport": "LAX",
"utc": "17:55:00"
}, {
"destinationairport": "SJC",
"equipment": "73W 73C 733 738",
"flight": "WN200",
"name": "Southwest Airlines",
"sourceairport": "LAX",
"utc": "18:45:00"
}, {
"destinationairport": "SJC",
"equipment": "73W 73C 733 738",
"flight": "WN059",
"name": "Southwest Airlines",
"sourceairport": "LAX",
"utc": "16:59:00"
}, {
"destinationairport": "SJC",
"equipment": "CR7",
"flight": "US655",
"name": "US Airways",
"sourceairport": "LAX",
"utc": "14:11:00"
}, {
"destinationairport": "SJC",
"equipment": "CR7",
"flight": "UA337",
"name": "United Airlines",
"sourceairport": "LAX",
"utc": "04:10:00"
}, {
"destinationairport": "SJC",
"equipment": "CR7",
"flight": "UA842",
"name": "United Airlines",
"sourceairport": "LAX",
"utc": "21:21:00"
}, {
"destinationairport": "SJC",
"equipment": "CR7",
"flight": "UA788",
"name": "United Airlines",
"sourceairport": "LAX",
"utc": "09:25:00"
}, {
"destinationairport": "SJC",
"equipment": "320",
"flight": "VX341",
"name": "Virgin America",
"sourceairport": "LAX",
"utc": "08:28:00"
}],
"status": "success",
"metrics": {
"elapsedTime": "167.923055ms",
"executionTime": "167.906948ms",
"resultCount": 12,
"resultSize": 2688,
"sortCount": 12
}
}
Since we have the results now, let's delete the top value of the stack for the positional parameters and delete all values of the named parameter airport.
cbq > \POP -args;
cbq > \UNSET -$airport;If we execute the prepared statement now, it will give us an error because named parameter -$airport is not defined.
cbq > execute airinfo; {
"requestID": "9407feab-abc3-4e5b-ba62-713219278bbc",
"signature": null,
"results": [],
"errors": [{
"code": 5010,
"msg": "Error evaluating span. - cause: No value for named parameter $airport."
}],
"status": "errors",
"metrics": {
"elapsedTime": "2.010702ms",
"executionTime": "1.99434ms",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}We can set the value again and then execute the prepared statement. It will take the new values and provide results.
cbq > \PUSH - $airport "SFO";
cbq > execute airinfo; {
"requestID": "d15d245a-e342-4e05-b19e-f30bbf0dce3b",
"signature": null,
"results": [{
"destinationairport": "LAX",
"equipment": "738",
"flight": "AA740",
"name": "American Airlines",
"sourceairport": "SFO",
"utc": "09:17:00"
},
"destinationairprt": "LAX",
"equipment": "738,
"flight": "AA607",
"name": "American Airlines",
"sourceairport": "SFO",
"utc": "02:08:00"
},
{
"destinationairport": "LAX",
"equipment": "738",
"flight": "AA654",
"name": "American Airlines",
"sourceairport": "SFO",
"utc": "07:23:00"
},
{
"destinationairport": "LAX",
"equipment": "738",
"flight": "AA927",
"name": "American Airlines",
"sourceairport": "SFO",
"utc": "15:28:00"
},
{
"destinationairport": "LAX",
"equipment": "E75",
"flight": "DL941",
"name": "Delta Air Lines",
"sourceairport": "SFO",
"utc": "18:26:00"
},
{
"destinationairport": "LAX",
"equipment": "E75",
"flight": "DL657",
"name": "Delta Air Lines",
"sourceairport": "SFO",
"utc": "08:28:00"
},
{
"destinationairport": "LAX",
"equipment": "733 73W 73C",
"flight": "WN269",
"name": "Southwest Airlines",
"sourceairport": "SFO",
"utc": "23:51:00"
},
{
"destinationairport": "LAX",
"equipment": "733 73W 73C",
"flight": "WN430",
"name": "Southwest Airlines",
"sourceairport": "SFO",
"utc": "03:20:00"
},
{
"destinationairport": "LAX",
"equipment": "733 73W 73C",
"flight": "WN440",
"name": "Southwest Airlines",
"sourceairport": "SFO",
"utc": "21:19:00"
},
{
"destinationairport": "LAX",
"equipment": "733 73W 73C",
"flight": "WN470",
"name": "Southwest Airlines",
"sourceairport": "SFO",
"utc": "07:48:00"
},
{
"destinationairport": "LAX",
"equipment": "738",
"flight": "US030",
"name": "US Airways",
"sourceairport": "SFO",
"utc": "09:47:00"
},
{
"destinationairport": "LAX",
"equipment": "319 320 752 738 753 739",
"flight": "UA664",
"name": "United Airlines",
"sourceairport": "SFO",
"utc": "01:07:00"
},
{
"destinationairport": "LAX",
"equipment": "319 320 752 738 753 739",
"flight": "UA812",
"name": "United Airlines",
"sourceairport": "SFO",
"utc": "04:07:00"
},
{
"destinationairport": "LAX",
"equipment": "319 320 752 738 753 739",
"flight": "UA517",
"name": "United Airlines",
"sourceairport": "SFO",
"utc": "20:25:00"
},
{
"destinationairport": "LAX",
"equipment": "319 320 752 738 753 739",
"flight": "UA938",
"name": "United Airlines",
"sourceairport": "SFO",
"utc": "16:45:00"
},
{
"destinationairport": "LAX",
"equipment": "320 319",
"flight": "VX851",
"name": "Virgin America",
"sourceairport": "SFO",
"utc": "05:16:00"
},
{
"destinationairport": "LAX",
"equipment": "320 319",
"flight": "VX942",
"name": "Virgin America",
"sourceairport": "SFO",
"utc": "08:29:00"
}
],
"status": "success",
"metrics": {
"elapsedTime": "89.118664ms",
"executionTime": "89.103172ms",
"resultCount": 17,
"resultSize": 3873,
"sortCount": 17
}
}As we can see above, the design consideration to represent parameters using stacks allows for better scripting and ease of use.
Opinions expressed by DZone contributors are their own.
Comments