目录
hive概述:
由Facebook开源用于解决海量结构化日志的数据统计,后称为Apache Hive为一个开源项目。
- 结构化数据:数据类型,字段,value---》hive
- 非结构化数据:比如文本、图片、音频、视频---》会有非关系型数据库存储,或者转换为结构化。
- 结构化日志数据:服务器生成的日志数据,会以空格或者制表符分割的数据,比如:apache、nginx等等。
- Hive 是一个基于 Hadoop 文件系统之上的数据仓库架构,存储用hdfs,计算用mapreduce
- hive还支持类sql语言,它可以将结构化的数据文件映射为一张数据库表,并提供简单的SQL查询功能(披着sql外衣的MapReduce)
- hive有个默认数据库:derby,默认存储元数据---》后期转换成关系型数据库存储mysql
小编已经写了怎么安装hive,以及怎么连接可视化软件,这里用到的是DBeaver软件
这篇文章的目的是怎么用hive对数据进行操作
Hadoop使用hdfs存储数据,Hive则是把hdfs数据文件映射成数据表,通过Hive SQL操作HDFS中的数据,其中Hive将SQL语句转换成MapReduce任务进行,因此Hive实质就是一个基于 HDFS 的 MapReduce 计算框架,对存储在 HDFS 中的数据进行分析和管理。
一、Hive数仓建模
(一)创建数据库和数据表
1.启动hive
- 首先启动集群
start-all.sh
- 启动hive
cd /opt/modules/hive/bin
hiveservice.sh start
hiveserver2
复制会话,输入hive
hive
- 连接到DBeaver
2.创建数据库
create database if not exists ods_didi; #原始数据层
create database if not exists dw_didi; #数据仓库层
create database if not exists app_didi; #应用层
show databases; #查看所有数据库
DBeaver软件已经与hive相对应!
3.创建表
新建SQL编辑器
创建表
-- 创建表
CREATE TABLE IF NOT EXISTS database_name.table_name (
column1_name data_type [COMMENT 'column1_comment'],
column2_name data_type [COMMENT 'column2_comment'],
...
)
[PARTITIONED BY (partition_column data_type [COMMENT 'partition_column_comment'], ...)]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY 'field_terminator' [ESCAPED BY 'escape_char']]
[STORED AS file_format]
[TBLPROPERTIES ('key1'='value1', 'key2'='value2', ... )];
-- 查看表结构描述
DESCRIBE database_name.table_name;
-- 查询表中所有数据
SELECT * FROM database_name.table_name;
- 中括号的都不是必须的
CREATE TABLE
: 用于创建表的关键字。IF NOT EXISTS
: 如果表不存在则创建,避免重复创建表。database_name.table_name
: 表的完整名称,包括数据库名和表名。- 列定义:每列包括列名、数据类型,可以包括注释。(分别是column1_name 、data_type、comment)
PARTITIONED BY
: 可选,用于指定分区列,实现对表的分区管理。- hive中的表需要映射到hdfs文件中,所以需要定义文件中的每一行之间的分隔符,每一列之间的分隔符。增加行分隔符的语句如下:
ROW FORMAT DELIMITED FIELDS TERMINATED BY
: 指定行的格式以及字段之间的分隔符。ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
- 这句的意思是以逗号来分隔行数据,那么这一行中的数据只要遇到一个逗号就可以划分为一个数据。这里的分隔符可以是其他字符,比如" ", "#" , "|", ''/t"(四个空格)等,一般只要用数据文件中可以区分每一行中的不同数据即可。
STORED AS
: 指定存储格式,如TEXTFILE
,ORC
,PARQUET
等。TBLPROPERTIES
: 可以包含表的一些属性,以键值对的形式提供。
举例:创建一个用户打车订单表,分别设置一下信息
-- 创建打车订单表
CREATE table if not exists ods_didi.t_user_order(
orderId string comment '订单ID',
telephone string comment '打车用户手机',
lng string comment '用户发起打车的经度',
lat string comment '用户发起打车的纬度',
province string comment '所在省份',
city string comment '所在城市',
es_money double comment '预估打车费用',
gender string comment '用户信息 - 性别',
profession string comment '用户信息 - 行业',
age_range string comment '年龄段(70后、80后、…)',
tip double comment '小费',
subscribe integer comment '是否预约(0 - 非预约、1 - 预约)',
sub_time string comment '预约时间',
is_agent integer comment '是否代叫(0 - 本人、1 - 代叫)',
agent_telephone string comment '预约人手机',
order_time string comment '预约时间'
)
partitioned by(dt string comment '时间分区')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
DESCRIBE ods_didi.t_user_order;
SELECT * FROM ods_didi.t_user_order;
整型:TINYINT、SMALLINT、INT、BIGINT
浮点:FLOAT、DOUBLE
布尔类型:BOOL (False/True)
字符串:STRING
创建其他表
- 订单取消表
- 订单支付表
- 订单评价表
4.查看hive里的表结构
desc ods_didi.t_user_order;
(二)hive数仓数据加载
1.上传数据
/opt/modules/tempdata
2.加载数据
load data local inpath '/opt/modules/tempdata/order.csv' into table t_user_order partition (dt='2020-04-12');
这里有个错误 FAILED: SemanticException [Error 10001]: Table not found ods_didi.t_order_total
显示找不到这个表,这时候表名是对应的,但是查不到,这是为什么呢?
因为自己创建了3个数据库,hive自带的数据库名字为default,在这个数据库中查找表,是查不到的,需要我们切换到对应的数据库
#切换
use 数据库名
use ods_didi;
继续加载其他四个表
3.查看结果
二、HiveSQL数据预处理操作
(一)创建表
1.创建用户订单宽表(dw——数据仓库层)
-- 创建表
CREATE table if not exists dw_didi.t_user_order_wide( #命令 数据库名.数据表名
orderId string comment '订单ID', #字段名 #数据类型 #注释
telephone string comment '打车用户手机',
lng string comment '用户发起打车的经度',
lat string comment '用户发起打车的纬度',
province string comment '所在省份',
......
order_hour string comment '小时',
order_time_range string comment '时间段',
order_time string comment '预约时间'
)
partitioned by(dt string comment '时间分区')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; #以逗号分割
DESCRIBE dw_didi.t_user_order_wide;#查看表结构
(二)插入数据
-- 静态分区插入
INSERT OVERWRITE TABLE database_name.table_name PARTITION (partition_column1=value1, partition_column2=value2, ...)
SELECT * FROM source_table;
1.静态分区插入:
在这个语句中:
INSERT OVERWRITE TABLE
: 表示覆盖插入,即先删除目标分区中的数据,再插入新数据。database_name.table_name
: 要插入数据的目标表。PARTITION (partition_column1=value1, partition_column2=value2, ...)
: 指定要插入数据的分区列及其对应的值。SELECT * FROM source_table
: 从源表source_table
中选择数据插入到目标表。
-- 动态分区插入
INSERT OVERWRITE TABLE database_name.table_name
PARTITION (partition_column1, partition_column2, ...)
SELECT column1, column2, ..., partition_column1, partition_column2, ... FROM source_table;
2.动态分区插入:
在这个语句中:
PARTITION (partition_column1, partition_column2, ...)
: 指定动态分区插入,Hive 会根据 SELECT 语句中的字段值自动确定分区值。SELECT column1, column2, ..., partition_column1, partition_column2, ... FROM source_table
: 选择要插入的数据,包括分区列和其他列。需要注意的是,插入数据的表和源表的列名、数据类型需要匹配,否则会导致插入失败。此外,分区列的值在 SELECT 语句中也需要包括在内。
举例:用户宽表插入数据
-- 插入数据
insert overwrite table dw_didi.t_user_order_wide partition(dt='2020-04-12')
select
orderid,telephone,lng,lat,province,city,es_money,gender,profession,age_range,tip,subscribe,
case when subscribe = 0 then '非预约'
when subscribe = 1 then '预约'
end as subscribe_name,sub_time,is_agent,
case when is_agent = 0 then '本人'
when is_agent = 1 then '代叫'
end as is_agent_name,
agent_telephone,
date_format(concat(order_time,':00'),'yyyy-MM-dd') as order_date,
year(date_format(concat(order_time,':00'),'yyyy-MM-dd:ss')) as order_year,
month(date_format(concat(order_time,':00'),'yyyy-MM-dd:ss'))as order_month,
day(date_format(concat(order_time,':00'),'yyyy-MM-dd:ss'))as order_day,
hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss'))as order_hour,
case when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 1 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 5 then '凌晨'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 5 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 8 then '早上'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 8 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 11 then '上午'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 11 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 13 then '中午'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 13 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 17 then '下午'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 17 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 19 then '晚上'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 19 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 20 then '半夜'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 20 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 24 then '深夜'
when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) >=0 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 1 then '深夜'
else 'N/A'
end as order_time_range,
date_format(concat(concat(order_time,':00'),':00'),'yyyy-MM-dd HH:mm:ss') as order_time
from ods_didi.t_user_order WHERE dt = '2020-04-12' and length(order_time) > 8;
SELECT * from dw_didi.t_user_order_wide
1.insert overwrite table dw_didi.t_user_order_wide partition(dt='2020-04-12'): 这是插入数据的语句的开头。dw_didi 是数据库名,t_user_order_wide 是表名,partition(dt='2020-04-12') 指定了插入数据的分区,这里的分区是按照日期 dt 进行的,具体为 '2020-04-12'。
2.select ...: 这是一个 SELECT 语句,用于选择要插入的数据。每一行都包含了一系列字段的值,这些字段的数据来源是原始的订单数据表。(ods_didi)3.case when hour(date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) > 1 and hour (date_format(concat(order_time,':00'),'yyyy-MM-dd HH:mm:ss')) <= 5 then '凌晨' ...: 这是一个复杂的 case 语句,根据订单时间的小时部分判断是凌晨、早上、上午等等,然后赋给一个新的字段。
- 查看结果
(三)查询数据
#hive 当中
select * from dw_didi.t_user_order_wide limit 2; #查询这个表的两条数据
三、指标分析
(一)创建表(app-应用层)
1.总订单笔数
1.1创建表
--创建保存日期对应订单笔数的app表
create table if not exists app_didi.t_order_total(
order_date string comment '日期(年月日)',
count integer comment '订单笔数'
)
partitioned by (month string comment '年月,yyyy-MM')
row format delimited fields terminated by ','
;
desc app_didi.t_order_total;
1.2插入数据
--总订单笔数插入数据
insert overwrite table app_didi.t_order_total partition(month='2020-04')
select
'2020-04-12',
count(orderid) as total_cnt
from
dw_didi.t_user_order_wide
WHERE
dt = '2020-04-12';
SELECT * from app_didi.t_order_total
2.预约和非预约用户占比
--插入数据
insert overwrite table app_didi.t_order_subscribe_total
partition(month='2020-04')
select
'2020-04-12',
subscribe_name,
count(*) as cnt from dw_didi.t_user_order_wide
where #条件
dt='2020-04-12'
group by #分组
subscribe_name;
SELECT * FROM app_didi.t_order_subscribe_total
3.不同时段占比分析
4.不同地域订单占比
5.不同年龄段,不同时段订单占比
这里我们对表已经做好了!!并且hdfs有对应的路径以及内容,配置好这个,有助于后面做数据迁移!!!