hive相关问题总结

今天药忘吃喽~ 2022-06-04 06:57 328阅读 0赞

hive mysql 中文乱码问题研究。

首先mysql 字符集修改  
1：\[client\] 下面增加：  
default-character-set = utf8  
2：\[mysqld\]下面增加：  
character-set-server = utf8  
3：\[mysql\] 下面增加：  
default-character-set = utf8  
4:重启mysql数据库：service mysqld restart  
5:hive-site.xml配置文件中指定utf-8:  
<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://IP:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8</value>  
  <description>JDBC connect string for a JDBC metastore</description>  
</property>  
6：启动hive,执行show tables;  
第一次执行hive会创建相关元数据库表，此时会报错，具体原因是package.jdo中定义的字段太长，因为默认是按照latin1编码的长度，utf8编码的话要长度缩小3倍。  
latin1编码的index和pk字段都有长度限制最大为767,utf8的话最大只能设置255了。所以就有下一步操作  
7:修改hive-metastore-0.10.0-cdh4.2.2.jar中的package.jdo文件，将index和pk字段的长度大于255的字段定义都改小，默认是缩小3倍把。比如原文件定义的是256，那么改成85.  
然后把修改好的jar包替换掉HIVE\_HOME/lib目录下的hive-metastore-0.10.0-cdh4.2.2.jar。  
8：删除mysql中的hive元数据库，drop database hive; 删除hdfs上的 /user/hive目录和/tmp/hive-hadoop目录（如果有的话）  
9:重新运行hive命令，执行show tables;重新创建元数据库表。此时应该能够创建成功，不会报哪个超过767长度的错误了。  
10:测试一下load中文分区：  
create table userinfo(id int ,name string,age int) partitioned by (province string) row format delimited fields terminated by '\\t';  
load data local inpath '/home/hadoop/hive\_example\_data/province\_jx.txt' overwrite into table userinfo partition (province='上海');  
select \* from userinfo;  
查询结果能够正常显示中文的分区。  
但是show partitions userinfo;显示的分区还是乱码，没解决。

今天执行hive带条件的查询select \* from userinfo where province='江西',结果报错：java.io.FileNotFoundException: File does not exist: hdfs://master24:9000/home/hadoop/hive-0.10.0-cdh4.2.2/lib/hive-builtins-0.10.0-cdh4.2.2.jar，job无法启动。

感觉很莫名啊，以前都执行的好好的。于是我首先去看了一下hadoop 集群的状态，是正常的，接下来我想到了是不是我跟我今天改了.hiverc文件有关，于是打开.hiverc文件，

把set hive.exec.mode,local.auto=true;这行删除掉了，再试了一下，好了可以提交job了。由于是照着书上配的，都没注意，太坑了，书上说这个参数是让作业可以在本地执行，有优化作用，尼玛啊，我感觉好像是把hdfs认为为单机local模式，而我的环境是集群环境，总之很莫名，不论如何，问题解决了就好。

我们使用hive一般是执行离线统计分析，然后将执行的结果导入到Mysql的表中供前端报表可视化展现来查询。

导回mysql的方式有许多，以前是用sqoop导回Mysql,还有人用hive jdbc查询然后将结果拉回到代码层面，再在代码层面用mysql jdbc写回Mysql数据库。

但是这两种方式都会有一个二次处理环节（虽然我们以前实现了SQL的解析可以把sqoop的操作对使用者来说透明化，比如insert into mysql.table1 select \* from hive.table2这样的sql会将hive查询出来的结果插入mysql,但是实现起来复杂度比较高）。

这次介绍另外一种处理方式，直接将Mysql的操作集成在udf中，这样直接写一个hql查询语句就可以了。

\[java\]  [view plain][]  [copy][view plain]

1.  package brickhouse.udf.mysql;  
2.    
3.    
4.  import org.apache.commons.dbcp.BasicDataSource;  
5.  import org.apache.commons.dbcp.BasicDataSourceFactory;  
6.  import org.apache.hadoop.conf.Configuration;  
7.  import org.apache.hadoop.fs.FileSystem;  
8.  import org.apache.hadoop.fs.Path;  
9.  import org.apache.hadoop.hive.ql.exec.Description;  
10. import org.apache.hadoop.hive.ql.exec.UDFArgumentException;  
11. import org.apache.hadoop.hive.ql.metadata.HiveException;  
12. import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;  
13. import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector;  
14. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;  
15. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector.Category;  
16. import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;  
17. import org.apache.hadoop.hive.serde2.objectinspector.primitive.IntObjectInspector;  
18. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;  
19. import org.apache.hadoop.io.IntWritable;  
20.   
21. import javax.sql.DataSource;  
22. import java.io.FileNotFoundException;  
23. import java.io.IOException;  
24. import java.io.InputStream;  
25. import java.sql.Connection;  
26. import java.sql.PreparedStatement;  
27. import java.sql.SQLException;  
28. import java.util.Properties;  
29.   
30. @Description(name = "mysql\_import",  
31.         value = "\_FUNC\_(config\_path, sql,args1,\[args2,...\]) - Return ret "  
32. )  
33. public class MysqlImportUDF extends GenericUDF \{  
34.     private IntObjectInspector retValInspector;  
35.     private DataSource dataSource;  
36.     private String sql;  
37.     private PrimitiveObjectInspector\[\] paramsInspectors;  
38.   
39.     @Override  
40.     public Object evaluate(DeferredObject\[\] arg0) throws HiveException \{  
41.   
42.         try (Connection connection = dataSource.getConnection();  
43.              PreparedStatement stmt = connection.prepareStatement(sql)) \{  
44.             System.out.println("execute sql:" + System.currentTimeMillis());  
45.             for (int i = 2; i < arg0.length; i++) \{  
46.                 Object param = paramsInspectors\[i - 2\].getPrimitiveJavaObject(arg0\[i\].get());  
47.                 stmt.setObject(i - 1, param);  
48.             \}  
49.             int ret = stmt.executeUpdate();  
50.             IntWritable iw = new IntWritable(ret);  
51.             return retValInspector.getPrimitiveWritableObject(iw);  
52.         \} catch (SQLException e) \{  
53.             e.printStackTrace();  
54.             throw new HiveException(e);  
55.         \}  
56.   
57.     \}  
58.   
59.   
60.     @Override  
61.     public void close() throws IOException \{  
62.         try \{  
63.             BasicDataSource bds = (BasicDataSource) dataSource;  
64.             bds.close();  
65.         \} catch (SQLException e) \{  
66.             e.printStackTrace();  
67.             throw new IOException(e);  
68.         \}  
69.     \}  
70.   
71.     @Override  
72.     public String getDisplayString(String\[\] arg0) \{  
73.         return "mysql\_import(config\_path, sql,args1\[,args2,...argsN\])";  
74.     \}  
75.   
76.   
77.     @Override  
78.     public ObjectInspector initialize(ObjectInspector\[\] arg0)  
79.             throws UDFArgumentException \{  
80.         if (arg0.length < 3) \{  
81.             throw new UDFArgumentException(" Expecting  at least three  arguments ");  
82.         \}  
83.         if (arg0\[0\].getCategory() == Category.PRIMITIVE  
84.                 && ((PrimitiveObjectInspector) arg0\[0\]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) \{  
85.             if (!(arg0\[0\] instanceof ConstantObjectInspector)) \{  
86.                 throw new UDFArgumentException("mysql connection pool config path  must be constant");  
87.             \}  
88.             ConstantObjectInspector propertiesPath = (ConstantObjectInspector) arg0\[0\];  
89.   
90.             String configPath = propertiesPath.getWritableConstantValue().toString();  
91.             Properties properties = new Properties();  
92.             Configuration conf = new Configuration();  
93.             Path path = new Path(configPath);  
94.             try (FileSystem fs = FileSystem.get(path.toUri(), conf);  
95.                  InputStream in = fs.open(path)) \{  
96.   
97.                 properties.load(in);  
98.                 this.dataSource = BasicDataSourceFactory.createDataSource(properties);  
99.             \} catch (FileNotFoundException ex) \{  
100.                 throw new UDFArgumentException("在文件系统中或者是HDFS上没有找到对应的配置文件");  
101.             \} catch (Exception e) \{  
102.                 e.printStackTrace();  
103.                 throw new UDFArgumentException(e);  
104.             \}  
105.         \}  
106.         if (arg0\[1\].getCategory() == Category.PRIMITIVE  
107.                 && ((PrimitiveObjectInspector) arg0\[1\]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) \{  
108.             if (!(arg0\[1\] instanceof ConstantObjectInspector)) \{  
109.                 throw new UDFArgumentException("the second arg   must be a sql string constant");  
110.             \}  
111.             ConstantObjectInspector sqlInsp = (ConstantObjectInspector) arg0\[1\];  
112.             this.sql = sqlInsp.getWritableConstantValue().toString();  
113.             if (this.sql == null || this.sql.trim().length() == 0) \{  
114.                 throw new UDFArgumentException("the second arg   must be a sql string constant and not nullable");  
115.             \}  
116.         \}  
117.         paramsInspectors = new PrimitiveObjectInspector\[arg0.length - 2\];  
118.         for (int i = 2; i < arg0.length; i++) \{  
119.             paramsInspectors\[i - 2\] = (PrimitiveObjectInspector) arg0\[i\];  
120.         \}  
121.         retValInspector = PrimitiveObjectInspectorFactory.writableIntObjectInspector;  
122.   
123.         return retValInspector;  
124.     \}  
125.   
126. \}

上传jar包，注册udf:

CREATE FUNCTION default.mysql\_import4 AS 'brickhouse.udf.mysql.MysqlImportUDF' USING JAR 'hdfs://name84:8020/tmp/jar/brickhouse-0.7.1.jar';

然后写一个HQL测试一下：

select default.mysql\_import4('hdfs://name84:8020/user/hive/udf/conf/mysql.properties','insert into xj\_test1(ds,\`mod\`,pv,uv) values(?,?,?,?) on duplicate key update pv=pv+?,uv=uv+?',b.ds,b.type,b.pv,b.uv,b.pv,b.uv) from (  
select ds,type,count(did) as pv,count(distinct did) as uv   
from dd\_xyzs\_pc\_action\_detail  
where ds='2016-10-23'  
group by ds,type  
) b

内层子查询是一个聚合查询，业务逻辑是计算每天每个type的pv,uv，然后外层包一层，用上面注册的udf,将计算结果插入mysql。

UDF第一个参数是静态参数，是一个配置文件路径，里面配置了如何开启连接池连接哪个数据库什么的。

第二个参数是一个mysql的sql语句，描述入库方式，然后后面的参数就不固定了，一一对应mysql语句中的占位符，比如我上面有6个占位符，然后我后面就跟了6个参数。

附一个mysql.properties配置文件的内容：

\[java\]  [view plain][]  [copy][view plain]

1.  driverClassName=com.mysql.jdbc.Driver  
2.  url=jdbc:mysql://192.168.78.26:3306/db\_stat?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&connectTimeout=60000&socketTimeout=60000  
3.  username=xyzs  
4.  password=xxxxxx  
5.  initialSize=1  
6.  maxActive=20  
7.  minIdle=5  
8.  maxIdle=15  
9.  connectionTimeoutMillis=5000  
10. maxWait=60000  
11. validationQuery=select 1 from dual  
12. validationQueryTimeout=1  
13. removeAbandoned=true  
14. removeAbandonedTimeout=180  
15. timeBetweenEvictionRunsMillis=30000  
16. numTestsPerEvictionRun=20  
17. testWhileIdle=true  
18. testOnBorrow=false  
19. testOnReturn=false

TODO：目前这个udf是每条结果单独执行一个sql插入，准备写一个batch插入的，将查询结果先collect\_list变成一个数组，然后一次批量插入数据库。

再附上一个批量插入的udf:

\[java\]  [view plain][]  [copy][view plain]

1.  package brickhouse.udf.mysql;  
2.    
3.    
4.  import org.apache.commons.io.FilenameUtils;  
5.  import org.apache.hadoop.conf.Configuration;  
6.  import org.apache.hadoop.fs.FileSystem;  
7.  import org.apache.hadoop.fs.Path;  
8.  import org.apache.hadoop.hive.ql.exec.Description;  
9.  import org.apache.hadoop.hive.ql.exec.UDFArgumentException;  
10. import org.apache.hadoop.hive.ql.metadata.HiveException;  
11. import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;  
12. import org.apache.hadoop.hive.serde2.objectinspector.\*;  
13. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector.Category;  
14. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;  
15.   
16. import java.io.FileNotFoundException;  
17. import java.io.InputStream;  
18. import java.sql.Connection;  
19. import java.sql.DriverManager;  
20. import java.sql.PreparedStatement;  
21. import java.sql.SQLException;  
22. import java.util.Properties;  
23.   
24. @Description(name = "mysql\_batch\_import",  
25.         value = "\_FUNC\_(config\_path, sql,array<struct>) - Return ret "  
26. )  
27. public class MysqlBatchImportUDF extends GenericUDF \{  
28.   
29.     public static final String DEFAULT\_CONFIG\_ROOT\_PATH = "/user/hive/udf/mysqludf/";  
30.     public static final String DEFAULT\_CONFIG\_FILE\_SUFFIX = "properties";  
31.     private StandardListObjectInspector retValInspector;  
32.     private Properties properties;  
33.     private String sql;  
34.     private StandardListObjectInspector paramsListInspector;  
35.     private StandardStructObjectInspector paramsElementInspector;  
36.   
37.     @Override  
38.     public Object evaluate(DeferredObject\[\] arg0) throws HiveException \{  
39.   
40.         //batch import由于是一次性插入，所以不创建连接池了，直接创建一个连接执行  
41.         try (Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));  
42.              PreparedStatement stmt = connection.prepareStatement(sql)) \{  
43.             connection.setAutoCommit(false);  
44.   
45.             for (int i = 0; i < paramsListInspector.getListLength(arg0\[2\].get()); i++) \{  
46.                 Object row = paramsListInspector.getListElement(arg0\[2\].get(), i);  
47.                 for (int j = 0; j < paramsElementInspector.getAllStructFieldRefs().size(); j++) \{  
48.                     StructField structField = paramsElementInspector.getAllStructFieldRefs().get(j);  
49.                     Object col = paramsElementInspector.getStructFieldData(row, structField);  
50.                     Object param = ((PrimitiveObjectInspector) structField.getFieldObjectInspector()).getPrimitiveJavaObject(col);  
51.                     stmt.setObject(j + 1, param);  
52.                 \}  
53.                 stmt.addBatch();  
54.             \}  
55.             int\[\] ret = stmt.executeBatch();  
56.             connection.commit();  
57.   
58.             Object returnlist = retValInspector.create(ret.length);  
59.             for (int i = 0; i < ret.length; i++) \{  
60.                 retValInspector.set(returnlist, i, ret\[i\]);  
61.             \}  
62.             return returnlist;  
63.   
64.         \} catch (SQLException e) \{  
65.             e.printStackTrace();  
66.             throw new HiveException(e);  
67.         \}  
68.   
69.     \}  
70.   
71.   
72.     @Override  
73.     public String getDisplayString(String\[\] arg0) \{  
74.         return "mysql\_batch\_import(config\_path, sql,array<struct>)";  
75.     \}  
76.   
77.   
78.     @Override  
79.     public ObjectInspector initialize(ObjectInspector\[\] arg0)  
80.             throws UDFArgumentException \{  
81.         if (arg0.length != 3) \{  
82.             throw new UDFArgumentException(" Expecting   three  arguments ");  
83.         \}  
84.         //第一个参数校验  
85.         if (arg0\[0\].getCategory() == Category.PRIMITIVE  
86.                 && ((PrimitiveObjectInspector) arg0\[0\]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) \{  
87.             if (!(arg0\[0\] instanceof ConstantObjectInspector)) \{  
88.                 throw new UDFArgumentException("mysql connection pool config path  must be constant");  
89.             \}  
90.             ConstantObjectInspector propertiesPath = (ConstantObjectInspector) arg0\[0\];  
91.   
92.             String fileName1 = propertiesPath.getWritableConstantValue().toString();  
93.             Path path1 = new Path(fileName1);  
94.             if (path1.toUri().getScheme() == null) \{  
95.                 if (!"".equals(FilenameUtils.getExtension(fileName1)) && !DEFAULT\_CONFIG\_FILE\_SUFFIX.equals(FilenameUtils.getExtension(fileName1))) \{  
96.                     throw new UDFArgumentException("不支持的文件扩展名，目前只支持properties文件!");  
97.                 \}  
98.                 //如果是相对路径,补齐根路径  
99.                 if (!fileName1.startsWith("/")) \{  
100.                     fileName1 = MysqlBatchImportUDF.DEFAULT\_CONFIG\_ROOT\_PATH + fileName1;  
101.                 \}  
102.             \}  
103.             //如果只写了文件前缀的话，补上后缀  
104.             if (!FilenameUtils.isExtension(fileName1, DEFAULT\_CONFIG\_FILE\_SUFFIX)) \{  
105.                 fileName1 = fileName1 + FilenameUtils.EXTENSION\_SEPARATOR\_STR + DEFAULT\_CONFIG\_FILE\_SUFFIX;  
106.             \}  
107.             Properties properties = new Properties();  
108.             Configuration conf = new Configuration();  
109.             Path path2 = new Path(fileName1);  
110.   
111.             try (FileSystem fs = FileSystem.newInstance(path2.toUri(), conf); //这里不能用FileSystem.get(path2.toUri(), conf)，必须得重新newInstance,get出来的是共享的连接，这边关闭的话，会导致后面执行完之后可能出现FileSystem is closed的异常  
112.                  InputStream in = fs.open(path2)) \{  
113.                 properties.load(in);  
114.                 this.properties = properties;  
115.             \} catch (FileNotFoundException ex) \{  
116.                 throw new UDFArgumentException("在文件系统中或者是HDFS上没有找到对应的配置文件");  
117.             \} catch (Exception e) \{  
118.                 e.printStackTrace();  
119.                 throw new UDFArgumentException(e);  
120.             \}  
121.         \}  
122.         //第二个参数校验，必须是一个非空的sql语句  
123.         if (arg0\[1\].getCategory() == Category.PRIMITIVE  
124.                 && ((PrimitiveObjectInspector) arg0\[1\]).getPrimitiveCategory() == PrimitiveObjectInspector.PrimitiveCategory.STRING) \{  
125.             if (!(arg0\[1\] instanceof ConstantObjectInspector)) \{  
126.                 throw new UDFArgumentException("the second arg   must be a sql string constant");  
127.             \}  
128.             ConstantObjectInspector sqlInsp = (ConstantObjectInspector) arg0\[1\];  
129.             this.sql = sqlInsp.getWritableConstantValue().toString();  
130.             if (this.sql == null || this.sql.trim().length() == 0) \{  
131.                 throw new UDFArgumentException("the second arg   must be a sql string constant and not nullable");  
132.             \}  
133.         \}  
134.   
135.         //第三个参数校验  
136.         if (arg0\[2\].getCategory() != Category.LIST) \{  
137.             throw new UDFArgumentException(" Expecting an array<struct> field as third argument ");  
138.         \}  
139.         ListObjectInspector third = (ListObjectInspector) arg0\[2\];  
140.         if (third.getListElementObjectInspector().getCategory() != Category.STRUCT) \{  
141.             throw new UDFArgumentException(" Expecting an array<struct> field as third argument ");  
142.         \}  
143.         paramsListInspector = ObjectInspectorFactory.getStandardListObjectInspector(third.getListElementObjectInspector());  
144.         paramsElementInspector = (StandardStructObjectInspector) third.getListElementObjectInspector();  
145.         retValInspector = ObjectInspectorFactory.getStandardListObjectInspector(PrimitiveObjectInspectorFactory.javaIntObjectInspector);  
146.   
147.         return retValInspector;  
148.     \}  
149.   
150. \}

前面两个参数和上面的mysql\_import\_ext一样.

参数3：传递一个array<struct>类型的字段,array中的元素必须是一个struct类型，并且struct中的field个数必须与sql占位符一致

示例demo将查询结果集再进行一次collect\_list操作获得一个array字段作为udf的第三个参数:

select default.mysql\_batch\_import('mysql\_78\_26','insert into xj\_test1(ds,\`mod\`,pv,uv) values(?,?,?,?) on duplicate key update pv=pv+?,uv=uv+?',collect\_list(struct(ds,type,pv,uv,pv,uv))) from

(

select ds,type,count(did) as pv,count(distinct did) as uv

from dd\_xyzs\_pc\_action\_detail

where ds='2016-10-23'

group by ds,type

) a

一、基本聚合函数 数据聚合是按照特定条件将数据整合并表达出来，以总结出更多的组信息。Hive包含内建的一些基本聚合函数，如MAX, MIN, AVG等等，同时也通过GROUPING SETS, ROLLUP, CUBE等函数支持更高级的聚合。Hive基本内建聚合函数通常与GROUP BY连用，默认情况下是对整个表进行操作。在使用GROUP BY时，除聚合函数外其他已选择列必须包含在GROUP BY子句中。  
例：计算employee表中数据总条数 hive> SELECT COUNT(\*) FROM employee; ![20150603170553583.png][]  
例：计算employee表中数据总条数，sex\_age必须包含在GROUP BY的子句中，否则报错！ hive> SELECT sex\_age, count(\*) AS row\_cnt FROM employee GROUP BY sex\_age; ![20150603170553584.png][]  
![20150603170553588.png][]  
那么有一个问题，如果我需要选择一行，但此时我不想对其进行GROUP BY那应该怎么办呢？这里有两个方法，一个是后面要讲到的使用分析函数，另一个就是使用COLLECT\_SET函数，该函数将返回一个包含被GROUP BY排除的列的副本集合。  
例：使用COLLECT\_SET，其中的列不用进行GROUP BY hive> SELECT sex\_age, count(\*) AS row\_cnt FROM employee GROUP BY sex\_age; ![20150603170553592.png][]  
注：聚合函数在同一个语句中可以组合使用，但是不能嵌套使用，即不能在一个聚合函数中套用另一个聚合函数！  
例：组合使用AVG和COUNT hive> SELECT sex\_age.sex, AVG(sex\_age.age) AS avg\_age, count(\*) AS row\_cnt FROM employee GROUP BY sex\_age.sex; ![20150603170553594.png][]  
例：聚合函数与CASE WHEN组合使用 hive> SELECT SUM(CASE WHEN sex\_age.sex='Male' THEN sex\_age.age ELSE 0 END)/COUNT(CASE WHEN sex\_age.sex='Male' THEN 1 ELSE NULL END) AS male\_age\_avg FROM employee; ![20150603170553596.png][]  
例：聚合函数与COALESCE和IF组合使用。COALESCE(arg1, arg2, arg3...)：遇到非null参数即返回改值 hive> SELECT SUM(COALESCE(sex\_age.age, 0)) AS age\_sum, SUM(IF(sex\_age.sex='Female',sex\_age.age,0)) AS female\_age\_sum FROM employee; ![20150603170553597.png][]  
  
例：嵌套聚合函数将会报错 hive> SELECT AVG(COUNT(\*)) AS row\_cnt FROM employee; ![20150603170554599.png][]  
例：聚合函数与DISTINCT关键词组合使用。DISTINCT: 去重 hive> SELECT COUNT(DISTINCT sex\_age.sex) AS sex\_uni\_cnt, COUNT(DISTINCT name) AS name\_uni\_cnt FROM employee; ![20150603170554601.png][] 注：如果COUNT和DISTINCT连用，Hive将忽略对reducer个数的设置（如：set mapred.reduce.tasks=20;）, 仅会有一个reducer！此时reduce将成为瓶颈，这时我们可以使用子查询的方式解决该问题。  
\----------------- 对比 ---------------------- 例：瓶颈问题 hive> SELECT COUNT(DISTINCT sex\_age.sex) AS sex\_uni\_cnt FROM employee; ![20150603170554602.png][]  
例：子查询解决COUNT, DISTINCT瓶颈 hive> SELECT COUNT(\*) AS sex\_uni\_cnt FROM (SELECT DISTINCT sex\_age.sex FROM employee) a; ![20150603170554603.png][]注：子句必须有一个别名，否则会解析语句失败。  
在Hive的聚合中，如果某个聚合列的值中有null，则包含该null的行将在聚合时被忽略。为了避免这种情况，可以使用COALESCE来将null替换为一个默认值。 例： hive> CREATE TABLE t AS SELECT \* FROM (SELECT employee\_id-99 AS val1, (employee\_id-98) AS val2 FROM employee\_hr WHERE employee\_id<=101 UNION ALL SELECT null val1, 2 AS val2 FROM employee\_hr WHERE employee\_id=100) a;  
例： hive> SELECT \* FROM t; ![20150603170554604.png][]  
例：val1=null将被忽略，但该行对应的其他非null值继续被聚合！ hive> SELECT SUM(val1), SUM(val1 + val2) FROM t; ![20150603170555606.png][]  
例：将值为null的使用COALESCE替换为0 hive> SELECT SUM(COALESCE(val1, 0)), SUM(COALESCE(val1, 0)+val2) FROM t; ![20150603170555607.png][]  
可以设置hive.map.aggr属性来控制map阶段的聚合，默认是false。如果设置为true，则将在map任务时进行first-level聚合，这将使得map有更好的性能，但会消耗更多内存。  
  
  
二、高级聚合 高级聚合主要有以下几种情况：  
1. GROUPING SETS 该关键字可以实现对同一个数据集的多重GROUP BY操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包换()数据集，则表示整体聚合。 例： hive> SELECT name, work\_place\[0\] AS main\_place, count(employee\_id) AS emp\_id\_cnt FROM employee\_id GROUP BY name, work\_place\[0\] GROUPING SETS((name, work\_place\[0\]), name, work\_place\[0\], ()); || SELECT name, work\_place\[0\] AS main\_place, count(employee\_id) AS emp\_id\_cnt FROM employee\_id GROUP BY name, work\_place\[0\] UNION ALL SELECT name, NULL AS main\_place, count(employee\_id) AS emp\_id\_cnt FROM employee\_id GROUP BY name UNION ALL SELECT NULL AS name, work\_place\[0\] AS main\_place, count(employee\_id) AS emp\_id\_cnt FROM employee\_id GROUP BY work\_place\[0\] UNION ALL SELECT NULL AS name, NULL AS main\_place, count(employee\_id) AS emp\_id\_cnt FROM employee\_id; ![20150603170555608.png][] ![20150603170555609.png][]  
然而GROUPING SETS目前还有未解决的问题，参考HIVE-6950https://issues.apache.org/jira/browse/HIVE-6950 例： hive> SELECT sex\_age.sex, sex\_age.age, count(name) AS name\_cnt FROM employee GROUP BY sex\_age.sex, sex\_age.age GROUPING SETS((sex\_age.sex, sex\_age.age)); ![20150603170555610.png][]  
  
2. ROLLUP和CUBE 这两个关键字都是GROUP BY的高级实现。  
对比于规定了n层聚合的GROUPING SETS，ROLLUP会创建n+1层聚合，在此n表示分组列的个数。 GROUP BY a, b, c WITH ROLLUP 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(a),())  
CUBE将会对分组列进行所有可能的组合聚合。如果为CUBE指定了n列，则将返回2^n个聚合组合。 GROUP BY a, b, c WITH ROLLUP 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())  
  
  
  
三、聚合条件--HAVING 从Hive0.7.0开始HAVING被添加到Hive作为GROUP BY结果集的条件过滤。HAVING可以作为子句的替代。  
例： hive> SELECT sex\_age.age FROM employee GROUP BY sex\_age.age HAVING count(\*)<=1; ![20150603170555612.png][]  
  
例：作用同上 hive> SELECT a.age FROM (SELECT COUNT(\*) AS cnt, sex\_age.age FROM employee GROUP BY sex\_age.age) a WHERE a.cnt<=1; ![20150603170556613.png][]  
  
  
四、解析函数 解析函数是从Hive0.11.0开始被支持，用于扫描多行输入来计算输出值。常与OVER, PARTITION BY, ORDER BY等连用。由于解析函数的用法相对复杂，在此不作讲解，有兴趣的可以上网搜索相关文章进行学习。  
  
  
五、采样 当数据集非常大的时候，我们需要找一个子集来加快数据分析。此时我们需要数据采集工具以获得需要的子集。在此可以使用三种方式获得采样数据：random sampling， bucket sampling， block sampling.  
  
1. Random sampling 使用RAND()函数和LIMIT关键字来获取样例数据。使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高。 --Syntax： SELECT \* FROM <Table\_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to sample>;  
  
2. Bucket table sampling 该方式是最佳化采样bucket表。RAND()函数也可以用来采样整行。如果采样列同时使用了CLUSTERED BY，使用TABLESAMPLE语句会更有效率。 --Syntax： SELECT \* FROM <Table\_Name> TABLESAMPLE(BUCKET <specified bucket number to sample> OUT OF <total number of buckets> ON \[colname|RAND()\]) table\_alias;  
例： hive> CREATE TABLE employee\_id\_buckets ( name string, employee\_id int, work\_place ARRAY<string>, sex\_age STRUCT<sex:string,age:int>, skills\_score MAP<string,int>, depart\_title MAP<string,ARRAY<string >> ) CLUSTERED BY (employee\_id) INTO 2 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';  
INSERT OVERWRITE TABLE employee\_id\_buckets SELECT \* FROM employee\_id;  
SELECT name FROM employee\_id\_buckets TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand()) a; ![20150603170556614.png][]  
  
3. Block sampling 该方式允许Hive随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据。 --Syntax： SELECT \* FROM <Table\_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s; --ByteLengggthLiteral： --(Digit)+ ('b' | 'B' | 'k' | 'K' | 'm' | 'M' | 'g' | 'G')  
例：按行抽样 hive> SELECT name FROM employee\_id\_buckets TABLESAMPLE(4 ROWS) a; ![20150603170556615.png][]  
例：按数据量百分比抽样 hive> SELECT name FROM employee\_id\_buckets TABLESAMPLE(10 PERCENT) a; 注：此方法有待考证，在Hive0.11.0中将所有25条数据全取出来了，在Hive0.13.0中取出了其中的12条，但是都不符合要求！！  
例：按数据大小采样 hive> SELECT name FROM employee\_id\_buckets TABLESAMPLE(1M) a; ![20150603170556616.png][]  
总结 聚合和抽样，特别是聚合函数，在大数据处理过程中是处理数据的主要方法。通过自由的条件限制以及聚合函数组合，基本能完成任意要求的数据处理或分组。本文仅仅是针对Hive进行了部分比较细致的讲解，关于像解析函数之类的使用比较复杂一点的处理方式需要进行更深一步的了解和运用。希望本文能提供到一定的帮助！

[view plain]: http://blog.csdn.net/xiao_jun_0820/article/details/53404939#
[20150603170553583.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553583.png
[20150603170553584.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553584.png
[20150603170553588.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553588.png
[20150603170553592.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553592.png
[20150603170553594.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553594.png
[20150603170553596.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553596.png
[20150603170553597.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170553597.png
[20150603170554599.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170554599.png
[20150603170554601.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170554601.png
[20150603170554602.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170554602.png
[20150603170554603.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170554603.png
[20150603170554604.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170554604.png
[20150603170555606.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555606.png
[20150603170555607.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555607.png
[20150603170555608.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555608.png
[20150603170555609.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555609.png
[20150603170555610.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555610.png
[20150603170555612.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170555612.png
[20150603170556613.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170556613.png
[20150603170556614.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170556614.png
[20150603170556615.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170556615.png
[20150603170556616.png]: http://www.it165.net/uploadfile/files/2015/0603/20150603170556616.png