Posts

SQLShell. A Cross-Database SQL Tool With NoSQL Potential

Will code for food

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

  • RDBMS Databases
  • 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).

    – Brian M. Clapper, author of SQLShell

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

    SQLShell Installer language prompt

    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.

    SQLShell Installer 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 

    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

    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=root
    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.

    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.

    Comments

    1. Endre

      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.