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).
Installing SQLShell
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 https://cloud.github.com/downloads/bmc/sqlshell/sqlshell-0.7.1-install.jarRunning 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.jarFirst 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 PATHWe 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 runSQLShell With MySQL
Install MySQL’s JDBC Driver
Download the driver from MySQL’s website.
curl -O 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 filesAdd the following configuration…
[drivers]
mysql = com.mysql.jdbc.DriverConnect To MySQL Using SQLShell
The format of your connection string for the MySQL JDBC driver should be
jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...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 mysql,jdbc:mysql://localhost/test?user=rootSQLShell, 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 updateLet’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 secondsSQLShell passes SQL statements in full to the database, so you can use any commands that your database understands.
Conclusion
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.
Very nice and thorough article Phil,
I have only one addition – actually its an update:
there is a JDBC driver for Google BigQuery available for a few days now:
https://code.google.com/p/starschema-bigquery-jdbc/
This might give many people new perspectives for their data visualization needs.
Great info the JDBC driver, Endre. Thanks for sharing!