Connecting to Vertica with SQuirreL SQL Client

Continuing with examples of connecting to Vertica, I went ahead and installed the SQuirreL SQL Client and configured it. SQuirreL is a Java based client that uses JDBC to connect to various databases. I is very helpful for looking around at tables and other objects. .

Before starting let me give some context on the setup. My kit is a  Windows 7  laptop (employee std. issue).  It has VMware Player with a Linux guests running Vertica 5.0. SQuirrel is installed into the Windows host and connects to the Vertica database instance running in the Linux VMware guest over the virtual network.

Assuming the above setup,  I took the following steps to install SQuirreL.

  1.  Down load SQuirreL from http://www.squirrelsql.org/  (Requires Java 1.6 to run the latest version of this client which at this time is 3.2.1)
  2. Install the SQuirrelL Client

Once the download completes you will run the java installer as shown below. This will give you a GUI that you use to complete the installation. I took all the defaults on the couple of screens that came up.

java -jar squirrel-sql-3.2.1-install.jar

This ran fine until near the end of the installation when it threw some “class not found  exceptions”. After learning how to set CLASSPATH in windows and looking unsuccessfully  for the missing classes, I found out from this blog that I could ignore the errors and the client would still work. Hurray! (This troubleshooting  reminded me of  how I hate to work with windows on this sort of thing )

Here is the complete summary of the install:

  1.  cd to location where the client jar was download.
  2. Run cmd.exe as administrator
  3. java -jar SQuirrel-sql-3.2.1-install.jar
  4. Click through the installer screens. (GUI)
  5. Ignore the errors
  6. Start the program by double clicking the  squirrel-sql.bat file located where you installed “the client” (e.g. c:\Program Files\squirrel-sql-3.2.1\squirrel-sql.bat)

Squirrel starts with an initial the splash screen of a squirrel and a acorn and then you will be presented with the “opening”  screen that looks like the screen shot below.

First screen

Squirrel 3.2.1 first screen

Configuring SQuirreL

With the installation complete, the first step to configure squirrel is to setup the JDBC client driver for Vertica. You will have to down load it from the myVertica site if you do not already have it. After I downloaded the client drivers  I moved the JDBC client driver jar file to a this simple path.

C:\vertica\vertica_jdbc5.jar

I  copied and renamed the jar file  from the original located in c:\Program Files\Vertica Systems\Vertica Client Drivers 4.1\lib\vertica_4.1.14_jdk_5.jar just to save some typing during unrelated jdbc trouble shooting. You don’t need to do this, I am just showing it to eliminate any confusion that this may create from the following example.

With the  Vertica JDBC driver installed we can continue with the SQuirrel setup up.

Staring from the “opening” screen shown above, under the blue “Drivers”  label click on the “+” button to get to the “Add Driver” screen. The following screen shots will lead you through the add driver steps.

First thing is to point SQuirrel to the JDBC client driver by selecting the “Extra Class Path” button.

Add Driver
When you click on the highlighted “Extra Class Path” button  the next screen comes up.
Click  “Add” to get to the next screen where we navigate the file system to the path where the Vertica JDBC Client driver jar file is located.
Using the “look in pull” down menu and the folder “finder” navigate to where the driver is on your hard disk. Once you get there, select it, fill in the other highlighted fields as shown below and then hit the open button. (Note that what you actually fill in will depend on your set up) The string, ExampleDB, is arbitrary one could use “foo” instead. Once done hit OK.
Hitting OK should return you to the first main window we saw at the top. It will indicate success if all went well. After that we will set up an alias and then make the connection. Below is how the completed  add driver screen should look. I scrolled down to show the Vertica driver.
Configure the Alias and make the connection:
Next  is to configure the aliases. Start by hitting the “Aliases” button followed by the “+” shown below. This will being you to the screens needed to complete the set up.
The above clicks bring you to the “Add Alias” screen shown below. The first step on this screen is to use the Driver menu to pick the Vertica driver we just finished adding. Once selected, the software will populate the URL field with the example we gave in the “Add Driver” steps.
Before proceeding, fill in the name of the Database you will be connecting to by replacing the” ExampleDB” string in the URL field with real database name. I am using the ClickStream_Schema for this example and will enter it’s name as above.  You also should  set the user name and password. Lastly, give the alias a name. The below screen shot shows the entries I made for my example.

After you  the enter the appropriate values click on the test button. The test screen, shown below, gives you a chance to connect to the database. Make sure the data base is up and running before you go and debug the client in the case of a failure. The test screen looks like this:

Click on the connect button and you should get a success notification. Congratulations!
Click on OK to finish the Alias setup and then proceed to the database screens.
Exploring the database
Now we  are ready to connect to the data base and look around. From the first alias screen select the data base alias name we just created. This example has the  “Click Stream Schema” as the alias to use.  Click on the “plug” button to connect to the database.
The connect button brings up the following screen.
If everything looks good hit the connect button. Shown below is the first connected screen. There you see some of the database objects and the ability to enter SQL statements. Cool!
Drill into the database by selecting the highlighted areas on the following screen shot.
Here we see the column definitions of the Date_Dimension table.
Let’s run a simple query. Select the SQL tab, type in a query and hit the run button. See an example below.
Here are the results:
All Right!. Time to have fun exploring. See the SQuirrel web site for more instruction on how to use this tool.
Cheers.
About these ads

12 thoughts on “Connecting to Vertica with SQuirreL SQL Client

  1. I followed the instruction, but got the following errors:
    Could not connect to database.
    com.vertica.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
    at com.vertica.core.v3.ConnectionFactoryImpl.openConnectionImpl(Unknown Source)
    at com.vertica.core.ConnectionFactory.openConnection(Unknown Source)
    at com.vertica.jdbc2.AbstractJdbc2Connection.(Unknown Source)
    at com.vertica.jdbc3.AbstractJdbc3Connection.(Unknown Source)
    at com.vertica.jdbc3g.Jdbc3gConnection.(Unknown Source)
    at com.vertica.Driver.makeConnection(Unknown Source)
    at com.vertica.Driver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.tpt.mytest.VerticaTest.main(VerticaTest.java:24)
    Caused by: java.net.ConnectException: Connection timed out: connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(Unknown Source)
    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.(Unknown Source)
    at java.net.Socket.(Unknown Source)
    at com.vertica.core.PGStream.(Unknown Source)
    … 10 more

  2. Awesome post, thanks! :)

    Just mention that the driver is case sensitive, so people can save a couple of minutes while referring to this

  3. Tom,

    I am getting below error when click ok after setting Driver

    java.lang.ClassNotFoundException: com.vertica.Driver
    Could not find class com.vertica.Driver in neither the Java class path nor the Extra class path of the VerticaCDB driver definition:
    java.lang.ClassNotFoundException: com.vertica.Driver

    Any help

    Thanks
    NewToVertica

  4. Thanks for taking the time to write this, it’s proven helpful a second time now as I just got a new PC.

    • Hi Kumaresh, Hopefully you have found a solution to your question. It has been awhile since I had the squirrel kit set up and haver upgraded Vertica since then. I will try to make it work and see if I can find an anser for you. I don’t ahver much experience with Squerrel byeond getting it hooked up to Vertica. Good luck.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s