四、查询语句

##4.1 语法规则与数据准备

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

基本查询语句语法：

xxxxxxxxxx
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

小写版：

xxxxxxxxxx
select [all | distinct] select_expr, select_expr, ...
  from table_reference
  [where where_condition]
  [group by col_list]
  [order by col_list]
  [cluster by col_list
    | [distribute by col_list] [sort by col_list]
  ]
 [limit [offset,] rows]

数据准备：

1. 原始数据

部门表：dept

xxxxxxxxxx
10  accounting  1700
20  research    1800
30  sales   1900
40  operations  1700

雇员表emp：

xxxxxxxxxx
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

2. 创建部门表

xxxxxxxxxx
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

3. 创建员工表

xxxxxxxxxx
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 '\t';

4. 在node4将数据写到/root/data/下面的两个文件中

   xxxxxxxxxx
   [root@node4 ~]# cd data/
   [root@node4 data]# vim dept.txt
   [root@node4 data]# vim emp.txt
   

5. 导入数据

xxxxxxxxxx
hive> load data local inpath '/root/data/dept.txt' into table dept;
Loading data to table default.dept
OK
Time taken: 2.42 seconds
hive> load data local inpath '/root/data/emp.txt' into table emp;
Loading data to table default.emp
OK
Time taken: 0.761 seconds