2011年6月14日 星期二

[Java][雲端運算] 透過Hadoop Map/Reduce,將資料送到MySQL

哈哈!終於搞定了!
卡了那麼久,竟然有一行根本不該加進去的。
話不多說,先說明接下來的Code該做什麼?
首先,我修改了Hadoop WordCount範例,主要是把HDFS中的input/input.txt,透過WordCount Map/Reduce後的資料,餵進MySQL資料庫。
依照以下步驟:
1. 建立資料庫:

DROP TABLE IF EXISTS `WordCount`.`Counting`;
CREATE TABLE  `WordCount`.`Counting` (
`name` char(48) default NULL,
`count` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. 將以下程式碼存成DBWordCount.java

import java.io.IOException;
import java.util.*;

import org.apache.hadoop.fs.Path;
import org.apache.hadoop.conf.*;
import org.apache.hadoop.io.*;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapred.*;
import org.apache.hadoop.util.*;
import java.io.DataInput;
import java.io.DataOutput;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.hadoop.mapred.lib.db.DBWritable;
import org.apache.hadoop.mapred.lib.IdentityReducer;
import org.apache.hadoop.mapred.lib.db.DBConfiguration;
import org.apache.hadoop.mapred.lib.db.DBOutputFormat;
import org.apache.hadoop.mapred.lib.db.DBInputFormat;
/*
MySQL DB Schema:

DROP TABLE IF EXISTS `WordCount`.`Counting`;
CREATE TABLE  `WordCount`.`Counting` (
`name` char(48) default NULL,
`count` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

*/

public class DBWordCount {
    // Output Record Object
    static class WordCountInfoRecord implements Writable,  DBWritable
    {
        public String name;
        public int count;
        public WordCountInfoRecord() {

        }

        public WordCountInfoRecord(String str, int c)
        {
            this.name = str;
            this.count = c;
        }

        public void readFields(DataInput in) throws IOException {
            this.name = Text.readString(in);
            this.count = in.readInt();
        }
        public void write(DataOutput out) throws IOException {
            Text.writeString(out, this.name);
            out.writeInt(this.count);
        }

        public void readFields(ResultSet result) throws SQLException {
            this.name = result.getString(1);
            this.count = result.getInt(2);
        }
        public void write(PreparedStatement stmt) throws SQLException {
            stmt.setString(1, this.name);
            stmt.setInt(2, this.count);
        }
        public String toString() {
            return new String(this.name + " " + this.count);
        }
    }

    public static class Map extends MapReduceBase implements Mapper<LongWritable, IntWritable,Text,IntWritable>
    {

      private final static IntWritable one = new IntWritable(1);
      private Text word = new Text();

      public void map(LongWritable key, Text value, OutputCollector<Text, IntWritable> output, Reporter reporter) throws IOException
      {
        String line = value.toString();
        StringTokenizer tokenizer = new StringTokenizer(line);
        while (tokenizer.hasMoreTokens()) {
          word.set(tokenizer.nextToken());

          word.set(tokenizer.nextToken());
          output.collect(word, one);
        }
      }
    }

    public static class Reduce extends MapReduceBase implements Reducer<Text, IntWritable, WordCountInfoRecord, NullWritable>
    {
      public void reduce(Text key, Iterator<intwritable> values, OutputCollector<
WordCountInfoRecord, NullWritable=""> output, Reporter reporter) throws IOException
{ int sum = 0; while (values.hasNext()) { sum += values.next().get(); } // Output Data into MySQL output.collect(new WordCountInfoRecord(key.toString(),sum), NullWritable.get()); } } public static void main(String[] args) throws Exception { // The following is basic wordcount JobConf conf = new JobConf(DBWordCount.class); conf.setJobName("MySQL DB Wordcount"); Class.forName("com.mysql.jdbc.Driver"); conf.setInputFormat(TextInputFormat.class); conf.setOutputFormat(DBOutputFormat.class); FileInputFormat.setInputPaths(conf, new Path(args[0])); // Setup MySQL Connection , default account:root , no password String[] MyDBPath={"jdbc:mysql://localhost:3306/WordCount","你的帳號", "你的密碼"}; DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",MyDBPath[0], MyDBPath[1], MyDBPath[2]); // Setup Output MySQL Format DBOutputFormat.setOutput(conf, "Counting","name", "count"); // Set Mapper and Reducer Class conf.setMapperClass(Map.class); //conf.setCombinerClass(Reduce.class); conf.setReducerClass(Reduce.class); // I've tried all combinations , but the bug still happen. conf.setMapOutputKeyClass(Text.class); conf.setMapOutputValueClass(IntWritable.class); conf.setOutputKeyClass(WordCountInfoRecord.class); conf.setOutputValueClass(NullWritable.class); JobClient.runJob(conf); } }


3. 撰寫以下Makefile:
# Author: Jyun-Yao Huang (allen501pc@gmail.com)

## Set up class path.
CLASSPATH=/opt/hadoop/hadoop-0.20.2-ant.jar:/opt/hadoop/hadoop-0.20.2-tools.jar:/opt/hadoop/hadoop-0.20.2-core.jar:/opt/hadoop/lib/mysql-connector-java-5.1.16-bin.jar:./

## Set up src files.
SRC = DBWordCount.java
## Set up class files.
OBJ = $(SRC:.java=.class)
## Set up jar file.
JAR_FILE = DBWordCount.jar
## Set up main function.
MAIN_FUNC = DBWordCount

all: $(OBJ)
        jar -cvf $(JAR_FILE) DBWordCount*.class
        make help

%.class: %.java
        javac -classpath $(CLASSPATH) $<
clean:
        hadoop fs -rmr output
cleanall:
        rm *.class *.jar
        hadoop fs -rmr output
run:
        hadoop jar $(JAR_FILE) $(MAIN_FUNC) input/input.txt

help:
        @echo "==============HELP====================="
        @echo "make run"
        @echo "=====END================"
4. 輸入以下指令:
make , make run.

大功告成!

最後,附上一張MySQL DataBase結果輸出:

4 則留言:

  1. output.collect(new WordCountInfoRecord(key.toString(),sum), NullWritable.get());

    這邊會出錯~The method collect(Text, IntWritable) in the type OutputCollector is not applicable for the arguments (DBWordCount.WordCountInfoRecord, NullWritable)

    能不能給我原始程式或者告訴我該怎樣作呢?

    回覆刪除
  2. 你好!我修正了程式碼。謝謝你的提醒,請你再試試看。謝謝囉。

    回覆刪除
  3. 你好~
    您的makefile在我依照您的方法make的時候會出錯...以下為錯誤資訊
    javac -classpath /opt/hadoop-0.20.205.0/hadoop-ant-0.20.205.0.jar:/opt/hadoop-0.20.205.0/hadoop-tools-0.20.205.0.jar:/opt/hadoop-0.20.205.0/hadoop-core-0.20.205.0.jar:/opt/hadoop-0.20.205.0/lib/mysql-connector-java-5.1.20-bin.jar:./ DBWordCount.java
    DBWordCount.java:91: > expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: ')' expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: ';' expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: ';' expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: illegal start of type
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    DBWordCount.java:91: ';' expected
    public void reduce(Text key, Iterator values, OutputCollector output, Reporter reporter) throws IOException
    ^
    8 errors
    make: *** [DBWordCount.class] Error 1

    不知道是哪邊出了問題...可以請您幫忙看一下嗎?

    回覆刪除
  4. 不好意思,請問您能夠把code mail給我嗎?我找台機器測測看。

    回覆刪除