第六章 查询

1、map的数据类型存储，使用JSON格式来表达map，即使用一个被括在{..}内的以逗号分割的键

集合数据查询为 字段名[0] 字段名['xx'] 字段名.xx 等方式来引用

2、数学函数

floor(double d) 返回<=d的最大bigint型值 select floor(3.23) 返回 3
ceil(double d) 返回>=d 的最小bigint型值 select floor(3.23) 返回 4

3、集合函数

count():计算
avg():求列的平均值
sum():求列的和

variance():返回列的方差
stddev_pop(col):返回一组数值的标准偏差
covar_pop(col1,col2):返回一组数值的协方差
corr(col1,col2):返回两组数值的相关系数
percentile(bigint int_expr,p) :int_expr在p(范围是[0,1]处对应的百分比)，其中p是一个double型数值
collecct_set(col):返回集合col元素排重后的数组

通常可以通过 set hive.map.aggr=true来提高聚合的性能
select count(*),avg(inv_amt) from dmt.user_inv_par_info_mon;

4、表生成函数

应用在array 或者map 型数值中

5、其他内置函数
cast(<expr> as <type>) # 将expr转换成type类型，如果转换失败，则返回Null
concat() #拼接字符串
concat_ws(string separator,str1,str2) # 指定分隔符，进行拼接
get_json_object(json_string,string_path) # 从给定路径上的JSON字符串中抽取出JSON对象
regexp_extract(subject,regex_pattern,index): # 抽取字符串subject中符合正则表达式regex_pattern的第index个部分的子字符串

regexp_replace():正则表达式进行替换

to_date(string timestamp） 返回时间字符串的日期部分

weekofyear(string date) 返回时间字符串位于一年中的第几个周内
datediff(string enddate,string startdate):时间相差的天数

6、通过设置set hive.exec.mode.local.auto=true 可以使用本地模式来进行查询，而不触发mapreduce任务

7、对浮点数进行比较时，需要保持极端谨慎的态度，要避免任何从窄类型隐式转换到更广泛类型的操作。和钱相关的都避免使用浮点数

8、rlike正则表达式匹配需要查找的条件表达式

9、having 语句允许用户通过一个简单的语法完成原本需要通过子查询才能对group by 语句产生的分组进行条件过滤的任务

10、join

大多数情况下，hive会对每个join对象启动一个mapreduce任务，当有三个表join时，先启动一个mapreduce任务进行连接操作，然后再启动一个mapreduce任务将第一个mapreduce的输出和表c进行连接操作

11、join优化

提示：当对3个或者更多个表进行join连接时，如果每个on字句都使用相同的连接键的话，那么只会产生一个mapreduce job。
Hive同时假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中的表的大小从左到右是依次增加的。

用户可以显式指定哪张表是大表；
select /* +STREAMTABLE(S)/ S.
from stocks s
join dividends d
on s.id = d.id

对于外连接（outer join）会忽略掉分区过滤条件

left semi-join :左半开连接，会返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件，相当于其他sql语言中的 where字句中的 In 和 exists操作，是其更高效的一种表现。
原因如下：对于左边表中一条指定的记录，在右边表中一旦找到匹配的记录，hive就会停止扫描

select a.*
from dmt.user_inv_par_info_mon a
left semi join dmt.user_inv_info_mon b
on a.user_id = b.user_id
;

select a.*
from dmt.user_inv_par_info_mon a
where user_id in (select user_id from dmt.user_inv_info_mon )
;

12、order by 和 sort by

order by 是在reducer过程执行的全局排序
sort by 只会在每个reducer 中对数据进行排序，也就是执行一个局部排序过程，这样可以保证每个reducer的输出数据都是有序的，但并非全局有序，可以提高后面进行的全局排序的效率。

select *
from dmt.user_inv_par_info_mon
sort by inv_amt desc；

13、含有sort by 的 distribute by

distribute by 控制map的输出在reducer中是如何划分的。mapreduce job中传输的所有数据都是按照键-值对的方式进行组织的，因此hive在将用户的查询语句转换成mapreduce job时，其必须在内部使用这个功能
hive 要求distribute by语句要写在sort by 语句之前,可以实现输出的文件数据是全局排序的，

14、cluster by
可以代替 distribute by 和 sort by的组合写法

15、hive抽样查询

hive 可以通过对表进行分桶抽样来满足这个要求，可以使用rand()函数进行抽样

select * from dmt.user_inv_par_info_mon tablesample(bucket 3 out of 10 on rand()) ;

也可以指定列名
select * from dmt.user_inv_par_info_mon tablesample(bucket 1 out of 2 on user_id) ;

（2）数据块抽样
hive提供了一种按照抽样百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样；

select * from dmt.user_inv_info_mon tablesample(0.1 percent) ;

需要注意的是：这种抽样方式并不一定适用于所有的文件格式，另外，这种抽样的最小抽样单元是一个hdfs数据块。因此如果表的数据大小小于普通的块128mb的话，将会返回所有的行。