Integrating Mule with data.world and monitor Bigfoot


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.

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 -

  1. JDBC Driver - data.world has a JDBC driver that can be used in any JVM based tool.

  2. 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

  1. Mule Anypoint Studio and Access to Mule Resources

  2. 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:

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:

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!

on twitter to get updates on new posts.

Stay updated!

On this blog, I post articles about different technologies like Java, MuleSoft, and much more.

You can get updates for new Posts in your email by subscribing to JavaStreets feed here -


Lives on Java Planet, Walks on Java Streets, Read/Writes in Java, JCP member, Jakarta EE enthusiast, MuleSoft Integration Architect, MuleSoft Community Ambassador, Open Source Contributor and Supporter, also writes at Unit Testers, A Family man!