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

Slemma and Vertica: Latest Versions Tested

Software Version

Slemma

Slemma 8.47.5

Client Platform

SaaS

Vertica Client

Vertica JDBC driver version 8.1.1, pre-configured by Slemma

Vertica Server

Vertica 9.2.0

Slemma Overview

Slemma is a SaaS product for business intelligence.

For information about Slemma, visit the Slemma website. On the website, you will find tutorials and other resources to help you start learning about Slemma.

Install Slemma

To request a free trial of Slemma on the cloud or on premises, click TRY SLEMMA NOW on the Slemma website.

About the Client Driver

Slemma uses JDBC to connect to Vertica. Slemma provides the Vertica JDBC driver automatically with the product.

Note You do not need to download and install the Vertica JDBC driver to use Slemma with Vertica.

Connect Slemma to Vertica

  1. Log in to Slemma.

  2. Click the Data sources button, then click the plus sign (+) at the bottom of the page.

  3. Click Vertica to add it as a new data source.

  4. On the GENERAL tab, provide the connection information for Vertica.

  5. Before clicking the CHECK CONNECTION button, make sure that your database is either on a public network that is accessible from the internet or that your firewall is configured to allow the address 52.0.5.176 to connect to your server.

    If your database is on a private network and you do not want it to be accessed from the internet, you can set up an SSH tunnel for the connection to Vertica.

    Note If you do not enable an SSH tunnel for your private network, you will see this error when you click CHECK CONNECTION:

    :

    For private networks, follow these steps to enable an SSH tunnel:

    1. On the SECURITY tab, check Use SSH tunnel.

    2. On your Vertica Server machine, open a Linux terminal and execute the SSH tunnel command shown above.

    3. Return to the Slemma GUI and click the TEST button to verify that you can establish a connection to Vertica.
  6. Click the CHECK CONNECTION button.

  7. In the DATA REFRESH tab, you can schedule automatic data refresh or specify manual refresh.

    To refresh manually, change the value of Update rate to Manual.

    .

    For details, see Refreshing the Data.

  8. In the ADVANCED Tab, you can set JDBC connection properties for Vertica.

    For details, see JDBC Connection Properties in the Vertica documentation.

  9. Click the CONNECT button to create a connection to Vertica.

    Note A Vertica connection in Slemma is called a data source or an integration.

Visualize Vertica Data in Slemma

Follow these steps to create a dashboard that presents Vertica data in Slemma.

  1. To create a new dashboard, select Dashboards and click the plus sign (+) at the bottom of the page.

  2. Under Templates, click the plus sign (+) to create a new blank template.

  3. To create a chart in the dashboard, click Insert, then click Chart.

  4. Select your Vertica data source.

  5. To visualize data from a single table or view, select it from the list under Select a Table or View.

    To select data from multiple tables, select Write SQL Query under Create View.

    If you select Write SQL Query, the query editor opens.

    1. Write your query and click Run. If the query does not run properly, correct it and click Run again.
    2. When the query runs successfully, click Next.

  6. Choose the chart type. In this example, we have selected Simple table.

  7. To configure the table, select Add A FIELD for each field you want to include.

  8. To complete the visualization, click APPLY, then click DONE.

  9. If you want to save the visualization for future use, click the Save button.

For more details about building charts, see How to Build a Chart in the Slemma documentation.

Refreshing the Data

The Slemma engine caches data in cloud storage. When Slemma executes a query through the UI or creates a dataset for a chart, it loads the data as follows:

  1. Executes the query in the database.
  2. Stores the resulting dataset in the cache.
  3. Loads the dataset from the cache to the UI.

Slemma does not refresh the dataset unless you refresh it manually or configure automatic refreshes. The following refresh options are available:

  • Manually refresh a dashboard or dataset
  • Schedule automatic refresh for a dashboard or a dataset

Manual Refresh

Manually Refresh a Dashboard

  1. Right-click the dashboard and select Refresh now.

  2. On the Confirm dialog box, click OK.

Manually Refresh a Dataset

Right-click the dataset and select Refresh now.

All the charts based on this dataset are updated.

Automatic Refresh

When you configure Slemma to automatically refresh your Vertica data, Slemma performs the refresh at 00.00 local time every 24 hours by default. This behavior applies to all datasets created from a Vertica data source (integration).

Automatically Refresh a Dashboard

  1. Right-click the dashboard and select Schedule refresh.

    A list of all the integrations and datasets used in the dashboard displays.

  2. Select an integration or dataset.
  3. To refresh the data immediately, click Refresh now.

    To schedule the refresh, click Schedule refresh.

    Select the scheduling options.

  4. Repeat for each integration and dataset that you want to refresh.

Automatically Refresh a Dataset

  1. Right-click the dataset and select Schedule refresh.

  2. Change the scheduling settings.

Dynamic Filtering

The dynamic filtering feature in Slemma lets you filter data for display based on criteria that you specify.

With dynamic filtering, you can deploy the same dashboards and datasets for different purposes or for different users. For example, you could implement dynamic filtering to display only regional sales figures to individual sales reps, although all the sales data is present in the data source.

To learn how to implement dynamic filtering, see the Slemma documentation.

Known Limitations

Slemma supports four basic data types: String, Number, Date, and Geography. Slemma converts Vertica data types as follows:

  • Binary, Varbinary, and Long Varbinary are not supported. Slemma converts these data types to string.

  • TimeTz, Time, IntervalSeconds, and IntervalMonth are not supported by Slemma. Slemma displays blank values for these data types.

  • TimeStamp and TimeStampTz data types display the Date part of the value only. To display the hour and minute part of the value, change the Date level to Minutes in the Field setting. If you change the type from Date to String in the Field setting, the complete value for TimeStamp and the date and time values only for TimeStampTz are displayed.

  • Numbers that have more than 16 digits are rounded off. For example:
    • 79228162514264337593543950335 is rounded to 7.922816251426434e+26

    • 7922816251426433759354395.0335 is rounded to 7.922816251426434e+24
  • Trailing zeros are removed from decimal numbers. For example:

    • 1.0000000 is displayed as 1.
    • -1.0000000 is displayed as -1.
    • 0E-15 is displayed as 0.
  • Digits to the right of the decimal point are rounded off to two digits. For example:
    • 7.9228162514264337593543950335 is displayed as 7.92.

    • 79228162514264.337593543950335 is displayed as 79228162514264.34.

  • Slemma supports the Float data type, but rounds off very large float values. For example:
    • 1.797693134862312+308 is read correctly but displayed as Infinity

Data type conversion in Slemma is summarized as follows:

Vertica Data Type Data Type in Slemma
BOOLEAN String
Char String
VarChar String
LongVarChar String
Integer Number
Decimal Number
Float Number
Date Date
Time String
TimeStamp Date
TimeTZ String
TimeStampTZ Date
Interval Day To Second String
Interval Year To Month String
Binary String
VarBinary String
LongVarBinary String
UUID String

For More Information