Shell Utility Commands

connect
consistency
describe
durability
exit
help
history
import
load
mode
output
page
show faults
show query
show tables
show users
show roles
timeout
timer
verbose
version

The following sections describe the utility commands accessed through "java -jar" <kvhome>/lib/sql.jar <command>".

The interactive prompt for the shell is:

sql-> 

The shell comprises a number of commands. All commands accept the following flags:

The shell commands have the following general format:

  1. All commands are structured like this:

    sql-> command [arguments] 
  2. All arguments are specified using flags that start with "-"

  3. Commands and subcommands are case-insensitive and match on partial strings(prefixes) if possible. The arguments, however, are case-sensitive.

connect

connect -host <hostname> -port <port> -name <storeName>
[-timeout <timeout ms>]
[-consistency <NONE_REQUIRED(default) |
                              ABSOLUTE | NONE_REQUIRED_NO_MASTER>]
[-durability <COMMIT_SYNC(default) |
                              COMMIT_NO_SYNC | COMMIT_WRITE_NO_SYNC>]
[-username <user>] [-security <security-file-path>]

Connects to a KVStore to perform data access functions. If the instance is secured, you may need to provide login credentials.

consistency

consistency [[NONE_REQUIRED | NONE_REQUIRED_NO_MASTER |
ABSOLUTE] [-time -permissible-lag <time_ms> -timeout <time_ms>]] 

Configures the read consistency used for this session.

describe

(describe | desc) as json 
	table <table_name> (<field_name> (,<field_name>)*)?
	| index <index_name> on <table_name> 

Provides a JSON description of a table or index.

durability

durability [[COMMIT_WRITE_NO_SYNC | COMMIT_SYNC |
COMMIT_NO_SYNC] | [-master-sync <sync-policy> -replica-sync <sync-policy>
-replica-ask <ack-policy>]] <sync-policy>: SYNC, NO_SYNC, WRITE_NO_SYNC
<ack-policy>: ALL, NONE, SIMPLE_MAJORITY

Configures the write durability used for this session.

exit

exit | quit 

Exits the interactive command shell.

help

help [command] 

Displays help message for all shell commands and sql command.

history

history [-last <n>] [-from <n>] [-to <n>] 

Displays command history. By default all history is displayed. Optional flags are used to choose ranges for display.

import

import -table <name> -file <name> [JSON | CSV] 

Imports records from the specified file into the named table. The records can be in either JSON or CSV format. If the format is not specified JSON is assumed.

Use -table to specify the name of a table to which the records are loaded. The alternative way to specify the table is to add the table specification "Table: <name>" before its records in the file.

For example, a file containing the records of 2 tables "users" and "email":

Table: users
<records of users>
...
Table: emails
<record of emails>
...          

load

load -file <path to file>

Load the named file and interpret its contents as a script of commands to be executed. If any command in the script fails execution will end.

For example, suppose the following commands are collected in the script file test.sql:

### Begin Script ###
load -file test.ddl
import -table users -file users.json
### End Script ###

Where the file test.ddl would contain content like this:

DROP TABLE IF EXISTS users;
CREATE TABLE users(id INTEGER, firstname STRING, lastname STRING,
age INTEGER, primary key (id)); 

And the file users.json would contain content like this:

{"id":1,"firstname":"Dean","lastname":"Morrison","age":51}
{"id":2,"firstname":"Idona","lastname":"Roman","age":36}
{"id":3,"firstname":"Bruno","lastname":"Nunez","age":49} 

Then, the script can be run by using the load command in the shell:

> java -jar KVHOME/lib/sql.jar -helper-hosts node01:5000 \
-store kvstore
sql-> load -file ./test.sql
Statement completed successfully.
Statement completed successfully.
Loaded 3 rows to users. 

mode

mode [COLUMN | LINE | JSON [-pretty] | CSV] 

Sets the output mode of query results. The default value is JSON.

For example, a table shown in COLUMN mode:

sql-> mode column;
sql-> SELECT * from users;
 +-----+-----------+-----------+-----+
 | id  | firstname | lastname  | age |
 +-----+-----------+-----------+-----+
 |   8 | Len       | Aguirre   |  42 |
 |  10 | Montana   | Maldonado |  40 |
 |  24 | Chandler  | Oneal     |  25 |
 |  30 | Pascale   | Mcdonald  |  35 |
 |  34 | Xanthus   | Jensen    |  55 |
 |  35 | Ursula    | Dudley    |  32 |
 |  39 | Alan      | Chang     |  40 |
 |   6 | Lionel    | Church    |  30 |
 |  25 | Alyssa    | Guerrero  |  43 |
 |  33 | Gannon    | Bray      |  24 |
 |  48 | Ramona    | Bass      |  43 |
 |  76 | Maxwell   | Mcleod    |  26 |
 |  82 | Regina    | Tillman   |  58 |
 |  96 | Iola      | Herring   |  31 |
 | 100 | Keane     | Sherman   |  23 |
 +-----+-----------+-----------+-----+
 ...

100 rows returned 

Empty strings are displayed as an empty cell.

sql-> mode column;
sql-> SELECT * from tab1 where id = 1;
 +----+------+----+------+
 | id |  s1  | s2 |  s3  |
 +----+------+----+------+
 |  1 | NULL |    | NULL |
 +----+------+----+------+

1 row returned

For nested tables, identation is used to indicate the nesting under column mode:

sql-> SELECT * from nested;
+----+-------+------------------------------------------------------------+
| id | name  |                           details                          |
+----+-------+------------------------------------------------------------+
|  1 | one   | address                                                    |
|    |       |     city    | Waitakere                                    |
|    |       |     country | French Guiana                                |
|    |       |     zipcode | 7229                                         |
|    |       | attributes                                                 |
|    |       |     color   | blue                                         |
|    |       |     price   | expensive                                    |
|    |       |     size    | large                                        |
|    |       | phone       | [(08)2435-0742, (09)8083-8862, (08)0742-2526]|
+----+-------+------------------------------------------------------------+
|  3 | three | address                                                    |
|    |       |     city    | Viddalba                                     |
|    |       |     country | Bhutan                                       |
|    |       |     zipcode | 280071                                       |
|    |       | attributes                                                 |
|    |       |     color   | blue                                         |
|    |       |     price   | cheap                                        |
|    |       |     size    | small                                        |
|    |       | phone       | [(08)5361-2051, (03)5502-9721, (09)7962-8693]|
+----+-------+------------------------------------------------------------+
... 

For example, a table shown in LINE mode, where the result is displayed vertically and one value is shown per line:

sql-> mode line;
sql-> SELECT * from users;

 > Row 1
 +-----------+-----------+
 | id        | 8         |
 | firstname | Len       |
 | lastname  | Aguirre   |
 | age       | 42        |
 +-----------+-----------+

  > Row 2
 +-----------+-----------+
 | id        | 10        |
 | firstname | Montana   |
 | lastname  | Maldonado |
 | age       | 40        |
 +-----------+-----------+

  > Row 3
 +-----------+-----------+
 | id        | 24        |
 | firstname | Chandler  |
 | lastname  | Oneal     |
 | age       | 25        |
 +-----------+-----------+
 ...
100 rows returned 

Just as in COLUMN mode, empty strings are displayed as an empty cell:

sql-> mode line;
sql-> SELECT * from tab1 where id = 1;

 > Row 1
 +---------+------+
 | id      | 1    |
 | s1      | NULL |
 | s2      |      |
 | s3      | NULL |
 +---------+------+

1 row returned

For example, a table shown in JSON mode:

sql-> mode json;
sql-> SELECT * from users;
{"id":8,"firstname":"Len","lastname":"Aguirre","age":42}
{"id":10,"firstname":"Montana","lastname":"Maldonado","age":40}
{"id":24,"firstname":"Chandler","lastname":"Oneal","age":25}
{"id":30,"firstname":"Pascale","lastname":"Mcdonald","age":35}
{"id":34,"firstname":"Xanthus","lastname":"Jensen","age":55}
{"id":35,"firstname":"Ursula","lastname":"Dudley","age":32}
{"id":39,"firstname":"Alan","lastname":"Chang","age":40}
{"id":6,"firstname":"Lionel","lastname":"Church","age":30}
{"id":25,"firstname":"Alyssa","lastname":"Guerrero","age":43}
{"id":33,"firstname":"Gannon","lastname":"Bray","age":24}
{"id":48,"firstname":"Ramona","lastname":"Bass","age":43}
{"id":76,"firstname":"Maxwell","lastname":"Mcleod","age":26}
{"id":82,"firstname":"Regina","lastname":"Tillman","age":58}
{"id":96,"firstname":"Iola","lastname":"Herring","age":31}
{"id":100,"firstname":"Keane","lastname":"Sherman","age":23}
{"id":3,"firstname":"Bruno","lastname":"Nunez","age":49}
{"id":14,"firstname":"Thomas","lastname":"Wallace","age":48}
{"id":41,"firstname":"Vivien","lastname":"Hahn","age":47}
...
100 rows returned 

Empty strings are displayed as "".

sql-> mode json;
sql-> SELECT * from tab1 where id = 1;
{"id":1,"s1":null,"s2":"","s3":"NULL"}

1 row returned

Finally, a table shown in CSV mode:

sql-> mode csv;
sql-> SELECT * from users;
8,Len,Aguirre,42
10,Montana,Maldonado,40
24,Chandler,Oneal,25
30,Pascale,Mcdonald,35
34,Xanthus,Jensen,55
35,Ursula,Dudley,32
39,Alan,Chang,40
6,Lionel,Church,30
25,Alyssa,Guerrero,43
33,Gannon,Bray,24
48,Ramona,Bass,43
76,Maxwell,Mcleod,26
82,Regina,Tillman,58
96,Iola,Herring,31
100,Keane,Sherman,23
3,Bruno,Nunez,49
14,Thomas,Wallace,48
41,Vivien,Hahn,47
...
100 rows returned 

Like in JSON mode, empty strings are displayed as "".

sql-> mode csv;
sql-> SELECT * from tab1 where id = 1;
1,NULL,"","NULL"

1 row returned 

Note

Only rows that contain simple type values can be displayed in CSV format. Nested values are not supported.

output

output [stdout | file]

Enables or disables output of query results to a file. If no argument is specified, it shows the current output.

page

page [on | <n> | off]

Turns query output paging on or off. If specified, n is used as the page height.

If n is 0, or "on" is specified, the default page height is used. Setting n to "off" turns paging off.

show faults

show faults [-last] [-command <index>]

Encapsulates commands that display the state of the store and its components.

show query

show query <statement> 

Displays the query plan for a query.

For example:

sql-> show query SELECT * from Users;
RECV([6], 0, 1, 2, 3, 4)
[
  DistributionKind : ALL_PARTITIONS,
  Number of Registers :7,
  Number of Iterators :12,
  SFW([6], 0, 1, 2, 3, 4)
  [
    FROM:
    BASE_TABLE([5], 0, 1, 2, 3, 4)
    [Users via primary index] as $$Users

    SELECT:
    *
  ]
] 

show tables

show [as json] tables | table <table_name>

Shows either all tables currently existing in the store, or the named table.

show users

show [as json] users | user <user_name>

Shows either all the users currently existing in the store, or the named user.

show roles

show [as json] roles | role <role_name>

Shows either all the roles currently defined for the store, or the named role.

timeout

timeout [<timeout_ms>]

Configures or displays the request timeout for this session. If not specified, it shows the current value of request timeout.

timer

timer [on | off]

Turns the measurement and display of execution time for commands on or off. If not specified, it shows the current state of timer. For example:

sql-> timer on
sql-> SELECT * from users where id <= 10 ;
 +----+-----------+-----------+-----+
 | id | firstname | lastname  | age |
 +----+-----------+-----------+-----+
 |  8 | Len       | Aguirre   |  42 |
 | 10 | Montana   | Maldonado |  40 |
 |  6 | Lionel    | Church    |  30 |
 |  3 | Bruno     | Nunez     |  49 |
 |  2 | Idona     | Roman     |  36 |
 |  4 | Cooper    | Morgan    |  39 |
 |  7 | Hanae     | Chapman   |  50 |
 |  9 | Julie     | Taylor    |  38 |
 |  1 | Dean      | Morrison  |  51 |
 |  5 | Troy      | Stuart    |  30 |
 +----+-----------+-----------+-----+

10 rows returned

Time: 0sec 98ms

verbose

verbose [on | off]

Toggles or sets the global verbosity setting. This property can also be set on a per-command basis using the -verbose flag.

version

version 

Display client version information.