Step By Step Process to Implement Assing JDBC Driver to Matillion

Have an Issue while Adding JDBC Driver to Matillion for Connecting Net-suite?

Finally, we added the Jdbc driver and were able to Connect Database Query Component to Net-suite.

PREREQUISITE:-

1. JDBC(type-4 ) Driver should be installed in your system (All latest drivers are type-4).
2. PUTTY Should be installed in your system.
3. AMAZON S3 instance with PPK KEY.

DOWNLOADING JDBC DRIVER

I am taking NETSUITE JDBC driver for example:

On the home page of Netsuite, you will find the settings option and click on the Set Up SuiteAnlalytics Connect.

After going to the above link you will get an option for Driver download.

After downloading the JDBC driver you will find a few files, This will be in the form of one or more .jar files that you can download. The download package may be a .zip or .tgz file, in which case you will need to unzip or decompress it to extract the individual .jar files.

 

CONVERTING PEM KEY TO PPK KEY

Open putty keygen from your machine

Load the PEM file and hit the save private key option, your PEM key will get converted into a PPK key and get downloaded to your machine.

 

CONFIGURING MATILLION ETL:-

The list of JDBC drivers that you can see in the Database Type property of the Database Query component is governed by a single configuration file.

1. To add a new entry to this list, you will need to log onto the running Matillion ETL instance and edit the file. Use an SSH client to connect to the instance, access the root user, and locate the file entitled /user/share/emerald/WEB-INF/classes/JDBC-providers.properties:

For performing step-1 we need to install PUTTY and use an SSH client to connect to the instance.

USING PUTTY to connect to the instance

Give your login address which you will get in instance details in Amazon S3

• Then go to Connection->Auth and browse your S3 instance PPK key.
• 

• Then you will get a login page in Putty
• 

• Give centos and press enter.
• After logging in you will get this

• 
• Run this below command
• sudo us –
• cd /usr/share/emerald/WEB-INF/classes
• vi JDBC-providers.properties
• The file is made up of a single JSON array, with one entry per supported database. Carefully add a new entry, using any text editor, remembering to end the new entry with a comma if it’s partway down the list. Follow the syntax of one of the existing entries and set values for:
  • name– the display name
  • driver– the Java class name of the driver, which should be in the vendor’s documentation
  • URL– a sample URL, used for documentation only
  • allow upload– always set this to true
  • fetch size– set this to 500 initially, but the value can be tuned
  • limit– this is a string that Matillion uses internally to create an SQL command for sampling, accepted values include:
    • fetch-first-n– uses a JDBC loop (this is the best default option)
    • limit-inline– uses the LIMIT keyword
    • limit-outer– uses LIMIT outside a nested SELECT
    • Downum– uses a ROWNUM rows top (Oracle style)
    • top-n– uses the TOP keyword (SQL Server style)

3. Depending on the driver, additional properties may also be set including:
   • auto-commit– normally true, but can be set to false
   • defaultProperties– another JSON array with properties that are meaningful to the driver in use
4. I have used the below syntax and pasted in putty.

{“name” : “Oracle_Netsuite”,

“driver” : “com.netsuite.jdbc.openaccess.OpenAccessDriver”,

“url” : “jdbc:ns://<NETSUITE ACCOUNT ID>.connect.api.netsuite.com:1708;ServerDataSource=NetSuite.com;encrypted=1;CustomProperties=(AccountID=< NETSUITE ACCOUNT ID>;RoleID=3)”,

“fetchSize” : “500”,

“limit” : “top-n”,

“allowUpload” : true  }

5. Once you have finished editing the file, it’s worth checking that the JSON syntax is still:

cat JDBC-providers.properties | jq “.”

 

Upload the JAR files into Matillion ETL

1. Once Matillion has restarted, click Admin→ Manage Database Drivers. This should open a dialog that allows .jar files to be uploaded for all of the JDBC drivers which have the allow upload property set to true.
2. Upload the .jarfile(s) one at a time, remembering to include any dependency .jar
3. Once all the .jarfiles are uploaded, everything required to begin to use the new JDBC driver is in place.

Restart Matillion

After editing the JDBC providers. properties file, the Matillion ETL instance must be restarted. Wait a few minutes while the new configuration comes into effect.

======================================================================================

After restarting the Matillion go to the Database-query Component in mapping and go to Properties>database type.

Select your newly created database type (in my case it is oracle-NetSuite)

Then give the URL in the connection URL option (same as given in creating oracle-NetSuite in putty).