NetSuite SuiteAnalytics Connect

This article details how to enable NetSuite SuiteAnalytics Connect on Matillion ETL. This requires you to download and configure the SuiteAnalytics Connect JDBC Driver, and configure the Database Query component to use the driver.

Warning

You do not use the Matillion ETL NetSuite Query connector with SuiteAnalytics Connect. Instead, you use the Database Query component, selecting NetSuite SuiteAnalytics Connect as the Database Type in that component. This article describes the steps you need to perform to enable that.

Prerequisites

Ensure that you have a NetSuite administrator account.
Ensure that the SuiteAnalytics Connect service is enabled.

Enable Netsuite SuiteAnalytics Connect on Matillion ETL

Download the SuiteAnalytics Connect JDBC driver

Log in to your NetSuite account.
On the homepage of your NetSuite account, under the Settings module, click Set Up SuiteAnalytics Connect.
Select Linux 64-bit from the drop-down.
Make a note of the following information on this page. You will need these values to configure the connector when you use it in a Matillion ETL job:
- SERVICE HOST
- SERVICE PORT
- SERVICE DATA SOURCE
- ACCOUNT ID
- ROLE ID
Click the Download button next to JDBC Driver. This will download a zip file that contains a file called NQjc.jar. This .jar file is what you will need to upload to Matillion ETL.

Create integration record

In your NetSuite account, click Setup → Integration → Manage Integrations → New.
Create a new integration record. Ensure that the following options are selected (checked):
- CLIENT CREDENTIALS (MACHINE TO MACHINE) GRANT
- SUITEANALYTICS CONNECT
Click Save.
Copy the following credentials displayed under Client Credentials:
- CONSUMER KEY / CLIENT ID
- CONSUMER SECRET / CLIENT SECRET
- APPLICATION ID
Warning

After you leave this page, CLIENT ID and CLIENT SECRET can't be viewed again. If you lose or forget these credentials, you will need to reset them to obtain new values.

Assign permissions to a user

In your NetSuite account, navigate to Setup → Users/Roles → Manage Users.
Select a user you wish to use for Matillion ETL jobs, or alternatively create a new user.
Click the Access tab and select GIVE ACCESS.
Under Roles, ensure that the role Data Warehouse Integrator is present, and add the role if not.

Note

Data Warehouse Integrator is a default NetSuite role that usually has all the necessary permissions for your data extract needs. Alternatively, you can create your own role and fine-tune the permissions.

Add the SuiteAnalytics driver to Matillion ETL

SSH onto your Matillion ETL virtual machine.
Switch user to root:
```
sudo su -
```
Navigate to the Matillion ETL config folder:
```
cd /usr/share/emerald/WEB-INF/classes
```
Edit the JDBC properties file:
```
vi jdbc-providers.properties
```

Add the following block of JSON to the end of the file:

{
    "name":  "NetSuite SuiteAnalytics Connect",
    "driver": "com.netsuite.jdbc.openaccess.OpenAccessDriver",
    "url" : "jdbc:ns://<serverHost>:<port>;ServerDataSource=<serverDataSource>;encrypted=1;CustomProperties=(AccountID=<accountId>;RoleID=<roleId>);NegotiateSSLClose=false",
    "fetchSize" : "100000",
    "limit": "top-n",
    "allowUpload" : "true",
    "defaultProperties" : {"loginTimeout": "60"}
}

Save the file.
Use the following command to confirm the JSON file still has a valid format
```
cat jdbc-providers.properties | jq "."
```
Restart the Matillion ETL server:
```
sudo service tomcat restart
```
Wait for the service to restart, and then log in to Matillion ETL as an admin user.
Click Admin → Manage Database Drivers.
Click New to create a new Jar Group, and select NetSuite SuiteAnalytics Connect from the Driver drop-down.
Click Browse and select the NQjc.jar file you downloaded in Download SuiteAnalytics Connect JDBC driver, above.
Click Test, and then click OK.

Generate a client credentials certificate

SSH onto your Matillion ETL virtual machine (VM).
Switch user to root:
```
sudo su -
```
Create a new folder to store the certificate:
```
mkdir /usr/share/netsuite-client
```
In this new folder, generate a certificate and private key with this command:
```
openssl req -x509 -newkey rsa:4096 -sha256 -keyout auth-key.pem -out auth-cert.pem -nodes -days 730
```
Skip each of the questions by pressing Enter.

Note

This certificate will last 2 years, which is the maximum NetSuite will allow.
Change ownership of the new folder and its contents to the tomcat user:
```
chown -R tomcat:tomcat /usr/share/netsuite-client
```
Copy the file auth-cert.pem from the VM to your desktop. Alternatively, copy and paste the entire contents to a text file on your desktop by copying the output of this command:
```
cat /usr/share/netsuite-client/auth-cert.pem 
```

Upload the certificate to NetSuite

Log in to your NetSuite account.
Navigate to Setup → Integration → OAuth 2.0 Client Credentials (M2M) Setup.
Click Create New.
In the Entity drop-down, select the user that was configured in Assign permissions to a user, above.
In the Role drop-down, select Data Warehouse Integrator or alternatively a custom role you have created for this.
In the Application drop-down, select the integration record that was created in Create integration record, above.
For the Certificate, upload the auth-cert.pem file that was saved on your desktop in Generate a client credentials certificate, above.
Click Save, then take a note of the CERTIFICATE ID of the new mapping you just created.

Matillion ETL job setup

This section gives an example of creating an orchestration job in Matillion ETL that uses SuiteAnalytics Connect through the Database Query component. This assumes you are familiar with the principles of creating and using jobs in Matillion ETL.

Note

You may wish to store some confidential information in a cloud secret (AWS, Azure, GCP). In the example job given here, we store client_id and certificate_id as key-value pairs in an AWS secret. The values for these were obtained during the setup steps described previously.

Configure job variables

Note

You could use environment variables instead of job variables. This may be a better option in some use cases, but in this example we are using only job variables. For details of variable use, read Variables.

Create a new orchestration job and on the Project menu click Variables → Manage Job Variables. Add the following job variables. Note that some are optional.

Name	Type	Behavior	Visibility	Value
jv_aws_region	Text	Copied	Public	(Optional) The region where you created your AWS Secret.
jv_aws_secret_name	Text	Copied	Public	(Optional) The name of the AWS Secret where you stored client_id and certificate_id.
jv_debug_mode	Numeric	Copied	Public	Set to `1` to print information to the task monitor for debugging during job development.
jv_netsuite_account_id	Text	Copied	Public	Obtained when you downloaded the SuiteAnalytics Connect JDBC driver during setup.
jv_netsuite_cert_filepath	Text	Copied	Public	File path to the private key file created when you generated a client credentials certificate during setup. For example, `/usr/share/netsuite-client/auth-key.pem`.
jv_netsuite_oauth_token	Text	Copied	Public	Leave blank, as this will be populated at runtime.
jv_netsuite_role_id	Text	Copied	Public	Obtained when you downloaded the SuiteAnalytics Connect JDBC driver during setup.
jv_netsuite_service_data_source	Text	Copied	Public	Obtained when you downloaded the SuiteAnalytics Connect JDBC driver during setup.
jv_netsuite_service_host	Text	Copied	Public	Obtained when you downloaded the SuiteAnalytics Connect JDBC driver during setup.
jv_netsuite_service_port	Text	Copied	Public	Obtained when you downloaded the SuiteAnalytics Connect JDBC driver during setup.

Configure a Python component

Add a Python Script component to the orchestration job, and add the following code to the script.

import requests
import logging
from pathlib import Path
import datetime
import jwt # PyJWT
import boto3

# NETSUITE VARIABLES
ACCOUNT_ID = jv_netsuite_account_id
URL_ACCOUNT_ID = ACCOUNT_ID.lower().replace("_","-")
CERTIFICATE_KEY_FILE = Path(jv_netsuite_cert_filepath)
GRANT_TYPE = "client_credentials"
CLIENT_ASSERTION_TYPE = 'urn:ietf:params:oauth:client-assertion-type:jwt-bearer'
TOKEN_ENDPOINT_URL = f"https://{URL_ACCOUNT_ID}.suitetalk.api.netsuite.com/services/rest/auth/oauth2/v1/token"
CONNECT_ENDPOINT_URL = f"https://{URL_ACCOUNT_ID}.connect.api.netsuite.com/services/rest/auth/oauth2/v1/token"
SCOPES = ['suite_analytics']

# AWS VARIABLES
AWS_SECRET_NAME = jv_aws_secret_name
AWS_REGION_NAME = jv_aws_region

def debugLog(message):
    if jv_debug_mode == 1:
        timestr = datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
        print(timestr,message)

# CREATE AWS SECRETS MANAGER CLIENT
session = boto3.session.Session()
client = session.client(
    service_name='secretsmanager',
    region_name=AWS_REGION_NAME
)

# GET VALUES FROM SECRET MANAGER
get_secret_value_response = client.get_secret_value(SecretId=AWS_SECRET_NAME)

if 'SecretString' in get_secret_value_response:
    secret_data = get_secret_value_response['SecretString']
else:
    secret_data = get_secret_value_response['SecretBinary']

secretData_dict = json.loads(secret_data)    

CLIENT_ID = secretData_dict["client_id"]
debugLog("Client Id: " + CLIENT_ID)
CERTIFICATE_ID = secretData_dict["certificate_id"]
debugLog("Certificate Id: " + CERTIFICATE_ID)

# BUILD JWT TOKEN SIGNED WITH PRIVATE KEY FOR NETSUITE OAUTH
now = datetime.datetime.now()
iat = now.timestamp()
exp_td = now + datetime.timedelta(hours=1)
exp = exp_td.timestamp()

payload = {
    'iss': CLIENT_ID,
    'scope': SCOPES,
    'aud': CONNECT_ENDPOINT_URL,
    'iat': iat,
    'exp': exp,
}

private_key = CERTIFICATE_KEY_FILE.read_bytes()
jwt_assertion = jwt.encode(payload, private_key, algorithm="PS256", headers={'kid': CERTIFICATE_ID})

# SEND JWT TOKEN TO OBTAIN OAUTH 2.0 ACCESS TOKEN  
data = {
    'grant_type': GRANT_TYPE,
    'client_assertion_type': CLIENT_ASSERTION_TYPE,
    'client_assertion': jwt_assertion,
}

resp = requests.post(TOKEN_ENDPOINT_URL, data=data)
data = resp.json()
debugLog(data)

context.updateVariable('jv_netsuite_oauth_token', data["access_token"])

This code will connect to AWS Secrets Manager to obtain the Client ID and Certificate ID. Alternatively, you can skip this step by instead saving the client ID and certificate ID as hard-coded values in the script or as additional job variables. This option is more simple but less secure than using AWS Secrets Manager.

At runtime, the script will place an access token inside the jv_netsuite_oauth_token variable we defined in advance. While developing the job, set the jv_debug_mode variable to 1 so you can see the value of the access token printed when you run the script. You can then copy and paste this value as the default value of the jv_netsuite_oauth_token variable. This token will expire after one hour, however, and will have to be regenerated.

Configure a Database Query component

Add a Database Query component to the orchestration job after the Python Script component.
Select NetSuite SuiteAnalytics Connect from the Database Type drop-down.

Note

If you don't see this option, it means something went wrong during the add SuiteAnalytics driver to Matillion ETL step of the setup.

Add the following to the Connection URL property:

jdbc:ns://${jv_netsuite_service_host}:${jv_netsuite_service_port};ServerDataSource=${jv_netsuite_service_data_source};encrypted=1;CustomProperties=(AccountID=${jv_netsuite_account_id};RoleID=${jv_netsuite_role_id};StaticSchema=1;OAuth2Token=${jv_netsuite_oauth_token});NegotiateSSLClose=false

For Username and Password put dummy values, for example "dummy" and "dummy".
Choose a table to load using either Advanced Mode or Basic Mode. If you have correctly configured your access token, you should be able to use the Data Source selector in Basic Mode, or you should be able to see Metadata Explorer in the SQL Query editor in Advanced Mode.

Tips for working with Netsuite SuiteAnalytics Connect

Don't use `SELECT *` for wide tables

If your table has over 1000 columns, NetSuite may hang during the query. To avoid this, you have to explicitly name the columns you want to select. For more information, read this NetSuite document.

Don't extrapolate table loading speeds

All tables load at different speeds due to width and custom columns. Therefore, you can't extrapolate loading speeds from one table to another. Tables with custom columns may load more slowly because NetSuite is doing a JOIN behind the scenes.

Make metadata-driven jobs

Metadata can be obtained by loading from the NetSuite system tables.

You can iterate over the tables listed in oa_tables.

You can store column names in a grid variable by loading from oa_columns.