package cn.songjq.hbase.mr.emp;

import java.io.BufferedReader;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStreamReader;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import org.apache.hadoop.conf.Configuration;

import org.apache.hadoop.hbase.HBaseConfiguration;

import org.apache.hadoop.hbase.HColumnDescriptor;

import org.apache.hadoop.hbase.HTableDescriptor;

import org.apache.hadoop.hbase.MasterNotRunningException;

import org.apache.hadoop.hbase.TableName;

import org.apache.hadoop.hbase.ZooKeeperConnectionException;

import org.apache.hadoop.hbase.client.HBaseAdmin;

import org.apache.hadoop.hbase.client.HTable;

import org.apache.hadoop.hbase.client.Mutation;

import org.apache.hadoop.hbase.client.Put;

import org.apache.hadoop.hbase.client.Result;

import org.apache.hadoop.hbase.client.ResultScanner;

import org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException;

import org.apache.hadoop.hbase.client.Scan;

import org.apache.hadoop.hbase.io.ImmutableBytesWritable;

import org.apache.hadoop.hbase.mapreduce.TableMapReduceUtil;

import org.apache.hadoop.hbase.mapreduce.TableMapper;

import org.apache.hadoop.hbase.mapreduce.TableReducer;

import org.apache.hadoop.hbase.util.Bytes;

import org.apache.hadoop.io.FloatWritable;

import org.apache.hadoop.io.IntWritable;

import org.apache.hadoop.mapreduce.Job;

import org.apache.hadoop.mapreduce.Mapper;

import org.apache.hadoop.mapreduce.Reducer;

import org.junit.Test;

/**

* 基于HBase数据库上的MapReduce程序编程案例,主要实现以下功能

* 1、读取HBase中员工emp表数据

* 2、对读取的emp表数据进行部门平均工资处理

* 3、将处理后的结果输出到HBase表中

*

* 所有Mapper,Reducer、Job均采用匿名内部类实现

* @author songjq

*

*/

public class DeptAvgSalary {

/**

* 数据准备

* 进行MapReduce程序编写前,需要将关系型数据库Emp表csv文件数据插入到HBase emp表中,并创建存放处理结果的表statistics

* @throws IOException

* @throws ZooKeeperConnectionException

* @throws MasterNotRunningException

*/

@Test

public void init() throws MasterNotRunningException, ZooKeeperConnectionException, IOException {

//获取HBase数据库连接对象

Configuration conf = HBaseConfiguration.create();

conf.set("hbase.zookeeper.quorum", "hadoop-server01:2181,hadoop-server02:2181,hadoop-server03:2181");

HBaseAdmin hbaseAdmin = new HBaseAdmin(conf);

//创建emp表并导入emp.csv文件数据

if(!hbaseAdmin.tableExists("emp")) {

//表不存在,创建表,并初始化表的数据

TableName tableName = TableName.valueOf("emp");

//表描述符

HTableDescriptor empTable = new HTableDescriptor(tableName);

//列描述符

HColumnDescriptor f1 = new HColumnDescriptor("empinfo");

//保留3个版本数据

f1.setMaxVersions(3);

empTable.addFamily(f1);

hbaseAdmin.createTable(empTable);

//初始化数据

HTable hTable = new HTable(conf, "emp");

putDataToEmp(hTable);

}

//创建statistics表

if(!hbaseAdmin.tableExists("statistics")) {

//表不存在,创建表,并初始化表的数据

TableName tableName = TableName.valueOf("statistics");

//表描述符

HTableDescriptor statisticsTable = new HTableDescriptor(tableName);

//列描述符

HColumnDescriptor f1 = new HColumnDescriptor("emp_stat");

//保留3个版本数据

f1.setMaxVersions(3);

statisticsTable.addFamily(f1);

hbaseAdmin.createTable(statisticsTable);

}

hbaseAdmin.close();

    }

/**

* 插入数据

* @throws IOException

* @throws RetriesExhaustedWithDetailsException

*/

public static void putDataToEmp(HTable hTable) throws RetriesExhaustedWithDetailsException, IOException {

List<Put> puts = new ArrayList<>();

//构造一个数据流,emp表的csv文件

FileInputStream filein = new FileInputStream("D:\\test\\hbase\\emp.csv");

InputStreamReader fileinReader = new InputStreamReader(filein);

BufferedReader br = new BufferedReader(fileinReader);

String line = null;

while((line=br.readLine())!=null) {

//得到一行数据

String[] split = line.split(",");

//7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30

int empno = Integer.valueOf(split[0]);

String ename = split[1];

String job = split[2];

int mgr = 0;

try {

mgr = Integer.valueOf(split[3]);

}catch (Exception e) {

mgr = 0;

}

String hiredate = split[4];

float salary = Float.valueOf(split[5]);

float comm = 0f;

try {

comm = Float.valueOf(split[6]);

}catch (Exception e) {

comm = 0f;

}

int deptno = Integer.valueOf(split[7]);

//行键rowkey

Put put = new Put(Bytes.toBytes(empno));

//列族

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("ename"), Bytes.toBytes(ename));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("job"), Bytes.toBytes(job));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("mgr"), Bytes.toBytes(mgr));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("hiredate"), Bytes.toBytes(hiredate));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("salary"), Bytes.toBytes(salary));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("comm"), Bytes.toBytes(comm));

put.add(Bytes.toBytes("empinfo"), Bytes.toBytes("deptno"), Bytes.toBytes(deptno));

puts.add(put);

}

hTable.put(puts);

br.close();

fileinReader.close();

filein.close();

}

/**

* Mapper端:

* 这里不继承Mapper类,而是集成TableMapper<KEYOUT, VALUEOUT>

* KEYOUT:输出k2

* VALUEOUT:输出v2

* 由于输入是HBase的一个rowkey记录,因此没有<k1,v1>

* @author songjq

*

*/

static class DeptAvgSalaryMapper extends TableMapper<IntWritable, FloatWritable> {

/*

* 没输入一条rowkey记录,就调用一次map方法

* (non-Javadoc)

* @see org.apache.hadoop.mapreduce.Mapper#map(KEYIN, VALUEIN, org.apache.hadoop.mapreduce.Mapper.Context)

*/

@Override

protected void map(ImmutableBytesWritable rowkey, Result rs,

Context context)

throws IOException, InterruptedException {

//获取员工部门号

int deptno = Bytes.toInt(rs.getValue(Bytes.toBytes("empinfo"), Bytes.toBytes("deptno")));

//获取员工薪水

float salary = Bytes.toFloat(rs.getValue(Bytes.toBytes("empinfo"), Bytes.toBytes("salary")));

//将获取结果写出去

context.write(new IntWritable(deptno), new FloatWritable(salary));

}

}

/**

* Reducer端:

* 这里不继承Reducer类,而是继承HBase的TableReducer<KEYIN, VALUEIN, KEYOUT>类

* KEYIN:Mapper输出k2

* VALUEIN:Mapper输出v2集合

* KEYOUT:Reducer的输出,按照rowkey输出到HBase表

* @author songjq

*

*/

static class DeptAvgSalaryReducer extends TableReducer<IntWritable, FloatWritable, ImmutableBytesWritable> {

/*

* 相同的key会调用一次reduce方法

* (non-Javadoc)

* @see org.apache.hadoop.mapreduce.Reducer#reduce(KEYIN, java.lang.Iterable, org.apache.hadoop.mapreduce.Reducer.Context)

*/

@Override

protected void reduce(IntWritable k3, Iterable<FloatWritable> v3,

Context ctx)

throws IOException, InterruptedException {

//定义部门平均工资

float deptAvgSal = 0f;

float deptTotalSal = 0f;

int count = 0;

Iterator<FloatWritable> iterator = v3.iterator();

while(iterator.hasNext()) {

FloatWritable sal = iterator.next();

deptTotalSal += sal.get();

count++;

}

//求部门平均工资

deptAvgSal = deptTotalSal/count;

/*

* 将处理结果写入HBase statistics表

*/

//创建一个Put对象,并指定rowkey的值,这里使用部门号deptno作为rowkey

Put put = new Put(Bytes.toBytes(k3.get()));

//往put对象添加插入的列数据 emp_stat->列族  dept_avg_sal->部门平均工资列

put.add(Bytes.toBytes("emp_stat"), Bytes.toBytes("dept_avg_sal"), Bytes.toBytes(deptAvgSal));

//输出到HBase,指定输出的rowkey,输出put

ctx.write(new ImmutableBytesWritable(Bytes.toBytes(k3.get())), put);

}

}

/**

* Main方法提交Job

* 提交到Hadoop可能会报错Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/client/HTable

* 因此报此错误需要设置export HADOOP_CLASSPATH=$HBASE_HOME/lib/*:$CLASSPATH

* @param args

* @throws Exception

*/

public static void main(String[] args) throws Exception {

//配置HBase客户端连接信息

Configuration conf = HBaseConfiguration.create();

conf.set("hbase.zookeeper.quorum", "hadoop-server01:2181,hadoop-server02:2181,hadoop-server03:2181");

Job job = Job.getInstance(conf);

job.setJarByClass(DeptAvgSalary.class);

//指定任务的Mapper

/*

* TableMapReduceUtil.initTableMapperJob(

* table, 输入的表

* scan, 指定扫描器

* mapper, 指定Mapper类

* outputKeyClass, 输出key类型

* outputValueClass, 输出value类型

* job 任务job

* );

*/

//创建一个扫描器

Scan scan = new Scan();

//指定要输入的列,如果列太多,不建议查询出来做为输入,根据要处理的列进行输入,这里只查询deptno和salary作为mapper的输入

scan.addColumn(Bytes.toBytes("empinfo"), Bytes.toBytes("deptno"));

scan.addColumn(Bytes.toBytes("empinfo"), Bytes.toBytes("salary"));

TableMapReduceUtil.initTableMapperJob(Bytes.toBytes("emp"),

 scan,

 DeptAvgSalaryMapper.class,

 IntWritable.class,

 FloatWritable.class,

 job);

//指定任务的Reducer

/*

* TableMapReduceUtil.initTableReducerJob(

*  table, 输出的表

*  reducer, Reducer类

*  job) 任务job

*/

TableMapReduceUtil.initTableReducerJob(

"statistics",

DeptAvgSalaryReducer.class,

job);

//提交任务

job.waitForCompletion(true);

}

/**

* 查看输出到HBase中的数据

* 执行结果:

* 部门编号 平均工资

10 2916.6667

20 2576.5

30 1729.5834

* @throws Exception

*/

@Test

public void scanStatisticsInfo() throws Exception {

Configuration conf = HBaseConfiguration.create();

conf.set("hbase.zookeeper.quorum", "hadoop-server01:2181,hadoop-server02:2181,hadoop-server03:2181");

HTable hTable = new HTable(conf, "statistics");

Scan scan = new Scan();

ResultScanner scanner = hTable.getScanner(scan);

Iterator<Result> iterator = scanner.iterator();

System.out.println("部门编号\t\t平均工资");

while(iterator.hasNext()) {

Result rs = iterator.next();

System.out.println(Bytes.toInt(rs.getRow())+"\t\t"+

Bytes.toFloat(rs.getValue(Bytes.toBytes("emp_stat"), Bytes.toBytes("dept_avg_sal"))));

}

hTable.close();

}

}