SQLLine is an open source utility modified by SQLstream to handle streaming data. SQLLine works similarly to other command-line database access utilities, such as sqlplus for Oracle, mysql for MySQL, and psql for PostgreSQL. SQLLine can also connect to other relational database drivers and execute SQL commands on those databases. More information about SQLLine can be found at http://sqlline.sourceforge.net/
This page contains the following sections:
SQLline is supplied either as part of the s-Server installation package or as part of the Client Tools download from the SQLstream website (via SQLstream-clienttools-linux.run or SQLstream-client-tools-.-windows.exe).
Using SQLstream’s SQLLine, you can do the following:
Three SQLstream scripts provide SQLLine functionality, and each one, once launched, can be used to connect to local or remote databases using a !properties command.
Script name & use | Capabilities | Resides |
---|---|---|
sqllineClient(local) | Automatically connects the user to a local SQLstream s-Server. You can then connect to other databases using a !connect command. | On the SQLstream server, in the SQLSTREAM_HOME/bin directory |
sqllineRemote(local) | Requires additional user-supplied instructions to connect to either SQLstream or another local or remote database. You can then connect to other databases using a !connect command. | On the SQLstream server, in the SQLSTREAM_HOME/bin directory |
sqlline(local or remote) | Can connect from a remote server to databases either on a SQLstream s-Server or on other remote servers | On a remote server in the ClientTools/bin directory. |
To connect to the local SQLstream s-Server using sqllineClient (on the same machine containing the script), use one of the following two methods:
Go to the SQLSTREAM_HOME/bin directory and use the following command:
sqllineClient (Linux)
sqlline.cmd (Windows)
or
If you have installed on Linux as root, double-click the ‘Run sqlline’ icon on the desktop. A terminal window appears, showing the following command being executed:
jdbc:sqlstream:sdp:;sessionName='sqllineClient@/dev/pts/1:demo@bento'
When that command completes, the terminal window shows the following:
Connecting to jdbc:sqlstream:sdp://my-server;sessionName='sqllineClient@/dev/pts/3:drew@drew-VirtualBox'
Connected to: SQLstream (<VERSION>)
Driver: SQLstreamJdbcDriver (VERSION-distrib)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.0.13-mb by Marc Prud'hommeaux
0: jdbc:sqlstream:sdp://my-server>
Once you have started SQLLine using one of the scripts, you can connect to database servers by using a command (!connect or !properties or by supplying the connection parameters on the use sqllineRemote as shown in the next section.
Default connection values can be set for all the tools in the clienttools/default.conn.properties file. The properties are specified there as DRIVER, SERVER, NAME, and PASSWORD.
Each of those default values can be overwritten by passing a command-line argument to the script as follows:
Property | Switch | Argument | Example |
---|---|---|---|
SERVER | -u | URL | -u jdbc:sqlstream:sdp://example.com:5571 |
NAME | -n | User name | n sqlstream |
Once connected to SQLstream or a database, you can use SQLLine in either of the following ways:
Begin using SQL commands against the current connection
or
Enter SQLLine commands.
All SQLLine commands start with an exclamation point (!).
You can get a list of all SQLLine commands by typing !help , or you can get specific help information on a known command by typing
!help <command name>
You also get specific help information when you enter a command without the parameters it requires.
For example, when you are running SQLline client, if you type !connect without supplying the required parameters, the response is as follows: ​
0: jdbc:sqlstream:sdp:> !connect
Usage: connect <url> <username> <password> [driver]
To minimize typing, SQLLine will complete a partial command you’ve typed if you then press the tab key.
You can reissue commands you have already entered by pressing the Up arrow or Down arrow keys until you see the one you want. You can then edit that command, or simply execute it by pressing Enter. (See also the !history command described below.)
Command Name | Explanation |
---|---|
!run <filename> | Executes the specified commands and SQL in a file. Ignores lines beginning with #. Aborts if errors occur, unless “force” is set to true. (See the configuration preferences settings section.) |
!history | Lists commands recently used, which are saved in a file. In Windows, the file is in HOME/sqlline/history. On all other platforms, the file is in HOME/.sqlline/history. |
!quit | Exits SQLLineClient |
!reconnect | Reconnects to a server that was brought down while connected to s-Server using SQLLine. |
!record <filename> | Saves all session output to the named file. To turn this off, use !record alone, with no filename. |
!save | Saves configuration preferences settings(See the configuration preferences settings section.) |
!set <command> | Sets a configuration/preference setting.(See the configuration preferences settings section.) |
!tables | Lists all the tables in the database of the current connection. (See also !describe.) |
For a complete list of SQLline commands, see Complete SQLLine Command Set below.
SQLstream has found the following settings to be best practices:
Command Name | Explanation |
---|---|
!set color | true or false |
!set incremental (Modified by SQLstream to enable working with streaming data) | true or false. Defaults to false. For streaming data, this setting must be set to true, because the entire result set is never really fetched. With incremental set to true, result rows are displayed as soon as they are fetched. |
!set force | true or false; if false, any subsequent execution error causes all subsequent commands to abort. |
!set outputformat | Sets the output format for displaying results, to one of the following terms:table, vertical, csv, tsv, xmlattrs, xmlelements |
!set showheader | Displays the names of the columns when displaying results. When this is set to false, the headers are only displayed at the beginning and not repeated. This is convenient when you are using !record to save the file. |
Once you launch SQLline client using one of the scripts, you can optionally connect to SQLstream (already done if you used sqllineClient script) or another database. To do this, you can use any one of the following three methods:
You can connect to SQLstream if you use the command !connect myserver after you create a file named “myserver”, containing the following lines specifying server connection properties:
url=jdbc:sqlstream:sdp;sessionName=sqllineClient:sqlstreamuser@localhost
driver=com.sqlstream.jdbc.Driver
user=myusername
password=mypassword
You can also use a !properties mydatabase command to connect to a database after you create a file named “mydatabase”, containing the needed database connection properties. That file’s contents would look similar to the following lines specifying those properties:
url=jdbc:mysql://sevilla:3306/
driver=com.mysql.jdbc.Driver
user=sqlstreamusername
password=s-serverbpwd
using the database-user-specific password.
Scripts with extension .sql are used in a variety of places in a SQLstream system. You can find many examples in the demo subdirectories of the SQLSTREAM_HOME/demo directory.
Support sql scripts residing in the SQLSTREAM_HOME/support/sql directory enable you to query your database schemas and configurations.
You can use the !run command to execute all such scripts, as for example:
!run <support script name>.sql
Most script names describe what they do. For example, the support scripts include the following
You can run SQLline as a client to any database server that supports JDBC (or to a local database). In other words, the SQLLine scripts enable command-line connection to a relational database to execute SQL commands.
This section illustrates how to establish such connections, using sqllineRemote as the example script. (The example assumes you have navigated to the directory $SQLSTREAM_HOME/bin, where this script resides.)
SqllineRemote uses Aspen runtime Jars for access to drivers.
On Linux, you can pass one or more connection-properties files to connect to the remote server(s):
./sqllineRemote file1 file2 ...
As a convenience, a connection-properties file names of the form
myserver.conn.properties
can be referenced as simply “myserver”:
./sqllineRemote myserver
To find files like myserver.conn.properties, sqllineRemote must execute in the directory in which it was started. Only then can !run find the properties file in that directory.
Create a <database>.conn.properties file with the following entries (supply your own password):
url=jdbc:<database>://myhostname
driver=org.<database>.Driver
user=<database>
password=
To connect to a particular database, append it to the URL, for example, jdbc:://myhostname/farrago. Then run
./sqllineRemote <database>
Test it by using sqllineRemote for table access.
"................>" ?
The “greater than” sign (>) is a continuation prompt, enabling you to continue the statement you just entered without providing a final semicolon.
To continue and complete that statement, type the rest of the statement after that prompt, and then press Enter. To cancel the statement instead, type ; (semicolon) and press Enter to get the usual prompt back.
You may have the incremental parameter set to false, the default.
This parameter needs to be set to true:
!set incremental true;
You can view s-Server’s massive raw performance by using a script that generates data using a SQL “VALUES” clause. (The rate at which s-Server ingests data is generally much slower than the rate at which it processes data.) s-Server ships with an example script in $SQLSTREAM_HOME/examples/parallelPerformanceTest.
To run the script on a Linux machine, navigate to $SQLSTREAM_HOME/examples/parallelPerformanceTest and enter the following:
genParallelPerformanceTest.py %N
where “%N” is the number of pipelines you want to run. This number should correspond with the number of cores on your server. For example, to run two pipelines, you would enter
./genParallelPerformanceTest.py 2
When you run genParallelPerformanceTest.py, it generates the following four SQL scripts in your current directory:
In these scripts, N pipelines are used to count rows, grouped by an integer column. Each pipeline aggregates its input, outputting every millisecond. A final query then sums those together, outputting every minute.
You can invoke these scripts by opening SQLLine and entering
sqlline --run=<script name>
You should run these scripts in order.
For example, if you ran the script with two pipelines, you would navigate to $SQLSTREAM_HOME/bin, open SQLLine, and enter the following lines, one at a time:
!run ../examples/parallelPerformanceTest/setup2pipelines.sql
!run ../examples/parallelPerformanceTest/startpumps2pipelines.sql
!run ../examples/parallelPerformanceTest/listen2pipelines.sql
When you run listen2pipelines.sql, you will see something like the following:
Each line represents the number of rows per half minute. To stop the pumps, enter the following
!run ../examples/parallelPerformanceTest/stoppumps2pipelines.sql
The following alphabetic list provides brief explanations for all SQLLine commands. Some commands interrelate.
Command Name | Explanation |
---|---|
!all | Executes the specified SQL against all the current connections. |
!autocommit | Sets autocommit mode on or off (See !commit and !rollback.) Note: autocommit is set to on by default. This differs from the SQLstream JDBC driver. See the topic the SQLstream JDBC driver in the Integration Guide for more details. |
!batch | Starts or execute a batch of statements |
!brief | Sets verbose mode off |
!call | Executes a callable statement |
!close | Closes the current connection to the database |
!closeall | Closes all currently open connections |
!columns | Lists all columns in the specified table |
!commit | Commits the current transaction (if !autocommit is off) |
!connect | Opens a new connection to the database. See the example command above and the description of properties files. |
!dbinfo | Gives metadata information about the database |
!describe | Describes a table |
!dropall | Drops all tables in the current database |
!exportedkeys | Lists all the exported keys for the specified table |
!go | Selects the current connection |
!help | Prints a summary of command usage |
!history | Displays the command history |
!importedkeys | Lists all the imported keys for the specified table |
!indexes | List all the indexes for the specified table |
!isolation | Sets the transaction isolation for this connection |
!list | Lists the current connections |
!manual | Displays the SQLLine manual |
!metadata | Obtains metadata information |
!nativesql | Shows the native SQL for the specified statement |
!outputformat | Sets the output format for displaying results to one of the following:table, vertical, csv, tsv, xmlattrs, xmlelements |
!primarykeys | Lists all the primary keys for the specified table |
!procedures | Lists all the procedures |
!properties | Connects to the database specified in the properties file(s) |
!quit | Exits the program |
!reconnect | Reconnects to the database |
!record | Records all output to the specified file |
!rehash | Fetches table and column names for command completion |
!rollback | Rolls back the current transaction (if !autocommit is off) |
!run | Runs a script from the specified file. (See the section on Running Scripts. |
!save | Saves the current variabes and aliases |
!scan | Scans for installed JDBC drivers |
!script | Starts saving a script to a file |
!set | Sets a SQLline variable |
!sql | Executes a SQL command |
!tables | Lists all the tables in the database |
!typeinfo | Displays the type map for the current connection |
!verbose | Sets verbose mode on Running Support Scripts |