Using Talend to read tweets

A project regarding visual discovery required me to look into the possibilities of reading tweets. During my search for a suitable method to accomplish this, I came across the software Talend Open Studio. This is an open source data integration product which allowed me to fulfil my need to read tweets in a very easy way. You won’t have to manually use the Twitter API to get raw JSON, but have the possibility to load and transform the structured data into a file.

In this post we’ll go through the steps you have to take to get the Talend software to use this API and get you the tweets you want. Please keep in mind that, in order to successfully do this, you will need a Twitter account to authenticate yourself and some custom components. Both will be discussed later on.

As you might know, it used to be possible to get an unlimited amount of tweets for free using the Twitter API v1. Unfortunately, Twitter decided to retire this API and replace it with the Twitter API v1.1 which requires you to authenticate before you get the (limited amount of) tweets you want.

Step 1: Adding custom Twitter components

Unfortunately, the components you need aren’t included in Talend by default. Therefore, you will need to add the following three items to your palette: tTwitterOAuth, tTwitterOAuthClose and tTwitterInput. These are made by Gabriele Baldassarre and can be downloaded on his website: http://gabrielebaldassarre.com. Place the components in a location that’s easy to remember, keeping in mind that you’ll need to use the locations path in a second. For example:

Location example

Now we’re going to add these to our palette. In Talend, go to “Window – Preferences – Talend – Components” and fill in the correct path next to ‘User component folder:’.

User component folder

Click on Apply/ok. As you can see the components can now be found in your palette:

Twitter palette

Step 2: Configuring tTwitterOAuth

tTwitterOAuth is responsible for the connection and authentication towards Twitter. As said before, in order to use this component you will need a Twitter account. Add the component to your job.

tTwitterOAuth

As you can see in the components settings, it requires 4 different strings (consumer key, consumer secret, access token and access token) in order to work. We’ll now explain how to get these.

Go to the Twitter developers website (https://dev.twitter.com/) and click on “My applications” (click on your profile picture in the upper right corner).

Twitter My applications

New Twitter application

Create a new application by clicking on “Create New App” (as seen in the picture above) and complete the given form. If you don’t have a website you can use a dummy link, for example your localhost (http://127.0.0.1).

Twitter application form

Upon returning to the applications page, you’ll see your recently created application. Go to its detail page by clicking on it.  If you click on “Test OAuth” you’ll find the information you need in order for your Talend component to work. This button can be found in the upper right corner as shown in the image below:

Test OAuth

Copy the consumer key, consumer secret, access token and access token secret into the matching fields of the tTwitterOAuth component. Keep in mind that these are strings and should therefore be surrounded by double quotes.

Settings tTwitterOAuth

Step 3: Linking tTwitterInput with tTwitterOAuth and tTwitterOAuthClose

Add a tTwitterInput component to your job. Whenever tTwitterOAuth has successfully authenticated us, we’ll tell our job to proceed to the tTwitterInput component. Do this by right-clicking tTwitterOAuth, selecting “Trigger – On subject OK“ and then clicking on tTwitterInput. The components are now linked. At this point, your job should look like this:

connection tTwitterOAuth/tTwitterInput

When tTwitterInput is done, we want to close our authentication. In order to do this, first we need to add a tTwitterOAuthClose component to the job. Next, tell the tTwitterInput component to proceed to the tTwitterOAuthClose component as done before: right click tTwitterInput, select “Trigger – On subject OK” and click on tTwitterOAuthClose.

Connection Twitter components

Step 4: Configuring tTwitterInput

tTwitterInput error

As you can see, at this moment we get the following error: “Parameter (Conditions) must have at least one value”, meaning we can’t just get any tweets we want but we have to specify at least one condition in order for this component to be able to search for tweets. For example, let’s say we only want tweets containing the hashtag Oracle. This is achieved by double-clicking on tTwitterInput and adding a condition (click on the green plus-sign) telling tTwitterInput to include the term “#Oracle”.

tTwitterInput basic settings

The error has now disappeared. You can add multiple operations and even decide whether they should have an AND or OR-structure. You can even add a filter telling it to only get negative or positive tweets, so technically this could be used for sentimental analysis.

Now we’re going to edit the column mapping in order to get the information we want. Keep in mind that as an example we’re only going to add a couple of basic operations. As you will see in the complete list there are many more possibilities then we’ll be showing. Click on the triple dots next to “Edit schema”.

tTwitterInput schema

TweetId is present in the schema by default. Let’s go ahead and add the content of the tweet (Text) and the date on which is was published (CreationDate).

tTwitterInput schema details

After you’re done editing the schema, click on OK. Now we have to change the operations of the column mapping to the right values. Change them to the following:

tTwitterInput mapping

Lastly, let’s specify some other characteristics of the tweets we want the component to return. Click on the “Advanced settings”-tab.  Let’s say we only want recently published information and limit the amount of tweets it should return by 100.

tTwitterInput advanced settings

As you can see, there are multiple conditions that you can edit.  Keep in mind that if you decide to specify a date, this should be done in a string-format (therefore, don’t forget to add double quotes or it won’t work). Also, there’s a limit on how far you can go back in time. As far as I’m aware the API is limited so that you can only receive tweets that have been published in the last week.

You’re now done and ready to get some tweets!

Step 5: Testing

Let’s put the received data into a CSV-document. Add a tFileOutputDelimited component to your job. Link it with your tTwitterInput by right clicking and selecting “Row – Structured” (you can also decide to write raw JSON if you prefer this) and clicking on tFileOutputDelimited. Edit the basic settings of your output file if you’d like to and run the job.

Twitter end result

Open the created file and voila, there are your tweets!

Tweets example

Migrate your MS Access data to an Oracle database using the ETL Tool Talend

APEX is promoted as the perfect replacement for MS Access applications. One thing you should consider though is how you migrate your data to the Oracle database. In APEX there is a handy tool called the Data Workshop that can be used for this. You first export your Excel files from the MS Access database, and then follow the data upload wizard to import the data into identical tables. Since you are not always working with a 1-1 relationship, you will most likely have to write some PL/SQL to get all the data in the right tables.

dataworkshop

The downside is that you will need to repeat this process when you go into production. This is not a big problem if you only have one table to migrate. But if you have multiple tables and/ or your users also want new data during tests and trainings, you will spend a lot of time exporting and importing Excel files.

A recent APEX project for a client required a large data migration from MS Access Databases to the Oracle database. Because we would require fresh data on several points in the development process we decided to use the ETL Open Source Tool Talend. We got impressed of how intuitive the tool is, it only took a few days before we were familiar with the tool. Once you get the hang of it, you can write (or should I say draw) migrations of tables in no time. We needed to migrate from an MS Access database but the tool supports a wide range of databases and documents to import your data from. In total we migrated around 30-40 tables to our Oracle database.

Let’s have a closer look at one of our migration jobs.

talendmsaccessoracle

At the left we see our MS Access database. Each tAccessInput component will get data from one table. After that we join the tables in our tMap_1 component. The reason we don’t just write our joins in one component, is because this way we can really see how many rows every table returns.

On the bottom we have some Oracle Database input connections. They will join the persons of our MS Access Database with the persons in our Oracle Database based on the National registration number. After that we write our data to our Oracle Database. You may notice that we have two lines going to Excel files. This is our error logging; we use this to log the rows that did not find a match. In our first Excel for example we write persons that did not find a match in our Oracle Database.

This is just one example, in total about 20 jobs were built. During the development we also had to deal with certain calculations or convert data. For most things there was a component ready to use and if there wasn’t you could always write a Java expression in the tMap items.

I hope I convinced you of the benefits of using Talend as a migration tool for APEX projects, because we will certainly use this tool again!

Connecting to Salesforce and Mailchimp using Talend

A lot of companies use Salesforce to manage their customers and contacts. In addition Mailchimp can be used for sending out mailings to these connections. Mailchimp also captures information about what people did with these mails. This can be useful information for your CRM. A while ago, I was asked to make a list of everyone that have opened their mails in Mailchimp. Let me show you how easy it is, to do something like that with Talend.

In Talend:

  • we can get a list of email addresses from Mailchimp of receivers that opened a mail
  • and we can ask Salesforce for the email addresses and names of all our connections
  • and we can also use a mapping component to join these lists.

Talend has a standard interface with Salesforce. And Mailchimp offers lots of RESTful web services, which we can make use of in our Talend job.

  1. Connecting to Salesforce  

Right click “Salesforce” under the Metadata and choose “Create Salesforce Connection”.

pic1

After choosing a name for our connection, all we need to fill in, is the username and password for our Salesforce-connection.  The rest is already filled in for us.

pic2

To enable the “Finish” button, we need to check our properties first, using the button “Check login”.

Under Metadata, we can now browse through all our Salesforce-data.

pic3

Now you’re probably wondering, how to use this data in your ETL-flow. Well.. that’s even easier!

Simply drag one of the tables (with the blue icons) into your job and choose for the “tSalesforceInput” component from it’s 3 suggestions.

pic4

After specifying the necessary mappings you should get something like this:

pic5

We’ve used Contact and Account data of Salesforce for this.

In the next part, let’s check out how we generated the list of email addresses.

2.       Connecting to Mailchimp

Accessing your Mailchimp-data, is a bit harder. We need two components from the Talend-palette:

The ‘tRest’ component,  because we need to use a RESTful webservice for requesting our data from Mailchimp. And the ‘tExtractJSONFields’ component for interpreting the data we receive back.

After dragging the tRest component to your job, choose ‘POST’ as the ‘method’ and fill in the URL, corresponding to the report you wish to receive.

pic6

If you want to receive your report in XML-format instead of JSON, just add “.xml” at the end of the URL.

Here we needed the Mailchimp report, that gives us information on opened emails.

If you are interested in other kinds of reports, you can find the list here:

http://apidocs.mailchimp.com/api/2.0/#lists-methods

Every request, needs certain parameters. We can specify them in the HTTP body field, like this:

“{\”apikey\”: \”your api key will be here\”,\”cid\”: \”put a campaign id here\”}”

The API-key will always be needed as the first parameter. You can find it in Mailchimp under your ‘Account Settings’  – ‘Extras’ .

pic7pic8

The second component we need, is called ‘ExtractJSONFields’. After dragging it to our job, we link our first component to it.

pic9

We can use ‘Edit schema’, to define the data we want to extract.

pic10

Finally all we need to do, is specify the location of this data we are interested in, for example the ‘email’-field inside the ‘member’-field.

pic11

Now that we’re able to access our data from Mailchimp, let’s take a look at how we used it for generating the list of e-mailaddresses.

First we asked Mailchimp for all our Campaigns, then we used the ‘flowToIterate’-component so we could ask Mailchimp for the email addresses, once for every campaign in the list:

pic12

Finally all we had to do, is put these two jobs together and press ‘run’.

So.. I hope you’ll enjoy it, as much as I did!