大数据阶段项目之项目实现

柔光的暖阳◎ 2021-12-10 14:09 318阅读 0赞

#               大数据阶段项目之项目实现  #

**目录**

大数据阶段项目之项目实现

一.启动Hadoop分布式集群（伪分布式）

二.创建一个文件夹存储数据

三.将文件收集到HDFS

1.在Flume的data下创建zebra.conf

2.利用flume收集数据，将收集的数据落地到HDFS系统中。

3.执行命令，存储HDFS

4.查看eclipse中是否存在

四.启动HIVE

五.使用hive操作

1.创建zebra数据库

2.建立外部表，指向要处理的数据（外部表+分区表，用时间作为分区）

3.修复分区

4.查看数据

5.手动设置分区

6.再次查看数据

五.清洗数据，提取有用数据（23个字段）

1.建表语句

2.插入数据

3.查看数据

六.对清晰之后的数据进行整理，建立事实表

1.建表语句：

2.插入数据：

七.查询关心的信息，以应用受欢迎程度表为例：

1.建表语句：

2.插入数据：

3.查询前5名受欢迎app：

八.Sqoop组件工作流程：

1.在mysql建立对应的表

2.利用sqoop导出d\_h\_http\_apptype表：

3.查看数据

4.可视化页面

--------------------

## 一.启动Hadoop分布式集群（伪分布式） ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70][]

## 二.创建一个文件夹存储数据 ##

![20190712195021695.png][]

## 三.将文件收集到HDFS ##

**HIVE是在HDFS上操作的，需要把文件存储到HDFS中，进行操作**

### 1.在Flume的data下创建zebra.conf ###

![20190712195222795.png][]

## 2.利用flume收集数据，将收集的数据落地到HDFS系统中。 ##

**flume在收集日志的时候，按天为单位进行收集**

a1.sources=r1
    a1.channels=c1
    a1.sinks=s1
    
    a1.sources.r1.type=spooldir
    a1.sources.r1.spoolDir=/home/zebra
    a1.sources.r1.interceptors=i1
    a1.sources.r1.interceptors.i1.type=timestamp
    
    a1.sinks.s1.type=hdfs
    a1.sinks.s1.hdfs.path=hdfs://192.168.150.137:9000/zebra/reportTime=%Y-%m-%d
    a1.sinks.s1.hdfs.fileType=DataStream
    a1.sinks.s1.hdfs.rollInterval=30
    a1.sinks.s1.hdfs.rollSize=0
    a1.sinks.s1.hdfs.rollCount=0
    
    a1.channels.c1.type=memory
    a1.sources.r1.channels=c1
    a1.sinks.s1.channel=c1

### 3.执行命令，存储HDFS ###

![2019071219582925.png][]

### 4.查看eclipse中是否存在 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1][]

## 四.启动HIVE ##

![20190712200114907.png][]

## 五.使用hive操作 ##

### 1.创建zebra数据库 ###

*  执行：create database zebra;
 *  执行：use zebra;

![20190712200216649.png][]

### 2.建立外部表，指向要处理的数据（外部表+分区表，用时间作为分区） ###

建表语句：create EXTERNAL table zebra (a1 string,a2 string,a3 string,a4 string,a5 string,a6 string,a7 string,a8 string,a9 string,a10 string,a11 string,a12 string,a13 string,a14 string,a15 string,a16 string,a17 string,a18 string,a19 string,a20 string,a21 string,a22 string,a23 string,a24 string,a25 string,a26 string,a27 string,a28 string,a29 string,a30 string,a31 string,a32 string,a33 string,a34 string,a35 string,a36 string,a37 string,a38 string,a39 string,a40 string,a41 string,a42 string,a43 string,a44 string,a45 string,a46 string,a47 string,a48 string,a49 string,a50 string,a51 string,a52 string,a53 string,a54 string,a55 string,a56 string,a57 string,a58 string,a59 string,a60 string,a61 string,a62 string,a63 string,a64 string,a65 string,a66 string,a67 string,a68 string,a69 string,a70 string,a71 string,a72 string,a73 string,a74 string,a75 string,a76 string,a77 string) partitioned by (reporttime string) row format delimited fields terminated by '|' stored as textfile location '/zebra';
    		增加分区操作：msck repair table zebra;
    		可以通过抽样语法来检验：select * from zebra TABLESAMPLE (1 ROWS);来检查是否导入成功

![20190712200836260.png][]

**增加分区操作：msck repair table zebra;**

### 3.修复分区 ###

**这个表是手动创建的，hive无法识别，需要手动修复分区**

![20190712201038911.png][]

### 4.查看数据 ###

![20190712201433568.png][]

**查看不到数据，也是就说，数据没有拿到**

**msck repair   添加分区，如果不好用的话，只能手动添加了**

### 5.手动设置分区 ###

**PS：日期对应当前日期**

![20190712201653802.png][]

### 6.再次查看数据 ###

![20190712201848606.png][]

## 五.清洗数据，提取有用数据（23个字段） ##

### 1.建表语句 ###

*  create table dataclear(reporttime string,appType bigint,appSubtype bigint,userIp string,userPort bigint,appServerIP string,appServerPort bigint,host string,cellid string,appTypeCode bigint,interruptType String,transStatus bigint,trafficUL bigint,trafficDL bigint,retranUL bigint,retranDL bigint,procdureStartTime bigint,procdureEndTime bigint)row format delimited fields terminated by '|';

### 2.插入数据 ###

*  insert overwrite table dataclear select concat(reporttime,' ','00:00:00'),a23,a24,a27,a29,a31,a33,a59,a17,a19,a68,a55,a34,a35,a40,a41,a20,a21 from zebra;

### 3.查看数据 ###

![20190712202545276.png][]

56s

![2019071220263959.png][]

**效率巨低 20s**

## 六.对清晰之后的数据进行整理，建立事实表 ##

### 1.建表语句： ###

*  create table f\_http\_app\_host (reporttime string,appType bigint,appSubtype bigint,userIp string,userPort bigint,appServerIP string,appServerPort bigint,host string,cellid string,attempts bigint,accepts bigint,trafficUL bigint,trafficDL bigint,retranUL bigint,retranDL bigint,failCount bigint,transDelay bigint)row format delimited fields terminated by '|';

![20190712202937709.png][]

### 2.插入数据： ###

*  insert overwrite table f\_http\_app\_host select reporttime,appType,appSubtype,userIp,userPort,appServerIP,appServerPort,host, if(cellid == '',"000000000",cellid),if(appTypeCode == 103,1,0),if(appTypeCode == 103 and find\_in\_set(transStatus,"10,11,12,13,14,15,32,33,34,35,36,37,38,48,49,50,51,52,53,54,55,199,200,201,202,203,204,205,206,302,304,306")!=0 and interruptType == 0,1,0),if(apptypeCode == 103,trafficUL,0), if(apptypeCode == 103,trafficDL,0), if(apptypeCode == 103,retranUL,0), if(apptypeCode == 103,retranDL,0), if(appTypeCode == 103 and transStatus == 1 and interruptType == 0,1,0),if(appTypeCode == 103, procdureEndTime - procdureStartTime,0) from dataclear;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2][]

## 七.查询关心的信息，以应用受欢迎程度表为例： ##

### 1.建表语句： ###

*  create table D\_H\_HTTP\_APPTYPE(hourid string,appType int,appSubtype int,attempts bigint,accepts bigint,succRatio double,trafficUL bigint,trafficDL bigint,totalTraffic bigint,retranUL bigint,retranDL bigint,retranTraffic bigint,failCount bigint,transDelay bigint) row format delimited fields terminated by '|';

![20190712203134694.png][]

**根据总表dataproc,按条件做聚合以及字段的累加**

### 2.插入数据： ###

*  insert overwrite table D\_H\_HTTP\_APPTYPE select reporttime,apptype,appsubtype,sum(attempts),sum(accepts),round(sum(accepts)/sum(attempts),2),sum(trafficUL),sum(trafficDL),sum(trafficUL)+sum(trafficDL),sum(retranUL),sum(retranDL),sum(retranUL)+sum(retranDL),sum(failCount),sum(transDelay)from f\_http\_app\_host group by reporttime,apptype,appsubtype;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3][]

## ##

### 3.查询前5名受欢迎app： ###

*  select hourid,apptype,sum(totalTraffic) as tt from D\_H\_HTTP\_APPTYPE group by hourid,apptype sort by tt desc limit 5;

## 八.Sqoop组件工作流程： ##

### 1.在mysql建立对应的表 ###

![20190712203628783.png][]

### 2.利用sqoop导出d\_h\_http\_apptype表： ###

*  sh sqoop export --connect jdbc:mysql://hadoop03:3306/zebra --username root --password root --export-dir '/user/hive/warehouse/zebra.db/d\_h\_http\_apptype/000000\_0' --table D\_H\_HTTP\_APPTYPE -m 1 --fields-terminated-by '|'

![20190712204324854.png][]

### 3.查看数据 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 4][]

### 4.可视化页面 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 5][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70]: /images/20211210/89b88ebad941465c9ea0621d6f266ac3.png
[20190712195021695.png]: /images/20211210/4f48770e53ce451199584a597dcdbeda.png
[20190712195222795.png]: /images/20211210/8f433f63f6c94cff918541a0c6333e54.png
[2019071219582925.png]: /images/20211210/55734289c926456f90a1f8fe8da36fa8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1]: /images/20211210/b4caae2b16e54259bb4f39a7a03e2f37.png
[20190712200114907.png]: /images/20211210/c78ef981351f455b8a03ecaa2348451e.png
[20190712200216649.png]: /images/20211210/97a66e0516714d6ba356c4b89f9fb527.png
[20190712200836260.png]: /images/20211210/aa4a2752c1ac4d6fb5ebf0d205c9f72d.png
[20190712201038911.png]: /images/20211210/824e254a66d341efa5452839d47fdf21.png
[20190712201433568.png]: /images/20211210/98e8566f07f64092a31817b75effa3e6.png
[20190712201653802.png]: /images/20211210/f597175ae0d94eab8963784a47bce3f7.png
[20190712201848606.png]: /images/20211210/7d39e9913d3e4205a7aca3910d5fe5e2.png
[20190712202545276.png]: /images/20211210/78bdef13e42b4902bfdd04858a220e4e.png
[2019071220263959.png]: /images/20211210/f1600158736e48e68926d1a32e842d32.png
[20190712202937709.png]: /images/20211210/e6dacaadda10409eb38882f8d3583b87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2]: /images/20211210/a045aed8f9c84f09bd56c4de0cd69e77.png
[20190712203134694.png]: /images/20211210/e9586307bfb44091bda7b5810aae39fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 3]: /images/20211210/e1aeec5aadf744a4bb4a8b6fd8b62c65.png
[20190712203628783.png]: /images/20211210/661a1cb00e564c70902adbc43a71f1ac.png
[20190712204324854.png]: /images/20211210/0cf0762e93924cb688d61bc0b71c3e75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 4]: /images/20211210/56fda2597c284397a105a94b62bb9664.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 5]: /images/20211210/2ef457fa1f9f41658d3a410bc85c9dec.png