Hive查询与排序

1.Hive查询

1.基本查询（Select…From）

1.1全表和特定列查询

  一、数据准备
   1.创建部门表
    create table if not exists dept(
        deptno int,
        dname string,
        loc int
        )
        row format delimited fields terminated by '	';
    2.创建员工表
    create table if not exists emp(
        empno int,
        ename string,
        job string,
        mgr int,
        hiredate string, 
        sal double, 
        comm double,
        deptno int)
        row format delimited fields terminated by '	';
     3.导入数据
      load data local inpath '/opt/module/datas/dept.txt' into table dept;
      load data local inpath '/opt/module/datas/emp.txt' into table emp;
    二、全表查询
       select * from emp;
    三、选择特定列查询
      select empno, ename from emp;

1.2列别名

  1）重命名一个列
  2）便于计算
  3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’  可以加 也可以不加

  select ename AS name, deptno dn from emp;

1.3算术运算符

   运算符        描述
    A+B	      A和B 相加
    A-B	      A减去B
    A*B	      A和B 相乘
    A/B	      A除以B
    A%B	      A对B取余
    A&B	      A和B按位取与
    A|B	      A和B按位取或
    A^B	      A和B按位取异或   相同为0  不同为1 
    ~A	      A按位取反   0变1  1变0

1.4常用函数

  1.求总行数（count） select count(*) cnt from emp;
  2.求工资的最大值（max）select max(sal) max_sal from emp;
  3.求工资的最小值（min）select min(sal) min_sal from emp;
  4.求工资的总和（sum） select sum(sal) sum_sal from emp; 
  5.求工资的平均值（avg）select avg(sal) avg_sal from emp;

1.5 Limit语句

    典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
    select * from emp limit 5;

1.6 Where语句

   1）使用WHERE子句，将不满足条件的行过滤掉
   2）WHERE子句紧随FROM子句
      select * from emp where sal >1000;
   注意：where子句中不能使用字段别名。

2.分组

2.1 Group By语句

   GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

2.2 Having语句

   1.having与where不同点
     where后面（条件）不能写分组函数，而having后面可以使用分组函数。
     having只用于group by分组统计语句。
   2.求每个部门的平均薪水大于2000的部门
     select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

2.Hive排序

2.1 全局排序（Order By）

   Order By：全局排序，只有一个Reducer
   ASC（ascend）: 升序（默认）
   DESC（descend）: 降序
   ORDER BY 子句在SELECT语句的结尾
   查询员工信息按工资升序排列 ：select * from emp order by sal;
   查询员工信息按工资降序排列 ：select * from emp order by sal desc;

2.2 按照别名排序

   按照员工薪水的2倍排序 ： select ename, sal*2 twosal from emp order by twosal；

2.3 多个列排序

  按照部门和工资升序排序 ：select ename, deptno, sal from emp order by deptno, sal ;

全局排序压力比较大 效率较低 解决方案 两种 一、局部排序 二、加limit (hive中map会先筛选出需要的数据，减少reduce的排序数据)

2.4 每个MapReduce内部排序（Sort By）

  Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。  不指定分区时 ，按照每一行的hash值进行排序
   hive的参数修改 
     1.动态修改  
       设置reduce个数  hive (default)> set mapreduce.job.reduces=3;
       查看设置reduce个数  hive (default)> set mapreduce.job.reduces;
     2.静态修改  直接在$HIVE_HOME/conf/hive_site.xml修改  如下图

2.5 分区排序（Distribute By） 可以指定hive按照哪一列的hash值进行分区

   Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定 
   义分区），进行分区，结合sort by使用。 
   对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果
    select * from emp distribute by empno sort by sal desc;

   注意：
 distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
 Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

2.6 Cluster By

     当distribute by和sorts by字段相同时，可以使用cluster by方式。
      cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
       以下两句话等同：          
      select * from emp distribute by empno sort by empno;
      select * from emp cluster by empno;

2.7 抽样查询

  select * from stu tablesample(bucket 1 out of 4 on id);
  注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 
  id 一般用unique 值，一共用算法把数据分为y份，取其中一份作为抽样查询的结果