hive笔记(二)
DML
插入
1.向表插入数据
load data [loacl] inpath ‘/usr/p/student.txt’ [overwrite]|into table student;(overwrite是覆盖，直接into是追加)
2.insert插入数数据并查询
insert overwrite|into table stu3 partition(month=‘202102’) select id, name from stu3 where month=‘202102’;
3.同表多插入
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’;
4.查询表并加载数据(查询的结果会添加到新创建的表中)
create table if not exists stu4 as select id, name from student;
5.Import数据到指定Hive表中
import table stu6 from ‘/user/hive/warehouse/test/student’;(先有导出（export）再有导入)
导出
1.将查询结果导出到本地
insert overwrite local directory ‘/usr/p/stu1’ select * feom student;
2．将查询的结果格式化导出到本地(导出hdfs是不加local)
insert overwrite local directory ‘/usr/p/stu1’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
select * from student;
3.通过hadoop命令导出到本地
dfs -get /user/hive/warehouse/stu4/000000_0 /usr/p/stu4
4. Hive Shell 命令导出
hive -e ‘select * from default.student;’ > /usr/p/stu4
5.Export导出到HDFS上
export table default.student to’/user/hive/warehouse/test/student’;
清空表数据
truncate table student;(Truncate只能删除管理表，不能删除外部表中数据)
查询
1.查询全表和特定列（像mysql一样可以起别名）
select |特定列 from 表
2.查询时做算术运算
select sal +1 from emp;
3.求总行数
select count() from emp;
4.求工资的最大值（max）
select max(sal) max_sal from emp;
5.求工资的最小值（min）
select min(sal) max_sal from emp;
6.求工资的总和（sum）
select sum(sal) max_sal from emp;
7.求工资的平均值（avg）
select avg(sal) avg_sal from emp;
8. Limit语句
select * from emp limit 5;
9.where语句
select * from emp where sal>2000
10.groupby 语句
select avg(sal) avg_sal from emp group by deptno
11.having 语句(having后面可跟分组语句，where后面不可)
select avg(sal) avg_sal from emp group by deptno having avg_sal>2000;
12.join等值连接，不支持非等值连接
select e.empno,e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
join连接问题
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno,e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
左外连接： JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno= d.deptno;
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
多表连接
select e.ename,d.dname,l.loc_name from emp e join dept d on e.deptno=d.deptno
join location l on d.loc=l.loc;
每个MapReduce内部排序（Sort By）
Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。
1．设置reduce个数
set mapreduce.job.reduces=3;
2．查看设置reduce个数
set mapreduce.job.reduces;
3据部门编号降序查看员工信息
select * from emp sort by empno desc;
4．将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory ‘/usr/p/student.txt’ select * from emp sort by deptno desc;
排序
select sal from emp order by sal(desc);
多个列排序
select * feom emp order by deptno,sal;
分区排序（Distribute By）
Distribute By：类似MR中partition，进行分区，结合sort by使用。Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
insert overwrite local directory ‘/usr/p/student.txt’ select * from emp distribute by deptno sort by empno desc;
Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC
select * from emp cluster by deptno;
分桶表的创建(分桶字段是建表之中的–按id分桶)
create table stu_buck(id int,name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by ‘\t’
分桶表直接插入是不成功的，因为它有两个默认的属性
load插入数据也是不成功的，这种方法是直接将文件上传到hdfs上，并没有进行分桶
select插入数据是可以的，不过要修改属性
要修改的属性：
set hive.enforce.bucketing=true;（是否分桶）
set mapreduce.job.reduces=-1;（不指定reduce的个数）
分桶抽样查询**（x<=y）**
select * from stu_buck tablesample(bucket 1 out of 4 on id);
tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
y：决定抽取桶的数量，是桶的倍数和因子，算法：表分桶数/y
x:决定从哪个桶开始
第二个为x+y个，第三个为x+y+y个
常用函数
1.NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
select nvl(comm,-1) from emp;
2.casewhen
case when then else end
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;
3.CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符()。分隔符
可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
4.EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
select movie,category_name from movie_info
lateral view explode(category) table_tmp as category_name;
5.窗口函数
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
