Skip to content

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.

You may find that NetSuite Query data retrieval from a single table is limited to 100,000 rows. To retrieve more than 100,000 rows, you should use the Database Query component, selecting NetSuite SuiteAnalytics Connect as the Database Type in that component, and ensure that SuiteAnalytics Connect is enabled on your NetSuite account.

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

  1. Log in to your NetSuite account.
  2. On the homepage of your NetSuite account, under the Settings module, click Set Up SuiteAnalytics Connect.
  3. Select Linux 64-bit from the drop-down.
  4. 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
  5. 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

  1. In your NetSuite account, click Setup → Integration → Manage Integrations → New.
  2. Create a new integration record. Ensure that the following options are selected (checked):
    • CLIENT CREDENTIALS (MACHINE TO MACHINE) GRANT
    • SUITEANALYTICS CONNECT
  3. Click Save.

  4. 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

  1. In your NetSuite account, navigate to Setup → Users/Roles → Manage Users.
  2. Select a user you wish to use for Matillion ETL jobs, or alternatively create a new user.
  3. Click the Access tab and select GIVE ACCESS.

  4. 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

  1. SSH onto your Matillion ETL virtual machine.

  2. Switch user to root:

    sudo su -

  3. Navigate to the Matillion ETL config folder:

    cd /usr/share/emerald/WEB-INF/classes

  4. Edit the JDBC properties file:

    vi jdbc-providers.properties

  5. 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"}
}

  6. Save the file.

  7. Use the following command to confirm the JSON file still has a valid format

    cat jdbc-providers.properties | jq "."

  8. Restart the Matillion ETL server:

    sudo service tomcat restart

  9. Wait for the service to restart, and then log in to Matillion ETL as an admin user.

  10. Click Admin → Manage Database Drivers.
  11. Click New to create a new Jar Group, and select NetSuite SuiteAnalytics Connect from the Driver drop-down.
  12. Click Browse and select the NQjc.jar file you downloaded in Download SuiteAnalytics Connect JDBC driver, above.
  13. Click Test, and then click OK.

Generate a client credentials certificate

  1. SSH onto your Matillion ETL virtual machine (VM).

  2. Switch user to root:

    sudo su -

  3. Create a new folder to store the certificate:

    mkdir /usr/share/netsuite-client

  4. 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.

  5. Change ownership of the new folder and its contents to the tomcat user:

    chown -R tomcat:tomcat /usr/share/netsuite-client

  6. 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

  1. Log in to your NetSuite account.
  2. Navigate to Setup → Integration → OAuth 2.0 Client Credentials (M2M) Setup.
  3. Click Create New.
  4. In the Entity drop-down, select the user that was configured in Assign permissions to a user, above.
  5. In the Role drop-down, select Data Warehouse Integrator or alternatively a custom role you have created for this.
  6. In the Application drop-down, select the integration record that was created in Create integration record, above.
  7. For the Certificate, upload the auth-cert.pem file that was saved on your desktop in Generate a client credentials certificate, above.
  8. 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

  1. Add a Database Query component to the orchestration job after the Python Script component.

  2. 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.

  3. 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

  4. For Username and Password put dummy values, for example "dummy" and "dummy".

  5. 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.

© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement