How to Build an ETL App for NetSuite SuiteAnalytics Data in Python with CData Connect AI

The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData Connect AI Python SDK and the petl framework, you can build NetSuite SuiteAnalytics-connected applications and pipelines for extracting, transforming, and loading NetSuite SuiteAnalytics data. This article shows how to connect to Connect AI and use petl to extract, transform, and load NetSuite SuiteAnalytics data.

The Connect AI Python SDK (cdata-connect-ai) is a DB-API 2.0 (PEP 249) compliant client, so petl can read directly from the SDK connection with etl.fromdb. There is no driver to install per source: connect with a Personal Access Token and build your pipeline.

Connect to NetSuite SuiteAnalytics in Connect AI

CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.

1. Log into Connect AI, click Sources, and then click Add Connection

2. Select "NetSuite SuiteAnalytics" from the Add Connection panel

3. Enter the necessary authentication properties to connect to NetSuite SuiteAnalytics.

   Prerequisites

   Before you can connect to NetSuite SuiteAnalytics, you must set up SuiteAnalytics Connect in your NetSuite account:

   1. Enable the Connect Service feature.

      1. Ensure that your Account Administrator has enabled your Account and Role with the Connect Service feature.

      2. Navigate to Setup > Company > Enable Features.

      3. Click the Analytics tab and check the SuiteAnalytics Connect box.

   2. Add the SuiteAnalytics Connect permission to an existing Role, and note the Role ID for later.

   3. Find the Settings portlet on your home page and click Set Up SuiteAnalytics Connect, then click Your Configuration to view your service host and account ID. These settings map directly to the Server and Account Id properties.

   Add the NetSuite SuiteAnalytics Connection

   1. Open the Sources page of the Connect Cloud dashboard.

   2. Click Add Connection and select NetSuite SuiteAnalytics from the list of connectors.

   3. Enter a Connection Name of your choice.

   Authenticate to NetSuite SuiteAnalytics

   Set the following required properties:

   • Server: The Service Host value you found when setting up SuiteAnalytics Connect.

   • Account Id: The Account ID value you found when setting up SuiteAnalytics Connect.

   • Role Id: The internal ID of the login role you granted the SuiteAnalytics Connect permission.

   Next, choose one of the supported authentication methods:

   Basic authentication

   • User: The username you use to authenticate to your NetSuite account.

   • Password: The password associated with that account.

   Token-based authentication

   • Consumer Key and Consumer Secret: Generated when you create your integration record in NetSuite.

   • Token Key and Token Secret: The access token and secret generated for that integration.

   After you enter your credentials, click Save & Test to validate the connection.

   
4. Click Save & Test
5. Navigate to the Permissions tab and update the user-based permissions.

Generate a Personal Access Token (PAT)

The Python SDK authenticates to Connect AI with your account email and a Personal Access Token (PAT). It is best practice to create a separate PAT for each application to maintain granularity of access.

1. Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
2. On the Settings page, go to the Access Tokens section and click Create PAT.
3. Give the PAT a name and click Create.
4. The PAT is only visible at creation, so copy it and store it securely.

Install Required Modules

Install the SDK and the petl framework using the pip utility:

pip install cdata-connect-ai
pip install petl

Build an ETL App for NetSuite SuiteAnalytics Data in Python

Once the required modules are installed, you are ready to build the ETL app. Code snippets follow, but the full source code is available at the end of the article.

First, import the modules and connect to Connect AI with your account email and PAT:

import petl as etl
import cdata_connect_ai

conn = cdata_connect_ai.connect(
    username="[email protected]",
    password="<your_pat>",
)

Create a SQL Statement to Query NetSuite SuiteAnalytics

Use SQL to create a statement for querying NetSuite SuiteAnalytics. In this article, we read data from the Account entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, SuiteAnalytics1).

sql = (
    "SELECT AnnualRevenue, NumberOfEmployees "
    "FROM [SuiteAnalytics1].[SuiteAnalytics].[Account] "
    "WHERE Industry = 'Life Sciences'"
)

Extract, Transform, and Load the NetSuite SuiteAnalytics Data

With a connection and query in hand, use petl to extract, transform, and load the NetSuite SuiteAnalytics data. In this example, we extract NetSuite SuiteAnalytics data, sort the data by the NumberOfEmployees column, and load the data into a CSV file.

table1 = etl.fromdb(conn, sql)

table2 = etl.sort(table1, 'NumberOfEmployees')

etl.tocsv(table2, 'account_data.csv')

NetSuite SuiteAnalytics is a read-only source in Connect AI, so this pipeline can extract and transform NetSuite SuiteAnalytics data but not load rows back. Close the connection when the extract is complete:

conn.close()

With the CData Connect AI Python SDK, you can work with NetSuite SuiteAnalytics data just like you would with any database, including direct access to data in ETL packages like petl.

More Information and Free Trial

Now you can pipe live NetSuite SuiteAnalytics data through petl using the CData Connect AI Python SDK. For more information on connecting to NetSuite SuiteAnalytics (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live NetSuite SuiteAnalytics data in Python.

Full Source Code

import petl as etl
import cdata_connect_ai

conn = cdata_connect_ai.connect(
    username="[email protected]",
    password="<your_pat>",
)

sql = (
    "SELECT AnnualRevenue, NumberOfEmployees "
    "FROM [SuiteAnalytics1].[SuiteAnalytics].[Account] "
    "WHERE Industry = 'Life Sciences'"
)

table1 = etl.fromdb(conn, sql)

table2 = etl.sort(table1, 'NumberOfEmployees')

etl.tocsv(table2, 'account_data.csv')
conn.close()