Appendix B. CLI Command Reference

This appendix describes the following commands:

The Command Line Interface (CLI) is run interactively or used to run single commands. The general usage to start the CLI is:

java -Xmx256m -Xms256m \
-jar KVHOME/lib/kvstore.jar runadmin \
-host <hostname> -port <port> [single command and arguments] 
-security KVROOT/security/client.security

If you want to run a script file, you can use the "load" command on the command line:

java -Xmx256m -Xms256m \
-jar KVHOME/lib/kvstore.jar runadmin -host <hostname> -port <port> \
-security \
KVROOT/securtiy/client.security \
load -file <path-to-script>

If none of the optional arguments are passed, it starts interactively. If additional arguments are passed they are interpreted as a single command to run, then return. The interactive prompt for the CLI is:

"kv-> "

Upon successful completion of the command, the CLI's process exit code is zero. If there is an error, the exit code will be non-zero.

The CLI comprises a number of commands, some of which have subcommands. Complex commands are grouped by general function, such as "show" for displaying information or "ddl" for manipulating schema. All commands accept the following flags:

CLI commands have the following general format:

  1. All commands are structured like this:

    "kv-> command [sub-command] [arguments] 
  2. All arguments are specified using flags which start with "-"

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

Inside a CLI script file, you can use # to designate a comment. Also, you can terminate a line with a backslash \ to continue a command onto the next line.

aggregate

Performs simple data aggregation operations on numeric fields like count, sum, average, keys, start and end. The aggregate command iterates matching keys or rows in the store so, depending on the size of the specified key or row, it may take a very long time to complete.

The aggregate subcommands are:

aggregate kv

aggregate kv [-count] [-sum <field[,field,..]>] [-avg <field[,field,..]>]
              [-key <key>] [-schema <name>]
              [-start <prefixString>] [-end <prefixString>] 

Performs simple data aggregation operations using the specified key.

where:

  • -count

    Returns the count of matching records.

  • -sum

    Returns the sum of the values of matching fields. All records with a specified schema with the named field are matched. Unmatched records are ignored.

  • -avg

    Returns the average of the values of matching fields. All records with a specified schema with the named field are matched. Unmatched records are ignored.

  • -key

    Specifies the key (prefix) to use.

  • -schema

    Specifies the Avro schema to use.

  • -start and -end flags

    Restricts the range used for iteration. This is particularly helpful when getting a range of records based on a key component, such as a well-formatted string. -start and -end arguments are inclusive.

For example, a simple count of all records in the store:

kv-> aggregate kv -count
count: 33508 

Sum and average operate on specific field names in matching records which means that only Avro records containing the named fields are used. Sum and average only operate on numeric fields of Avro types INT, LONG, FLOAT, and DOUBLE.

For example, with the following schema:

{
   "type" : "record",
   "name" : "Cookie",
   "fields" : [ {
   "name" : "id",
   "type" : "string",
   "default" : ""
   }, {
     "name" : "frequency",
     "type" : "int",
     "default" : 0
   }, {
     "name" : "lastVisit",
     "type" : "string",
     "default" : ""
   }, {
     "name" : "segments",
     "type" : {
        "type" : "array",
     "items" : "string"
     },
     "default" : [ ]
   } ]
} 

An example of sum on a field named frequency:

kv-> aggregate kv -sum frequency -key /visits/charitable_donors/date
sum(frequency): 2068 

An example of average on a field named frequency:

kv -> aggregate kv -avg frequency -key /visits/charitable_donors/date
avg(frequency): 2.494571773220748 

aggregate table

aggregate table -name <name>
    [-count] [-sum <field[,field,..]>]
    [-avg <field[,field,..]>]
    [-index <name>]
    [-field <name> -value <value>]*
    [-field <name> [-start <value>] [-end <value>]]
    [-json <string>] 

Performs simple data aggregation operations on numeric fields of the table.

where:

  • -name

    Specifies the table for the operation.

  • -count

    Returns the count of matching records.

  • -sum

    Returns the sum of the values of matching fields.

  • -avg

    Returns the average of the values of matching fields.

  • -index

    Specifies the name of the index to use. When an index is used, the fields named must belong to the specified index and the aggregation is performed over rows with matching index entries.

  • -field and -value pairs are used to specify the field values of the primary key to use to match for the aggregation, or you can use an empty key to match the entire table.

  • The -field flat, along with its -start and -end flags, can be used for restricting the range used to match rows.

  • -json

    Specifies the fields and values to use for the aggregation as a JSON input string.

See the example below:

# Create a table 'user_test' with an index on user_test(age):
kv-> execute 'CREATE TABLE user_test (id INTEGER,
firstName STRING, lastName STRING, age INTEGER, PRIMARY KEY (id))'
Statement completed successfully

kv-> execute 'CREATE INDEX idx1 on user_test (age)'
Statement completed successfully

# Insert 3 rows:
kv-> put table -name user_test -json
'{"id":1,"firstName":"joe","lastName":"wang","age":21}'
Operation successful, row inserted.
kv-> put table -name user_test -json
'{"id":2,"firstName":"jack","lastName":"zhao","age":32}'
Operation successful, row inserted.
kv-> put table -name user_test -json
'{"id":3,"firstName":"john","lastName":"gu","age":43}'
Operation successful, row inserted.

# Get count(*), sum(age) and avg(age) of rows in table:
kv-> aggregate table -name user_test -count -sum age -avg age
Row count: 3
Sum:
        age(3 values): 96
Average:
        age(3 values): 32.00

# Get count(*), sum(age) and avg(age) of rows where
age >= 30, idx1 is utilized to filter the rows:
kv-> aggregate table -name user_test -count -sum age
-avg age -index idx1 -field age -start 30
Row count: 2
Sum:
        age(2 values): 75
Average:
        age(2 values): 37.50