hive 知识总结

hive为何要修改数据库:

deby只支持一个SESSION会话，如果hive使用默认的deby，那么在linux客户端开启第二个Hive命令行的时候，会报错, 而mysql是支持多会话的数据库。

hive对应的列为何不规定长度:   不确定这些字段的长度, 而且最终存储在hdfs文件中(联想与txt)

txt中也没法规定一个间隔列到底要固定存放多宽的举例。

hive以前能够正常使用,突然启动hive报错, 那么一般都是mysql中metastore出现了问题。

hive特点：

提取 转换 加载
除了有SQL， UDF
还支持熟悉mr的用户自定义mapper reducer来处理内建的mapper reducer无法完成的复杂分析工作。

是解析引擎，将SQL转换为mr任务，来在hadoop上执行。
何时走jobtracker   select 非*
何时走namenode     select *

hive本地模式:

hive > set mapred.job.tracker=local;
hive > set hive.exec.mode.local.auto=true;
为何要使用本地模式: 查询速度很快,默认是关闭的,  但是要保证你查询的数据要在你执行hive所在的机器上，如果查询的数据文件不在本地，那么报file does not exists, 感觉还是没必要使用

修改hive warehoust的位置：
hive-default.xml   ---> hive.metastore.warehoust.dir

hive进入命令行的两种方式:
1 #hive
2 #hive --service cli

hive支持增量更新--->也就是hdfs支持增量更新

hive>

   stu

 1  张三

 2 李四

   score

1   50

2   90

 insert into table stu select * from score;  后查询stu:

 stu

1   50

2   90

 1  张三

 2 李四

0 hive参数配置使用: (----> 在hql语句中，通过${}引用变量的值  通过${env:}引用java system的数据 

通过${system:}来引用shell环境变量的值) 

方式1：  范围hivevar定义局部变量
#hive -d columeage=age
hive>create table t(name string, ${columeage} string)
方式2：  范围hiveconf定义全局   
#hive --hiveconf hive.cli.print.current.db=true;
#hive --hiveconf hive.cli.print.header=true;
方式3：  获取java system系统参数 
hive>create table t(name string, ${system:user.name} string)
方式4：  获取shell参数    通过命令env查看shell下的所有环境变量的数据  
hive>create table t(name string, ${env:HOSTNAME} string)

shell env数据部分结果如下：

[root@chinadaas109 ~]# env
HOSTNAME=chinadaas109
TERM=vt100
SHELL=/bin/bash
HISTSIZE=1000
.....

那么hive hql中引用 shell env参数案例如下：

hive (default)> create table ttttt(id string,${env:HOSTNAME});
FAILED: Parse Error: line 1:41 cannot recognize input near ')' '<EOF>' '<EOF>' in column type

hive (default)> create table ttttt(id string,${env:HOSTNAME} string);
OK
Time taken: 0.256 seconds
hive (default)> desc ttttt;
OK
col_name        data_type       comment
id      string
chinadaas109    string
Time taken: 0.254 seconds
hive (default)>

1 hive脚本执行:

linux上直接执行：
$>hive -e "hql" 

$>hive -e "hql">aaa  将执行结果覆盖写入到当前linux目录的aaa文件中

$>hive -S -e "hql">aaa    -S = -slience 以安静方式执行，不显示交互信息 OK time token:1.398 secondes Fetch: 4 row(s)   执行顺序不能换
   如果想保存处理结果同时又不想有交互信息，那么可以使用 -S
$>hive -f filename        -f = -file  执行完后仍留在linux命令台, 做ETL时使用到的案例写法 hive -f data_process_$DATE/02_all_step.sql

$>hive -i /home/my/hive-init.sql  -i 执行hive时初始化时执行， 执行完后将进入hive环境中

在hive命令行下执行：
hive>source file    在当前linux目录下执行存放hql的文件，如果你的hql文件所在的file在别的目录，那么指定相对或者绝对路径后 执行  hive>source 路径/file
eg:  showtable文件在当前linux目录的test文件夹下，其内容为 show tables;  那么在hive命令行下执行的时候写法为:
hive (default)> source test/showtable

2 hive与外部资源的交互：

hive环境内到linux的交互命令---> ！  但是并不是所有的linux命令都能支持hive环境内通过!引用
hive>!ls;
hive>!pwd;

hive环境内到hdfs交互命令     在hadoop命令格式为 hadoop dfs -ls 到hive的时候，将hadoop去掉即可，这么来记忆在hive中如何使用hadoop的命令
hive>dfs -ls /;
hive>dfs -mkdir /hive;

3 hive的JDBC模式

进入hive安装目录，bin/下看到有hive  hiveserver2等两个命令，后者就是hive远程服务对应的开启脚本 

hive端需要开启 远程服务 (端口号10000) 
在hive0.12之前，都是hiveserver1, 在hive0.13的时候，就是hiveserver2，因此要确定你的hiveserver是哪个版本。
直接使用 hiveserver2方式启动hive远程服务，

也可以通过linux shell下执行  hive --service hiveserver -p 10000 >/dev/null 2>&1 &

方式启动，启动后，通过 如下命令查看是否开启，
[root@chinadaas109 ~]# netstat -anp | grep 10000
tcp        0      0 :::10000                    :::*                        LISTEN      10643/java

在java代码中调用hive的JDBC建立连接，


java代码：

Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");

Connection conn = DriverManager.getConnection("jdbc:hive://192.168.1.114:10000/default","","")

Statement stmt = conn.createStatement();

String querySQL = "select id from hello"

 

ResultSet rs = stmt.executeQuery(querySQL);

 

while(rs.next()){

 

}

 

rs.close();

stmt.close();

conn.close();

4 hive web界面模式 (网络资料，本人没有安装过，做个备份)

Web界面安装：
下载apache-hive-0.14.0-src.tar.gz
制作war包放到HIVE_HOME/lib/：hwi/web/*里面所有文件打成war包   ---> 变成zip后 在重命名为war即可
复制 tools.jar(jdk的lib包下面的jar包) 到 hive/lib下
修改hive-site.xml
<property>
    <name>hive.hwi.listen.host</name>
    <value>0.0.0.0</value>
    </property>
  <property>
    <name>hive.hwi.listen.port</name>
    <value>9999</value>
    </property>
  <property>
    <name>hive.hwi.war.file</name>
    <value>lib/hive-hwi-0.14.0.war</value>
</property>
hive web界面的 (端口号9999) 启动方式
	#hive --service hwi &
用于通过浏览器来访问hive
http://hadoop0:9999/hwi/

 5  hive set  hiverc  hivehistory

hive控制台set命令:  等效于 前面介绍的 在linux命令行下的 #hive --hiveconf hive.cli.print.current.db=true; 这种设置方式
hive>set hive.cli.print.current.db=true;
hive>set hive.metastore.warehouse.dir=/hive



hive启动时初始化参数可以使用set命令，在家目录的.hiverc文件内将上述set命令放进去，如果没有 .hiverc文件，可以手动创建此文件
为何要进入~呢，因为谁操作的hive，那么hive就会在这个人的家目录下 记录hive的 配置信息和历史操作命令
eg:
[root@chinadaas109 ~]# cat .hiverc 
set hive.cli.print.current.db=true; 
set hive.cli.print.header=true;
add jar /home/new_load_data/lib/hive-udf.jar; 
create temporary function hivenvl as 'org.apache.hadoop.hive.ql.udf.hivenvl'; 


hive历史操作命令集
~/.hivehistory

6 hive数据类型 复合类型 简介

 数据类型的意义所在：
设置好了类型后，就可以通过hive函数来得到最大 最小 平均之类的结果

hive行/列 分隔符图:

 row format dilimited   全写是这样的,但是全写下建表会报错,  一般 \n这行省略而且hive也只认\n为行间隔

lines terminated by '\n'   
fields terminated by '\t';

因此就成了 我们常见的：

 row format dilimited  fields terminated by '\t';

数据类型：

基本数据类型：
  整型
  布尔
  浮点
  字符
  
复合数据类型：
  Struct
  Array
  Map


在创建符合类型的时候，基本字段的分隔符和复合类型字段的分隔符需要区别出来，如下一个是,一个是: 否则hive不知道该如何解析、


Array使用:
array中的数据为相同类型，例如，假如array A中元素['a','b','c']，则A[1]的值为'b'
hive>create table table1(name string, student_id_list array<INT>) ROW FORMAT DELIMITED  FIELDS TERMINATED BY ','  COLLECTION ITEMS TERMINATED BY ':';


  文件数据file1的内容：
   class1,1001:1002
   class2,1001:1002
load data local inpath file1 into table table1


Struct使用:
structs内部的数据可以通过DOT（.）来存取，例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a
hive> create table student_test(id INT, info struct<name:STRING, age:INT>)   ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  COLLECTION ITEMS TERMINATED BY ':';  

Map使用:

访问指定域可以通过["指定域名称"]进行，例如，一个Map M包含了一个group-》gid的kv对，gid的值可以通过M['group']来获取
create table employee(id string, perf map<string, int>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';

7 数据库定义：

默认数据库"default"
使用#hive命令后，不使用hive>use <数据库名>，系统默认的数据库。可以显式使用hive> use default;

创建一个新库
hive>CREATE DATABASE  
[IF NOT EXISTS] mydb  
[COMMENT] '....';
[LOCATION] '/.......'  

hive>SHOW DATABASES;

hive>DESCRIBE DATABASE [extended] mydb;

hive>DROP DATABASE [IF EXISTS] mydb [CASCADE];  删除库的时候 级联删除库下对应的表

注意:

create database db location '/yourdbname';

如果不写location 数据库默认会在hdfs的/user/hive/warehouse/youdbname.db下
如果写成  location '/';  那么drop database db;时会把hdfs /路径下的所有数据都删除掉, 在实际工作中,我是

直接创建不使用location，使用默认库位置。

 同样创建表时,也不要轻易指定 location的位置,一旦位置指定错误 eg  location '/root' 那么drop table youtable时,
 也会把/root下所有路径都删除掉, 这种责任都是付不起的。

结论: 库 表创建不要指定 location位置,让hive自动给你指定。

查询其他数据库的表:  show tables in youdbname;
这样在多库情况下,你在A库内,看B库内都有哪些表可以用这个命令。

hive创建好的别的库在hdfs路径位置:

hive默认路径下创建库和表hdfs路径, 其中 t1 t2都是在default库下
/user/hive/warehouse/mydb.db/stu 
/user/hive/warehouse/t1
/user/hive/warehouse/t2

9 垃圾回收站:  /use/root/.Trash/Current/db

为了提高磁盘利用率, 回收站功能都关闭中,因为谁也不知道删除的这个东西到底占用多大的空间,
而且删除东西到回收站都是move操作,会占用大量IO.

因此先确认你的集群是否开启垃圾回收
然后在删除时 确认三次后在删除， 确认的方式就是直接 hdfs dfs -ls /use/root/.Trash/ 看是否有此路径

10  查看表详细描述信息:

格式化方式查看表信息:

desc formatted  stu;

hive (default)> desc formatted  stu; 
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
id                      int                                         
name                    string                                      
age                     int                                         
sex                     int                                         
                 
# Detailed Table Information             
Database:               default                  
Owner:                  root                     
CreateTime:             Sun Sep 13 02:19:50 PDT 2015     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop3:9000/user/hive/warehouse/stu      
Table Type:             MANAGED_TABLE            
Table Parameters:                
        COLUMN_STATS_ACCURATE   false               
        last_modified_by        root                
        last_modified_time      1442136074          
        numFiles                1                   
        numRows                 -1                  
        rawDataSize             -1                  
        totalSize               24                  
        transient_lastDdlTime   1442136074          
                 
# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        field.delim             \t                  
        serialization.format    \t                  
Time taken: 0.195 seconds, Fetched: 37 row(s)

 11 hive内部表的作用和内部表转换为外部表(做过实验 可以)

管理表的用处:  测试开发时,一个表用完了 就可以直接删除掉, 并且测试开发中,一般创建自己的数据库。

内部表修改成外部表: 

alter table stu set tblproperties("EXTERNAL"="TRUE");  必须制定大写

然后 hive>desc formatted stu; 看 Table Type是否修改成MANAGED_TABLE

然后hive>drop stu; 此时再按照刚才删除表的格式创建表 stu, 在select * from stu;发现能查看到数据

因为hive创建表是创建好了目录,如果目录内有文件并且文件内数据组织形式和表组织形式一样
那么即使你是新创建的表, select * 依旧能够加载到这个目录下文件的数据。

这个案例过程证明了两点：

1 将hive表格式修改成外部表是正确的。删除表并没有删除文件

2 hive创建表仅仅是创建了目录而已，是一种弱关系

11.1 将外部表修改成内部表:

hive>alter table stu set tblproperties("EXTERNAL"="FALSE"); 

表不管是内部还是外部都可以设置location位置,只不过内部表默认下是有个存储路径(user/hive/warehouse)。

外部表stu并不在user/hive/warehouse下，
修改格式后,
1 表数据依旧能select * 到
2 因为外部表都是有location的,因此即使修改了表为内部表,location位置依旧是原来存储位置

 12  hive 更改表存储属性：

 alter table t1 set fileformat sequencefile;  前提是表的hdfs文件是对应的数据格式， 否则报错

 13 hive 列的操作:  工作中不建议使用

列操作感觉实用性不大，如果你表中已经灌入数据了, 移动列之间位置或者删除列都会造成对应列下数值的错位，因为数值不会跟着列做变更
原表中增加列
删除列---- 通过replace方式 不建议用
修改列名
移动列之间的位置

14 hive索引：

创建索引
create index t1_index on table t1(name)as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table t1_index_table;
as指定索引器，   in table t1_index_table 可以不写,即简写成：
create index t1_index on table t1(name)as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;
全写下多出一张表t1_index_table；
简写下多出一张表default__stu_stu_index__
格式都为：
hive (default)> desc default__stu_stu_index__;
OK
col_name        data_type       comment
name                    string                                      
_bucketname             string                                      
_offsets                array<bigint>  

再次创建索引时,全写下需要指定别的表名, in table othertablename; 


重建索引   创建后还需要重建索引才能生效  创建仅仅是建立了关联 重建类似于初始化 
 alter index t1_index on t1 rebuild;
显示索引
show formatted index on t1;
删除索引
drop index  if exists t1_index on t1;  关键词 if exists可以不加

  14 hive 分区表 桶表:   hive-1 受控表简介(分区表 桶表)

15 hive数据导入导出方式:  hive的四种导入数据方式 两种数据导出方式

 16 hive执行计划： 
粗略展示hivesql执行计划：
hive>explain extended select name from stu group by name;
详细展示hivesql执行计划：
hive>explain extended select name from stu group by name;  由于执行后展示东西太多，没有粘贴,可以线下执行能看到这个语句拆解部分

17 hive日志位置:

hive-log4j.properties内配置如下：
hive.log.threshold=ALL
hive.root.logger=INFO,DRFA
hive.log.dir=${java.io.tmpdir}/${user.name}
hive.log.file=hive.log

${java.io.tmpdir}在linux下是 /tmp 上述翻译过来就是 默认使用 hive.log 一般用root用户安装和登录 那么就在 /tmp/root/hive.log下存储hive日志 

18 hive为何使用 \001作为默认列间隔符：

    \001 是asc码的一个编位 是一个字符 但是是看不到的 屏幕显示不出来  
    防止误拆字段数据,  防止产生二义性。 因为别的字符在字段中可能会出现 但是\0001是不会出现在普通文件中的。

19 查看hive源码涉及的类:

pom.xml中增加：

<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-exec</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-serde</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-service</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-metastore</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-common</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-cli</artifactId>
	<version>0.13.1</version>
</dependency>
<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-jdbc</artifactId>
	<version>0.13.1</version>
</dependency>

涉及的主要类:

 package org.apache.hadoop.hive.ql.Driver implements CommandProcessor(命令执行器)

package org.apache.hadoop.hive.ql.exec.Task

package org.apache.hadoop.hive.ql.exec.mr.MapRedTask; 集群任务

@Override
  public int execute(DriverContext driverContext) {....}

package org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask;  本地任务

hive将hql编译得到一个执行计划，最终输出是写到hdfs上，将这个输出路径返回给hive，

hive在通过namenode去读取数据，最后数据展示在UI(cmd/浏览器)， 结合下面图看：

 20 hive查看默认文件块大小:

hive>set dfs.block.size;
dfs.block.size=134217738  字节 = 128M

21 指定hive表以某种格式存储：

create table t12(id string) stored as sequencefile;
create table t12(id string) stored as rcfile;

 22   hive的 mapper  reducer数量:

    hive任务的mappe-reducer数量

23  hive中何种情况下 只会有一个 reducer task:

a)  汇总操作(并没有使用group by,否则会根据分组不同分到不同reduce节点上)

b) 使用order by: 全局排序

c) 笛卡尔积,  eg:  a join b 

24 开启hive并行执行hive.exec.parallel:

hive-default.xml.template中设置为:

<property>
    <name>hive.exec.parallel</name>
    <value>false</value>  如果允许设置参数为true后 并行最多能是8个
    <description>Whether to execute jobs in parallel</description>
  </property>
  <property>
    <name>hive.exec.parallel.thread.number</name>
    <value>8</value>
    <description>How many jobs at most can be executed in parallel</description>
  </property>

25  hive 调优涉及的几个点:

数据结构

sql语句 集群规模

每个节点配置

都会造成任务执行快慢 ,只有经过很多测试 调出参数来后 才能更好应用。

26  hive中各种by:

order by:

distribute by province + sort by gdp;   这两个by是一家 通常连续出现-->全国中每个省内按照市区的gdp排序;

cluster by是  distribute by... sort by ...的简写.

 27  子查询中一定要有别名,

select * from (select id,devid,job_time from tb_in_base) a;

28 hive中的 union all (不支持union)

union all :  a) 两表要获取相同的字段 b) 结果是两个表的合集 c)注意写法

下面用mysql写法来粘贴(hive写法一致)  其中 1,2,3来自stu   4,5,6来自stu1表

mysql> select a.id, a.name  from stu a union all select b.id, b.name  from stu1 b ;
+------+------+
| id   | name |
+------+------+
| 1    | zm1  |
| 2    | zm2  |
| 3    | zm3  |
| 4    | zm4  |
| 5    | zm5  |
| 6    | zm6  |
+------+------+

 union all 和聚合函数结合:

select count(a.id),sum(a.id)  from (select a.id,a.devid,a.job_time from tb_in_base a
union all select b.id,b.devid,b.job_time from tb_in_up b) a;
两表直接进行union all 可以使用count、sum 等聚合函数

union all 时不能使用count、sum 、max等 聚合函数, 上面例子中能使用是因为真正聚合操作是在单表a上执行的
select * from (select count(a.id) from tb a) a;  ---> 报错 不能运行

  29 hive 类型转换:

hive中金钱字段存储为string类型，现在要计算：

select conam, cast(conam as decimal)*0.94 from d_ei;

 30 hive是否支持 in / not in

stu值：

id    name

1     zm1

2     NULL

做测试:  

hive>select * from stu where id in('1','2');  可以得到 id为1和2的数据

hive>select * from stu where id  not in('1','2');  可以得到id为3的数据

stu:

id    name

1    zhangsan

2   NULL

3   NULL

 测试版本  cdh-5.2.1/hive0.13.1

31 hive内部会自动将字符串转换并计算 ：

hive> select '1' + '2' from stu; ---> 得到 3.0

 hive> select '1' + '2a' from stu; ---> 得到 NULL

hive> select '12.1' + '13.2' from stu; ---> 得到 25.2999999...7

 hive> select 1/'2' from stu; ---> 得到 0.5

32 hive使用正则:

将oracle逻辑迁移到hive上，遇到如下写法：

oracle写法:

case when regexp_like(pri_nadate,'19[0-9]{2}0[1-9]')

转变成hive写法为:

hive> select '195407' rlike '19[0-9]{2}0[1-9]' from stu;   必须要用 hive的 rlike才能正确理解正则，不能用like

_c0

true

true