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.
- 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)
- 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:
- cd to location where the client jar was download.
- Run cmd.exe as administrator
- java -jar SQuirrel-sql-3.2.1-install.jar
- Click through the installer screens. (GUI)
- Ignore the errors
- 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.
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.
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.
- 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.