Archive
Archive
voltsql — An interactive command interface for issuing SQL queries to a running VoltDB database
voltsql [args...]
The voltsql utility is an experimental interface provided for customer evaluation. We encourage users to try it and send us feedback so we can enhance and refine the feature going forward.
There are currently two known constraints to using voltsql: it requires additional Python libraries not currently installed by default on all operating systems and it cannot be used in non-interactive scripts.
The voltsql command is similar to sqlcmd in that it lets you query a VoltDB database. The difference is that voltsql is an interactive interface that includes command completion and context sensitive assistance. As you type SQL commands, voltsql suggests possible keywords that match your typing. When a statement calls for keywords or schema elements, such as tables or column names, voltsql provides a list of valid options.
The voltsql command requires Python 2.6 or later. (It does not support Python 3.) It also requires additional Python
libraries, which are listed in the file lib/python/voltsql/requirements.txt where VoltDB is installed.
To install the required libraries, use the following steps:
Install pip.
Optionally, install and activate virtualenv.
Open a terminal session and install all project dependencies. When installing system-wide, use the
sudo command. For example, if VoltDB is installed in the voltdb folder in your
home directory:
$ sudo pip install -r ~/voltdb/lib/python/voltsql/requirements.txt
Note that on Macintosh OS X, an earlier version of the click library is pre-installed and cannot be updated system-wide without deleting the current version first. You can use sudo to delete the current version before installing the update, or avoid the issue by installing in a private environment using virtualenv.
Once installed, invoke the voltsql command like any other VoltDB command. The main use of the utility is to assist you in entering SQL statements interactively by completing keywords, clauses, and the names of schema objects while you type. There are three options — which are displayed at the bottom of the command window — that affect how voltsql responds to your typing. These options can be toggled on and off using the specified function keys.
When on, smart completion lists schema elements such as table and column names where appropriate along with SQL keywords. Press F2 to turn smart completion on and off.
When on, auto refresh refreshes the schema after each statement is processed. When off, you must refresh the schema manually with the UPDATE directive if the schema changes. Leaving auto refresh on ensures you have the latest schema available for smart completion. Turning it off can save time if the schema does not change very often. Press F3 to turn auto refresh on and off.
When on, multiline input lets you enter statements on multiples lines before processing the statement or command. Press ESC and then ENTER to execute the statement. When off, each line is processed when you press ENTER. Press F4 to turn multiline input on and off.
In addition you can use the same directives recognized by sqlcmd, to perform the following actions:
Describe the database contents (SHOW, LIST, DESCRIBE and EXPLAIN)
Manage stored procedure and user-defined function classes (LOAD and REMOVE CLASSES)
Add, modify, and delete schema objects (DDL such as CREATE, ALTER and DROP)
Query and modify the database contents (SQL statements such as SELECT, UPDATE, and DELETE)
Displays help text explaining how to use the voltsql interface.
Specifies the network address of one or more nodes in the database cluster. By default, voltsql attempts to connect to a database on localhost.
Specifies the port number to use when connecting to the database servers. All servers must be using the same port number. By default, voltsql connects to the standard client port (21212).
Specifies the username to use for authenticating to the database. The username is required if the database has security enabled.
Specifies the password to use when connecting to the database. You must specify a username and password if security is enabled for the database. If you specify a username with the --user argument but not the --password argument, VoltDB prompts for the password.
Specifies a file that lists the username and password of the account to use when connecting to a database with
security enabled. This is useful when writing shell scripts because it avoids having to hardcode the password as plain
text in the script. The credentials file is interpreted as a Java properties file defining the properties
username and password. For example:
username: johndoe password: 4tUn8
Because it is a Java properties file, you must escape certain special characters in the username or password, including the colon or equals sign.
Specifies the use of kerberos authentication when connecting to the database server(s). The service name identifies the Kerberos client service module, as defined by the JAAS login configuration file.
Specifies the format of the output of query results. Output can be formatted as comma-separated values (csv), fixed monospaced text (fixed), or tab-separated text fields (tab). By default, the output is in fixed monospaced text.
Specifies that the column headings and other metadata associated with query results are not displayed. By
default, the output includes such metadata. However, you can use this argument, along with the
--output-format argument, to write just the data itself to an output file.
Specifies a time limit for read-only queries. Any read-only queries that exceed the time limit are canceled and control returned to the user. Specify the time out as an integer number of milliseconds. The default timeout is set in the cluster configuration (or set to 10 seconds by default). Only users with ADMIN privileges can set a voltsql timeout longer than the cluster-wide setting.
Specifies the use of TLS encryption when communicating with the server. Only necessary if the cluster is configured to use TLS encryption for the external ports. See Section D, “Using CLI Commands with TLS/SSL” for more information.
V9.x Documentation