Share this article:

Vertica Integration with RazorSQL: Connection Guide

Click here for a PDF version of this document. 

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and RazorSQL as of July, 2016.

RazorSQL Overview

RazorSQL is a SQL editor, database query tool, database browser, and database administration tool. RazorSQL features the following components:

  • Visual tools for creating and modifying database objects
  • Tools for importing and exporting data in various file formats
  • Search tools for navigating database objects and data
  • Tools for building SQL queries and generating SQL and DDL
  • A SQL formatting tool
  • SQL history tracking
  • A SQL favorites tool for storing frequently used SQL statements

This document is based on results of testing the following versions of RazorSQL with Vertica: 

  • RazorSQL version 7.0.1 on Windows with Vertica Server 7.2.3 on CentOS 6.5 using the RazorSQL built-in Vertica 6.1.3 JDBC driver, as well as the Vertica 7.2.3 JDBC and ODBC drivers.
  • RazorSQL version 7.0.1 on CentOS 6.5 with Vertica Server 7.2.3 on CentOS 6.5 using the RazorSQL built-in Vertica 6.1.3 JDBC driver, as well as the Vertica 7.2.3 JDBC driver.

Download and Install RazorSQL

To find the RazorSQL platform requirements, installation instructions, and software downloads, navigate to the RazorSQL website.

Download and Install Vertica Client Drivers

RazorSQL includes Vertica as a supported database and provides the Vertica 6.1.3 JDBC driver by default. If you prefer to use a newer Vertica JDBC or ODBC driver, you can download one from the Vertica Client Drivers page. To download and install a Vertica client driver, complete the following steps.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

To install your client package, follow the steps for installation in the Vertica documentation.

Connect Vertica to RazorSQL

To create a RazorSQL connection profile and connect to your Vertica database, complete the following instructions.

  1. Launch RazorSQL.
  2. Under the Connections tab, select Add Connection Profile.


    The Connection Wizard opens.

  3. Select Vertica and click Continue, as seen in the following graphic:

1.PNG

  1. Under Connection Type, choose one of the following drivers:
    • JDBC (Vertica), which is the default driver
    • JDBC, which allows you to use a JDBC driver of your choosing
    • ODBC, which allows you to use an ODBC driver of your choosing

Note RazorSQL does not support using ODBC drivers on Linux.

2.PNG

  1. Fill in your connection information in the corresponding fields.


    By default, Auto Commit is set to On and SQL Restrictions is set to None.

  2. Click Connect.


    RazorSQL connects to your Vertica database.

RazorSQL Interface

After successfully connecting to RazorSQL, the database browser interface opens as shown in the following graphic. The object tree is located on the left, the SQL editor is in the top-right panel, and the results are in the bottom-right panel. Use the menus at the top of the interface to explore the tools offered by RazorSQL.

3.png

Troubleshooting

If you right-click a function and select Execute Procedure, this fails with a Driver not capable error, as seen in the following image. This error appears because Vertica functions are not intended to be run as standalone functions. Vertica functions are typically used within a SQL string and passed arguments.

4.PNG

Known Limitations

In the object tree, all Vertica User-Defined Functions (UDFs) appear erroneously in the FUNCTIONS, SYSTEM TABLE, AND VIEW folders. They should appear only in the FUNCTIONS folder. This is an issue in the GUI only; it causes no performance issues.

For More Information

For More Information About… … See
RazorSQL www.razorsql.com
Vertica Community Edition https://my.vertica.com/community/
Vertica Documentation http://my.vertica.com/docs/latest/HTML/index.htm
Big Data and Analytics Community https://my.vertica.com/big-data-analytics-community-content/

Share this article: