Share this article:

Vertica Integration with MATLAB: 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 MATLAB as of May 2019.

MATLAB Overview

MATLAB is a high-performance platform for technical computing developed by MathWorks. In a Business Intelligence environment you can use it for data analysis, exploration, and visualization. With MATLAB you can import data from a source database, transform data using available functions, and write data to a target database.

MATLAB is available on Windows and Linux and uses JDBC to connect to your Vertica database.

Vertica and MATLAB Software Versions

This document provides guidance using the software listed as follows:

Software Version
Partner Product

MATLAB 9.8.0.1417392

Partner Product OS Platforms

CentOS Linux release 7.4.1708 (Core)

Vertica Client

Vertica JDBC driver 10.0.0

Vertica Server

Vertica Server 10.0.0

Note Instructions in this document are based on installing and running MATLAB in a Linux environment using the trial version of the software.

Downloading and Installing MATLAB

To download and install MATLAB:

  1. Go to the MathWorks website.
  2. Click Get MATLAB at the top right hand side of the screen.

  3. Click Download a Trial.
  4. Login to your MathWorks account or create an account to continue with the download.

  5. Download and run the free trial of the software.

For instructions about installing MATLAB on Linux, follow the MATLAB user guide.

Installing the Vertica Client Driver

Before connecting MATLAB to Vertica, you must download and install the Vertica JDBC driver, as follows:

  1. Go to the Vertica Client Drivers page on Vertica.
  2. Download the Vertica JDBC driver that is compatible with the Vertica Server version that you are using.
  3. Place the JDBC jar file in any convenient location in your system. In this guide, we used Linux and placed the Vertica JDBC driver jar file at: home\MATLAB\R2020a\vertica-jdbc-10.0.0-0.jar

    Note To complete the installation of the Vertica JDBC driver, it is required to create a text file with the name javaclasspath.txt. This file should contain the location of the Vertica JDBC jar file and the Vertica default driver class. The location where the file should be created may vary based on your MATLAB installation. This location is given by the ‘prefdir’ command which must be executed in the MATLAB command window inside MATLAB desktop application.

    In this guide, we installed MATLAB under root user and the file location returned by ‘prefdir’ command is: ‘/root/.matlab/R2020b’. For details about javaclasspath.txt file and ‘prefdir’ command, see here.

    Steps 4 to 6 explain the additional configuration steps used in this guide.

  4. On a Linux terminal, navigate to the directory

    /root/.matlab/R2020b

  5. Create and edit the javaclasspath.txt file with the following information:

    Note The first line is the location of the Vertica JDBC jar file and the second one is the Vertica driver class.

    \home\MATLAB\R2020a\vertica-jdbc-10.0.0-0.jar 
    com.vertica.jdbc.Driver
  6. Save and close the javaclasspath.txt file.

Connecting MATLAB to Vertica

  1. From a Linux terminal, navigate to the directory where MATLAB is installed. For example:

    $ cd /usr/local/MATLAB/R2020b/bin/

  2. Launch MATLAB:

    $ ./matlab

    The MATLAB desktop application opens.

  3. In the top menu, click the APPS tab, and then click Database Explorer.

    The Database Explorer window opens.


  4. Click Configure Data Source and select Configure JDBC data source.

    The JDBC Data Source Configuration dialog appears:

  5. Enter your Vertica connection information:
    Name: Type your connection or data source name.
    Vendor: Select Other.
    Driver Location: Browse to the location of the Vertica JDBC jar file.
    Driver: Provide the driver class name. Vertica's driver class name is com.vertica.jdbc.Driver.
    URL: The database JDBC connection URL, for example:
    'jdbc:vertica://<Vertica_Server>:5433/<DB_Name>

    In the URL

  6. <Vertica_Server> is the IP or server name of the Vertica Server machine.
    5433 is the Port.
    <DB_Name> is the Vertica database name.
  7. Click Test. The Test Connection dialog box appears.

  8. Type your database credentials and click Test.

    A message appears indicating the connection is successful.

  9. Click OK. Click Save and close the dialog box.

    A JDBC connection to Vertica is now created.

  10. Close the Database Explorer and return to HOME in the MATLAB desktop application.

Reading or Importing Data from Vertica

To read or import data from Vertica:

  1. In the top menu of the MATLAB desktop application, click the APPS tab, and then click Database Explorer. The Database Explorer window opens.

  2. In the Database Explorer toolbar, click Connect and from the drop-down list, select the data source that you created in the Connecting MATLAB to Vertica section.

  3. Enter the database credentials and click Connect.

  4. Select the Catalog or database name, and Schema name from the drop-down list and click OK.

  5. In the Data Browser area in the left side of the screen, select the tables and columns from which you want to retrieve data.

    Note The corresponding SQL and query result can be seen simultaneously on the right side.

  6. Click Import Data in the Database Explorer toolbar.

    Import Data has three options to choose from:

    • Import Data allows you to store the query results in a MATLAB variable in the form of a table.

    • Generate SQL Query allows you to save the query statement in a file with .SQL extension.

    • Generate MATLAB Script allows you to save the query statement in MATLAB script format.

    In this document, we used the first option to import the query results into a MATLAB variable:

  7. Click Import.

  8. Close the Database Explorer and return to HOME in the MATLAB desktop application.

    To see the data stored in the variable, click the VARIABLE tab in the top menu of the MATLAB desktop application.

Writing Data into Vertica

The following is a basic example you can follow to write data into a Vertica table.

  1. Open the MATLAB desktop application. In the command window area, type the database function to enter the connection information to your Vertica database:

    Conn = database('datasource', 'username', ‘password’)

    In the database function

    datasource is the name of the Vertica JDBC connection.

    username is the username to connect to Vertica.

    password is the password to connect to Vertica.

    Note To create a new JDBC connection to Vertica, use the JDBC Data Source Configuration from the Database Explorer as described in Connecting MATLAB to Vertica.

  2. Create the table that you intend to write into Vertica as follows:

    • First, create workspace variables that hold the data:

      LastName = {'Sanchez';'Johnson';'Li';'Diaz';'Brown'};
      Age = [38;43;38;40;49];
      Height = [71;69;64;67;64];
      Weight = [176;163;131;133;119];
      BloodPressure = [124.93; 109.77; 125.83; 117.75; 122.80];
    • Then, use the table function that contains the workspace variables.

      Data = table(LastName, Age, Height, Weight, BloodPressure);

      For more information on table function, refer to this link.

  3. Specify the table in Vertica into which you want to insert the data:

    Tablename = 'public.Test_Table';

  4. Run the sqlwrite function to insert the data into the Vertica table specified above:

    sqlwrite(Conn, Tablename, Data);

    Note If the table doesn’t exist in Vertica, MATLAB creates the table and loads the data during the sqlwrite command execution. If the table already exists, data will be appended to the existing table.

Note You can also import data from a Vertica table into a variable such as ‘data_ variable’ by using Database Explorer’s ‘Import Data’ option. You can then execute the sqlwrite function in the following format to write this data into Vertica:

sqlwrite(conn, tablename, data_variable);

Troubleshooting

Review these known issues and their workarounds when connecting to Vertica using MATLAB.

MATLAB Console crashes when using JDBC connectivity

Issue: When using JDBC to connect to Vertica, MATLAB desktop application may close suddenly when reading data from a Vertica database table.

Solution: Do not use a single workspace file to import multiple tables. Use one workspace file per table.

MATLAB Console cannot find the JDBC driver

Issue: This issue may occur when you create and test a connection to Vertica. The content of the javaclasspath.txt file consists of the Vertica JDBC jar file name along with the absolute path where the driver is located in Linux and the Driver's class name. If the content of the javaclasspath.txt file is incorrect, the following error message is displayed because the driver is not found by the application.

Unable to find JDBC driver

Solution #1:

Check that the PATH and JAVA_HOME environment variables have been set up correctly.

Solution #2:

Run the javaclasspath command from the MATLAB command window area and ensure the Vertica JDBC jar file name, location, and driver class name are correct. Edit the javaclasspath.txt file if necessary. For more information on the javaclasspath.txt file, see Installing the Vertica Client Driver in this guide.

Solution #3:

Ensure Vertica JDBC jar file is owned by the user who installed MATLAB. The owner and group must have full permissions and others must have read and execute permissions only.

Known Limitations

Before using MATLAB, be aware of the following known limitations:

  • For CHAR, VARCHAR, and LONG VARCHAR data types, data is visible till 10000th position. The message "<Preview truncated at 10000 characters>" is displayed.

  • For TIME data type, milliseconds are rounded off during data display and truncated for data write operations.

  • For TIMETZ data type, milliseconds and timezone offset are truncated.

  • For TIMESTAMPTZ data type, timesone offset is not supported.
  • Binary, VARBINARY, and LONG VARBINARY data types are not supported during data load into the target table.

  • UUID data type is not supported.

  • INTERVAL SECOND and INTERVAL MONTH data types are not supported.

  • For INTEGER data type, MATLAB displays data correctly for up to 9 digits and loads till 16 digits by rounding it off. Beyond 16 digits, the following error is displayed

    “Row Rejected by Server.”

  • For DECIMAL data type, MATLAB displays data correctly up to 7 digits beyond which data is rounded off and displayed in exponential form. It loads data correctly up to 16 digits.

 

 

Share this article: