一.向表中裝載數(shù)據(jù)（Load）
1．語(yǔ)法
hive> load data [local] inpath '/opt/module/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];
（1）load data:表示加載數(shù)據(jù)
（2）local:表示從本地加載數(shù)據(jù)到hive表憨栽；否則從HDFS加載數(shù)據(jù)到hive表
（3）inpath:表示加載數(shù)據(jù)的路徑
（4）overwrite:表示覆蓋表中已有數(shù)據(jù)营罢，否則表示追加
（5）into table:表示加載到哪張表
（6）student:表示具體的表
（7）partition:表示上傳到指定分區(qū)
2．實(shí)操案例
（0）創(chuàng)建一張表
hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t';
（1）加載本地文件到hive
hive (default)> load data local inpath '/opt/module/datas/student.txt' into table default.student;
（2）加載HDFS文件到hive中
上傳文件到HDFS
hive (default)> dfs -put /opt/module/datas/student.txt /user/atguigu/hive;
加載HDFS上數(shù)據(jù)
hive (default)> load data inpath '/user/atguigu/hive/student.txt' into table default.student;
（3）加載數(shù)據(jù)覆蓋表中已有的數(shù)據(jù)
上傳文件到HDFS
hive (default)> dfs -put /opt/module/datas/student.txt /user/atguigu/hive;
加載數(shù)據(jù)覆蓋表中已有的數(shù)據(jù)
hive (default)> load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;
通過查詢語(yǔ)句向表中插入數(shù)據(jù)（Insert）
1．創(chuàng)建一張分區(qū)表
hive (default)> create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';
2．基本插入數(shù)據(jù)
hive (default)> insert into table student partition(month='201709') values(1,'wangwu');
3．基本模式插入（根據(jù)單張表查詢結(jié)果）
hive (default)> insert overwrite table student partition(month='201708')
select id, name from student where month='201709';
4．多插入模式（根據(jù)多張表查詢結(jié)果）（有問題，只是查詢單表不同分區(qū)的）
hive (default)> from student
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';
- 真的多表查詢插入至其他表
將s3仲智，s4表的數(shù)據(jù)union all成新表new_table 然后插入到s5
from (select * from s3 union all select * from s4) new_table
insert into table s5
select * ;
查詢語(yǔ)句中創(chuàng)建表并加載數(shù)據(jù)（As Select）
根據(jù)查詢結(jié)果創(chuàng)建表（查詢的結(jié)果會(huì)添加到新創(chuàng)建的表中）
create table if not exists student3
as select id, name from student;
創(chuàng)建表時(shí)通過Location指定加載數(shù)據(jù)路徑
1．創(chuàng)建表买乃，并指定在hdfs上的位置
hive (default)> create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
2．上傳數(shù)據(jù)到hdfs上
hive (default)> dfs -put /opt/module/datas/student.txt
/user/hive/warehouse/student5;
3．查詢數(shù)據(jù)
hive (default)> select * from student5;
Import數(shù)據(jù)到指定Hive表中
注意：先用export導(dǎo)出后，再將數(shù)據(jù)導(dǎo)入钓辆。
hive (default)> import table student2 partition(month='201709') from
'/user/hive/warehouse/export/student';
二.數(shù)據(jù)導(dǎo)出
Insert導(dǎo)出
1．將查詢的結(jié)果導(dǎo)出到本地
hive (default)> insert overwrite local directory '/opt/module/datas/export/student'
select * from student;
2．將查詢的結(jié)果格式化導(dǎo)出到本地
hive(default)>insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
3．將查詢的結(jié)果導(dǎo)出到HDFS上(沒有l(wèi)ocal)
hive (default)> insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
Hadoop命令導(dǎo)出到本地
hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;
Hive Shell 命令導(dǎo)出
基本語(yǔ)法：（hive -f/-e 執(zhí)行語(yǔ)句或者腳本 > file）
[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
/opt/module/datas/export/student4.txt;
Export導(dǎo)出到HDFS上
(defahiveult)> export table default.student to
'/user/hive/warehouse/export/student';
Sqoop導(dǎo)出
后續(xù)..............................剪验。
清除表中數(shù)據(jù)（Truncate）
注意：Truncate只能刪除管理表，不能刪除外部表中數(shù)據(jù)
hive (default)> truncate table student;
三.查詢語(yǔ)句語(yǔ)法：
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
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 number]
基本查詢（Select…From）
全表和特定列查詢
1．全表查詢
hive (default)> select * from emp;
2．選擇特定列查詢
hive (default)> select empno, ename from emp;
注意：
（1）SQL 語(yǔ)言大小寫不敏感前联。
（2）SQL 可以寫在一行或者多行
（3）關(guān)鍵字不能被縮寫也不能分行
（4）各子句一般要分行寫功戚。
（5）使用縮進(jìn)提高語(yǔ)句的可讀性。
列別名
1．重命名一個(gè)列
2．便于計(jì)算
3．緊跟列名似嗤，也可以在列名和別名之間加入關(guān)鍵字‘AS’
4．案例實(shí)操
查詢名稱和部門
hive (default)> select ename AS name, deptno dn from emp;
having與where不同點(diǎn)
select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;
#WHERE 語(yǔ)句在 GROUP BY 語(yǔ)句之前啸臀；SQL 會(huì)在分組之前計(jì)算 WHERE 語(yǔ)句。
#HAVING 語(yǔ)句在 GROUP BY 語(yǔ)句之后烁落；SQL 會(huì)在分組之后計(jì)算 HAVING 語(yǔ)句乘粒。
（1）where針對(duì)表中的列發(fā)揮作用，查詢數(shù)據(jù)伤塌；having針對(duì)查詢結(jié)果中的列發(fā)揮作用灯萍，篩選數(shù)據(jù)。
（2）where后面不能寫分組函數(shù)每聪，而having后面可以使用分組函數(shù)旦棉。
（3）having只用于group by分組統(tǒng)計(jì)語(yǔ)句。
四.其他常用查詢函數(shù)
空字段賦值
- 函數(shù)說明
NVL：給值為NULL的數(shù)據(jù)賦值药薯，它的格式是NVL( string1, replace_with)他爸。它的功能是如果string1為NULL，則NVL函數(shù)返回replace_with的值果善，否則返回string1的值诊笤，如果兩個(gè)參數(shù)都為NULL ，則返回NULL巾陕。 - 數(shù)據(jù)準(zhǔn)備：采用員工表
- 查詢：如果員工的comm為NULL讨跟，則用-1代替
hive (default)> select nvl(comm,-1) from emp;
OK
_c0
20.0
300.0
500.0
-1.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0
- 查詢：如果員工的comm為NULL纪他，則用領(lǐng)導(dǎo)id代替
hive (default)> select nvl(comm,mgr) from emp;
OK
_c0
20.0
300.0
500.0
7839.0
1400.0
7839.0
7839.0
7566.0
NULL
0.0
7788.0
7698.0
7566.0
5.CASE WHEN
- 數(shù)據(jù)準(zhǔn)備
name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
鳳姐 A 女
婷姐 B 女
婷婷 B 女
需求
求出不同部門男女各多少人。結(jié)果如下：
A 2 1
B 1 2
創(chuàng)建本地emp_sex.txt晾匠，導(dǎo)入數(shù)據(jù)
[atguigu@hadoop102 datas]$ vi emp_sex.txt
悟空 A 男
大海 A 男
宋宋 B 男
鳳姐 A 女
婷姐 B 女
婷婷 B 女
創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;
按需求查詢數(shù)據(jù)
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
五.排序
全局排序（Order By）
Order By：全局排序茶袒，一個(gè)Reducer
1．使用 ORDER BY 子句排序
ASC（ascend）: 升序（默認(rèn)）
DESC（descend）: 降序
2．ORDER BY 子句在SELECT語(yǔ)句的結(jié)尾
3．案例實(shí)操
（1）查詢員工信息按工資升序排列
hive (default)> select * from emp order by sal;
（2）查詢員工信息按工資降序排列
hive (default)> select * from emp order by sal desc;
按照別名排序
按照員工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
多個(gè)列排序
按照部門和工資升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
每個(gè)MapReduce內(nèi)部排序（Sort By）
Sort By：每個(gè)Reducer內(nèi)部進(jìn)行排序，對(duì)全局結(jié)果集來說不是排序凉馆。
1．設(shè)置reduce個(gè)數(shù)
hive (default)> set mapreduce.job.reduces=3;
2．查看設(shè)置reduce個(gè)數(shù)
hive (default)> set mapreduce.job.reduces;
3．根據(jù)部門編號(hào)降序查看員工信息
hive (default)> select * from emp sort by empno desc;
4．將查詢結(jié)果導(dǎo)入到文件中（按照部門編號(hào)降序排序）
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'
select * from emp sort by deptno desc;
分區(qū)排序（Distribute By）
Distribute By：類似MR中partition薪寓，進(jìn)行分區(qū)，結(jié)合sort by使用澜共。
注意向叉，Hive要求DISTRIBUTE BY語(yǔ)句要寫在SORT BY語(yǔ)句之前。
對(duì)于distribute by進(jìn)行測(cè)試嗦董，一定要分配多reduce進(jìn)行處理母谎，否則無法看到distribute by的效果。
案例實(shí)操：
（1）先按照部門編號(hào)分區(qū)京革，再按照員工編號(hào)降序排序奇唤。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
Cluster By
當(dāng)distribute by和sorts by字段相同時(shí)，可以使用cluster by方式匹摇。
cluster by除了具有distribute by的功能外還兼具sort by的功能咬扇。但是排序只能是升序排序，不能指定排序規(guī)則為ASC或者DESC廊勃。
1）以下兩種寫法等價(jià)
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部門編號(hào)分區(qū)懈贺，不一定就是固定死的數(shù)值，可以是20號(hào)和30號(hào)部門分到一個(gè)分區(qū)里面去供搀。
