Hive从入门到精通,HQL硬核整理四万字,全面总结,附详细解析,赶紧收藏吧!!_hive的hql分析-CSDN博客
一,了解Hive
1,Hive的概念及架构
Hive是建立在Hadoop上的数据仓库基础架构。
提供了工具,可以进行ETL
它的查询语言是HQL
Hive是SQL解析引擎,将SQL转译成MR,在Hadoop上执行。
Hive表就是HDFS的目录,数据存储在Hadoop HDFS
Hive相当于hadoop的客户端工具
2,Hive操作客户端
1,CLI,即shell命令行
2,JDBC/ODBC,通过java接口,类似传统数据库JDBC
元数据与普通数据
1,元数据:Hive将元数据存储在数据库中(meatestore),目前只支持mysql
derby。元数据包含什么:表的名字,表的列和分区及其属性,表的数据所在目录。
2,真实数据,存在在HDFS
metastore是hive元数据的集中存放地。
metastore默认使用内嵌的derby数据库作为存储引擎
二,Hive的基本语法
1,Hive建表语法
2,Hive加载数据
1,使用HDFS dfs -put '本地数据' 'hive表对应的HDFS目录下'
2,使用load data inpath
2.1 在HDFS目的之间移动,注意是移动
load data inpath 'input/students.tex' into table students;
2.2 从本地文件系统导入
加上local 关键字,本地指什么?Liunx本地目录下的文件
具体指hiveserver2 部署所在服务的本地
load data local inpath frompath into table students
3,表对表加载
create table xxx as SQL语句
4,表对表加载
insert into table xxx SQL语句(没有as)
注意
1,不管内部表还是外部表,默认存储路径都是 hive/warehouse/xx.db/表名的目录下
3,Hive内部表(Managed tables) vs 外部表(External tables)
区别
1,路径,外部表的路径可以自定义 内部表的路径需要在默认路径hive/warehouse/目录下
2,删除表后,普通表(内部表)数据文件和表信息(表的元数据)都删除,外部表近删除表信息
3,一般公司使用外部表多,避免误删除,和location一起使用
4,外部表还可以将其他数据源中的数据映射到hiv中,比如hbase es
5,设计外部表初衷是让元数据与数据解耦
4,hive分区
1,建立分区表
create external table students_pt1
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(pt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
5,hive动态分区
原始表有分区字典,比如日期,根据日期分区
1)、建立原始表并加载数据
create table students_dt
(
id bigint,
name string,
age int,
gender string,
clazz string,
dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
2)、建立分区表并加载数据
create table students_dt_p
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
3)、使用动态分区插入数据
// 分区字段需要放在 select 的最后,如果有多个分区字段 同理,
//它是按位置匹配,不是按名字匹配
insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,dt from students_dt;
// 比如下面这条语句会使用age作为分区字段,而不会使用student_dt中的dt作为分区字段
insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;
4)、多级分区
create table students_year_month
(
id bigint,
name string,
age int,
gender string,
clazz string,
year string,
month string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
create table students_year_month_pt
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(year string,month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
insert into table students_year_month_pt partition(year,month) select id,name,age,gender,clazz,year,month from students_year_month;
6,分桶
7,Hive连接JDBC
1,启动hiveservice2的服务
hive --service hiveservice2 &
2,maven依赖
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
3)、 编写JDBC代码
import java.sql.*;
public class HiveJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/test3");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from students limit 10");
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
String gender = rs.getString(4);
String clazz = rs.getString(5);
System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
}
rs.close();
stat.close();
conn.close();
}
}
三,Hive数据类型
1,基本数据类型
数值型
TINYINT / SMALLINT / INT / BIGINT
布尔型 BOOLEAN
浮点型 FLOAT / DOUBLE
字符串 STRING
2,日期类型
时间戳 timestamp 日期 date
3,复杂类型
Structs Maps Arrays
四,Hive HQL使用语法
五,实战经验
SELECT
concat(current_timestamp(),rand()*100),
'599190472848441600',
'',
'COMPLETED',
'',
'363338147746021377',
'668f47481ee17b5cb7f6da93',
'',
'100386089104489578496',
'100386089104489578496',
current_timestamp(),
'100386089104489578496',
current_timestamp(),
'1',
m.menu_name,
current_timestamp() AS end_time,
COUNT(*) AS COUNT,
COUNT(DISTINCT l.operation_user) AS person_number
FROM
(select * from smcvyl.ods_xdap_apaas_xdap_menus_1d_full where pt='20240710') m
JOIN
(select * from smcvyl.ods_xdap_apaas_apaas_data_log where pt='20240710') l ON l.operation_object = CONCAT('MENU_', m.id)
WHERE
l.operation_time BETWEEN date_sub(now(), 7) AND now() -- 使用Hive的日期函数date_sub()
AND l.function_menu = 'APP_MENU'
AND l.operation_type = 'VIEW_VIEWS'
GROUP BY
m.menu_name;
smcvyl.ods_xdap_apaas_xdap_menus_1d_full
smcvyl.ods_xdap_apaas_apaas_data_log
分析 对应的业务表,需要同步到hive中,即ODS 原始的业务数据
这个流程怎么做的?首先明确hive有自己的数据库
疑问:
0,hive如何进行ETL的
1,同步的业务数据,是不是也在hdfs中
2,具体操作流程,建立同样的表结构?hive与mysql有区别?建库的逻辑,上面库名 smcvyl,看样是针对整个部门的,(类似数据集市的概念),没有细分多个业务库?
3,数据同步逻辑,业务mysql - > hive hdfs,加载数据 load?
4,hiveODS如何与业务mysql保持实时同步的,如果业务数据增加了或者修改了,hive表ODS如何实时更新的?
5,数据清洗的逻辑,取的需要统计数据了,如何同步的,新建的业务表,具体sql怎么样的,首先是创建统计表,是否是 insert into select?
7,job如何控制的,怎么在每周一早上执行
8,如下,关于project_name,项目的概念?类似数据库,hive中的概念是什么,项目?
增量数据: {project_name}.ods_{数据来源}_{源系统表名}_delta
全量数据: {project_name}.ods_{数据来源}_{源系统表名}
9,内部表 外表表概念 ,业务表需要定义成外部表吗,路径在哪里