Hadoop,mapreduce Operation MySQL

Previous post introduction, how to read a text data source and a combination of multiple data sources: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

The 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, you must copy the JDBC driver you want to use under the $hadoop_home/lib/directory for each node in the distributed system.

2. When MapReduce accesses a relational database, it frequently queries and reads data from the MapReduce program, which greatly increases the access load of the database, so the Dbinputformat interface is only suitable for reading small amounts of data, but not 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, the method can be referred to: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:

   void Write (preparedstatement statement) throwssqlexception;  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, configuring the JDBC driver, 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 implement 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. Write the MapReduce function, including the Mapper class, the Reducer class, the input and output file format, and then call 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:

        PublicClassStudentrecord implements writable, dbwritable{IntId            String name;            String gender;            String number; @OverridePublicvoid ReadFields (DatainputInch) throws IOException {//TODO auto-generated Method StubThis.id =Inch. ReadInt ();This.gender = text.readstring (Inch);THIS.name =Inch. readString ();This.number =Inch. readString (); } @OverridePublicvoid Write (DataOutputOut) throws IOException {//TODO auto-generated Method StubOut.writeint (This. ID); Text.writestring (OutThis. name);Out.writeint (This. gender);Out.writeint (This. number); } @OverridePublicvoidReadFields (ResultSet result) throws SQLException {//TODO auto-generated Method StubThis.id = Result.getint (1);THIS.name = result.getstring (2);This.gender = result.getstring (3);This.number = result.getstring (4); } @OverridePublicvoidWrite (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 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>// 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

PublicClassDbaccessreader {PublicStaticvoidMain (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", Span style= "color: #0000ff;" >null  "id"  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

    PublicStaticvoidMain (string[] args) throws IOException {Configuration conf =NewConfiguration (); jobconf conf =NewJobconf (); 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);  

Hadoop,mapreduce Operation MySQL