Vertica Integration with Toad Data Modeler: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Toad Data Modeler Overview

Toad Data Modeler allows you to construct logical and physical data models, and compare and synchronize these models. It also enables you to quickly generate complex SQL/DDL, create scripts, and reverse and forward engineer both databases and data warehouse systems.

Vertica and Toad Data Modeler: Latest Versions Tested

This document provides guidance using the software listed as follows:

Software Version
Partner Product

Toad Data Modeler

Partner Platform

Microsoft Windows 10

Vertica Client

Vertica ODBC driver 64-bit version 11.1.1

Vertica Server

Vertica Analytic Database 11.0.0

Server Platform RHEL 7.9

Installing Toad Data Modeler

  1. Go to the Toad Data Modeler download site and download the Toad Data Modeler (64-bit) version.

    Note You need to complete the required fields to access the download page.

    When you select Toad Data Modeler (64-bit) the following file gets downloaded:


    The name of the downloaded file may differ slightly depending on what version of Toad Data Modeler is currently available for trial.

  1. Open this file and follow the prompts in the installation wizard to complete the installation. For more information see the Toad Data Modeler Technical Documentation.

Installing the Vertica Client Driver

Before you can connect Toad Data Modeler to Vertica, you must install the Vertica ODBC client driver and set up a DSN.

  1. Go to the Vertica Client Drivers page on Vertica.
  2. Download the the Vertica client package that is compatible with your Vertica server version.
  3. Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  1. Double-click the installer and follow the prompts. When prompted to select one or more drivers, ensure to select the 64-bit ODBC driver.
  2. Create an ODBC DSN as described in the Creating an ODBC Data Source Name (DSN) section.

Creating an ODBC Data Source Name (DSN)

In the Windows ODBC Data Source Administrator (64-bit), configure a 64-bit System DSN for the Vertica ODBC driver you installed and your Vertica database. For example:

Connecting to Toad Data Modeler to Vertica

With Toad Data Modeler running ,do the following to connect to Vertica:

  1. Select Menu > Settings > Add/Remove Database. If it is not already selected, select the Vertica checkbox. Click OK.

  2. Press Ctrl-Alt-C to open the Connections dialog. You can also access this dialog by selecting File > Reverse Engineer > Connections or clicking the Connections icon.
  3. In the Connections dialog, click the New Connection icon on the toolbar and add a new connection for the Vertica database DSN you created. For each screen in the Connections dialog, enter the field information and click Next.
    Name: Use any name pertinent to your organization.
    Select Data Source: Select Vertica from the list.
    Select Data Provider: Select Connection via ODBC.
    Connecting: select the DSN you created in the previous section from the Datasource Name pulldown menu. Then enter the database user name and password.
    What to Reverse Engineer: Select the Vertica objects you want to reverse engineer into DDL.
    Options: Optionally select the Divide Objects on Workspace by Schema and Infer Relationships check boxes.
  4. Click Finish, the following appears:

  1. Select the connection you created and click Create New Model from Database.
  2. Select the object types you want to add to your model.
  3. Click Execute. When you receive the prompt that Reverse Engineering is complete, click OK and then Close.
  4. Close the Connection dialog.

    At this point your model should appear in the Physical Model Explorer:

  1. Select Menu > File > Save As to save the model to the desired file name and location.

You can now explore the model's navigation tree, view the objects in your Vertica database, and modify or create new objects. See the Toad Data Modeler User Guide for details.

Known Limitations

Before using Toad Data Modeler, be aware of the following known limitations:

  • Toad Data Modeler does not currently support reverse engineering of super projections, only non-super projections appear in the navigation tree.
  • Toad Data Modeler does not currently fully reverse engineer Vertica Functions, Libraries, Procedures, and Schemas. It provides basic create SQL information, but no details. For example, for library objects it doesn't provide library path/name, dependencies, and language.
  •  Toad Data Modeler does not currently support the following features as documented in the Supported Databases section of the User Guide:
    • Import from SQL file

    • SQL/DDL Code Generation
    • Change Script Generation

For More Information