参考视频

黑马程序员Hive全套教程，大数据Hive3.x数仓开发精讲到企业级实战应用_哔哩哔哩_bilibili

介绍

        Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模式，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集

        Hive核心是将 HQL转换成MapReduce程序，然后将程序提交到Hadoop集群执行。

        Hive由Facebook实现并开源

Hive和Hadoop关系

        从功能上来说，数仓至少具备 存储数据 和 分析数据 的能力

        Apache Hive 作为一款大数据时代的数仓软件，具备以上能力。只是Hive并不是自己实现，而是借助Hadoop。

        Hive利用HDFS存储数据，利用MapReduce查询分析数据。

        Hive的最大优点在于让用户专注于编写HQL，Hive帮你转换成MapReduce程序完成对数据的分析

        被称为 Sql On Hadoop

Hive的理解

        1 Hive能将数据文件映射成一张表，这个映射指什么？

        答：文件和表之间的对应关系

        在Hive中能够写sql处理的前提是针对表，而不是针对文件，因此需要将文件和表之间的对应关系描述记录清楚。

        映射信息 专业的叫法称之为 元数据信息（元数据是指用来描述数据的数据metadata）

        具体看，要记录的元数据信息包括：表对应哪个文件（位置信息），表的列对应文件的哪个字段（顺序信息），文件字段的分隔符是什么

        2 Hive的本身到底承担了什么功能职责？

        答：SQL语法解析编译成为MapReduce

结构

1 客户端 ；

2 Hive对sl的语法解析，编译，执行计划变化；

3 Hadoop （MapReduce,Yarn,HDFS）

4 元数据存储

架构，组件

1 用户接口：

        CLI(command line interface,为shell命令行访问)

        JDBC/ODBC

        WebGUI（浏览器访问）

2 元数据存储

        通常是存储在关系数据库，如mysql/derby中。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等

3 Hive 的 Driver 驱动程序

        功能：语法解析器，计划编译器，优化器，执行器

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

元数据（Metadata）

        元数据（metadata），又称中介数据，中继数据，为描述数据的数据。主要是描述数据属性的信息，用来支持如指示存储位置，历史数据，资源查找，文件记录等功能

        Hive Metadata 即 Hive 的元数据，元数据存储在关系型数据库中，如hive内置的Derby，或者第三方如Mysql等

元数据服务（Metastore）

        Metastore 即 元数据服务，作用是管理metadata元数据，对外保留服务地址，让各种客户端通过脸颊metastore服务，由metastore再去连接Mysql数据库来存取元数据

        服务配置由3种模式: 内嵌模式，本地模式，远程模式。        

	内嵌模式	本地模式	远程模式（企业一般这个）
Metastore单独配置、启动	否	否	是
Metastore存储介质	derby	mysql	mysql

        远程模式：

                在生产环境中，建议用远程模式来配置HIve Metastore。在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性，安全性

hive提供的连接：

        老版本：bin/hive 连接Metastore

        

.../hive-3.1.2/bin/hive

        新版本 : bin/beeline 连接Metastore, beeline连接又新启动的HiveServer2连接访问元数据服务

                hiveserver2服务启动前必须先启动datastore服务。

.../hive-3.1.2/bin/beeline

启动后需要手动输入地址
! connect jdbc:hive2://node1:10000
root
密码

Hive可视化客户端： DataGrip,Dbeaver,SQuirrel SQL Client等 

安装部署

前置： 安装好Hadoop，Mysql

参考文档：HIve安装配置（超详细）-CSDN博客

Location 

create table(..
)location '/data' -- 指定本张表的数据在hdfs上的存储路径

Hive 事务

早期hive的sql中没有update和delete

局限性：

        1 尚不支持begin,commit 和 rollback ，所有语言操作都是自动提交的

        2 仅支持ORC文件格式(STORED AS ORC)

        3 默认情况下事务配置为关闭，需要配置参数开启功能

        4 表必须是分桶表(Bucketed)才可以使用事务功能

        5 表参数 transactional必须为true

        6 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表 

设置：
set hive.support.concurrency = true; -- hive是否支持并发
set hive.enforce.bucketing = true;    -- hive2.0开始不需要，是否开启分桶
set hive.exec.dynamic.partition.mode = nonstrict; -- 动态分区
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -- 事务管理类，默认类
set hive.compactor.initiator.on = true; -- 是否在Metastore实例上运行启动线程和关闭线程
set hive.compactor.worker.threads = 1; -- 在此metastore实例上运行多少个压缩程序工作线程

创建事务表
create table(...)
clustered by (id) into 2 buckets stored as orc TALPROPERTIES ('transactional' = 'true');
-- clustered by (id) into 2 buckets  id字段分成2个桶
-- orc 是一种高效的列式存储
-- 开启事务

此表可以用update，delete

Hive View视图

        Hive的视图（View）一种虚拟的表，只保存定义，不实际存储数据

        通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图

        创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败

        视图是用来简化操作，不缓冲记录，也没有提高查询性能的功能

create view v_v1 as select id,name from t_v1 limit 10;

create view v_v2 as select id,name from v_v1 limit 2;

show views;

-- 查看创建语句
show create table v_v1;

-- 删除视图
drop view v_v1;

-- 更改视图属性
alter view v_v1 set TALPROPERTIES ('comment' = 'This is a view');

视图一旦生成，不能加载插入新数据

Hive 3.0新功能

        物化视图

                物化视图（Materialized View）是一个包括查询结果的数据库对象，可以用于预先计算并保存表连接或聚集等耗时较多的操作结果。

                使用物化视图的目的是通过预计算，提高查询性能，当然需要占用一定的存储空间

                hive3.0丢弃了index索引的语法支持，推荐使用物化视图的列式存储文件格式来加快查询的速度

        物化视图和视图的区别：

                视图是虚拟的，物化视图是真实的。

                视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能

create materialized view [if not exists] [db_name].materialized_view_name
[DISABLE REWRITE]
[COMMENT materialized_view_comment]
[PARTITIONED ON(...)]
[CLUSTERED ON () DISTRIBUTED ON () SORTED ON ()]
..
AS SELECT ...

目前数据更新，只支持手动重构
ALTER MATERIALIZED VIEW [db_name].materialized_view_name REBUILD;

是否重写查询使用物化视图，可以通过全局参数控制，默认true
hive.materializedview.rewriting=true

Hive SQL语法

字段类型：

        1 原生数据类型(primitive data type)

                数字型： TINYINT, SMALLINT, INT/INTEGER, BIGINT, FLOAT, DECIMAL, NUMBERIC

                日期型： TIMESTAMP, DATE, INTERVAL

                字符型：STRING，VARCHAR， CHAR

                boolea型：BOOLEAN, BINARY

        2 复杂数据类型(complex data type)

                ARRAY, MAP,用的多   

create table mytable(
    id int , 
    skin_price map<string, int>
)row format delimited
fields terminated by ',' --字段分割符
collection items terminated by '-' -- 集合元素之间的分割符
map keys termainated by ':';   -- 指定map的kv之间的分隔符

操作
String : 'a'||'b' = concat('a','b')
复杂类型运算符
select price[n] from table;    -- array类型，应该是这样，暂时没试过
select price[key] from table;  -- map类型

        DDL

内部表：

        内部表（Internal table）也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表是内部表，hive拥有该表的结构和文件。

        换句话说，hive完全管理表（元数据和数据）的生命周期。当删除内部表时，会删除数据和表的元数据

外部表：

        外部表中的数据不是hive拥有或管理的，只管理表元数据的生命周期。

        要创建一个外部表，需要使用 EXTERNAL 语法关键字

        删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据

        实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全

问：如果选择内外表？

答： 1 当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表；2 当数据来之不易，防止误删，请使用外部表，因为即使删除表，文件也会被保留

分区表

优化，提高查询效率

比如一张表里，有杭州，江苏两城的信息，但杭州只查询杭州的信息，江苏只查询江苏的信息,经常会出现查询条件 city='杭州' or city='江苏'. 避免全表扫描查询，可以用分区提高效率
hdfs 上的文件，库/表/n分区(role=XXX)[/..分区]/文件

create table db.mytable(id int ,name string,city string,qu string)
partitioned by (cityname string comment '城市名称分区',quname string comment '区县名称')

多分区，一般采用两级分区，再多了数据约碎，不利于管理数据

静态分区
load data local inpath '/mmm/hangzhou-shangchengqu.txt' into table db.mytable partition(cityname='hangzhou',quname ='shangchengqu');

查询sql
 select * from db.mytable where city = 'hangzhou'; -- 非分区表
 select * from db.mytable where cityname = 'hangzhou' ; --分区
 -- 查询的结果中，有分区字段，但实际hdfs的数据中是没有的

动态分区:  分区的字段值是基于查询结果（参数位置）自动推断出来的 ，核心语法 insert + select
1 一张融合表
2 设置两个参数
set hive.exec.dynamic.partition=true;  -- 是否开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; -- 指定分区模式，nonstrict非严格，strict严格，严格模式至少有一个为静态分区
insert into table db.mytableDynamic partition(cityname,quname) select t.*,t.city,t.qu from db.mytable t;

-- 分区操作

新增分区
ADD PARTITION 会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询时将不会返回结果
因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区后导入分区数据
ALTER TABLE table_name ADD PARTITION(dt='20270101') location '/user/hadoop/warehouse/..'

ALTER TABLE table_name ADD PARTITION(dt='20270101') location '/user/hadoop/warehouse/..' PARTITION(dt='20270102') location '/user/hadoop/warehouse/..'

重命名
ALTER TABLE table_name PARTITION partition_desc RENAME TO PARTITION partition_spec;
ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');

删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-09') 
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-09') PURGE; --直接删除数据，不进垃圾桶

修改分区
-- 更改分区文件存储格式
ALTER TABLE table_name PARTITION(dt='2008-08-09') SET FILEFORMAT file_format;
-- 更改分区位置
ALTER TABLE table_name PARTITION(dt='2008-08-09') SET LOCATION 'new location';

修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS]
-- hdfs暴力新增分区，hive元数据里没有，用ADD PARTITIONS
-- drop同理
-- sync 等效于 add和drop
-- 如果存在大量未跟踪的分区，则可使用批量运行MSCK REPAIR TABLE，以避免OOME（内存不足）
eg：
MSCK REPAIR TABLE table_name ADD PARTITIONS;

分区表的注意事项：

1 分区表不是建表的必要语法规则，是一种优化手段，可选

2 分区字段不能是表中已有的字段，不能重复

3 分区字段是虚拟字段，其数据并不存储在底层的文件中

4 分区字段值的确来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）

5 Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

分桶表

        分桶表也叫桶表，是一种用于优化查询二设计的表

        分桶表对应的数据文件在底层会被分解成若干个部分，通俗来说就是拆分成若干个独立的小文件

        在分桶时，要根据指定那个字段数据分成几桶

        桶编号相同的数据会被分到同一个桶当中。

        比如用id关联查询，可以用分桶，数值或者hashcode取模。

2.0之前需要指定开启分桶
set hive.enforce.bucketing=true

create table t1(...)
clustered by(state) into 5 buckets;

在创建分桶表时，还可以指定分桶内的数据排序
create table t1(...)
clustered by(state desc) into 5 buckets;

数据新增
1 创建一张全量表
2 
insert into table_bucket select * from noBucketTable;
hdfs :对5取模，文件夹名称：000000_0,000001_0,000002_0,000003_0,000004_0

分桶的好处：

1 基于分桶字段查询时，减少全表扫描

2 JOIN时可以提高MR程序效率，减少笛卡尔积数量

3 分桶表数据进行高效抽样

        当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得特别重要。抽样可以从被抽取的数据中估计和推断出整体的特性

简单的DDl语句

------------- 数据库 ---------------------------
创建新的数据库
create <database>
    COMMENT 数据库的注视说明语句
    LOCATION 知道数据库在HDFS存储位置，默认配置文件中的路径（最好不要指定）
    WITH DBPROPERTIES (property_nanme=property_value,...) 用于指定一些数据库的属性配置
    
切换数据库
use <database>

删除数据库，谨慎,库下没表才能删除
drop (DATABASE|SCHEMA)(IF EXISTS) <database> [RESTRICT|CASCADE]
    CASCADE 可以删除带有表的数据库


------------- 对表 ------------------------------

新建表
CREATE [TEMPORARY临时表][EXTERNAL外部表] TABLE [IF NOT EXISTS] [db_name.]table_name [COMMENT col_comment](
    col_name data_type [COMMENT col_comment],...
)
[COMMENT table_comment]
[PARTITION BY (col_name data_type[COMMENT col_comment],...)] --分区
[CLUSTERED BY (col_name,col_name,...)[SORTED BY (col_name [ASC|DESC],...)] INTO num_buckets BUCKETS] -- 分桶
[ROW FORMAT DELIMITED|SERDE ...]
[STORED as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value,...)]

    ROW FORMAT DELIMITED 
        [FIELDS TERMINATED BY char] 字段之间分隔符
        [COLLECTION ITEMS TERMINATED By char] 集合元素之间分隔符
        [MAP KEYS TERMINATED BY char] Map映射kv之间分隔符
        [LINES TERMINATED By char] 行数据之间分隔符
eg:
CREATE TABLE IF NOT EXISTS mydb.myUserTable(
    id string comment 'id'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
CREATE TABLE IF NOT EXISTS mydb.myUserTable AS SELECT id,name FROM table2


默认分隔符 \001

显示库
show databases;

显示表
show tables [in database];

显示元数据信息
desc formatted myUserTable;
describe formatted myUserTable;

注释信息的中文乱码—— 元数据存储在mysql数据库，默认编码，主要支持Latin编码
Mysql数据库执行：
    alter table hive3.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
    alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
    alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
    alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
    alter table hive3.INDEX_PARAMS modify column PARAM__VALUE varchar(4000) character set utf8;
-- 原来的表的注释无法修改，只能新建表才会生效

删除表
drop table mydb.myUserTable;

Show

显示表的属性信息
show tblproperties table_name;

显示表，视图的创建语句
show create table [db_name.]table_name|view_name;

显示表的所有列，包括分区列
show columns (from|in) table_name [(from|in) db_name];
show columns in student;

显示当前支持的所有自定义和内置的函数
show functions;

DESC用法
查看表信息
desc extended table_name

查看表信息，格式化美观
desc formatted table_name

查看数据库相关信息
describe database_name;

DML

导入数据：1 load 2 insert

新建的数据库，在hdfs下面格式：${hive.metastore.warehouse.dir}/database.db/tablename/结构化文件.txt

暴力,直接放上去：
hadoop fs -put xx.txt ${hive.metastore.warehouse.dir}/database.db/tablename/

hive推荐load
LOAD DATA [LOCAL] INPUT 'filepath' [OVERWRITE] INTO TABLE tablename;
    -LOCAL 加上，后面跟的文件需要和hive的metastore一个服务器，不加则需要在hdfs的文件系统路径里；文件移动是复制操作
    -OVERWRITE 加上是覆盖，不加是追加；文件是移动操作，原来的会没有 
3.0D的新特性：insert as select 查询的最后一组列或load的数据里的最后一列组 作为分区，无法转换则报错，还支持inputformat，
serDe指定输入格式，例如Text，ORC等
load data local inpath 'pwd' into table tabl1; 

INSERT 可用，但速度很慢
INSERT INTO TABLE tablename VALUES (1,2,3);
INSERT INTO TABLE tablename(id,name) VALUES (1,'zl');
INSERT INTO TABLE tablename SELECT id,name FROM tablename2; (推荐)
-- 一次遍历多次插入
from student
insert overwrite table student_1 select num
insert overwrite table student_2 select name;

查询 

DISTINCT 去重
GROUP BY 分组 ，搭配 HAVING + 聚合,出现在group by的select字段要么是group by分组字段，要么是被聚合函数应用的字段
    - HAVING 后跟 聚合函数条件，顺序 where,group by ,having
LIMIT [offset,] rows  分页,offset的偏移量从0开始
    LIMIT 1，3：从第2行开始（包含），共3行
WHERE 
    - 判空 age IS NULL
    - 区间 between 1500 and 3000  =  >1500 and  < 3000 
    - 数据 in (1,2,3)
ORDER BY [ASC|DESC] 排序
聚合： COUNT(*)返回被选行数，COUNT(column)返回某列不为NULL的行数,SUM，MAX，MIN,AVG,length,reverse,concat,split,substr,current_date(),unix_timestamp(),datediff()日期比较，date_add()日期增加，date_sub()日期减少，round()四舍五入,rand()随机函数,if(),nvl()为空判断,case when then [when then ] else end 条件判断，等，不管多少行，聚合后只返回一条数据
    count(distinct column):列存在重复数据，去重后统计
AS 起别名

执行顺序：
    from -》 where -》 group -》 having -》order -》 select
    聚合函数 比 having 优先执行
    where子查询 优先 聚合函数 

join

inner join .. on = join .. on
left|right join .. on
full outer join = full join  全外连接或者外连接 = 左连接+右连接-重复行
left semi join 左半开连接，只返回左表的记录，前提是其记录对于右边的表满足on语句的判定条件（相当于内连接，只显示左表信息）
cross join 返回被连接的两个表的笛卡尔积，慎用

聚合函数

show functions;  查看当下可用的函数
describe function extended functionname 来查看函数的使用方式
    eg： describe function extended max

炸开作用,表生成函数
select explode(`array`(11,22,33));
select explode(`map`("id",123,"name","alias"));
select explode(years) from t1;
侧视图
select a.name,b.year
from t1 a lateral view explode(years) b as year 
order by b.year desc; 
- b 表别名
- year 列别名，map是多个 col1,col2
select a.name,count(b.year) as nums
from t1 a lateral view explode(years) b as year 
group by a.name
order by nums desc;

 HIVE的函数分两大类：内置函数，用户自定义函数（UDF）

内置函数，例如聚合函数

用户自定义函数：UDF（普通函数一进一出），UDAF（聚合函数，多进一出），UDTF（表生成函数，一进多出）

CTE

with q1 as (select num,name from student where num = 9898)
select * from q1;

with q1 as (select num,name from student where num = 9898)
from q1 select *;

with q1 as (select num,name from student where num = 9898)
     q2 as (selct num from q1)
select * from (select num from q2) a;

with q1 as (select num,name from student where num = 9898)
     q2 as (select num,name from student where num = 9899)
select * from q1 union all select * from q2;

with q1 as (select num,name from student where num = 9898)
from q1
insert overwrite table a1 select *;

create table s2 as 
with q1 as (select * from student)
select * from q1;

create view v1 as
with q1 as (select * from student)
select * from q1;

Hive支持select导出数据

导出操作时一个OVERWRITE覆盖操作，慎重

标准写法
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FROMAT row_format][STORED AS file_format]
SELECT ... FROM ...
eg:
insert overwrite directory '/temp/xx' select * from student;
insert overwrite directory '/temp/xx' row format delimited fields terminated by ',' stored as orc select * from student ;
insert overwrite local directory '/root/xx' select * from student;