一、Hive基础
Hive是一个SQL解析引擎，将SQL语句转译成MR Job，然后在Hadoop平台上运行，达到快速开发的目的。
在没有hive之前，对HDFS上的文件或HBase中的表进行查询时，要手工写一堆MapReduce代码，只能由懂MapReduce的程序员才能搞定，耗时耗力。为解决这个问题，FaceBook实现并开源Hive，解决海量结构化日志查询。
Hive是数据仓库，和传统的数据库有一定的区别。hive中的表是纯逻辑表，即表的元数据。本质就是Hadoop的目录/文件，达到了元数据与数据存储分离的目的。Hive本身不存储数据，它完全依赖HDFS和MapReduce。
– Hive的内容是读多写少，不支持对数据的改写和删除，只能对数据进行批量地导入导出。
【读时模式：hive只有读的时候才会检查数据，解析字段和schema（schema是数据结构的表达）。优点是写数据非常快。】
【写时模式：写的比较慢，它写的过程中是需要去建立索引，压缩、数据一致性、字段检查等等，但读的时候会很快。】
二、Hive概念
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射成一张表，并提供类SQL查询功能（HQL），处理的数据存储在HDFS，用MapReduce实现底层的数据分析，适合离线处理，执行程序运行在YARN。本质是：将HQL转换成MapReduce程序。下面介绍几个关键组件。
1.用户接口: Client
用户接口用来连接hive服务。其中CLI(hive shell)、JDBC/ODBC(java访问hive)比较常用，而WEBUI(浏览器访问hive)只能做数据查询，因此使用较少。
1）cli
命令行模式方式最为常用，将在后面详细介绍。
2）webui
# hive --service hwi #启动webui服务，通过http://192.168.101.10:9999/hwi可查看，但是只能做数据查询用，不常用为了使上述命令能够正常使用，首先进行2个步骤。第一步先在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-1.1.0.war</value>
</property>[root@master lib]# cp /usr/local/src/jdk1.8.0_144/lib/tools.jar . #第二步将tools.jar拷贝到hive的lib目录下，完成上述2步,即可正常使用启动命令3）hiveserver
以JDBC/ODBC的程序登录到hive中操作数据，必须选用远程服务启动方式。
2.元数据: Metastore
hive的元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；默认存储在自带的derby数据库中，推荐使用采用MySQL存储Metastore。
3.底层架构：Hadoop
使用HDFS存储表格数据，使用MapReduce进行计算。
4.驱动器: Driver
包含：解析器、编译器、优化器、执行器。
解析器：将SQL字符串转换成抽象语法树(AST)，对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误（比如select中被判定为聚合的字段在group by中是否有出现）；
编译器：将AST编译生成逻辑执行计划；
优化器：对逻辑执行计划进行优化；
执行器：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/TEZ/Spark；
【AST(abstract syntax tree): 是源代码的抽象语法结构的树状表示，树上的每个节点都表示源代码中的一种结构，之所以说是抽象的，是因为抽象语法树并不会表示出真实语法出现的每一个细节，比如说，嵌套括号被隐含在树的结构中，并没有以节点的形式呈现。抽象语法树并不依赖于源语言的语法，也就是说语法分析阶段所采用的上下文无文文法，因为在写文法时，经常会对文法进行等价的转换（消除左递归，回溯，二义性等），这样会给文法分析引入一些多余的成分，对后续阶段造成不利影响，甚至会使合个阶段变得混乱。因些，很多编译器经常要独立地构造语法分析树，为前端、后端建立一个清晰的接口。】
5.hive优点
hive操作接口采用类SQL语法，提供快速开发的能力(简单、容易上手)；避免了去写MapReduce，减少开发人员的学习成本；统一的元数据管理，可与impala/spark等共享元数据；易扩展(HDFS+MapReduce：可以扩展集群规模；支持自定义函数)等等。
Hive的执行延迟比较高，因此hive常用于数据分析的，对实时性要求不高的场合；Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
三、Hive-1.1.0的安装
1.解压hive-1.1.0-cdh5.7.0.tar.gz
tar -zxvf hive-1.1.0-cdh5.7.0.tar.gz 2.安装mysql
将metastore存放在mysql中，能使得多个机器都能访问metastore，打开多个hive窗口，如果元数据在默认的derby中，只能打开一个窗口。
[root@master ~]# rpm -qa | grep mysql 首先查看是否安装了mysql，有的话，如果版本不对，先卸载，centos7安装的是MariaDB，需要替换掉# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
# rpm -ivh mysql-community-release-el7-5.noarch.rpm #安装后mysql将会替换centos7中的MariaDB
# yum install mysql-community-server
# service mysqld restart初次安装mysql，root账户没有密码，可以直接登录。
# mysql -u root 设置密码
mysql> use mysql; #首先要切换到mysql的数据库。
mysql> select host,user,password from user;#首先查看目前的用户和密码信息。
mysql> set password for'root'@'master' =password('123456');#使master能够登录hive，密码为123456
mysql> set password for'root'@'localhost' =password('123456');#将本地登录也采用相同的密码
mysql> exit；mysql> update user set password=password('123456') where user='root' and host='localhost';也可以采用这条命令进行密码设置注：可以让用户进行远程登录，如下设置。
mysql> GRANT ALL PRIVILEGES ON . TO ‘user’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit;
设置用户名为：user，密码为：password的账号可能通过任意一台机器访问数据库。3.配置文件
1）hive-site.xml，连接mysql
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hivemetastore?createDatabaseIfNotExist=true</value>#设置hive元数据的名称
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>#配置驱动
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>#配置用户
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>#配置密码
</property>
</configuration>
将mysql-connector-java-5.1.27-bin.jar拷贝到hive的lib目录下。
为了在使用hive时，能够知道当前使用的database是哪个，可以继续添加配置信息如下。
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>2）hive-en.sh
HADOOP_HOME=/usr/local/src/hadoop-2.6.0-cdh5.7.04.配置系统环境变量
export HIVE_HOME=/usr/local/src/hive-1.1.0-cdh5.7.0
export PATH=$PATH:$HIVE_HOME/bin5.在hdfs上创建目录
首先确保HDFS正常运行，之后进行命令。hive的元数据默认是存在hdfs上。
$ bin/hadoop fs -mkdir /tmp
$ bin/hadoop fs -p -mkdir /user/hive/warehouse
* 修改目录权限
$ bin/hadoop fs -chmod g+w /tmp
$ bin/hadoop fs -chmod g+w /user/hive/warehouse这时候在输入hive即可启动hive。同时使用 mysql -uroot -p123456登录数据库，在查看databases时，能够看到一个叫做hivemetastore的元数据。
6.修改Hive日志信息
$ mv hive-log4j.properties.template hive-log4j.properties修改其中一条：
hive.log.dir=/usr/local/src/hive-1.1.0-cdh5.7.0/logs四、Hive基础命令
1.bin/hive
# bin/hive -help 查看使用命令
# bin/hive --hiveconf<property=value> 可以在未进入hive时，直接进行相应的配置
# bin/hive -e <quoted-query-string> 直接在未进入hive时，进行hive的操作。
例如：bin/hive-e "select * from db_hive.student ;"
# bin/hive -f <filename> ，这个filename就是执行hive命令的脚本
例如：# touch hivef.sql，加入如下的内容：select* from db_hive.student ;
# bin/hive -f /opt/datas/hivef.sql
# bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt 输出结果
* bin/hive -i <filename>，与用户udf相互使用进入hive命令行模式下：
hive(default)> dfs -ls / ; 在hive cli命令窗口中如何查看hdfs文件系统
hive(default)> !ls /opt/datas;在hive cli命令窗口中如何查看本地文件系统
hive > set ;查看当前所有的配置信息
hive (db_hive)> set system:user.name ;查看某个属性的配置
hive (db_hive)> set system:user.name=beifeng ;为某个属性进行配置，此种方式，设置属性的值，仅仅在当前会话session生效
注：以bin/hive --hiveconf hive.root.logger=INFO,console启动hive，可以将日志信息直接输出到控制台2.数据库的操作
创建数据库的几种写法：
create database db_hive_01 ;
create database if not exists db_hive_01 ;标准写法
create database if not exists db_hive_01 location /user/hive/warehouse/db_hive_01.db' ;# hadoop dfs -ls /user/hive/warehouse，可以看到hdfs上创建了db_hive_01数据库。查看创建的数据库信息：
desc database db_hive_01 ;
desc database extended db_hive_01 ;更详细删除数据库：
drop database db_hive_03 ;
drop database db_hive_03 cascade;级联删除
drop database if exists db_hive_03 ;标准写法
3.表的操作
hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
hive> load data local inpath '/opt/datas/student.txt' into table db_hive.student;
hive> desc extended student;查看完整的表信息
hive> desc formatted student;查看格式良好的信息
hive> show functions;查看hive的操作
hive> desc functions 具体函数名称;
hive> desc extened functions 函数名称; 更详细的操作查看alter table dept_like rename to dept_like_rename ;改名
drop table if exists dept_like_rename ; 删除表truncate table dept_cats ;清除表dept_cats的数据4.创建表的几种方式
方式一：
create table IF NOT EXISTS default.bf_log_20150913( #前面加上default是为了指明表创建在该数据库下
ip string COMMENT 'remote ip address' ,//参数介绍
user string ,
req_url string COMMENT 'user request url')
COMMENT 'Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE ;
load data local inpath '/opt/datas/bf-log.txt' into tabledefault.bf_log_20150913;方式二：
create table IF NOT EXISTS table2 as select ip,req_url from table2;通过方式一创建的表格信息，来创建新的表格，相当于复制了表格的部分或者全部的信息，这时候新创建的表就会有数据。
方式三：
create table if not exists default.dept_like like default.dept ;只是创建了结构，并没有数据，这个表结构和default.dept一样
5.导出hive数据
方式一：输出到本地
insert overwrite local directory '/opt/datas/hive_exp_emp' select * from default.emp ;在本地目录/opt/datas/下会创建一个hive_exp_emp文件夹，文件夹下存有导出的数据。还可以按照下面方式指定分割符。
insert overwrite local directory '/opt/datas/hive_exp_emp'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMSTERMINATED BY '\n'
select * from default.emp ;修改输出格式方式二：采用-e方式
bin/hive -e "select * from default.emp ;" >/opt/datas/exp_res.txt方式三：输出到hdfs系统
insert overwrite directory '/user/hive/hive_exp_emp' select* from default.emp; 放在文件系统上五、Hive表的管理
hive表的本质就是Hadoop的目录/文件，实际的数据存储在hdfs上，hive只是操作这些数据。
1.外部表
管理表（内部表），删除表时，会删除表数据以及元数据，不特别指定时，默认都是内部表。
托管表（外部表），用的多，删除时候，只会删除元数据，不会删除表数据。
dfs -put /opt/emp.txt /user/hive/warehouse/emp_ext2 #首先将一个日志文件上传到dfs系统中emp_ext2目录下create EXTERNAL table IF NOTEXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location'/user/hive/warehouse/emp_ext2';必须要指定路径，否则加载不了数据这时候创建的default.emp_ext2会自动加载刚刚上传的emp_ext2表数据。如果在/user/hive/warehouse/emp_ext2这个路径下面，上传了多个日志文件，那么创建的emp_ext2表会将这些数据全部加载，不论格式是否正确（错误的格式在表中会显示为null）。
需要注意的是，如果在该目录下也创建了一个管理表，管理表也可以读取到该目录下的数据文件，管理表删除了将会把数据也删除掉，导致外部表也读取不到数据。外部表会显示空。
2.分区表
在Hive 中，表中的一个 Partition 对应表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。
– 例如：pvs 表中包含 ds 和 city 两个 Partition，则
– 对应于 ds = 20090801,ctry = US 的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；
– 对应于 ds = 20090801,ctry = CA 的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA
partition将数据按照一定的规格和条件进行管理，目的是为了辅助查询，缩小查询范围，加快数据的检索速度，是个非常重要的概念。
create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)partitioned by (month string,day string) #这里要注意分区的字段在创建表中并不存在，但是用desc查看表结构，可以看到多了分区字段
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;hive (default)> desc emp_partition;
OK
col_name data_type comment
empno int
ename string
job string
mgr int
hiredate string
sal double
comm double
deptno int
month string
day string
# Partition Information
# col_name data_type comment
month string
day string load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition(month='201509',day='13') ;加载数据，必须要指明分区，这个时候在hdfs上就可以看到相应的文件夹。
select * from emp_partition where month = '201509' and day ='13' ;查询的时候也必须指明具体查的是哪个分区。3.分区表注意问题
create table IF NOT EXISTS default.dept_part(
deptno int,
dname string,
loc string
)partitioned by (day string)
ROW FORMAT DELIMITED FIELDSTERMINATED BY '\t';dfs -mkdir -p/user/hive/warehouse/dept_part/day=20150913 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150913 ;创建表时，并没有指明该表时分区表，如果通过手动方式建立分区，hive的元数据并不知道有这个分区的存在，所以该表格只是普通的表格，如果要将该表格转换为分区表，需要进行修复操作。
hive (default)>msck repair table dept_part ;
hive (default)>alter table dept_part add partition(day='20150913');show partitions dept_part ;查询分区的状态4.bucket桶表
Bucket类似于分区表，如果想存完整的数据又不想突破一张表的一个能力范围，可以把一张表拆分成多个表，这个通常叫分库。
主要目的是为了优化查询。例如两个大表都是通过userid来分桶，当做join的时候（自动激活map端的map-side join），只要命中了第一张表的分桶ID，那么就可以直接定位到第二张表分桶的ID，提高查询的速度。第二个优点就是方便采样。
hive中table可以拆分成partition，partition可以通过‘CLUSTERED BY’进一步分成bucket，bucket中的数据可以通过‘SORT BY’排序。
'set hive.enforce.bucketing = true' 首先开启桶表功能create external table rating_table_b
(userId INT,
movieId STRING,
rating STRING
)
clustered by (userId) into 32 buckets;
查询表的结构：
desc formatted rating_table_b;
灌入输入：
set hive.enforce.bucketing = true;
from rating_table
insert overwrite table rating_table_b
select userid, movieid, rating; #在表格的目录下，将会自动生成32个桶表
• 查看sampling数据：
– hive> select * from student tablesample(bucket 1 out of 2 on id);
– table sample是抽样语句，语法：TABLESAMPLE( BUCKET x OUT OF y )
– y必须是table总bucket数的倍数或者因子。
hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。 x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。
5.复合类型
• 数据类型
– 原生类型：TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY（Hive 0.8.0以上才可用）、TIMESTAMP（Hive 0.8.0以上才可用）
– 复合类型：Arrays：ARRAY<data_type>、Maps:MAP<primitive_type, data_type>、Structs:STRUCT<col_name: data_type[COMMENT col_comment],……>、Union:UNIONTYPE<data_type, data_type,……>
例如：select name ,score[‘数学’]，就可以查询到数学成绩
INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age
FROM page_view pv
JOIN user u ON (pv.userid = u.userid);6.transform
支持多语言，实现类似UDF功能。
1) 字段拼接
第1种方法：创建AWK脚本：
]# cat transform.awk
{
print $1"_"$2
}hive> add file /root/hive_test_3/transform_test/transform.awk; #添加脚本执行拼接功能：
select transform(movieid, title) using "awk -f transform.awk" as (uuu) from movie_table;第2种方法：创建Python脚本：
]# cat transform.py
import sys
for line in sys.stdin:
ss = line.strip().split('\t')
print '_'.join([ss[0].strip(), ss[1].strip()])hive> add file/root/hive_test_3/transform_test/transform.py;执行拼接功能：
hive> select transform(movieid, title)using "python transform.py" as (uuu) from movie_table;2) wordcount
首先创建文章表，灌入整篇文章：
hive> CREATE TABLE docs(line STRING);
hive> LOAD DATA INPATH '/The_Man_of_Property.txt' OVERWRITE INTO TABLE docs; #先把文件放入hdfs开发mapper代码：
]# cat mapper.py
import sys
for line in sys.stdin:
ss = line.strip().split(' ')
for word in ss:
print '%s\t1' % (word)hive> add file/root/hive_test_3/transform_wc/mapper.py;测试map功能，输出应该是“单词+1”格式：
select transform(line) using "python mapper.py" as word,count from docs cluster by word;开发reduce：
]# cat red.py
import sys
last_key = None
last_count = 0
for line in sys.stdin:
ss = line.strip().split('\t')
if len(ss) != 2:
continue
key = ss[0].strip()
count = ss[1].strip()
if last_key and last_key != key:
print '%s\t%d' % (last_key, last_count)
last_key = key
last_count = int(count)
else:
last_key = key
last_count += int(count)hive> add file/root/hive_test_3/transform_wc/red.py;执行map和reduce功能：
select
transform(wc.word, wc.count) using "python red.py" as w, c
from
(select transform(line) using "python mapper.py" as word,count from docs cluster by word) wc;把wordcount结果存储起来，创建一张表：
CREATE TABLE word_count(word STRING, countINT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';再次执行map和reduce功能：
insert overwrite table word_count
select
transform(wc.word, wc.count) using"python red.py" as w, c
from
(select transform(line) using "pythonmapper.py" as word,count from docs cluster by word) wc;查询确认结果：
hive> select * from word_count order by count desc limit 10;7.hive整合hbase
首先确保hbase正常启动。
进入hbase shell终端，创建Hbase表：
hbase shell
create 'classes','user'
插入数据：
put 'classes','001','user:name','jack'
put 'classes','001','user:age','20'
put 'classes','002','user:name','liza'
put 'classes','002','user:age','18'创建Hive表：
]# cat create_hive_hbase.sql
create external table classes(id int,name string,age int)
STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key,user:name,user:age")
TBLPROPERTIES("hbase.table.name"= "classes");检查表结构：
hive> desc formatted classes;
检查数据：
select * from classes;
删除hbase某条记录：
hbase(main):022:0> delete "classes", '001', 'user:name' #再次检查hive中表格，会发现相应的字段变成null六、数据压缩
数据压缩可以减小本地磁盘存储空间和保存到本地所用时间，即IO，同时减少网络IO传输压力。主要的压缩有以下几种方式。
1.snappy
要采用snappy压缩的话，要编译Hadoop源码
1) 安装sanppy，解压即可（已经编译过）
2) 编译haodop 2.x源码
mvn package-Pdist,native -DskipTests -Dtar -Drequire.snappy
编译好之后会出现
/opt/modules/hadoop-2.5.0-src/target/hadoop-2.5.0/lib/native
3）进入到2.5.0-native-snappy，这是已经编译好的文件
4）将里面的文件全部拷贝到hadoop的lib/native下
5）检查一下是否支持压缩
$ bin/hadoop checknative
15/08/31 23:10:16 INFO bzip2.Bzip2Factory: Successfully loaded &initialized native-bzip2 library system-native
15/08/31 23:10:16 INFO zlib.ZlibFactory: Successfully loaded &initialized native-zlib library
Native library checking:
hadoop: true /opt/modules/hadoop-2.5.0/lib/native/libhadoop.so
zlib: true /lib64/libz.so.1
snappy: true /opt/modules/hadoop-2.5.0/lib/native/libsnappy.so.1不替换的话，是false
lz4: true revision:99
bzip2: true /lib64/libbz2.so.1
2.MapReduce数据压缩
6）进行测试，对比压缩前后
bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jarwordcount /user/beifeng/mapreduce/wordcount/input /user/beifeng/mapreduce/wordcount/output
bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jarwordcount -Dmapreduce.map.output.compress=true-Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec/user/beifeng/mapreduce/wordcount/input/user/beifeng/mapreduce/wordcount/output22进入8088端口，进入history的configuration，可以看到设置的参数。
3.hive数据压缩
在hive中设置也是如此：
set -Dmapreduce.map.output.compress=true
set -Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec七、文件存储的格式
hive有如下几种文件存储方式。常用的是ORC和PARQUET。
| SEQUENCEFILE序列化
|TEXTFILE -- (Default, depending on hive.default.fileformatconfiguration)
|RCFILE -- (Note: Available in Hive 0.6.0 andlater)
|ORC -- (Note: Available in Hive0.11.0 and later)
|PARQUET -- (Note: Available in Hive 0.13.0 and later) 列式存储
|AVRO -- (Note: Available in Hive0.14.0 and later)
|INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
1.textfile
create tablepage_views(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED ASTEXTFILE ;
load data localinpath '/opt/datas/page_views.data' into table page_views ;
dfs -du -h /user/hive/warehouse/page_views/ ;
18.1M /user/hive/warehouse/page_views/page_views.data2.orc
create tablepage_views_orc(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;
insertinto table page_views_orc select * from page_views ;企业中生成的方法
dfs -du -h/user/hive/warehouse/page_views_orc/ ;
2.6M /user/hive/warehouse/page_views_orc/000000_03.parquet
create tablepage_views_parquet(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET;
insert into tablepage_views_parquet select * from page_views ;
dfs -du -h/user/hive/warehouse/page_views_parquet/ ;
13.1M /user/hive/warehouse/page_views_parquet/000000_0
例子：采用snappy压缩
create tablepage_views_orc_snappy(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties("orc.compress"="SNAPPY");
insert into tablepage_views_orc_snappy select * from page_views ;
dfs -du -h/user/hive/warehouse/page_views_orc_snappy/ ;有3M多，是正常的，因为默认的bzip压缩是空间最小的八、UDF、UDAF、UDTF
UDF、UDAF、UDTF只能用java代码编写，根据具体的业务不同编写不同的代码，以下是UDF的例子，讲解怎么给hive配置自己编写的函数，具体的内容不展开。
1.配置下pom.xml
<!-- Hive Client -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>${hive.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>${hive.version}</version>
</dependency>2.配置函数
add jar /opt/datas/hiveudf.jar ;
create temporary function my_lower as "hive.udf.LowerUDF" ;
select ename, my_lower(ename) lowername from emp limit 5 ;或者
CREATE FUNCTION self_lower AS 'hive.udf.LowerUDF'USING JAR 'hdfs://master:8020/user/hive/jars/hiveudf.jar'; jar包必须在文件系统上
select ename, self_lower(ename) lowername from emp limit 5 ;九、Hive优化
hive本质是MapReduce，所以很多情况下，hive优化就是针对MapReduce进行。
1.Map的优化：
– 作业会通过input的目录产生一个或者多个map任务。
– Map越多越好吗？是不是保证每个map处理接近文件块的大小？事实上，map太多则会正价启动成本，map太少又会较小并行度，所以必须合理取值。
– 如何合并小文件，减少map数？
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
sethive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;– 如何适当的增加map数？
set mapred.map.tasks=10; 事实上，影响比较小– Map端聚合hive.map.aggr=true。Mr中的Combiners，提前将结果聚合。
2.Reduce的优化：
– hive.exec.reducers.bytes.per.reducer；reduce任务处理的数据量
reduce的个数= InputFileSize / bytesper reducer
– 调整reduce的个数：
• 设置reduce处理的数据量，设置多少就是多少
setmapred.reduce.tasks=10select pt,count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-04' group by pt;
写成如下，将会导致只有一个reduce执行
select count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-04';存在聚合计算的场景：建议开启group by，防止reduce任务退化成一个。
笛卡尔积：两个表做join时候，会出现笛卡尔积现象，此时用on替代where。join还有如下的语句优化。
• 一个MR job
SELECT a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (a.key = c.key1)
• 生成多个MR job
SELECT a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1)3.分区裁剪（partition）
Where中的分区条件，会提前生效，不必特意做子查询，直接Join和Group By。
4.map join
/*+ MAPJOIN(tablelist) */，必须是小表，不要超过1G，或者50万条记录，直接放在内存中，优化查询速度。
select /*+ MAPJOIN(A) */ B.userid, A.movieid, B.rating #指定A是小表，不特别指定的话，会按照顺序将第一个表放入内存中
from movie_table A
join rating_table B
on A.movieid == B.movieid
limit 10;5.Union all
union all 将两个表直接合并。先做union all再做join或group by等操作可以有效减少MR过程，尽管是多个Select，最终只有一个mr，数据没有去重，所以速度较快。
create table a as select * from movie_table limit 10;
create table b as select * from movie_table limit 10;
测试union all：
select count(1)
from (
select c.id
from(
select movieid as id from a
union all
select movieid as id from b
) c
group by c.id) d;
测试union：更耗时
select count(1)
from (
select movieid as id from a
union
select movieid as id from b) c;
6.Multi-insert & multi-group by
– 从一份基础表中按照不同的维度，一次组合出不同的数据
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1
INSERT OVERWRITE TABLE tablename2 [PARTITION(partcol2=val2 )] select_statement2 group by key27.Automatic merge
– 当文件大小比阈值小时，hive会启动一个mr进行合并
hive.merge.mapfiles= true 是否和并 Map 输出文件，默认为 True
hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False
hive.merge.size.per.task = 256*1000*1000 合并文件的大小8.Multi-Count Distinct
通常产生两个MR Job，在第一个JOB中，map的输出结果会随机分布到reduce中，每个reduce做部分的聚合操作，并输出结果，在第二个job中，对于上面预处理的结果按照group by key分布到reduce中，最终完成聚合操作。自动完成负载均衡，防止数据倾斜。
set hive.groupby.skewindata=true;9.表连接顺序
如果不特别指明，按照JOIN顺序中的最后一个表应该尽量是大表，因为JOIN前一阶段生成的数据会存在于Reducer的buffer中，通过stream最后面的表，直接从Reducer的buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是JOIN顺序中，前面表连接的结果的Key，数据量相对较小，内存开销就小），与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。
SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1)；
a表被视为大表SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a
JOIN b ON a.key = b.key;
MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多。使用hint的方式启发JOIN操作
左连接时，左表中出现的JOIN字段都保留，右表没有连接上的都为空。
SELECT a.val, b.val
FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'SELECT a.val, b.val
FROM a
LEFT OUTER JOIN b
ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')执行顺序是，首先完成2表JOIN，然后再通过WHERE条件进行过滤，这样在JOIN过程中可能会输出大量结果，再对这些结果进行过滤，比较耗时。可以进行优化，将WHERE条件放在ON后，在JOIN的过程中，就对不满足条件的记录进行了预先过滤。
10.并行实行
同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短。
set hive.exec.parallel=true11.数据倾斜
原因：key分布不均导致的，人为的建表疏忽，业务数据特点
症状：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
查看未完成的子任务，可以看到本地读写数据量积累非常大，通常超过10GB可以认定为发生数据倾斜。
倾斜度：平均记录数超过50w且最大记录数是超过平均记录数的4倍。最长时长比平均时长超过4分钟，且最大时长超过平均时长的2倍。
hive.groupby.skewindata=true #万能方法1）数据倾斜-大小表关联
Hive在进行join时，按照join的key进行分发，而在join左边的表的数据会首先读入内存，如果左边表的key相对分散，读入内存的数据会比较小，join任务执行会比较快；而如果左边的表key比较集中，而这张表的数据量很大，那么数据倾斜就会比较严重，而如果这张表是小表，则还是应该把这张表放在join左边。
方法：Small_table join big_table。这个可以通过指定大小表来完成。
2）数据倾斜-大大表关联（通过系统机制）
日志中有一部分的userid是空或者是0的情况，导致在用user_id进行hash分桶的时候，会将日志中userid为0或者空的数据分到一起，导致了过大的斜率。
思路：把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
方法：过滤语句写成如下形式。
on case when(x.uid = '-' or x.uid = '0‘ or x.uid is null) then concat('dp_hive_search',rand()) else x.uid end = f.user_id;
3）数据倾斜-大大表关联（业务削减）
Select * from dw_log t join dw_user t1 on t.user_id=t1.user_id #两个表都上千万，跑起来很悬
思路： 当天登陆的用户其实很少
方法：提前筛选出聚合key
Select /*+MAPJOIN(t12)*/ *
from dw_log t11
join (
select /*+MAPJOIN(t)*/ t1.*
from (
select user_id from dw_log group by user_id 取出用户id
) t
join dw_user t1
ont.user_id=t1.user_id
) t12 on t11.user_id=t12.user_id4）数据倾斜-聚合时存在大量特殊值
原因：做countdistinct时，该字段存在大量值为NULL或空的记录。
思路：count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union
• 方法
select cast(count(distinct (user_id))+1as bigint) as user_cnt
from tab_a
where user_id is not null and user_id<> ''5）数据倾斜-空间换时间
Select day,count(distinct session_id),count(distinct user_id) from log a group by day;
问题：同一个reduce上进行distinct操作时压力很大
方法：
select day,
count(case whentype='session' then 1 else null end) as session_cnt,
count(case whentype='user' then 1 else null end) as user_cnt
from (
selectday,session_id,type
from (
select day,session_id,'session'as type
from log
union all #空间换时间
select dayuser_id,'user' as type
from log
)
group by day,session_id,type
) t1
by d
3651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
