Best Practices for Hive Efficiency

Storage Format
Most importantly you want to store your data in an optimal storage format.
Avoid text, JSON, and even sequence file when possible.
Ideally use RCFile. RCFile (Row Columnar File) format is optimised to utilise Hadoop’s parallelism by storing data in row groups which then are organised in columnar sections. The row groups are stored (one ore more per file) redundantly on HDFS and the same data can be processed by several nodes. The columnar structure improves compression (similarity of data) and allows to skip columns irrelevant to a query (reduce disk IO).

Compression
Generally, use block compression over value compression since it is more efficient (see mapred.output.compression.type setting). Try and use splittable compression algorithms provided by Hadoop & Hive like Snappy (see mapred.output.compression.codec setting). Avoid Gzip – not splittable and CPU intensive – unless you ingest pre-split Gzipped files from a secondary process.

Ensure your final Hive output is always compressed:
set hive.exec.compress.output=true;
And also your intermediate output should be compressed:
set hive.exec.compress.intermediate=true;

Data Locality
Hive supports S3 as a storage layer. Use it for backups and not as your main read/write storage since you will need to read and write across the network. HDFS is great to store your data and gives Hadoop a chance to optimise data access.

Partition large tables
Partition tables if you collect time series data or logs that accumulate over time and you only need to query parts of it. You can store the data in a subdirectory tree like year/month/day or if it is geographic data continent/country/region/city and so forth. That allows your query to skip the irrelevant data.

Querying

Map Joins
Use small tables on the left side of your joins and enable auto optimisation:
set hive.auto.convert.join=true;
This will cache small enough tables and allow for mapper-only joins which are much faster. You can also tweak the size of what HIve considers small enough to cache:
set hive.mapjoin.smalltable.filesize=50000000;
Make sure your JVM has enough memory though.

Avoid Order By
One of the slowest things you can do is to order a large output. Order is global and not yet optimised in Hive and results in one final reducer sorting the data. If you can use sort by or cluster by which organises data on a per reducer (local) level which is much faster of course.

Tune Hadoop
Lastly, optimise Hadoop of course and keep an eye on the jobtracker and logs of the mapreduce jobs like spills. You can increment your JVM and sort cache size, increase min and max split size to ensure that you don’t create countless tiny map tasks and so forth.

盘点SQL on Hadoop中用到的主要技术

数据仓库中的SQL性能优化(Hive篇)

Hive函数大全

一、关系运算:
1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
10. LIKE比较: LIKE
11. JAVA的LIKE操作: RLIKE
12. REGEXP操作: REGEXP
二、数学运算:
1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
9.位取反操作: ~
继续阅读“Hive函数大全”

hive表信息查询

1.hive模糊搜索表
show tables like ‘*name*’;

2.查看表结构信息
desc formatted table_name;
desc table_name;

3.查看分区信息
show partitions table_name;

4.根据分区查询数据
select table_coulm from table_name where partition_name = ‘2014-02-25’;

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

6.从文件加载数据进表(OVERWRITE覆盖,追加不需要OVERWRITE关键字)
LOAD DATA LOCAL INPATH ‘dim_csl_rule_config.txt’ OVERWRITE into table dim.dim_csl_rule_config;
–从查询语句给table插入数据
INSERT OVERWRITE TABLE test_h02_click_log PARTITION(dt) select *
from stage.s_h02_click_log where dt=’2014-01-22′ limit 100;

7.导出数据到文件
insert overwrite directory ‘/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′,’2014-03-08′,’2014-03-09′,’2014-03-10’);”> /home/jrjt/testan/baitiao.dat;

8.自定义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.jd.Get_Property为类路径;

9.查询显示列名 及 行转列显示
set hive.cli.print.header=true; // 打印列名
set hive.cli.print.row.to.vertical=true; // 开启行转列功能, 前提必须开启打印列名功能
set hive.cli.print.row.to.vertical.num=1; // 设置每行显示的列数

10.查看表文件大小,下载文件到某个目录,显示多少行到某个文件
dfs -du hdfs://BJYZH3-HD-JRJT-4137.jd.com:54310/user/jrjt/warehouse/stage.db/s_h02_click_log;
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 文件名 > 文件名

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

12.hive-wui路径
http://172.17.41.38/jobtracker.jsp

13.删除分区
alter table tmp_h02_click_log_baitiao drop partition(dt=’2014-03-01′);
alter table d_h02_click_log_basic_d_fact drop partition(dt=’2014-01-17’);

14.hive命令行操作
执行一个查询,在终端上显示mapreduce的进度,执行完毕后,最后把查询结果输出到终端上,接着hive进程退出,不会进入交互模式。
hive -e ‘select table_cloum from table’
-S,终端上的输出不会有mapreduce的进度,执行完毕,只会把查询结果输出到终端上。这个静音模式很实用,,通过第三方程序调用,第三方程序通过hive的标准输出获取结果集。
hive -S -e ‘select table_cloum from table’
执行sql文件
hive -f hive_sql.sql

15.hive上操作hadoop文件基本命令
查看文件大小
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;

16.插入数据sql、导出数据sql
1.insert 语法格式为:
基本的插入语法:
INSERT OVERWRITE TABLE tablename [PARTITON(partcol1=val1,partclo2=val2)]select_statement FROM from_statement
insert overwrite table test_insert select * from test_table;

对多个表进行插入操作:
FROM fromstatte
INSERT OVERWRITE TABLE tablename1 [PARTITON(partcol1=val1,partclo2=val2)]select_statement1
INSERT OVERWRITE TABLE tablename2 [PARTITON(partcol1=val1,partclo2=val2)]select_statement2

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的方式加载到建立好的表中。数据一旦导入就不可以修改。

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

导入数据到本地目录:
insert overwrite local directory ‘/home/zhangxin/hive’ select * from test_insert1;
产生的文件会覆盖指定目录中的其他文件,即将目录中已经存在的文件进行删除。

导出数据到HDFS中:
insert overwrite directory ‘/user/zhangxin/export_test’ select value from test_table;

同一个查询结果可以同时插入到多个表或者多个目录中:
from test_insert1
insert overwrite local directory ‘/home/zhangxin/hive’ select *
insert overwrite directory ‘/user/zhangxin/export_test’ select value;

17.mapjoin的使用 应用场景:1.关联操作中有一张表非常小 2.不等值的链接操作
select /*+ mapjoin(A)*/ f.a,f.b from A t join B f on ( f.a=t.a and f.ftime=20110802)

18.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

19.查看perl进程
ps -ef|grep perl

20.hive命令移动表数据到另外一张表目录下并添加分区
dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-18 /user/jrjt/warehouse/ods.db/o_h02_click_log/;
dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log_baitiao/* /user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/;–复制所有分区数据
alter table d_h02_click_log_baitiao_basic_d_fact add partition(dt=’2014-03-11′) location ‘/user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/dt=2014-03-11’;

21.导出白条数据
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;

22.hive修改表名
ALTER TABLE o_h02_click_log_i RENAME TO o_h02_click_log_i_bk;

23.hive复制表结构
CREATE TABLE d_h02_click_log_baitiao_ag_sum LIKE tmp.tmp_h02_click_log_baitiao_ag_sum;

24.hive官网网址
https://cwiki.apache.org/conflue … ionandConfiguration
http://www.360doc.com/content/12/0111/11/7362_178698714.shtml

25.hive添加字段
alter table tmp_h02_click_log_baitiao_ag_sum add columns(current_session_timelenth_count bigint comment ‘页面停留总时长’);
ALTER TABLE tmp_h02_click_log_baitiao CHANGE current_session_timelenth current_session_timelenth bigint comment ‘当前会话停留时间’;

26.hive开启简单模式不启用mr
set hive.fetch.task.conversion=more;

27.以json格式输出执行语句会读取的input table和input partition信息
Explain dependency query

hadoop 完全分布式 下 datanode无法启动解决方法

1. 先执行stop-all.sh暂停所有服务
2. 将所有Salve节点上的tmp(即 hdfs-site.xml 中指定的 dfs.data.dir 文件夹,DataNode存放数据块的位置)、 logs 文件夹删除 , 然后重新建立tmp , logs 文件夹
3. 将所有Salve节点上的/usr/hadoop/conf下的core-site.xml删除,将master节点的core-site.xml文件拷贝过来,到各个Salve节点
scp /usr/hadoop/conf/core-site.xml hadoop@slave1:/usr/hadoop/conf/
4. 重新格式化: hadoop namenode -format
5. 启动:start-all.sh

此外也可能遇见slave的Datanode错误:
错误1,hadoop datanode 问题 INFO org.apache.hadoop.ipc.RPC: Server at /:9000 not available yet, Zzzzz..

解决方法见:http://blog.sina.com.cn/s/blog_893ee27f0100zoh7.html,

错误2,slave节点的DataNode不能连接master, 日志信息为为: INFO org.apache..ipc.Client: Retrying connect to server: master/172.16.0.100:9000. Already tried 0 time(s);

解决办法:
1、ping master能通,telnet master 9000不能通,说明开启了防火墙
2、关闭master主机防火墙,可以通过/sbin/iptables -F清除所有规则来暂时停止防火墙
如果想清空的话,先执行 /sbin/iptables -P INPUT ACCEPT,然后执行 /sbin/iptables -F

HIVE和HBASE区别

1. 两者分别是什么?

Apache Hive是一个构建在Hadoop基础设施之上的数据仓库。通过Hive可以使用HQL语言查询存放在HDFS上的数据。HQL是一种类SQL语言,这种语言最终被转化为Map/Reduce. 虽然Hive提供了SQL查询功能,但是Hive不能够进行交互查询–因为它只能够在Haoop上批量的执行Hadoop。

Apache HBase是一种Key/Value系统,它运行在HDFS之上。和Hive不一样,Hbase的能够在它的数据库上实时运行,而不是运行MapReduce任务。Hive被分区为表格,表格又被进一步分割为列簇。列簇必须使用schema定义,列簇将某一类型列集合起来(列不要求schema定义)。例如,“message”列簇可能包含:“to”, ”from” “date”, “subject”, 和”body”. 每一个 key/value对在Hbase中被定义为一个cell,每一个key由row-key,列簇、列和时间戳。在Hbase中,行是key/value映射的集合,这个映射通过row-key来唯一标识。Hbase利用Hadoop的基础设施,可以利用通用的设备进行水平的扩展。

2. 两者的特点

Hive帮助熟悉SQL的人运行MapReduce任务。因为它是JDBC兼容的,同时,它也能够和现存的SQL工具整合在一起。运行Hive查询会花费很长时间,因为它会默认遍历表中所有的数据。虽然有这样的缺点,一次遍历的数据量可以通过Hive的分区机制来控制。分区允许在数据集上运行过滤查询,这些数据集存储在不同的文件夹内,查询的时候只遍历指定文件夹(分区)中的数据。这种机制可以用来,例如,只处理在某一个时间范围内的文件,只要这些文件名中包括了时间格式。

HBase通过存储key/value来工作。它支持四种主要的操作:增加或者更新行,查看一个范围内的cell,获取指定的行,删除指定的行、列或者是列的版本。版本信息用来获取历史数据(每一行的历史数据可以被删除,然后通过Hbase compactions就可以释放出空间)。虽然HBase包括表格,但是schema仅仅被表格和列簇所要求,列不需要schema。Hbase的表格包括增加/计数功能。

3. 限制

Hive目前不支持更新操作。另外,由于hive在hadoop上运行批量操作,它需要花费很长的时间,通常是几分钟到几个小时才可以获取到查询的结果。Hive必须提供预先定义好的schema将文件和目录映射到列,并且Hive与ACID不兼容。

HBase查询是通过特定的语言来编写的,这种语言需要重新学习。类SQL的功能可以通过Apache Phonenix实现,但这是以必须提供schema为代价的。另外,Hbase也并不是兼容所有的ACID特性,虽然它支持某些特性。最后但不是最重要的–为了运行Hbase,Zookeeper是必须的,zookeeper是一个用来进行分布式协调的服务,这些服务包括配置服务,维护元信息和命名空间服务。

4. 应用场景

Hive适合用来对一段时间内的数据进行分析查询,例如,用来计算趋势或者网站的日志。Hive不应该用来进行实时的查询。因为它需要很长时间才可以返回结果。

Hbase非常适合用来进行大数据的实时查询。Facebook用Hbase进行消息和实时的分析。它也可以用来统计Facebook的连接数。

5. 总结

Hive和Hbase是两种基于Hadoop的不同技术–Hive是一种类SQL的引擎,并且运行MapReduce任务,Hbase是一种在Hadoop之上的NoSQL 的Key/vale数据库。当然,这两种工具是可以同时使用的。就像用Google来搜索,用FaceBook进行社交一样,Hive可以用来进行统计查询,HBase可以用来进行实时查询,数据也可以从Hive写到Hbase,设置再从Hbase写回Hive。