Using SQLLine

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:

  • Connect to local and remote instantiations of s-Server
  • Execute SQL commands
  • Define and create streams
  • Run streaming queries

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.

Connecting to SQLstream with sqllineClient

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.

Command-Line Arguments

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

SQLLine Functionality

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.

Using 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.)

Frequently-Used Commands

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.

SQLLine Configuration Preference Settings

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.

Using Properties Files

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:

Using a Properties File with the !connect Command

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

Using a Properties File with the !properties Command

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

  • showFennelConfig.sql
  • showFarragoConfig.sql
  • showForeignServers.sql
  • showForeignStreams.sql
  • showProcedureColumns.sql
  • showProcedures.sql
  • showSessions.sql
  • showStatements.sql
  • showSystemInfo.sql

Connecting to Databases Using SQLLine Scripts

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.

  1. !tables
  2. Select * from sales.emps
  3. Insert a row into sales.emps or whatever tables you know are there.

Errors and Troubleshooting

Why am I getting the following prompt?

"................>" ?

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.

Why am I not getting results when I try to view a streaming table?

You may have the incremental parameter set to false, the default.

This parameter needs to be set to true:

!set incremental true;

Viewing s-Server Performance in SQLLine

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:

  • setup%Npipelines.sql, which creates all streams and pumps.
  • startpumps%Npipelines.sql, a SQL script to start data flowing.
  • listen%Npipelines.sql, a SQL script to display totals every minute.
  • stoppumps%Npipelines.sql, a SQL script to stop data from flowing.

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

Complete SQLLine Command Set

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