hive 使用技巧

1.抽样 从一个表中随机抽样得到一个不重复的数据样本，

随机取样 SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT ; 这是使用RAND()函数和LIMIT关键字来获取样例数据。 使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高 顺便说下： order by是对输入做全局排序，因此要在一个reduce中完成，而一个reduce会导致大数据量情况下计算非常缓慢,这也是尽量避免用order by的原因。使用时尽量配合limit使用，因为order by后面不跟limit。order by会强制将reduce number设置成1，不加limit，会将所有数据sink到reduce端来做全排序。 if (sortExprs == null) { sortExprs = qb.getParseInfo().getOrderByForClause(dest); if (sortExprs != null) { assert numReducers == 1; // in strict mode, in the presence of order by, limit must be specified Integer limit = qb.getParseInfo().getDestLimit(dest); if (conf.getVar(HiveConf.ConfVars.HIVEMAPREDMODE).equalsIgnoreCase( "strict") && limit == null) { throw new SemanticException(generateErrorMessage(sortExprs, ErrorMsg.NO_LIMIT_WITH_ORDERBY.getMsg())); } } }

sort by不是全局排序，其在数据进入reducer前完成排序，因此，如果用sort by进行排序，当设置mapred.reduce.tasks>1时，也就是在reduce大于一个的情况下，则sort by只能保证每个reducer的输出有序，并不保证全局有序。sort by不同于order by，它不受hive.mapred.mode属性的影响，sort by的数据只能保证在同一个reduce中的数据可以按指定字段排序,支持desc/asc。使用sort by你可以指定执行的reduce个数(通过set mapred.reduce.tasks=n来指定,也就生成几个局部有序的文件)，对输出的数据再执行归并排序，即可得到全部结果。

distribute by则是控制在map端如何拆分数据给reduce端。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此，distribute by经常和sort by配合使用。

public int getPartition(K2 key, V2 value, int numReduceTasks) { return (key.hashCode() & Integer.MAX_VALUE) % numReduceTasks; }

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒叙排序，不能指定排序规则为ASC或者DESC。

select * from test distribute by rand() sort by rand()/order by rand() limit 10; 拼接随机数 生成一个随机的列再取其中的值 select * from ( select a.*,cast(rand() * 10000 as int) as rid from test a ) b order by b.rid limit 1000 数据块取样 block_sample: TABLESAMPLE (n PERCENT) 根据inputSize的大小，取样百分分比n的数据。 如获取test表50%的数据： SELECT * FROM test TABLESAMPLE (50 PERCENT)

block_sample: TABLESAMPLE (nM) 根据大小获取多少M的数据

SELECT * FROM test TABLESAMPLE (30M);

block_sample: TABLESAMPLE (n ROWS) 根据inputSize获取取样每个map输入的行数据 总行数=10*map数

SELECT * FROM tesgt TABLESAMPLE (2 ROWS) where day='2018-10-03' 分桶取样 分桶其实就是根据某一个字段Hash取模分桶数，放入指定数据的桶中 语法 : TABLESAMPLE (BUCKET x OUT OF y [ON colname])

其中x是要抽样的桶编号，桶编号从1开始，colname表示抽样的列，y表示桶的数量。 例如：将表test随机分成5个桶，抽样第一个桶的数据；

SELECT * FROM test TABLESAMPLE (BUCKET 1 OUT OF 5 ON rand());

2.分组

文件切分 set mapred.reduce.tasks=5; create table test2 select * from test1 distribute by rand(123);

设置5个reduce，通过distribute by rand(123) 将数据随机打散，平均分配到5个文件中，这种方式能够处理文件过大,文件大小不均的问题，同时可以通过这种方式增加map数

3.控制map数 set mapred.max.split.size=100000000; set mapred.min.split.size.per.node=100000000; set mapred.min.split.size.per.rack=100000000; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

100000000表示100M, set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;这个参数表示执行前进行小文件合并， 前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的） 第二是设置hive参数，将额外启动一个MR Job打包小文件 hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False hive.merge.size.per.task = 25610001000 合并文件的大小 4.控制reduce数 reduce数量由以下三个参数决定， set mapred.reduce.tasks=2 (指定reduce的任务数量为2) set hive.exec.reducers.bytes.per.reducer=1073741824（每个reduce任务处理的数据量，默认为1000^3=1G）

set hive.exec.reducers.max=999（每个job最大的reduce数，默认为999） 计算reducer数的公式 N=min( hive.exec.reducers.max ,总输入数据量/ hive.exec.reducers.bytes.per.reducer ) 只有一个reduce的情况： a、没有group by 的汇总 b、order by c、笛卡尔积

5.数据倾斜 hive.map.aggr=true 会在mapper先做一次聚合，减少reduce需要处理的数据，相当于在Map端做combiner,假如map各条数据基本上不一样, 则没有聚合的意义，在做combiner反而画蛇添足,hive里为了更合理点儿通过参数hive.groupby.mapaggr.checkinterval = 100000 ，hive.map.aggr.hash.min.reduction=0.5,来控制。预先取100000条数据聚合,如果聚合后的条数/100000>0.5，则不再聚合 hive.groupby.skewindata=true 配置变量的缩写，可以设置为true或false，表示是否是倾斜表等

set hive.skewjoin.key=100000; 这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置 set hive.optimize.skewjoin=true; 如果是join 过程出现倾斜 应该设置为true

6.生成唯一id列

生成uuid，去除中间的“-”

regexp_replace(reflect("java.util.UUID", "randomUUID"), "-", "")

这里主要看看reflect函数 reflect函数可以支持在sql中调用java中的自带函数 例如： 取column1和column2中的最大值

select reflect("java.lang.Math","max",column1,column2) from test

sql:select reflect(class_name,method_name,column1,column2) from tablename;

如果字段是函数名称 java.lang.Math ,method_name是具体的方法,max/min也是支持的

7.牛逼的开窗函数 SUM、AVG、MIN、MAX 配合 over()使用 LAG,LEAD,FIRST_VALUE,LAST_VALUE GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 可基于窗口解决许多sql难以实现的问题 如分大类小类的求和,不同层级的累加,分片等等 http://lxw1234.com/archives/tag/hive-window-functions

参考： https://www.cnblogs.com/xd502djj/p/3799432.html https://blog.csdn.net/u013668852/article/details/79866931