初识BigData-hadoop-hdfs-hvie
Stru99le Lv2
  2023-10-23 17:02:55 2023-10-23 17:02   2023-11-06 15:35:42      7k 字  29 分钟  

node1:9870

启动环境(要在 hadoop 用户下）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 1.启动hdfs
start-dfs.sh
# 停止
stop-dfs.sh
# 2.启动yarn
start-yarn.sh
# 3.启动历史服务器
mapred --daemon start historyserver
# 4.启动metastore(在hive目录下)
#前台启动 bin/hive --service metastore
#后台启动 nohup bin/hive --service metastore >> logs/metastore.log 2>&1 &
# 5.启动hive
#直接写SQL bin/hive
#可供其他客户端链接 bin/hive --service hiveserver2
#hiveserver2后台启动 nohup bin/hive --service hiveserver2 >> logs/hiveserver2.log 2>&1 &

单独控制进程的启停。

  1. $HADOOP_HOME/sbin/hadoop-daemon.sh，此脚本可以单独控制所在机器的进程的启停

    用法：hadoop-daemon.sh (start|status|stop)(namenode|secondarynamenode|datanode)

  2. $HADOOP_HOME/bin/hdfs，此程序也可以用以单独控制所在机器的进程的启停

用法：hdfs –daemon (start|status|stop) (namenode|secondarynamenode|datanode)

文件系统的操作命令

Hadoop 提供了两套命令:

hadoop(老版本),用法:hadoop fs

hdfs（新版本),用法:hdfs dfs

  1. 创建文件夹

    hadoop fs -mkdir [-p] …

    hdfs dfs -mkdir [-p] …

    path​​为待创建目录

    -p​​选项与 linux mkdir​​一致会沿着路径创建父目录

  2. 查看指定目录下内容

    • hadoop fs -ls [-h] [-R] […]

    • hdfs dfs -ls [-h] [-R] […]

      path​​指定目录路径

      -h​​人性化显示文件 size

      -R​​递归查看指定目录及其子目录

  3. 上次文件到 HDFS 指定目录下

    • hadoop dfs -put [-f] [-p] …

    • hdfs dfs -put [-f] [-p] …

      -f​​覆盖目标文件(已存在下)

      -p​​保留访问和修改时间，(客户端所在机器）

      dst​​目标文件系统

  4. 查看 HDFS 文件内容

    • hadoop fs -cat …

    • hdfs dfs -cat …

      1
      2
      hadoop fs -cat /itcast/words.txt
      hdfs dfs -cat /itcast/profile

      读取大文件可以使用管道符配合 more

    • hadoop fs -cat | more

    • hdfs dfs -cat | more

  5. 下载 HDFS 文件

    • hadoop fs -get [-f] [-p] …

    • hdfs dfs -get [-f] [-p] …

      下载文件到本地文件系统指定目录，localdst 必须是目录

      -f ​覆盖目标文件

      -p ​保留访问和修改时间，所有权和权限

  6. 拷贝 HDFS 文件

    • hadoop fs -cp [-f] …
    • hdfs dfs -cp [-f] …

  7. 追加数据到 HDFS 文件中

    • hadoop fs -appendTofile …

    • hdfs dfs -appendTofile …

      将所有给定本地文件的内容追加到给定 dst 文件，若 dst 文件不存在，将创建该文件。如果 <localSrc> ​为 -​，则输入为从标准输入中读取。

  8. HDFS 数据移动操作

    • hadoop fs -mv …

    • hdfs dfs -mv …

      可以重命名

  9. HDFS 数据删除操作

    • hadoop fs -rm -r [-skipTrash] URI [URI …]

    • hdfs dfs -rm -r [-skipTrash] URI [URI …]

      -skipTrash ​跳过回收站直接删除

      ‍

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      #
      <property>
      <name>fs.trash.interval</name>
      <value>1440</value>
      </property>

      <property>
      <name>fs.trash.checkpoint.interval</name>
      <value>120</value>
      </property>

      # 无需重启集群，在哪个机器配置的，在哪个机器执行命令就生效。回收站默认位置在：/user/用户名(hadoop)/.Trash

HDFS 储存

  1. HDFS 副本块数量的配置

    可以在上传文件的时候，临时决定被上传文件以多少个副本存储

    hadoop fs -D dfs.replication=2 -put test.txt /tmp/ ​如该命令，就可以在上传 test.txt ​文件时，临时设置其副本数为 2.

    对于已经存在 HDFS 的文件，修改 dfs.replication ​属性不会生效，可通过命令 hadoop fs -setrep [-R] 2 path ​将指定 path 的内容将会被修改为 2 个副本储存。-R ​选项表示对子目录也生效。

  2. fsck 命令检查文件的副本数

    hdfs dfs path [-file[-blocks[-locations]]]​

    -files ​可以列出路径内的文件状态

    -files -blocks ​输出文件块报告(几个块，多少副本)

    -files -blocks -locations ​输出每一个 block 的详情

yarn

node1:8088

  • 启动 historyserver

    mapred --daemon start historyserver​

HIVE

Apache Hive 其 2 大主要组件就算:SQL 解析器以及元数据存储

image​

Hive 架构图

image​

  • 元数据存储

    存储在关系数据库如 mysql/derby 中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
    Hive 提供了 Metastore 服务进程提供元数据管理功能

  • Driver 驱动程序，包括语法解析器、计划编译器、优化器、执行器

    完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有执行引擎调用执行。

    这部分内容不是具体的服务进程，而是封装在 Hive 所依赖的 Jar 文件即 Java 代码中。

  • 用户接口

    包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为 shell 命令行；Hive 中的 Thrift 服务器允许外部客户端通过网络与 Hive 进行交互，类似于 JDBC 或 ODBC 协议。WebGUI 是通过浏览器访问 Hive。

    Hive 提供了 Hive Shell、 ThriftServer 等服务进程向用户提供操作接口

  • 启动 hive

    • 启动元数据管理服务(必须启动，在 hive 目录下启动)

      • 前台启动 bin/hive --service metastore​​
      • 后台启动 nohup bin/hive --service metastore >> logs/metastore.log 2>&1 &​​

    • 启动客户端

      Hive Shell方式(可以直接写SQL) bin/hive​​

      image​

    • ‍

Hive 使用语法

数据库操作

  • 创建数据库

    create database if not exists myhive;​ 创建名为 myhive 的数据库

  • 查看数据库详细信息

    desc database myhive

    image​

  • 创建数据库并指定 hdfs 存储位置

    create database myhive2 location 'user/hive/myhive2';​

  • 删除一个空数据库，如果数据库下有数据表，那么就会报错

    drop database myhive;​​

  • 强制删除数据库

    drop database myhive2 cascade;​​

  • 内部表（CREATE TABLE table_name ……）
    未被 external 关键字修饰的即是内部表， 即普通表。 内部表又称管理表,内部表数据存储的位置由 hive.metastore.warehouse.dir 参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。

  • 外部表（CREATE EXTERNAL TABLE table_name ……LOCATION……）
    被 external 关键字修饰的即是外部表， 即关联表。
    外部表是指表数据可以在任何位置，通过 LOCATION 关键字指定。 数据存储的不同也代表了这个表在理念是并不是 Hive 内部管理的，而是可以随意临时链接到外部数据上的。
    所以，在删除外部表的时候， 仅仅是删除元数据（表的信息），不会删除数据本身。

    1. 先创建外部表，然后移动数据刀 LOCATION 目录

      • 检查 hadoop fs -ls /tmp​，确认不存在 /tmp/test_ext1 ​目录
      • 创建外部表：create external table test_ext1(id int,name string) row format delimited fields terminated by '\t' location '/tmp/test_ext1';​
      • select * from test_ext1 ​空结果，无数据
      • 上传数据:hadoop fs -put test_external.txt /tmp/test_ext1/​
      • select * from test_ext1​，即可看到数据

    2. 先存在数据，后创建外部表

      • hadoop fs -mkdir /tmp/test_ext2​
      • hadoop fs -put test_external.txt /tmp/test_ext2/​
      • create external table test_ext2(id int name string) row format delimited fields terminated by '\t' location '/tmp/test_ext2';​
      • select * from test_ext2;​

    数据在 HDFS 中以明文形式存在

    image​

  • 自定义分隔符

    create table myhive.stu2(id int,name string)row format delimited fields terminated by '\t';​

  • 内外部表转换

    • 内转外

      alter table stu set tblproperties('EXTERNAL'='TRUE');​

    • 外转内

      alter table stu set tblproperties('EXTERNAL'='FALSE');​

  • hive 表数据导出 -insert overwrite 方式

    • 语法 insert overwrite [local] directory 'path' select_statement1 FROM from_statement;​

    • 将查询的结果导出到本地-使用默认分隔符

      insert overwrite local diretory '/home/hadoop/export1' select * from myhive.test_load;​

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

      insert overwrite local directory '/home/hadoop/export2' row format delimited fields terminated by '\t' select * from myhive.test_load;​

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

      insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from myhive.test_load;​

分区表

可以把大的文件切割划分成一个个的小的文件，每次操作一个小的文件就会很容易

image​​image
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 创建分区语法
create table tablename(...) partitioned by (分区列 列类型,...) row format delimited fields terminated by '';
# 创建一个表带多个分区
create table score2(s_id string,c_id string,s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';
# 加载数据到分区表中
load data local inpath '/export/server/hivedatas/score.txt' into table score partition (month='202004');
# 加载数据到一个多分区的表中
load data local inpath '/export/server/hivedatas/score.txt' into table score2 partition (year='2020',month='04',day='01');
# 查看分区
show partitions score;
# 添加一个分区
alter table score add partition (month='202004');
# 同时添加多个分区 (添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹)
alter table score add partition (month='202004') partition (month = '202005');
# 删除分区
alter table score drop partition (month='202006');
<h4 id=分桶表><a href=#分桶表 class=headerlink title=分桶表></a>分桶表</h4><p>分桶和分区一样也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式，但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。</p> <ul> <li><p>分桶表创建</p> <p>开启分桶的自动优化 (自动匹配 reduce task 数量和桶数量一致)</p> 
1
2
3
set hive.enforce.bucketing=true;
# 创建分桶表
create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';
</li> <li><p>分桶表数据加载</p> <p>桶表的数据加载，只能通过 <code>insert select</code> ​所以比较好的方法是:</p> <ol> <li>创建一个临时表，通过 <code>load data</code> ​加载数据进入表</li> <li>然后通过 <code>insert select</code> ​从临时表向桶表插入数据</li> </ol> 
1
2
3
4
5
6
# 创建普通表
create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
# 普通表中加载数据
load data local inpath '/export/server/hivedatas/course.txt' into table course_common;
# 通过insert overwrite 给桶表加载数据
insert overwrite table course select * from course_common cluster by(c_id);
</li> </ul> <p>在创建分桶表时注意<em>​ cluster</em> 的写法:{% asset_img image-20231016214724-7zaokpe.png image">​

  • 为什么不可以用 load data ​必须用 insert select ​插入数据

    问题在于:如何将数据划分，划分的规则是什么?

    数据的划分是基于分桶列的值进行 hash 取模来决定的，由于 load data ​不会触发 MapReduce ​也就是没有计算过程，无法执行 Hash 算法，只是简单的移动数据而已，所以无法用于分桶表数据插入。

  • 性能提升

    分区表:在指定分区列的前提下，减少被操作的数据量，从而提示性能。

    分桶表:基于分桶列的特定操作，如:过滤、JOIN、分组、均可带来性能提升

修改表操作

  • 表重命名

    1
    alter table old_table_name rename to new_table_name;

  • 修改表属性值

    1
    2
    3
    4
    5
    alter table table_name SET TBLPROPERTIES table_properties;
    # table_properties:(property_name = property_value,property_name = property_value,...)
    # ALTER TABLE table_name SET TBLPROPERTIES("EXTERNAL"="TRUE");  修改内外部表属性
    # ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment); 修改表注释

  • 添加分区

    新分区添加了但是空的没数据，需要手动添加或上传数据文件

    1
    alter table tablename add partition (month='202002');

  • 修改分区(修改元数据记录，HDFS 的实体文件夹不会改名但是在元数据中是改名了的,内部表会改，外部表不会改)

    1
    alter table tablename partition (month = '202003') rename to partition (month='202202');

  • 删除分区(对于内部表而言删除元数据，数据本身还在，外部表则不在）

    1
    alter table tablename drop partiton (month='202104');

  • 添加列

    1
    alter table table_name add columns (v1 int,v2 string);

  • 修改列名

    1
    alter table test_change change v1 v1newname int;

  • 删除表

    1
    drop table tablename;

  • 清空表

    1
    truncate table tablename;

    ps:只可以清空内部表

数据查询

SELECT 基本查询

  • 查询所有

    1
    select * from orderdb.orders;

  • 查询单列

    1
    select orderid,totalmoney,username,useraddres,paytime from orderdb.orders;

  • 查询数据量

    1
    select count(*) from orderdb.orders;

  • 过滤广东省订单

    1
    select * from orderdb.orders where useraddress like '%广东%';

  • 找出广东省单笔营业额最大的订单

    1
    2
    select * from orderdb.orders where useraddress like '%广东%' order by totalmoney desc limit 1;
    # desc 降序，limit 1 最大的一个
分组、聚合

  • 统计未支付、已支付各自的人数

    1
    select ispay,count(*) as cnt from orderdb.orders group by ispay;

  • 在已付款订单中，统计每个用户最高的一笔消费金额

    1
    select userid,MAX(totalmoney) as max_money from orderdb.orders where ispay = 1 group by userid;

  • 统计每个用户的平均订单消费额

    1
    2
    select userid,avg(totalmoney) as avg_money from orderdb.orders group by userid having avg_money > 10000;
    # having 表示筛选，与where不同;where在分组前进行筛选的,而having是在分组后进行筛选
JOIN

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

    1
    2
    # 内关联，目的是获取来自 orders 表的订单编号 (orderid)、用户ID (userid)，以及与之相关联的用户表中的用户名 (username)。
    select o.orderid,o.userid,u.username,o.totalmoney,o.useraddress,o.paytime from orderdb.orders o join orderdb.users u on o.userid = u.userid;

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

    左连接 (​*LEFT JOIN*​) 会返回左边表（​*orders*​）中所有的记录，同时匹配右边表（​*users*​）中相应条件的记录。如果右边表中没有匹配的记录，那么将会返回 ​​*NULL*​​ 值。

    1
    select o.orderid,o.userid,u.username,o.totalmoney,o.useraddres,o.paytime from orderdb.orders o left join orderdb.users u on o.userid = u.userid;
RLIKE

relike 关键字，可以供用户使用正则和数据进行匹配

1
2
3
4
5
6
7
8
# 查找广东省的数据
select * from orderdb.orders where useraddress rlike '.*广东.*';
# 查找用户地址是：xx省 xx市 xx区的数据
select * from orderdb.orders where useraddress rlike '..省 ..市 ..区';
# 查找用户姓为张、王、邓
select * from orderdb.orders where username rlike '[张王邓]\\S+';
# 查找手机号符合:188****0*** 规则
select * from orderdb.orders where userphone rlike '188\\S{4}0\\S{3}';
UNION 联合

用于将多个 select ​语句的结果组合成单个结果集。每个 select ​语句返回的列的数量和名称必须相同。否则将引发架构错误。

基础语法:

select …

union [all]

select …

1
2
3
4
# 联合两个查询结果集
select * from course where t_id = '周杰伦';
	union
select * from course where t_id = '王力宏';

union 默认有去重功能:

1
2
3
4
5
6
7
8
# 直接联合两个同样的查询结果
select * from course
union
selcet * from course;
# 不需要去重效果
selcet * from course
	union all
select * from course;

  • 其他写法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # union写在from中
    selcet t_id,count(*) from
    (
    	select t_id from myhive.course where t_id ='周杰伦'
    		union all
    	select t_id from myhive.course where t_id = '王力宏'
    ) as u group by t_id;
    # 用于insert selcet中
    create table myhive.course2 like myhive.course;
    insert overwrite table myhive.course2
    	selcet * from myhive.course
    		union all
    	selcet * from myhive.course;
抽样操作

在大体量的数据环境下,对于表的一个简单 select * 都会非常慢，哪怕看很少的数据都会走 MapReduce 流程，Hive 提供的快速抽样的语法，可以快速从大表中随机抽取一些数据供用户查看

TABLESAMPLE 函数

  • 语法 1，基于随机分桶抽样:

    select ... from tb1 tablesample(bucket x out of y on (colname | rand())​

    • y 表示将表数据随机划分成 y 粉(y 个桶）
    • x 表示从 y 里面随机抽取 x 份数据作为取样
    • colname 表示随机的依据基于某个列的值
    • rand()表示随机的依据基于整行

    示例:

    注意:

    1.使用 colname 作为随机依据，则其它条件不变下，每次抽样结果一致；

    2.使用 rand()作为随机依据，每次抽样结果都不同

    1
    2
    select username,orderid,totalmoney from orderdb.orders tableample(bucket 1out of 10 on username);
    select * from orderdb.orders tableample(bucket 1 out of 10 on rand());

  • 语法 2，基于数据块抽样

    select ... from tb1 tableample(num rows | num percent | num(K|M|G));​

    • num rows 表示抽样 num 条数据
    • num perfect 表示抽样 num 百分百比例的数据
    • num(K|M|G)表示抽取 num 大小的数据，单位可以是 K、M、G 表示 KB、MB、GB

    注意:

    使用这种语法抽样，条件不变的话，每一次抽样的结果都一致；即无法做到随机，只是按照数据顺序从前向后取。

Virtual Columns 虚拟列

虚拟列是 Hive 内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。Hive 目前可用 3 个虚拟列:

  • INPUT_FILE_NAME,显示数据行所在的具体文件

  • BLOCK_OFFSET_INSIDE_FILE,显示数据行所在文件的偏移量

  • ROW_OFFSET_INSIDE_BLOCK,显示数据所在 HDFS 块的偏移量

    • 此虚拟列需要设置:SET hive.exec.rowoffset=true 才可使用

示例:

select orderId,userName,INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE from orders;​

image​

Hive 函数

函数分类

分为两大类:内置函数(Built-in Functions)、用户自定义函数 UDF（User-Defined Functions):

image​

官方文档

  • 查看函数列表

    使用 show function 查看当下可用的所有函数

    通过 describe function extended funcname 来查看函数的使用方式

    image​

  • Mathmatical Functions 部分数学函数

    –取整函数:round 返回 double 类型的整数值部分（四舍五入）

    select round(3.1415926);

    –指定精度取证函数:round(double a,int d)返回指定精度 d 的 double 类型

    select round(3.1415926,4);

    –取随机数:rand()每次执行都不一样，返回一个 0-1 范围内的随机数

    select rand();

    –指定种子取随机函数:rand(int seed)得到一个稳定的随机序列

    select rand(3);

    –求数字的绝对值

    select abs(-3);

    –得到 pi 值(小数点后 15 位精度)

    select pi();

  • Collection Functions 集合函数

    Return Type Name(Signature) Description
    int size(Map<K.V>) 返回 map 类型的元素个数
    int size(Array) 返回 array 类型的元素个数
    array map_keys(Map<K.V>) 返回 map 内的全部 key（得到的是 array）
    array map_values(Map<K.V>) 返回 map 内的全部 value（得到的是 array）
    boolean array_contains(Array, value) 如果 array 包含指定 value，返回 True
    array sort_array(Array) 根据数组元素的自然顺序按升序对输入数组进行排序并返回它

  • Type Conversion Functions 类型转换函数

    Return Type Name(Signature) Description
    binary binary(string binary)
    Expected “=” to follow “type” cast(expr as ) 将表达式 expr 的结果转换为给定类型。例如，cast(‘1’ as BIGINT） 会将字符串 ‘1’ 转换为整数表示。如果转换不成功，则返回 null。对于 cast(expr as boolean)，对于非空字符串将会返回 True

  • Date Functions 日期函数 - 部分

    Return Type Name(Signature) Description
    timestamp current_timestamp() 返回当前时间戳。在同一个查询中对 current _ time 戳的所有调用都返回相同的值。
    date current_date 返回当前日期。在同一个查询中对 current_date 戳的所有调用都返回相同的值。
    2.1.0 版本之前返回 string 现在版本返回 date to_date(string timestamp) 时间戳转日期
    int year(string date)quarter(date/timestamp/string)month(string date)day(string date)dayofmonth(date)hour(string date)minute(string date)second(string date)weekofyear(string date) 得到给定时间的：年得到给定时间的：季度得到给定时间的：月得到给定时间的：日得到给定时间的：当前月份第几天得到给定时间的：小时得到给定时间的：分钟得到给定时间的：秒得到给定时间的：本年第几周
    int datediff(string enddate, string startdate) 返回 enddate 到 startdate 之间的天数
    2.1.0 版本之前返回 string 现在版本返回 date date_add(date/timestamp/string startdate, tinyint/smallint/int days)date_sub(date/timestamp/string startdate, tinyint/smallint/int days) 日期相加: date_add(‘2008-12-31’, 1) = ‘2009-01-01’.日期相减: date_sub(‘2008-12-31’, 1) = ‘2008-12-30’.

  • Condition Functions 条件函数

    Return Type Name(Signature) Description
    T if(boolean testCondition, T valueTrue, T valueFalseOrNull) 如果 testCondition 为 true，则返回 valueTrue，否则返回 valueFalseOrNull。
    boolean isnull( a ) 如果 a 为 NULL，则返回 true，否则返回 false。
    boolean isnotnull ( a ) 如果 a 不为 NULL，则返回 true，否则返回 false。
    T nvl(T value, T default_value) 如果 value 为 null，则返回 default_value，否则 value。
    T COALESCE(T v1, T v2, …) 返回第一个不是 NULL 的 v，如果所有 v 都是 NULL，则返回 NULL。
    T CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 当 a = b 时，返回 c; [当 a = d 时，返回 e]* ;否则返回 f。
    T CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END When a = true, returns b; when c = true, returns d; else returns e.a 可以是表达式，如 1=1
    T nullif( a, b ) 如果 a=b，则返回 NULL;否则返回 a 。等价：CASE WHEN a = b then NULL else a
    void assert_true(boolean condition) 如果 boolean_condition 结果不为 True，则引发异常报错比如：select assert_true (2<1).

  • String Functions 字符串函数

    Return Type Name(Signature) Description
    string concat(string binary A, string
    string concat_ws(string SEP, string A, string B…) 同 concat，但是可以自己定义字符串之间的分隔符（SEP）
    int length(string A) 字符串长度
    string lower(string A)upper(string a) 全部转小写全部转大写
    string trim(string A) 返回从 A 的两端裁剪空格得到的字符串。例如，trim(‘ foobar ’)的结果是‘ foobar’
    array split(string str, string pat) 按照 pat 分隔字符串，pat 是正则表达式

  • Data Masking Functions 数据脱敏函数 -部分

    Return Type Name(Signature) Description
    string mask_hash(string|char|varchar str) 对字符串进行 hash 加密非字符串加密会得到 NULL

  • Misc. Functions 其他函数 -部分

    Return Type Name(Signature) Description
    int hash(a1[, a2…])) 返回参数的 hash 数字
    string current_user() 返回当前登录用户
    string current_database() 返回当前选择的数据库
    string version() 返回当前 hive 版本
    string md5(string/binary) 返回给定参数的 md5 值

案例

基于 hadoop 和 hive 实现聊天数据系统分析，构建聊天数据分析表

需求

  • 统计今日总消息量
  • 统计今日每小时消息量、发送和接收用户数
  • 统计今日各地区发送消息数据量
  • 统计今日发送消息和接收消息的用户数
  • 统计今日发送消息最多的 top10 用户
  • 统计今日接收消息最多的 Top10 用户
  • 统计发送人的手机型号分布情况
  • 统计发送人的设备操作系统分布情况

数据内容

  • 大小:30w 条数据

  • 列分割符：hive 默认分隔符’\001’

  • 数据字典及样例数据

    消息时间 发件人昵称 发件人账号 发件人性别 发件人 IP 发件人系统 发件人手机型号 发件人网络制式 发件人 GPS 收件人昵称 收件人 IP 收件人账号 收件人系统 收件人手机型号 收件人网络制式 收件人 GPS 收件人性别 消息类型 双方距离 消息
    2021-11-0115:11:33 古博易 14747877194 男 48.147.134.255 Android 8.0 小米 Redmi K30 4G 94.704577,36.247553 莱优 97.61.25.52 17832829395 IOS 10.0 Apple iPhone 10 4G 84.034145,41.423804 女 TEXT 77.82KM 天涯海角惆怅渡，牛郎织女隔天河。佛祖座前长顿首，只求共度一百年。

建库建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 创建数据库
create database db_msg;
# 切换数据库
use db_msg;
# 列举数据库
show databases;
# 如果表存在就删除
drop table if exists db_msg.tb_msg_source;
# 建表
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 系统，再通过 load 加载数据到表

1
2
3
load data local inpath '/home/hadoop/chat_data-30w.csv' overwrite into table tb_msg_source;
# 验证结果
select msg_time,sender_name,sender_ip,sender_phonetype,receiver_name,receiver_network from tb_msg_source limit 10;
image

数据清洗

问题

    1. 当数据中有一些数据的字段为空，不是合法数据
1
select msg_time,sender_name,sender_gps from db_msg.tb_msg_source where length(sender_gps) =0 limit 10;
image
  • 2.需求中，需要统计每天，每小时的消息量，但是数据中没有天和小时字段，只有整体时间字段，不好处理
1
select msg_time from db_msg.tb_msg_source limit 10;

‍

  • 3.需求中，需要对经度和维度构建地区的可视化地图，但是数据中 GPS 经纬度为一个字段，不好处理
1
select sender_gps from db_msg.tb_msg_source limit 10;

解决方法

需求：

  • 需求 1 对字段为空的不合法数据进行过滤

    where ​过滤 将为空的数据过滤掉

    where length(sender_gps)>0​

  • 需求 2 通过时间字段构建聊天和小时字段

    date hour ​函数 分别取时间和小时

    date(msg_time),hour(msg_time)​

  • 需求 3 从 GPS 的经纬度中提取经度和纬度

    split ​函数 分割成数据再分别取前一二个

    split(sender_gps,',')[0];​

  • 需求 4 将 ETL 以后的结果保存到一张新的 Hive 表中

实现:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 创建一个新表
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 "纬度"
);
# 使用insert 将改好的需求插入到新表中
insert overwrite table db_msg.tb_msg_tb1
select
    *,date(msg_time) as msg_day,hour(msg_time) as msg_hour,
    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;

完成清洗的新表(部分):

image

  • ETL 概念

    • E,Extract,抽取
    • T,Transform,转换
    • L,Load,加载

    从 A 抽取数据(E)，进行数据转换过滤(T),将结果加载到 B(L),就是 ETL

需求指标统计

  • 指标 1：统计今日消息总量

    1
    create table db_msg.tb_rs_total_msg_cnt comment '每日消息总量' as select msg_day,count(*) as total_msg_cnt from db_msg.tb_msg_etl group by msg_day;

    ‍

    image​

    ‍

    ‍

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

    1
    2
    3
    4
    5
    create table db_msg.tb_rs_hour_msg_cnt comment '每小时消息量趋势' as 
        select msg_hour,count(*) as total_msg_cnt,count(distinct sender_account) as sender_user_cnt,count(distinct receiver_account) as receiver_user_cnt
    from db_msg.tb_msg_etl group by msg_hour;
    # as：这表示接下来的部分是一个 SQL 查询的结果将会被插入到新表中,count(*)：统计所有行的数量，即总消息数。
    # group by msg_hour：这是一个分组操作，它将查询结果按照 msg_hour 字段进行分组，意味着所有具有相同 msg_hour 值的行会被聚合在一起，从而得到每个小时的统计数据。
    image​

    ‍

  • 需求 3：统计今日各地区发送消息总量

    1
    2
    3
    create table db_msg.tb_rs_loc_cnt comment '每日各地区发送消息总量' as
        select msg_day,sender_lng,sender_lat,count(*) as total_msg_cnt
    from db_msg.tb_msg_etl group by msg_day, sender_lng, sender_lat;
    image​

  • 需求 4：统计今日发送和接收用户人数

    1
    2
    3
    4
    create table db_msg.tb_rs_user_cnt comment '今日发送和接收用户人数' as
        select msg_day,count(distinct sender_account) as sender_user_cnt,count(distinct receiver_account) as receiver_user_cnt
               from db_msg.tb_msg_etl group by msg_day;
    # 这里注意要加distinct关键字，进行去重，每个人每天能发送多条消息
    image​

  • 需求 5：统计发送消息条数最多的 Top10 用户

    1
    2
    3
    4
    create table db_msg.tb_rs_s_user_top10 comment '发送消息条数最多的top10用户' as 
        select sender_name,count(*) as sender_msg_cnt
    from db_msg.tb_msg_etl group by sender_name order by sender_msg_cnt desc limit 10;
    # desc 表示降序排序，意味着数值越大的将会排在前面
    image​

  • 需求 6：统计接收消息最多的 top10 用户

    1
    2
    3
    create table db_msg.tb_rs_r_user_top10 comment '接收消息条数最多的top10用户' as
        select receiver_name,count(*) as receiver_msg_cnt
    from db_msg.tb_msg_etl group by receiver_name order by receiver_msg_cnt desc limit 10;
    image​

  • 需求 7：统计发送人的手机型号分布

    1
    2
    create table db_msg.tb_rs_sender_phone comment '统计发送人的手机型号分布' as
        select sender_phonetype,count(*) as cnt from db_msg.tb_msg_etl group by sender_phonetype;
    image​

  • 需求 8：统计发送人的手机 os 分布

    1
    2
    create table db_msg.tb_rs_sender_phone comment '统计发送人的手机型号分布' as
        select sender_phonetype,count(*) as cnt from db_msg.tb_msg_etl group by sender_phonetype;
    image​

可视化平台

使用finebi平台，注册好后使用官方提供的激活码，激活使用即可

  • FineBI与Hive集成文档：https://help.fanruan.com/finebi/doc-view-301.html

  • 驱动配置

    • 如果使用FineBI连接Hive，读取Hive的数据表，需要在FineBI中添加Hive的驱动jar包
    • 将Hive的驱动jar包放入FineBI的lib(FineBI6.0\webapps\webroot\WEB-INF\lib)目录下

  • 插件安装

    • 我们自己放的Hive驱动包会与FineBI自带的驱动包产生冲突，导致FineBI无法识别我们自己的驱动包
    • 安装FineBI官方提供的驱动包隔离插件(fr-plugin-hive-driver-loader-3.0.zip)
    • 安装好后重启finalbi即可

  • 测试连接

    image

基于FineBI完成指标的可视化展现

数据准备

在BI界面，点击公共数据，新建个Hive的文件夹然后添加数据库表，选择db_msg的八项数据，点击确定；然后点击每个表，更新数据。

(过程中可能会出现中文乱码情况，解决方法可见:https://www.cnblogs.com/qingyunzong/p/8724155.html 不过值得注意的是:之前创建的表中的中文依然会是问号)

  1. 新建分析，点击我的分析-新建文件夹(hive数据分析)-新建分析主题

    image

  2. 在主题里面点击图标类型的123数字类型->将sender_user_cnt拖到文本框->修改文本->取消固定大小，并将sender_user_cnt修改为发送消息人数，并修改组件名，拖入仪表盘；

    image

同理添加其它组件

  1. 启动环境(要在 hadoop 用户下）
  2. 文件系统的操作命令
  3. HDFS 储存
  4. yarn
  5. HIVE
    1. Hive 架构图
  6. Hive 使用语法
    1. 数据库操作
      1. 分区表
      2. 修改表操作
      3. 数据查询
        1. SELECT 基本查询
        2. 分组、聚合
        3. JOIN
        4. RLIKE
        5. UNION 联合
        6. 抽样操作
          1. TABLESAMPLE 函数
        7. Virtual Columns 虚拟列
    2. Hive 函数
      1. 函数分类
  7. 案例
    1. 需求
    2. 数据内容
    3. 建库建表
    4. 加载数据
    5. 数据清洗
      1. 问题
      2. 解决方法
    6. 需求指标统计
  8. 可视化平台
    1. 基于FineBI完成指标的可视化展现
      1. 数据准备