Vertica, by The Godfather of Databases, no batteries (drivers) included

by Mar 27, 2013

Hi Guys,

With every new version of Aqua Data Studio that we release we try to better document the features and enhancements that we add. It is important to understand the enhancements and why we add them. We are going to try to go a little further and write updates on some of the design ideas and processes we go through to add the features. Over the next few weeks I'll post some updates on some of the 13.0 enhancements. Once we release 13.0 GA, then I'll start posting on 14.0 designs so everyone will have an early view of the features that are coming and what the thought process is for each of the features. Today I'll share a little about Vertica support in 13.0.

We've had users asking for Vertica database support for years now, and we finally got around to adding it. The momentum behind Vertica seems to have picked up significantly after the HP acquisition. The integration of Vertica into Aqua Data Studio was pretty straight forward. Vertica by HP is a database for large database analytics which runs on a cluster of commodity servers.

Vertica was initially developed by Michael Stonebraker in 2005. We've been developing Aqua Data Studio for about 13 years now, but Stonebraker has been developing databases since 1973. He originally started the PostgreSQL project in 1973. Looks like he's been seeding the database industry for over 40 years. Check out his Wikipedia page below.

At a high level, Vertica is similar to PostgreSQL in that there is a Vertica database server which you make a connection to, and the connection is assigned to one database. Unlike MySQL, SQL Server and Sybase ASE you can't get a list of multiple databases on the server and change the database context of the connection. You are able to create multiple databases on the server admin tool, but Vertica does not recommend (or allow) two databases running simultaneously on one server. With this design, if you have 100 users connecting to the Vertica cluster, then they will all connect to the same database. If you have a fixed read-only list of tables for your users, then you could put all of your tables in a PUBLIC schema for everyone to access. But if you want to allow each user to have his own set of tables, then you can allow this by allowing each user to create their own tables in their own schemas. In Aqua Data Studio we opted to present the schema browser with a single database node containing a list of schemas, which then each provide a list of objects in that schema. This is a hybrid presentation of MySQL and Oracle (See attached screenshot). Users can opt to flatten the schema grouping in the Server Registration->Advanced->[Schema Folder Grouping] so that all objects in the database are displayed together.

Vertica has the concept of Projections. We highly recommend anyone using Vertica to understand this concept. Below are links to an overview of Projections.

Vertica has a proprietary JDBC driver which is not licensed for distribution, so this driver (vertica.jar) needs to be added to any Aqua Data Studio installation by end users after installation. Vertica is 1 of 2 vendors which do not allow distribution of their JDBC driver.…/Vertica…/PostgreSQL…/Michael_Stonebraker…/…/…/