skills_hive
目录
skills_hive
1 hive常用配置
<!-- 是否以本地模式启动，默认false -->
<!-- 作用：hive查询时使用本地查询，不走集群，加快少量数据的查询速度 -->
<!-- 临时开启 set hive.exec.mode.local.auto=true -->
<property>
<name>hive.exec.mode.local.auto</name>
<value>false</value>
<description>
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。
不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间
可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。
对于小数据集，执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当时候自动启动这个优化。
</description>
</property>
<!-- 设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M -->
<property>
<name>hive.exec.mode.local.auto.inputbytes.max</name>
<value>134217728</value>
</property>
<!-- 设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4 -->
<property>
<name>hive.exec.mode.local.auto.input.files.max</name>
<value>4</value>
</property>
<!-- 让提示符显示当前库，默认false -->
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<!-- 查询结果中header显示字段名称，默认false -->
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<!-- 查询结果中header中显示表名称，默认true -->
</property>
<name>hive.resultset.use.unique.column.names</name>
<value>false</value>
</property>
<!-- 每个节点可以创建的最大分区数 默认值:100 -->
<property>
<name>hive.exec.max.dynamic.partitions.pernode</name>
<value>100</value>
</property>
<!-- 每个mr可以创建的最大分区数 默认值:1000 -->
<property>
<name>hive.exec.max.dynamic.partitions</name>
<value>10000</value>
</property>
<!-- 让可以不走mapreduce任务的，就不走mapreduce任务，默认more -->
<property>
<name>hive.fetch.task.conversion</name>
<value>more</value>
</property>
<!-- 开启任务并行执行，默认false -->
<property>
<name>hive.exec.parallel</name>
<value>true</value>
<description>
当一个sql中有多个job时候，且这多个job之间没有依赖，
则可以让顺序执行变为并行执行（一般为用到union all的时候）
</description>
</property>
用MapJoin把小表全部加载到内存在map端进行join,避免reducer处理
set hive.auto.convert.join = true;
大表小表的阈值设置(默认25M以下认为是小表)
set hive.mapjoin.smalltable.filesize=25123456;
是否在Map端进行聚合,默认为True
set hive.map.aggr = true;
在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
有数据倾斜的时候进行负载均衡(默认是false)
set hive.groupby.skewindata = true;
设置严格模式，默认非严格nonstrict
set hive.mapred.mode = strict; #开启严格模式
set hive.mapred.mode = nostrict; #开启非严格模式，默认值
设置JVM重用
set mapred.job.reuse.jvm.num.tasks=10;
开启推测执行（如果用户因为输入数据量很大而需要执行长时间的map或者Reduce task的话,那么启动推测执行造成的浪费是非常巨大大）
set mapred.map.tasks.speculative.execution=true;
set mapred.reduce.tasks.speculative.execution=true;
set hive.mapred.reduce.tasks.speculative.execution=true;
reference：https://blog.csdn.net/Python_Billy/article/details/115269401
2 hive交互方式，beeline使用方式
使用beeline的原因：
beeline的输出结果更加规整，并且结果列之间有分隔虚线，如下所示：
0: jdbc:hive2://localhost:10000/db1> select * from t_msg;
+-----+--------+----------------------------+----------+--+
| id | name | location | city |
+-----+--------+----------------------------+----------+--+
| 1 | allen | ["usa","china"] | [1,3,7] |
| 2 | kobe | ["usa","england"] | [2,3,5] |
+-----+--------+----------------------------+----------+--+
hive交互方式： reference：https://www.cnblogs.com/nacyswiss/p/12605866.html
beeline使用方式： reference: https://www.cnblogs.com/lenmom/p/11218807.html
http://www.itcast.cn/news/20190829/12032894477.shtml
beeline连接hiveserver2时,服务启动顺序如下:
1. MySQL服务,因为hive的元素是存放在MySQL中的,默认是derby.(MySQL的远程链接必须授权)
2. metastore服务,看hive-site.xml是否配置,或是否配置忽略,比如在整合impala的时候是需要指定的.
不过仅仅是使用beeline的话，不需要启动metastore服务！
hive --service metastore 或 nohup hive --service metastore 2>&1 &
3. hiveserver2服务
hive --service hiveserver2 或 nohup hive --service hiveserver2 2>&1 &
4. 启动beeline客户端
beeline -n username -p password -u jdbc:hive2://127.0.0.1:10000/databaseName
如果hive.server2.authentication设置为NONE，则用户名和密码参数不用写
3 pv,uv,vv,ip概念
3.1 网站浏览量（PV）
是PageView的英文缩写，指页面的浏览次数，用以衡量用户访问的网页数量。用户每打开一个页面便记录1次PV，多次打开同一页面则浏览量累计；例如我们在论坛帖子或文章头部经常看到的“阅读次数”或者“浏览次数”。
3.2 独立访客（UV）
UV是Unique Visitor的英文缩写，1天内相同的访客多次访问您的网站只计算1个UV，以cookie为依据。所以我们当然系统UV越高越好，这样就说明有很多不同的访客访问网站，网站流量就增加了。
3.3 独立IP
独立IP指1天内使用不同IP地址的用户访问网站的数量。同一IP无论访问了网站里的多少个页面，独立IP数均为1。
3.4 访问次数（VV）
VV是Visit View的英文缩写，指从访客来到您网站到最终关闭网站的所有页面离开，计为1次访问。若访客连续30分钟没有新开和刷新页面，或者访客关闭了浏览器，则被计算为本次访问结束。访问次数（VV）记录所有访客1天内访问了多少次您的网站，相同的访客有可能多次访问您的网站，那说明这个访客对网站很有兴趣。
4 explain查看hive sql的执行计划
explain
select t.step, count(step) as num
from ods_click_pageviews t
where t.datestr = '20181101' and t.body_bytes_sent > 10
group by t.step
having t.step > 1 and num > 1
limit 3;
# 以下为命令结果，实际sql的执行，会按照执行计划，从上往下顺序地执行
# 注意：having 中的 t.step > 1 ，会被移动到where 子句中优先执行，这就是谓词下推 。
# 谓词下推 ：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。
Explain
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: t
Statistics: Num rows: 91 Data size: 18372 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: ((body_bytes_sent > 10) and (step > 1)) (type: boolean)
Statistics: Num rows: 10 Data size: 2018 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: step (type: string)
outputColumnNames: step
Statistics: Num rows: 10 Data size: 2018 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(step)
keys: step (type: string)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 10 Data size: 2018 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: string)
sort order: +
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 10 Data size: 2018 Basic stats: COMPLETE Column stats: NONE
value expressions: _col1 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
keys: KEY._col0 (type: string)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 5 Data size: 1009 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: (_col1 > 1) (type: boolean)
Statistics: Num rows: 1 Data size: 201 Basic stats: COMPLETE Column stats: NONE
Limit
Number of rows: 3
Statistics: Num rows: 1 Data size: 201 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 201 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: 3
Processor Tree:
ListSink
Time taken: 0.154 seconds, Fetched: 57 row(s)
reference: https://zhuanlan.zhihu.com/p/67565309
note: 嵌套子查询，先执行里面的子查询语句，后执行外部的查询语句。
5 hive导出查询结果
hive -e "set hive.exec.mode.local.auto=true; use dl_venmo_integration_managed_views; select * from account_note where account_note_key in ('2','3')" > account_note_query_result2.txt
hive -e "set hive.exec.mode.local.auto=true; use dl_venmo_integration_managed_views; select subject,' ------> ', regexp_replace(subject,'\020','') from \`case\` where case_key = '2'" > case_query_result.txt
hive -e "set hive.exec.mode.local.auto=true; use dl_venmo_integration_managed_views; select * from \`case\` where case_key = '2'" > case_query_result3.txt
6 hive替换不可见字符与替换掉其他字符
在执行hive计算过程中需要对一些特殊字符进行处理并替换掉，可以使用如下办法
- select hex(col) from tb; 获取特殊字符的十六进制编码；或者使用linux vim 进行编辑，将光标移到该字符中，按下ga将会显示 ga是Vim自带的显示光标字符编码的功能，或者使用 :set invlist 显示 不可见字符
- 使用regexp_replace函数进行替换，如替换如下不可见字符：
select regexp_replace(oid,'\u00a0','') - 是否包含中文正则
regexp_replace(lower(name),'[^0-9a-zA-Z\u4e00-\u9fa5]','') 除中文字母数字以外的字符全部替换掉
“\u4e00”和“\u9fa5”是unicode编码，并且正好是中文编码的开始和结束的两个值，所以这个正则表达式可以用来判断字符串中是否包含中文
