Hive 的简单使用及调优参考文档
-
HIVE的使用
命令行界面
使用一下命令查看hive的命令行页面，
hive --help --service cli 简化命令为hive –h
会输出下面的这些东西
-d,--define <key=value> Variable subsitution to apply to hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
-h <hostname> connecting to Hive Server on remote host
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-p <port> connecting to Hive Server on port number
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)
我们常用的 hive –e ，hive –f ，hive –S 和hive –h
演示下上面几个命令的使用示例
Shell页面执行hive命令
假如 希望使用shell脚本把hive库bdp_3 表里的mobile_01_2的表数据导出
hive -e "use bdp_3;select * from mobile_01_2;">11.log
就可以在执行hive命令的当前目录下看到11.log里面有表mobile_01_2的数据了。当然为了切换用户命令也可以省略写成下面的方式，直接在表名前加用户名
hive -e "select * from bdp_3.mobile_01_2;">12.log
Shell脚本中获得HQL统计结果
在shell脚本中获得HQL的统计结果 并赋给一个变量
count=`hive -e "select count(*) from bdp_3.mobile_01_2;"`
echo 'count的值为：'$count
shell执行封装好的HQL脚本文件
封装好的hql 命令放到单独的一个文件里，然后统一在shell 里执行，此时可以使用hive –f 命令
假设在 /root/shell/11.q 文件下有一句HQL
create table bdp_3.test as
select * from bdp_3.mobile_01_2;
执行命令
hive –f /root/shell/11.q
之后就能在数据库里看到这张创建的表了
-
有趣的几个Hive功能
Hive命令字段补齐
hive 命令字段补全功能，同linux一样，敲命令时候敲一般按TAB键系统会帮助补全关键字，有几个选择的时候会给提示
Hive 命令窗口使用 shell 命令
在hive CLI窗口 输入！ 加分号结尾就能使用shell 命令了，比如查看当前目录
显示当前目录文件
这个功能和真正的shell还是有写区别的，可以自己多试试
Hive 使用hadoop的dfs命令
只需要把hadoop 命令中的关键字hadoop 干掉，分号结尾就好了，比如，查看hdfs 根目录 hadoop 命令为 hadoop dfs –ls / 在hive中只需要敲dfs –ls /
hive 数据导出
hive 数据导出有三种常见的场景
（1）导出到本地文件系统；（2）导出到HDFS中；（3）导出到Hive的另一个表中。
导出到本地文件系统
insert overwrite local directory '/root/shell/mobile_01_1'
select * from mobile_log_01;
这条HQL的执行需要启用Mapreduce完成，运行完这条语句之后，将会在本地文件系统的/root/shell/mobile_01_1目录下生成文件，这个文件是Reduce产生的结果（这里生成的文件名是000000_0），我们可以看看这个文件的内容
注：上面使用的都是默认的导出格式，如果需要使用特定的分隔符导出可以使用下面的语句
insert overwrite local directory '/root/shell/mobile_01_3'
row format delimited
fields terminated by '$'
select * from bdp_3.mobile_01_1;
导出到hdfs中
insert overwrite directory '/root/shell/mobile_01_1'
select * from mobile_log_01;
导出本地和导出到hdfs的区别 就差一个local关键字
Hive 还可以使用hive –e 和hive –f 命令来导出数据如同我前面1.1.1一样，我这里就不在讲述
导出到Hive的另一个表中
这个同常见的sql命令一样，我就不在讲述
-
Hive数据库的一些常见操作
-
Hive数据库操作
创建数据库
-
hive> create database if not exists db_test;
OK
Time taken: 2.3 seconds
查看数据库
hive> show databases;
OK
bdp
bdp_3
db_test
default
Time taken: 0.526 seconds, Fetched: 4 row(s)
查看指定的数据库
hive> show databases like 'db*';
OK
db_test
Time taken: 0.075 seconds, Fetched: 1 row(s)
使用 use 命令切换不同的数据库
use bdp_3;
-
Hive表操作
Hive查看表
Hive建表我这里不多说了，比较基础，给几个查看表的语句
查看数据库下面的表
hive> show tables;
hive> show tables in bdp_3;
显示表的详细信息
hive> describe extended bdp_3.mobile_01_1;
查看表分区
hive>show partitions mobile_01_1;
建表
CREATE TABLE BOSS_05016_HD2
(
feild_1 STRING,
feild_2 STRING,
feild_3 STRING,
feild_4 STRING,
feild_5 STRING,
feild_6 STRING,
feild_7 STRING)
ROW format delimited
fields terminated by'\t' --指定制表符为列分隔符
lines terminated by'\n' --指定换行符为行分隔符(注：目前hive好像只支持换行符为行分隔符，默认也是这样，可以不指定)
;
删除表
hive>drop table if exists mobile_01_1;
重命名表
hive> alter table mobile_01_1 rename to mobile_01_3;
修改表
大多数表可以通过alter table 语句来修改 但是这只会修改表的元数据，表数据本身不会有任何修改
Hive 可以修改表名，表字段名，修改列，增加或者删除列，也可以通过alter table 来删除和添加分区表的分区
这里后续慢慢补充
-
Hive函数
Hive目前支撑的内部函数
可以使用下面命令来查看hive目前支撑的内部函数
hive> show functions;
OK
!
!=
%
&
*
+
-
/
<
<=
<=>
<>
=
==
>
>=
^
abs
acos
and
array
array_contains
ascii
asin
assert_true
atan
avg
base64
between
bin
case
ceil
ceiling
coalesce
collect_set
compute_stats
concat
concat_ws
context_ngrams
conv
corr
cos
count
covar_pop
covar_samp
create_union
cume_dist
date_add
date_sub
datediff
day
dayofmonth
decode
degrees
dense_rank
div
e
elt
encode
ewah_bitmap
ewah_bitmap_and
ewah_bitmap_empty
ewah_bitmap_or
exp
explode
field
find_in_set
first_value
floor
format_number
from_unixtime
from_utc_timestamp
get_json_object
hash
hex
histogram_numeric
hour
if
in
in_file
index
inline
instr
isnotnull
isnull
java_method
json_tuple
lag
last_value
lcase
lead
length
like
ln
locate
log
log10
log2
lower
lpad
ltrim
map
map_keys
map_values
max
min
minute
month
named_struct
negative
ngrams
noop
noopwithmap
not
ntile
nvl
or
parse_url
parse_url_tuple
percent_rank
percentile
percentile_approx
pi
pmod
positive
pow
power
printf
radians
rand
rank
reflect
reflect2
regexp
regexp_extract
regexp_replace
repeat
reverse
rlike
round
row_number
rpad
rtrim
second
sentences
sign
sin
size
sort_array
space
split
sqrt
stack
std
stddev
stddev_pop
stddev_samp
str_to_map
struct
substr
substring
sum
tan
to_date
to_unix_timestamp
to_utc_timestamp
translate
trim
ucase
unbase64
unhex
unix_timestamp
upper
var_pop
var_samp
variance
weekofyear
when
windowingtablefunction
xpath
xpath_boolean
xpath_double
xpath_float
xpath_int
xpath_long
xpath_number
xpath_short
xpath_string
year
|
~
Time taken: 0.106 seconds, Fetched: 191 row(s)
hive>
附件为hive函数大全
-
HIVE优化
JOIN 优化
Hive 支持通常的SQL JOIN 语句，但是只支持等值连接
通过连接顺序优化
分析一个语句
Select * from
table_1 a join table_2 b on a.serv_id = b.serv_id
join table_3 c on a.serv_id = c.serv_id
HIVE的解析执行顺序是按照从左到右，hive对每个join对象启动一个mapreduce任务，首先对 表a和表b进行连接操作 然后将输出结果和表c进行连接操作。
所以我们可以通过合理的指定连接表的顺序来优化语句，保证查询顺序中表的大小从左到右依次是增加的。Hive执行时假定查询中最后一个表是最大的表，在对每行记录进行连接操作时，它会先尝试将其他表缓存起来，然后扫描最后那个表进行计算。
标记大表
Select /*+STREAMTABLE(a) */ * from
table_1 a join table_2 b on a.serv_id = b.serv_id
join table_3 c on a.serv_id = c.serv_id;
通过这个标记来告诉hive 那张表是大表，hive在执行时会尝试将a表作为驱动表。
map-side JOIN
如果所有的表中有一张是小表，可以在最大的表通过mapper的时候将小表完全放到内存中，hive可以在map端执行连接过程，因为hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接所需要的reduce过程。
详细解读可以查看
http://tech.ddvip.com/2013-10/1381313795203738.html
hive v0.7版本以前 使用这个优化可以这么写语句
select /*MAPJOIN(d) */ s.ymd,s.symbol,d.dividend from
stocks s join dividends s on s.ymd=d.ymd;
hive v0.7版本以后，废弃这个标记方式，(增加这个标记同样有用)，需设置hive的属性，hive.auto.convert.JOIN的值为true,默认这个属性的值是false。
用户可以设置使用这个优化的小表的大小
hive.mapjoin.smalltable.filesize=25000000 （默认是25M）
注意:hive的right out join 和full out join 不支持这个优化
使用EXPLAIN 和EXPLAIN EXTENDED
学习hive是如何工作的，第一个步骤就是学习explain 功能，可以帮助我们学习hive是如何将查询
explain select * from boss_05002;
explain select count(1) from boss_05002;
hive> explain select count(1) from boss_05002;
OK
ABSTRACT SYNTAX TREE:
(TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME boss_05002))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION count 1)))))
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-1
Map Reduce
Alias -> Map Operator Tree:
boss_05002
TableScan
alias: boss_05002
Select Operator
Group By Operator
aggregations:
expr: count(1)
bucketGroup: false
mode: hash
outputColumnNames: _col0
Reduce Output Operator
sort order:
tag: -1
value expressions:
expr: _col0
type: bigint
Reduce Operator Tree:
Group By Operator
aggregations:
expr: count(VALUE._col0)
bucketGroup: false
mode: mergepartial
outputColumnNames: _col0
Select Operator
expressions:
expr: _col0
type: bigint
outputColumnNames: _col0
File Output Operator
compressed: false
GlobalTableId: 0
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Time taken: 0.314 seconds, Fetched: 52 row(s)
限制调整
Limit 语句是大家经常用到的，但是很多情况下limit语句还是需要执行这个查询语句，然后返回部分结果，因为这种情况通常是浪费的，所以应该尽可能避免出现这种情况
Hive 有个配置属性可以开启：
hive.limit.optimize.enable
当hive.limit.optimize.enable=true时，有两个对应的参数可以设置
hive.limit.optimize.limit.file和hive.limit.row.max.size
注意：这个功能有一个缺陷 ，有可能输入中有用的数据永远不会被处理到，任意一个需要reduce步骤的查询，join和group by操作 以及聚合函数的调用，都会产生不同的结果。
本地模式
大多数的hadoop job 是需要hadoop提供的可扩展性来处理大数据的，不过，有时hive的输入数据量是非常小的，在这种情况下，为查询触发执行任务的时间消耗可能会比实际job的执行时间要多的多，对于大多数这种情况，hive可以通过本地模式在单台机器上处理所有的任务，对于小数据集执行的时间可以明显缩短：
用户可以设置hive属性：hive.exec.mode.local.auto = true 来让hive 在适当的时候自动启动这个优化
并行执行
Hive 会将一个查询转化成一个或多个阶段，这样的阶段可以是mapreduce阶段，抽样阶段，合并阶段，limit阶段，或者hive执行过程中可能需要的其他阶段，默认情况下hive一次性只会执行一个阶段，不过某个特定的job可能包含众多阶段，而这些阶段可能并非完全互相依赖，也就是说这些阶段是可以并行的，这样可以缩短整个job的执行时间缩短。
可以通过设置hive参数 hive.exec.parallel=true 来开启并发执行。
严格模式
调整mapper和reduce 个数
Hive通过将查询划分成一个或多个mapreduce任务来达到并行的目的，每个任务都可能具有多个mapper和reducer任务，确定最佳的mapper个数和reducer 个数取决于多个变量，例如输入数据量大小以及对这些数据执行的操作类型等。
一般，hive是按照输入的数据量大小来确定reducer 个数的，我们可以通过dfs –count命令来计算输入量大小。可以通过属性 hive.exec.reducers.bytes.per.reducers.bytes.per.reducer来设置。默认值是1GB。Hive默认的reducer个数是3。可以通过设置属性mapred.reduce.tasks的值来设置。
在共享集群上处理大任务时，集群可以提供的map和reduce 资源个数是固定的，某个大job可能会消耗完所有的资源二导致其他job无法执行，可以通过设置hive.exec.reducers.max属性来限制某个查询消耗太多的reducer资源。
JVM重用
JVM重用是hadoop调优参数的内容，其对hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间很短。
Hadoop 的默认配置通常是使用派生JVM来执行map和reduce任务的，这是JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含成百上千个task任务的情况下JVM可以使的JVM实例在同一个job中重新使用N次。N的值可以在hadoop的mapred-site.xml文件中进行设置。
注意： 这个功能的缺点，开启JVM重用会一直占用使用到的task插槽，以便进行重用，直到任务完成后才释放，如果某个job中 有几个reduce task 执行的时间要比其他的reduce task 消耗的时间多的多的话，那么保留的插槽会一直空闲，直到所有task都结束了才释放。
索引
索引可以用来加快含有group 不要 语句的查询计算速度。Hive 从 v0.8.0开始增加了一个bitmap 索引实现
动态分区调整
推测执行
单个MapReduce中的 多个GROUP BY
HIVE 压缩
Hive不要求将数据转换成特定的格式才能使用。压缩通常会节约客观的磁盘空间。压缩同样可以增加吞吐量和性能。
-
hive中的编码器
查看hive支持的编译器
hive> set io.compression.codecs;
io.compression.codecs=org.apache.hadoop.io.compress.DefaultCodec,
org.apache.hadoop.io.compress.GzipCodec,
org.apache.hadoop.io.compress.BZip2Codec,
org.apache.hadoop.io.compress.DeflateCodec,
org.apache.hadoop.io.compress.SnappyCodec,
org.apache.hadoop.io.compress.Lz4Codec
hive>
选择编译器
BZip2压缩率最高，但是同时消耗 最多的cpu，GZip是压缩率和解压缩速度上的下一个选择。 在需要优先考虑磁盘空间利用率和I/O的情况下，可以优先考虑这两种压缩方式。
LZO和Snappy 压缩率比前面两种小，但是 压缩解压速度要快，特别是解压缩过程，如果相对于磁盘空间和I/O开销，频繁的读取数据所需的解压缩速度更重要的话，可以优先考虑这两种方式。
另一个重点考虑的因素是压缩文件是否可分割。
Mapreduce 需要将非常大的输入文件分割成多个划分(通常一个文件一个划分)，每个划分会被分发到单独的map进程中，文本文件每一行一条记录，hadoop知道文件的记录边界。GZip和Snappy 将这些边界信息掩盖了，BZip2和LZO提供了块级别的压缩，每个块含有完整的记录信息，hadoop可以在块级别对这些文件进行划分。
开启中间压缩
对中间数据进行压缩可以减少job中map和reduce task 间的数据传输。对于中间数据压缩，选择一个低cpu开销的编解码器比较合理，推荐使用Snappycodec。可以通过设置属性：hive.exec.compress.intermediate=true来开启中间件压缩。
-
HIVE 存在的一些其他需要注意的问题
管理表(内部表)和外部表问题
Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径， 不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。
Hive表的严格(strict)模式和非严格(nostrict)模式
Hive 有一个参数可以用来控制hive是否为严格模式hive.mapred.mod=strict，当处于严格模式下有很多操作是不允许的，比如，不加limit 的全表查询，表的笛卡尔积 join 操作，分区表不加分区过滤的查询 ，orderby不加limit的查询
可参考：http://blog.csdn.net/wisgood/article/details/19852553
Hive分区字段的使用
Hive的分区字段是用来划分文件目录的，本身不需要在数据文件中存在
假设stocks表 的分区字段为symbol 执行如下查询
Select count(distinct symbol) from stocks 会返回结果 0 这个是hive存在的bug
浮点数比较问题
假设 有张employees 表的taxes 字段是float 类型的，标识有两条数据的taxes 的值为0.2
此时使用查询
Select * from employees where taxes > 0.2;
你会惊奇的发现 这两条数据在列！！
为了避免这种情况发生应该使用下列语句
Select * from employees where taxes > cast(0.2 as float);