How to Connect Vertica Database using JDBC Driver?

  • Post author:
  • Post last modified:September 12, 2019
  • Post category:Vertica
  • Reading time:5 mins read

The Vertica is one of the widely used analytics database clusters. You can connect to Vertica using many methods such as JDBC, ODBC, etc. You can use any programming language that supports JDBC connection string to connect Vertica database using JDBC driver. Almost all modern programming language provides api to use jdbc drivers. In this article, we will check how to connect to Vertica using JDBC driver. We have used Python as a programming language to demonstrate jdbc connection.

Vertica JDBC Driver

The HP Vertica comes with support to JDBC driver. You can use this jdbc driver with any programming language that supports JDBC connection string.

You can download Vertica JDBC driver from official download site.

Alternatively, you can ask HP to provide set of software’s when you purchase Vertica database.

Before going into using JDBC driver in Python, let us see how to use jaydebeapi, a third-party module to use JDBC drivers in Python.

Though you can use any module that supports JDBC drivers, jaydebeapi is one of the easy module that I have been using.

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to various databases using Java JDBC drivers and connection string. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

pip install Jaydebeapi

Set CLASSPATH to Driver Location

If there are dependent jar files, you have to provide path of those dependent driver in CLASSPATH environmental variable.

As we have no other dependent jar for this Vertica JDBC driver, you can directly refer this driver in your jaydebeapi module. Alternatively, you can export jar location to CLASSPATH shell variable and run your python program without needing to set jar location in your module.

You can read on how to set CLASSPATH variable in another post – Set and Use Environment Variable inside Python Script.

How to Connect Vertica using JDBC Driver?

In this section, we will check how can we connect Vertica database using JDBC driver. As mentioned in previous subsections, I will be using python and jaydebeapi to refer Vertica analytics cluster JDBC driver.

Once you have Vertica jdbc jar in a place and installed required third-party modules, you are ready to access Vertica database from within your Python program using JDBC driver.

Note that, Vertica jdbc driver class name is “com.vertica.jdbc.Driver”

Here is the code that can help you:

import jaydebeapi as jay
import os

# Vertica Server Details
dsn_database = "vmart"
dsn_hostname = "192.168.200.150"
dsn_port = "5433"
dsn_uid = "dbadmin"
dsn_pwd = "dbadmin"

# Vertica JDBC class name
jdbc_driver_name = "com.vertica.jdbc.Driver"

# Vertica JDBC driver path
jdbc_driver_loc = os.path.join('D:\\Work\\Connections_Softwares\\Jar\\vertica-jdbc-9.2.0-0.jar')

# Sample SQL Query
sql_str = "select now()"

# JDBC connection string
connection_string='jdbc:vertica://'+dsn_hostname+':'+dsn_port+'/'+dsn_database

url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

# Establish JDBC connection
conn = jay.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()

print(result[0])

Here is the sample output:

Connection String: jdbc:vertica://192.168.200.150:5433/vmart 
('2019-04-05 14:40:21.638642',)

You can also use ODBC driver to connect Vertica. You can read more about this on my other post:

Hope this helps 🙂

Tags: ,