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, isql for Sybase/SQL Server, 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/
It 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-<%VERSION%>clienttools-linux.run or SQLstream-client-tools-<%VERSION%>.-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 Double-click the ‘Run sqlline’ icon on the desktop (either platform). A terminal window appears, showing the following command being executed:
When that command completes, the terminal window shows the following:
Connecting to jdbc:sqlstream:sdp://bento;sessionName='sqllineClient@/dev/pts/1:demo@bento' log4j:INFO Using URL [jar:file:/home/demo/SQLstream-sServer-3.0.0/ lib/GroboUtils-5-core.jar!/log4j.properties] for automatic log4j configuration of repository named [default]. Connected to: SQLstream (version 3.0.0) Driver: SQLstreamJdbcDriver (version 3.0-distrib) Autocommit status: true Transaction isolation: TRANSACTION_REPEATABLE_READ sqlline version 1.0.8-eb by Marc Prud'hommeaux 0: jdbc:sqlstream:sdp://bento>
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
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.)
|!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.|
||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.|
|!reconnect||Reconnects to a server that was brought down while connected to SQLstream using SQLLine.|
||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.)|
||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.)|
The following alphabetic list provides brief explanations for all SQLLine commands. Some commands interrelate.
|!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|
Launching sqlline on Linux
###Launching sqlline on Linux If you are running .sql files, it will be easier if you run sqlline from the directory that contains the .sql files, as in the following:
There is also an icon that the installation process places on the desktop, labeled SQLLine. Double-clicking that icon launches a local SQLLine version, using a built-in launch command of the form /home/demo/SQLstream-2.5.0/bin/sqllineClient. Once you use a script to launch SQLLine, you can choose to connect to a local or remote database by using a !connect or !properties command, as described in Using SQLLine Commands.
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: - Use the !connect command with all parameters specified on the command line, as shown by the sample !connect command in the Commands Most Often Used section. - Use the !connect command with a properties file in which you have specified the needed parameters. - Use a !properties command with such a properties file.
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, of course, 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
can be referenced as simply “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,
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 _$SQLSTREAMHOME/examples/parallelPerformanceTest.
To run the script on a Linux machine, navigate to $SQLSTREAM_HOME/examples/parallelPerformanceTest and enter the following:
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
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