Integrating Mule with data.world and monitor Bigfoot

July, 25 2017
Manik Magar
mule-esb
integrations
data.world
bigfoot
api

Mule ESB helps us integrate with different systems. In this example, we will see how to connect to data.world, a data resource repository and retrieve some data. Let’s integrate mule with data.world to monitor bigfoot sightings around us.

Table of Contents

What is data.world?
Mule Integration
Conclusion
References

What is data.world?

data.world is a data resource repository that contains probably thousands of datasets. Many of these datasets are public and shared with public domain license.

There are two options to connect to data.world and both uses dwsql to retrieve data -

JDBC Driver - data.world has a JDBC driver that can be used in any JVM based tool.
Query API - It also exposes Query APIs that allows to run SQL statements to retrieve data.

Dataset chosen is for demo purpose only, I have not validated the dataset :). So, if it says bigfoot is not in your state but tomorrow knocks your door, don’t blame mule or me for not alerting you! ;)

Mule Integration

We will integrate mule with data.world using both options and build an http endpoint to query number of sightings of bigfoot in requested state.

Prerequisite

Mule Anypoint Studio and Access to Mule Resources
To access data.world, you will need to register on their site and get you API token at https://data.world/settings/advanced.

Using JDBC

This approach is similar as integrating any other traditional databases.

Add a maven dependency to your project -

<dependency>
    <groupId>world.data</groupId>
    <artifactId>dw-jdbc</artifactId>
    <version>0.4.1</version>
</dependency>

Create database connection config -

<db:generic-config name="Generic_Database_Configuration"
  driverClassName="world.data.jdbc.Driver"
  url="jdbc:data:world:sql:timothyrenner:bfro-sightings-data;user=manikmagar;password=abcdefghi"
  doc:name="Generic Database Configuration"/>

URL structure :

URL:  jdbc:data:world:sql:[dataset owner userid]:[dataset id]
User: your data.world username
Password: your data.world API token

Integration flow:

HTTP listener path: http://localhost:8081/data/jdbc?state=Delaware

It should output: Number of Bigfoot sightings in Delaware: 5

<flow name="mule-demo-data-worldFlow" initialState="started">
       	<http:listener config-ref="HTTP_Listener_Configuration" path="/data/jdbc" doc:name="HTTP"/>
       	<set-variable value="#[message.inboundProperties.'http.query.params'.state]" variableName="state" doc:name="Variable" />
      <db:select config-ref="Generic_Database_Configuration"
      	doc:name="Database" >
              <db:parameterized-query><![CDATA[SELECT * FROM bfro_reports where state = #[flowVars.state]]]></db:parameterized-query>
      </db:select>
    	<set-payload value="#['Number of Bigfoot sightings in '+ flowVars.state +':' + payload.size()]" doc:name="Set Payload"/>
        <logger message="#[payload ]" level="INFO" doc:name="Logger"/>
</flow>

Using Query API

HTTP Request config for data world api endpoint -

<http:request-config name="DW_HTTPS_Request_Configuration"
  protocol="HTTPS"
  port="443"
  host="query.data.world"
  basePath="/sql"
  doc:name="HTTP Request Configuration"/>

basePath must be /sql to run SQL queries.

Integration flow:

HTTP listener path: http://localhost:8081/data/api?state=Delaware

It should output: Number of Bigfoot sightings in Delaware: 5

<flow name="mule-demo-data-worldFlow1">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/data/api" doc:name="HTTP"/>
        <set-variable value="#[message.inboundProperties.'http.query.params'.state]" variableName="state" doc:name="Variable" />
        <set-payload value="#[&quot;SELECT * FROM bfro_reports where state = '&quot;+ flowVars.state + &quot;'&quot;]"
          doc:name="Set Payload" mimeType="text/plain"/>
        <http:request config-ref="DW_HTTPS_Request_Configuration"
          path="/timothyrenner/bfro-sightings-data" method="GET" doc:name="HTTP">   (1)
            <http:request-builder>
                <http:query-param paramName="query" value="#[payload]"/>
                <http:header headerName="accept" value="application/json"/>
                <http:header headerName="authorization" value="Bearer abcdefgh"/> (2)
            </http:request-builder>
        </http:request>
        <byte-array-to-string-transformer mimeType="application/json" doc:name="Byte Array to String"/>
        <json:json-to-object-transformer returnClass="java.util.HashMap" doc:name="JSON to Object"/>
        <set-payload value="#['Number of Bigfoot sightings in '+ flowVars.state +':' + payload.results.bindings.size()]" doc:name="Set Payload"/>
        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
    </flow>

1	path is of format `/[dataset owner userid]/[dataset id]`
2	Bearer should have your API token eg. abcdefgh

Conclusion

Integrations are easy!

References

Data World integrations - https://data.world/integrations/java
Data World API Doc - https://dwapi-query.api-docs.io/v0/sql/sql-query
Bigfoot Dataset - https://data.world/timothyrenner/bfro-sightings-data
Mule Database Connector - https://docs.mulesoft.com/mule-user-guide/v/3.8/database-connector
Mule Http Connector - https://docs.mulesoft.com/mule-user-guide/v/3.8/http-connector

Follow @manikmagar on twitter to get updates on new posts.