大数据学习-Hive

介绍

分布式 SQL 计算

做数据的统计分析，SQL 是最方便的工具

在大数据中，有很多的统计分析场景，那么 SQL 来处理大数据是非常合适且频繁的

以后可能就是 SQL Boy 了，所以学习前需要有 MySQL 的基础

Hive 的功能

是一个分布式 SQL 计算工具，底层由 MapReduce 实现

将 SQL 语句翻译为 MapReduce 程序，然后执行，即写的是 SQL，但是执行的是 MapReduce 程序

Hive 基础架构

元数据管理

一般就是保存在数据库中，MySQL

SQL 解析器

具体来说就是 Hive Driver，完成语法解析、编译优化和执行等操作

用户接口

有命令行、JDBC、web UI 等一系列工具，方便用户使用 Hive

Hive 部署

Hive 是一个单机工具，只需要放在一台服务器中即可

这里需要安装两个东西：Hive、MySQL 5.7，都放在 node1 中

安装 MySQL 5.7

略

安装 Hive

需要先配置 Hadoop 的代理用户

修改 Hadoop 文件

core-site.xml 文件添加这些，并分发到其他节点，重启 Hadoop 集群

<property>

    <name>hadoop.proxyuser.hadoop.hosts</name>

    <value>*</value>

</property>

<property>

    <name>hadoop.proxyuser.hadoop.groups</name>

    <value>*</value>

</property>

把配置文件发送给其他服务器

cd /export/server/hadoop/etc/hadoop

scp * node2:/export/server/hadoop-3.3.4/etc/hadoop/

scp * node3:/export/server/hadoop-3.3.4/etc/hadoop/

正式安装

切换到hadoop用户

su - hadoop

下载Hive安装包：

http://archive.apache.org/dist/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz

解压到node1服务器的：/export/server/内

tar -zxvf apache-hive-3.1.3-bin.tar.gz -C /export/server/

设置软连接

ln -s /export/server/apache-hive-3.1.3-bin /export/server/hive

将 MySQL 驱动放入 Hive 文件夹的 lib 目录中

# 下载MySQL驱动包

https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.34/mysql-connector-java-5.1.34.jar

 

# 将下载好的驱动jar包，放入：Hive安装文件夹的lib目录内

配置 Hive

在 Hive 的 conf 目录内，新建 hive-env.sh 文件，填入以下环境变量内容：

cd /export/server/hive/conf

vim hive-env.sh

export HADOOP_HOME=/export/server/hadoop

export HIVE_CONF_DIR=/export/server/hive/conf

export HIVE_AUX_JARS_PATH=/export/server/hive/lib

在 Hive 的 conf 目录内，新建 hive-site.xml 文件，填入以下内容：

vim hive-site.xml

<configuration>

    <property>

        <name>javax.jdo.option.ConnectionURL</name>

        <value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;usesSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>

    </property>

    <property>

        <name>javax.jdo.option.ConnectionDriverName</name>

        <value>com.mysql.jdbc.Driver</value>

    </property>

    <property>

        <name>javax.jdo.option.ConnectionUserName</name>

        <value>root</value>

    </property>

    <property>

        <name>javax.jdo.option.ConnectionPassword</name>

        <value>1234</value>

    </property>

    <property>

        <name>hive.server2.thrift.bind.host</name>

        <value>node1</value>

    </property>

    <property>

        <name>hive.metastore.uris</name>

        <value>thrift://node1:9083</value>

    </property>

    <property>

        <name>hive.metastore.event.db.notification.api.auth</name>

        <value>false</value>

    </property>

</configuration>

注意了，有一处 1234，这个应该是用 root 连接 MySQL 的密码，要和创建 MySQL 时设置的密码一样

初始化元数据库

在 MySQL 中创建数据库 hive，首先需要进入 MySQL

CREATE DATABASE hive CHARSET UTF8;

执行元数据库初始化命令

cd /export/server/hive/bin

./schematool -initSchema -dbType mysql -verbos

初始化成功后，去看 hive 数据库，会发现多了 74 张表，都是 Hive 的元数据

启动 Hive

创建 hive 日志文件夹

mkdir /export/server/hive/logs

启动元数据管理服务

必须启动，否则无法工作

# 前台启动：

/export/server/hive/bin/hive --service metastore

# 后台启动：

nohup /export/server/hive/bin/hive --service metastore >> /export/server/hive/logs/metastore.log 2>&1 &

输入 jps，可以看到 RunJar，说明该服务已经启动

启动客户端服务

先启动 HDFS 和 Yarn

二选一（当前先选择 Hive Shell 方式）

Hive Shell 方式（可以直接写SQL）：

/export/server/hive/bin/hive

输入 show databases;可以看到 default 数据库，说明部署成功了

输入 exit;退出前台程序

Hive ThriftServer 方式（不可直接写SQL，需要外部客户端链接使用）：

/export/server/hive/bin/hive --service hiveserver2

Hive列注释、表注释等乱码解决方案

在 Navicat 中打开 Hive 数据库，然后执行下面的 SQL

-- 在Hive的MySQL元数据库中执行

use hive;

-- 修改字段注释字符集

alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

-- 修改表注释字符集

alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

-- 修改分区表参数，以支持分区键能够用中文表示

alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

-- 修改索引注解

alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

Hive 基本使用

首先需要启动 Metastore 服务

在前台启动 Hive 后，可以创建表，很像 SQL

create table test(

    id int,

    name string,

    gender string

);

这里的数据类型和 MySQL 的关键字不同，但是含义是一样的

插入数据

insert into test values(1,'lyj','male');

你会发现插入速度很慢，还会打印很多日志

hive> insert into test values(1,'lyj','male');

Query ID = hadoop_20240412171222_01bc3760-4c15-40d2-b73d-a6b09c6aedc0

Total jobs = 3

Launching Job 1 out of 3

Number of reduce tasks determined at compile time: 1

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1712909011549_0001, Tracking URL = http://node1:8089/proxy/application_1712909011549_0001/

Kill Command = /export/server/hadoop/bin/mapred job  -kill job_1712909011549_0001

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

2024-04-12 17:13:31,441 Stage-1 map = 0%,  reduce = 0%

2024-04-12 17:14:03,733 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.89 sec

2024-04-12 17:14:15,768 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.09 sec

MapReduce Total cumulative CPU time: 6 seconds 90 msec

Ended Job = job_1712909011549_0001

Stage-4 is selected by condition resolver.

Stage-3 is filtered out by condition resolver.

Stage-5 is filtered out by condition resolver.

Moving data to directory hdfs://node1:8020/user/hive/warehouse/test/.hive-staging_hive_2024-04-12_17-12-22_145_7390953200422943167-1/-ext-10000

Loading data to table default.test

MapReduce Jobs Launched:

Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 6.09 sec   HDFS Read: 16364 HDFS Write: 276 SUCCESS

Total MapReduce CPU Time Spent: 6 seconds 90 msec

OK

Time taken: 117.948 seconds

http://node1:8088/，来访问这个，也能看到 Yarn 中的任务

http://node1:9870/explorer.html#/，有这个文件夹

/user/hive/warehouse/test，这就是创建表的文件夹，数据以文本文件存在 HDFS 中

Hive 客户端

shell 客户端，可以直接写 SQL

另一个是 hiveservice2，需要外部客户端连接才能间接使用

nohup /export/server/hive/bin/hive --service metastore >> /export/server/hive/logs/hiveserver2.log 2>&1 &

nohup /export/server/hive/bin/hive --service hiveserver2 >> /export/server/hive/logs/hiveserver2.log 2>&1 &

输入 jps，能看到 RunJar 即可（有重复也没事）

hiveserver2 默认占用 10000 端口，通过这个命令可查看

netstat -anp | grep 10000

beeline 内置客户端

/export/server/hive/bin/beeline

启动了 hiveserver2 后，执行上面的命令，这是 Hive 自带的客户端

启动后需要连接 beeline

!connect jdbc:hive2://node1:10000

然后要输入用户名：hadoop，没有设置密码就直接回车

DBeaver 客户端

发现 Navicat 无法连接，所以就下载它了

编辑驱动这，点击 库，把默认给的都删除，选择下载好的驱动

Hive 实操

数据库操作

创建数据库

 CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION 'path'] [COMMENT database_comment];

1. IF NOT EXISTS，如存在同名数据库不执行任何操作，否则执行创建数据库操作
2. [LOCATION]，自定义数据库存储位置，如不填写，默认数据库在HDFS的路径为：/user/hive/warehouse
3. [COMMENT database_comment]，可选，数据库注释

删除数据库

 DROP DATABASE [IF EXISTS] db_name [CASCADE];

1. [IF EXISTS]，可选，如果存在此数据库执行删除，不存在不执行任何操作
2. [CASCADE]，可选，级联删除，即数据库内存在表，使用CASCADE可以强制删除数据库

数据库修改 LOCATION

 ALTER DATABASE database_name SET LOCATION hdfs_path;

== 不会在HDFS对数据库所在目录进行改名，只是修改location后，新创建的表在新的路径，旧的不变 ==

默认存在 HDFS 的 /user/hive/warehouse 路径下

用 location 指定数据存放的路径

选择数据库

 USE db_name;

1. 选择数据库后，后续SQL操作基于当前选择的库执行
2. 如不使用use，默认在default库执行

若想切换回使用default库

 USE DEFAULT;

查询当前USE的数据库

 SELECT current_database();

表操作

创建表基本和 SQL 差不多，不过多了一些关键字

数据类型常用：int,varchar,string,date,timestamp,double 等

内部表

创建表的 SQL 没有使用 external 关键字修饰，那这个表就是内部表，就是普通表

删除内部表会直接删除元数据和它存储的数据

持久使用

如果删除了内部表，在集群的 MySQL 的 Hive 数据库中，有一个表是 TBLS，里面记录着有哪些表

外部表

创建表时加上关键字 external，就是外部表，同时还必须加上关键字 location 指定数据存放路径，这个路径不能指定到文件级别，只能是目录级别

这个外部，指的是表数据可以放在任意位置，通过 location 关键字指定

另一个意思就是数据不是由 Hive 管理的，所以在删除表时，只会删除元数据，数据还存在

临时使用，表结构和数据是相互独立的

可以先创建表，然后将数据移动到外部表指定的 location 中

可以先有数据，然后创建表时通过 location 指向数据

数据分隔符

直接查看 HDFS 中的文件，发现两列直接的数据之间好像没有间隔

其实是有的，默认的分隔符是：'\001'，是一个特殊的字符，无法用键盘打出来，在 ASCII 表中代表 SOH

可以自定义

建表语句的括号后面加上这一串，很长的固定写法

row format delimited fields terminated by '你要的字符'

这个表示以制表符作为分隔符

内外部表的转换

查看表的各种信息

desc formatted 表名;

有两个关键 key：

1. location（数据存放路径）
2. table type（内部表是 MANAGED_TABLE，外部表是 EXTERNAL_TABLE）

内转外

alter table 表名 set tblproperties('EXTERNAL'='TRUE'); -- 注意单词不要写错，必须大写

外转内

alter table 表名 set tblproperties('EXTERNAL'='FALSE');

数据导入

将文件数据导入表中

load data [local] inpath '文件路径' [overwrite] into table 表名;

1. local：数据是否在本地，在 Linux 上需要使用 file://路径；如果在 HDFS 可以不用写 local
2. overwrite：是否覆盖旧数据，不覆盖就是追加

测试表结构

CREATE TABLE myhive.test_load(

  dt string comment '时间（时分秒）',

  user_id string comment '用户ID',

  word string comment '搜索词',

  url string comment '用户访问网址'

) comment '搜索引擎日志表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

从 Linux 中加载数据到表中

把资料中的 search_log.txt 上传到 /home/hadoop（Linux 路径），执行

load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load;

可以看到数据正确加载了，因为资料中数据的结构和表结构完全对应

从 HDFS 中加载数据

首先把表删除了，然后通过 web 将刚才的文件上传到 HDFS 的 /tmp 目录下，执行

load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load;

数据也可以正常加载，不过在 HDFS 的 /tmp/search_log.txt 文件消失了，而是转移到了 /user/hive/warehouse/myhive.db/test_load 目录下

从其他表中导入数据

这种方式速度就慢很多，因为走 MapReduce

当数据量大的时候，load 和 这个方式差不多

再创建一个表，结构一样，名字不同

CREATE TABLE myhive.test_load2(

  dt string comment '时间（时分秒）',

  user_id string comment '用户ID',

  word string comment '搜索词',

  url string comment '用户访问网址'

) comment '搜索引擎日志表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

将查询出来的数据，插入到前面的表中

可以指定条件

追加

insert into table tbl1 select * from tbl2;

覆盖

insert overwrite table tbl1 select * from tbl2;

数据导出

有 local 就是导出到 Linux，没有就是导出到 HDFS

还是使用 MapReduce

使用 SQL

将查询结果导出到 Linux，使用默认的分隔符

insert overwrite local directory '/home/hadoop/export1'

    select * from test_load ;

将查询的结果导出到本地 - 指定分隔符

insert overwrite local directory '/home/hadoop/export2'

    row format delimited fields terminated by '\t'

    select * from test_load;

将查询的结果导出到 HDFS 上 (不带 local 关键字)

insert overwrite directory '/tmp/export'

    row format delimited fields terminated by '\t'

    select * from test_load;

使用 Hive Shell

这样导出快很多

/export/server/hive/bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

执行 SQL 脚本

/export/server/hive/bin/hive -f export.sql > /home/hadoop/export4/export4.txt

分区表

就是可以按照指定的规则，将一个表划分为多个表

比如对于一个很大的表，可以按照按照月份进行划分，这样可以得到多个表，每个表代表一个月

分区后，在查询时加上条件，这样速度就会快很多

实现：因为 Hive 中的数据都是存在文件夹中的，所以可以用文件夹来拆分表

所以也可以有多个层级的分区表

SQL

create table tablename(...)

    partitioned by (分区列 列类型, ......)

   

row format delimited fields terminated by '';

单层级分区

-- 按照月份进行分区

create table score(

    id string,

    cid string,

    score int

) partitioned by (month string)

row format delimited fields terminated by '\t';

加载数据，但是数据中没有月份这个列，表中也没有

load data local inpath '/home/hadoop/score.txt'

    into table score

    partition(month='202005');

但是数据加载完毕后，会多出一列，在 HDFS 中以分区 month=202005 作为文件夹名称

多层级分区

create table score222(

    id string,

    cid string,

    score int

) partitioned by (year string,month string,day string)

row format delimited fields terminated by '\t';

指定三个分区，那么在加载数据也需要指定三个，不能只有 year

load data local inpath '/home/hadoop/score.txt'

    into table score222

    partition(year='2024',month='04',day='13');

目录结构：/user/hive/warehouse/myhive.db/score222/year=2024/month=04/day=13

分区增删改查

注意删除分区，会把对应的数据一起删除

分桶表

数据查询

前期准备

准备订单表结构和数据

CREATE DATABASE itheima;

USE itheima;

CREATE TABLE itheima.orders (

    orderId bigint COMMENT '订单id',

    orderNo string COMMENT '订单编号',

    shopId bigint COMMENT '门店id',

    userId bigint COMMENT '用户id',

    orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',

    goodsMoney double COMMENT '商品金额',

    deliverMoney double COMMENT '运费',

    totalMoney double COMMENT '订单金额（包括运费）',

    realTotalMoney double COMMENT '实际订单金额（折扣后金额）',

    payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',

    isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',

    userName string COMMENT '收件人姓名',

    userAddress string COMMENT '收件人地址',

    userPhone string COMMENT '收件人电话',

    createTime timestamp COMMENT '下单时间',

    payTime timestamp COMMENT '支付时间',

    totalPayFee int COMMENT '总支付金额'

) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/itheima_orders.txt' INTO TABLE itheima.orders;

准备用户表结构和数据

CREATE TABLE itheima.users (

    userId int,

    loginName string,

    loginSecret int,

    loginPwd string,

    userSex tinyint,

    userName string,

    trueName string,

    brithday date,

    userPhoto string,

    userQQ string,

    userPhone string,

    userScore int,

    userTotalScore int,

    userFrom tinyint,

    userMoney double,

    lockMoney double,

    createTime timestamp,

    payPwd string,

    rechargeMoney double

) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/itheima_users.txt' INTO TABLE itheima.users;

基本查询

基本条件查询和 SQL 一样，但是多了一些其他的

1. cluster by
2. distribute by
3. sort by

这种简单的查询出结果很快

这种用聚合函数的，就会走 MapReduce，很慢

JOIN 订单表和用户表，找出用户名

SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime

FROM itheima.orders o JOIN

    itheima.users u

    ON o.userid = u.userid;

左外关联，订单表和用户表，找出用户名

SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime

FROM itheima.orders o LEFT JOIN

    itheima.users u

    ON o.userid = u.userid;

可以发现和 SQL 没有区别

RLIKE-正则表达式

正则表达式能匹配的字符和规则更多，功能更加强大

多个正则作为条件

-- 查询地址是广东，并且姓张的订单信息

SELECT  *

FROM itheima.orders 

WHERE useraddress RLIKE '.*广东.*'

  AND username RLIKE '^张.*';

UNION-联合

就是和 SQL 的 UNION 一样，将两个查询结果合并为一张表（所以查询结果的结果需要一致）

默认去重，如果不想去重，就使用 UNION ALL

SELECT * FROM course WHERE t_id = '周杰轮'

   

UNION

SELECT * FROM course WHERE t_id = '王力鸿'

这样得到的结果可以作为临时表，用来作为查询表、导入导出数据的表等

Sampling-随机抽样

对一个很大的表进行随机抽样，快速从大表中随机抽取一些数据给用户看

当表很大的时候，进行 select * 都很慢

本质操作是使用 TABLESAMPLE 函数，可以基于分桶进行抽样，也可以基于数据块抽样

随机分桶（组）抽样

SELECT ...

FROM tbl

TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

1. y 表示将表数据随机划分成 y 份（y 个桶）
2. x 表示从 y 里面随机抽取 x 份数据作为取样
3. colname 表示随机的依据基于某个列的值（基于某个列的值进行 Hash 取模）
   1. 如果其他条件不变，每次执行结果不变
4. rand() 表示随机的依据基于整行（随机用某个列的值去做 Hash 取模）
   1. 每次的结果都不同（连行数都不同，可以用 limit 限制结果行数）

-- 将表数据随机划分为 10 份，从 10 份中随机抽取 1 份作为结果

-- 随机的依据是 username 字段

SELECT username, orderId, totalmoney

FROM itheima.orders

TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);

-- 分桶表和普通表都可用

SELECT *

FROM itheima.orders

TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand())

limit 10;

当查询的表是分桶表时，速度会比 块抽样 快

基于数据块抽样

SELECT ...

FROM tbl

TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));

1. num ROWS 表示抽样 num 条数据
2. num PERCENT 表示抽样 num 百分百比例的数据
3. num(K|M|G) 表示抽取 num 大小的数据，单位可以是 K、M、G 表示 KB、MB、GB

这仨只能要其中一个作为 TABLESAMPLE 的参数，比如 100 rows；50 percent；1K，1M，1G

但是这个百分比不太准确，1000 条数据，要 1%，能得到 15 条

如果条件不变，结果也不变，因为没有随机的东西在里面

虚拟列

是 Hive 内置的可以在查询语句中使用的特殊标记，可以查询数据本身的三个参数

Hive 目前可用 3 个虚拟列：

1. INPUT__FILE__NAME，显示数据行所在的具体文件
2. BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
3. ROW__OFFSET__INSIDE__BLOCK，显示数据所在 HDFS 块的偏移量
   1. 查询虚拟列需要设置：SET hive.exec.rowoffset=true 才不会报错

-- 先执行它

SET hive.exec.rowoffset=true

SELECT userid ,username , INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK

FROM itheima.course;

作用

可以看到每条数据在存储上的具体信息，虚拟列不仅仅可以用于 SELECT，在 WHERE、GROUP BY 等均可使用

除此以外，在某些错误排查场景上，虚拟列可以提供相关帮助。

常用内置函数

-- 查看所有可用函数

show functions;

-- 查看函数使用方式

describe function extended count;

-- 数值函数

-- round 取整，设置小数精度

select round(3.1415926);-- 取整(四舍五入)

select round(3.1415926, 4);-- 设置小数精度4位(四舍五入)

-- 随机数

select rand();-- 完全随机

select rand(3);-- 设置随机数种子，设置种子后每次运行结果不变

-- 绝对值

select abs(-3);

-- 求PI

select pi();

-- 集合函数

-- 求元素个数

select size(work_locations) from test_array;

select size(members) from test_map;

-- 取出map的全部key

select map_keys(members) from test_map;

-- 取出map的全部value

select map_values(members) from test_map;

-- 查询array内是否包含指定元素，是就返回True

select * from test_array where ARRAY_CONTAINS(work_locations, 'tianjin');

-- 排序

select *, sort_array(work_locations) from test_array;

-- 类型转换函数

-- 转二进制

select binary('hadoop');

-- 自由转换，类型转换失败报错或返回NULL

select cast('1' as bigint);

-- 日期函数

-- 当前时间戳

select current_timestamp();

-- 当前日期

select current_date();

-- 时间戳转日期

select to_date(current_timestamp());

-- 年月日季度等

select year('2020-01-11');

select month('2020-01-11');

select day('2020-01-11');

select quarter('2020-05-11');

select dayofmonth('2020-05-11');

select hour('2020-05-11 10:36:59');

select minute('2020-05-11 10:36:59');

select second('2020-05-11 10:36:59');

select weekofyear('2020-05-11 10:36:59');

-- 日期之间的天数

select datediff('2022-12-31', '2019-12-31');

-- 日期相加、相减

select date_add('2022-12-31', 5);

select date_sub('2022-12-31', 5);

更多的函数讲解在本目录的 docx 文件中有，还有自定义函数，但是很麻烦

实操

需求

每个需求都写到一个新的表中

1. 统计每天的总消息量
2. 统计今日每小时消息量、发送和接收用户数
3. 统计今日各地区发送消息数据量
4. 统计今日发送消息和接收消息的用户数
5. 统计今日发送消息最多的Top10用户
6. 统计今日接收消息最多的Top10用户
7. 统计发送人的手机型号分布情况
8. 统计发送人的设备操作系统分布情况

分析方法

先分析出需要查找哪些字段

再看需要用聚合函数统计那些字段

最后根据需求设置查询条件

数据内容

这里只是学习，所以都能看到这些假数据

创建表结构 & 导入数据

-- 建表

create table db_msg.tb_msg_source(

    msg_time string comment "消息发送时间",

    sender_name string comment "发送人昵称",

    sender_account string comment "发送人账号",

    sender_sex string comment "发送人性别",

    sender_ip string comment "发送人ip地址",

    sender_os string comment "发送人操作系统",

    sender_phonetype string comment "发送人手机型号",

    sender_network string comment "发送人网络类型",

    sender_gps string comment "发送人的GPS定位",

    receiver_name string comment "接收人昵称",

    receiver_ip string comment "接收人IP",

    receiver_account string comment "接收人账号",

    receiver_os string comment "接收人操作系统",

    receiver_phonetype string comment "接收人手机型号",

    receiver_network string comment "接收人网络类型",

    receiver_gps string comment "接收人的GPS定位",

    receiver_sex string comment "接收人性别",

    msg_type string comment "消息类型",

    distance string comment "双方距离",

    message string comment "消息内容"

);

导入数据，从 Linux 本地导入

先把 chat_data-30W.csv 上传到 /home/hadoop

load data local inpath 'chat_data-30W.csv' into table tb_msg_source;

先上传到 HDFS，再导入表

-- 通过 Linux 命令上传到 HDFS

hadoop fs -mkdir -p /chatdemo/data

hadoop fs -put chat_data-30W.csv /chatdemo/data/

-- 加载数据到表中，基于HDFS加载

load data inpath '/chatdemo/data/chat_data-30W.csv' into table tb_msg_source;

-- 验证一下表的数量

select count(*) from tb_msg_source;

数据清洗

问题

有些数据的 GPS 是空的，不能用

需求中需要统计每天和每小时的消息量，但是字段中没有天和小时字段，只有整体时间的字段

需求中需要根据经纬度构建可视化地图，但是数据中的经纬度是混合在一起的，不好处理

解决方法

过滤字段为空的数据：where LENGTH(sender_gps) > 0

通过时间构建天和小时的字段：date(msg_time) 可以将数据搞得只有年月日，hour(msg_time) 只有时分秒

从经纬度中提取出经度和纬度：split(sender_gps,',')，这样得到一个数组，再通过索引得到值

将清洗后的数据放入新的表

create table db_msg.tb_msg_etl(

    msg_time string comment "消息发送时间",

    sender_name string comment "发送人昵称",

    sender_account string comment "发送人账号",

    sender_sex string comment "发送人性别",

    sender_ip string comment "发送人ip地址",

    sender_os string comment "发送人操作系统",

    sender_phonetype string comment "发送人手机型号",

    sender_network string comment "发送人网络类型",

    sender_gps string comment "发送人的GPS定位",

    receiver_name string comment "接收人昵称",

    receiver_ip string comment "接收人IP",

    receiver_account string comment "接收人账号",

    receiver_os string comment "接收人操作系统",

    receiver_phonetype string comment "接收人手机型号",

    receiver_network string comment "接收人网络类型",

    receiver_gps string comment "接收人的GPS定位",

    receiver_sex string comment "接收人性别",

    msg_type string comment "消息类型",

    distance string comment "双方距离",

    message string comment "消息内容",

    msg_day string comment "消息日",

    msg_hour string comment "消息小时",

    sender_lng double comment "经度",

    sender_lat double comment "纬度"

);

ETL——Extract Transform Load，抽取 转换 加载

INSERT OVERWRITE TABLE db_msg.tb_msg_etl -- 把from后的表数据导入这个表

SELECT

    *,

    DATE(msg_time) AS msg_day, -- 从msg_time字段中取出天

    HOUR(msg_time) AS msg_hour,  -- 从msg_time字段中取出小时

    SPLIT(sender_gps, ',')[0] AS sender_lng, -- 取出经度

SPLIT(sender_gps, ',')[1] AS sender_lat  -- 取出纬度

FROM db_msg.tb_msg_source -- 目标表

WHERE LENGTH(sender_gps) > 0; -- 去除无gps数据的数据

实操

给列起别名不能用中文，但是表和列的注释可以

-- 如果创建表的数据是从查询结果得到的，需要有 as

create table xxx as

select ……

如果发现表的注释是乱码，在上面有解决方法，我做了之后是能够解决乱码问题

统计每天的总消息量

每天，所以需要把天这个字段查询出来；

消息量，需要用 count，因为是每天，所以用天数来分组，group by msg_day

CREATE table tb_everyday_msg_count

COMMENT "每日消息总量" as

select  msg_day ,

        count(*) total_msg_count

from tb_msg_etl

group by msg_day ;

统计今日每小时消息量、发送和接收用户数

显然要把小时查询出来，发送接收用户数需要用 count，在 count 中写表达式去重；最后根据 hour 分组

create table tb_per_hour_sender_receiver_count as

select

msg_hour,

COUNT(*) msg_count ,

count(DISTINCT sender_name) sender_count,

count(DISTINCT receiver_name) receiver_count

from tb_msg_etl

group by msg_hour;

统计今日各地区发送消息数据量

这里是用经纬度来代替省份城市，那经纬度肯定要查出来，还有 count(*) msg_count 和天数，分组这里要根据三个字段来分组

create table tb_per_zone_msg_count

comment "今日各地区发送消息数据量" as

select

msg_day,

sender_lng,

sender_lat,

count(*) msg_count

from tb_msg_etl

group by msg_day, sender_lng, sender_lat;

统计今日发送消息和接收消息的用户数

查询出天数，发送者和接收者的数量，并去重，最后根据天数来分组

create table tb_today_sender_receiver_count as

select

msg_day,

count(DISTINCT sender_name) sender_count,

count(DISTINCT receiver_name) receiver_count

from tb_msg_etl

group by msg_day;

统计今日发送消息最多的 Top10 用户

查询出天数，发送者和消息的数量，根据天数和发送者名字分组，之后倒序排序消息数量，取 10 条数据

因为倒序了，所以前 10 条数据就是发送消息最多的用户

create table tb_send_msg_count_user_top10 as

select

msg_day,

sender_name,

count(*) msg_count

from tb_msg_etl

group by msg_day, sender_name

order by msg_count DESC

limit 10;

统计今日接收消息最多的Top10用户

和上面类似，今天，接收消息用户，最多

所以需要查询的字段肯定有 msg_day，receiver_name

因为要统计接收消息数，所以肯定需要 count(*) msg_count，那这个统计谁的数量？这个来看表，每条消息都有接收者的名字，所以这个统计的是接收者的名字的数量

create table tb_receive_msg_count_top10 as

select

msg_day,

receiver_name,

count(*) msg_count

from tb_msg_etl

group by msg_day,receiver_name

order by msg_count DESC

limit 10;

统计发送人的手机型号分布情况

应该是统计每种手机的占比，推荐乘上 100，这样百分比好看一些

字段：sender_phonetypecount(*)/3000 type_percent

create table tb_sender_phone_type_percent as

select

sender_phonetype,

count(*)/3000 type_percent

from tb_msg_etl

group by sender_phonetype;

统计发送人的设备操作系统分布情况

同上，统计每种操作系统的占比

字段：sender_oscount(*)/3000 type_percent

create table tb_sender_os_percent as

select

sender_os,

count(*)/3000 type_percent

from tb_msg_etl

group by sender_os

可视化展示

BI

这里使用 FineBI，只进行可视化展示

FineBI - BI数据分析软件_企业级大数据分析平台，来这注册下载后，输入给你的激活码

等待一会会自动弹出网页，这里我的用户名是 admin，密码是 123456，之后选择内置数据库

FineBI 连接 Hive

把课程提供的 5 个连接驱动

放到安装目录的这里：C:\FineBI6.0\webapps\webroot\WEB-INF\lib

来到 FineBI 的网页

右下角有个 从本地安装，这个是 Hive 隔离插件

最后重启 FineBI

来到网页端的这里，新建数据连接，找到 Hive

密码不用填写

测试连接成功

展示

然后点击 myhive，再点击新建数据集-数据库表，选择实操中创建的 8 个表

然后点击右上角的全局更新

然后点击最左边的 我的分析，新建文件夹：myhive，新建分析主题，批量选择 8 个表

进到分析主题页面后，点击下面的组件标签

点击图表类型的 123，再选择表中的字段拖动到图形属性中

第五章-24-案例-可视化展现_哔哩哔哩_bilibili

其实就是熟练活

总结

其实 hadoop 那里真不难，就是环境的构建麻烦，Hive 这里就得实操了，写 SQL