Hadoop大数据平台构建与应用(工作手册式)（微课版） 案例 6.3 手游信息数据存储设计V1.2

手游信息数据存储设计【任务描述】客户是一个互联网手机游戏平台，需要针对广大手游用户进行手游产品的统计分析，需要存储每个手游用户即客户对每个手游产品的关注度（游戏热度），且存储时间维度上的关注度信息，从而能针对客户的喜好进行挖掘并进行类似精准营销的手游定点推送，广告营销等业务，从而扩大该平台的用户量并提升用户粘着度。根据上述客户需求，本案例将会创建HBase数据库表结构，用于记录用户关注的手游、手游被哪些用户关注、用户每天对手游产品的使用记录、用户每日使用手游次数统计这4项信息。【任务分析】本小节任务将首先创建用户关注手游信息表、手游被关注信息表、用户手游使用记录表、用户手游使用次数统计表、用户手游日使用次数统计表这4张HBase数据库表结构，然后所创建的4张HBase数据库表结构，需完成如下业务逻辑。当用户关注手游时，需要将信息记录到用户关注手游信息表和手游被关注信息表。当用户使用手游时，需要将使用记录记录到用户手游使用记录表。查看某手游被关注的情况。统计用户每天使用手游的次数，将结果存储到用户手游日使用次数统计表。【任务实施】设计表结构用户关注手游信息表如REF_Ref99465154\h表61所示。表STYLEREF1\s6SEQ表\*ARABIC\s11用户关注手游信息表编号项目项目内容说明1RowKeyuser_id用户ID2列族games3列名games_id手游ID4值文本内容手游名称手游被关注信息表如REF_Ref118390898\h表62所示。表STYLEREF1\s6SEQ表\*ARABIC\s12手游被关注信息表编号项目项目内容说明1RowKeygames_id手游ID2列族user3列名user_id用户ID4值user_id用户ID用户手游使用记录表如REF_Ref118390903\h表63所示。表STYLEREF1\s6SEQ表\*ARABIC\s13用户手游使用记录表编号项目项目内容说明1RowKeyuser_id_timestamp用户ID+使用开始时间2列族degee3列名games_id手游ID4值1表示使用1次用户手游日使用次数统计表如REF_Ref118390907\h表64所示。表STYLEREF1\s6SEQ表\*ARABIC\s14用户手游日使用次数统计表编号项目项目内容说明1RowKeyuser_id_ymd用户ID+使用日期2列族degee3列名games_id手游ID4值gamecounts使用次数创建表结构打开IDEA，创建名为hbase_mobilegame的工程，在src/main/resources目录下添加Hadoop配置文件core-site.xml和hdfs-site.xml，在pom.xml文件添加所需依赖，如REF_Ref99465396\h代码61所示，添加完成后加载依赖。代码STYLEREF1\s6SEQ代码\*ARABIC\s11添加工程依赖<dependencies><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-common</artifactId><version>3.1.4</version></dependency><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-hdfs</artifactId><version>3.1.4</version></dependency><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-client</artifactId><version>3.1.4</version></dependency><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-mapreduce-client-common</artifactId><version>3.1.4</version></dependency><dependency><groupId>org.apache.hbase</groupId><artifactId>hbase-mapreduce</artifactId><version>2.4.11</version></dependency><dependency><groupId>org.apache.hbase</groupId><artifactId>hbase-client</artifactId><version>2.4.11</version></dependency><dependency><groupId>org.apache.hbase</groupId><artifactId>hbase-server</artifactId><version>2.4.11</version></dependency></dependencies>在src/main/java目录下创建名为createtable的package，在createtable包内创建CreateTable.java，在main方法外定义一些全局变量，包括所需的4张表结构的表名、Configuration对象conf、Connection对象conn，内容如REF_Ref99465389\h代码62所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s12CreateTable.javapackagecreatetable;importorg.apache.hadoop.conf.Configuration;importorg.apache.hadoop.hbase.client.Connection;publicclassCreateTable{privatestaticfinalbyte[]USERFOLLOWS="userfollows".getBytes();privatestaticfinalbyte[]GAMEFOLLOWS="gamefollows".getBytes();privatestaticfinalbyte[]USAGERECORDS="usagerecords".getBytes();privatestaticfinalbyte[]USAGECOUNT="usagecount".getBytes();privatestaticConfigurationconf;privatestaticConnectionconn;publicstaticvoidmain(String[]args){}}根据如REF_Ref99465154\h表61所示的表结构设计，在main方法外创建方法initUserfollows()，用于创建用户关注手游信息表，如REF_Ref99465370\h代码63所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s13创建用户关注手游信息表privatestaticvoidinitUserfollows()throwsIOException{//创建AdminAdminadmin=conn.getAdmin();//创建表描述器HTableDescriptoruserfollowsHTableDescriptor=newHTableDescriptor(TableName.valueOf(USERFOLLOWS));//创建列簇描述器HColumnDescriptorgamesHColumnDescriptor=newHColumnDescriptor("games");userfollowsHTableDescriptor.addFamily(gamesHColumnDescriptor);//创建表if(admin.tableExists(TableName.valueOf(USERFOLLOWS))){if(admin.isTableEnabled(TableName.valueOf(USERFOLLOWS))){admin.disableTable(TableName.valueOf(USERFOLLOWS));}admin.deleteTable(TableName.valueOf(USERFOLLOWS));}admin.createTable(userfollowsHTableDescriptor);admin.close();}根据如REF_Ref118390898\h表62所示的表结构设计，在main方法外创建方法initGamefollows()，用于创建手游被关注信息表，如REF_Ref118451989\h代码64所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s14创建手游被关注信息表privatestaticvoidinitGamefollows()throwsIOException{//创建AdminAdminadmin=conn.getAdmin();TableNametableName=TableName.valueOf(GAMEFOLLOWS);//创建表描述器HTableDescriptorgamefollowsHTableDescriptor=newHTableDescriptor(tableName);//创建列簇描述器HColumnDescriptorusersHColumnDescriptor=newHColumnDescriptor("users");gamefollowsHTableDescriptor.addFamily(usersHColumnDescriptor);//创建表if(admin.tableExists(tableName)){if(admin.isTableEnabled(tableName)){admin.disableTable(tableName);}admin.deleteTable(tableName);}admin.createTable(gamefollowsHTableDescriptor);admin.close();}根据如REF_Ref118390903\h表63所示的表结构设计，在main方法外创建方法initUsagerecords()，用于创建用户手游使用记录表，如REF_Ref118451994\h代码65所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s15创建用户手游使用记录表privatestaticvoidinitUsagerecords()throwsIOException{//创建AdminAdminadmin=conn.getAdmin();TableNametableName=TableName.valueOf(USAGERECORDS);//创建表描述器HTableDescriptorusagerecordsHTableDescriptor=newHTableDescriptor(tableName);//创建列簇描述器HColumnDescriptordegeeHColumnDescriptor=newHColumnDescriptor("degee");usagerecordsHTableDescriptor.addFamily(degeeHColumnDescriptor);//创建表if(admin.tableExists(tableName)){if(admin.isTableEnabled(tableName)){admin.disableTable(tableName);}admin.deleteTable(tableName);}admin.createTable(usagerecordsHTableDescriptor);admin.close();}根据如REF_Ref118390907\h表64所示的表结构设计，在main方法外创建方法initUsagecount()，用于创建用户手游日使用次数统计表，如REF_Ref118452000\h代码66所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s16创建用户手游日使用次数统计表privatestaticvoidinitUsagecount()throwsIOException{//创建AdminAdminadmin=conn.getAdmin();TableNametableName=TableName.valueOf(USAGECOUNT);//创建表描述器HTableDescriptorusagecountHTableDescriptor=newHTableDescriptor(tableName);//创建列簇描述器HColumnDescriptordegeeHColumnDescriptor=newHColumnDescriptor("degee");usagecountHTableDescriptor.addFamily(degeeHColumnDescriptor);//创建表if(admin.tableExists(tableName)){if(admin.isTableEnabled(tableName)){admin.disableTable(tableName);}admin.deleteTable(tableName);}admin.createTable(usagecountHTableDescriptor);admin.close();}在main方法内使用HBaseConfiguration的单例方法实例化conf，并配置参数，建立HBase连接，调用创建表的4个方法，如REF_Ref99465360\h代码67所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s17main方法publicstaticvoidmain(String[]args)throwsIOException{//创建Configuration对象conf=HBaseConfiguration.create();conf.set("hbase.master","master:16010");conf.set("hbase.rootdir","hdfs://master:8020/hbase");conf.set("hbase.zookeeper.quorum","master,slave1,slave2,slave3");conf.set("perty.clientPort","2181");//建立HBase连接conn=ConnectionFactory.createConnection(conf);//创建用户关注手游信息表userfollowsinitUserfollows();//手游被关注信息表gamefollowsinitGamefollows();//用户手游使用记录表usagerecordsinitUsagerecords();//用户手游日使用次数统计表usagecountinitUsagecount();}按顺序依次启动Hadoop集群、Zookeeper集群、HBase集群。运行CreatTable.java程序，IDEA的“Run”窗口输出“Processfinishedwithexitcode0”表示运行成功，进入HBaseShell，使用“list”命令查看HBase数据表，如REF_Ref99465259\h图61所示，已成功创建gamefollows、usagecount、usagerecords、userfollows这4张表。图STYLEREF1\s6SEQ图\*ARABIC\s11查看HBase数据表业务逻辑现模拟生成一份userfollowgame.txt，记录了用户关注手游时的用户ID、手游ID和手游内容，如REF_Ref118467178\h表65所示。表STYLEREF1\s6SEQ表\*ARABIC\s15userfollowgame.txt数据内容用户ID手游ID手游内容weixin01g01王者荣耀weixin01g02欢乐麻将qq01g02欢乐麻将qq01g03欢乐斗地主weixin02g04消消乐在src/main/java目录下创建名为business的package，在business包内创建UserFollowGame.java，基于如REF_Ref118467178\h表65所示的数据，实现当用户关注手游时，将信息记录到用户关注手游信息表和手游被关注信息表，如REF_Ref99465350\h代码68所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s18UserFollowGame.javapackagebusiness;importorg.apache.hadoop.conf.Configuration;importorg.apache.hadoop.hbase.HBaseConfiguration;importorg.apache.hadoop.hbase.TableName;importorg.apache.hadoop.hbase.client.Connection;importorg.apache.hadoop.hbase.client.ConnectionFactory;importorg.apache.hadoop.hbase.client.Put;importorg.apache.hadoop.hbase.client.Table;importorg.apache.hadoop.hbase.util.Bytes;importjava.io.IOException;publicclassUserFollowGame{privatestaticfinalbyte[]USERFOLLOWS="userfollows".getBytes();privatestaticfinalbyte[]GAMEFOLLOWS="gamefollows".getBytes();privatestaticConfigurationconf;privatestaticConnectionconn;publicstaticvoidmain(String[]args)throwsIOException{//创建Configuration对象conf=HBaseConfiguration.create();conf.set("hbase.master","master:16010");conf.set("hbase.rootdir","hdfs://master:8020/hbase");conf.set("hbase.zookeeper.quorum","master,slave1,slave2,slave3");conf.set("perty.clientPort","2181");//建立HBase连接conn=ConnectionFactory.createConnection(conf);userfollowgame("weixin01","g01","王者荣耀");userfollowgame("weixin01","g02","欢乐麻将");userfollowgame("qq01","g02","欢乐麻将");userfollowgame("qq01","g03","欢乐斗地主");userfollowgame("weixin02","g04","消消乐");}/***负责当用户关注手游时的数据记录**@paramuser*@paramgameid*@paramgame*@throwsIOException*/privatestaticvoiduserfollowgame(Stringuser,Stringgameid,Stringgame)throwsIOException{//写入数据到用户关注手游表TableuserfollowTable=conn.getTable(TableName.valueOf(USERFOLLOWS));longtimestamp=System.currentTimeMillis();Putput=newPut(Bytes.toBytes(user));put.addColumn(Bytes.toBytes("games"),Bytes.toBytes(gameid),timestamp,Bytes.toBytes(game));userfollowTable.put(put);//写入数据到手游被关注用户表TablegamefollowTable=conn.getTable(TableName.valueOf(GAMEFOLLOWS));put=newPut(Bytes.toBytes(gameid));put.addColumn(Bytes.toBytes("users"),Bytes.toBytes(user),timestamp,Bytes.toBytes(user));gamefollowTable.put(put);//释放资源gamefollowTable.close();userfollowTable.close();}}运行UserFollowGame.java程序，运行成功后，通过HBaseShell通过scan命令遍历数据表userfollows、gamefollows，如REF_Ref99465270\h图62所示，已成功将数据插入相应数据表中。图STYLEREF1\s6SEQ图\*ARABIC\s12遍历数据表userfollows、gamefollows现模拟生成一份gamerecords.txt，记录了用户使用手游时的用户ID和手游ID，如REF_Ref118468601\h表66所示。表STYLEREF1\s6SEQ表\*ARABIC\s16gamerecords.txt数据内容用户ID手游IDweixin01g01qq01g02weixin02g04weixin01g01qq01g02qq01g03在business包内创建UserGameRecords.java，基于如REF_Ref118468601\h表66所示的数据，实现当用户使用手游时，将使用记录记录到用户手游使用记录表，如REF_Ref99465291\h代码69所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s19UserGameRecords.javapackagebusiness;importjava.io.IOException;importorg.apache.hadoop.conf.Configuration;importorg.apache.hadoop.hbase.HBaseConfiguration;importorg.apache.hadoop.hbase.TableName;importorg.apache.hadoop.hbase.client.Connection;importorg.apache.hadoop.hbase.client.ConnectionFactory;importorg.apache.hadoop.hbase.client.Put;importorg.apache.hadoop.hbase.client.Table;importorg.apache.hadoop.hbase.util.Bytes;publicclassUserGameRecords{privatestaticfinalbyte[]USAGERECORDS="usagerecords".getBytes();privatestaticConfigurationconf;privatestaticConnectionconn;publicstaticvoidmain(String[]args)throwsIOException{//创建Configuration对象conf=HBaseConfiguration.create();conf.set("hbase.master","master:16010");conf.set("hbase.rootdir","hdfs://master:8020/hbase");conf.set("hbase.zookeeper.quorum","master,slave1,slave2,slave3");conf.set("perty.clientPort","2181");//建立HBase连接conn=ConnectionFactory.createConnection(conf);putUserRecords("weixin01","g01");putUserRecords("qq01","g02");putUserRecords("weixin02","g04");putUserRecords("weixin01","g01");putUserRecords("qq01","g02");putUserRecords("qq01","g03");}/***写入使用记录到用户手游使用记录表**@paramuser*@paramgameid*@throwsIOException*@throwsIllegalArgumentException*/privatestaticvoidputUserRecords(Stringuser,Stringgameid)throwsIllegalArgumentException,IOException{Tabletable=conn.getTable(TableName.valueOf(USAGERECORDS));longtimestamp=System.currentTimeMillis();Putput=newPut(Bytes.toBytes(user+"_"+timestamp));put.addColumn(Bytes.toBytes("degee"),Bytes.toBytes(gameid),timestamp,Bytes.toBytes("1"));table.put(put);table.close();}}运行UserGameRecords.java程序，运行成功后，通过HBaseShell通过scan命令遍历数据表usagerecords，如REF_Ref99465270\h图62所示，已成功将数据插入数据表中。图STYLEREF1\s6SEQ图\*ARABIC\s13遍历数据表userfollows、gamefollows在business包内创建ScanData.java，模拟实现查看手游g02被关注的情况、用户weixin01关注手游情况、手游qq01使用记录情况，如REF_Ref99465318\h代码610所示，运行结果如REF_Ref118473636\h图64所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s110ScanData.javapackagebusiness;importjava.io.IOException;importorg.apache.hadoop.conf.Configuration;importorg.apache.hadoop.hbase.Cell;importorg.apache.hadoop.hbase.CellUtil;importorg.apache.hadoop.hbase.HBaseConfiguration;importorg.apache.hadoop.hbase.TableName;importorg.apache.hadoop.hbase.client.Connection;importorg.apache.hadoop.hbase.client.ConnectionFactory;importorg.apache.hadoop.hbase.client.Get;importorg.apache.hadoop.hbase.client.Result;importorg.apache.hadoop.hbase.client.ResultScanner;importorg.apache.hadoop.hbase.client.Scan;importorg.apache.hadoop.hbase.client.Table;importorg.apache.hadoop.hbase.filter.CompareFilter.CompareOp;importorg.apache.hadoop.hbase.filter.RowFilter;importorg.apache.hadoop.hbase.filter.SubstringComparator;importorg.apache.hadoop.hbase.util.Bytes;publicclassScanData{privatestaticConfigurationconf;privatestaticConnectionconn;privatestaticfinalbyte[]USERFOLLOWS="userfollows".getBytes();privatestaticfinalbyte[]GAMEFOLLOWS="gamefollows".getBytes();privatestaticfinalbyte[]USAGERECORDS="usagerecords".getBytes();publicstaticvoidmain(String[]args)throwsIOException{//创建Configuration对象conf=HBaseConfiguration.create();conf.set("hbase.master","master:16010");conf.set("hbase.rootdir","hdfs://master:8020/hbase");conf.set("hbase.zookeeper.quorum","master,slave1,slave2,slave3");conf.set("perty.clientPort","2181");//建立HBase连接conn=ConnectionFactory.createConnection(conf);//查看手游被关注情况System.out.println("查看手游被关注情况:");getData("g02",GAMEFOLLOWS);System.out.println("查看用户关注手游情况:");getData("weixin01",USERFOLLOWS);System.out.println("查看手游使用记录情况：");scanData("qq01",USAGERECORDS);}/***扫描全表数据**@paramgameid*@paramtablename*@throwsIOException*@throwsIllegalArgumentException*/privatestaticvoidscanData(Stringgameid,byte[]tablename)throwsIllegalArgumentException,IOException{Tabletable=conn.getTable(TableName.valueOf(tablename));Scanscan=newScan();scan.setFilter(newRowFilter(CompareOp.EQUAL,newSubstringComparator(gameid)));ResultScannerscanner=table.getScanner(scan);for(Resultresult:scanner){//输出查询结果for(Cellcell:result.rawCells()){System.out.println(Bytes.toString(CellUtil.cloneRow(cell))+":"+Bytes.toString(CellUtil.cloneFamily(cell))+":"+Bytes.toString(CellUtil.cloneQualifier(cell))+":"+Bytes.toString(CellUtil.cloneValue(cell)));}}}/***获取一行数据**@paramgameid*@paramtablename*@throwsIllegalArgumentException*@throwsIOException*/privatestaticvoidgetData(Stringgameid,byte[]tablename)throwsIllegalArgumentException,IOException{Tabletable=conn.getTable(TableName.valueOf(tablename));Getget=newGet(Bytes.toBytes(gameid));get.setMaxVersions();//查找表数据Resultresult=table.get(get);//输出查询结果for(Cellcell:result.rawCells()){System.out.println(Bytes.toString(CellUtil.cloneRow(cell))+":"+Bytes.toString(CellUtil.cloneFamily(cell))+":"+Bytes.toString(CellUtil.cloneQualifier(cell))+":"+Bytes.toString(CellUtil.cloneValue(cell)));}}}图STYLEREF1\s6SEQ图\*ARABIC\s14查看数据在src/main/java目录下创建名为mr_putdata的package，在mr_putdata包内通过编写MapReduce程序，实现每个用户每日使用手游的次数统计，并将结果存储至用户手游日使用次数统计表。在mr_putdata包内创建UseCountMapper.java，读取用户手游使用记录表usagerecords，将表内数据转成键值对，其中将rowkey中的时间戳转化为年月日，以“user+年月日+gameID”为键，“1”为值，如REF_Ref118477741\h代码611所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s111UseCountMapper.javapackagemr_putdata;importjava.io.IOException;importjava.text.SimpleDateFormat;importjava.util.Date;importorg.apache.hadoop.hbase.Cell;importorg.apache.hadoop.hbase.CellUtil;importorg.apache.hadoop.hbase.client.Result;importorg.apache.hadoop.hbase.io.ImmutableBytesWritable;importorg.apache.hadoop.hbase.mapreduce.TableMapper;importorg.apache.hadoop.hbase.util.Bytes;importorg.apache.hadoop.io.IntWritable;importorg.apache.hadoop.io.Text;importorg.apache.hadoop.mapreduce.Mapper;publicclassUseCountMapperextendsTableMapper<Text,IntWritable>{Textuser_ymd=newText();IntWritableone=newIntWritable(1);/***读取每一行记录，将键中的时间戳转化为年月日*以user_ymd为键，1为值*/@Overrideprotectedvoidmap(ImmutableBytesWritablekey,Resultvalue,Mapper<ImmutableBytesWritable,Result,Text,IntWritable>.Contextcontext)throwsIOException,InterruptedException{//分割键的用户和时间戳String[]user_ts=Bytes.toString(key.get()).split("_");for(Cellcell:value.rawCells()){Stringuser_ts_game=user_ts[0]+"_"+getYMD(Long.valueOf(user_ts[1]))+"_"+Bytes.toString(CellUtil.cloneQualifier(cell));user_ymd.set(user_ts_game);context.write(user_ymd,one);}}/***将时间戳转换为日期并返回年月日**@paramts*@return*/publicstaticStringgetYMD(longts){Datedate=newDate(ts);SimpleDateFormatsd=newSimpleDateFormat("yyyyMMdd");returnsd.format(date);}}在mr_putdata包内创建UseCountReducer.java，读取Mapper传输过来的键值对，统计键相同的值的和，以“user+年月日”为键，求和结果为值，如REF_Ref118477734\h代码612所示。代码STYLEREF1\s6SEQ代码\*ARABIC\s112UseCountReducer.javapackagemr_putdata;importjava.io.IOException;importorg.apache.hadoop.hbase.client.Mutation;importorg.apache.hadoop.hbase.client.Put;importorg.apache.hadoop.hbase.io.ImmutableBytesWritable;importorg.apache.hadoop.hbase.mapreduce.TableReducer;importorg.apache.hadoop.hbase.util.Bytes;importorg.apache.hadoop.io.IntWritable;importorg.apache.hadoop.io.Text;importorg.apache.hadoop.mapreduce.Reducer;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;publicclassUseCountReducerextendsTableReducer<Text,IntWritable,ImmutableBytesWritable>{ImmutableBytesWritableuser_ymd=newImmutableBytesWritable();Loggerlog=LoggerFactory.getLogger(UseCountReducer.class);@Overrideprotectedvoidreduce(Textrk,Iterable<IntWritable>ones,Reducer<Text,IntWritable,ImmutableBytesWritable,Mutation>.Contextcontext)throwsIOException,InterruptedException{String[]user_ymd_game=rk.toString().split("_");intsum=0;for(IntWritableone:ones){sum+=one.get();}//用户_日期作为rowkeyStringrowkey=user_ymd_game[0]+"_"+user_ymd_game[1];user_ymd.set(Bytes.toBytes(rowkey));Putput=newPut(Bytes.toBytes(rowkey));put.addColumn(Bytes.toBytes("degee"),Bytes.toBytes(user_ymd_game[2]),Bytes.toBytes(String.valueOf(sum)));context.write(user_ymd,put);}}在mr_putdata包内创建UseCountDriver.java，用于关联UseCountMapper和UseCountReducer以及在IDEA上提交整个程序，如REF_Ref118477727\h代码613所示。代码