Executing an Oozie workflow with Pig, Hive & Sqoop actions

最新推荐文章于 2022-07-26 16:17:02 发布

原创最新推荐文章于 2022-07-26 16:17:02 发布 · 146 阅读

CC 4.0 BY-SA版权

In the earlier blog entries, we have looked into how install Oozie here and how to do the Click Stream analysis using Hive and Pig here. This blog is about executing a simple work flow which imports the User data from MySQL database using Sqoop, pre-processes the Click Stream data using Pig and finally doing some basic analytics on the User and the Click Stream using Hive.
Before running a Hive query, the table/column/column-types have to be defined. Because of this, the data for Hive needs to have some structure. Pig is better for processing semi structured data when compared to Hive. Here is Pig vs Hive at a very high level. Because of the above mentioned reason, one of the use case is that Pig being used for pre-processing (filter out the invalid records, massage the data etc) of the data to make it ready for Hive to consume.
The below DAG was generated by Oozie. The fork will spawn a Pig action (which cleans the Click Stream data) and a Sqoop action (which imports the user data from a MySQL database) in parallel. Once the Pig and the Sqoop actions are done, the Hive action will be started to do the final analytics combining the Click Stream and the User data.

Here are the steps to define the work flow and then execute it. This is with the assumption that? MySQL, Oozie and Hadoop have been installed, configured and work properly. Here are the instructions for installing and configuring Oozie.
- The work flow requires more than 2 map slots in the cluster, so if the work flow is executed on a single node cluster the following has to be included in the `mapred-site.xml`.

de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.tasktracker.map.tasks.maximum</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>4</de>de>valuede>de>>de>

de></de>de>propertyde>de>>de>

de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.tasktracker.reduce.tasks.maximum</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>4</de>de>valuede>de>>de>

de></de>de>propertyde>de>>de>

- Create the file `oozie-clickstream-examples/input-data/clickstream/clickstream.txt` in HDFS with the below content. Note than the last record is an invalid record which is filtered by Pig when the work flow is executed. The first field is the userId and the second field is the site visited by the user.

de>1de>de>,www.bbc.comde>

de>1de>de>,www.abc.comde>

de>1de>de>,www.gmail.comde>

de>2de>de>,www.cnn.comde>

de>2de>de>,www.eenadu.netde>

de>2de>de>,www.stackoverflow.comde>

de>2de>de>,www.businessweek.comde>

de>3de>de>,www.eenadu.netde>

de>3de>de>,www.stackoverflow.comde>

de>3de>de>,www.businessweek.comde>

de>A,www.thecloudavenue.comde>

- Create a user table in MySQL

de>CREATE TABLE user (de>

de>de>de>user_id INTEGER NOT NULL PRIMARY KEY,de>

de>de>de>name CHAR(de>de>32de>de>) NOT NULL,de>

de>de>de>age INTEGER,de>

de>de>de>country VARCHAR(de>de>32de>de>),de>

de>de>de>gender CHAR(de>de>1de>de>)de>

de>);de>

- And insert some data into it

de>insert into user values (de>de>1de>de>,de>de>"Tom"de>de>,de>de>20de>de>,de>de>"India"de>de>,de>de>"M"de>de>);de>

de>insert into user values (de>de>2de>de>,de>de>"Rick"de>de>,de>de>5de>de>,de>de>"India"de>de>,de>de>"M"de>de>);de>

de>insert into user values (de>de>3de>de>,de>de>"Rachel"de>de>,de>de>15de>de>,de>de>"India"de>de>,de>de>"F"de>de>);de>

- Extract the `oozie-4.0.0/oozie-sharelib-4.0.0.tar.gz` file from the Oozie installation folder and copy the mysql-connector-java-*.jar to the `share/lib/sqoop` folder. This jar is required for Sqoop to connect to the MySQL database and get the user data.
- Copy the above mentioned `share` folder into HDFS. Here is the significance of sharelib in Oozie. These are the common libraries which are used across different actions in Oozie.

de>bin/hadoop fs -put /home/vm4learning/Code/share/ /user/vm4learning/share/de>

- Create the work flow file in HDFS (oozie-clickstream-examples/apps/cs/workflow.xml). Note that the connect string for the Oozie has to be modified appropriately.

de><?de>de>xmlde> de>versionde>de>=de>de>"1.0"de> de>encodingde>de>=de>de>"UTF-8"de>de>?>de>

de><de>de>workflow-appde> de>xmlnsde>de>=de>de>"uri:oozie:workflow:0.2"de> de>namede>de>=de>de>"cs-wf-fork-join"de>de>>de>

de>de>de><de>de>startde> de>tode>de>=de>de>"fork-node"de>de>/>de>

de>de>de><de>de>forkde> de>namede>de>=de>de>"fork-node"de>de>>de>

de>de>de><de>de>pathde> de>startde>de>=de>de>"sqoop-node"de> de>/>de>

de>de>de><de>de>pathde> de>startde>de>=de>de>"pig-node"de> de>/>de>

de>de>de></de>de>forkde>de>>de>

de>de>de><de>de>actionde> de>namede>de>=de>de>"sqoop-node"de>de>>de>

de>de>de><de>de>sqoopde> de>xmlnsde>de>=de>de>"uri:oozie:sqoop-action:0.2"de>de>>de>

de>de>de><de>de>job-trackerde>de>>${jobTracker}</de>de>job-trackerde>de>>de>

de>de>de><de>de>name-nodede>de>>${nameNode}</de>de>name-nodede>de>>de>

de>de>de><de>de>preparede>de>>de>

de>de>de><de>de>deletede> de>pathde>de>=de>de>"${nameNode}/${examplesRootDir}/input-data/user"de>de>/>de>

de>de>de></de>de>preparede>de>>de>

de>de>de><de>de>configurationde>de>>de>

de>de>de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.job.queue.name</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>${queueName}</de>de>valuede>de>>de>

de>de>de></de>de>propertyde>de>>de>

de>de>de></de>de>configurationde>de>>de>

de>de>de><de>de>commandde>de>>import --connect jdbc:mysql://localhost/clickstream --table user --target-dir ${examplesRootDir}/input-data/user -m 1</de>de>commandde>de>>de>

de>de>de></de>de>sqoopde>de>>de>

de>de>de><de>de>okde> de>tode>de>=de>de>"joining"de>de>/>de>

de>de>de><de>de>errorde> de>tode>de>=de>de>"fail"de>de>/>de>

de>de>de></de>de>actionde>de>>de>

de>de>de><de>de>actionde> de>namede>de>=de>de>"pig-node"de>de>>de>

de>de>de><de>de>pigde>de>>de>

de>de>de><de>de>job-trackerde>de>>${jobTracker}</de>de>job-trackerde>de>>de>

de>de>de><de>de>name-nodede>de>>${nameNode}</de>de>name-nodede>de>>de>

de>de>de><de>de>preparede>de>>de>

de>de>de><de>de>deletede> de>pathde>de>=de>de>"${nameNode}${examplesRootDir}/intermediate"de>de>/>de>

de>de>de></de>de>preparede>de>>de>

de>de>de><de>de>configurationde>de>>de>

de>de>de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.job.queue.name</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>${queueName}</de>de>valuede>de>>de>

de>de>de></de>de>propertyde>de>>de>

de>de>de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.compress.map.output</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>true</de>de>valuede>de>>de>

de>de>de></de>de>propertyde>de>>de>

de>de>de></de>de>configurationde>de>>de>

de>de>de><de>de>scriptde>de>>filter.pig</de>de>scriptde>de>>de>

de>de>de><de>de>paramde>de>>INPUT=${examplesRootDir}/input-data/clickstream</de>de>paramde>de>>de>

de>de>de><de>de>paramde>de>>OUTPUT=${examplesRootDir}/intermediate</de>de>paramde>de>>de>

de>de>de></de>de>pigde>de>>de>

de>de>de><de>de>okde> de>tode>de>=de>de>"joining"de>de>/>de>

de>de>de><de>de>errorde> de>tode>de>=de>de>"fail"de>de>/>de>

de>de>de></de>de>actionde>de>>de>

de>de>de><de>de>joinde> de>namede>de>=de>de>"joining"de> de>tode>de>=de>de>"hive-node"de>de>/>de>

de>de>de><de>de>actionde> de>namede>de>=de>de>"hive-node"de>de>>de>

de>de>de><de>de>hivede> de>xmlnsde>de>=de>de>"uri:oozie:hive-action:0.2"de>de>>de>

de>de>de><de>de>job-trackerde>de>>${jobTracker}</de>de>job-trackerde>de>>de>

de>de>de><de>de>name-nodede>de>>${nameNode}</de>de>name-nodede>de>>de>

de>de>de><de>de>preparede>de>>de>

de>de>de><de>de>deletede> de>pathde>de>=de>de>"${nameNode}/${examplesRootDir}/finaloutput"de>de>/>de>

de>de>de></de>de>preparede>de>>de>

de>de>de><de>de>configurationde>de>>de>

de>de>de><de>de>propertyde>de>>de>

de>de>de><de>de>namede>de>>mapred.job.queue.name</de>de>namede>de>>de>

de>de>de><de>de>valuede>de>>${queueName}</de>de>valuede>de>>de>

de>de>de></de>de>propertyde>de>>de>

de>de>de></de>de>configurationde>de>>de>

de>de>de><de>de>scriptde>de>>script.sql</de>de>scriptde>de>>de>

de>de>de><de>de>paramde>de>>CLICKSTREAM=${examplesRootDir}/intermediate/</de>de>paramde>de>>de>

de>de>de><de>de>paramde>de>>USER=${examplesRootDir}/input-data/user/</de>de>paramde>de>>de>

de>de>de><de>de>paramde>de>>OUTPUT=${examplesRootDir}/finaloutput</de>de>paramde>de>>de>

de>de>de></de>de>hivede>de>>de>

de>de>de><de>de>okde> de>tode>de>=de>de>"end"de>de>/>de>

de>de>de><de>de>errorde> de>tode>de>=de>de>"fail"de>de>/>de>

de>de>de></de>de>actionde>de>>de>

de>de>de><de>de>killde> de>namede>de>=de>de>"fail"de>de>>de>

de>de>de><de>de>messagede>de>>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</de>de>messagede>de>>de>

de>de>de></de>de>killde>de>>de>

de>de>de><de>de>endde> de>namede>de>=de>de>"end"de>de>/>de>

de></de>de>workflow-appde>de>>de>

- Create the job.properties file in HDFS (oozie-clickstream-examples/apps/cs/job.properties).

de>nameNode=hdfs:de>de>//localhost:9000de>

de>jobTracker=localhost:de>de>9001de>

de>queueName=de>de>defaultde>

de>examplesRoot=oozie-clickstream-examplesde>

de>examplesRootDir=/user/${user.name}/${examplesRoot}de>

de>oozie.use.system.libpath=de>de>truede>

de>oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/apps/csde>

- Create the Hive script file in HDFS (oozie-clickstream-examples/apps/cs/script.sql). The below mentioned query will find the top 3 url's visited by users whose age is less than 16.

de>DROP TABLE clickstream;de>

de>CREATE EXTERNAL TABLE clickstream (userid INT, url STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY de>de>'\t'de> de>LOCATION de>de>'${CLICKSTREAM}'de>de>;de>

de>DROP TABLE user;de>

de>CREATE EXTERNAL TABLE user (user_id INT, name STRING, age INT, country STRING, gender STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY de>de>','de> de>LOCATION de>de>'${USER}'de>de>;de>

de>INSERT OVERWRITE DIRECTORY de>de>'${OUTPUT}'de> de>SELECT url,count(url) c FROM user u JOIN clickstream c ON (u.user_id=c.userid) where u.age<de>de>16de> de>group by url order by c DESC LIMIT de>de>3de>de>;de>

- Create the Pig script file in HDFS (oozie-clickstream-examples/apps/cs/filter.pig).

de>clickstream = load de>de>'$INPUT'de> de>using PigStorage(de>de>','de>de>) as (userid:de>de>intde>de>, url:chararray);de>

de>SPLIT clickstream INTO good_records IF userid is not de>de>nullde>de>,? bad_records IF userid is de>de>nullde>de>;de>

de>STORE good_records into de>de>'$OUTPUT'de>de>;de>

- Execute the Oozie workflow as below. Note that the `job.properties` file should be present in the local file system and not in HDFS.

de>bin/oozie job -oozie http:de>de>//localhost:11000/oozie -config /home/vm4learning/Code/oozie-clickstream-examples/apps/cs/job.properties -runde>

- Initially the job will be in the `RUNNING` state and finally will reach the `SUCCEEDED` state. The progress of the work flow can be monitored from Oozie console at http://localhost:11000/oozie/.

- The output should appear as below in the `oozie-clickstream-examples/finaloutput/000000_0` file in HDFS.

de>www.businessweek.com de>de>2de>

de>www.eenadu.net de>de>2de>

de>www.stackoverflow.com de>de>2de>

Here are some final thoughts on Oozie.
- It's better to test the individual actions like Hive, Pig and Sqoop independent of Ooize and later integrate them in the Oozie work flow.
- The Oozie error messages very cryptic and the MapReduce log files need to be looked to figure out the actual error.
- The Web UI which comes with Oozie is very rudimentary and clumsy, need to look into some of the alternatives.
- The XML for creating the work flows is very verbose and is very error prone. Any UI for creating workflows for Oozie would be very helpful.
Will look into the alternatives for some of the above problems mentioned in a future blog entry.

Posted by Praveen Sripati at 12:15 PM