使用Hive分析网站访问日志统计日活跃及日新增用户数据、编写脚本

最新推荐文章于 2024-05-17 11:42:08 发布
最新推荐文章于 2024-05-17 11:42:08 发布
阅读量1.9k 收藏 5
点赞数 2
分类专栏： Hive 大数据 文章标签： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33398607/article/details/100117053
版权

demo

文章目录

demand

建立一个表，来存储每天新增的数据(分区表)
统计每天的活跃用户(日活)（需要用户的ip，用户的账号，用户访问时间最早的一条url和时间）
统计每天的新增用户(日新)

data

2019-08-15号的数据：
192.168.33.6,hunter,2019-08-15 10:30:20,/a
192.168.33.7,hunter,2019-08-15 10:30:26,/b
192.168.33.6,jack,2019-08-15 10:30:27,/a
192.168.33.8,tom,2019-08-15 10:30:28,/b
192.168.33.9,rose,2019-08-15 10:30:30,/b
192.168.33.10,julia,2019-08-15 10:30:40,/c

2019-08-16号的数据：
192.168.33.16,hunter,2019-08-16 10:30:20,/a
192.168.33.18,jerry,2019-08-16 10:30:30,/b
192.168.33.26,jack,2019-08-16 10:30:40,/a
192.168.33.18,polo,2019-08-16 10:30:50,/b
192.168.33.39,nissan,2019-08-16 10:30:53,/b
192.168.33.39,nissan,2019-08-16 10:30:55,/a
192.168.33.39,nissan,2019-08-16 10:30:58,/c
192.168.33.20,ford,2019-08-16 10:30:54,/c

2019-08-17号的数据：
192.168.33.46,hunter,2019-08-17 10:30:21,/a
192.168.43.18,jerry,2019-08-17 10:30:22,/b
192.168.43.26,tom,2019-08-17 10:30:23,/a
192.168.53.18,bmw,2019-08-17 10:30:24,/b
192.168.63.39,benz,2019-08-17 10:30:25,/b
192.168.33.25,haval,2019-08-17 10:30:30,/c
192.168.33.10,julia,2019-08-17 10:30:40,/c

table create

create table weblog(ip string,name string,datetime string,url string)
partitioned by (day string)
row format delimited fields terminated by ',';

load data

load data local inpath '/root/test/log15' into table weblog
partition(day='2019-08-15');
load data local inpath '/root/test/log16' into table weblog
partition(day='2019-08-16');
load data local inpath '/root/test/log17' into table weblog
partition(day='2019-08-17');

show partitions weblog;

+-----------------+
|    partition    |
+-----------------+
| day=2019-08-15  |
| day=2019-08-16  |
| day=2019-08-17  |
+-----------------+

select * from weblog;

+----------------+--------------+----------------------+-------------+-------------+
|   weblog.ip    | weblog.name  |   weblog.datetime    | weblog.url  | weblog.day  |
+----------------+--------------+----------------------+-------------+-------------+
| 192.168.33.6   | hunter       | 2019-08-15 10:30:20  | /a          | 2019-08-15  |
| 192.168.33.7   | hunter       | 2019-08-15 10:30:26  | /b          | 2019-08-15  |
| 192.168.33.6   | jack         | 2019-08-15 10:30:27  | /a          | 2019-08-15  |
| 192.168.33.8   | tom          | 2019-08-15 10:30:28  | /b          | 2019-08-15  |
| 192.168.33.9   | rose         | 2019-08-15 10:30:30  | /b          | 2019-08-15  |
| 192.168.33.10  | julia        | 2019-08-15 10:30:40  | /c          | 2019-08-15  |
| 192.168.33.16  | hunter       | 2019-08-16 10:30:20  | /a          | 2019-08-16  |
| 192.168.33.18  | jerry        | 2019-08-16 10:30:30  | /b          | 2019-08-16  |
| 192.168.33.26  | jack         | 2019-08-16 10:30:40  | /a          | 2019-08-16  |
| 192.168.33.18  | polo         | 2019-08-16 10:30:50  | /b          | 2019-08-16  |
| 192.168.33.39  | nissan       | 2019-08-16 10:30:53  | /b          | 2019-08-16  |
| 192.168.33.39  | nissan       | 2019-08-16 10:30:55  | /a          | 2019-08-16  |
| 192.168.33.39  | nissan       | 2019-08-16 10:30:58  | /c          | 2019-08-16  |
| 192.168.33.20  | ford         | 2019-08-16 10:30:54  | /c          | 2019-08-16  |
| 192.168.33.46  | hunter       | 2019-08-17 10:30:21  | /a          | 2019-08-17  |
| 192.168.43.18  | jerry        | 2019-08-17 10:30:22  | /b          | 2019-08-17  |
| 192.168.43.26  | tom          | 2019-08-17 10:30:23  | /a          | 2019-08-17  |
| 192.168.53.18  | bmw          | 2019-08-17 10:30:24  | /b          | 2019-08-17  |
| 192.168.63.39  | benz         | 2019-08-17 10:30:25  | /b          | 2019-08-17  |
| 192.168.33.25  | haval        | 2019-08-17 10:30:30  | /c          | 2019-08-17  |
| 192.168.33.10  | julia        | 2019-08-17 10:30:40  | /c          | 2019-08-17  |
+----------------+--------------+----------------------+-------------+-------------+

日活

统计每天的活跃用户(日活)（需要用户的ip，用户的账号，用户访问时间最早的一条url和时间）

  1. 统计每个用户当天第一次访问的记录到表active:

（1）排序：

select ip,name,datetime,url,row_number()over(partition by name order by datetime) from weblog where day = '2019-08-15';

+----------------+---------+----------------------+------+----------------------+
|       ip       |  name   |       datetime       | url  | row_number_window_0  |
+----------------+---------+----------------------+------+----------------------+
| 192.168.33.6   | hunter  | 2019-08-15 10:30:20  | /a   | 1                    |
| 192.168.33.7   | hunter  | 2019-08-15 10:30:26  | /b   | 2                    |
| 192.168.33.6   | jack    | 2019-08-15 10:30:27  | /a   | 1                    |
| 192.168.33.10  | julia   | 2019-08-15 10:30:40  | /c   | 1                    |
| 192.168.33.9   | rose    | 2019-08-15 10:30:30  | /b   | 1                    |
| 192.168.33.8   | tom     | 2019-08-15 10:30:28  | /b   | 1                    |
+----------------+---------+----------------------+------+----------------------+

（2）统计每个用户当天第一次访问的记录

select ip,name,datetime,url from (select ip,name,datetime,url,row_number()over(partition by name order by datetime) as rn from weblog where day = '2019-08-15') tmp where rn = 1;

+----------------+---------+----------------------+------+
|       ip       |  name   |       datetime       | url  |
+----------------+---------+----------------------+------+
| 192.168.33.6   | hunter  | 2019-08-15 10:30:20  | /a   |
| 192.168.33.6   | jack    | 2019-08-15 10:30:27  | /a   |
| 192.168.33.10  | julia   | 2019-08-15 10:30:40  | /c   |
| 192.168.33.9   | rose    | 2019-08-15 10:30:30  | /b   |
| 192.168.33.8   | tom     | 2019-08-15 10:30:28  | /b   |
+----------------+---------+----------------------+------+

（3）写入active表

  • create table：
create table active(ip string,name string,datetime string,url string) partitioned by (day string);
  • insert data：
insert into table active partition (day = '2019-08-15')
select ip,name,datetime,url from (
select ip,name,datetime,url,row_number()over(partition by name order by datetime) as rn from weblog where day = '2019-08-15') tmp where rn = 1;

select * from active;
+----------------+--------------+----------------------+-------------+-------------+
|   active.ip    | active.name  |   active.datetime    | active.url  | active.day  |
+----------------+--------------+----------------------+-------------+-------------+
| 192.168.33.6   | hunter       | 2019-08-15 10:30:20  | /a          | 2019-08-15  |
| 192.168.33.6   | jack         | 2019-08-15 10:30:27  | /a          | 2019-08-15  |
| 192.168.33.10  | julia        | 2019-08-15 10:30:40  | /c          | 2019-08-15  |
| 192.168.33.9   | rose         | 2019-08-15 10:30:30  | /b          | 2019-08-15  |
| 192.168.33.8   | tom          | 2019-08-15 10:30:28  | /b          | 2019-08-15  |
+----------------+--------------+----------------------+-------------+-------------+

show partitions active;
+-----------------+
|    partition    |
+-----------------+
| day=2019-08-15  |
+-----------------+

日新

统计每天的新增用户(日新)

  • 历史用户表

create table history_user(name string);

  • 新用户表

create table new_user like weblog;

  1. 将日活用户表与历史用户表关联，找出在历史用户表中尚不存在的日活用户
select a.ip,a.name,a.datetime,a.url,h.name
from active a left join history_user h on a.name = h.name
where a.day = '2019-08-15';

+----------------+---------+----------------------+--------+---------+
|      a.ip      | a.name  |      a.datetime      | a.url  | h.name  |
+----------------+---------+----------------------+--------+---------+
| 192.168.33.6   | hunter  | 2019-08-15 10:30:20  | /a     | NULL    |
| 192.168.33.6   | jack    | 2019-08-15 10:30:27  | /a     | NULL    |
| 192.168.33.10  | julia   | 2019-08-15 10:30:40  | /c     | NULL    |
| 192.168.33.9   | rose    | 2019-08-15 10:30:30  | /b     | NULL    |
| 192.168.33.8   | tom     | 2019-08-15 10:30:28  | /b     | NULL    |
+----------------+---------+----------------------+--------+---------+

select ip,name,datetime,url from (
select a.ip,a.name,a.datetime,a.url,h.name as hn
from active a left join history_user h 
on a.name = h.name
where a.day = '2019-08-15'
) tmp where hn is null;

+----------------+---------+----------------------+------+
|       ip       |  name   |       datetime       | url  |
+----------------+---------+----------------------+------+
| 192.168.33.6   | hunter  | 2019-08-15 10:30:20  | /a   |
| 192.168.33.6   | jack    | 2019-08-15 10:30:27  | /a   |
| 192.168.33.10  | julia   | 2019-08-15 10:30:40  | /c   |
| 192.168.33.9   | rose    | 2019-08-15 10:30:30  | /b   |
| 192.168.33.8   | tom     | 2019-08-15 10:30:28  | /b   |
+----------------+---------+----------------------+------+

  1. 将历史表中用户为null的用户记录添加到日新表中

    insert into table new_user partition (day = '2019-08-16')
select ip,name,datetime,url from (
select a.ip,a.name,a.datetime,a.url,h.name as hn
from active a left join history_user h 
on a.name = h.name
where a.day = '2019-08-16'
) tmp where hn is null;

  2. 将日新表的记录写入历史表中

insert into table history_user
select name from new_user where day = '2019-08-16';

select * from history_user;
+--------------------+
| history_user.name  |
+--------------------+
| hunter             |
| jack               |
| julia              |
| rose               |
| tom                |
+--------------------+
  1. 下一天的日新重复执行
insert into table new_user partition (day = '2019-08-16')
select ip,name,datetime,url from (
select a.ip,a.name,a.datetime,a.url,h.name as hn
from active a left join history_user h 
on a.name = h.name
where a.day = '2019-08-16'
) tmp where hn is null;

insert into table history_user
select name from new_user where day = '2019-08-16';

最终结果：

select * from history_user;
+--------------------+
| history_user.name  |
+--------------------+
| hunter             |
| jack               |
| julia              |
| rose               |
| tom                |
| ford               |
| jerry              |
| nissan             |
| polo               |
| benz               |
| bmw                |
| haval              |
+--------------------+

select * from new_user;

+----------------+----------------+----------------------+---------------+---------------+
|  new_user.ip   | new_user.name  |  new_user.datetime   | new_user.url  | new_user.day  |
+----------------+----------------+----------------------+---------------+---------------+
| 192.168.33.6   | hunter         | 2019-08-15 10:30:20  | /a            | 2019-08-15    |
| 192.168.33.6   | jack           | 2019-08-15 10:30:27  | /a            | 2019-08-15    |
| 192.168.33.10  | julia          | 2019-08-15 10:30:40  | /c            | 2019-08-15    |
| 192.168.33.9   | rose           | 2019-08-15 10:30:30  | /b            | 2019-08-15    |
| 192.168.33.8   | tom            | 2019-08-15 10:30:28  | /b            | 2019-08-15    |
| 192.168.33.20  | ford           | 2019-08-16 10:30:54  | /c            | 2019-08-16    |
| 192.168.33.18  | jerry          | 2019-08-16 10:30:30  | /b            | 2019-08-16    |
| 192.168.33.39  | nissan         | 2019-08-16 10:30:53  | /b            | 2019-08-16    |
| 192.168.33.18  | polo           | 2019-08-16 10:30:50  | /b            | 2019-08-16    |
| 192.168.63.39  | benz           | 2019-08-17 10:30:25  | /b            | 2019-08-17    |
| 192.168.53.18  | bmw            | 2019-08-17 10:30:24  | /b            | 2019-08-17    |
| 192.168.33.25  | haval          | 2019-08-17 10:30:30  | /c            | 2019-08-17    |
+----------------+----------------+----------------------+---------------+---------------+

shell脚本

定时每天执行一次求前一天的日活、日新

先求日活插入日活表，再求日新插入日新表，再更新历史用户表。

#!/bin/bash
date_str=`date -d '-1 day' +'%Y-%m-%d'`
echo "准备处理 $date_str 的数据..."

hive_exec=/usr/local/big_data/hive/bin/hive

#日活
HQL_user_active_day="
insert into table test1.active partition (day = \"$date_str\")
select ip,name,datetime,url from (
select ip,name,datetime,url,row_number()over(partition by name order by datetime) as rn from test1.weblog where day = \"$date_str\") tmp where rn = 1;"

$hive_exec -e "$HQL_user_active_day"

#日新
HQL_user_new_day="
insert into table test1.new_user partition (day = \"$date_str\")
select ip,name,datetime,url from (
select a.ip,a.name,a.datetime,a.url,h.name as hn
from test1.active a left join test1.history_user h 
on a.name = h.name
where a.day = \"$date_str\"
) tmp where hn is null;"

$hive_exec -e "$HQL_user_new_day"

#更新历史表
HQL_user_history_update="
insert into table test1.history_user
select name from test1.new_user where day = \"$date_str\";"

$hive_exec -e "$HQL_user_history_update"

echo "$date_str 的数据处理完成!"
deantzzz
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
基于hive的日志数据统计实战
淡然一笑 的专栏
06-07 1万+
Hive是一个可扩展性极强的数据仓库工具，借助于hadoop分布式存储计算平台和hive对SQL语句的理解能力，我们所要做的大部分工作就是输入和输出数据的适配，恰恰这两部分IO格式是千变万化的，我们只需要定制我们自己的输入输出适配器，hive将为我们透明化存储和处理这些数据，大大简化我们的工作。本文的重心也正在于此，这部分工作相信每一个做数据分析的朋友都会面对的，希望对您有益。
Hive综合应用案例 — 用户搜索日志分析
J_zrm的博客
12-20 7507
---------- 禁止修改 ---------- drop database if exists mydb cascade; ---------- 禁止修改 ---------- ---------- begin ---------- ---创建mydb数据库 create database mydb; ---使用mydb数据库 use mydb; ---创建表db_search create table db_search( id int, key1 string, ranking .
华为编程题目（实时更新）
最新发布
weixin_45564943的博客
05-17 2614
实时更新华为的重难点编程题目
自学大数据：Hive基于搜狗搜索的用户日志行为分析
08-29 6577
每天很多都听到很多关于wome
Hive基于搜狗搜索的用户日志行为分析
gywtzh0889的专栏
03-21 3615
问题导读 1.本文是如何学习大数据的？ 2.用户查询中包含的中文、英文字数的平均个数,本文提出哪两个思路？ 3.用户访问应用的时间特点如何使用hive实现统计的？ 前言 ”大数据时代“，“大数据/云计算”，“大数据平台”，每天听到太多的大数据相关的词语，好像现在说一句话不跟大数据沾边都不好意思说自己是做IT的。可能这与整个IT圈子的炒作也有关联，某一个方面来看其实就是一营销术语。很多
Hive_基于Hive的网站日志分析
whisky_12的博客
12-21 1316
文章目录 12
利用Hive进行复杂用户行为大数据分析及优化案例
02-20
利用Hive进行复杂用户行为大数据分析及优化案例（全套视频+课件+代码+讲义+工具软件），具体内容包括： 01_自动批量加载数据到hive 02_Hive表批量加载数据的脚本实现（一） 03_Hive表批量加载数据的脚本实现（二） ...
基于Hadoop网站流量日志数据分析系统.zip
06-24
基于Hadoop网站流量日志数据分析系统 1、典型的离线流数据分析系统 2、技术分析 - Hadoop - nginx - flume - hive - mysql - springboot + mybatisplus+vcharts nginx + lua 日志文件埋点的 基于Hadoop网站流量...
基于 Hive的数据分析案例 -MM聊天软件数据分析
06-16
本资源摘要信息主要介绍了基于 Hive 的数据分析案例，通过对 MM 聊天软件的数据进行统计分析，了解用户行为，实现精准的用户画像，并为公司的发展决策提供精确的数据支撑。 知识点一：Hive 数据分析 Hive 是基于 ...
基于Hive的淘宝用户行为数据分析.docx
11-01
总结，基于Hive的淘宝用户行为数据分析是一项综合运用大数据处理技术、数据库管理和统计分析的实践，其结果对电商平台的运营决策具有重要指导价值。随着大数据技术的不断发展，此类分析将变得更加精细化和智能化。
hadoop实现网站流量数据分析（MapReduce+hive）程序+说明.rar
06-28
标题中的“hadoop实现网站流量数据分析（MapReduce+hive）程序+说明.rar”指的是一个使用Hadoop框架，结合MapReduce和Hive技术进行网站流量数据分析的项目。这个项目可能包含了程序代码、配置文件以及详细的使用说明...
论文研究-基于Hive的海量搜索日志分析系统研究.pdf
07-22
针对传统分布式模型在海量日志并行处理时的可扩展性和并行程序编写困难的问题, 提出了基于Hive的Web海量搜索日志分析机制。利用HQL语言以及Hadoop分布式文件系统(HDFS)和MapReduce编程模式对海量搜索日志进行分析处理, 对用户搜索行为进行了分析研究。对用户搜索行为中的查询热点主题、用户点击数和URL排名、查询会话的分析结果对于搜索引擎的排序算法和系统优化都有一定的指导意义。
hive案例-用户行为日志分析
kylin_xue的专栏
02-18 4551
目录 背景 建立相关表 weblog表 member用户表 orders订单表  hive用户日志分析 简单查询、关联查询 日期函数、正则表达式、窗口函数 用户画像标签库的建立和使用【灵活使用行转列、列转行操作】 背景 用户行为日志格式如下： {"address":{"country":"中国","province":"山东","city":"济南&qu
搜狗日志Hive数据分析
weixin_34055787的博客
02-10 1585
目录 Hive数据分析... 4 一、数据处理.... 4 1.1处理不符合规范的数据。... 4 1.2访问时间分段。... 5 二、基本统计信息.... 6 三、数据属性基础分析.... 6 3.1用户ID分析... 6 3.1.1UID的查询次数。... 6 3.1.2UID频度排名分析。... 7 3.2搜索关键词分析... 8 3.2.1热词...
hive读取mysql日志_基于hive的日志分析系统
weixin_34357697的博客
02-03 294
hive简介hive是一个基于hadoop的开源数据仓库工具，用于存储和处理海量结构化数据。它把海量数据存储于hadoop文件系统，而不是数据库，但提供了一套类数据库的数据存储和处理机制，并采用HQL(类SQL)语言对这些数据进行自动化管理和处理。我们可以把hive中海量结构化数据看成一个个的表，而实际上这些数据是分布式存储在HDFS中的。Hive经过对语句进行解析和转换，最终生成一系列基于had...
基于Hive的搜狗搜索日志与结果Python可视化设计
毕业作品网站
09-25 1256
id=14908接下来的步骤主要是：元数据库 Mysql 安装、Hive 安装部署、Hive SQL 数据分析、数据可视化。主要步骤包括：安装部署 Hive、启动 Hadoop 集群、进入 Hive 命令行、创建数据库和数据表、加载或导入数据、用Hive SQL 完成需求、使用 Python 实现数据可视化。本实验安装 MySQL 是为了给 Hive 提供元数据存储库，主要包括：yum 安装 MySQL、修改 MySQL root 密码、添加 zkpk 用户并赋予远程访问权限、修改数据库默认编码。
Hive学习六:HIVE日志分析（用户画像）
youfashion的博客
04-19 5202
Hive学习六:HIVE日志分析（用户画像）标签（空格分隔）： HiveHive学习六HIVE日志分析用户画像 案例分析思路 一创建临时中间表 二将中间结果存放到临时表中 三创建结果表并存入最终jieguoji 总结 案例分析思路根据原始数据表里面的信息提取用户画像信息，一方面实现难度较大，另一方面由于数据量较大，从而导致实现的性能较差。由于以上2点，所以考虑从原始表中提取用户的会话信息放到临时中间
hive-学习搜狗日志分析
LLMUZI123456789的博客
11-28 879
round(sum(if(t1.user_kw_rank>3,1,0))/count(t1.uid),2)*100 || ‘%’ over_three_rts --搜索三次以上。,round(sum(if(t1.user_kw_rank=2,1,0))/count(t1.uid),2)*100 || ‘%’ tow_nums_rts --只搜索两次。sum(if(instr(t.url,t.keyword)>0,1,0)) – 27561 可看出大部分搜索URL，并不能得到自己想要的结果。
hive统计每日的活跃用户和新用户sql开发（附shell脚本）
laozaoxiaowanzi的博客
12-23 2062
假如有一个web系统，每天生成以下日志文件： 2020年12月21日数据 192.228.33.6,hunter,2020-12-21 10:30:20,/a 192.228.33.7,hunter,2020-12-21 10:30:26,/b 192.228.33.6,jack,2020-12-21 10:30:27,/a 192.228.33.8,tom,2020-12-21 10:30:28,/b 192.228.33.9,rose,2020-12-21 10:30:30,/b 192.228.3
Hive数据分析实战：MM聊天软件2021年11月1日数据挖掘
案例涵盖了多个分析需求，如统计总消息量、每小时消息量、地区消息分布、用户发送和接收消息数、Top10活跃用户等。为了实现这些需求，首先需要创建Hive数据库和表结构，然后进行数据导入和SQL查询分析。" 在这个...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值