【Hive】查询&函数

查询

这里的查询和MySQL中的很类似，就不过多赘述了。

基本查询

limit

典型的查询会返回多行数据。limit子句用于限制返回的行数。

select * from emp limit 5; // 从第一行开始，向下抓取5行
select * from emp limit 2,3; // 表示从第2行开始，向下抓取3行

limit有两种用法，写一个数字和两个数字。两个数字相当于从第一个数字开始，向下抓取第二个数字的行数。同理，第一个数字相当于不写起始点，默认为1，所以只写一个数字就是直接抓取5行（从第一行开始）。

配置Hive的本地服务模式

set mqpreduce.framwork.name = local;

set mqpreduce.framwork.name命令可以查看目前该参数设置成了上面。

该参数实际上是hadoop目录中mapred-stie.xml上的一个参数，它的作用是设置mapreduce程序运行在什么上面，如果写的是yarn，那么mapreduce程序就会提交到yarn上面去运行，如果写的是local，那么就是本地模式。本地模式即所有的maptask和reducetask都在一个节点、一个进程中运行，此时每一个maptask和reducetask都是一个线程。

所以当数据量比较小的时候，使用本地模式可以加快速度，不用等那么久。但是在生产模式中慎用，只适合学习时使用。此外，由于是使用set在程序中设置，所以它是临时的，只在当前会话内有效，故hiveserver2要是断开连接了那么就需要重新设置一遍。

聚合函数

聚合函数，顾名思义就是把多个数据聚合在一起进行计算，最后返回一个数字。

count(*)，表示统计所有行数，包含null值；
count(某列)，表示该列一共有多少行，不包含null值；
max()，求最大值，不包含null，除非所有值都是null；
min()，求最小值，不包含null，除非所有值都是null；
sum()，求和，不包含null。
avg()，求平均值，不包含null。

count就是用来数行的，如果是null那么就不会包括在内，不是null那么就会记一行。

在Hive中，count（*）和 count（1）是一模一样的，底层逻辑也一模一样，执行效率也一毛一样，也就是说在Hive中，count（*）和count（1）是一样的，但是在某些关系型数据库如MySQL中，count（1）的效率比count（*）更高。

count（）括号里填1，2，3，4都是一样的效果，而count（1）和count（*）是一样的效果，所以count（）括号里不管填什么，效果都一样，没啥区别。

如果是空，那么这些聚合函数就都不会统计在内。

分组查询

分组后，待查询的字段名就只能是被分组的字段名和聚合函数。

分组就是所有数据根据某一个字段进行分组，即把一张表分成好几份，每一份返回一个数据，也就是说一共分成了几份最后就返回几个数据，分别对应该被分组字段。而聚合函数是把多个数据聚合在一起进行计算最后返回一个数据，所以在分组时聚合函数会对每一个组进行聚合并返回一个值，所以也是返回和组数相同的数据。

此时如果select了一个别的字段，该字段既不是聚合函数也不是被分组的字段，而我们输出的数据都是混和处理过的，那么该字段该放在生成的表中的哪里呢？很明显，它无处可去，所以使用分组后，select的字段就只能是被分组的字段以及聚合函数。

Join语句

联合查询

联合查询分union和union all两种，区别只是union会对数据进行去重，而union all不进行去重。

union只需要保证字段数量和对应的字段类型一样就行，字段名无所谓的，因为union只是两张表简单粗暴地直接上下接在一起罢了，只要类型一样就行，哪还管名字呢。若真的名字不一样，那么返回的结果里字段名就是第一张表的，也就是上面那张表的。

排序

重点掌握全局排序，即order by，而sort by、distribute by和cluster by都不常用，了解即可。

全局排序（Order By）

order by用于排序，在待排序地的字段名后面写asc或desc用于指定是升序还是降序，其中asc为升序，desc为降序。此外，默认为升序，即什么都不写就是升序排序。

在hive中，我们最好不要直接使用order by，这样很容易出问题。因为在底层其实是一个mr程序，几个map分别对数据进行排序，然后汇入reduce，又由于order by是全局排序，所以最后处理的数据一定会只进入一个reduce中，此时如果数据量很大的话，reduce就很可能会出现问题，所以在使用排序时最好别直接用。

那么正确的操作是什么呢？那就是在普通的语法的末尾加上limit 行数，即指定输出排序后的前多少行。这样做的话底层的map就会只输出前指定行数的数据汇入reduce，然后reduce再最终挑选出前指定行数的数据。这样的话就算输入的数据量很大，由于指定了输出前多少行，最后汇入reduce的行数也只有指定行数的两倍而已，就能大概率保证reduce不会出现问题了。

每个Reduce内部排序（Sort By）

对于大规模的数据集，order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。

Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。

使用sort by其实不是很适合用select语句查看结果，因为整体是无序的，我们应该用insert overwrite local directory 目录名 查询语句; 将结果写入文件中。因为我们在使用sort by之前一定要使用命令set mapreduce.job.reduces = 个数;指定多少个reduce，而一共指定了多少个reduce，最后就会输出多少个文件，这些文件里每一个都是按照指定字段排好序的。

分区（Distribute By）

在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by进行排序。

说白了就是distribute by就是指定分区字段的，指定了之后mr程序就会按照该字段进行分区。

distribute by一般都和sort by一起使用，因为单独使用distribute by的话就只分区不排序，这样得到的每个文件都是无序的，而只使用sort by的话分区就不能指定，只能让输出的每一个文件都按照我们指定的字段进行排序。使用这两种方式的任意一种时万一我们有别的想法都无法实现，所以我们可以将这两种方式进行结合起来用， distribute by指定分区后sort by指定排序字段，这样就能让文件按照我们想的进行分区并且按我们的需要进行排序了。

分区排序（Cluster By）

分区排序，顾名思义，就是分区后进行排序。

由上面sort by可知，我们一般都会将distribute by一和sort by结合起来使用，此时如果我们希望的分区字段和排序字段相同，那么就可以直接使用cluster by，这样的话可以避免写distribute by 字段和sort by 字段，这样更加方便、简洁。

只不过，使用cluster by的话排序只能是升序排序，不能指定排序规则为asc或者desc。

函数

round()如果不指定保留的小数位数，就默认只保留整数，即没有小数。此外，四舍五入是只是对数值进行四舍五入，即如果是负数，相当于看不见负号，四舍五入后再把负号加回来。如round（-1.5）的结果是-2。

我们说的“日期”就是指年月日，“时间”指时分秒。实际上hive和MySQL也都是这么认为的。

时间戳都是零时区的，所以我们想将时间戳转换成某时区的时间字符串当然使用from_utc_timestamp()了，不然转换成的时间字符串还是零时区的。切记，时间戳都是指utc的，即零时区，咱门这是东八区，想要转换成时间字符串那叫转换成北京时间！时间戳都是指零时区的，没有什么东八区的时间戳！！！

datediff()的第一个参数是结束日趋，第二个参数是开始日期。该函数的值是由结束日期减开始日期得到的，也就是说，左边减右边。

需要注意，hive中的日期格式和Linux的不一样，hive中和java是一样的，都是yyyy-MM-dd HH:mm:ss，而Linux则是+%Y-%m-%d %H:%M:%S。