Hive查询系列之初相识_7369smithclerk79021980-12-17800.0020 7499allensale

Hive查询系列之初相识

1 基本查询（select……from）

1.1 全表和特定列查询

0）数据准备

（0）原始数据

dept:

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
1
2
3
4

emp：

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

（1）创建部门表

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
1
2
3
4
5
6

（2）创建员工表

create external 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 '\t'
location '/company/emp';
1
2
3
4
5
6
7
8
9
10
11

（3）导入数据

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;
1
2
3

1）全表查询

hive (default)> select * from emp;
1

2）选择特定列查询

hive (default)> select empno, ename from emp;
1

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

1.2 列别名

1）重命名一个列

2）便于计算

3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4）案例实操

​ 查询名称和部门

hive (default)> select ename AS name, deptno dn from emp;
1

1.3 算术运算符

​ 案例实操：查询出所有员工的薪水后加10000显示。

hive (default)> select sal +10000 from emp;
1

1.4 常用函数

1）求总行数（count）

hive (default)> select count(*) cnt from emp;
1

2）求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;
1

3）求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;
1

4）求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp; 
1

5）求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;
1

1.5 Limit语句

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

hive (default)> select * from emp limit 5;
1

获取第三、四、五行的数据

hive (default)> select * from emp limit 2,3;
1

1.6 Where语句

1）使用WHERE子句，将不满足条件的行过滤掉

2）WHERE子句紧随FROM子句

3）案例实操

​ 查询出薪水大于1000的所有员工

hive (default)> select * from emp where sal > 1000;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.0	300.0	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.0	1400.0	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.0	NULL	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.0	NULL	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.0	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.0	NULL	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.0	0.0	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.0	NULL	20
7902	FORD	ANALYST	7566	1981-12-3	3000.0	NULL	20
7934	MILLER	CLERK	7782	1982-1-23	1300.0	NULL	10
Time taken: 3.079 seconds, Fetched: 12 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意：where子句中不能使用字段别名。

1.7 比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

2）案例实操

（1）查询出薪水等于5000的所有员工

hive (default)> select * from emp where sal = 5000;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7839	KING	PRESIDENT	NULL	1981-11-17	5000.0	NULL	10
Time taken: 0.257 seconds, Fetched: 1 row(s)
1
2
3
4
5

（2）查询工资在500到1000的员工信息

hive (default)> select * from emp where sal between 500 and 1000;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7369	SMITH	CLERK	7902	1980-12-17	800.0	NULL	20
7900	JAMES	CLERK	7698	1981-12-3	950.0	NULL	30
Time taken: 0.245
1
2
3
4
5