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 February, 2018.

MATLAB Overview

MATLAB is a high-performance platform for technical computing. Though designed for engineers and scientists, MATLAB runs in an easy-to-use interface. 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 manages large datasets using clusters and the cloud to provide scalability.

Note Instructions in this document are based on installing and running the trial version of the software.

Vertica and MATLAB Software Versions

This document provides guidance using the software listed as follows:

Software Version
MATLAB Client

MATLAB 9.3.0.713579

MATLAB OS Platform CentOS 7.2.1511, Windows Server 2012 R2 Standard
Desktop Platform

Windows 10

Vertica Client

Vertica 9.0

Vertica Server

Vertica Server version 9.0

Server Platform CentOS 7.3

Install MATLAB

You need the following before proceeding with the installation:

  • Your email and MathWorks account password. Use these to log into your account during the installation process. If you do not have an account, create one here.
  • Appropriate permissions to install software on your system, as follows:
    • Windows: The Administrator user name and password.
    • Linux: The root password.

Follow the installation instructions to install MATLAB.

If you are installing on Linux you must run a Display Server Appllication on the Windows machine from where you access the Linux machine.

For example, this document shows enabling XMing in Putty and connecting to the Linux machine:


Install the Vertica Client Driver

MATLAB uses JDBC to connect on Linux and both JDBC and ODBC to connect on Windows.

Note This document uses JDBC on Windows for instructional purposes. Also note that the JDBC driver is not included in the Windows package.

Before connecting MATLAB to Vertica, you must download and install the Vertica client package that includes the appropriate drivers, as follows:

  1. Go to the Vertica Client Drivers page on MyVertica.
  2. Download the Vertica client drivers that match your operating system and the Vertica version that you are using.
  3. Follow the client installation instructions in the Vertica documentation.

Connect to Vertica on Windows

Before creating a connection, do the following to add the JDBC driver to the MATLAB static Java class path:

  1. Run the prefdir command from the MATLAB Command Window. This creates a file path to the MATLAB preferences folder on your computer. The file path can be whatever you want.
  2. Close MATLAB.
  3. Navigate to the preferences folder you created in Step 1 and create a file named javaclasspath.txt.
  4. Open javaclasspath.txt and add the full path from Step 1 to the database driver JAR file, for example:

    C:\vertica-jdbc-9.0.0-0.jar com.vertica.jdbc.Drive

  1. Restart MATLAB.

With MATLAB running do the following to create a connection to Vertica:

  1. In the MATLAB application, select the APPS tab, then select Database Explorer.
  2. Select New > JDBC. The JDBC Data Source Configuration dialog appears:


  3. Enter the following:

    Name: The data source name.
    Vendor: Select Other.
    Driver: The name of the JDBC driver to which you want to connect.
    URL: The database connection URL, for example:

    'jdbc:vertica://172.XX.XX.XXX:5433/<DB_Name>?MultipleActiveResultSets=1)', where:

    172.XX.XX.XXX is the Hostname
    5433is the Port
    <DB_Name>is the database name

    Username: The user name used to access the JDBC driver.
    Password: The password used to access the JDBC driver.
  4. Click Test to test the connection. The Test Connection dialog box appears:

  5. Click Connect. Upon a successful connection, the Database Explorer dialog box displays a message indicating the connection is successful.

You have created a connection to Vertica. Now do the following to read or import data from Vertica:

  1. Open the new data source in the Database Explorer.
  2. Select the schema from the dropdown menu. For example:

  3. Select the checkbox for a table and select the columns for which you want to retrieve data. For example:

  4. Click Import to store the data in MATLAB:



    The menu to the left of the Import button allows you to select in what format you want to store the table. This document uses Cell Array.
  5. To write data to Vertica, connect to the Vertica database using the data source name, user name, and password. For example:

    conn = database('testdb','com.vertica.jdbc.Driver','jdbc:vertica://172.XX.XX.XXX:5433/<DB_Name>?MultipleActiveResultSets=1'

  1. Create a cell array of column names for the database table in Vertica to which you are sending data, for example:

    colnames = {'DataTypeSet','ValueDesc','ValueColumn'};

  1. Define a target table to which you want to insert data:

    tablename = 'Test_Table';

  1. Run the following command to insert the data into the Vertica target table:

    datainsert(conn,tablename,colnames,Test_Table);


    where:

    conn stores the target database connection information
    tablename is the target table name
    colnames is the column name of the target table
    Test_Table is the source or cell array from which data is read.

Connect to Vertica on Linux

Before creating a connection to Vertica, you must install the Vertica .jar file on your Linux machine as follows:

  1. Run the prefdir command from the MATLAB Command Window. This creates a file path to the MATLAB preferences folder on your computer. This path can be whatever you want.

  2. Close MATLAB if it is running.
  3. Navigate to the preferences folder you created in Step 1 and create a file named javaclasspath.txt.
  4. Open javaclasspath.txt and add the full path from Step 1 to the database driver JAR file, for example:

    /root/.matlab/R2017a/vertica-jdbc-9.0.0-0.jar com.vertica.jdbc.Drive

  1. Restart MATLAB as follows:
    1. Access the MATLAB bin folder:

    $ cd /usr/local/MATLAB/R2017a/bin

    1. Run the following command to start MATLAB:

    $ ./matlab

With MATLAB running do the following to create a connection to Vertica:

  1. In the MATLAB application, select the APPS tab, then select Database Explorer:


  2. Select New > JDBC. The JDBC Data Source Configuration dialog appears:


  3. Enter the following:

    Name: The data source name.
    Vendor: Select Other.
    Driver: The name of the JDBC driver to which you want to connect.
    URL: The database connection URL, for example:

    'jdbc:vertica://172.XX.XX.XXX:5433/<DB_Name>?MultipleActiveResultSets=1', where:

    172.XX.XX.XXX is the Hostname
    5433is the Port
    <DB_Name>is the database name

    Username: The user name used to access the JDBC driver.
    Password: The password used to access the JDBC driver.
  4. Click Test to test the connection. The Test Connection dialog box appears:

  5. Click Connect. Upon a successful connection, the Database Explorer dialog box displays a message indicating the connection is successful.

You have now created a connection to Vertica. Now do the following to read or import data from Vertica:

  1. Open the new data source in the Database Explorer.
  2. Select the schema from the dropdown menu. For example:

  3. Select the checkbox for a table and select the columns for which you want to retrieve data. For example:

  4. Click Import to store the data in MATLAB:



    The menu to the left of the Import button allows you to select in what format you want to store the table. This document uses Cell Array.
  5. To write data to Vertica, connect to the Vertica database using the data source name, user name, and password. For example:

    conn = database('testdb','com.vertica.jdbc.Driver','jdbc:vertica://172.XX.XX.XXX:5433/<DB_Name>?MultipleActiveResultSets=1')

  1. Create a cell array of column names for the database table in Vertica to which you are sending data, for example:

    colnames = {'DataTypeSet','ValueDesc','ValueColumn'};

  1. Define a target table to which you want to insert data:

    tablename = 'Test_Table';

  1. Run the following command to insert the data into the Vertica target table:

    datainsert(conn,tablename,colnames,Test_Table);

    where:

    conn stores the target database connection information
    tablename is the target table name
    colnames is the column name of the target table
    Test_Table is the source or cell array from which data is read.

Troubleshooting

To minimize the risk of MATLAB crashing while using an JDBC connection:

  • Attempt to eliminate re-reads of the database by carefully selecting the table from which you are importing data.
  • Do not use a single workspace file to import multiple tables. Use one workspace file to import each table.

If you receive the following error message when using JDBC on Linux:

Unable to find JDBC driver

troubleshoot as follows:

  • Run the javaclasspath command from the MATLAB command line and check to ensure the jar file and driver name are correct. If not, or if either is missing, check to ensure the preferences folder and javaclasspath.txt are valid.
  • Do one or both of the following:
    • Run the following command from the MATLAB command line:

      getenv -JAVA_HOME

      If the output appears empty, set the JAVA_HOME environment variable on the Linux machine:

      export JAVA_HOME=/usr/lib/jvm

    • Run the following command from the MATLAB command line:

      getenv -PATH

      If the output appears empty, set the PATH environment variable on the Linux machine:

      export PATH=/usr/lib/jvm

  • Ensure the jar file has the following execution rights:


If you receive the following error:

error: terminate called after throwing an instance of 'std::runtime_error' what(): Unable to launch the MATLABWindow application Fatal Internal Error: std::exception: map::at

  • Run the following command from /usr/local/MATLAB/R2017b to determine the error type:

     

    ! bin/glnxa64/MATLABWindow

If you receive a

libXx.so.1 file is missing

error:

  • Install the file if necessary or locate the file on your system. Copy the file to /Path/ bin/glnxa64/.
  • Re-run the /Path/ bin/glnxa64/ command. If Windows opens, close the window and restart MATALAB.

Known Limitations

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

  • The JDBC connection is unstable and may lead to unexpected. application crashes. See Troubleshooting above for tips on minimizing this behavior.
  • MATLAB does not read NULL as null. It may be read as NaN, string ‘null’ depending on the table column’s definition.
  • MATLAB reads Interval Month and Interval Seconds as array. If you save and re-open the file MATLAB cannot read either datatype.
  • MATLAB does not support Timezone, UUID, and Binary, Varbinary and LongVarbinary data types.

 

 

Share this article: