Hive查询

1. 基本查询

   -- 创建部门表
   create table if not exists dept(
       deptno int,    -- 部门编号
       dname string,  -- 部门名称
       loc int        -- 部门位置
   )
   row format delimited fields terminated by '\t';
   
   -- 创建员工表
   create table if not exists emp(
       empno int,      -- 员工编号
       ename string,   -- 员工姓名
       job string,     -- 员工岗位（大数据工程师、前端工程师、java工程师）
       sal double,     -- 员工薪资
       deptno int      -- 部门编号
   )
   row format delimited fields terminated by '\t';
   
   -- 导入数据
   load data local inpath '/opt/module/hive/datas/dept.txt' into table dept;
   load data local inpath '/opt/module/hive/datas/emp.txt' into table emp;
   

   -- 查询特定列
   select ename, sal from emp;
   
   -- 起别名，as可以省略
   select ename as name, sal salary from emp;
   
   -- 查询中可以放运算符
   select ename, sal + 10 from emp;
   
   -- 常用函数（UDAF）
   select count(1) cnt,               -- 数个数
          sum(sal) sum_sal,           -- 求和
          avg(sal) avg_sal,           -- 求平均
          min(sal) min_sal,           -- 最小值
          max(sal) max_sal            -- 最大值
   from emp;
   
   -- UDAF函数执行时候，NULL不参与运算的
   select avg(children['xiao song']), 
          min(children['xiao song']), 
          max(children['xiao song']), 
          sum(children['xiao song']),
          count(children['xiao song'])
   from test;
   
   -- 显示前5
   select ename from emp limit 5;
   
   -- 显示2-5名
   select ename from emp limit 1,4;
   
   -- 过滤 查询工资大于1000的人
   select ename, sal from emp where sal>1000;
   
   -- 安全等与 <=> 演示，对比下面两句查询
   select ename, job from emp where job = null;
   select ename, job from emp where job <=> null;
   
   -- 字符串（通配符）以张开头的人
   select * from emp where ename like "张%";
   -- 字符串（正则表达式）以张开头的人
   select * from emp where ename rlike "^张";
   

2. 分组和分组过滤

   -- 查询各个部门的平均工资
   select deptno, avg(sal) from emp 
   group by deptno;
   
   -- 查询平均工资大于2000的部门和其平均工资
   select deptno, avg(sal) avg_sal from emp 
   group by deptno 
   having avg_sal>2000;
   

3. 连接

   -- 查询员工姓名，员工部门名称
   select emp.ename, dept.dname from emp inner join dept on emp.deptno = dept.deptno;
   
   -- 为了演示多种连接区别，向emp和dept表插入数据
   insert into emp values(7360, '死鬼', '失业', null, 50);
   insert into dept values(60, '不存在', 1700);
   -- 左连接
   select emp.ename, dept.dname from emp left join dept on emp.deptno = dept.deptno;
   -- 右连接
   select emp.ename, dept.dname from emp right join dept on emp.deptno = dept.deptno;
   -- 全连接
   select emp.ename, dept.dname from emp full join dept on emp.deptno = dept.deptno;
   
   -- 多表连接
   create table if not exists location(
       loc int,          -- 部门位置id
       loc_name string   -- 部门位置
   )
   row format delimited fields terminated by '\t';
   load data local inpath '/opt/module/hive/datas/location.txt' into table location;
   -- 查询员工姓名，部门名称，单位名称
   select e.ename,
          d.dname,
          l.loc_name
   from emp e
       right join dept d on e.deptno = d.deptno
       left join location l on d.loc = l.loc;
   
   -- 笛卡尔积 (不写连接条件会产生笛卡尔积)
   select * from emp join dept;
   
   -- 注意下面的情况
   insert into dept values(40, 'XXXX', 1800);
   select emp.ename, dept.dname from emp inner join dept on emp.deptno = dept.deptno;
   

4. 排序

   -- 按照工资从高到低排序
   select * from emp order by sal desc;
   -- 排序可以用别名
   select ename, sal salary from emp order by salary desc;
   -- 先按照部门编号升序排序，相同部门按照工资降序排序
   select * from emp order by deptno asc, sal desc;
   -- 按照部门平均工资降序给部门排序
   select deptno, avg(sal) avg_sal from emp group by deptno order by avg_sal desc;
   
   -- 全局排序数据量大可能会遇到性能问题，所以排序一般结合limit使用
   select * from emp order by sal desc limit 5;
   
   -- 分区和分区排序
   -- 首先设置reduce数量为3
   set mapreduce.job.reduces=3;
   
   -- distribute by指定查询按照什么字段的hash分区, sort by在分区内部排序
   select * from emp distribute by empno sort by empno desc;
   
   -- 当distribute by和sort by字段相同且为升序时，可以用cluster by代替
   select * from emp distribute by empno sort by empno;
   -- 相当于
   select * from emp cluster by empno;