Developing with Vertica

When you first begin developing with any new database manager the hardest part is establishing connectivity.  Let's assume you just want to connect to Vertica from your laptop using something a little bit nicer than vsql over ssh.  As I mentioned previously, Vertica has no native GUI query tool.  In this post we'll cover everything you need to know to start exploring VMart from a modern, GUI-based query tool.  

GUI-based universal database clients

There are a ton of FOSS (free and open source) universal database clients.  A universal db client can connect to any database that has a driver for the data access platform that the universal client was written for.  Most universal db clients are java-based so if there is an available JDBC driver then you can connect.  Using a universal db client is a great way to learn NoSQL since most NoSQL products have JDBC drivers.  The theory is that if you learn the universal db client you should be able to learn any underlying database manager.  

I prefer to use a universal db client that is similar in commands and functionality to SSMS.  I think the best tools are DBeaver and SQuirreL. Both are java-based and are easier to setup, frankly, than SSMS.  Initially I used SQuirreL but that seemed to be more prone to crashing and was a bit of a memory hog.  It's a fine tool but I'm not going to cover it in this blog post.  Feel free to explorer on your own.  

DBeaver

I then tried DBeaver (pronounced dee beaver and is a play on SQuirreL).  I'm hooked.  In 2 minutes I was connecting to SQL Servers, Oracle, Vertica, and Cassandra in different DBeaver windows.  How cool is that?  

DBeaver is completely non-commercial and open-source, however I can't find the exact type of open source license that is used.  You can download the source or a standard Windows installer.  It is best to download the Enterprise Edition which also includes modules designed for some NoSQL products.  EE is also free.  

Once downloaded and installed you need to tell DBeaver which database manager you want to connect to.  The screenshot to the right shows all of the different database managers that DBeaver can work with.  Obviously we are going to choose Vertica on this screen.  

If you haven't installed Vertica drivers on your laptop yet DBeaver gives you an opportunity to automatically do this.  DBeaver automatically detects that you have no JDBC Vertica drivers installed and offers to install them for you.  

You should now see the following screen:  

Similar to SQL Server you need to know the host, dbname, and user/password that you want to connect to.  Like SQL Server if you don't have the SQL Browser service running on your SQL Server, you will need to know the listening port.  By default Vertica uses 5433.  

If you are unsure of your listening port you can run nano VMART/port.dat as dbadmin.  

Once you have that you should be presented with the Database Navigator which is similar to SSMS's Object Explorer.  

From here you can run queries and visually explore your Vertica database.  Within 5 minutes you can install DBeaver and connect to VMart and issue your first query.  I don't know how it could be any easier than that.  

You have just read "[[Developing with Vertica]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.