hive中distinct用法

欢迎各位关注我的个人微信公众号：赶令，将会定期更新大数据相关知识及个人心得

hive中的distinct是去重的意思，和group by在某些情况下有相同的功能

下面测试下distinct的部分功能，先创建一张测试表

create table test.trip_tmp( id int, user_id int, salesman_id int, huose_id int ); 

插入模拟数据

insert into test.trip_tmp values(1, 2, 3, 3); insert into test.trip_tmp values(1, 2, 3, 3); insert into test.trip_tmp values(2, 2, 3, 3); insert into test.trip_tmp values(3, 2, 3, 3); insert into test.trip_tmp values(4, 2, 5, 3); insert into test.trip_tmp values(6, 3, 3, 3); insert into test.trip_tmp values(5, 4, 2, 3); insert into test.trip_tmp values(5, 2, 3, 3); insert into test.trip_tmp values(6, 2, 5, 3); insert into test.trip_tmp values(5, 2, 3, 3); insert into test.trip_tmp values(5, 2, 5, 3); 

查看表的所有数据

select * from test.trip_tmp; OK 1 2 3 3 1 2 3 3 5 2 5 3 2 2 3 3 3 2 3 3 4 2 5 3 6 3 3 3 5 4 2 3 5 2 3 3 6 2 5 3 5 2 3 3 Time taken: 0.277 seconds, Fetched: 11 row(s) 

对表的所有列去重

select distinct id, user_id, salesman_id, huose_id from test.trip_tmp; OK 1 2 3 3 2 2 3 3 3 2 3 3 4 2 5 3 5 2 3 3 5 2 5 3 5 4 2 3 6 2 5 3 6 3 3 3 Time taken: 13.142 seconds, Fetched: 9 row(s) 

这样distinct后的所有列重复的数据去除了

hive中使用distinct必须在select的最前面，不能在distinct的前面加列名，否则会报错

select huose_id, distinct id, user_id, salesman_id from test.trip_tmp; NoViableAltException(96@[80:1: selectItem : ( ( tableAllColumns )=> tableAllColumns -> ^( TOK_SELEXPR tableAllColumns ) | ( expression ( ( ( KW_AS )? identifier ) | ( KW_AS LPAREN identifier ( COMMA identifier )* RPAREN ) )? ) -> ^( TOK_SELEXPR expression ( identifier )* ) );]) at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser$DFA13.specialStateTransition(HiveParser_SelectClauseParser.java:4625) at org.antlr.runtime.DFA.predict(DFA.java:80) at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectItem(HiveParser_SelectClauseParser.java:1616) at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectList(HiveParser_SelectClauseParser.java:1177) at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectClause(HiveParser_SelectClauseParser.java:951) at org.apache.hadoop.hive.ql.parse.HiveParser.selectClause(HiveParser.java:42192) at org.apache.hadoop.hive.ql.parse.HiveParser.atomSelectStatement(HiveParser.java:36852) at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:37119) at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:36765) at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:35954) at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:35842) at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2285) at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1334) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:208) at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:77) at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:70) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:468) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1317) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1457) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1237) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1227) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) FAILED: ParseException line 1:17 cannot recognize input near 'distinct' 'id' ',' in selection target 

distinct也可以这样用，但和把所有的列都放一起差不多

select distinct (id, user_id, huose_id), salesman_id from test.trip_tmp; OK { 
  "col1":1,"col2":2,"col3":3} 3 { 
  "col1":2,"col2":2,"col3":3} 3 { 
  "col1":3,"col2":2,"col3":3} 3 { 
  "col1":4,"col2":2,"col3":3} 5 { 
  "col1":5,"col2":2,"col3":3} 3 { 
  "col1":5,"col2":2,"col3":3} 5 { 
  "col1":5,"col2":4,"col3":3} 2 { 
  "col1":6,"col2":2,"col3":3} 5 { 
  "col1":6,"col2":3,"col3":3} 3 Time taken: 9.201 seconds, Fetched: 9 row(s) 

distinct不能和聚合函数并列使用，否则会报错

select distinct id, user_id, salesman_id, count(huose_id) from test.trip_tmp; FAILED: SemanticException [Error 10128]: Line 1:42 Not yet supported place for UDAF 'count' 

但可以在聚合函数里面使用distinct

select count(distinct id) from test.trip_tmp; OK 6 Time taken: 4.775 seconds, Fetched: 1 row(s) 

最后，如果能用group by的就尽量使用group by，因为group by性能比distinct更好，尤其数据量大的时候能明显感觉到。