Hive DML学习

前任经验：有些东西要很清晰，不然面试的时候一不留神就挂了。

在关系型数据库里面，insert、update的情况是很多的，但是在大数据上，比如hive里，insert的情况是不多的，基本上都是load。把一个文件或者一批文件load到hive的表里面。

LOAD：

LOAD DATA  [LOCAL]  INPATH  'filepath'  [OVERWRITE]  INTO TABLE tablename  [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOCAL：表示从linux上加载数据  不选local表示从hdfs上加载数据

filepath：表示文件的路径

OVERWRITE ：表示是否覆盖表里的内容，不选overwrite表示，把数据追加到表里。

PARTITION ：分区

先创建一张表：

create table dept(
deptno int,
dname string,
location string
) row format delimited fields terminated by '\t';

然后加载数据到dept表里面：

LOAD DATA LOCAL INPATH '/home/hadoop/data/dept.txt' OVERWRITE INTO TABLE dept;
LOAD DATA LOCAL INPATH '/home/hadoop/data/dept.txt' INTO TABLE dept;
覆盖和追加

从上面可以看出没有overwrite和没有overwrite的区别。

下面试一下，从hdfs上加载数据到表里面：

先把数据放到hdfs上：

然后加载数据到dept表里面：

LOAD DATA INPATH '/hive/dept/dept.txt' OVERWRITE INTO TABLE dept;

然后再看hdfs上的dept.txt文件，发现没有了：

（它被移到dept这张表默认的hdfs的路径下了，这里是：hdfs://10-9-140-90:9000/user/hive/warehouse/d6_test.db/dept）

下面试一下，把hive上查询的结果写到linux本地或者hdfs上：

写到linux本地：

INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/data/emptmp'
row format delimited fields terminated by ','
SELECT empno,ename FROM emp;

写到hdfs上（把LOCAL去掉即可）：

INSERT OVERWRITE  DIRECTORY '/emptmp'
row format delimited fields terminated by ','
SELECT empno,ename FROM emp;

不进入hive直接查询表：

hive -e 和hive -f的使用：

hive -e "select * from d6_test.emp"   或者

hive -e "use d6_test; select * from emp"

这种不进入hive就可以查询表的情况适合？适合写脚本，比如写一个脚本，在里面写hive语句：

可以hive -f查看一下：看一下下面说明：

hive -f 的使用：

从上面可以看出，可以把一大堆sql写到一个sql文件里面，然后用hive -f去执行这个sql文件。

然后如果每天需要执行一次，crontab -e  ，添加一个计划即可。

hive里的清屏：!clear

聚合函数：max min sum count avg   多进一出

每个部门的平均工资
1) 拿到每个部分的信息
2) 在1)的基础之上求平均工资

select deptno,avg(sal) from emp group by deptno;

出现select中的字段要么出现在group by中，要么出现在聚合函数中

select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal >=2000;

case when then 常用于报表中：

select ename, sal, 
case 
when sal > 1 and sal <=1000 then 'lower'
when sal > 1000 and sal <=2000 then 'middle'
when sal > 2000 and sal <=3000 then 'high'
else 'highest' end
from emp;

（没有走MapReduce）

函数 build-in（内置的函数：hive本身自带的函数）     外置的函数：自定义的函数

看官网，在这里面可以找到相应的函数以及使用说明：

在hive里面使用： show functions;   可以看到hive所有内置的函数：

用 desc function 函数名称;   可以查看这个函数的相关说明：

create table dual(x string );

insert into table dual value('');（造数据）

select abs(-9) from dual;

如果再看详细点的信息，可以这样：desc function extended 函数名称;     有范例example：

（官网）

时间相关的函数：

当前时间：current_date

当前具体时间：current_timestamp

时间戳：unix_timestamp()

unix_timestamp()时间戳可以传入参数进行转换。比如：

 select unix_timestamp(('2019-03-09 13:41:15.841', 'yyyy-MM-dd hh:mm:ss') from dual;

（加10天，减10天）

求每个月月底：

其它函数：

round() 四舍五入

ceil(x)  取不小于x的最小整数

floor(x) 取不大于x的最大整数

substr()   取子字符串

concat() 连接多个字符串

concat_ws()  连接两个字符串（有分隔符）

split()   根据分隔符去拆分字符串

substr("abcdef",4)

 substr("abcdef",4,2)

concat("abc","def","ghijk") 

 concat_ws("---","abc","def","gh")（可以多个）

如果现在去解析，   ip:port  这个，需要把ip和port给分开。需要split()

split("192.168.11.11:8020",":")

split("192.168.11.11","\\.") （\\是转义字符）

create table hive_wc(sentence string);
load data local inpath '/home/hadoop/data/hive_wc.txt' into table hive_wc;

这表里面有三行，每一行都是一串字符串，现在想把它们分隔开：

select split(sentence,"\t") from hive_wc;

（每一行变成了数组形式）

现在已经把它们分隔开了，但是我们如果想要的结果是下面这样子：

hello
world
hello
hello
world
welcome
hello

这属于行转列、列转行。（一个非常经典的面试题目）

需要借助一个函数;explode

select explode(split(sentence,"\t")) from hive_wc;

再进行分组，count，就可以计算出wordcount了：

select word,count(1) from (select explode(split(sentence,"\t")) as word from hive_wc)t group by word;

select word, count(1) as count 
from
(select explode(split(sentence, "\t")) as word from hive_wc ) t
group by word
order by count desc;