CloverETL is a powerful tool that can help you to work with data from different sources. Today we take a look on how CloverETL can help you getting data from NetSuite. Although NetSuite is a powerful and extensive platform, there are cases when you need to use your data as part of a broader mix somewhere outside of it. Let's say you're combining several data sources – NetSuite being one of them – into a single reporting database. However, this might get tricky, especially in the case of cloud-hosted services. To do the job, you’ll need some ETL tool, like CloverETL, and specific cloud connectors to all the data sources.
Using SuiteTalk API
In the following example, I'll demonstrate by searching through an “Employees List” using SuiteTalk – a SOAP-based NetSuite API that offers a wide range of operations for retrieving and storing data.
To use Web Services in CloverETL we will use the generic WebServiceClient component and configure it to work with SuiteTalk API. WebServiceClient takes the WSDL of the service and lets you visually configure the operation and map data to the Request and from the Response. This makes using ugly SOAP structures much simpler. When building connectors to modern cloud data sources, you often need to use multiple WebServiceClient components in sequence to retrieve service URLs, authentication tokens, issue search query, retrieve resulting data, etc.
Employee Search Example
To show how you can build connectors in CloverETL, we're going to make a new component that searches through an employee list in NetSuite using email addresses. No coding, just a few clicks. Here's what we're building:
You can see we have a new component here – let's call it NetSuiteEmployeeSearch – with one simple configuration parameter for the search term ("Email Search (contains)") and three authentication credentials. It will issue a search and then fetch the results in a nice and easy-to-handle format.
NetSuiteEmployeeSearch is actually a subgraph that takes care of talking to the SuiteTalk API and retrieving data from it, while hiding the underlying details of the operations and presenting only the important settings to the end user.
Usually “connectors” like this one present a great opportunity for using subgraphs. A subgraph performs functions that you want to use more than once, only varying the configuration slightly in each instance. Most importantly, you want the connector to present a clean interface and to be simple to use.
Let's take a peek inside.
The Connector Subgraph
Inside the subgraph, we need to make two calls to SuiteTalk. First, we have to get our data center URL. That's because since September 2012, NetSuite has been hosting customer accounts on multiple data centers. It's best to ask for a link first even though you might already know where your data is being hosted. The second step is the actual call to the 'search' operation of the API.
Try It Yourself
You can download the example and customize it to your needs. Getting it to work is easy:
- Download NetSuite Example Project
- Import project into your Designer.
- Open graphs/NetSuite-Search.grf
- Double-click NetSuiteEmployeeSearch
- Set NetSuite Auth Email, Password, and Account ID parameters. If you forget to set these properly, the graph WILL finish OK but won't produce any data.
- Set Email Search (contains) to whatever substring you want to search for (e.g. “wolfeelectronics.com”)
- That's it! Now run the graph.
- Right-click the edge and click View data to see the results.
Dissecting the Example
Now let's look at some of the inner workings of the connector. Don't be scared by the configuration options of the two main WebServiceClient components when you see it for the first time! Luckily, there are useful GUIs for all the ugly XML SOAP structures and settings.
Here are a few important tips on working with the connector:
I strongly recommend using a local copy of the SuiteTalk WSDL – using the remote one can be painfully slow. (There's a copy under data-in folder already).
We use NetSuite API operations called “getDataCenterUrls” for getting your data center link and “search” that returns records based on the type of searchRecord (see Request Body structure below).
Request Body structure
We need to create the payload for the API calls. Fortunately, for everything there's a template. For data center URL, you just use the Generate button and fill in the account ID as needed. The search request is a little trickier but using customized generation makes it fairly straightforward.
Generating right search
In order to generate the right search request, use Generate > Customized Generation and then right-click “searchRecord” and Select subtype. Once you select the search type (EmployeeSearchBasic), click OK. Now you can expand searchRecord and you will be able to select the field you want to use as search criteria (email>searchValue). Keep in mind, that you can't expand searchRecord until you've “casted” it to the right search type using Select subtype. (It's probably easiest to check the image below.)
Once you've done the above step, CloverETL generates a corresponding piece of XML code for you. Don't forget to change the search operator (“contains” in this case). The only place where I was able to find a list of valid operators was on an actual search form in the NetSuite web interface.
The parameter $SEARCH_TERM is taken from the subgraph user configuration ("Email Search (contains)"). It is marked as a public parameter, and so is available for the user to modify.
Authentication is pretty simple – just passing the three values (email, password, account ID) into the Request Header structure (no login step or complicated auth as for example, as in the case of the Facebook API). The parameters ($NETSUITE_*) have the same story as $SEARCH_TERM – they are defined as public, and users of the subgraph can provide them as part of the component configuration or just edit them directly in the netsuite.prm file.
The API operations return a structure response which needs to be mapped onto the output. Getting what you need might require a little bit of experimenting but if you're familiar with the XMLExtract interface, it's going to be fairly easy. Keep in mind that just like with Request mapping, you can "cast" (Select subtype) items in the left tree to get to the desired structure – see images below.
There is a neat trick you can use to create metadata directly from the Response mapping. Just select any metadata as the output (or blank) and drag&drop fields you want from the middle column, and you’re done!
Notice that the second WebServiceClient component is in phase 1, not 0. This is super important as it ensures the two calls are made in sequence, not in parallel.
One last thing
Have you noticed the neat NetSuite icon of the subgraph? You can define custom icons and category colors to subgraphs to make them more visually appealing. When editing the subgraph, click anywhere in the blank space and switch to Properties tab. You can set Category and icon paths (PNG) there.
Creating You Own Version
You can use this example as a basis for other operations that you might want to run on NetSuite.
- Keep the getDataCenterUrls and Reformat components as they are. There is no need to change anything there.
- On the second WebServiceClient component, choose the Operation Name you want (search, insert, update)
- Under Request Body Structure click Generate → Customized generation
- Use Select subtype on the generic structure so that you get exactly what you need
- Fill in values to the generated request template (often using public parameters)
- Use Response mapping the get the output (don't forget you can use this to conveniently create metadata too!)
So there you have it. Once you get to know the API a little bit, creating connectors is quite easy. Feel free to use this example as a template for some great work!
More from the CloverETL Blog
Check out some of our most popular posts:
- Choosing data integration software: 8 essential questions to ask
- Data warehouses, lakes, hubs, and vaults explained
- GDPR: finding your data is the first step
- Data integration for identity and access management
- 3 misconceptions that will hurt your data migration