Data integration is a big part of putting your predictive models to work. ADAPA allows for easy model deployment, but how can you actually move data from, say, your database to ADAPA for scoring? One simple way is by using Jitterbit and its web service call operation.
[High Res Video: Jitterbit Database Integration]
Jitterbit is the provider of the most popular open source data and application integration platform. In this blog, we will show you how to score data residing on a database in ADAPA as well as write the resulting scores back to the same database.
Before using Jitterbit, we highly recommend that you spend sometime getting acquainted with the tool and what it can do for you. The Jitterbit website contains a series of informative webinars. We suggest you watch at least three before attempting to…
Data integration is a big part of putting your predictive models to work. ADAPA allows for easy model deployment, but how can you actually move data from, say, your database to ADAPA for scoring? One simple way is by using Jitterbit and its web service call operation.
[High Res Video: Jitterbit Database Integration]
Jitterbit is the provider of the most popular open source data and application integration platform. In this blog, we will show you how to score data residing on a database in ADAPA as well as write the resulting scores back to the same database.
Obviously, you will need first to install and launch the Jitterbit Integration Environment as well as the Jitterbit Server (you can find both available for download in the Jitterbit website). If using MySQL, as we are in this example, you will also need to install the MySQL ODBC driver in the same computer where you install Jitterbit. You can download the MySQL ODBC driver from here.
Before using Jitterbit, we highly recommend that you spend sometime getting acquainted with the tool and what it can do for you. The Jitterbit website contains a series of informative webinars. We suggest you watch at least three before attempting to work on your own. These are:
- Jitterbit Overview: Introduction to Open Source Integration
- Using Jitterbit: A First Look
- Integration: Working with Sources
Now that you are a Jitterbit expert, make sure your models are uploaded in ADAPA. Once you do that, they will be automatically available for execution via web-services. It is now time to Jitterbit.
Once you are able to successfully launch the Jitterbit Integration Server, you should be able to easily create a “New Operation”. Just select from the top menu: File->New->Operation. Once you do that, you will see the new operation you created in Jitterbit as shown below.
Now it is time for you to name it. For this example, let’s call it IrisScoring since we are interested in scoring Iris data (for more information on the Iris dataset and the Iris Neural Network PMML model used here, please visit our Zementis PMML Examples page). Now, right click anywhere on the new IrisScoring operation box and select from the drop-down menu: Activity->Web Service Call. Once you do that, you will see in Jitterbit that the operation now includes a web service call as shown below.
The IrisScoring operation has now all the elements it needs for us to successfully read, score, and write our data from and to a database. Remember that once you upload a model into ADAPA, it is ready to be executed via either the web-console, or web-services. In here, we use web-services to execute the Iris Neural Network model (Iris_NN) and Jitterbit to put the integration puzzle together.
Now, it is just a matter of filling out the blanks. For that, let’s work together and follow the 3 steps outlined below.
Step 1
In this step, we will define the “Source” and the “Target” of our operation. Basically, Source defines where we are getting our data from for building our web service request and Target where we are saving the web service response we get back from ADAPA.
Let’s start by creating a “New Source”. For that, right click on the first block named “Source” of the IrisScoring operation and select from the drop-down menu: “Create New”. The screen will now display the form for “New Source”. From the drop-down menu for type, select “Database”. Notice here that,you can also select to read data from other sources including FTP sites, flat files, etc. Once you select “Database” though, Jitterbit will show all the connection parameters necessary for it to access the database where the Iris data resides. For the driver, if you want to read data from MySQL, select the MySQL ODBC Driver. Enter the server and database names as well as login and password. Jitterbit should now look as shown below.
Now, let’s create a “New Target”. For that, right click on the last block named “Target” of the IrisScoring operation and select “Create New”. In our example, we want to write back the model results to the same MySQL database we read our input data from and so the information we provide to Jitterbit contains the same connection parameters as we did for “New Source”.
Step 2
Now that we have our new source and target defined, let’s focus on the web service call itself. To create a “New WebServiceCall”, right click on the middle block named “Web Service Call” of the IrisScoring operation and select “Create New”. You will now need to give Jitterbit the URL of the ADAPA WSDL file. The WSDL contains a separate operation for each of your PMML models that have been uploaded in ADAPA. Once Jitterbit is able to access the WSDL, the screen should look like the one below.
Note that you can find a link to the ADAPA WSDL file in the bottom of the help page of your ADAPA Instance. Also, note that all the models you uploaded in ADAPA are available for selection in the drop-down menu. In this example, we are using the Iris_NN model.
Also make sure you provide Jitterbit with the appropriate Security information for accessing ADAPA by entering name and password. These are the same as the e-mail and password you used to login into the ADAPA Web Console (where you uploaded your predictive models).
Jitterbit will extract the default web service URL and SoapAction HTTP from the WSDL. Congratulations! You have just finished setting up a “New WebServiceCall”. See screenshot below.
Step 3
It in this step that we will define the mappings (or transformations) between our database tables and the web service call. We start by defining the transformation between the source database and the web service request. For that, right click on the second block named “Request” of the IrisScoring operation to create a “New Transformation”. Select from the drop-down menus for Source – Database and for Target – Web Service Request. Jitterbit will then prompt you for a number of parameters. Enter then accordingly.
Obviously, in order to finish building our request transformation we need to link our database source table to our web service request. After linking all fields (a simple operation in Jitterbit), the screen should look like the one below.
Let’s now create a response transformation in which we will map the web service response back to a table in our database so that our model results can be saved appropriately. To create “New Transformation (2)”, right click on the fourth block named “Response” and select from the drop-down menus for Source – Web Service Response and for Target – Database. Jitterbit will ask us for all the parameters necessary for the transformation to be completed, including selecting the Target Database as well as the database table we want to write to. Once we do that, Jitterbit will prompt us to link the web service response to the correct field in the target table. After linking is complete, the screen should look like the one below.
Our project is complete. By selecting the IrisScoring operation, the Jitterbit screen will look like the one below.
Now it is time for us to deploy and submit the IrisScoring operation to the Jitterbit server. During execution, Jitterbit will read the data from the database, send it over to ADAPA for scoring via a web service request and write the response back to the same database.
