hive学习笔记整理

1.hive：处理海量数据，数据仓库工具，将结构化的数据文件映射为一张表
hive的本质，将sql转化成mapreduce的程序
底层基于hadoop，存储的数据在hdfs，分析数据走mr，资源调度靠yarn
metastore：数据源文件存储，默认存放再deby中，可以自行更换成mysql
数据在hdfs中
hive和hdfs的关联关系，就是hive的元数据
hive是一个数仓分析工具，不是数据库
安装hive时会遇到的问题：日志jar包冲突问题。删除其中一个jar包
配置metastore到mysql：
在hive_home/conf 目录下新建hive-site.xml 修改connectionURL
先启动hadoop
然后启动hive
启动集群：
启动有两种方式： 1.直接启动hive 2.通过beeline启连接hivserver2 通过jdbc启动hive Beeline -u jdbc：hive2：//hadoop102：10000  -n llw

创建表语句
create table mytable(
id int ,
name string 
)
insert into mytbl values(1001,'zhangsan')
写脚本时执行hive  ：  hive -e "select * from mytbl"

hive参数配置的三种方式：
1）配置文件 ： hive-site.xml （永久有效）
2）hive启动时 hive --hiveconf （临时测试只用一次）
3）连入到hive之后，set命令（临时测试只用一次）

hive的数据类型：
1.基本类型：tinyint，smallint，int，bigint，float，double,boolean,string,timestamp
2.集合数据类型： array（数组类型）,map（key-value），struct（结构体）
create table test2(
name string,
friends array<string>,--数组
chilren map<string,int>,--键值
address struct<street:string,city:string>
)
row format delimited fields terminated by ','--通过逗号分割每个字段
collection items terminated by '_',--集合的分割元素为下划线
map keys terminated by ':'  -- map分割元素为：
lines terminated by '\n'
hive会在hdfs中创建一个目录，放置对应的数据文件
hive直接分析当前文件夹下的数据

类型转换
1.隐式类型转换：
 1）小数据类型向大数据类型转换
 2）所有整数类型，float 和 string类型 都可以隐式的转换成double
 3）tinyint smallint int 都可以转换为float
 4）boolean不可以转换为其他类型
2.显示转换 
 1)CAST() 例：CAST(1 AS INT)
 2）如果转换不成功则返回null

总结：
1安装mysql --先卸载，rpm包安装，安装有顺序（先安装被依赖的组件）
3.修改密码，初始密码在一个log中
4.安装mysql没办法用其他机器远程连入
5.安装hive
6.Jar包冲突，去掉上层的jar包
7.配置元数据，在hive-site.xml中，配置元数据
8.hive启动方式，hive客户端，beeline客户端（需要经过hiveserver2，通过jdbc协议连接）
9.metastore服务，如果配置上了，就需要启动，（如果不配置，hive直接连接mysql，如果配置了就是metastore去连接mysql）
10.启动命令：后台启动服务，打印到控制台
11.属性配置：hive运行日志默认放到tmp，可以修改配置文件（log4j文件），放到指定位置
12.参数配置方式：1。修改配置文件 2.启动hive'时有一个命令，--hiveconf 3.启动后有一个set
13.数据类型：基本类型：int  bigint  float double string     集合类型：array，map，struct
14.类型转换：隐式类型转换，小到大，字符串可以转为double；强制转换CAST，如果转不成功就给一个null

第二天
DDL(数据定义语言)
建库
CREATE DATABASE IF NOT EXISTS DATABASE_NAME
LOCATION HDFS PATH //创建的库默认指定hdfs的一个路径

建表
create [external] table if not exists table_name  -external :外部表，
（col_name data_type[comment col_comment]） --列名，列描述信息
[COMMENT TABLE_COMMENT] --表描述信息
partitioned by (col_name data_type) --分区字段，创建分区表并指定分区字段以及字段类型
[clustered by (col_name,col_name,...) into num buckets ] --指定分桶字段，num分到多少个桶例指定分桶数
[row format delimieted fields terminated by row_format] --指定一条字段的分隔符
[STORED AS file_format] --指定文件存储格式（行式存储，列式存储，默认为textfile）
[LOCATION HDFS PATH] --表的映射路径
[tblproperties(property_name=properly_value,...)] --指定表的属性
[as select_statement] --基于某一个查询表

Row format delimited fields terminated by 
‘,’---通过逗号分割每个字段,meiyi
Collection items terminated by ‘_’ --集合的分割元素为下划线
Map keys terminated by ‘:’ --指定kv分割父
Lines teminated by ‘\n’

***无论文件的location在哪，如果指定了库里的表，这个文件就在这个库里面，与路径无关***

内部表，外部表转换：
alter table test4 set tblproperties(external='true')
内部表在删除时会把对应的目录和数据都删除
外部表不会删除对应的表数据

dml操作
1.数据导入load
 1）向表中装载数据：
 LOAD [LOCAL] DATA  INPATH  '/PATH' [OVERWRITE] INTO TABLE TABLE_NAME
 [PARTION (PARTCOLL='')] 

 LOCAL:如果有，加载本地数据，如果没有加载hdfs里的数据
 inpath： 数据路径
 overwrite：覆盖表中已有的数据
 partition：上传到指定分区
 2）insert
 insert into table_name values(1231,'3222')
 3) as select 
 create table  student2 as select * from student
 4)location
   创建表时直接location数据（最长用的一种方式）
   create table student3（id string,name string）
   row format delimited fields terminated by '\t'
   location '/student4';
 5)import 
  只能导入export导出的数据
  import table student2 from /user/hive/warehouse/export/student;
2.数据导出
  1）insert
  insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result' select * from emptest;

  insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result' 
  row format delimited fields terminated by ':'
  select * from emptest;
*****如果表中的列的值为null，导出到文件中以后通过\N来表示*************

2）export 
export table emptest to '/emptest' ;
export导出的是表结构以及表数据，包括元数据，可以用于hadoop平台之间hive表的迁移

3) dfs 直接将文件拿下来
4) 直接重定向输出
  hive -e 'select * from default.student;' >/opt/module/hive/datas/export/student4.txt

3.查询
1）查询语法
select from table_name
[where ...]
[group by ...]
[having ...]
[order by ...]
[cluster by col]| 以col分组并排序
[distribute by col1] [sort by col] /以col1分组，以col2排序 

***知识点***
orderby：；全局排序，asc，desc 只有一个reducer
map多少个是由切片决定的
reducer有多少个是由分区数决定的
每个reducer内部排序sort_by ,区内排序
sort by 一般不会单独用，因为他是随机分区，通常结合distribute by

设置reduce个数：set mapreduce.job.reduce=3;
select * from emp distribute by deptno sort by depno desc
以deptno分区（如果是分四个区，则用no/4取余的方式分区再排序）,然后已deptno排序
分区表和分桶表
1.分区表（存的时候分目录）：因为数据量太大，按照不同的字段分目录
create table dept_partition(
 deptno int,
 dname string,
 loc string 
)
partition by (day string)
row format delimited fields terminated by '\t'

该表一共四个字段：三个普通字段，一个分区字段
往分区表加载数据
load data local inpath '/opt/module/hi~' into table dept_partition_table partition(day='20221019')
Load data local inpath ‘/opt/module/hi~’ into table dept_patrition partition(day=’20221020’);
Load data local inpath ‘/opt/module/hi~’ into table dept_patrition partition(day=’20221021’);

查看分区表信息：show partitions table_name

对分区表分区的操作：
1.查询分区 show partitions 表名
2.增加分区
Alter table 表名add  partition(day=’20210408’) --添加分区
Alter table 表名add  partition(day=’20210409’)  partition(day=’20210410)--添加多个分区

3.删除分区
Alter table 表名 drop partition(day=’20210408’) 删除分区
Alter table 表名 drop  partition(day=’20210409’),partition(day=’20210410)--删除多个分区

二级分区（多级分区）
创建二级分区表
Create table dept_partition2(
Deptno int,
Dname string,
Loc string
)
Partition by (day string ,hour string)
Row format delimited fields teminated by ‘\t’
Load data local inpath ‘’ into table dept_partition2 partitiion(day=’20220405’,hour=’12’);

数据和分区表关联方式
1.手动创建分区目录，手动上传数据
Hadoop fs -mkdir  /usr/hive/warehouse/mydb.db/dept_partition/day=20200404
Hadoop fs -put  /opt  /usr/.../day=2020404
1）进行分区的修复操作：msck  repair table 表名；
2）Alter table 表名 add  partition(day=’20200405’)
2.直接load数据

如果直接给了数据文件没有分区，如何创建分区表：
1.2.0之后支持直接load
如果是老版本，就先落一个中间表，然后再把数据插入到对应的分区表中

分桶表：
  将数据集分解成更容易管理的若干个部分
  创建分桶表：
  create table studen_bucket(
  id int,
  name string
  )
  clustered by(id) into 4 buckets
  row format delimited fields terminated by '\t'
  load data inpath '' into table 表名
  分区表分的是目录
  分桶分的是数据文件

  多维分析：
  create table testgrouping (
   id int,
   name string,
   sex string,
   deptno int
  )
  row format delimited fields termianted by '\t'

  统计每个部门各多少人，男女各多少人，每个部门中男女各多少人
  select deptno，sex，count(id) from testgrouping group by depotno,sex grouping sets((deptno,sex),sex,deptno)
  空字段赋值：
  if null
  nvl()
  decode

行转列，列转行
  concat（str1,str2,str3）拼接
  concat_ws(',',str1,str2,str3)
  collect_set(col):
  去重汇总，主要功能是将数据的某一列去重后，汇总成一个array
  collect_list(col):汇总不去重

列转行：将复杂的array或者map拆分成多行数据
1explode，将数组拆分成多行
literral view ： 将一列数据拆分成多行数据，在此基础上对拆分后的数据进行聚合
select name ,explode(friends) from test2

select movie,category_name
from movie_info
lateral view explode(split(category,',')) movie_info_tmp as category_name