Add a Database Driver and Connect Your Data

Owned by Charles Bishop (Deactivated)
Last updated: May 19, 2022 by Juliane Wetzel

You have a lot of your data stored within a database that you need to analyze. This guide shows you all the steps needed to connect the MSSQL database database to Datameer. After you have set up the database connection you can then import your data so that it is ready for analysis. The Datameer user must have administration privileges. 

Preventing Security Issues 

INFO

Datameer Admins who add a new database driver must be aware of malicious driver files that can attack Datameer X. To prevent adding dangerous driver jar files, obtain the required database driver files only from safe sources. 

Downloading Database Drivers

  1. Download the correct database drivers from the driver source. This example uses the Microsoft SQL Server driver.
    Download: Microsoft SQL Server JBDC Driver
    If you are on a Mac or Linux, download sqljdbc_3.0.1301.101_enu.tar.gz and continue on to step 2. 

    If you are using Windows, download sqlidbc_3.0.1301.101_enu.exe and skip to Step 3.

  2. Once you downloaded the correct database driver to the computer, extract the tar file that you have downloaded. 

    1. Open the terminal. 

    2. Open the folder where the database driver was downloaded: cd /Users/<user name>/Downloads

    3. Extract the tar file with the following command: tar -xzf sqljdbc_3.0.1301.101_enu.tar.gz 

  3. Only if you use Windows: After the executable database driver file is downloaded, double-click the file to run it.

Adding the Database Driver to Datameer

  1. Open the Datameer instance and click the Admin tab.
  2. Select Database Drivers.
     
  3. Click New.
     

  4. Name the driver MSSQL. 

  5. Select MsSql from the Database Driver Template menu and add the driver class and connection pattern.

  6. Click Choose File to upload the jar file you previously extracted. This is named sqlidbc4.jar and is in the folder where it was extracted.

    JDBC Driver Classes are used to load the proper driver into the Java session. (Note, some class path drivers end with a period (.) Check your distribution documentation for details.)

    Connection Pattern is a URL representation for the database.

  7. Click Save.

Configuring the JDBC Import

By default, Datameer sets the JDBC isolation level to TRANSACTION_READ_COMMITTED.

It can be configured by setting the following parameter in the das-job.properties file:

<INSTALLDIR>/conf/das-job.properties
## jdbc import settings
das.jdbc.import.transaction-isolation=TRANSACTION_READ_COMMITTED

This property can be overridden at a job level by adding the parameter to the Custom Properties of a Data Import from a Database when ingesting the data. The parameter can also be set on the connection itself to ensure that the sample data is read successfully.

Find more information about this in Configuring Datameer.

Creating a Connection and Import Job with Microsoft SQL Server

Refer to the MSSQL connector instructions.