'''
set hive.cli.print.header=``true``;
set hive.``fetch``.task.conversion=more;
--打印列名
set hive.``fetch``.task.conversion=more; ``--開啟了Fetch任務(wù)，所以對于上述簡單的列查詢不在啟用
set hive.cli.print.row.``to``.vertical=``true``; ``--開啟行轉(zhuǎn)列功能, 前提必須開啟打印列名功能
set hive.cli.print.row.``to``.vertical.num=1; ``--設(shè)置每行顯示的列數(shù)
set row format delimited fields terminated ``by '\t'``;
set hive.execution.engine=tez; ``--設(shè)置hive執(zhí)行引擎
set mapred.reduces.tasks=2; ``--指定reduce的任務(wù)書
set hive.m.merge.mapfiles=``true``;``--合并小文件
set hive.optimize.cp=``true``; ``--列裁剪
set hive.optimize.prunner=``true``; ``--分區(qū)表裁剪
set hive.limit.optimeize.enable=``true``; ``--優(yōu)化Limit n語句
set hive.limit.row.``max``.``size``=1000000;
1417632547990
1417693025660
set hive.limit.optimize.limit.file=10; ``--最大文件數(shù)
--job的輸入數(shù)據(jù)大小必須小于參數(shù):
set hive.``exec``.mode.``local``.auto.inputbytes.``max``=134217728; ``--(默認(rèn)128M)
set hive.``exec``.mode.``local``.auto.tasks.``max``=4; ``--(默認(rèn)4)job的map數(shù)必須小于參數(shù):
set hive.``exec``.mode.``local``.auto=``true``; ``--(默認(rèn)為false)設(shè)置為本地模式，job的reduce數(shù)必須為0或者1
set hive.mapred.``local``.mem=89; ``--本地模式啟動(dòng)的JVM內(nèi)存大小
set hive.``exec``.parallel=``true``;``--默認(rèn)為false; --并發(fā)執(zhí)行:
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;
--開啟動(dòng)態(tài)分區(qū)特性
set hive.``exec``.``dynamic``.partition=``true``;
set hive.``exec``.``dynamic``.partition.m.mode=nonstrict;
set hive.``exec``.``dynamic``.partitions.pernode=20; ``--參數(shù)指的是每個(gè)節(jié)點(diǎn)上能夠生成的最大分區(qū)。這個(gè)在最壞情況下應(yīng)該是跟最大分區(qū)一樣的值
set hive.``exec``.``dynamic``.partitions.partitions=20; ``--指定總共的最大的動(dòng)態(tài)分區(qū)數(shù)
set mapred.reduces.tasks=2; ``--指定reduce的任務(wù)書
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``; ``--查看表結(jié)構(gòu)信息
desc formatted ``table``;
show ``table like '*name*'``; ``--模糊搜索表
show partition table_name; ``--參看表分區(qū)信息
select table_column ``from table_name ``where partition_name=``'2014-01-25'``; ``--根據(jù)分區(qū)查詢數(shù)據(jù)
alter table tmp_h02_click_log_baitiao ``drop partition(dt=``'2014-03-01'``); ``--刪除分區(qū)
LOAD DATA ``LOCAL INPATH ``'dim_csl_rule_config.txt' OVERWRITE ``INTO TABLE dim.dim_csl_rule_config; ``--從文件加載數(shù)據(jù)僅表(OVERWRITE覆蓋,追加不需要OVERWRITE關(guān)鍵字)
INSERT OVERWRITE ``TABLE test_h02_click_log PARTITION(dt) ``--從查詢語句給table插入數(shù)據(jù)
select * ``from stage.s_h02_click_log ``where dt=``'2014-01-25' LIMIT 1000;
--導(dǎo)出數(shù)據(jù)到文件
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函數(shù)
1.繼承UDF類
2.重寫evaluate方法
3.把項(xiàng)目打成jar包
4.hive中執(zhí)行命令``add jar /home/jrjt/dwetl/PUB/UDF/udf/GetProperty.jar
5.創(chuàng)建函數(shù)``create temporary function get_pro ``as 'jd.Get_Property' //jd.Get_Property為類路徑
--查詢顯示列明及行轉(zhuǎn)列顯示
set hive.cli.print.header=``true``;//打印列名
set hive.cli.print.row.``to``.vertical=``true``; //開啟行裝列功能，前提必須開啟打印列名功能
set hive.cli.print.row.``to``.vertical.num=1;//設(shè)置每行顯示的列數(shù)
dfs -ls /``user``/hive/warehouse/table02; ``--查看hdfs文件信息
dfs -du hdfs://namenode.jd.com:54310/``user``/jrjt/warehouse/stage.db/s_h02_click_log; ``--查看某個(gè)任務(wù)，不在hive shell中執(zhí)行
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命令移動(dòng)表數(shù)據(jù)到另外一張表目錄下并添加分區(qū)
/``user``/jrjt/warehouse/ods.db/o_h02_click_log/;
hadoop job -kill job_201403041453_58315 ``--殺死某個(gè)任務(wù)，不在hive shell中執(zhí)行
[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 命令行操作
--執(zhí)行一個(gè)查詢，在終端上顯示mapreduce的進(jìn)度，執(zhí)行完畢后，最后把查詢結(jié)果輸出到終端上,接著hive進(jìn)程退出
--不會(huì)進(jìn)入交互模式
hive -e ``'select table_column from table'
hive -S ``'...' --終端上的輸出不會(huì)有mapreduce的進(jìn)度，執(zhí)行完畢，只會(huì)把查詢結(jié)果輸出到終端上。這個(gè)靜音模式很實(shí)用
hive -S -e ``'select table_column from table' --用于第三方程序調(diào)用，第三方程序調(diào)用，第三方程序通過hive的標(biāo)準(zhǔn)輸出獲取結(jié)果集
hive -f hive_sql.sql ``--執(zhí)行sql文件
-------------------------------------------------------------------------------------------
hive上操作hadoop文件基本命令
--插入數(shù)據(jù)sql、導(dǎo)出數(shù)據(jù)sql
insert``語法格式為：
--基本的插入語句
INSERT OVERWRITE ``TABLE table_name [PARTITION(partcol1=var1,partcol2=val2)]
select * ``from table``;
--對多個(gè)表進(jìn)行插入操作
FROM test_table
insert overwrite ``table test_insert1 ``select key
insert overwrite ``table test_insert2 ``select value;
/*
--insert的時(shí)候,from子句即可放在select 子句后面，也可以放在insert 子句前面。
--hive 不支持用insert語句一條一條的進(jìn)行插入操作，也不支持update操作。
--數(shù)據(jù)是以load的方式加載到建立好的表中。數(shù)據(jù)一旦導(dǎo)入就不可以修改
*/
INSERT OVERWRITE [``LOCAL``] DIRECTORY directory ``SELECT ... ``FROM ... ``--通過查詢將數(shù)據(jù)保存到filesystem
insert overwrite directory ``'/user/zhangxin/export_test' select value ``from test_table; ``--導(dǎo)入數(shù)據(jù)到本地目錄
--同一個(gè)查詢結(jié)果可以同時(shí)插入到多個(gè)表或者多個(gè)目錄中：
from test_insert1
insert overwrite ``local directory ``'/home/zhangxin/hive' select * ;
--mapjoin的使用應(yīng)用場景：
1.關(guān)聯(lián)操作中有一張表非常小
2.不等值的連接操作
select /*+mapjoin(A)*/ f.a,f.b ``from A t ``join B f ``on``(f.a=t.ftime=20110802)
---------------------------------------------------------------------------
--perl 啟動(dòng)任務(wù)
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進(jìn)程
ps -ef|grep perl
--導(dǎo)出白條數(shù)據(jù)
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 ``'頁面停留時(shí)長'``);
--hive開啟簡單模式不啟用mr
set hive.``fetch``.task.conversion=more;
--以json格式輸出執(zhí)行語句回讀取的input table和input partition信息
explain dependency query;
explain dependency query;
--壓縮文件(對map輸出進(jìn)行壓縮，可以減少數(shù)據(jù)傳輸量)
set mapred.compress.map.``output``=``true``;
---------------------------------------------------------------------------
--Hive優(yōu)化
hive.optimize.cp=``true``; ``--列裁剪
hive.optimize.prunner=``true``; ``--分區(qū)表裁剪
hive.limit.optimeize.enable=``true``; ``--優(yōu)化Limit n語句
hive.limit.row.``max``.``size``=1000000;
hive.limit.optimize.limit.file=10;``--最大文件數(shù)
--一、本地模式(小任務(wù))
--需要滿足一下條件
hive.``exec``.mode.``local``.auto.inputbytes.``max``=134217728;(默認(rèn)128M) ``--job的輸入數(shù)據(jù)大小必須小于參數(shù):
hive.``exec``.mode.``local``.auto.tasks.``max``=4;(默認(rèn)4) ``--job的map數(shù)必須小于參數(shù):
hive.``exec``.mode.``local``.auto=``true``; ``--job的reduce數(shù)必須為0或者1
hive.mapred.``local``.mem=89; ``--本地模式啟動(dòng)的JVM內(nèi)存大小
--二、并發(fā)執(zhí)行:
hive.``exec``.parallel=``true``;``--默認(rèn)為false;
hive.``exec``.parallel.thread.number=8;
--三、Strict Mode;(嚴(yán)格模式)
hive.mapred.mode=``true``;
--嚴(yán)格模式下不允許執(zhí)行一下查詢：
--分區(qū)表上沒有指定分區(qū)
--沒有l(wèi)imit限制的order by語句
--笛卡爾積:join時(shí)沒有on語句
--四、動(dòng)態(tài)分區(qū)
hive.``exec``.``dynamic``.partition.mode=strict; ``--該模式下必須指定一個(gè)靜態(tài)分區(qū)
hive.``exec``.``max``.``dynamic``.partitions=1000;
hive.``exec``.``max``.``dynamic``.paratitions.pernode=100; ``--在每個(gè)mapper/reducer節(jié)點(diǎn)允許創(chuàng)建的最大分區(qū)數(shù)
dfs.datanode.``max``.xceivers=8192; ``--允許datanode打開多少個(gè)文件
--五、推測執(zhí)行
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``;``--當(dāng)多個(gè)Group By語句有相同的分組列，則會(huì)優(yōu)化為一個(gè)MR任務(wù)
--七、
hive.``exec``.rowoffset;``--是否提供虛擬行
--八、分組
--兩個(gè)聚集函數(shù)不能有不同的distinct列，以下表達(dá)式是錯(cuò)誤的:(select語句中只能有g(shù)roup by的列或者聚集函數(shù))
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中會(huì)做部分聚焦操作，效率更高但需要更多的內(nèi)存。
hive.groupby.mapaggr.checkinterval;``--在Map端進(jìn)行聚合操作的條數(shù)數(shù)目
--十
hive.groupby.skewindata=``true``;``--數(shù)據(jù)傾斜是負(fù)載均衡，當(dāng)選項(xiàng)設(shè)定為true，生成的查詢計(jì)劃會(huì)有兩個(gè)
--MRJob。第一個(gè)MRJob中，Map的輸出結(jié)果集合會(huì)隨時(shí)分布到Reduce中，每個(gè)Reduce做部分聚合操作，并輸出結(jié)果，
--這樣處理的結(jié)果是相同的group by
--有可能被分發(fā)到不同的reduce中，從而達(dá)到負(fù)載均衡的目的;第二個(gè)MRJob再根據(jù)預(yù)處理的
--數(shù)據(jù)結(jié)果按照Group By key分布到Reduce中(這個(gè)過程可以保證相同的GroupBy key被分布到同一個(gè)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 時(shí)需要跟limit子句
hive.mapred.mode=nonstrict;``--nonstrict時(shí)使用單個(gè)reduce完成排序
Sort ``by colname ``ASC``/``DESC``;``--每個(gè)reduce內(nèi)排序
distribute ``by``(子查詢情況下使用):控制特定行應(yīng)該到哪個(gè)reducer,并不保證reduce內(nèi)數(shù)據(jù)順序
cluster ``by``:當(dāng)Sort ``By``、Distribute ``by``使用相同的列時(shí)。
--十三、合并小文件
hive.merg.mapfiles=``true``;``--合并map輸出
hive.merge.mapredfiles=``false``;``--合并reduce輸出
hive.merge.``size``.per.task=256*1000*1000;``--合并文件的大小
hive.mergejob.maponly=``true``;``--如果支持CombineHiveInputFormat則生成只有Map的任務(wù)執(zhí)行merge
hive.merge.smallfiles.avgsize=16000000; ``--文件的平均大小小于該值時(shí)，會(huì)啟動(dòng)一個(gè)MR任務(wù)執(zhí)行merge。
--十四、map/reduce數(shù)目
--減少map數(shù)目
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數(shù)目
--當(dāng)input的文件都很大，任務(wù)邏輯復(fù)雜，map執(zhí)行非常慢的時(shí)候，可以考慮增加Map數(shù)，來時(shí)的每個(gè)map處理的數(shù)據(jù)量減少，從而提高任務(wù)的執(zhí)行效率。
--假設(shè)有這樣一個(gè)任務(wù)
select data_desc,``count``(1),``count``(``distinct id),``sum``(``case when ...),``sum``(``case when ..),``sum``(...)
from a ``group by data_desc;
--如果表a只有一個(gè)文件、大小為120M，但包含幾千萬的記錄，如果用1個(gè)map區(qū)完成這個(gè)任務(wù)，肯定是比較耗時(shí)的，這種情況下，我們要考慮將這個(gè)文件合理的拆分
--成多個(gè)，這樣就可以用多個(gè)map任務(wù)去完成。
set mapred.reduce.tasks=10;
create table a_1 ``as select * ``from a distribte ``by rand(123);
--這樣會(huì)將表a表的記錄，隨機(jī)的分散到包含10個(gè)文件a_1表中，在用a_1代替上面SQL中的a表，
--則會(huì)用10個(gè) map任務(wù)去完成。每個(gè)map任務(wù)處理大于12M(幾百萬記錄)的數(shù)據(jù),效率肯定會(huì)好很多
reduce數(shù)目設(shè)置:
參數(shù)1：
hive.``exec``.reducers.bytes.per.reducer=1G;``--每個(gè)reduce任務(wù)的處理量
參數(shù)2：
hive.``exec``.reducers.``max``=999(0.95*TaskTracker數(shù));``--每個(gè)任務(wù)最大的reduce數(shù)目
reducer數(shù)=``min``(參數(shù)2，總輸入量/參數(shù)1)
set mapred.reduce.tasks:每個(gè)任務(wù)默認(rèn)的reduce數(shù)目。典型為0.99*reduce槽數(shù)，hive將其設(shè)置為-1，自動(dòng)確定reduce數(shù)目。
--15.使用索引
hive.optimize.``index``.filter:自動(dòng)使用索引
hive.optimize.groupby:使用聚合索引優(yōu)化groupby操作
'''
