Talend Open Studio - The OpenSource ETL Tool

Talend Open Studio is used to perform various ETL process. It is one of the famous Open Source Tool in the market. I have created this post here so that people who are interested in using a Talend Open Studio can have a look at the product through different perspective and do not have to search for multiple websites. I have consolidated my findings about TOS  from different websites. Having used this tool for sometime now, I am still exploring the components and still trying to get aquianted with the User Interface.

Overview of Talend Open Studio: Talend offer a variety of products to meet an organisation data needs. These fall into the two categories:
1.  Community – core functionality available under Open Source GPL
2. Commercial – enhanced functionality under open subscription license.

A look into Open Source ETL Tools

Finding a right Open Source ETL tool is dificult. I have done some searching on the internet on different websites and blogs and I have listed out the following Open Source ETL tools.  Different Open Source ETL Tools was studied based on License requirements, history, Open source nature and support of the product. I have singled out four tools namely Talend, CloverETL, Pentaho and SpagoBI. The fundamental overview of these tools are as under:

Talend – It is a startup of French origin that has positioned itself as a pure play of open source data integration and now offers its product - Open Studio. For vendors wishing to embed Open Studio capabilities in their products, Talend has an OEM license agreement. That is what JasperSoft has done, thus creating an open source BI stack to compete with Pentaho's Kettle. Talend is a commercial open source vendor which generates profit from support, training and consulting services. What Open Studio offers is a user-friendly graphical modeling environment as it provides traditional approach for performance management as well as a pushdown optimization (architectural approach). The latter allows users to bypass the actual cost of dedicated hardware to support an ETL engine and enables users to leverage spare capacity of the server within both the source and target environments to power the transformations. Talend Website

CloverETL – Clover ETL is a data transformation and data integration tool (ETL) distributed as a Commercial Open Source software. As the Clover ETL framework is Java based, it is independent and resource- efficient.  CloverETL is used to cleanse, standardize, transform and distribute data to applications, database and warehouses. Clover ETL has been used not only on the most wide spread Windows platform but also on Linux, HP-UX, AIX, AS/400, Solaris and OSX. It can be both used on low-cost PC as on high- end multi processors servers. CloverETL is easy to buy. No complex subscriptions or confusing service tail. Just the software license and maintenance, pure and simple. License is priced by CPU and in easy-to-use packages. Allows you to grow and upgrade to higher levels within the year. CLoverETL Website

I hope

The journey begins with the first step..
The Journey begins with a knock on my door.. "Wake up, Wake up, Lets go..". I jumped out of my Bed and there I see, my roomie all set. I wished I could sleep for some more time to take care of those antagonists in my dream who were firing bazookas and Howitzer on me.

Still sleepy, I realised we had made a plan the previous evening to conquer a fort go for a trek. The train we are supposed to board was at 6.40am and we didn't want to miss the train. Keeping in mind we are left with just 5 minutes to reach the Railway Station I quickly packed my stuffs. All packed and ready, we left for the Railway station and Yes.. just as you expected, we miss the train by just 50 seconds. The next train was after 20 minutes which we boarded.

After reaching the destination, we are now all set to walk up the hill. To me, the hill looks high and far. Never in the last few years have I thought about physical fitness and suddenly today I wished I should not have discontinued that Gym.

Talend Implementation to Load Staging tables

Working with ETL and finding a new tool to implement a code that is already implemented in another tool is a nice work. I am trying to explore the Open Source Tool (Talend Open Studio (TOS)) and yes if I find this Open Source Tool equally powerful as a Licensed tool then it means a lot of savings in the License costs. The preliminary research was obviously on the internet, cosolidating views and posts from different websites and blogs.

After doing some research on thy open source nature of TOS, I explored the tool at the component level. Now I am trying to write my first piece of code, an I am stuck. For me this TOS is not very intuitive in understanding (not like Abiniti ETL). Given a proper classroom training this tool can be easy but learning it on the net with the help of documents posted on the Talend Forum may take time. Not sure though. I have just started.

I have been working with Abinitio ETL earlier and now I have moved to Talend Open Studio. I am new to talend ETL. I am trying to implement a simple logic for which if anyone has a clue please help me. It was pretty simple with Abinitio Tool but I am finding it dificult with Talend ETL.

The Problem: 
 1. I have a table RUNDT which has Runkey, Date and a runflag as attribute. This is extracted using Stored Procedures in MSSQL.

2. If the runflag is "Y" then I have to run the following process:
           2(a): Extract Project_ID from Project_Master table. Project_master tables has number of projects. Means it has multiple records.

           2(b) Now for each project_id, I have to perform the following steps
                        i) Read another Table Resource_table
                       ii)  Along with project_id plus attributes from Resource_table I have to load it into a staging table.

I have implemented the same logic in SSIS but I am finding it difficult to use the tforeach iteration in Talend Open Source.

The snapshot of the SSIS application:




All help appreciated!!

I have posted the same questions in Talend Community Forum

Vai - Part II

High School kan kai ve chuan “Mizo Nun Hlui” leh “Mizo Hla” kan zir a. Tawng Upa kan zir chho tan a. Hriatthiam loh a awmin, Ka pu (fam) ka zawt thin a, sawi tur a ngah thin khawp mai. “Mizo Nun Hlui” tih lehkhabu ah khan “an ziak diklo – tu ziah nge?” tih a nei nual. Ka pu hi Sialsuk ami niin Mizo Nun Hlui hre ve tak a ni a.

“Mizo tamtak aiin Mizo tawng a thiam zawk” ti tu an awm ve nual. Taitesena leh Chawngbawla chanchin ka zir lai te khan sentence tin kha a lo ngaithla a, choka lam atang khan a rawn sawifiah zel thin a. “By-heart” a har thei thin khawp mai.

Ka pu chungchang hi kan sawi zau mai mai anga, MNF ho ram riah lai khan ka pu te unau hian East Pakistan atangin silai an supply thin a ni awm a.

Aizawl School pakhat a ka luh lai chuan Pu F. Malsawma (Education Minister ni thin) khan “Mizo tawng” chungchang thu a sawi na ah, ka pu hming a rawn lam hlawl mai a. “Pu XXX an a ramvah chanchin an sawi mai chu(h), beng tla lek lek in ka ngaithla e”, a ti a. Ka pu kha Mizo tawng a thiam mai bakah Mizo Culture nena inzawmtlatna nei a ni. Nihmahsela a hnam ang zawngin Mizo a ni lova. Mahse Vai kan tih ang hi chu a ni chuang hek lo. Vai a nih pawn Mizo-vai a ni tal ang.

Vai - Part I

Ka tet lai chuan “Vai” tih hi ka hrethiam ve lova. Kan veng naupang ho nen kan inkawm a vai leh mizo kan awm pawh ka hre ngai lo, chak-ai kan khawrh hova, ulawng kan in man siak a. Buk sahovin kan in bai-lem-chhuan a, tui kan khuap a kan ulawng mante chu ka chhuah leh a, sairawkherh-a kan indona hmun thlang chiah-ah Sorkar Office Bus ding thin kha – a acid kan liak thin a. Sertawk ball-ah Goal ven te leh thalasikah tui kang nghah pah a sava veh te kha nuam ka ti. Ka ngai bang thei lo.Sunday sikul ah ka thiante ka zui a, Chang zirin “Davida fakna hla” kan sa ve luah luah thin a nih kha!!

School ah tawngtaina hruai turin min ruat a, Bible chang chhiarin tawngtaina ka kaihruai ve thin lai te ka ngaihtuah chhuak ta. Ka nu leh pate hi Christian nilo mahse Bible Bung leh Chang (Chapter and verses) inthen dan te, tawngtaina kaihruai hun a ka chhiarchhuah tur Bible chang min zawnpui a, thenawm Pi Hmuaki te kan rawn a, tawngtaina kaihruai dan te ka zir ve a nih kha! A la rei lo ve..!!

Sikul chawlh ah Pu Rova te lo zawhna ah kan kal a, Baibing, Tumbu, Rawtuai kan khawrh a,ka hawn ve teuh a. Rilru ah an la cham reng e!! “Feh tura thian dun an kal in, kalkawng peng anih chuan kawng pengah chuan feh kal lam ah tiang pakhat an dah thin a, a haw hmasa zawkin a lo la, a hnuhnung zawk chuan a kalpui chu a haw tawh tih a lo hre thei thin a ni” tih an sawite kha hemi tuma ka thil hriat a ni.

Naupan laia A AW B kan zir tan lai khan “A,AW,B.. Ai Awka Bawm, CH D E – Chhun Denga Ek…” tiin kan au luah luah thin a. Pawl 4 ka nih laia ka thil hriat reng pakhat chu, Exam na – ah khan “Sentence Formation” tih vel kha chhan tur a ni a.

A Big Confusion about Religion

Ngaihtuah na mak anih leh nihloh chu sawithiam a har. “Religion(Sakhua) lampang ah hian ka tui vak hran lo na a, ka bul vel a mi te, ka thiante leh ka chhung hnai te chuan an pawm thlap maithei. An rinna a dik leh dikloh lam sawi ka tum lo.. Mahse ka rilru confused tak mai hi ka dahtha mai thei silo. Tun kar chhova ka rilru a awm reng mai chu, “Sawifiah tum dawn ta ila, ka ngaituahna min hriatthiampui thei an awm ang em?” tih hi a ni.

Thil engkim lo intan dan (Genesis) hi sawi tel lo ang. A nihna dik takah chuan tuman kan hrelo. Kan rindan mai mai ani.”Engtin nge thil nung a lo awm?” tihvel kha chu in rilru ah lo ngaihtuah mai ula, In ngaihtuahna chu in lo dah tha dawn nia.

Khawvelah hian religion 4000- 5000(approx) vel a awm an ti a. Heng religion chi hrang hrang te hi an mahni theuhvin script an nei a, leh an rindan ang zelin khawvel pian dan leh nunna lo awm tan dan an sawifiah a, mahni sakhua an pawm em em vek bawk a. Ka rinna (read sakhua) hi a dik ber an ti vek bawk. Hindu emaw, Budhhist or Islam or eng sakhua pawh hian kan rinna(belief) hi a dik ber an tih chuan chumi sakhaw ringlotute chuan an ring sual der tihna a ni dawn bawk a. A dik ber hi a awm lo tihna anih chu.

Christian(approx 33% of world population) hian kan rinna a dik ber a tih chuan, midang 77% chuan ram ropui an thleng ve dawn lo tihna a ni bawk a. Chutiang zelin Islam (approx 23% population of the world) hian dik berah in chhal ve ta se, 87% ho chu Islam ho meidil ah kan tla dawn tihna a nih chu. Ka sawi tum ber chu “Ka sakhaw rin lai hi a dik ber?” ti a inchhal thei tumah kan awm lo tih hi a ni. Sakhua ka sawiselna ah ngai lo ila, Mihring hian “religion-based” a rindan kan siam hi mak ka ti tawp theilo ka ti tihna a ni ber ang chu.

Chhutta Money

Its kind of difficult to understand sometimes that “chhutta” is never available easily. I believe most of us might have come through this “chhutta nahi hai kya?” question numerous times in our daily life. For me it has become an integral part of my life. This chhutta question has put me in to a lot of thinking lately. From a barber shop to a local kirana shop, a paan tapri or an auto-wallahs, chhutta is always scarce to find. I sometimes wonder where have all these chutta’s gone.

I postulated some theories myself which I completely shrugged off at the end because its lame. How can I even think that most of these chhuttas must have been siphoned off to Swiss Bank apparently by some nation loving politicians. Politicians are the only people I can always blame for every cause. Politicians are my Punching Bag. Whether it is corruption or a Bomb Blast, I tend to feel that politicians are the culprit. It has raised a strong protest inside me and sometimes I doubt the mere existence of democracy and feel we need a Karl Marx to fix all this. Pheww.. from Chhuttas to Karl Marx.. Long Story.

For once when I assume “well.. my theory is true”, then I blame and condemn these politicians. I feel directly affected with their actions. I feel betrayed and cheated. The chhuttas that I always needed in my daily life has been siphoned off to Swiss Bank. What the heck? For a simple Indian like me, this thought of the politicians stealing the public money, whether it is chhutta or khoka was difficult to accept.

Once I had landed myself into a big trouble because of “unavailable chhuttas”. I had a punctured tyre on some remote highway. I took an auto in search for a mechanic and when I finally found a mechanic, the fare I was supposed to pay was 54 bucks. I reach out my pocket and I was really unfortunate to learn that I had only a single note of Rs 1000 in my wallet. When I took the note and tried to hand it over to the auto-wallah, promptly came the reply “chhutta de na boss”. Since I didn’t have this important thing that the auto-wallah wants, I look at the mechanic, raise my eyebrows(this is how mainland Indian communicates sometimes) and I understand the look in the face of the mechanic guy who nodded his head apparently trying to say “Dude, I am sorry, no chhutas here, find somewhere else..!!”.

I feel helpless, I had to ride with the auto-wallah for another 3 Kms before I reach a paan-tapri in search for chhutta, I had to buy a packet of cigarette and a matchbox to finally get what I want. I had to suffer some sort of financial loss due to unavailable chhuttas once again. The auto was the only way to go back to the mechanic for which I was charged an extra amount for the good extra distance that I travelled in search of chhutta.

That day, when I reach home, I had this question in mind… Is this chhutta problem only in mainland India? It brings me back to my childhood days in Mizoram where this chhutta concept was rather different. Going down the memory lane I realize that there was a time in Mizoram where the local shop-owners generally round off the MRP of commodities to its nearest currency denomination. For instance, if a Bourbon biscuit cost Rs 8, the shopkeeper would sell it for Rs 10. Also if a single local call from a PCO cost Rs 3.50, I’l have to shell out Rs 5. The chhutta problem still holds true even in Mizoram but its considerably less than Mainland India.

The “chhutta” question comes to me in different language. Even though it means the same, I have been asked “chhutta-money” in different ways in different times. Some of which I remember is “Pawisa Nawi(Mizoram)’, “Kusura Paisa (Assam), “Bhangti Paisa (Kolkata)”, “Khujura (Nepali)”, “Chhutta/Sutte (Maharashtra)”.

I remember how my Grandfather used to give me advice to have a lot of “Khujura-Money” in my wallet every time I had to travel to Mainland India. I never took this advice seriously though, probably because I know that “chhutta-problem” is going to remain, whether it is North-East India or Mainland India. Now being in Pune, I have decided to prepare a generic answer for every chhutta-question from now on. The next time any one ask for chhutta, I am just going to reply with “Sutte nahi ahe, bhau?”