SQLShell: A Cross-Database SQL Tool With NoSQL Potential
In this blog post I will introduce SQLShell and demonstrate, step-by-step, how to install it and start using it with MySQL. I will also reflect on the possibilites of using this with NoSQL technologies, such as HBase, MongoDB, Hive, CouchDB, Redis and Google BigQuery.
SQLShell is a cross-platform, cross-database command-line tool for SQL, much like psql for PostgreSQL or the mysql command-line tool for MySQL.
Why Use It?
If you already use only one command-line tool, such as PostgreSQL’s psql or MySQL’s mysql tool and are happy then you may not need to. If you find yourself jumping between several of these tools and would like common functionality, or you are a fan of the many NoSQL technologies out there, then this is worth keeping an eye on.
JDBC Driver Support
SQLShell is built in Scala. Scala is a scalable programming language that compiles to Java byte code, can run on the JVM and can be used alongside Java code. Therefore, SQLShell can interface to any database for which there is a JDBC (Java Database Connectivity) driver. Encase you do not know, there are many.
JDBC Drivers Available
There are several JDBC drivers for many NoSQL technologies, but none of these implement JDBC fully enough to SQLShell, yet.
Something to note about Google’s recent BigQuery (part of Google Storage) is that, while they do not have a JDBC driver, yet, they do internally make use of sqlcmd with BigQuery. Sqlcmd is also created by Brian M. Clapper at Clapper.org, but he has discontinued development of it in favor of the new SQLShell. Therefore, I think there is a good chance that Google may soon develop a JDBC driver compatible with SQLShell. That said, Google does like Python and sqlcmd is written in Python.
Brian M. Clapper, author of SQLShell, writes about the move from Python to Scala in the SQLShell user-guide.
SQLShell is a SQL command line tool, similar in concept to tools like Oracle’s SQL Plus, the PostgreSQL psql command, and the MySQL mysql tool.
SQLShell is a Scala rewrite of my Python sqlcmd tool (rewritten because, as it turns out, I think JDBC is more consistent and portable than Python’s DB API).
You can download a pre-compiled installation JAR file for SQLShell or compile the binary yourself. Downloading the JAR is recommended and I have download version 0.7.1
curl -O http://cloud.github.com/downloads/bmc/sqlshell/sqlshell-0.7.1-install.jar
Running this installer can be done with the java command, which will launch a graphical installer. The graphical installer uses IzPack, which is a cross-platform installation framework. Therefore, even though I am using Mac OS X, you should not have any problems installing on Windows or Linux.
java -jar sqlshell-0.7.1-install.jar
First thing you see if all goes well is the language prompt. I am speak the English.
Following this you will see a welcome screen, then a intro and info page, and then the license page. Be sure to read the license carefully – especially the part about “your first born child”.
Here is what you will see for the intro screen.
When you get to the end, you will see the following message…
You’ve successfully installed SQLShell.
For your convenience, a command-line wrapper script has been provided in
the “bin” directory under “/Applications/clapper.org/sqlshell”.
That tells you where sqlshell is installed. Under this directory is the bin directory, which contains sqlshell. It’s a command-line tool, and since we will be using it often, I’ll add the path of that bin directory to my PATH.
echo 'export PATH=$PATH:/Applications/clapper.org/sqlshell/bin' >> ~/.profile source ~/.profile which sqlshell || echo "Not found in path" # This tests that it's found in your PATH
We can see what parameters it expects, by running sqlshell with the –help argument.
sqlshell --help SQLShell, version 0.7.1 (2010/11/10 17:27:55) Usage: sqlshell [OPTIONS] db [@file] OPTIONS -? -h --help Show this usage message. -V --version Show version and exit. -c config_file --config config_file Specify configuration file. Defaults to: /Users/phil/.sqlshell/config -n --no-ansi --noansi Disable the use of ANSI terminal sequences. This option just sets the initial value for this setting. The value can be changed later from within SQLShell itself. -r lib_name --readline lib_name Specify readline libraries to use. Legal values: editline, getline, gnu, jline, simple. (May be specified multiple times.) -s --stack Show all exception stack traces. -v --verbose Enable various verbose messages. This option just sets the initial verbosity value. The value can be changed later from within SQLShell itself. PARAMETERS db Name of database to which to connect, or an on-the-fly database specification, of the form: driver,url,[user[,password]] If the name of a database is specified, SQLShellwill look in the configuration file for the corresponding connection parameters. If a database specification is used, the specification must one argument. The driver can be a full driver class name, or a driver alias from the configuration file. The user and password are optional, since some databases (like SQLite) don't require them at all. @file Path of file of commands to run
SQLShell With MySQL
Install MySQL’s JDBC Driver
Download the driver from MySQL’s website.
curl -O <a href="http://www.mysql.com/downloads/connector/j/">http://mysql.mirror.iweb.ca/Downloads/Connector-J/mysql-connector-java-5.1.14.tar.gz</a> tar xvzf mysql-connector-java-5.1.14.tar.gz cd mysql-connector-java-5.1.14 cp mysql-connector-java-5.1.14-bin.jar /Applications/clapper.org/sqlshell/lib/
Above, we copied the mysql-connector-java-5.1.14-bin.jar file to SQLShell’s lib. SQLShell will load all the JAR files found in that directory at start-up, and will then reference them by package and class name. We will use the class name of the MySQL driver to configure a “mysql” alias in the SQLShell configuration.
To configure the “mysql” alias we will edit the default configuration file ~/.sqlshell/config, which currently does not exist.
mkdir ~/.sqlshell vim ~/.sqlshell/config # I use vim to edit files
Add the following configuration…
[drivers] mysql = com.mysql.jdbc.Driver
Connect To MySQL Using SQLShell
The format of your connection string for the MySQL JDBC driver should be
I’m just going to connect to the local mysql database and look at the pre-installed MySQL database called “test”. You should have the same database, unless you deleted it.
SQLShell, version 0.7.1 (2010/11/10 17:27:55) Copyright (c) 2009-2010 Brian M. Clapper Using JLine Type "help" for help. Type ".about" for more information. sqlshell>
Ok, we are connected to MySQL and can start running some queries.
We can see the commands that we can run using the help command.
sqlshell> help Help is available for the following commands: ------------------------------------------------------------------------------- .about .capture .desc .echo .run .set .show alter begin commit create delete drop exit help history insert r rollback select update
Let’s see what databases we have…
sqlshell> show databases; Execution time: 0.26 seconds Retrieval time: 0.21 seconds 2 rows returned. SCHEMA_NAME ------------------ information_schema test sqlshell>
We can create an new database.
sqlshell> create database sqlshell_test; 1 row affected. Execution time: 0.5 seconds
SQLShell passes SQL statements in full to the database, so you can use any commands that your database understands
The initial purpose of this blog post was to demonstrate SQLShell with a NoSQL database, but unfortunately I failed to find a JDBC driver that implemented all the features that SQLShell requires. I’m hoping that development of such drivers will continue, as it will be good to have standard inferface to all these NoSQL technologies directly from the command-line. I look forward to seeing how SQLShell and these JDBC develop over time.