|Share this article:|
Vertica Integration with Toad Data Modeler: Connection Guide
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 Toad Datamodeler as of October, 2017.
Toad Data Modeler Overview
Toad Data Modeler allows you to construct logical and physical data models, as well as:
- Compare and synchronize models
- Quickly generate complex SQL/DDL
- Create and modify scripts
- Reverse and forward engineer both databases and data warehouse systems
For more details see the Toad Data Modeler website.
This document provides guidance using the software listed as follows:
Toad Data Modeler 220.127.116.11
Microsoft Windows 10
Vertica ODBC driver 64 bit version 8.0.1
Vertica Server versions 8.0.1, 8.1.0, 9.0
|Server Platform||CentOS 7.3|
Install Toad Data Modeler
- 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.
- 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.
Install the Vertica Client Driver
Toad Data Modeler uses the 64 bit ODBC driver to connect to Vertica. Before you can connect Toad Data Modeler to Vertica, you must install the Vertica client package for Windows and set up a DSN to your database. Download and install the Vertica client driver as follows:
- Go to the Vertica Client Drivers page on Vertica.
- Download the Vertica client package that matches your operating system and the Vertica version that you are using.
- Follow the client installation instructions in the Vertica documentation.
Note Be sure to install the 64 bit ODBC driver.
Set up a 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:
Connect to Vertica
With Toad Data Modeler running do the following to connect to Vertica:
- Select Menu > Settings > Add/Remove Database. If it is not already selected, select the Vertica checkbox. Click OK.
- 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.
- In the Connections dialog, click the New Connection icon on the toolbar and add a new connection for the Vertica database DSN you created, as follows:
Name: use any name pertinent to your organization
Select Data Source: select a Vertica version best suited for your database
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 checkboxes.
- Click Finish, the following appears:
- Select the connection you created and click Create New Model from Database.
- Select the object types you want to add to your model.
- Click Execute. When you receive the prompt that Reverse Engineering is complete, click OK and then Close.
- Close the Connection dialog.
At this point your model should appear in the Physical Model Explorer:
- 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.
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