-
基本查询
-- 创建部门表 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 "^张";
-
分组和分组过滤
-- 查询各个部门的平均工资 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;
-
连接
-- 查询员工姓名,员工部门名称 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;
-
排序
-- 按照工资从高到低排序 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;
Hive查询
最新推荐文章于 2024-09-11 16:22:56 发布