VoltDB SQL Command-Line Tool

written by vdbdev on October 9, 2013 with no comments

sqlcmd is a SQL shell interpreter that allows you to execute VoltDB SQL and
Stored Procedures interactively as well as non-interactively, via scripts.

The following examples show how to execute SQL and Stored Procedures against
the VoltDB Voter example. To run these examples, run the examples/voter
application, first starting the server (starting a single node server is fine) and
then run the client to populate the server with data.

Executing SQL Interactively

The voter application captures votes for game show contestants. At the end of the
voting period, you may want to issue a query to find out who the winning contestant
was. Using sqlcmd, you can execute the following SQL interactively:

$ sqlcmd
SQL Command :: localhost:21212
1> SELECT a.contestant_name AS contestant_name
2>         , a.contestant_number AS contestant_number
3>         , SUM(b.num_votes)    AS total_votes
4>      FROM v_votes_by_contestant_number_state AS b
5>         , contestants AS a
6>     WHERE a.contestant_number = b.contestant_number
7>  GROUP BY a.contestant_name
8>         , a.contestant_number
9>  ORDER BY total_votes DESC
10>         , contestant_number ASC
11>         , contestant_name ASC;
CONTESTANT_NAME  CONTESTANT_NUMBER  TOTAL_VOTES 
---------------- ------------------ ------------
Edwina Burnam                     1      1456963
Jessie Eichman                    6      1151339
Alana Bregman                     5       825156
Jessie Alloway                    4       733010
Kelly Clauss                      3       685517
Tabatha Gehling                   2       678146




(6 row(s) affected)

The winner was, as usual with the Voter sample, Edwina Burnam, with 1456963 votes!

Executing VoltDB Stored Procedures Interactively

Both user created and system stored procedures can be invoked interactively using sqlcmd.

Invoking User-defined Stored Procedures

The voter application Vote stored procedure takes 3 parameters, the phone number,
the contestent number and the max votes per phone number. To invoke this stored
procedure interactively you would issue the following:

$ sqlcmd
1> exec Vote 9031368788 4 4;


(0 row(s) affected)

Invoking VoltDB System Procedures

You can use sqlcmd to execute VoltDB System Procedures. For example, to update your
application catalog (make schema changes) to a running VoltDB database, you can invoke
the @UpdateApplicationCatalog system procedure as follows:

$ sqlcmd 
1> exec @UpdateApplicationCatalog voter.jar deployment.xml;
STATUS
-------
      0    


(1 row(s) affected)

Another useful piece of information is statistics on the execution time of
your application’s stored procedures. You can retrieve this information using
the @Statistics system procedure using the PROCEDUREPROFILE selector as follows:

$ sqlcmd 
1> exec @Statistics PROCEDUREPROFILE 0;
TIMESTAMP      PROCEDURE                    WEIGHTED_PERC  INVOCATIONS  AVG       MIN       MAX       ABORTS  FAILURES 
-------------- ---------------------------- -------------- ------------ --------- --------- --------- ------- ---------
 1379084672791 voter.procedures.Vote                    99      5586292     31556      7000  11215000       0         0
 1379084672791 voter.procedures.Initialize               0            1  92012000  92012000  92012000       0         0
 1379084672791 voter.procedures.Results                  0            1   2571000   2571000   2571000       0         0



(3 row(s) affected)

Executing SQL scripts

VoltDB SQL and Stored Procedures can be invoked from the command line non-interactively,
meaning by passing SQL commands to sqlcmd via script.

$ cat script.sql
SELECT a.contestant_name AS contestant_name
     , a.contestant_number AS contestant_number
     , SUM(b.num_votes)    AS total_votes
  FROM v_votes_by_contestant_number_state AS b
     , contestants AS a
WHERE a.contestant_number = b.contestant_number
GROUP BY a.contestant_name
     , a.contestant_number
ORDER BY total_votes DESC
     , contestant_number ASC
     , contestant_name ASC; 
$  sqlcmd --output-format=csv < script.sql
SELECT a.contestant_name AS contestant_name
         , a.contestant_number AS contestant_number
         , SUM(b.num_votes)    AS total_votes
      FROM v_votes_by_contestant_number_state AS b
         , contestants AS a
     WHERE a.contestant_number = b.contestant_number
GROUP BY a.contestant_name
         , a.contestant_number
ORDER BY total_votes DESC
         , contestant_number ASC
         , contestant_name ASC;
CONTESTANT_NAME,CONTESTANT_NUMBER,TOTAL_VOTES
Edwina Burnam,1,1456963
Jessie Eichman,6,1151339
Alana Bregman,5,825156
Jessie Alloway,4,733010
Kelly Clauss,3,685517
Tabatha Gehling,2,678146

In this example, our SQL to retrieve the winner of the voting contest was placed
in a file, script.sql, and fed to sqlcmd. The output format was changed to csv,
comma-separated value output. Note that you can also include stored procedure
commands within SQL input files as well.

For more information on the sqlcmd utility, see the online CLI sqlcmd documentation.