Hadoop,mapreduce Operation MySQL

Transferred from: http://www.cnblogs.com/liqizhou/archive/2012/05/16/2503458.html

http://www.cnblogs.com/ liqizhou/archive/2012/05/15/2501835.html

This blog describes how mapreduce read relational database data, select the relational database for MySQL, because it is open source software, so we use more. Used to go to school without using open source software, directly with piracy, but also quite with free, and better than open source, such as to Oracle,windows7 and so on. Now work, because the company to consider the cost of the problem, so all used as open source, Ubuntu,mysql, I now support open source, especially like Hadoop, really good, not only can use the software, can also read the source code. Words don't say much.

Hadoop Technology launched a once-challenged and criticized relational database researcher, who argues that MapReduce does not have the ability to store and process structured data in relational databases. To this end, the Hadoop community and researchers have done much to support MapReduce in the hadoop0.19 version of access to relational databases, such as: Mysql,mysql, PostgreSQL, Oracle and several other database systems.

1. read out data from MySQL

hadoop Access relational database is implemented mainly through the interface: Dbinputformat class , package Location: Org.apache.hadoop.mapred.lib.db. Dbinputformat interacts with the database through the JDBC interface provided by the database vendor in the Hadoop application, and can use standard SQL to read the records in the database. Learning Dbinputformat must first know two conditions.

1. before using Dbinputformat, The JDBC driver that will be used must be copied to the $hadoop_home/lib/directory for each node in the distributed system.

2. When accessing a relational database, a large number of frequently querying and reading data from a MapReduce program greatly increases the access load of the database, so the Dbinputformat interface is only suitable for reading small data volumes. Not suitable for processing data warehouses. The method to process the Data Warehouse is: Using the Dump tool of the database to output a large number of data to be analyzed as text, and upload the HDFs for processing, processing methods can be consulted: http://www.cnblogs.com/liqizhou/archive/2012/05/15/2501835.html

The Dbinputformat class contains the following three built-in classes

1. Protected class Dbrecordreader implementsrecordreader<longwritable, T>: Used to read a list of tuple records from a single database table.

2. 2.public Static class Nulldbwritable implements Dbwritable,writable: Mainly used to implement Dbwritable interface. Dbwritable interface to implement two functions, the first is write, the second is readfileds, these two functions are not difficult to understand, one is to write, one is to read all the fields. The prototype is as follows:
   

   public void Write (PreparedStatement statement) throwssqlexception;public void ReadFields (ResultSet ResultSet) throws SQLException;

3. Protected static class Dbinputsplit implements Inputsplit: Mainly used to describe the scope of the input tuple collection, including start and end two properties, start is used to represent the index number of the first record, and end represents the most The index number of the next record.

The following is a detailed description of how to read database records using Dbinputformat, as follows:

1. dbconfiguration.configuredb (jobconf job, Stringdriverclass, String Dburl, String userName, String passwd) function, configure the JDBC driver, the data source, and the user name and password for database access. The JDBC driver for the MySQL database is "Com.mysql.jdbc.Driver" and the data source is "Jdbc:mysql://localhost/testdb", where TestDB is the database to be accessed. Usename is generally "root", passwd is your database password.

2. dbinputformat.setinput (jobconf job, Class<?extends dbwritable> inputclass, String tableName, String conditions,string, String ... FieldNames), the parameter of this method is easy to understand, Inputclass implements Dbwritable interface. , string tablename The table name, conditions represents the condition of the query, and the order by means of the condition, FieldNames is the field, which is quite the result of splitting the SQL statement. Of course, you can also overload with SQL statements. Etinput (jobconf job, Class<?extends dbwritable> Inputclass, String inputquery, Stringinputcountquery).

3. Writes the MapReduce function, including the Mapper class, the Reducer class, the input and output file format, and then calls Jobclient.runjob (conf).

Here's a theory, here's an example: Suppose there is a database student in the MySQL database, assuming that the field in the database has "id", "name", "Gender", "number".

The first step is to implement the Dbwrite and write data interfaces. The code is as follows:

        public class StudentRecord implements  writable, dbwritable{            int id;             String name;             String gender;             String number;              @Override         public void readfields ( Datainput in)  throws IOException {             // TODO Auto-generated method stub             this.id = in.readint ();             this.gender = text.readstring (in);             this.name = in.readstring ();             this.number = in.readstring ();        }              @Override          public void write (dataoutput out)  throws IOException {             // todo auto-generated method  stub            out.writeint (this.id);             text.writestring (Out,this.name);             out.writeint (This.gender);        &nBsp;    out.writeint (This.number);        }                           @Override         public void  ReadFields (Resultset result)  throws SQLException {             // TODO Auto-generated method stub             this.id = result.getint (1);             this.name = result.getstring (2);             this.gender = result.getstring (3);             this.number = result.getstring (4);         }                          @Override          public void write (preparedstatement stmt)  throws SQLException{             // todo auto-generated method  stub            stmt.setint (1, this.id) ;             stmt.setstring (2, this.name);             stmt.setstring (3, this.gender);             stmt.setstring (4, this.number);         }              @Override  &Nbsp;      public string tostring ()  {             // TODO Auto-generated method stub             return new string (this.name +   " "  + this.gender +  " "  +this.number);         }

The second step is to implement the map and reduce classes

    public class dbaccessmapper extends mapreducebase implements             Mapper<LongWritable,  teacherrecord, longwritable, text> {        @ Override        public void map (LongWritable key,  teacherrecord value,                 outputcollector<longwritable, text> collector, reporter reporter)                  throws  ioexception {            // todo  auto-generated method stub             New collector.collect (New longwRitable (Value.id),  new text (value                     .tostring ()));         }    }

The third step: the implementation of the main function, function

Public class dbaccessreader {    public static void main ( String[] args)  throws ioexception {        jobconf  conf = new jobconf (Dbaccessreader.class);         Conf.setoutputkeyclass (Longwritable.class);         Conf.setoutputvalueclass (Text.class);         conf.setinputformat ( Dbinputformat.class);         fileoutputformat.setoutputpath (conf,  new path ("Dboutput"));         dbconfiguration.configuredb ( conf, "Com.mysql.jdbc.Driver",         "Jdbc:mysql://localhost/school", " Root "," 123456 ");         string [] fields = {" id ",   "Name",  "gender",  "number"};        dbinputformat.setinput (Conf, studentrecord.class, "Student ",null " id ",  fields);         conf.setmapperclass ( Dbaccessmapper.class);         conf.setreducerclass ( Identityreducer.class);         jobclient.runjob (conf);         }}

2. Write Data

Often the amount of data that results from data processing is generally not too large and may be appropriate for Hadoop to write directly to the database. Hadoop provides a direct output of the corresponding database to calculate the results.

1. Dboutformat: Provides a database write interface.

2. Dbrecordwriter: An interface that provides data records written to the database.

3. Dbconfiguration: Provides an interface for database configuration and creating links.

Dboutformat provides a static method Setoutput (job,string table,string ... filednames); The parameters of this method are easy to read. Suppose you want to insert a student of data, whose code is

public static void Main (string[] args) throws IOException {configuration conf = new Configuration ();        jobconf conf = new jobconf ();        Conf.setoutputformat (Dboutputformat.class);         Dbconfiguration.configuredb (conf, "Com.mysql.jdbc.Driver", "Jdbc:mysql://localhost/school", "root", "123456");        Dboutputformat.setoutput (conf, "Student", 456, "Liqizhou", "Man", "20004154578"); Jobclient.runjob (conf);