Vertica Integration with GoodData: 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 GoodData.CN (Cloud Native) Community Edition: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
Partner Product GoodData.CN Community Edition v1.2 deployed On-Premises
Desktop Platform

Red Hat Enterprise Linux 7.3
Vertica Client

Vertica JDBC 10.0.1
Vertica Server

Vertica Server 11.0.0-0
Vertica Server Platform Red Hat Enterprise Linux 7.3

GoodData Overview

GoodData.CN (Cloud Native) is a business intelligence platform that provides tools for data exploration, data modeling, and data visualization.

GoodData is a cloud-based solution that is available as a fully hosted service or installed locally on-premises. The Community Edition is available for evaluation as a Docker container image, and for production it is available for Kubernetes.

GoodData uses JDBC and allows live connections to your Vertica database.

Installing GoodData.CN

To install GoodData.CN (Cloud-Native) Community Edition,

Note Before you install GoodData.CN, ensure that you install Docker and it is running. For Docker information, refer to the Docker documentation.

  1. Go to GoodData.CN community edition website.

  2. Click Get Community Edition for instructions on how to use the GoodData container image.

For installation information, refer to Install GoodData.CN.

Installing the Vertica Client Driver

GoodData.CN connects to Vertica using the Vertica JDBC client driver. This driver is built into GoodData.CN platform and is packaged as part of the application. You do not need to download and install the Vertica JDBC client driver.

GoodData bundles Vertica’s JDBC driver version 10.0.1, which is both backwards and forward compatible.

Connecting Vertica to GoodData.CN

Note For information about the Vertica data source and performance tips, refer to Vertica on the GoodData website.

  1. Open GoodData Portal in the browser. In the case of GoodData.CN Community Edition, the URL is: http://localhost:3000/

  2. Login with your credentials. To log in for the first time after installation, use the following credentials:

    Email address: demo@example.com

    Password: demo123


  3. Click Login. The home page opens.

  4. In the homepage, click Create workspace. A workspace is a GoodData project.

  5. Enter a name for your workspace or project.

  6. Click Create. The workspace VerticaProject will appear in the list of workspaces in the home page.

  7. Click either Connect data or Open.

  8. The Connect demo data screen appears.

  9. Click Skip.

    The Data tab is displayed. This is where you build a Logical Data Model (LDM).

    Note Before you can build an LDM, you need to register your Vertica data source.

  10. To register your Vertica data source, you use GoodData’s API. The following is an example on Linux that registers a connection to the Vertica sample database VMart in the public schema.

    Copy and paste the following CURL request in a Linux terminal of the machine hosting the GoodData.CN platform. You need to modify the CURL script to provide your details in the angle brackets:

    curl http://localhost:3000/api/entities/dataSources \
    -H "Content-Type: application/vnd.gooddata.api+json" \
    -H "Accept: application/vnd.gooddata.api+json" \
    -H "Authorization: Bearer YWRtaW46Ym9vdHN0cmFwOmFkbWluMTIz" \
    -X POST \
    -d '{
    "data": {
    "attributes": {
    "name": "<my_data_source>",
    "url": "jdbc:vertica://<my_vertica_server>:5433/<db_name>?Label=<my_label>",
    "schema": "<my_schema>",
    "type": "VERTICA",
    "username": "<my_user>",
    "password": "<my_password>"
    },
    "id": "<my_data_source_id>",
    "type": "dataSource"
    }

  11. After the execution is successful, switch back to the GoodData.CN portal and refresh the Data tab.

    The new Vertica data source will be listed in the DATA SOURCES drop-down.

    Note After you have registered your Vertica data source, you can begin modeling your data (aka Logical Data Model (LDM)). For information about creating an LDM, see Manage a Logical Data Model.

    Once you design a Logical Data Model (LDM) you can begin exploring your data and building insights and KPIs. For information on building insights and dashboards, see Analyze Data in the GoodData documentation.

Troubleshooting

Review these known issues and workarounds when connecting to Vertica using GoodData.CN:

Error message: “Connection failed, try again later… SORRY WE CAN’T DISPLAY THIS INSIGHT” when attempting to create an insight after a Logical Data Model (LDM) has been published.

Issue: GoodData.CN allows publishing a Logical Data Model (LDM) that contains fields that are missing the mapping to the source tables/views in Vertica. GoodData does not display a warning or error message about the required mapping and doesn’t prevent publishing.

The mapping is required to create insights and so creating an insight fails with the following error message:

Solution: In GoodData.CN portal, go to the Data tab and examine the mapping for each of the fields in each of the datasets in the LDM. Manually map the fields to the corresponding fields in Vertica tables/views if necessary:

For each data set in your LDM

  1. Click the More menu.
  2. Select View details.
  3. In the details window, click the Data mapping tab. Ensure each field is assigned to a source column in Vertica or manually assign the correct mapping.
  4. Click Save changes.
  5. Publish the LDM.

You can now begin creating your insights.

Known Limitations

The following is a known limitation when creating a Logical Data Model (LDM):

  • GoodData does not support composite keys (a primary key that consists of more than one column).

    As a workaround, create views in Vertica. A view with a single-column primary key for the referenced (parent) table and a view with a single-column foreign key for the referencing (child) table. Then you can use these views to create the relationships between tables in the LDM.

The following is a list of known limitations with data types when connecting to Vertica from GoodData.CN via JDBC:

  • For NUMERIC data type, columns of size 1024 bytes are not supported. The error message SORRY, WE CAN’T DISPLAY THIS INSIGHT is displayed when building an insight. No errors are displayed when building an LDM.

  • For LONG VARCHAR data type, columns of size 32 million bytes are not supported. The error message SORRY, WE CAN’T DISPLAY THIS INSIGHT is displayed when building an insight. No errors are displayed when building an LDM.

  • INTEGER columns are mapped to Text labels when building an LDM. If the INTEGER columns are measures or facts, you can utilize GoodData’s naming conventions to auto-detect the columns as measures/facts. For more information, see Database Naming Conventions.
  • For TIMESTAMP and TIMESTAMPTZ data types, seconds, milliseconds, and time zone offset are not supported and are not displayed when creating an insight. Minutes are the smallest granularity displayed in an insight. No errors when building an LDM.
  • BINARY, VARBINARY and LONG VARBINARY, INTERVAL (Interval Year to Month, Interval Hour to Second, Interval Hour to Minute, Interval Day to Second), TIME and TIMETZ, and UUID data types are not supported or displayed in the user interface when building an LDM.

For information about Vertica’s specific data types, see SQLDataTypes.

For More Information