HIVE
什么是数据仓库什么是HiveHive架构Hive的优缺点Hive和数据库比较Hive基本数据类型Hive集合数据类型类型转化 DDL数据操作
创建数据库查询数据库/表删除数据库修改数据库 数据库表操作
建表语法内部表外部表分区表分桶表根据查询结果创建表根据已经存在的表结构创建表创建内部表创建外部表创建分区表创建动态分区表创建二级分区表创建分桶表修改表名内部表和外部表互相转换增加分区删除分区HDFS数据与分区表产生联系增加/修改/替换列信息删除表 DML数据操作
向表中装载数据（Load）通过查询语句向表中插入数据（Insert）创建表并加载数据（As Select）import数据到指定Hive表中Insert数据导出Hadoop命令导出到本地Hive Shell命令导出Export导出到HDFS上Sqoop导出清楚表中的数据（Truncate） 查询
全表和特定列查询算术运算符常用函数Limit语句Where语句比较运算符Like和RLike逻辑运算符Group ByHavingJoin排序分区排序 Distribute By分桶排序Cluster By分桶抽样查询空字段赋值case when then else end行转列列转行 篇幅太长了，剩下的下一篇总结
HIVE什么是数据仓库
数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策什么是Hive
由FaceBook开源用于解决海量结构化日志的数据统计 Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表， 并提供类SQL查询功能 1、Hive处理的数据存储在HDFS 2、Hive分析数据底层的实现是MapReduce 3、执行程序运行在Yarn上 Hive依赖于HDFS存储数据，Hive将HQL转换成MapReduce执行， 所以说Hive是基于Hadoop的一个数据仓库 实质就是一款基于HDFS的MapReduce计算框架，对存储的HDFS中的数据进行分析和管理Hive架构
1、Hive的用户接口层，CLI即Shell命令行，CLI最常用 2、Hive将元数据存储在数据库中，连接到这些数据库的模式有三种， 单用户模式、多用户模式、远程服务器模式 3、完成HQL的查询语句分析、语法分析、编译、优化以及查询计划的生成。 生成的查询计划存储在HDFS中，并由MapReduce调用执行 4、Hive的数据存储在HDFS中，针对大部分的HQL查询请求，Hive内部自动转换成MapReduce任务执行Hive的优缺点
优点 1、操作接口采用类SQL语法，提供快速开发的能力 2、避免了去写MapReduce，减少开发人员的学习成本 3、Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合 4、Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高 5、Hive支持用户自定义函数，用户可以根据自己的需求实现自己的函数 缺点 1、迭代式算法无法表达 2、数据挖掘方面不擅长 3、Hive自动生成的MapReduce作业，通常情况下不够智能化 4、Hive调优比较困难，粒度较粗Hive和数据库比较
由于Hive采用了类似SQL的查询语言，因此很容易将Hive理解为数据库。 其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。 数据库可以用在Online的应用中，但是Hive是为了数据仓库为设计。
查询语言 由于SQL被广泛的应用再数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。
数据存储位置 Hive是建立再Hadoop之上的，所有Hive的数据都是存储再HDFS中的。 而数据库则可以将数据保存在块设备或者本地文件系统中
数据更新 由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少。 因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的 而数据库中的数据通常是需要经常进行修改的，因此可以增删改查
索引 Hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描 因此，也没有对数据中的某些Key建立索引 Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据， 因此访问延迟比较高，Hive不适合在线数据查询 由于MapReduce的引入，Hive可以并行访问数据，因此即使没有索引， 对于大数据量的访问，Hive仍然可以体现出优势 数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问， 数据库可以由很高的效率，较低的延迟。
执行 Hive中大多数查询是通过Hadoop提供的MapReduce来实现的， 而数据库通常有自己的执行引擎
执行延迟 由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是Hadoop的可扩展性是一致的 而数据库由于ACID语义的严格限制，扩展行非常有限
数据规模 由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据 数据库可以支持的数据规模较小Hive基本数据类型
| Hive数据类型 | Java数据类型 | 长度 | 例子 |
|---|---|---|---|
| TINYINT | byte | 1byte有符号整数 | 2 |
| SMALINT | short | 2byte有符号整数 | 20 |
| INT | int | 4byte有符号整数 | 20 |
| BIGINT | long | 8byte有符号整数 | 20 |
| BOOLEAN | boolean | 布尔类型，true或者false | TRUE FALSE |
| FLOAT | float | 单精度浮点数 | 3.14159 |
| DOUBLE | double | 双精度浮点数 | 3.14159 |
| STRING | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
| TIMESTAMP | 时间类型 | ||
| BINARY | 字节数组 |
| 数据类型 | 描述 | 语法示例 |
|---|---|---|
| STRUCT | 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 | struct() |
| MAP | MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 | map() |
| ARRAY | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。 | Array() |
Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换 隐式转换规则如下 1、任何整数类型都可以隐式地转换为一个范围更广地类型，如TINYINT可以转化为INT，INT可以转换为BIGINT 2、所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE 3、TINYINT、SMALLINT、INT都可以转换为FLOAT 4、BOOLEAN类型不可以转换为任何其他地类型DDL数据操作
创建数据库
create database mydb;
mydb是自定义数据库名称
create database if not exists mydb comment 'create my db named dbname'
with dbproperties('createtime'='2022-03-19')
if not exists避免要创建的数据库已经存在错误
comment是给数据库加注释
with dbproperties后面跟的键值，描述该数据库的属性信息，eg：作者是谁，创建日期是什么时候等；
create database mydb location '/db_hive.db'
创建数据库指定数据库在HDFS上存放地位置
查询数据库/表
查看建表语句 show create database mydb 显示数据库 show databases 过滤显示查询地数据库 show databses like 'db_hive*' 查看数据库详情 desc database db_hive 显示数据库详细信息 desc database extended db_hive 切换当前数据库 use mydb 查看数据库地数据表 show tables show tables in mydb 查询表结构 desc formatted hive_table删除数据库
删除空数据库 drop database db_hive 如果删除的数据库不存在，采用if exists判断 drop database if exists db_hive 如果数据库不为空，采用cascade，强制删除 drop databse db_hive cascade修改数据库
alter database db_hive set dbproperties('createtime'='2022-03-20')
数据库表操作 建表语法
1、create table 创建一个指定名字的表 2、if not exists 忽略已存在表异常 3、external 关键字可以让用户创建一个外部表 4、comment 为表和列添加注释 5、partitioned by 创建分区表 6、clustered by 创建分桶表 7、sorted by 不常用 8、row format delimited 指定序列化的分隔符 9、stored as 指定存储文件类型 10、location 指定表在HDFS上的存储位置 11、like 允许用户复制现有的表结构，但是不复制数据内部表
默认创建的表都是管理表，也称为内部表。 Hive创建内部表时，会将数据移动到数据仓库指向的路径； 删除内部表时，会将元数据和数据一起删除外部表
Hive不认为外部表完全拥有这份数据 若创建外部表，仅记录数据所在的路径，不对数据的位置做任务改变 删除外部表的时候，只删除元数据，不删除数据 应用场景：每天收集到的网站日志定期流向HDFS文本文件，在外部表的基础上做大量的统计分析， 用到的中间表、结果表使用内部表存储，数据通过select insert进入内部表分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。 Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成为小的数据集。 在查询时通过where子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多分桶表
分区针对的是数据的存储路径，分桶针对的是数据文件 分桶是将数据集分解成更容易管理的若干部分的另一个技术根据查询结果创建表
create table if not exists hive_table as select id,name,age from mytable根据已经存在的表结构创建表
create table if not exists hive_table like mytable创建内部表
create table mytable(id int,name string,age int) row format delimited fields terminated by 't' stored as textfile location '/user/hive/warehouse/mytable'创建外部表
create external table mytable(id int,name string,age int) row format delimited fields terminated by 't' stored as textfile location '/user/hive/warehouse/mytable'创建分区表
create table hive_table(id int,name string,age int) partitioned by(month string) row format delimited fields terminated by 't'创建动态分区表
create table hive_table(id int,name string,age int,createtime string) partitioned by （,createtime string） row format delimited fields terminated by ‘t’ 根据表字段进行动态分区 set hive.exec.dynamic.partiton.mode=nonstrict; 设置分区表为非严格模式 hive.sexc.max.dynamic.partitions=1000 所有MR的节点上默认分区数 1000 hive.sexc.max.dynamic.partitions.pernode=100 每个MR的节点上，最大可以创建多少个动态分区 100 hive.exec.max.created.files=10000 整个MR job中，最大可以创建多少个HDFS文件 hive.error.on.empty.partition=false insert into table 表名 partition(动态分区字段) select 字段，。。。 from dept; hive3.0 动态分区不用花partition就能进行动态分区创建二级分区表
create table hive_table(id int,name string,age int) partitioned by(month string，day string) row format delimited fields terminated by 't'创建分桶表
create table hive_table(id int,name string,age int) clustered by(id) into 4 buckets row formated delimited fields terminated by 't'修改表名
alter table hive_table rename to new_hive_table内部表和外部表互相转换
alter table mytable set tblproperties('external'='true')
'external'='true' 将内部表转为外部表
'external'='false' 将外部表转为内部表
增加分区
alter table hive_table add partition(month='202203') partition(month='202204')删除分区
alter table hive_table drop partition(month='202203') partition(month='202204')HDFS数据与分区表产生联系
修复分区 msck repair table 表名 会检查表名路径下的文件是否为分区文件，如不是，则修改元数据使其成为分区表 添加分区 alter table 表名 add partition（分区字段=‘’） 可以将已经存在的非分区文件转为分区表 上传数据不设置分区时，会HIVE_DEFAULT_PARTITION增加/修改/替换列信息
添加列 alter table hive_table add columns(desc string) 更新列 alter table hive_table chang column id sid int 替换列 alter table hive_table replace columns(desc string,dname string,loc string) replace是替换表中所有字段，可以用于删除表字段删除表
drop table hive_table
DML数据操作 向表中装载数据（Load）
语法 1、load data 表示加载数据 2、local 表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表 3、inpath 表示加载数据的路径 4、overwrite 表示覆盖表中已有数据，否则表示追加 5、into table 表示加载到哪张表 6、hive_table 表示具体的表 7、partition 表示上传到指定分区
加载本地文件到Hive load data local inpath 'xx/xx.txt' into table default.hive_table 加载HDFS文件搭配Hive中 load data inpath 'user/hive/xx.txt' into table default.hive_table 加载数据覆盖表中已有的数据 load data inpath 'user/hive/xx.txt' overwrite into table default.hive_table通过查询语句向表中插入数据（Insert）
基本插入数据 insert into table hive_table partition(month='202203') values(1,'zhangsan') 根据单表查询结果插入数据 insert overwrite table hive_table partition(month='202203') select id,name from mytable where month='202202' 根据多张表查询结果 from hive_table insert overwrite table hive_table partition(month='202202') select id,name where month='202202' insert overwrite table hive_table partition(month='202201') select id,name where month='202202'创建表并加载数据（As Select）
创建表并指定在HDFS上的位置 create table if not exists hive_table(id int,name string) row format delimited fields terminated by 't' location '/user/hive/warehouse/hive_table' 上传数据到HDFS上 dfs -put /opt/modele/datas/test.txt /user/hive/warehouse/hive_tableimport数据到指定Hive表中
import table hive_table partition(month='202203') from '/user/hive/warehouse/hive_table'Insert数据导出
将查询的结果导出到本地 insert overwrite local directory '/opt/module/datas/export/hive_local' select * from hive_table 将查询的结果格式化导出到本地 insert overwrite local directory '/opt/module/datas/export/hive_local' row format delimited fields terminated by 't' select * from hive_table 将查询到的结果导出到HDFS上（没有local） insert overwrite directory '/opt/module/datas/export/hive_local' row format delimited fields terminated by 't' select * from hive_tableHadoop命令导出到本地
dfs -get /user/hive/warehouse/hive_table/month=202203/000000_0 /opt/module/datas/export/hive_table.txt;Hive Shell命令导出
bin/hive -e 'select * from default.hive_table;' /opt/module/datas/export/hive_table.txt;Export导出到HDFS上
export table detault.hive_table to '/user/hive/warehouse/export/hive_table'Sqoop导出
bin/sqoop export --connect jdbc:mysql://hadoop1001:2206/company --username root --password 123456 --table hive_table --num-mappers 1 --export-dir /company --input-fields-terminated-by 't' num-mappers 启动N个map来并行导入/出数据，默认4个清楚表中的数据（Truncate）
Truncate只能删除管理表，不能删除外部表中的数据 truncate table hive_table
查询 全表和特定列查询
全表查询 select * from hive_table 特定列查询 select id,name from hive_table 列别名 select id as sid,name nm from hive_table算术运算符
| 运算符 | 描述 |
|---|---|
| A+B | A和B 相加 |
| A-B | A减去B |
| A*B | A和B 相乘 |
| A/B | A除以B |
| A%B | A对B取余 |
| A&B | A和B按位取与 |
| A|B | A和B按位取或 |
| A^B | A和B按位取异或 |
| ~A | A按位取反 |
求总行数 count select count(*) cnt from emp 求最大值 max select max(sal) max_sal from emp 求最小值 min select min(sal) min_sal from emp 求和 sum select sum(sal) sum_sal from emp 求平均值 avg select avg(sal) avg_sal from empLimit语句
Limit子句用于限制返回的行数 select * from emp limit 5;Where语句
使用where子句，将不满足条件的行过滤掉 selsect * from emp where sal>1000比较运算符
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE |
| A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL |
| A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE |
A| 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE | |
| A<=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE |
| A>B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE |
| A>=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE |
| A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
| A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE |
| A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE |
| IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 |
| A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
| A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
Like和RLike1、使用Like运算选择类似的值 select * from emp where sal LIKE '2%'; 2、选择条件可以包含字符或数字 R表示正则匹配 select * from emp where sal RLIKE '[2]';逻辑运算符
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
select * from emp where sal>1000 and deptno=30; select * from emp where sal>1000 or deptno=30; select * from emp where deptno not IN(30, 20);Group By
Group by 语句通常会和聚合函数一起，按照一个或者多个列队结果进行分组， 然后对每个组执行聚合操作 select t.deptno,avg(t.sal) avg_sal from emp t group by t.deptnoHaving
having和where不同点 1、where针对表中的列发挥作用，查询数据； having针对查询结果中的列发挥作用，筛选数据分组函数 2、where后面不能分组函数，而having后面可以使用 3、having只用于group by分组统计语句 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;Join
Hive支持通常的SQL Join语句，但是只支持等值连接，不支持非等值连接 内连接 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。 select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; 左外连接 JOIN操作符左边表中符合WHERe子句的所有记录将会被返回 select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno; 右外连接 JOIN操作符右边表中符合WHERe子句的所有记录将会被返回 select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno; 满外连接 将会返回所有表中符合WHERe语句条件的所有记录。 如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代 select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno; 多表连接 SELECt e.ename, d.deptno, l.loc_name FROM emp e JOIN dept d ON d.deptno = e.deptno JOIN location l ON d.loc = l.loc;排序
Order by 全局排序，一个Reduce select * from emp order by sal ASC 升序（默认） select * from emp order by sal asc DESC 降序 select * from emp order by sal desc 多列排序 select ename, deptno, sal from emp order by deptno, sal ; 每个MapReduce内部排序（Sort by） select * from emp sort by empno desc;分区排序 Distribute By
Distribute By 类似MR中partition，进行分区，结合sort by使用 注意：Hive要求Distribute by语句要写在Sort by语句之前 select * from emp distribute by deptno sort by empno desc分桶排序Cluster By
当distribute by和sort by字段相同时，可以使用cluster by方式 cluster by 除了具有disteibute by的功能外还兼具sort by的功能。 但是排序只能升序排序，不能指定排序规则为ASC或者DESC 等价写法 select * from emp cluster by deptno select * from emp disterbute by deptno sort by deptno分桶抽样查询
TABLESAMPLE(BUCKET x OUT OF y) 。 y必须是table总bucket数的倍数或者因子 select * from stu_buck tablesample(bucket 1 out of 4 on id)空字段赋值
NVL：给值为NULL的数据赋值，她的格式是NVL(string1,replace_with) 它的功能是如果string1为Null，则NVL函数返回replace_with的值，否则返回string1的值， 如果两个参数都为NULL，则返回NULL select nvl(comm,-1) from empcase when then else end
判断方法 ， 类似if else用法 select dept_id, sum(case sex when '男' then 1 else 0 end) male_count, sum(case sex when '女' then 1 else 0 end) female_count from emp_sex group by dept_id; case sex when '男' then 1 else 0 end 判断条件，是男是则返回1 否则返回0行转列
concat（string A/col，string B/col。。。）返回输入字符串连接后的结果，支持任务个输入字符串
concat_ws(separator,str1,str2,...) 它是一个特殊形式的concat().第一个参数剩余参数键的分隔符。
分隔符可以是于剩余分隔符一样的字符串
collect_set(col) 函数只接受基本数据类型，它的作用主要是讲某字段的值进行去重汇总，产生array类型字段
concat
select concat('a','-','b','-','c');
输出结果
a-b-c
------------------------
select concat(deptno,'-',dname) from dept;
输出
10-ACCOUNTING
20-RESEARCH
30-SALES
40-OPERATIONS
-------------------------
concat_ws
select concat_ws('-','a','b','c')
第一位放分隔符，后面放参数
输出结果
a-b-c
---------------------------
collect_set
select collect_set(id) from student1;
输出 【“1001”，“1002”，“1003”】
collect_list
select collect_list(id) from student1;
输出 【“1001”，“1002”，“1003”,“1001”，“1002”，“1003”,“1001”，“1002”，“1003”】
列转行
explode（col） 讲hive一列中复杂的array或者map结构拆分成多行
lateral view 用于和split，explode等UDTF一起使用，它能够讲一列数据拆成多行数据，
在此基础上可以对拆分后的数据进行聚合
select
movie,
category_name
from
movie_info
lateral view explode(category) table_tmp as category_name;
篇幅太长了，剩下的下一篇总结
