hive总结

Hive

1. hive简介

• hive是一个数据仓库，非Java编程者对HDFS的数据做mapreduce操作。hive是基于hadoop的一个数据仓库工具。数据仓库是用来做查询分析的数据库，基本不用来做修改和删除.
• hive:数据仓库。
• hive:解释器，编译器，优化器等。
• hive运行时，元数据储存再关系型数据库中。（mysql）

2. hive的搭建的模式

1. 内嵌模式（元数据保存再内嵌的derby中，允许一个会话连接，尝试多个会话连接会报错）

2. 单用户模式

• 安装mysql代替derby储存数据

3. 远程模式/多用户模式

• 在服务器端启动MetaStoreServer，客户端利用Thrift协议通过MetaStoreServer访问元数据库

3. HQL详解

• 具体见官方文档

  1. DDL语句

     • 创建数据库 create databse [database_name]
     • 删除数据库 drop database [database_name]
     • 修改数据库 ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
     • 使用和查看数据库 use/show database_name
     • 创建表
       • 建表语句
         create table person(
         id int,
         name string,
         age int,
         likes array,
         address map<string,string>
         )
         row format delimited fields terminated by ‘,’
         collection items terminated by ‘-’
         map keys terminated by ‘:’
         lines terminated by ‘\n’
         *删除表
         drop table table_name //注意：只能删除内部表的元数据和源数据，删除不了外部表的源数据

  2. DML语句

     • 导入数据

       • LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
       • INSERT OVERWRITE TABLE person2 [PARTITION(dt=‘2008-06-08’, country)]
         SELECT id,name, age From ppt;
       • FROM person t1
         INSERT OVERWRITE TABLE person1 [PARTITION(dt=‘2008-06-08’, country)]
         SELECT t1.id, t1.name, t1.age ;
         【from放前面好处就是后面可以插入多条语句 】
         

     • 查询数据并保存

       • A、保存数据到本地：
         例： insert overwrite local directory ‘/opt/datas/hive_exp_emp2’
         ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
         select * from db_1128.emp ;
         //查看数据
         !cat /opt/datas/hive_exp_emp2/000000_0;
       • B、保存数据到HDFS上：
         例： insert overwrite directory ‘/user/beifeng/hive/hive_exp_emp’
         select * from db_1128.emp ;

     • hive的group by \join \having等操作和mysql没多大的区别。

4.Hive分区

• 创建单分区

  
  	create table day_table(id int,content string)   
  	partitioned by (dt string) row format delimited fields   
  	terminated by ','//单分区		
  
  

• 创建多分区

  
  	create table day_table(id int,content string)
  	partitioned by(day string,hour string) row format delimitede fields 
  	terminated by ',' //多分区 
  
  

• 添加分区

  	  
  	//已经创建好分区表，需要添加分区
  	alter table day_table add partition(dt='2018-08-05')
  
  

• 删除分区

  	
  	alter table day_table drop partition(dt='2018-08-05')
  
  

• 数据加载进分区表

  
  	load data [local] inpath '/usr/**' into table day_table partition (dt='2018-08-05')
  
  

• 查看分区

  show partitions day_table

• 重命名分区

  alter table day_table partition(dr='2018-08-05') rename to partition(dt='2018-08-08')

• 动态分区

  • 建立非分区表并加载数据

  • 建立外部分区表并动态加载数据 （注意删除外部表的相关事项）

  • 加载数据到动态分区

  • 设置hive.exec.dynamic.partition.mode=nonstrict

    	
    	from psn21
    	insert overwrite table psn22 partition(age, sex)  
    	select id, name, age, sex, likes, address distribute by age, sex;
    
    

• hiveSerDe

  • Serializer and Deserializer SerDe 用于做序列化和反序列化。

  	
  	Hive正则匹配
  	 CREATE TABLE logtbl (
  	    host STRING,
  	    identity STRING,
  	    t_user STRING,
  	    time STRING,
  	    request STRING,
  	    referer STRING,
  	    agent STRING)
  	  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  	  WITH SERDEPROPERTIES (
  	    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
  	  )
  	  STORED AS TEXTFILE;
  
  
  

5.Beeline,JDBC,hiveserver2

• beeline和hiveserver2
  • 服务端 Hiveserver2直接启动
    • 1、beeline -u jdbc:hive2://node5:10000/default -n root
    • 2、先启动 beeline，然后在交互式界面上使用命令：!connect jdbc:hive2://node5:10000 root 123
  • JDBC
    • 一般是平台使用 展示或接口，服务端启动hiveserver2后，在java代码中通过调用hive的jdbc访问默认端口10000进行连接、访问

6.自定义函数UDF,UDAF,UDTF

• UDF:一进一出
  • 自定义 UDF 需要继承 org.apache.hadoop.hive.ql.UDF。
  • 需要实现 evaluate 函数，evaluate 函数支持重载
  • 把程序打包放到目标机器上去；
  • 进入 hive 客户端，添加 jar 包：hive>add jar /jar/udf_test.jar;(清除缓存时记得删除jar包delete jar /*)
  • 创建临时函数：hive>CREATE TEMPORARY FUNCTION add_example AS ‘hive.udf.Add’;
• UDAF:多进一出
• UDTF:一进多出

7.hive中的参数和变量的设置方式

• 修改配置文件hive-site.xml
• hive set命令 set hive.cli.print.header=true;
• 启动hive cli hive --hiveconf hive.cli.print.header=true
• hive参数初始化配置 初始化再/root/下设置.hiver隐藏文件

8.分桶

• 分桶的意义：数据抽样（ sampling ）、map-join

• 分桶的方式：分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。

• 开启分桶设置：set hive.enforce.bucketing=true;

• 分桶代码流程

  • 创建分桶表

  
  	CREATE TABLE psnbucket( id INT, name STRING, age INT)
  	CLUSTERED BY (age) INTO 4 BUCKETS 
  	ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  
  

  • 加载数据

  
  	insert into table psnbucket select id, name, age from psn31;
  
  

  • 抽样

  	
  	select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age);
  
  

9.视图和索引

• 视图实际上是一句sql，视图并不保存源数据，只是保存了一份元数据
• 索引的存在可以很好的优化查询，是一种以空间换时间的行为

10.hive的运行方式

• 命令行cli运行：控制台模式
  • 与hdfs交互 dfs -ls /
  • 与linux交互 !pwd
• 脚本运行方式（！！！）
  • 注意如果使用crontab -e定时则需要再sh脚本中写上hive的完整路径
    
• JDBC方式：hiveserver2
• web GUI接口：（hwi,hue等）

11.权限管理

• Storage Based Authorization in the Metastore Server基于存储的授权
• SQL Standards Based Authorization in HiveServer2基于SQL标准的Hive授权
• Default Hive Authorization (Legacy Mode) hive默认授权

12.hive的优化（！！！）

• 核心思想：将优化当做一个mapreduce去优化

  • hive本地模式：当数据量小于hive.exec.mode.local.auto.inputbytes.max的时候可以使用本地模式，如果超出仍然会是集群模式
  • 并行计算：

  	
  	通过设置以下参数开启并行模式：
  	set hive.exec.parallel=true;
  	Select t1.cf1,t2.cf2 from 
  	(select count(id)  as cf1 from table1) t1,
  	(select count(id)  as cf2 from table1) t2,
  	注意：hive.exec.parallel.thread.number
  	（一次SQL计算中允许并行执行的job个数的最大值）	
  
  

  • 严格模式：set hive.mapred.mode=strict;

  
  	查询限制
  	1、对于分区表，必须添加where对于分区字段的条件过滤；
  	2、order by语句必须包含limit输出限制；
  	3、限制执行笛卡尔积的查询。
  
  
  

  • hive优化

  	
  	Order By - 对于查询结果做全排序，只允许有一个reduce处理
  	（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）
  	Sort By - 对于单个reduce的数据进行排序
  	Distribute By - 分区排序，经常和Sort By结合使用
  	Cluster By - 相当于 Sort By + Distribute By
  	（Cluster By不能通过asc、desc的方式指定排序规则；
  	可通过 distribute by column sort by column asc|desc 的方式）	
  
  

  • 控制Hive中Map以及Reduce的数量
  • JVM重用：适用场景：小文件过多，task个数过多