Share this article:

Vertica Integration with Domo: 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.

Vertica and Domo: Latest Versions Tested

Software Version
Partner Product

Domo Workbench 5.0

Domo Server (SaaS)

Domo Workbench Platform

Microsoft Windows Server 2012

Vertica Client

Vertica ODBC 9.2

Vertica JDBC 7.0 (pre-configured)

Vertica Server Vertica Server 9.2

Domo Overview

Domo is a cloud-based reporting tool that can import and use data fromVertica and many other data sources. Domo includes the following components:

  • Domo Workbench: A windows-based data modeling tool. Domo Workbench deploys data models to Domo Server.

  • Domo Server: A cloud-based web application that is managed by Domo as Software as a Service (SaaS).

For details, visit the Domo website.

How Vertica Works with Domo

Domo Workbench

With Domo Workbench, you can define DataSets using Vertica data and publish them to Domo Server.

You must install the Vertica ODBC client driver before you can connect Domo Workbench to Vertica. (See Install the Vertica Client Driver and Create a DSN for instructions.) The Vertica database can be installed on premises or on the cloud.

Domo Server

With Domo Server, you can access Vertica DataSets that were published from Domo Workbench or loaded directly from a cloud deployment of Vertica.

The Vertica 7.0 JDBC driver is bundled with Domo Server and is managed by Domo as part of its SaaS offering. Domo Server connects to Vertica on the cloud and requires a public IP address for the connection.

Create a Domo Account and Sign In

Create an Account

You can create a trial account for thirty days, or you can purchase a Pro license.

To create a trial accountt:

  1. Navigate to www.domo.com and click TRY FREE.
  2. Provide your work email and a phone number.

    Your work email will be your user name.

  3. Create a password and answer additional questions to complete the creation of your account.

When the account has been created, you will note that the page URL now includes your domain name:

https://your_domain.domo.com/

Note Your domain is the domain of your work email. You will need to provide your domain name whenever you sign in to your account.

Sign In to Your Account

After you create your account, you will follow these steps each time you log in:

  1. Navigate to www.domo.com and click Sign In.

    Note The first time you sign in, you will be asked to complete your profile.

  2. Provide your domain name and click Continue.
  3. Provide your user name and password.

Install Domo Workbench

  1. Sign in to your account on www.domo.com and click the icon to the left of your profile picture to display a panel of options.
  2. Click the Admin option.

  3. Select Tool Downloads from the menu on the left of the page.

  4. Click DOWNLOAD DOMO WORKBENCH.

  5. Double-click the Domo Workbench installer and follow the on-screen instructions to install Domo Workbench.

Install the Vertica Client Driver and Create a DSN

Domo Workbench uses the Vertica ODBC driver with a System DSN to connect to Vertica. To install the driver and create the DSN, follow these steps:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the client driver package that is compatible with the architecture of your operating system and Vertica server version.

    Note  

    For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Follow the installation instructions in the Vertica documentation.

  4. Create a DSN:

    1. In the Windows ODBC Data Source Administrator, open the System DSN tab and click Add.

      Note  

      Domo requires a System DSN. If you create a User DSN or a File DSN, it will not be available in Domo Workbench.

    2. Specify values for DSN Name, Database, Server, Port, User name, and Password, then click OK.
    3. Click OK to create the ODBC DSN.

Connect Domo with Vertica

Follow the instructions in the following topics:

Connect Domo Server with Vertica

  1. Sign in to your account on www.domo.com and click the + icon to expand the menu, then select Data.

  2. Select Database to open the Data Connectors page.
  3. Select Vertica.

  4. Click GET THE DATA.

  5. On the Create Vertica Dataset page, provide the connection details for a cloud deployment of Vertica (at a public IP address) and click Connect.

  6. Click Next.
  7. Provide a query. For example:

    SELECT product_dimension.category_description, 
    	product_dimension.department_description, 
    	online_sales.online_sales_fact.sales_dollar_amount  
      FROM product_dimension INNER JOIN
    	online_sales.online_sales_fact ON 
    	product_dimension.product_key = 
    	online_sales.online_sales_fact.product_key 
    	AND product_dimension.product_version = 
    	online_sales.online_sales_fact.product_version;

    Note The VMart example database must be installed to use this sample query.

  8. Click Next.

  9. On the Scheduling page, click Next again.
  10. In the DATASET NAME box, type a name for the Vertica DataSet and click SAVE.

    The query results will be loaded into memory in Domo Server.

Connect Domo Workbench with Vertica

  1. Open Domo Workbench.

  2. On the welcome screen, click the key icon to open the Accounts page.

  3. On the Accounts page, click the + icon to add a new server account.

  4. Provide your domain name and click Authorize.

    The authorization link and authorization code appear.

  5. Open the authorization link and provide the authorization code to link Domo Workbench with your Domo Server account.

  6. Click the Save icon on the top right before leaving the page.

  7. Click the jobs icon to open the Jobs page.

  8. Click the + icon to create a new job.

  9. Provide the following details about the job:

    • Job Name
    • Transport Type
    • Reader Type
    • Dataset Name
    • Dataset Type

  10. Click Create to create the job.

  11. Open the Configure tab and click Edit for Source.

    The ODBC Connection Provider Editor opens.

  12. Provide these values in the following fields:

  13. Click Apply.

Deploy a Project from Domo Workbench to Domo Server

  1. After following the steps in Connect Domo Workbench with Vertica , open the Jobs tab for the Dataset Job.
  2. Double-click the job you created.

    The job opens in edit mode.

  3. Open the Configure tab and click Edit for Processing.

  4. IIn the Database Query Editor, provide a query. For example:

    SELECT product_dimension.category_description, 
    	product_dimension.department_description, 
    	online_sales.online_sales_fact.sales_dollar_amount 
      FROM product_dimension INNER JOIN 
    	online_sales.online_sales_fact ON 
    	product_dimension.product_key = 
    	online_sales.online_sales_fact.product_key 
    	AND product_dimension.product_version = 
    	online_sales.online_sales_fact.product_version;

    Note  

    The VMart example database must be installed to use this sample query.

  5. Click Apply.

  6. Click the Save icon on the top right corner to save the changes in the job.

  7. Click the eye icon to preview the output of the query.

  8. Click the Run icon to deploy the Dataset to Domo Server.

    The query results will be imported in Domo Server.

Known Limitations

Data Type Incompatibilities in Domo Server (JDBC) and Domo Workbench (ODBC)

  • CHAR, VARCHAR and LONGVARCHAR data types with length greater than 1024 length are truncated.
  • NUMERIC data types are supported up to 15 digits before the decimal point. Values to right of the decimal point are truncated to 2 digits.
  • Minimum year supported is 1970.
  • Milliseconds are truncated in TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ data types. No error is reported.
  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported. Wrong values are displayed.

Data Type Incompatibility in Domo Workbench (ODBC) Only

  • INTERVAL data type returns the Workbench error Unknown SQL type - 112.

For More Information

Share this article: