關於hadoop中的DBInputFormat實驗

標籤：

1、注意，需要聲明為靜態內部類，否則會報java.lang.NoSuchMethodException...<init>的錯誤
public static class MySqlWritable implements Writable, DBWritable {

2、如果輸出目錄存在，需要先刪除

3、由於需要從mysql資料取值，則需要有mysql資料庫驅動包，hadoop classpath查看hadoop類載入路徑，將驅動包拷貝到其中一個目錄下即可；

4、解決mysql"Access denied for user‘root‘@‘IP地址‘"問題
a、登入mysql
mysql -u username -p passwd

b、然後執行如下命令：
b-1、 GRANT ALL PRIVILEGES ON *.* TO ‘myuser‘@‘%‘ IDENTIFIED BY ‘mypassword‘ WITH GRANT OPTION;
b-2、FLUSH   PRIVILEGES;

Java代碼：

package cn.sniper.hadoop.inputformat;import java.io.DataInput;import java.io.DataOutput;import java.io.IOException;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.apache.hadoop.conf.Configuration;import org.apache.hadoop.fs.Path;import org.apache.hadoop.io.IntWritable;import org.apache.hadoop.io.LongWritable;import org.apache.hadoop.io.Text;import org.apache.hadoop.io.Writable;import org.apache.hadoop.mapreduce.Job;import org.apache.hadoop.mapreduce.Mapper;import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;import org.apache.hadoop.mapreduce.lib.db.DBWritable;import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;import cn.sniper.hadoop.util.HdfsUtil;public class MyDBInputFormat {    public static String IN_PATH = "hdfs://192.168.1.231:9000/README.txt";    public static String OUT_PATH = "hdfs://192.168.1.231:9000/out";        public static class MyMap extends Mapper<LongWritable, MySqlWritable, Text, IntWritable> {        protected void map(LongWritable key, MySqlWritable value, org.apache.hadoop.mapreduce.Mapper<LongWritable,MySqlWritable,Text,IntWritable>.Context context) throws java.io.IOException ,InterruptedException {            System.out.println(value);            context.write(new Text(value.toString()), new IntWritable(1));        };    }        /**     * 注意，需要聲明為靜態內部類，否則會報java.lang.NoSuchMethodException...<init>的錯誤     * @author sniper     *     */    public static class MySqlWritable implements Writable, DBWritable {                private String id;                private String name;                private String nickName;                @Override        public void readFields(ResultSet resultSet) throws SQLException {            id = resultSet.getString(1);            name = resultSet.getString(2);            nickName = resultSet.getString(3);        }        @Override        public void write(PreparedStatement statement) throws SQLException {            statement.setString(1, id);            statement.setString(2, name);            statement.setString(3, nickName);        }        @Override        public void readFields(DataInput dataInput) throws IOException {            id = dataInput.readUTF();            name = dataInput.readUTF();            nickName = dataInput.readUTF();        }        @Override        public void write(DataOutput dataOutput) throws IOException {            dataOutput.writeUTF(id);            dataOutput.writeUTF(name);            dataOutput.writeUTF(nickName);        }        @Override        public String toString() {            return "MySqlWritable [id=" + id + ", name=" + name + ", nickName="                    + nickName + "]";        }    }        public static void main(String[] args) throws Exception {                Configuration conf = new Configuration();                String driverClass = "com.mysql.jdbc.Driver";        String dbUrl = "jdbc:mysql://192.168.1.105:3306/my";        String userName = "root";        String passwd = "123456";        DBConfiguration.configureDB(conf, driverClass, dbUrl, userName, passwd);                Job job = Job.getInstance(conf, MyDBInputFormat.class.getName());        job.setJarByClass(MyDBInputFormat.class);                //預設是TextInputFormat        job.setInputFormatClass(DBInputFormat.class);        String inputQuery = "select c_id, c_name, c_nickName from t_test";        String inputCountQuery = "select count(*) from t_test";                DBInputFormat.setInput(job, MySqlWritable.class, inputQuery, inputCountQuery);                /*String tableName = "t_test";        String conditions = "";        String orderBy = "";        String[] fieldNames = new String[]{"c_id", "c_name", "c_nickName"};        DBInputFormat.setInput(job, MySqlWritable.class, tableName, conditions, orderBy, fieldNames);*/                job.setOutputFormatClass(TextOutputFormat.class);        FileOutputFormat.setOutputPath(job, new Path(OUT_PATH));                job.setMapperClass(MyMap.class);                //map輸出的key如果不做設定，預設和reduce的key、value一致        job.setMapOutputKeyClass(Text.class);        job.setMapOutputValueClass(IntWritable.class);                job.waitForCompletion(true);    }}

關於hadoop中的DBInputFormat實驗