调参指南

'''
set hive.cli.print.header=``true``;

set hive.``fetch``.task.conversion=more;

--打印列名

set hive.``fetch``.task.conversion=more; ``--开启了Fetch任务，所以对于上述简单的列查询不在启用

set hive.cli.print.row.``to``.vertical=``true``; ``--开启行转列功能, 前提必须开启打印列名功能

set hive.cli.print.row.``to``.vertical.num=1; ``--设置每行显示的列数

set row format delimited fields terminated ``by '\t'``;

set hive.execution.engine=tez; ``--设置hive执行引擎

set mapred.reduces.tasks=2; ``--指定reduce的任务书

set hive.m.merge.mapfiles=``true``;``--合并小文件

set hive.optimize.cp=``true``; ``--列裁剪

set hive.optimize.prunner=``true``; ``--分区表裁剪

set hive.limit.optimeize.enable=``true``; ``--优化Limit n语句

set hive.limit.row.``max``.``size``=1000000;

1417632547990

1417693025660

set hive.limit.optimize.limit.file=10; ``--最大文件数

--job的输入数据大小必须小于参数:

set hive.``exec``.mode.``local``.auto.inputbytes.``max``=134217728; ``--(默认128M)

set hive.``exec``.mode.``local``.auto.tasks.``max``=4; ``--(默认4)job的map数必须小于参数:

set hive.``exec``.mode.``local``.auto=``true``; ``--(默认为false)设置为本地模式，job的reduce数必须为0或者1

set hive.mapred.``local``.mem=89; ``--本地模式启动的JVM内存大小

set hive.``exec``.parallel=``true``;``--默认为false; --并发执行:

set hive.``exec``.parallel.thread.number=8;

set hive.``exec``.mode.``local``.auto=``true``;

清空表

insert overwrite ``table t_table1 ``select * ``from t_table1 ``where 1=0;

create table sample ple_dis_02 ``like sample_dis_01;

--开启动态分区特性

set hive.``exec``.``dynamic``.partition=``true``;

set hive.``exec``.``dynamic``.partition.m.mode=nonstrict;

set hive.``exec``.``dynamic``.partitions.pernode=20; ``--参数指的是每个节点上能够生成的最大分区。这个在最坏情况下应该是跟最大分区一样的值

set hive.``exec``.``dynamic``.partitions.partitions=20; ``--指定总共的最大的动态分区数

set mapred.reduces.tasks=2; ``--指定reduce的任务书

set hive.m.merge.mapfiles=``true``; ``--合并小文件

--这里注意要把select语句中要把distribute的key也select出来

insert overwrite ``table sample_dis_04 partition(prov)

select user_id,

name``,

prov_desc,

prov_desc ``as prov_desc_dis

from sample_dis_01

distribute ``by prov_desc;

------------------------------------------------------------------

desc table``; ``--查看表结构信息

desc formatted ``table``;

show ``table like '*name*'``; ``--模糊搜索表

show partition table_name; ``--参看表分区信息

select table_column ``from table_name ``where partition_name=``'2014-01-25'``; ``--根据分区查询数据

alter table tmp_h02_click_log_baitiao ``drop partition(dt=``'2014-03-01'``); ``--删除分区

LOAD DATA ``LOCAL INPATH ``'dim_csl_rule_config.txt' OVERWRITE ``INTO TABLE dim.dim_csl_rule_config; ``--从文件加载数据仅表(OVERWRITE覆盖,追加不需要OVERWRITE关键字)

INSERT OVERWRITE ``TABLE test_h02_click_log PARTITION(dt) ``--从查询语句给table插入数据

select * ``from stage.s_h02_click_log ``where dt=``'2014-01-25' LIMIT 1000;

--导出数据到文件

INSERT OVERWRITE DISECTORY ``'/tmp/csl_rule_cfg' SELECT a.* ``from dim.dim_csl_rule_config a;

hive -e ``"select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type

from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id in('2014-03-06','2014-03-07');"

>/home/jrjt/test/testan/baitiao.dat

--自定义UDF函数

1.继承UDF类

2.重写evaluate方法

3.把项目打成jar包

4.hive中执行命令``add jar /home/jrjt/dwetl/PUB/UDF/udf/GetProperty.jar

5.创建函数``create temporary function get_pro ``as 'jd.Get_Property' //jd.Get_Property为类路径

--查询显示列明及行转列显示

set hive.cli.print.header=``true``;//打印列名

set hive.cli.print.row.``to``.vertical=``true``; //开启行装列功能，前提必须开启打印列名功能

set hive.cli.print.row.``to``.vertical.num=1;//设置每行显示的列数

dfs -ls /``user``/hive/warehouse/table02; ``--查看hdfs文件信息

dfs -du hdfs://namenode.jd.com:54310/``user``/jrjt/warehouse/stage.db/s_h02_click_log; ``--查看某个任务，不在hive shell中执行

dfs -get /``user``/jrjt/warehouse/ods.db/o_h02_click_log_i_new/dt=2014-01-21/000212_0/home/jrjt/testan/;

head -n 1000 文件名>文件名

dfs -du /``user``/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15; ``--查看文件大小

dfs -rm /``user``/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15; ``--删除文件

dfs -cp /``user``/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-08 ``--hive命令移动表数据到另外一张表目录下并添加分区

/``user``/jrjt/warehouse/ods.db/o_h02_click_log/;

hadoop job -kill job_201403041453_58315 ``--杀死某个任务，不在hive shell中执行

[http://172.17.41.38/jobtracker.jsp ](http://172.17.41.38/jobtracker.jsp%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0) ``--hive-wui路径

-----------------------------------------------------------------------------------

--hive 命令行操作

--执行一个查询，在终端上显示mapreduce的进度，执行完毕后，最后把查询结果输出到终端上,接着hive进程退出

--不会进入交互模式

hive -e ``'select table_column from table'

hive -S ``'...' --终端上的输出不会有mapreduce的进度，执行完毕，只会把查询结果输出到终端上。这个静音模式很实用

hive -S -e ``'select table_column from table' --用于第三方程序调用，第三方程序调用，第三方程序通过hive的标准输出获取结果集

hive -f hive_sql.sql ``--执行sql文件

-------------------------------------------------------------------------------------------

hive上操作hadoop文件基本命令

--插入数据sql、导出数据sql

insert``语法格式为：

--基本的插入语句

INSERT OVERWRITE ``TABLE table_name [PARTITION(partcol1=var1,partcol2=val2)]

select * ``from table``;

--对多个表进行插入操作

FROM test_table

insert overwrite ``table test_insert1 ``select key

insert overwrite ``table test_insert2 ``select value;

/*

--insert的时候,from子句即可放在select 子句后面，也可以放在insert 子句前面。

--hive 不支持用insert语句一条一条的进行插入操作，也不支持update操作。

--数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改

*/

INSERT OVERWRITE [``LOCAL``] DIRECTORY directory ``SELECT ... ``FROM ... ``--通过查询将数据保存到filesystem

insert overwrite directory ``'/user/zhangxin/export_test' select value ``from test_table; ``--导入数据到本地目录

--同一个查询结果可以同时插入到多个表或者多个目录中：

from test_insert1

insert overwrite ``local directory ``'/home/zhangxin/hive' select * ;

--mapjoin的使用应用场景：

1.关联操作中有一张表非常小

2.不等值的连接操作

select /*+mapjoin(A)*/ f.a,f.b ``from A t ``join B f ``on``(f.a=t.ftime=20110802)

---------------------------------------------------------------------------

--perl 启动任务

perl /home/jrjt/dwetl/APP/APP/A_H02_CLICK_LOG_CREDIT_USER/bin/a_h02_click_log_credit_user.pl

APP_A_H02_CLICK_LOG_CREDIT_USER_20140215.dir >

& /home/jrjt/dwetl/LOG/APP/20140306/a_h02_click_log_credit_user.pl.4.log

--查看perl进程

ps -ef|grep perl

--导出白条数据

hive -e ``"select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type

from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id like '2014-03%';" >

/home/jrjt/testan/baitiao.xlsx

--hive添加字段

alter table tmp_h02_click_log_baitiao_ag_sum ``add columns(current_session_timelength_count ``bigint comment ``'页面停留时长'``);

--hive开启简单模式不启用mr

set hive.``fetch``.task.conversion=more;

--以json格式输出执行语句回读取的input table和input partition信息

explain dependency query;

explain dependency query;

--压缩文件(对map输出进行压缩，可以减少数据传输量)

set mapred.compress.map.``output``=``true``;

---------------------------------------------------------------------------

--Hive优化

hive.optimize.cp=``true``; ``--列裁剪

hive.optimize.prunner=``true``; ``--分区表裁剪

hive.limit.optimeize.enable=``true``; ``--优化Limit n语句

hive.limit.row.``max``.``size``=1000000;

hive.limit.optimize.limit.file=10;``--最大文件数

--一、本地模式(小任务)

--需要满足一下条件

hive.``exec``.mode.``local``.auto.inputbytes.``max``=134217728;(默认128M) ``--job的输入数据大小必须小于参数:

hive.``exec``.mode.``local``.auto.tasks.``max``=4;(默认4) ``--job的map数必须小于参数:

hive.``exec``.mode.``local``.auto=``true``; ``--job的reduce数必须为0或者1

hive.mapred.``local``.mem=89; ``--本地模式启动的JVM内存大小

--二、并发执行:

hive.``exec``.parallel=``true``;``--默认为false;

hive.``exec``.parallel.thread.number=8;

--三、Strict Mode;(严格模式)

hive.mapred.mode=``true``;

--严格模式下不允许执行一下查询：

--分区表上没有指定分区

--没有limit限制的order by语句

--笛卡尔积:join时没有on语句

--四、动态分区

hive.``exec``.``dynamic``.partition.mode=strict; ``--该模式下必须指定一个静态分区

hive.``exec``.``max``.``dynamic``.partitions=1000;

hive.``exec``.``max``.``dynamic``.paratitions.pernode=100; ``--在每个mapper/reducer节点允许创建的最大分区数

dfs.datanode.``max``.xceivers=8192; ``--允许datanode打开多少个文件

--五、推测执行

mapred.map.tasks.sepeculative.execution=``true``;

mapred.reduce.tasks.speculative.execution=``true``;

hive.mapred.reduce.tasks.speculative.execution=``true``;

--六、Single MapReduce MultiGroup By

hive.multigroupby.singlemar=``true``;``--当多个Group By语句有相同的分组列，则会优化为一个MR任务

--七、

hive.``exec``.rowoffset;``--是否提供虚拟行

--八、分组

--两个聚集函数不能有不同的distinct列，以下表达式是错误的:(select语句中只能有group by的列或者聚集函数)

insert overwrite ``table pv_gender_agg ``select pv_users.gender,``count``(``distinct pv_users.user_id),``count``(``distinct pv_users.ip)

from pv_users ``group by pv_users.gender;

--九

hive.map.aggr=``true``; ``--在map中会做部分聚焦操作，效率更高但需要更多的内存。

hive.groupby.mapaggr.checkinterval;``--在Map端进行聚合操作的条数数目

--十

hive.groupby.skewindata=``true``;``--数据倾斜是负载均衡，当选项设定为true，生成的查询计划会有两个

--MRJob。第一个MRJob中，Map的输出结果集合会随时分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，

--这样处理的结果是相同的group by

--有可能被分发到不同的reduce中，从而达到负载均衡的目的;第二个MRJob再根据预处理的

--数据结果按照Group By key分布到Reduce中(这个过程可以保证相同的GroupBy key被分布到同一个Reduce中)

--最后完成最终的聚合操作

--Multi-Group-By INSERT

FROM test

INSERT OVERWRITE ``TABLE count1

SELECT count``(``distinct test.dqcode)

Group by test.zipcode

INSERT OVERWRITE ``TABLE count2

SELECT count``(``distinct test.dqcode)

group by test.sfcode;

--十二、排序

Order By colName ``ASC``/``DESC

hive.mapred.mode=strict;``--strict 时需要跟limit子句

hive.mapred.mode=nonstrict;``--nonstrict时使用单个reduce完成排序

Sort ``by colname ``ASC``/``DESC``;``--每个reduce内排序

distribute ``by``(子查询情况下使用):控制特定行应该到哪个reducer,并不保证reduce内数据顺序

cluster ``by``:当Sort ``By``、Distribute ``by``使用相同的列时。

--十三、合并小文件

hive.merg.mapfiles=``true``;``--合并map输出

hive.merge.mapredfiles=``false``;``--合并reduce输出

hive.merge.``size``.per.task=256*1000*1000;``--合并文件的大小

hive.mergejob.maponly=``true``;``--如果支持CombineHiveInputFormat则生成只有Map的任务执行merge

hive.merge.smallfiles.avgsize=16000000; ``--文件的平均大小小于该值时，会启动一个MR任务执行merge。

--十四、map/reduce数目

--减少map数目

set mapred.``max``.split.``size

set mapred.``min``.split.``size

set mapred.``min``.split.``size``.per.node;

set mapred.``min``.split.``size``.per.rack;

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

--增加map数目

--当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来时的每个map处理的数据量减少，从而提高任务的执行效率。

--假设有这样一个任务

select data_desc,``count``(1),``count``(``distinct id),``sum``(``case when ...),``sum``(``case when ..),``sum``(...)

from a ``group by data_desc;

--如果表a只有一个文件、大小为120M，但包含几千万的记录，如果用1个map区完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这个文件合理的拆分

--成多个，这样就可以用多个map任务去完成。

set mapred.reduce.tasks=10;

create table a_1 ``as select * ``from a distribte ``by rand(123);

--这样会将表a表的记录，随机的分散到包含10个文件a_1表中，在用a_1代替上面SQL中的a表，

--则会用10个 map任务去完成。每个map任务处理大于12M(几百万记录)的数据,效率肯定会好很多

reduce数目设置:

参数1：

hive.``exec``.reducers.bytes.per.reducer=1G;``--每个reduce任务的处理量

参数2：

hive.``exec``.reducers.``max``=999(0.95*TaskTracker数);``--每个任务最大的reduce数目

reducer数=``min``(参数2，总输入量/参数1)

set mapred.reduce.tasks:每个任务默认的reduce数目。典型为0.99*reduce槽数，hive将其设置为-1，自动确定reduce数目。

--15.使用索引

hive.optimize.``index``.filter:自动使用索引

hive.optimize.groupby:使用聚合索引优化groupby操作

'''