Vertica Integration with MATLAB: 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 MATLAB: Latest Versions Tested

Software Version
Partner Product

MATLAB 9.12.0.1884302 (Trial Version)
Partner Platform

Windows 2019 Standard Server
Vertica Client

Vertica JDBC driver 11.1.0
Vertica Server

Vertica Server 11.1.0

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.

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

MATLAB uses the JDBC client driver to connect to your Vertica database. To download and install the JDBC driver package:

  1. Go to the Vertica Client Drivers page on the Vertica website.
  2. Download the Vertica JDBC driver that is compatible with your Vertica server version.

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

  3. Place the JDBC jar file in a location on your system.

Connecting MATLAB to Vertica

  1. Open MATLAB from the installation directory or double-click the MATLAB desktop icon.

    The MATLAB desktop application opens.

  2. From the menu, click APPS > Database Explorer.

    The Database Explorer window opens.

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

    The JDBC Data Source Configuration dialog box appears:

  4. Enter the following 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>

    where

    5. <Vertica_Server> is the IP or server name of the Vertica Server machine.
    5433 is the Port.
    <DB_Name> is the Vertica database name.

  5. Click Test. The Test Connection dialog box appears.

  6. Type your database credentials and click Test.

    A message appears indicating the connection is successful.

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

    A JDBC connection to Vertica is now created.

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

Reading or Importing Data from Vertica

To read or import data from Vertica:

  1. From the menu, click APPS > 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 go 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 for 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.

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);

Known Limitations

Before using MATLAB, review the following known limitations:

  • 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 displayed.

  • BINARY, VARBINARY, and LONG VARBINARY data types are not loaded into the target table and an error message is displayed.

  • UUID data type is not displayed or loaded.

  • INTERVAL SECOND and INTERVAL MONTH data types are not displayed or loaded.

  • For INTEGER data type, data is displayed up to 9 digits and loaded up to 16 digits beyond which the error Row Rejected by Server is displayed.

  • For DECIMAL data type, data is displayed up to 7 digits beyond which it is displayed in exponential form. Data is loaded up to 16 digits beyond which the error Row Rejected by Server is displayed.

For More Information