Hive 记录

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，内部表 外表表概念 ，业务表需要定义成外部表吗，路径在哪里