【hive】记录一下工作中用到的hive命令

前置命令

1. 初始化元数据信息

schematool -dbType mysql -initSchema

2. 启动hive和hivesever2

nohup hive --service metastore  1>/mnt/metastore.log 2>&1 &
nohup hive --service hiveserver2 1>/mnt/hiveserver2.log 2>&1 &

一、连接hive

1. beeline连接没有权限的hive

beeline -u "jdbc:hive2://192.168.0.100:10000"

2. beeline连接kerberos的hive

// 1. 认证keytab文件

kinit -kt hive.keytab hive/tdh1

// 2. beeline连接hive

beeline -u "jdbc:hive2://192.168.0.100:10000/default;principal=hive/thd1@TDH"

3. beeline连接ldap的hive

使用ldap来认证，需要加-n （name）和 -p （password）参数

beeline -u "jdbc:hive2://192.168.0.100:10000" -n hive -p 123456

4. hive指定yarn队列

set mapred.job.queue.name=root.risk;
set mapreduce.job.queuename=root.tj;
set mapred.queue.names=queue3;

老版本一般 mapred开头
新版本是mapreduce开头: CDH-5.13-hive1.1 版本
老版本，新版本对应的参数可以查出来

5. 直接连接hive，并打印 debug 信息

bin/hive --hiveconf hive.root.logger=DEBUG,console 

二、create

1. 创建与源表相同的表结构（只不过表格式不同）

创建一个存储格式为parquet类型的表

create table student_parquet like student_txt stored as parquet;

创建一个存储格式为parquet类型，并且压缩类型是snappy的表

create table student_parquet like student_txt stored as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');

创建一个存储格式为orc类型的表

create table student_orc like student_txt stored as orc;

创建一个存储格式为orc类型，并且压缩类型为snappy的表

create table student_orc like student_txt stored as orc TBLPROPERTIES ("orc.compress"="SNAPPY");

创建一个存储格式为rc类型的表

create table student_rc like student_txt stored as rcfile;

创建一个存储格式为sequence类型的表

create table student_seq like student_txt stored as sequencefile;

三、分区表

1. 创建分区表

有个技巧 ：一般在结尾有"ed"的用于建表语句中，如partitioned by(分区)，stored as（存储格式），clustered by（分桶）等。

create table <table_name> (name string,age int) partitioned by (year string);

2. 创建多分区表

create table <table_name> (name string,age int) partitioned by (year string, date string);

3. 查看hive表的分区

show partitions <table_name>;

4. 删除hive表的分区

ALTER TABLE student_txt DROP IF EXISTS PARTITION (day='2020');

5. 添加hive表的分区

ALTER TABLE student_txt add partition (day='2020');

6. 查询hive分区表的数据

select * from student_parquet where day=2021;
##  day = 分区字段

7. 按条件批量删除分区

alter table schema.table_name drop partition (ds<'2018-08-01');

四、分桶表

1. 创建分桶表（bucket）

clustered by <分桶字段> ，必须要有分桶字段，对分桶键做hash然后取模

create table student_bucket_parquet (name string, age int) partitioned by (year string) clustered by (age) into 16 buckets stored as parquet;

五、修改hive表属性

1. 修改hive表的location

alter table ods_lhzb_lhzb_xxgl_tszs_xlxx set location 'hdfs://inceptot1/user/hive/warehouse/ods_lhzb.db/admin/ods_lhzb_lhzb_xxgl_tszs_xlxx_test';

2.修改hive表中字段分割符

alter table test01 set serdeproperties('field.delim'='\t');

3.修改序列化分隔符

alter table test01 set serdeproperties('serialization.format'='\t');

4. 修改表字段的注释

alter table student CHANGE COLUMN name name int comment '姓名';

六、导入数据

1. 增量导入数据

insert into student_score select stu.s_id,stu.s_name,sc.s_score from student stu join score sc on stu.s_id = sc.s_id;

2. 覆盖导入数据

insert overwrite table student_score select stu.s_id,stu.s_name,sc.s_score from student stu join score sc on stu.s_id = sc.s_id;

3. 从本地文件系统导入数据

LOAD DATA LOCAL INFILE 'D:/app/load_data_mysql/test.txt' INTO TABLE <table_name>；

4. 从HDFS文件系统追加导入数据

LOAD DATA INPATH '/app/load_data_mysql/test.txt' INTO TABLE <table_name>；

5. 从HDFS文件系统覆盖导入数据

LOAD DATA INPATH '/app/load_data_mysql/test.txt' overwrite INTO TABLE <table_name>；

6. 多表插入（多插入模式）
   注意：不能插入相同的表，但是可以插入同一张表的不同分区表中

from student_txt 
insert overwrite table student_parquet partition(day) 
select name , min(age), min(day) group by name
insert into table student_parquet partition(day) 
select name , max(age), max(day) group by name;

七、desc

1. 查看表信息

desc <table_name>;

2. 查看表结构详细信息
   可以查看numFiles，totalSize等信息。

desc formatted <table_name>;

3. 描述数据库的属性信息

desc database <database_name>;

八、explain

查看sql执行计划， explain 后面跟sql语句

explain select * from student_txt;

查看执行计划的扩展信息

explain extended select * from student_txt;

查看SQL数据输入依赖的信息

explain dependency select * from student_parquet;

看SQL操作涉及的相关权限信息

explain authorization select * from student_parquet;

九、analyze

1. 收集表的统计信息
   表的统计信息一般包含表存储的文件个数（numFiles）、总文件大小（totalSize）、表的总行数（numRows）、分区数（numPartitions）和未压缩的每行的数据量（rawDataSize）等。

analyze table <table_name> compute statistics;

十、lock

查看锁表情况

show locks;

查看某张表lock情况

show locks <table_name>;

注：在cdh5.13版本当中，没有测试成功，和show locks命令结果一致。有知道的大神可以分享下。

解锁

解锁表

unlock table <table_name>;

解锁某个分区

unlock table <table_name> partition(dt='2014-04-01'); 

注：表锁和分区锁是两个不同的锁，对表解锁，对分区是无效的，分区需要单独解锁

十一、权限管理

查看role的权限

show grant role <role_name>;

十二、hive set 常用参数汇总

设置reduce个数

set mapred.reduce.tasks = 20;

开启hive的动态分区模式

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

设置输出文件的数量和大小
merge job后每个文件的目标大小（targetSize），用之前job输出文件的total size除以这个值，就可以决定merge job的reduce数目。merge job的map端相当于identity map，然后shuffle到reduce，每个reduce dump一个文件，通过这种方式控制文件的数量和大小

hive.merge.size.per.task // 默认是256M
在Map-Reduce的任务结束时合并小文件
set hive.merge.mapredfiles=true
设置客户端大小：
以map任务为例，Container其实就是在执行一个脚本文件，而脚本文件中，会执行一个 Java 的子进程，这个子进程就是真正的 Map Task，mapreduce.map.java.opts 其实就是启动 JVM 虚拟机时，传递给虚拟机的启动参数，而默认值 -Xmx200m 表示这个 Java 程序可以使用的最大堆内存数，一旦超过这个大小，JVM 就会抛出 Out of Memory 异常，并终止进程。而 mapreduce.map.memory.mb 设置的是 Container 的内存上限，这个参数由 NodeManager 读取并进行控制，当 Container 的内存大小超过了这个参数值，NodeManager 会负责 kill 掉 Container。在后面分析 yarn.nodemanager.vmem-pmem-ratio 这个参数的时候，会讲解 NodeManager 监控 Container 内存（包括虚拟内存和物理内存）及 kill 掉 Container 的过程。
也就是说，mapreduce.map.java.opts一定要小于mapreduce.map.memory.mb
mapreduce.reduce.java.opts同mapreduce.map.java.opts一样的道理。

mapreduce.map.java.opts
mapreduce.reduce.java.opts
set mapreduce.reduce.java.opts=-Xmx5000M -XX:MaxPermSize=1024m;
目前先总结到这!