create database if not exists myhive;
use myhive;
desc database myhive;
数据库本质上就是在HDFS之上的文件夹。
默认数据库的存放路径是HDFS的:/user/hive/warehouse内
使用location关键字,可以指定数据库在HDFS的存储路径。
create database myhive2 location '/myhive2';
删除一个空数据库,如果数据库下面有数据表,那么就会报错
drop database myhive;
强制删除数据库,包含数据库下面的表一起删除
drop database myhive2 cascade;
Hive中可以创建的表有好几种类型, 分别是:
内部表(CREATE TABLE table_name …)
未被external关键字修饰的即是内部表, 即普通表。 内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定(默认:/user/hive/warehouse),删除内部表会直接删除元数据(metadata)及存储数据,因此内部表不适合和其他工具共享数据。
内部表的创建语法就是标准的:CREATE TABLE table_name…
create database if not exists myhive;
use myhive;
create table if not exists stu(id int,name string);
insert into stu values (1,"zhangsan"), (2, "wangwu");
select * from stu;
hdfs dfs -ls /user/hive/warehouse/myhive.db/stu
hdfs dfs -cat /user/hive/warehouse/myhive.db/stu/*
可以看到,数据在HDFS上也是以明文文件存在的。
奇怪的是, 列ID和列NAME,好像没有分隔符,而是挤在一起的。
这是因为,默认的数据分隔符是:”\001”是一种特殊字符,是ASCII值,键盘是打不出来
在某些文本编辑器中是显示为SOH的。
在创建表的时候可以自己决定:
create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t';
row format delimited fields terminated by ‘\t’:表示以\t分隔
除了标准的CREATE TABLE table_name的形式创建内部表外
我们还可以通过:
create table stu3 as select * from stu2;
create table stu4 like stu2;
DESC FORMATTED stu2;
我们是内部表删除后,数据本身也不会保留,让我们试一试吧。
DROP TABLE table_name,删除表
drop table stu2;
可以看到,stu2文件夹已经不存在了,数据被删除了。
外部表(CREATE EXTERNAL TABLE table_name …LOCATION…)
被external关键字修饰的即是外部表, 即关联表。
外部表是指表数据可以在任何位置,通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的,而是可以随意临时链接到外部数据上的。
所以,在删除外部表的时候, 仅仅是删除元数据(表的信息),不会删除数据本身。
内部表和外部表
外部表,创建表被EXTERNAL关键字修饰,从概念是被认为并非Hive拥有的表,只是临时关联数据去使用。
创建外部表也很简单,基于外部表的特性,可以总结出: 外部表 和 数据 是相互独立的, 即:
先创建外部表,然后移动数据
2. 演示先创建外部表,然后移动数据到LOCATION目录
首先检查:hadoop fs -ls /tmp,确认不存在/tmp/test_ext1目录
hadoop fs -ls /tmp
创建外部表:create external table test_ext1(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext1’;
create external table test_ext1(id int, name string) row format delimited fields terminated by '\t' location '/tmp/test_ext1';
可以看到,目录/tmp/test_ext1被创建
select * from test_ext1,空结果,无数据
上传数据: hadoop fs -put test_external.txt /tmp/test_ext1/
select * from test_ext1,即可看到数据结果
hadoop fs -put test_external.txt /tmp/test_ext1/
select * from test_ext1
演示先存在数据,后创建外部表
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;
drop table test_ext1;
drop table test_ext2;
可以发现,在Hive中通过show table,表不存在了
但是在HDFS中,数据文件依旧保留
ive可以很简单的通过SQL语句转换内外部表。
查看表类型:desc formatted stu;
desc formatted stu;
内部表
alter table stu set tblproperties('EXTERNAL'='TRUE');
alter table stu set tblproperties('EXTERNAL'='FALSE');
要注意:(‘EXTERNAL’=‘FALSE’) 或 (‘EXTERNAL’=‘TRUE’)为固定写法,区分大小写!!!
我们使用 LOAD 语法,从外部将数据加载到Hive内,语法如下:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
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';
load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load;
load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load;
加上overwrite 关键字。相同的数据不会追加。
除了load加载外部数据外,我们也可以通过SQL语句,从其它表中加载数据。
语法
将SELECT查询语句的结果插入到其它表中,被SELECT查询的表可以是内部表或外部表。
insert into myhive.test_load2 select * from myhive.test_load;
数据在本地
推荐 load data local加载。
数据在HDFS
如果不保留原始文件: 推荐使用LOAD方式直接加载。
如果保留原始文件: 推荐使用外部表先关联数据,然后通过INSERT SELECT 外部表的形式加载数据。
数据已经在表中
只可以INSERT SELECT。
将hive表中的数据导出到其他任意目录,例如linux本地磁盘,例如hdfs,例如mysql等等
语法:insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement;
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;
insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from test_load;
基本语法:(hive -f/-e 执行语句或者脚本 > file)
bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt
bin/hive -f export.sql > /home/hadoop/export4/export4.txt
什么是分区表?
在大数据中,最常用的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这样每次操作一个小的文件就会很容易了
同样的道理,在hive当中也是支持这种思想的,就是我们可以把大的数据,按照每天,或者每小时进行切分成一个个的小的文件,这样去操作小的文件就会容易得多了。
同时Hive也支持多个字段作为分区,多分区带有层级关系,如图
基本语法:
create table tablename(...) partitioned by (分区列 列类型, ......)
row format delimited fields terminated by '';
创建一个分区表
-- 创建一个分区表,按照月分区
create table myhive.score(
name string,
course string,
score int
) partitioned by (month string)
row format delimited fields terminated by '\t';
加载数据到分区
load data local inpath '/home/hadoop/score.txt' into table myhive.score partition(month='202309');
9月分区
10月分区
hdfs dfs -ls /user/hive/warehouse/myhive.db/score
hdfs dfs -cat /user/hive/warehouse/myhive.db/score/month=202309/score.txt
根据年月日分区
create table myhive.score2(
name string,
course string,
score int
) partitioned by (year string,month string,day string)
row format delimited fields terminated by '\t';
-- 加载数据到分区表中
load data local inpath '/home/hadoop/score.txt'
into table myhive.score2 partition(year='2023',month='09',day='01');
load data local inpath '/home/hadoop/score.txt'
into table myhive.score2 partition(year='2023',month='09',day='02');
select * from myhive.score2;
分桶和分区一样,也是一种通过改变表的存储模式,从而完成对表优化的一种调优方式。
但和分区不同,分区是将表拆分到不同的子文件夹中进行存储,而分桶是将表拆分到固定数量的不同文件中进行存储。
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';
桶表的数据加载,由于桶表的数据加载通过load data无法执行,只能通过insert select.
所以,比较好的方式是
-- 创建一个普通表
create table course_temp(
c_id string,
c_name string,
t_id string
) row format delimited fields terminated by '\t';
-- 向普通表加载数据
load data local inpath '/home/hadoop/course.txt' into table course_temp;
select * from course_temp;
-- 临时表向course表加载数据
insert overwrite table course select * from course_temp cluster by(c_id);
如果没有分桶设置,插入(加载)数据只是简单的将数据放入到:
一旦有了分桶设置,比如分桶数量为3,那么,表内文件或分区内数据文件的数量就限定为3
当数据插入的时候,需要一分为3,进入三个桶文件内。
问题就在于:如何将数据分成三份,划分的规则是什么?
Hash算法是一种数据加密算法,其主要特征:
同样的值被Hash加密后的结果是一致的
比如字符串“hadoop”被Hash后的结果是12345(仅作为示意),那么无论计算多少次,字符串“hadoop”的结果都会是12345。
比如字符串“bigdata”被Hash后的结果是56789(仅作为示意),那么无论计算多少次,字符串“bigdata”的结果都会是56789。
基于如上特征,在辅以有3个分桶文件的基础上,将Hash的结果基于3取模(除以3 取余数)
那么,可以得到如下结果:
所以,必须使用insert select的语法,因为会触发MapReduce,进行hash取模计算。
基于Hash取模,数据中的每一个分桶列的值,都被hash取模得到0、1、2其中一个数
基于结果,存入对应序号的桶文件中。
如果说分区表的性能提升是:在指定分区列的前提下,减少被操作的数据量,从而提升性能。
分桶表的性能提升就是:基于分桶列的特定操作,如:过滤、JOIN、分组,均可带来性能提升。
alter table old_table_name rename to new_table_name;
如:alter table score4 rename to score5;
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); 修改表注释
ALTER TABLE tablename ADD PARTITION (month='201101');
新分区是空的没数据,需要手动添加或上传数据文件
ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105');
ALTER TABLE tablename DROP PARTITION (month='201105');
ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string);
ALTER TABLE test_change CHANGE v1 v1new INT;
DROP TABLE tablename;
TRUNCATE TABLE tablename;
只可以清空内部表
Hive支持的数据类型很多,除了基本的:int、string、varchar、timestamp等
还有一些复杂的数据类型:
如下数据文件,有2个列,locations列包含多个城市:
说明:name与locations之间制表符分隔,locations中元素之间逗号分隔
可以使用array数组类型,存储locations的数据
建表语句:
create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';
导入数据
load data local inpath '/home/hadoop/data_for_array_type.txt' overwrite into table myhive.test_array;
常用array类型查询:
-- 查询所有数据
select * from myhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from myhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from myhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from myhive.test_array where array_contains(work_locations,'tianjin');
map类型其实就是简单的指代:Key-Value型数据格式。 有如下数据文件,其中members字段是key-value型数据
字段与字段分隔符: “,”;需要map字段之间的分隔符:“#”;map内部k-v分隔符:“:”
create table myhive.test_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';
MAP KEYS TERMINATED BY ‘:’ 表示key-value之间用:分隔
load data local inpath '/home/hadoop/data_for_map_type.txt' overwrite into table myhive.test_map;
# 查询全部
select * from myhive.test_map;
# 查询father、mother这两个map的key
select id, name, members['father'] father, members['mother'] mother, age from myhive.test_map;
# 查询全部map的key,使用map_keys函数,结果是array类型
select id, name, map_keys(members) as relation from myhive.test_map;
# 查询全部map的value,使用map_values函数,结果是array类型
select id, name, map_values(members) as relation from myhive.test_map;
# 查询map类型的KV对数量
select id,name,size(members) num from myhive.test_map;
# 查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), 'brother');
struct类型是一个复合类型,可以在一个列中存入多个子列,每个子列允许设置类型和名称
有如下数据文件,说明:字段之间#分割,struct之间冒号分割
create table myhive.test_struct(
id string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';
load data local inpath '/home/hadoop/data_for_struct_type.txt' into table myhive.test_struct;
select * from myhive.test_struct;
-- 直接使用列名.子列名 即可从struct中取出子列查询
select id, info.name from myhive.test_struct;
查询语句的基本语法
SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BYcol_list]
[HAVING where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
整体上和普通SQL差不多,部分有区别,如:CLUSTER BY、DISTRIBUTE BY、SORT BY等
CREATE DATABASE it;
USE it;
CREATE TABLE it.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 it.orders;
CREATE TABLE it.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 it.users;
SELECT * FROM itheima.orders;
SELECT orderid, totalmoney, username, useraddress, paytime FROM itheima.orders;
SELECT COUNT(*) FROM itheima.orders;
SELECT * FROM itheima.orders WHERE useraddress LIKE '%广东%';
SELECT * FROM itheima.orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;
SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;
SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay = 1 GROUP BY userid;
SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;
SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money > 10000;
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;
正则表达式是一种规则集合,通过特定的规则字符描述,来判断字符串是否符合规则。
RLIKE
Hive中提供RLIKE关键字,可以供用户使用正则和数据进行匹配。
我们以上面中使用的订单表为例,来简单使用一下RLIKE正则匹配。
SELECT * FROM itheima.orders WHERE useraddress RLIKE '.*广东.*';
SELECT * FROM itheima.orders WHERE useraddress RLIKE '..省 ..市 ..区';
SELECT * FROM itheima.orders WHERE username RLIKE '[张王邓]\\S+';
SELECT * FROM itheima.orders WHERE userphone RLIKEE '188\\S{4}0\\S{3}';
UNION 用于将多个 SELECT 语句的结果组合成单个结果集。
每个 select 语句返回的列的数量和名称必须相同。否则,将引发架构错误。
基础语法:
SELECT ...
UNION [ALL]
SELECT ...
准备数据进行测试
CREATE TABLE it.course(
c_id string,
c_name string,
t_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/hadoop/course.txt' INTO TABLE it.course;
SELECT * FROM course WHERE t_id = '周杰轮'
UNION
SELECT * FROM course WHERE t_id = '王力鸿'
UNION默认有去重功能:
SELECT * FROM course
UNION
SELECT * FROM course
SELECT * FROM course
UNION ALL
SELECT * FROM course
SELECT t_id, COUNT(*) FROM
(
SELECT t_id FROM itheima.course WHERE t_id = '周杰轮'
UNION ALL
SELECT t_id FROM itheima.course WHERE t_id = '王力鸿'
) AS u GROUP BY t_id;
CREATE TABLE it.course2 LIKE it.course;
INSERT OVERWRITE TABLE it.course2
SELECT * FROM it.course
UNION ALL
SELECT * FROM it.course;
对表进行随机抽样是非常有必要的。
大数据体系下,在真正的企业环境中,很容易出现很大的表,比如体积达到TB级别。
对这种表一个简单的SELECT * 都会非常的慢,哪怕LIMIT 10想要看10条数据,也会走MapReduce流程,这个时间等待是不合适的。
Hive提供的快速抽样的语法,可以快速从大表中随机抽取一些数据供用户查看。
进行随机抽样,本质上就是用TABLESAMPLE函数
语法1,基于随机分桶抽样:
SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
示例:
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());
注意:
语法2,基于数据块抽样
SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));
注意:
虚拟列是Hive内置的可以在查询语句中使用的特殊标记,可以查询数据本身的详细参数。
Hive目前可用3个虚拟列:
示例:
SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM it.course;
使用虚拟列,可以让我们更精准的查看到具体每一条数据在存储上的详细参数细节。
虚拟列不仅仅可以用于SELECT,在WHERE、GROUP BY等均可使用。
如:
SELECT *, BLOCK__OFFSET__INSIDE__FILE FROM course WHERE BLOCK__OFFSET__INSIDE__FILE > 50;
SELECT INPUT__FILE__NAME, COUNT(*) FROM it.orders GROUP BY INPUT__FILE__NAME;
除此以外,在某些错误排查场景上,虚拟列可以提供相关帮助。
Hive的函数分为两大类:内置函数(Built-in Functions)、用户定义函数UDF(User-Defined Functions):
Hive内建了不少函数
使用show functions查看当下可用的所有函数;
通过describe function extended funcname来查看函数的使用方式。
----Mathematical 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();
聊天平台每天都会有大量的用户在线,会出现大量的聊天数据,通过对聊天数据的统计分析,可以更好的对用户构建精准的用户画像,为用户提供更好的服务以及实现高ROI的平台运营推广,给公司的发展决策提供精确的数据支撑。
我们将基于一个社交平台App的用户数据,完成相关指标的统计分析并结合BI工具对指标进行可视化展现。
基于Hadoop和Hive实现聊天数据统计分析,构建聊天数据分析报表。
–如果数据库已存在就删除
drop database if exists db_msg cascade ;
–创建数据库
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数据到表
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;
数据问题
select msg_time, sender_name, sender_gps from
db_msg.tb_msg_source where length(sender_gps) = 0 limit 10;
select msg_time from db_msg.tb_msg_source limit 10;
select sender_gps from db_msg.tb_msg_source limit 10;
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 OVERWRITE TABLE db_msg.tb_msg_etl
SELECT
*,
day(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 tb_msg_source WHERE LENGTH(sender_gps) > 0;
select msg_time, msy_day, msg_hour, sender_gps, sender_lng, sender_latfrom db_msg.tb_msg_etllimit 10;
其实我们刚刚完成了 从表tb_msg_source 查询数据进行数据过滤和转换,并将结果写入到:tb_msg_etl表中的操作。
这种操作,本质上是一种简单的ETL行为。
ETL:
ETL在大数据系统中是非常常见的。
--保存结果表
CREATE TABLE IF NOT EXISTS 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;
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_hour_msg_cnt
COMMENT "每小时消息量趋势" AS
SELECT
msg_hour,
COUNT(*) AS total_msg_cnt,
COUNT(DISTINCT sender_account) AS sender_usr_cnt,
COUNT(DISTINCT receiver_account) AS receiver_usr_cnt
FROM db_msg.tb_msg_etl GROUP BY msg_hour;
CREATE TABLE IF NOT EXISTS 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;
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_usr_cnt
COMMENT "今日发送消息人数、接受消息人数" AS
SELECT
msg_day,
COUNT(DISTINCT sender_account) AS sender_usr_cnt,
COUNT(DISTINCT receiver_account) AS receiver_usr_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day;
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_s_user_top10
COMMENT "发送消息条数最多的Top10用户" AS
SELECT
sender_name AS username,
COUNT(*) AS sender_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_name
ORDER BY sender_msg_cnt DESC
LIMIT 10;
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_r_user_top10
COMMENT "接收消息条数最多的Top10用户" AS
SELECT
receiver_name AS username,
COUNT(*) AS receiver_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY receiver_name
ORDER BY receiver_msg_cnt DESC
LIMIT 10;
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_phone
COMMENT "发送人的手机型号分布" AS
SELECT
sender_phonetype,
COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_phonetype;
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_os
COMMENT "发送人的OS分布" AS
SELECT
sender_os,
COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_os;
BI:Business Intelligence,商业智能。
指用现代数据仓库技术、线上分析处理技术、数据挖掘和数据展现技术进行数据分析以实现商业价值。
简单来说,就是借助BI工具,可以完成复杂的数据分析、数据统计等需求,为公司决策带来巨大的价值。
所以,一般提到BI,我们指代的就是工具软件。常见的BI软件很多,比如:
FineBI的介绍:https://www.finebi.com/
FineBI 是帆软软件有限公司推出的一款商业智能(Business Intelligence)产品。FineBI 是定位于自助大数据分析的 BI 工具,能够帮助企业的业务人员和数据分析师,开展以问题导向的探索式分析。
FineBI的特点
注册拿到激活码
安装软件,运行激活
运行后会打开一个网页,设置用户名密码
自己使用,选择内置数据库即可
fineBI首页
fineBI引入hive驱动
安装隔离插件
添加仪表板
去掉标题
切换样式
接受消息人数
结束!!
hy:43
一个胜利者不会放弃,而一个放弃者永远不会胜利。