Running the SQL Shell

You can run the SQL shell interactively or use it to run single commands. Here is the general usage to start the shell:

java -jar KVHOME/lib/sql.jar
       -helper-hosts <host:port[,host:port]*> -store <storeName>
       [-username <user>] [-security <security-file-path>]
       [-timeout <timeout ms>]
       [-consistency <NONE_REQUIRED(default) |
                              ABSOLUTE | NONE_REQUIRED_NO_MASTER>]
       [-durability <COMMIT_SYNC(default) |
                              COMMIT_NO_SYNC | COMMIT_WRITE_NO_SYNC>]
       [single command and arguments] 

The following are the mandatory parameters:

-helper-hosts: Specifies a comma-separated list of hosts and ports.

-store: Specifies the name of the store.

-security: Specifies the path to the security file in a secure deployment of the store.

For example: $KVROOT/security/user.security

The store supports the following optional parameters:

-consistency: Configures the read consistency used for this session. The read operations are serviced either on a master or a replica node depending on the configured value. For more details on consistency, see Consistency Guarantees. The following policies are supported. They are defined in the Consistency class of Java APIs.

If you do not specify this value, the default value ABSOLUTE is applied for this session.

• ABSOLUTE - The read operation is serviced on a master node. With ABSOLUTE consistency, you are guaranteed to obtain the latest updated data.
• NONE-REQUIRED - The read operation can be serviced on a replica node. This implies, that if the data is read from the replica node, it may not match what is on the master. However, eventually, it will be consistent with the master.

For more details on the policies, see Consistency in the Java Direct Driver API Reference Guide.

-durability: Configures the write durability setting used in this session. This value defines the durability policies to be applied for achieving master commit synchronization, that is, the actions performed by the master node to return with a normal status from the write operations. For more details on durability, see Durability Guarantees.

If you do not specify this value, the default value COMMIT_SYNC is applied for this session.

• COMMIT_NO_SYNC - The data is written to the host's in-memory cache, but the master node does not wait for the data to be written to the file system's data buffers or subsequent physical storage.
• COMMIT_SYNC - The data is written to the in-memory cache, transferred to the file system's data buffers, and then synchronized to a stable storage before the write operation completes normally.
• COMMIT_WRITE_NO_SYNC - The data is written to the in-memory cache, and transferred to the file system's data buffers, but not necessarily into physical storage.

For more details on the policies, see Durability in the Java Direct Driver API Reference Guide.

-timeout: Configures the request timeout used for this session. The default value is 5000ms.

-username: Specifies the username to log in as.

For example, you can start the shell like this:

java -jar KVHOME/lib/sql.jar
-helper-hosts node01:5000 -store kvstore
sql->

This command assumes that a store kvstore is running at port 5000. After the SQL starts successfully, you execute queries. In the next part of this document, you will find an introduction to SQL for Oracle NoSQL Database and how to create query statements.

If you want to import records from a file in either JSON or CSV format, you can use the import command. For more information see import.

If you want to run a script, use the load command. For more information see load.

sql-> command [arguments]

-single command and arguments: Specifies the utility commands that can be accessed from the SQL shell. You can use them with the syntax shown above.

For a complete list of utility commands accessed through "java -jar" <kvhome>/lib/sql.jar <command> see Shell Utility Commands.