We have used Apache Hop or Pentaho Data Integration for nearly 15 years now. What a great tool, eco-system, and scalable ETL solution! 95% of the components work very quickly, out of the box, and are easy to use. One of the best use-cases for an ETL tool is to query RestAPI services and retrieve data. In the early days of these RestAPI services you could simply pass in a username and password to get access to any endpoints that you needed to pull data. As API’s have increased security over the years, the addition of bearer tokens, refresh tokens, Oauth tokens, and other forms of user authentication have made simple API calls more complex.
As these API calls have become more complex, companies have started publishing Postman collections that make connections and queries much easier for end users to establish API calls and see the data they produce. Translating all these things from usable postman collection to an ETL tool like Apache Hop or Pentaho Data integration, is quite a challenge sometimes though. For this blog post, I will highlight some ways to make this transition easier. We will focus on the easy use of postman, and how to leverage postman to create code to use in Apache Hop. For this scenario, I will use the API endpoints for Ring Central, which is a highly used commercial VOIP solution for many businesses.
For Ring Central, the postman collection can be downloaded from their developer portal or from postman here.
You will also need to have access to the Ring Central developers portal. You can use the credentials for the “Demonstration App” that comes out of the box with a Ring Central Developer account.
Once you have the Client ID and Client Secret, you can then set up your Postman collection to query the API. The key thing to remember is that you will need to query the API once to authorize (which gets you a token to use). Then, you can query the other endpoints once you have that bearer token. Be sure to place your application JWT in the “assertion” portion of the Body.
Once you successfully click the “Send” button and get back the proper response, we can then move on to the next step.
We now have to do our first steps in Apache Hop. Hop does not do well with some of the more complex things needed to do the same things that postman can do. But, postman can easily give you the curl command that does all the things necessary to do the authorization query. You just have to click the code button to do this.
This entire Code Snippet, can now just be executed in a hop Workflow using a shell step.
Be sure at the end of this shell step you send the output to a file that we can parse in the next step to extract the access token.
Now that we have the results from the auth query, we can parse that json file with json input step in hop, to extract the token we will need for later steps.
Next, we need to see what it takes to query the call detail record in Ring Central, test that in postman, then create a curl command to do the same thing with Apache Hop
The following hop pipeline is creating all things necessary to build that same curl command to query the Call Detail Records from RingCentral.
Here is what is happening inside the “Generate Rows” step. As you can see we are creating the text needed for the majority of the curl command. We will insert things like BeginDate, and EndDate when we concatenate the fields down the line.
The Concatenate Step:
After this file is written, the workflow that called this pipeline needs to CHMOD this file, and then execute this file.
From this point, you will have the full JSON response from the Ring Central API query. A simple Apache Hop pipeline can now be used to parse all the call detail. Here is a screenshot of how we query that main call detail, then split off the “Legs” portion of the call detail to determine call queues from ring central.
We hope you have found this informative. There is a lot of nuance in some of the details, that we would happily discuss with you if needed.
KPI Forge is available for Pentaho Data Integration conversions to Apache Hop, as well as conversions from other ETL platforms into Apache Hop. Let’s talk about your data integration or data analytics pains, and provide a solution for you!
Comments