hive3.1.x教程：数据分析1-统计每个用户的 IP 都关联了哪些人

作者：马育民 • • 阅读：10023

准备数据

数据说明

第一列：id
第二列：ip
第三列：访问日期

数据

注意： \N 导入到 hive 中，表示 NULL

100000001,192.168.1.1,2017-01-01
100000002,192.168.1.1,2017-01-01
100000003,192.168.1.2,2017-01-01
100000004,192.168.1.2,2017-01-01
100000005,192.168.1.3,2017-01-01
100000006,\N,2017-01-01
100000001,192.168.1.1,2017-01-01
100000002,192.168.1.1,2017-01-01
100000003,192.168.1.2,2017-01-01
100000007,192.168.1.1,2017-01-01
100000008,192.168.1.1,2017-01-01
100000009,192.168.1.4,2017-01-01
100000010,192.168.1.4,2017-01-01
100000011,\N,2017-01-01
100000016,192.168.1.6,2017-01-01
100000013,192.168.1.5,2017-01-01
100000017,\N,2017-02-01
100000009,192.168.1.4,2017-06-01
100000012,192.168.1.1,2017-07-01
100000014,192.168.1.5,2017-08-01
100000015,192.168.1.1,2017-09-01

上传

上传到 hadoop1 服务器的 /program 目录，比如文件名：1.txt

hive操作

登录 hadoop1 服务器

启动hive客户端

执行命令 hive，启动 hive 客户端，

创建表

create table url (id string,ip string,sysdate string) row format delimited fields terminated by','stored as textfile;

导入数据

load data local inpath "/program/1.txt" overwrite into table url;

数据分析1

统计 2017-01-01 一个 IP 有哪些用户访问？

提示：公司、家庭、学校，出口 IP 一般是相同的，统计哪些IP人口聚集

第一步

select ip,collect_set(id))
from url
where ip is not null and sysdate='2017-01-01'
group by ip;

解释：

  • collect_set：去重id，将所有非重复值，组成一个数组，无序

执行结果：

192.168.1.1     ["100000001","100000002","100000007","100000008"]
192.168.1.2     ["100000003","100000004"]
192.168.1.3     ["100000005"]
192.168.1.4     ["100000009","100000010"]
192.168.1.5     ["100000013"]
192.168.1.6     ["100000016"]

第二步

将第一步的数组，拼接成字符串，中间由 , 分割

select ip,concat_ws(',',collect_set(id))
from url
where ip is not null and sysdate='2017-01-01'
group by ip;

解释：

  • concat_ws：用指定 分隔符，将数组中的元素连接起来

执行结果：

192.168.1.1     100000001,100000002,100000007,100000008
192.168.1.2     100000003,100000004
192.168.1.3     100000005
192.168.1.4     100000009,100000010
192.168.1.5     100000013
192.168.1.6     100000016

数据分析2

统计 2017-01-01 以来，去重 IP，ID的数据

select ip,id from url where sysdate>='2017-01-01' group by ip,id;

执行结果：

192.168.1.1     100000001
192.168.1.1     100000002
192.168.1.2     100000003
192.168.1.2     100000004
192.168.1.3     100000005
NULL    100000006
192.168.1.1     100000007
192.168.1.1     100000008
192.168.1.4     100000009
192.168.1.4     100000010
NULL    100000011
192.168.1.1     100000012
192.168.1.5     100000013
192.168.1.5     100000014
192.168.1.1     100000015
192.168.1.6     100000016
NULL    100000017

数据分析3

统计每个用户，以及哪些用户，与该用户使用同一个IP。即：统计每个用户的 IP 都关联了哪些人。
提示：方便查看 “共享 IP 最多的用户”

将上面两个 SQL join 起来

select s1.id,concat_ws('_',collect_set(s2.idset)) as ip_idset
from
  (select ip,id from url where ip is not null and sysdate='2017-01-01' group by ip,id) s1
join
  (
    select ip,concat_ws('_',collect_set(cast(id as string))) as idset 
    from url
    where ip is not null and sysdate='2017-01-01'
    group by ip
  ) s2
on (s1.ip=s2.ip)
group by s1.id;

执行结果：

100000001       100000001_100000002_100000007_100000008
100000002       100000001_100000002_100000007_100000008
100000003       100000003_100000004
100000004       100000003_100000004
100000005       100000005
100000007       100000001_100000002_100000007_100000008
100000008       100000001_100000002_100000007_100000008
100000009       100000009_100000010
100000010       100000009_100000010
100000013       100000013
100000016       100000016

原文出处：/show_1GW2MJoNP52h.html

吉ICP备15003043号