默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore。
启动hive时，会在当前目录下创建一个metastore_db的derby数据库，所以在不同目录下启动就会有不同的数据库，但数据不会共享。
derby是单用户模式。
Hive默认使用的元数据库为derby，开启Hive之后就会占用元数据库，且不与其他客户端共享数据。
Hive在查询数据时，由于没有索引，需要扫描整个表，因此延迟较高。
hive的bin目录里面的
- schematool 初始化元数据库使用
- hive 本地起一个客户端
- hiveserver2 通过JDBC访问服务端
- beeline 通过JDBC访问客户端
端口
- 19888端口：Web页面历史服务
- 8088端口：Web页面yarn服务
hive引擎有三种：MR、spark、tez；默认引擎是MR
hive-site.xml配置文件
hive --service metastore 开启hive元数据服务，是一个前台进程
在/home/用户 目录下面可以查看历史HQL的记录，使用 ls -a ，查看 .hivehistory 文件
Hive的log默认存放在/tmp/用户/hive.log目录下
命令行配置参数
hive -hiveconf 参数=值
count(*) 和元数据相关，一般是直接查找元数据库，而不运行mr程序
复杂数据类型：Array、Map、Struct
cast(“1” as int) 强制转换为整数1，如果强转类型失败，返回空值 NULL
显示创建表的语句：show create table test;
if not exists 不存在则创建
location ‘hdfs_path’ 指定存放位置
create database if not exists test location ‘/hive’
修改数据库属性
alter database db_hive set dbproperties(‘createtime’=‘20210225’)
删除数据库
drop database hive; # 删除空数据库
drop database hive cascade; # 强制删除数据库，可以删除非空数据库
if exists 判断数据库
create external table …… # 创建外部表
表：内部表(管理表)、外部表
内外部表互相转换
alter table table_name set tblproperties(‘EXTERNAL’=‘TRUE’); # 修改为外部表
alter table table_name set tblproperties(‘EXTERNAL’=‘FALSE’); # 修改为内部表
注意：‘EXTERNAL’=‘TRUE’ 为固定写法，区分大小写
desc formatted table_name; # 查看表的详细信息
创建表时指定 row format 格式，下载数据文件时分隔符可识别，默认分隔符特奇怪
修改表
重命名
alter table table_name rename to new_table_name;
增加、修改和删除分区
增加、修改、替换列信息
alter table table_name change col_old_name col_new_name column_type;
alter table table_name add|replace columns (col_name data_type,……)
注意：add 是新增一字段，字段位置在所有列之后，分区之前；replace则是替换表中所有字段，所以可以用replace删除字段
用insert插入数据会在元数据库修改两个字段值（numFiles、numRows），用 load data …… 导入数据会在元数据库修改一个字段（numFiles），用 hdfs -put …… 不会修改元数据库
load data 导入数据会把hdfs上的文件移动到表文件下，load data local 导入会复制到 表文件夹下，原因是因为hdfs上面显示的是元数据信息，真实数据保存在其他地方，一直没移动。
DML数据操作
数据导入
1.向表中加载数据（load）
load data [local] inpath ‘data_path’ [overwrite] inteo table table_name [patitions(day=‘2’)]
注意：load 默认路径在hdfs
2.通过查询语句插入数据（insert）
insert into table_name1 select * from table_name2;
insert overwrite table table_name1 select * from table_name2;
3.根据查询结果创建表
create table if not exists table_name1 as select id,name table_name2;
4.创建表时根据location加载数据,指定数据在hdfs上面的位置
create external table if not exists table_name(id int,name string) row format delimited fields terminated by ‘,’ location ‘/hive/data/’
5.import 数据到指定 hive 表
import table table_name from ‘path’;
注意：数据得要是使用export导出的
数据导出
1.将查询结果导出本地
insert overwrite local directory ‘本地路径’ row format delimited fileds terminated by ‘,’ select * from table_name;
2.将查询结果导出hdfs
insert overwrite directory ‘hdfs路径’ row format delimited fileds terminated by ‘,’ select * from table_name;
3.export导出的hdfs
export table default.table_name to ‘hdfs_path’;
注意：export和import主要用于两个hadoop集群之间Hive表迁移。
清除表中数据（truncate），truncate只能删除管理表，不能删除外部表
truncate table table_name;
select nvl(e.name,d.name) from user e join student d on e.id = d.id;
nvl(A,B),A为null就用B的值
笛卡尔积：A表的每一条数据和B表连接，所以查询记录数等于 A*B
范围分区，保证分区的全局有序，即可保证全局数据有序
set mapreduce.job.reduces 设置reduces个数
HQL里面用了order by reduce就只有一个，因为要进行全局排序
sort by 区内排序；随机分区，为了避免数据倾斜
distribute by（分区），分区排序
一般sort by 和 distribute by 一起使用
当sort by 的字段和distribute by的字段是同一个则可以用 cluster by ，cluster by 只能默认升序
分区表和分桶表
create table table_name(id int,name string) partitioned by (day string)
row format delimited fields terminated by ‘,’
show partitions table_name
二级分区，在创建分区表时多加一个分区字段
create table table_name(id int,name string) partitioned by (day string,hour string)
row format delimited fields terminated by ‘,’
分区信息与数据联系
1.执行修复命令；扫描hdfs的目录结构补全mysql中的元数据信息
msck repair table table_name;
2.手动添加分区
alter table table_name add partition(name=’’)
3.load数据到分区
load 一个分区表 不加分区信息会放在一个默认分区
动态分区
insert into table table_name partition(part_name) select id,part_name from table_name
insert into table table_name select id,part_name from table_name
注意：上面两条语句的效果一样，part_name 是动态分区列且一定要放在其他列后面，用第二条语句可以不用手动开启非严格模式，分区表的分区列名和被查询表中的列名得一样
分桶表
create table table_name(id int,name string)
clustered by (id)
into 4 buckets
row format delimited fields terminated by ‘,’
分区表的分区字段不能和其他字段重名，分桶表的字段是其他其中一个
函数
查看系统内置函数，show functions;
显示自带函数的用法，desc function upper;
详细显示自带函数的用法，desc function extended upper;
UDF：一进一出
UDAF：多进一出
UDTF：一进多出
”一“指的是行数，而非可接受的参数
case when then else end
查询不同部门男女多少人
方法一：
select
bu,
sum(case sex when “男” then 1 else 0 end) man,
sum(case sex when “女” then 1 else 0 end) famale
from table_name group by bu
方法二：
select
bu,
sum(if(sex=“男”,1,0)) man,
sum(if(sex=“女”,1,0)) famale
from table_name group by bu
UDAF
collect_set(col),聚合函数，将多行数据放在一个数组内显示
例：name age
小李 18
小花 17
小明 18
select age,collect_set(name) names from stu group by age;
结果：
age names
18 [“小李”,“小明”]
17 小花
UDF
行转列
concat
select concat(col1,’-’,col2,’-’,col3)
concat_ws
select concat(’-’,col1,col2,col3)
select concat(’-’,Arrays)
concat_ws(’|’,collect_set(col))
UDTF
列转行
explode(col):将hive中一列复杂的Array或者Map结构拆分成多行
lateral view 侧写
select
movie,
category_name
from movie_info
lateral view explode(split(category,",")) movie_info_tmp as category_name;
注意：lateral view和explode 联合使用，movie_info_tmp 侧写表名，category_name 侧写字段名
如果在使用explode之后还需要原表的数据，就可以用侧写
例如：name city
小明 上海,北京,广东
小花 北京,深圳,杭州
小智 北京,深圳
结果：
name city
小明 上海
小明 北京
小明 广东
小花 北京
小花 深圳
小花 杭州
小智 北京
小智 深圳
窗口函数(开窗函数)
为每一条数据做聚合开窗
current row 当前行
n preceding 往后n行数据
n following 往后n行数据
unbounded 起点
unbounded preceding 表示从前面的起点
unbounded following 表示到后面的终点
lag(col,n,default_val) 往前第n行数据
lead(col,n,default_val) 往后第n行数据
ntile(n) 把有序窗口的待分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。n必须为int类型
[聚合函数] over(partition by [col1] order by [col2] rows between [unbounded preceding] and [current row])
注意：[] 表示有其他的函数或关键字替换
rank() 并列，总数减少
dense_rank() 并列，总数减少
row_number() 不并列
自定义函数
4.上传jar包，一般放在hive的lib目录中，不需要每次add jar
5.add jar [jar_path];
6.创建临时函数于开发好的java class关联
create temporary function [my_len] as “com.atguigu.hive.mystring”
ps：my_len是自己去的函数名
7.使用自定义函数
grouping sets:多维分析
select a,b,count() from test group by a,b grouping sets ((a,b),a,b,());
等于
select a,b,count() from test group by a,b
union
select a,null,count() from test group by a
union
select null,b,count() from test group by b
union
select null,null,count(*) from test;
MR程序默认跑7天，超过7天会给你停止
fetch抓取
fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。
hive.fetch.task.conversion=more 配置参数：none,minimal,more
默认为more，为none则只要用到hdfs数据就用mr程序
小表大表Join(MapJOIN)
将小表放在join的左边，可以使用mapjoin让小的维度表先进内存。在map端完成join。（新版hive已经优化，小表大表位置随便放）
设置自动选择mapjoin
set hive.auto.convert.join=true; 默认为ture
大表小表的阈值设置（默认是25M以下认为是小表）
set hive.mapjoin.smalltable.filesize=25000000;
inner join 会自动过滤空值key
大表join大表（SMB(sort Merge Bucket join)）把两个大文件分成很多个小文件join
分桶join，按照join字段取hash值分桶，然后将两个桶编号一样的join，两个值进行hash如果一样一定是进入编号相同的桶
创建两个大表对应的分桶表，然后用两个分桶表join
设置参数允许
set hive.optimize.bucketmapjoin = ture;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
开启Map端聚合
谓词下推,在inner join的连接语句中使用where 会在join前过滤，前提是where 的字段是连接字段
严格模式
将hive.strict.checks.no.partition.filter 设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。简单说，就是用户不允许扫描所有分区。
将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。
将hive.strict.checks.cartesian.product设置为true时，会限制笛卡儿积的查询。
jvm重用，适用在很多小文件
|