hive 学习笔记

 

 

前言：

scribe Servers(日志采集系统)

1、概念hive

hive是基于hadoop之上的数据仓库

一种可以存储、查询和分析存储在Hadoop中的大规模数据

hive定义了简单的类SQL查询语言，称为HQL，它允许熟悉SQL的用户查询数据。

允许熟悉MapReduce开发者的开发自定义的mapper和reducer来处理内建的mapper和reducer无法完成的复杂的分析工作。

2、hive安装

3、hive HA原理：

（1）将若干个hive实例纳入一个资源池，然后对外提供一个唯一的接口，进行proxy relay

4、HIve的元素存储到Mysql

Hive的默认元数据信息存在Derby里面

内置的Derby是单Session的（一个客户端连接过来，另一个无法访问）

Unable to instantiate org.Apache.Hadoop.hive.metastore

支持Mysql、Oracle等

(1)一般内置Mysql，这个主要是通过配置（hive-site.xml）去配置的。

(2)需要把mysql-connector-java-5.1-bin.jar放在lib目录下

安装Mysql数据库

5、Hive的基本类型：

（1）基本数据类型：

Tinyint

smallint

int  以上三种统称为int类型

bigint

float

double 

（2）复合类型

ARRAY 一组有序字段，字段类型必须相同Array(1,2)

MAP 一组无序的键/值对，键的类型必须是原子的，值可以是任何类型，同一个映射的键的类型必须相同，值得类型也必须相同

STRUCT 一组命名的字段，字段类型可以不同

6、创建表的语句

实战经验：在项目上一般采用sql语句创建hive表，比较简便的方法是将创建表的语句写到一个sql文件中，直接执行这个sql语句，那么创建表就完成了，如果以后项目上对hive表有变动的地方，直接执行hive语句即可，这样就简便快捷。

7、加载数据，

(1)从本地文件加载数据：

load data local inpath '/opt/file.txt' [OVERWRITE] into table hive_table;

(2)从hdfs上加载：

load data inpath  '/opt/file' [OVERWRITE] into table hive_table;

 

overwrite 关键字，是覆盖的功能，如果里面的数据不需要了，则可以使用这个关键字去覆盖表的数据

 

8、hive与sql的比较：

 

对比项目	Hive	SQL
数据插入	支持批量导入	支持单挑和批量导入
数据更新	不支持	支持
索引	支持	支持
分区	支持	支持
执行延迟	高	低
拓展性	好	有限

 

9、hive 常用功能

hive参数：

YEAR=2014 hive -e "select * from table where year=${env;YEAR}"

hive 的一次命令

hive -e "select * from table limit 10"

hive结果存在本地：

hive -$ -e "select * from table limit 3" >>/tmp/mydqury

hive 查找：

hive -$ -e "set"| grep warehouse

执行hive文件

hive -f /path/to/file/myhive.sql

查看当前路径：

hive>！pwd

hive 里面的执行shell命令

hive>! /bin/echo "......"

查看hdfs

hive>dfs -ls /

第一讲问题总结：

hive的元数据存到mysql里面：

（1）mysql未安装上

（2）python连接hive

内部表、外部表：

表的分区

删除表

修改表

查询语句

where语句

case when then 语句

第二讲：

1、Python 连接hive

（1）安装Thrift

（2）启动

hive  --service hiveserver

启动远程服务

 

2、hive内部表和外部表：

内部表和外部表的具体应用场景需要结合具体的项目。

 

3、Parition(分区)的概念：

ch

数据通过目录划分分区，分区字段是特殊字符。

目录结构：/pub/{dt}/{customer_id}

show partitions partition_table;

按照分区加载数据：

ALTER TABLE patition_table ADD PARTITION (dt='value',dep='value') location '/opt/20140405/pati.log'

 

 

使用分区查询：

select * from tablename where partition_1='value' and partition_2='value';

4、分区相关的操作：

（1）修改表增加分区(????)：

alter table table_name（partition_name1='value',partition_name2='value'） location 'loaction_name' partition(dt='2014-04-02',class='060') location '/pub/ / '

（2）修改表重命名表

alter table table_name rename to new_table_name

（3）修改表修改字段

alter table table_name str1 str2;

从字段str1改到了str2

（4）添加字段：

alter table table_name add columns(str1 string)

添加字段。

5、hive调用mapreduce的情况：

select * from table ;这个情况是不会调用到mapreduce 的

select * from table where partition_name='value';这个情况也是不会调用mapreduce的  其他情况下都会调用mapreduce。

6、case when 

select *  case when 1<salary<500 then 'L1' when 5000=<salary =<1000 then 'L2'

else 'L8' from partition_table;

eg:

第三章：   hive 高级查询语句

1、group by 操作

分组操作：

当使用group by 字句,select 语句，只能包含group by 包含的列

select a,sum(b) from t1 group by a;

 

优化：hive.map.aggr 控制如何聚合，默认是false，如果设置为true，Hive将会在map端做第一级的聚合

这通常提供更好的效果，但是要求更多的内存才能运行成功

设置为true时则在map端就会进行count操作。

set hive.map.aggr=true;

select count(*) from table_name;

2、Join 操作

Hive只支持等值Join：

（1）Reduce join

select a.val，b.val c.val from a join b on(a.key=b.key) join c on (c.key=b.key1)

（2）Map join 只有一个大表时

select  /*+MAP JOIN(b)*/ a.key,a.value from a join b on a.key=b.key

（3）left semi join 

select * from things left semi join sales on(sales.id=things.id)

Reduce join 最为普通的join 策略，不受数据量的大小影响，也可以叫做reduce side join ，最没有效率的一种join方式，它由一个mapreduce job完成

原理：

首先将大表和小表分别进行map操作，在map shuffle的阶段每一个map output key 编程了table_name_tag_prefix+join_column_value,但是在进行partition的时候它冷然只是用join_column_value 进行hash

 

Map join 的计算步骤分两步，将小表的数据编程hashtable 广播到所有的map端，将大表的数据进行合理的切分，然后在map阶段的时候用大表的数据一行一行的去探测（probe）小表的hashtable，如果join key 相等，就写入HDFS 

map join 之所以叫做map join 是因为它所有的工作都在map端进行。

3、Order by 和 Sort by 

order by :asc(升序)默认的 desc 降序。

hive>select * from user_table order by id;

FAILED：Error in semantic 

原因：在order by 状态下所有数据会到一台服务器进行reduce操作，也即只有一个reduce，如果在数据量大的情况下回出现无法输出结果的情况，如果进行limit n ，那只有n map number条记录而已，只有一个reduce也可以处理过来。

 

Order by :

set hive.mapred.mode=nostrict;default value 

set hive.mapred.mode=strict;

(1)order by 和数据库中的Order by 功能一致,按照某一项&几项排序输出

（2）与数据库中order by 的区别在于hive.mapred.mode =strict 模式下必须指定limit ，否则执行会报错。

 

sort by :

sort by 不受hive.mapred.mode是否为strict，的影响

sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序

使用sort by 你可以指定执行的reduce个数（set mapred.reduce.tasks=<number>） 这样可以输出更多的数据。

 

order by和sort by 区别：

（1）Order by与Mysql的order by操作一样，会对查询的结果集做一次全局的排序，也就是说所有的数据都传给一个reduce来处理，对于大数据来说，这个过程可能会很慢

（2）而sort by 只会在每个reduce中进行排序，这样只保证每个reduce的输出数据是有序的（并非全局排序），这样可以提高后面全局排序的效率。

4、Union all

hive语法中对于Union all的使用时非常常见的，主要用于多表合并的场景，Union all 要求各表select出的字段类型必须完全匹配

在实际使用过程中，经常会出现对多表且不同字段的结果表进行合并操作

hive 不支持顶层union，只能将union封装在子查询中，且必须union的查询输出定义别名

 

5、索引

6、bucket

 

 

 

第五讲  Hive的存储类型和复合数据类型：

 

（1）TextFile（文件类型）

（2）Sequence File（二进制）

（3）RCFile

Facebook 开发的一个集行存储和列存储的优点于一身，压缩比更高，读取列更快，它MapReduce环境中大规模数据处理中扮演着重要的角色

 RCFile一种列存储相结合的存储方式，首先，将数据按行分块，保证同一个record在一个块上，避免°一个记录需要读取多个block。

eg: create table hive_rc(name String,gender String，age int)  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

store as rcfile; 

load data local inpath '/path' into table hive_rc;

(提示check the file's format)

建立一张临时表：

create table hive_raw(name String,gender String,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

stored as textfile;

insert into table hive_rc select * from hive_raw；

 

select * from hive_rc;

hadoop fs -ls /user/hive/warehourse/db

 

add jar /path.jar   添加自定义jar包

（4）Hive的自定义输入格式（序列化  SRE）

通过SerDe(serialize/deserialize)，在数据序列化和反序列化时格式化数据

这种方式稍微复杂一点，对数据的控制能力也要弱一些，它使用正则表达式来匹配和处理数据，性能也会有所影响，但它的优点是可以自定义表属性信息，serdeprorerties,在SerDe中通过这些属性信息可以有更多的定制行为。

 

 

ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'

'output.format.string'=''

stored as TEXTFILE

 

WITH SERDEPROPERTIES 

 

（5）Array

一组有序字段，字段的类型必须相同

192.168.1.1,3105007010|3105007011|3105007012

192.168.1.3,3105007014|3105007015|3105007016

CREATE TABLE login_array(

ip string,

uid array<BIGINT>

)

ROW FORMAT DELIMITED 

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '|'

STORED AS TEXTFILE;

load data local inpath '/home/jayliu/test.txt' into table login_array;

固定模式：LOAD DATA (LOCAL) INPATH '/path' into table table_name;

如果是本地则需要添加local  如果不是本地在hdfs上就不需要去添加local ,table_name 值得是表的名称。

使用下标去访问数组：

select ip,uid[0] from login_array where dt='20130101';     使用下标访问数组

select ip,size(uid) from login_array where dt='20130101'   查看数组长度

select ip from login_array where dt='20130101' where array_contains (uid,'30111111') 数组查找

（6）Map

一组无序的键/值对，键的类型必须是原子的，值可以是任何类型，同一个映射的键的类型必须相同，值得类型也必须相同 。

这种形式的文件可以采用MAP 的形式存储：

2014-03-03 12:22:34# 127.0.0.1#get#amap#src=123&code=456&cookie=789#status=success&time=2s

2014-03-04 12:22:35# 127.0.0.1#get#amap#src=123&code=456&cookie=789#status=success&time=2s

create extenal table table_map 

( ts string,

ip string,

type string,

request map<string,string>,

response map <string,string>

)

row format delimited fields terminated by '#'

collection items terminated by '&'

map keys terminated by '='

stored as textfile;

 

查询语句：select * from map_array_raw;

 

select request['src'] from mao_text_raw;

 

（7）Struct

192.168.1.1,3105007010,wow:10|cf:1|qqgame:2

192.168.1.1,3105007010,wow:10|cf:1|qqgame:22

这种格式的

create table login_struct

（ip string,

user struct<uid:bigint,name:string>

）

partitioned by (dt string)

row format delimited fields terminated by ','

collection items terminated by '#'

stored as textfile;

overwrite 

 

 

 

 

 

 

 

 

 

 

 

 

hive 实战

hive内置函数、自定义UDF和UDAF

 hadoop dfs admin -softmode get

 

1、Hive内置函数：

（1）关系运算

等值比较=，

不等值比较<>

小于比较<

小于等于比较<=

大于比较>

大于等于比较>=

空值判断 IS NULL

 

非空值判断 IS NOT NULL

like 比较：LIKE

JAVA的like操作：RLIKE

REGEXP操作:regexp

等值比较=

 

（2）逻辑运算与数学运算

加法操作 +

使其中一个值加5

select total+5 from udf_table;

 

减法操作 -

乘法操作 *

除法操作 /

取余操作 %

位与操作 &

位或操作 |

位异操作 ^

位取反操作

 

 

（3）Hive 逻辑运算

 

 

逻辑与操作  AND

or

（4）数值运算

round 取整

指定精度取整 round

向下取整 floor

向上取整 ceiling

百度下，找一些例子

（5）日期函数

UNIX 时间戳转日期函数 from_unixtime

获取当前UNIX时间戳函数 unix_timestamp

 

（6）条件函数

if函数

非空查找函数 coalesce

条件判断函数 case

 

（7）字符串函数

字符串长度函数：length

字符串反转函数：

（8）类型转换函数