基于Sqoop+Hive+MySQL网站日志分析项目实战

偏执的太偏执、 2022-11-21 14:52 218阅读 0赞

### 目录 ###

*   *  一、需求分析及数据准备
     *   *  （1）需求分析
         *  （2）实施分案
         *  （3）准备数据
     *  二、pv+uv数据分析
     *  三、导出结果
     *  四、企业优化方案
     *  五、编写自动加载数据脚本
     *  六、编写自动调度数据分析
     *  七、运行测试

## 一、需求分析及数据准备 ##

### （1）需求分析 ###

对网站日志的 pv  ,  uv 分析并导出结果数据到mysql表

### （2）实施分案 ###

1.创建hive分区表(date,hour)
    
      2.加载数据
    
      3.数据分析生产结果表
    
      4.导出结果表数据到MySQL

### （3）准备数据 ###

数据我上传到百度云网盘了  
链接: [https://pan.baidu.com/s/1aMKQQ-zaIefc6sIRYHv2BA][https_pan.baidu.com_s_1aMKQQ-zaIefc6sIRYHv2BA] 密码: r6up

**（1）创建表**

create table track.track_log (
    id                         string ,
    url                        string ,
    referer                    string ,
    keyword                    string ,
    type                       string ,
    guid                       string ,
    pageId                     string ,
    moduleId                   string ,
    linkId                     string ,
    attachedInfo               string ,
    sessionId                  string ,
    trackerU                   string ,
    trackerType                string ,
    ip                         string ,
    trackerSrc                 string ,
    cookie                     string ,
    orderCode                  string ,
    trackTime                  string ,
    endUserId                  string ,
    firstLink                  string ,
    sessionViewNo              string ,
    productId                  string ,
    curMerchantId              string ,
    provinceId                 string ,
    cityId                     string ,
    fee                        string ,
    edmActivity                string ,
    edmEmail                   string ,
    edmJobId                   string ,
    ieVersion                  string ,
    platform                   string ,
    internalKeyword            string ,
    resultSum                  string ,
    currentPage                string ,
    linkPosition               string ,
    buttonPosition             string
    ) 
    PARTITIONED BY (date string,hour string)
    
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

**（2）加载数据**

truncate table track_log;

load data local inpath '/opt/datas/2015082818' overwrite into table track.track_log 
    partition(date='20150828',hour='18');
    
    load data local inpath '/opt/datas/2015082819' overwrite into table track.track_log 
    partition(date='20150828',hour='19');

## 二、pv+uv数据分析 ##

**（1）数据分析(pv + uv)**

select  date,hour,count(url) pv, count(distinct guid) uv from track.track_log where date='20150828' group by date,hour;

date    hour    pv      uv
    20150828        18      64972   23938
    20150828        19      64972   23938

由于缺少19时的数据，我就复制了两份相同的数据，所以会导致数据结果一样，但是操作的步骤一样

**（2）创建中间表**  
在hive表track\_log分析出数据后，我们需要把结果放到一个中间表daily\_hour\_visit中，这个表就是存放每天分析出结果的数据。

create table track.daily_hour_visit (
    date string,
    hour string,
    pv string,
    uv string ) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

将表track\_log分析的结果数据放入到表daily\_hour\_visit中

insert into table  track.daily_hour_visit 
    select  date,hour,count(url) pv, count(distinct guid) uv from track.track_log 
    where date='20150828' group by date,hour;

## 三、导出结果 ##

**（1）导出数据到mysql**  
分析出结果存放到中间表daily\_hour\_visit中，我们需要把中间表的数据导入到mysql表中

在mysql中创建数据库，数据表

create database db_track;

create table daily_hour_visit(
    date varchar(30) not null,
    hour varchar(30) not null,
    pv varchar(30) not null,
    uv varchar(30) not null,
    primary key(date,hour)
    )

使用sqoop命令的方式导出数据

bin/sqoop export \
    --connect jdbc:mysql://bigdata-pro-m01:3306/db_track \
    --username root \
    --password 199911 \
    --num-mappers 1  \
    --table daily_hour_visit \
    --input-fields-terminated-by '\t' \
    --export-dir /user/hive/warehouse/track.db/daily_hour_visit

mysql> select * from daily_hour_visit;
    +----------+------+-------+-------+
    | date     | hour | pv    | uv    |
    +----------+------+-------+-------+
    | 20150828 | 18   | 64972 | 23938 |
    | 20150828 | 19   | 64972 | 23938 |
    +----------+------+-------+-------+

## 四、企业优化方案 ##

以上做法均是命令的方式操作，适合用于测试环境，但是在实际企业应用中不会这么去使用，由于企业中的数据量较多较多，通常使用shell脚本自动化去操作。比如说，网站日志数据每天一份 ,数据会放到约定的一个目录(/opt/track/track\_log)，每一天的数据分为24个小文件，也就是24小时每小时的数据，在之前创建track\_log表也会对数据进行分区，按date和hour分区。

数据模型：

20150828(目录)
        
         2015082800(数据)
         2015082801(数据)
         2015082802(数据)
         2015082803(数据)
         2015082804(数据)
         2015082805(数据)
         2015082806(数据)
         ..........
    
    20150829(目录)
        
         2015082800(数据)
         2015082801(数据)
         2015082802(数据)
         2015082803(数据)
         2015082804(数据)
         2015082805(数据)
         2015082806(数据)
         ..........

真实数据模拟测试：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]  
实际应用，要考虑的：

*  通过脚本自动进行数据加载(load)
 *  通过脚本进行数据分析并自动导出结果(process + export)

**1.需求**

编写脚本实现自动调度运行hive分析和sqoop导出结果集（pv + uv）

**2.实施方案**

1.创建约定的数据目录（/opt/track/track_log）
    
    2.创建运行脚本的目录（/opt/track）
    
    3.编写数据自动加载脚本
    
    4.编写自动调度运行Hive和sqoop的脚本

## 五、编写自动加载数据脚本 ##

load\_track\_log.sh 脚本内容：

#! /bin/bash
    
    ## track log dir path
    LOD_DIR=/opt/track/track_log
    
    ## hive home
    HIVE_HOME=/opt/modules/hive
    
    yesterday=`date -d "1 day ago" +"%Y%m%d"`
    
    
    cd $LOD_DIR
    
    for line in `ls $yesterday`;
        do
            date=${line:0:4}${line:4:2}${line:6:2}
            hour=${line:8:2}
            $HIVE_HOME/bin/hive -e "load data local inpath '$LOD_DIR/$yesterday/$line' overwrite into table track.track_log partition(date='$date',hour='$hour');"
    
        done

## 六、编写自动调度数据分析 ##

daily\_hour\_visit.sh 脚本内容：

#!/bin/bash
    
    yesterday=`date -d "1 day ago" +"%Y%m%d"`
    HIVE_HOME=/opt/modules/hive
    SQOOP_HOME=/opt/modules/sqoop
    SCRIPY_HOME=/opt/track
    
    echo "start ........"
    
    echo "============== step 1 : data process start ============"
    
    $HIVE_HOME/bin/hive -hiveconf yesterday=$yesterday -f $SCRIPY_HOME/hive_visit.sql
    
    echo "============== step 1 : data process complete! ============"
    
    echo "============== step 2 : export mysql start ============"
    
    $SQOOP_HOME/bin/sqoop --options-file $SCRIPY_HOME/export_visit.txt
    
    echo "============== step 2 : export mysql complete! ============"
    
    echo " ******** end ******* "

hive-visit.sql 脚本内容：

truncate table track.daily_hour_visit;
    
    insert into table  track.daily_hour_visit
    select  date,hour,count(url) pv, count(distinct guid) uv from track.track_log
    where date='${hiveconf:yesterday}' group by date,hour;

export\_visit.txt 脚本内容：

export
    --connect
    jdbc:mysql://bigdata-pro-m01:3306/db_track
    --username
    root
    --password
    199911
    --num-mappers
    1
    --table
    daily_hour_visit
    --input-fields-terminated-by
    '\t'
    --export-dir
    /user/hive/warehouse/track.db/daily_hour_visit

## 七、运行测试 ##

在测试之前我先将之前测试的数据表都给清空

hive (track)> truncate table track_log;

mysql> truncate table daily_hour_visit;

首先测试加载数据：

./load_track_log.sh

查看一下是否加载数据成功

select count(1) from track_log;

1559328

测试数据分析和导出数据脚本：

./daily_hour_visit.sh

查看分析结果表

hive (track)> select * from daily_hour_visit;

daily_hour_visit.date   daily_hour_visit.hour   daily_hour_visit.pv     daily_hour_visit.uv
    20201101        01      64972   23938
    20201101        05      64972   23938
    20201101        09      64972   23938
    20201101        12      64972   23938
    20201101        16      64972   23938
    20201101        23      64972   23938
    20201101        02      64972   23938
    20201101        06      64972   23938
    20201101        13      64972   23938
    20201101        17      64972   23938
    20201101        20      64972   23938
    20201101        03      64972   23938
    20201101        07      64972   23938
    20201101        10      64972   23938
    20201101        14      64972   23938
    20201101        18      64972   23938
    20201101        21      64972   23938
    20201101        00      64972   23938
    20201101        04      64972   23938
    20201101        08      64972   23938
    20201101        11      64972   23938
    20201101        15      64972   23938
    20201101        19      64972   23938
    20201101        22      64972   23938
    Time taken: 0.033 seconds, Fetched: 24 row(s)

查看导出数据的mysql表

mysql>  select * from daily_hour_visit;

+----------+------+-------+-------+
    | date     | hour | pv    | uv    |
    +----------+------+-------+-------+
    | 20201101 | 00   | 64972 | 23938 |
    | 20201101 | 01   | 64972 | 23938 |
    | 20201101 | 02   | 64972 | 23938 |
    | 20201101 | 03   | 64972 | 23938 |
    | 20201101 | 04   | 64972 | 23938 |
    | 20201101 | 05   | 64972 | 23938 |
    | 20201101 | 06   | 64972 | 23938 |
    | 20201101 | 07   | 64972 | 23938 |
    | 20201101 | 08   | 64972 | 23938 |
    | 20201101 | 09   | 64972 | 23938 |
    | 20201101 | 10   | 64972 | 23938 |
    | 20201101 | 11   | 64972 | 23938 |
    | 20201101 | 12   | 64972 | 23938 |
    | 20201101 | 13   | 64972 | 23938 |
    | 20201101 | 14   | 64972 | 23938 |
    | 20201101 | 15   | 64972 | 23938 |
    | 20201101 | 16   | 64972 | 23938 |
    | 20201101 | 17   | 64972 | 23938 |
    | 20201101 | 18   | 64972 | 23938 |
    | 20201101 | 19   | 64972 | 23938 |
    | 20201101 | 20   | 64972 | 23938 |
    | 20201101 | 21   | 64972 | 23938 |
    | 20201101 | 22   | 64972 | 23938 |
    | 20201101 | 23   | 64972 | 23938 |
    +----------+------+-------+-------+
    24 rows in set (0.00 sec)

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

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[https_pan.baidu.com_s_1aMKQQ-zaIefc6sIRYHv2BA]: https://pan.baidu.com/s/1aMKQQ-zaIefc6sIRYHv2BA
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/9b165c1316c647fe881ab6e92ca9253c.png