Share this article:

Vertica Integration with TIBCO Spotfire: Connection Guide

Applies to Vertica 7.2.x and earlier 

 

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 TIBCO Spotfire as of February, 2016.

TIBCO Spotfire Overview

TIBCO Spotfire is an analytics and business intelligence platform that provides data analysis through predictive statistics. TIBCO Spotfire connects to Vertica using the OBDC driver, ADO.NET, and Java. This document provides connection information using ADO.NET and Java. Hewlett Packard Enterprise recommends using ADO.NET.

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

  • Vertica 7.0.x with TIBCO Spotfire 6.0
  • Vertica 7.0.x, 7.1.x, and 7.2.x with TIBCO Spotfire 6.5.x
  • Vertica 7.0.x, 7.1.x, and 7.2.x with TIBCO Spotfire 7.0.x
  • Vertica 7.1.x and 7.2.x with TIBCO Spotfire 7.5

Download and Install TIBCO Spotfire

Download the version of TIBCO Spotfire that meets your needs from the TIBCO Spotfire website. For more information about downloading and installing the TIBCO Spotfire software, see the product documentation. The following documents are especially helpful for using TIBCO Spotfire and Vertica:

  • TIBCO Spotfire Deployment and Administration Manual
  • TIBCO Spotfire Server Installation and Configuration Manual
  • TIBCO Spotfire Connector for Vertica

You can find more information about the TIBCO Spotfire Connector for Vertica on the support page.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using TIBCO Spotfire, you must install the Vertica client package. This package includes the ADO.NET and JDBC client drivers that TIBCO Spotfire uses to connect to Vertica.

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 the architecture of 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

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Connect to Vertica fromTIBCO Spotfire Using ADO.NET

TIBCO Spotfire 6.0 and later supports an In-Database Data Source for Vertica using ADO.NET.

Important The Vertica data connection should appear on the Add Data tab. If you cannot see this option:

  1. Check to verify you have installed the ADO.NET drivers. If they are not installed, follow the steps to Download and Install the Vertica Client Drivers.
  2. If the drivers are installed, contact TIBCO Spotfire support about the Spotfire Connector for Vertica.
  3. Check with your administrator to see if the Vertica connector was disabled because of a license issue. For more information, see Details on Views in Connection in the TIBCO Spotfire documentation.

Follow these steps to connect to Vertica using ADO.NET:

  1. Start the TIBCO Spotfire software.
  2. Select the Add Data tab.
  3. Click HP Vertica.
  4. Spotfire1.png

  5. A dialog box appears. Enter the Server, Username, and Password.
  6. Click Connect. The Views in Connection window opens.
  7. From the panel on the left, select the table(s) you want to add. In this example, the fact table online_sales is selected.
  8. Spotfire2.png

  9. Click Add. The views and columns populate.
  10. Click OK. The Add Data Tables window appears.
  11. Under Load Method, select Keep data table external.
  12. Spotfire3.png

  13. Click OK.

You can now begin analyzing and visualizing your data.

Add New Data Tables

To add additional tables to your visualization, follow these steps:

  1. Click File > Add Data Tables. The Add Data Tables window appears.
  2. Click Add in the upper right-hand corner.
  3. From the drop-down menu, under From Current Analysis, select Connection (Vertica ). This connects you directly to the database you are working with.

You can now select the tables you want to add to your visualization.

Connect to Vertica from TIBCO Spotfire Using Java

Before you can connect to TIBCO Spotfire using Java, you must enable the Data Source by following these steps.

Note: Hewlett Packard Enterprise recommends connecting using ADO.NET.

  1. On the TIBCO Spotfire Server, run Configure TIBCO Spotfire Server.
  2. Select Data Source Template.
  3. On the right-hand side, under Data Source, click Add New.
  4. Enter Vertica as the name.
  5. Paste the following text into the Data Source Template field:

    <jdbc-type-settings>
    <type-name>Vertica</type-name>
    <driver>com.vertica.jdbc.Driver</driver>
    <connection-url-pattern>jdbc:vertica://host:port/db,dbadmin,dbpassowrd</connection-url-pattern>
    <ping-command>SELECT 1 FROM DUAL</ping-command>
    </jdbc-type-settings>

  1. Click Save.
  2. Check the Enabled button so that Vertica appears in the Data Source.
  3. Save the configuration.

To connect to Vertica from TIBCO Spotfire using Java, follow these steps:

  1. Start the TIBCO Spotfire server.
  2. Start Spotfire.
  3. In Spotfire, select Tools > Information Designer.
  4. Select the DataSource tab.
  5. Click Setup Data Source.
  6. Enter the data source information as shown in the following graphic. 

Spotfire4.png

  1. Click Save As.

Vertica Data Types

When you are setting up a connection to an external data source, TIBCO Spotfire has to map the data types in Vertica against the data types in Spotfire. The following table shows the data type mappings.

Vertica Database Type TIBCO Spotfire Data Type Supported

BOOLEAN

Boolean

Yes

INTEGER

Integer

Yes

BIGINT

LongInteger

Yes

VARBINARY

Binary

Yes

BINARY

Binary

Yes

CHAR

String

Yes

VARCHAR

String

Yes

MONEY

Currency

Yes

NUMERIC

  • When p <= 9 and s = 0: Integer
    When 9 < p <= 18 and s = 0: LongInteger
    Else: Real

Yes

DOUBLE

Real

Yes

FLOAT

SingleReal

Yes

DATE

Date

Yes

TIME

Time

Yes

TIMETZ

Time

Yes

TIMESTAMPTZ

DateTime

Yes

TIMESTAMP

DateTime

Yes

INTERVAL DAY

TimeSpan

Yes

INTERVAL HOUR

TimeSpan

Yes

INTERVAL MINUTE

TimeSpan

Yes

INTERVAL SECOND

TimeSpan

Yes

INTERVAL DAY TO HOUR

TimeSpan

Yes

INTERVAL DAY TO MINUTE

TimeSpan

Yes

INTERVAL DAY TO SECOND

TimeSpan

Yes

INTERVAL HOUR TO SECOND

TimeSpan

Yes

INTERVAL MINUTE TO SECOND

TimeSpan

Yes

INTERVAL YEAR

No

INTERVAL YEAR TO MONTH

No

INTERVAL MONTH

No

All other data types are not supported.

Supported Functions

TIBCO Spotfire supports the following Vertica functions:

Function Type Functions

Date and Time Functions

DAY, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MICROSECOND, MINUTE, MONTH, QUARTER,  SECOND, WEEK, YEAR

Conversion Functions

SN

Math Functions

ABS, ACOS, ASIN, ATAN, ATAN2, CBRT, CEILING, COS, COT, DEGREES, DISTANCE, DISTANCEV, EXP, FLOOR, LN, LOG, MOD, PI, POWER, RADIANS, RANDOM, RANDOMINT, ROUND, SIGN, SIN, SWRT, TAN, TRUNC

Operators

%, - , + , * , /

Statistical Functions

AVG, CORR, COUNT, COVAR_POP, COVAR_SAMP, MAX, MIN, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, STDDEV, STDDEV_POP, STDDEV_STAMP, SUM, UNIQUECOUNT, VAR_POP, VAR_SAMP, VARIANCE

Text Functions

CONCAT, INSTR, LENGTH, LOWER, LPAD, LTRIM, RPAD, RTRIM, TRIM, UPPER

For More Information

For More Information About… See…

TIBCO Spotfire

http://spotfire.tibco.com/

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

https://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: