大数据学习-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&usesSL=false&useUnicode=true&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
慢是由于现实执行的是 MapReduce，http://node1:8088/，来访问这个，也能看到 Yarn 中的使命
https://img-blog.csdnimg.cn/direct/62abfd75e32f4639aaf39fd4d34bbfd0.png
访问 http://node1:9870/explorer.html#/，有这个文件夹
https://img-blog.csdnimg.cn/direct/e368b3da0b09435485488653248caf81.png
/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 无法连接，所以就下载它了
https://img-blog.csdnimg.cn/direct/c468ca8b2b134833bc84400acfd8d45e.png
编辑驱动这，点击 库，把默认给的都删除，选择下载好的驱动
https://img-blog.csdnimg.cn/direct/a36471181180467e8dcdec8055eb5798.png
Hive 实操
数据库操作
创建数据库
CREATE DATABASE db_name ;
[*]IF NOT EXISTS，如存在同名数据库不执行任何操作，否则执行创建数据库操作
[*]，自界说数据库存储位置，如不填写，默认数据库在HDFS的路径为：/user/hive/warehouse
[*]，可选，数据库注释
删除数据库
DROP DATABASE db_name ;
[*]，可选，假如存在此数据库执行删除，不存在不执行任何操作
[*]，可选，级联删除，即数据库内存在表，使用CASCADE可以强制删除数据库
数据库修改 LOCATION
ALTER DATABASE database_name SET LOCATION hdfs_path;
== 不会在HDFS对数据库所在目录进行改名，只是修改location后，新创建的表在新的路径，旧的稳固 ==
默认存在 HDFS 的 /user/hive/warehouse 路径下
用 location 指定数据存放的路径
选择数据库
USE db_name;
[*]选择数据库后，后续SQL操作基于当前选择的库执行
[*]如倒霉用use，默认在default库执行
若想切换回使用default库
USE DEFAULT;
查询当前USE的数据库
SELECT current_database();
表操作
创建表根本和 SQL 差不多，不过多了一些关键字
数据类型常用：int,varchar,string,date,timestamp,double 等
内部表
创建表的 SQL 没有使用 external 关键字修饰，那这个表就是内部表，就是普通表
删除内部表会直接删除元数据和它存储的数据
恒久使用
假如删除了内部表，在集群的 MySQL 的 Hive 数据库中，有一个表是 TBLS，内里记录着有哪些表
https://img-blog.csdnimg.cn/direct/590d5e7826b44c68b558b89c22bbe181.png
外部表
创建表时加上关键字 external，就是外部表，同时还必须加上关键字 location 指定数据存放路径，这个路径不能指定到文件级别，只能是目录级别
这个外部，指的是表数据可以放在任意位置，通过 location 关键字指定
另一个意思就是数据不是由 Hive 管理的，所以在删除表时，只会删除元数据，数据还存在
暂时使用，表结构和数据是相互独立的
可以先创建表，然后将数据移动到外部表指定的 location 中
可以先有数据，然后创建表时通过 location 指向数据
数据分隔符
直接检察 HDFS 中的文件，发现两列直接的数据之间好像没有隔断
其实是有的，默认的分隔符是：'\001'，是一个特殊的字符，无法用键盘打出来，在 ASCII 表中代表 SOH
可以自界说
建表语句的括号后面加上这一串，很长的固定写法
row format delimited fields terminated by '你要的字符'
这个表示以制表符作为分隔符
https://img-blog.csdnimg.cn/direct/38d80aa90ffb4437be5dfbdf5ab4635c.png
内外部表的转换
检察表的各种信息
desc formatted 表名;
有两个关键 key：
[*]location（数据存放路径）
[*]table type（内部表是 MANAGED_TABLE，外部表是 EXTERNAL_TABLE）
内转外
alter table 表名 set tblproperties('EXTERNAL'='TRUE'); -- 留意单词不要写错，必须大写
外转内
alter table 表名 set tblproperties('EXTERNAL'='FALSE');
数据导入
将文件数据导入表中
load data inpath '文件路径' into table 表名;
[*]local：数据是否在当地，在 Linux 上需要使用 file://路径；假如在 HDFS 可以不消写 local
[*]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;
可以看到数据正确加载了，由于资料中数据的结构和表结构完全对应
https://img-blog.csdnimg.cn/direct/be0c8ad49fc1490d8351712b062be386.png
从 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 作为文件夹名称
https://img-blog.csdnimg.cn/direct/18e6e61693464963b898045de6c3b476.png
多层级分区
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
分区增删改查
https://img-blog.csdnimg.cn/direct/8ed95e39f9a440c090f20cbfc9ec768e.png
留意删除分区，会把对应的数据一起删除
https://img-blog.csdnimg.cn/direct/e4ec930d7d004d6fbabe4382008882f9.png
分桶表
数据查询
前期准备
准备订单表结构和数据
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 一样，但是多了一些其他的
[*]cluster by
[*]distribute by
[*]sort by
这种简单的查询出结果很快
https://img-blog.csdnimg.cn/direct/ba8513efa6ae478a8aac7387e53dd31b.png
这种用聚合函数的，就会走 MapReduce，很慢
https://img-blog.csdnimg.cn/direct/2bc9b1daa4484830bc75e2e4e174979c.png
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-正则表达式
正则表达式能匹配的字符和规则更多，功能更加强大
https://img-blog.csdnimg.cn/direct/e0fc47ca9d6e4efd835cb97614610bfc.png
多个正则作为条件
-- 查询地点是广东，并且姓张的订单信息
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()))
[*]y 表示将表数据随机分别成 y 份（y 个桶）
[*]x 表示从 y 内里随机抽取 x 份数据作为取样
[*]colname 表示随机的依据基于某个列的值（基于某个列的值进行 Hash 取模）
[*]假如其他条件稳固，每次执行结果稳固
[*]rand() 表示随机的依据基于整行（随机用某个列的值去做 Hash 取模）
[*]每次的结果都不同（连行数都不同，可以用 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));
[*]num ROWS 表示抽样 num 条数据
[*]num PERCENT 表示抽样 num 百分百比例的数据
[*]num(K|M|G) 表示抽取 num 巨细的数据，单位可以是 K、M、G 表示 KB、MB、GB
这仨只能要此中一个作为 TABLESAMPLE 的参数，比如 100 rows；50 percent；1K，1M，1G
但是这个百分比不太正确，1000 条数据，要 1%，能得到 15 条
https://img-blog.csdnimg.cn/direct/de122e323a7f45afb3219850133d4571.png
假如条件稳固，结果也稳固，由于没有随机的东西在内里
假造列
是 Hive 内置的可以在查询语句中使用的特殊标记，可以查询数据本身的三个参数
Hive 现在可用 3 个假造列：
[*]INPUT__FILE__NAME，体现数据行所在的具体文件
[*]BLOCK__OFFSET__INSIDE__FILE，体现数据行所在文件的偏移量
[*]ROW__OFFSET__INSIDE__BLOCK，体现数据所在 HDFS 块的偏移量
[*]查询假造列需要设置：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;
https://img-blog.csdnimg.cn/direct/3364591a7a23407495723995cb344b4a.png
作用
可以看到每条数据在存储上的具体信息，假造列不但仅可以用于 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 文件中有，还有自界说函数，但是很麻烦
实操
需求
每个需求都写到一个新的表中
[*]统计每天的总消息量
[*]统计今日每小时消息量、发送和吸收用户数
[*]统计今日各地区发送消息数据量
[*]统计今日发送消息和吸收消息的用户数
[*]统计今日发送消息最多的Top10用户
[*]统计今日吸收消息最多的Top10用户
[*]统计发送人的手机型号分布环境
[*]统计发送人的装备操作系统分布环境
分析方法
先分析出需要查找哪些字段
再看需要用聚合函数统计那些字段
最后根据需求设置查询条件
数据内容
这里只是学习，所以都能看到这些假数据
https://img-blog.csdnimg.cn/direct/bd985bd7a09846a7bb9be591a1630944.png
创建表结构 & 导入数据
-- 建表
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;
https://img-blog.csdnimg.cn/direct/9c67038a7dc142c38aa8c98862472e06.png
数据洗濯
问题
有些数据的 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, ',') AS sender_lng, -- 取出经度
SPLIT(sender_gps, ',') 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
https://img-blog.csdnimg.cn/direct/0e3530d0299245379ec926acd6e76a15.png
这里使用 FineBI，只进行可视化展示
FineBI - BI数据分析软件_企业级大数据分析平台，来这注册下载后，输入给你的激活码
等候一会会自动弹出网页，这里我的用户名是 admin，密码是 123456，之后选择内置数据库
FineBI 连接 Hive
把课程提供的 5 个连接驱动
https://img-blog.csdnimg.cn/direct/55c15c35c27142f08d3bacecd59e1e1d.png
放到安装目录的这里：C:\FineBI6.0\webapps\webroot\WEB-INF\lib
来到 FineBI 的网页
https://img-blog.csdnimg.cn/direct/655d9600aa434800bdcae186200275b3.png
右下角有个 从当地安装，这个是 Hive 隔离插件
https://img-blog.csdnimg.cn/direct/8ba429e7cac847178406895c4f9f029e.png
最后重启 FineBI
来到网页端的这里，新建数据连接，找到 Hive
https://img-blog.csdnimg.cn/direct/87b853f312024de0a40e7c233910d39b.png
密码不消填写
https://img-blog.csdnimg.cn/direct/e1d3057aac3349229523b6da2bb177a8.png
测试连接乐成
展示
https://img-blog.csdnimg.cn/direct/b72a459c9a874a51959d463e6c318a2a.png
然后点击 myhive，再点击新建数据集-数据库表，选择实操中创建的 8 个表
然后点击右上角的全局更新
然后点击最左边的 我的分析，新建文件夹：myhive，新建分析主题，批量选择 8 个表
进到分析主题页面后，点击下面的组件标签
点击图表类型的 123，再选择表中的字段拖动到图形属性中
看视频吧：第五章-24-案例-可视化展现_哔哩哔哩_bilibili
https://img-blog.csdnimg.cn/direct/e888657baa26472682c0af7e158cfeee.png
其实就是熟练活
总结
其实 hadoop 那里真不难，就是环境的构建麻烦，Hive 这里就得实操了，写 SQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]