一起学Hive-3

发表于

Hive进阶: 数据导入,查询, 内置函数, 表连接, 子查询.

数据导入

跟mysql类似:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
语法:
LOAD DATA [LOCAL] INPATH 'filepath'  [OVERWRITE]
INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ..)]

Local表示是否从本地文件中导入, 不加local表示从hdfs文件目录中导入.
Overwrite表示是否导入时, 将表中原来数据覆盖掉.
Partition之前讲过, 是否要导入分区表, 要导入到的话, 指明分区的条件.

将我们桌面的上student02.txt中的数据导入到hive表中.

先新建一个student04表, 并指明分割符为逗号.(要与导入文件的数据格式相同)
create table student04(sid int, sname string, sage int) row format delimited fields terminated by ',';

student04.txt表中的数据:
1,dottie,22
2,daejong,23
3,apple,10
load data local inpath '/Users/daejong/Desktop/student04.txt' into table student04

查看输出:
hive> load data local inpath '/Users/daejong/Desktop/student04.txt' into table student04;
Loading data to table default.student04
Table default.student04 stats: [numFiles=1, totalSize=36]
OK
Time taken: 0.582 seconds
hive> select * from student04;
OK
1	dottie	22
2	daejong	23
3	apple	10
Time taken: 0.138 seconds, Fetched: 3 row(s)

这样导入数据就成功了.

但是问题来了, 这种方式一次只能导入一个文件.
如果要将一个目录下所有文件一次都导入hive表中,并且覆盖之前的 该怎么做?
data目录下有student04.txt和student05.txt文件.
load data local inpath '/Users/daejong/Desktop/data/' overwrite into table student04;
输出:
hive> load data local inpath '/Users/daejong/Desktop/data/' overwrite into table student04;
Loading data to table default.student04
Table default.student04 stats: [numFiles=2, numRows=0, totalSize=74, rawDataSize=0]
OK
Time taken: 0.402 seconds
hive> select * from student04;
OK
1	dottie	22
2	daejong	23
3	apple	10
4	hello	23
5	world	24
6	helloworld	42
Time taken: 0.135 seconds, Fetched: 6 row(s)

这样就实现一次导入多个文件到hive数据表中.

当然也可以使用apache提供的Sqoop工具吗进行数据的批量导入导出.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sqoop.apache.org下载sqoop包

sqoop1.4.6

wget http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/sqoop-1.4.6.bin__hadoop-1.0.0.tar.gz

tar -zxvf 解压即可

再设置两个环境变量
export HADOOP_COMMON_HOME=/Users/daejong/Documents/java/hadoop-2.7.3
export HADOOP_MAPRED_HOME=/Users/daejong/Documents/java/hadoop-2.7.3

cd 到sqoop的bin目录下.
然后想要从哪个数据库导入之前就把该数据库的驱动的jar包放到sqoop的lib目录下,
然后使用sqoop/bin的sqoop命令就可以导入数据到hdfs中或者hive中即可.

本质也是将导入数据的过程 转成一个job提交到mapreduce执行.
具体导入的过程 看实际需要吧.

数据查询

在hive里面 select查询都是转换成一个mapreduce的作业执行, 当然了也会有少量的查询时不会转成mapreduce的作业而执行的. 如select * from table1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
查询的hql语句, 跟我们之前写的mysql的sql语句都类似.
select * from student;
select sname, sage from student;
select sname, sage * 12  from student;

注意空值.
在hive中 nvl(var1, 100) 函数
判断如果变量var1的值为null, 则输出100.
select sname, nvl(sage, 18) from student;
查询所有sname为null的数据
select * from student where sname is null; 即可
不可以这样写: where sname = null;

使用distinct去除重复的数据. 即: 名字可以能重名, 这里关键字可以去重.
select distinct sname from student;


使用fetch task提高查询速度.
在hive中输入
set hive.fetch.task.conversion=more
当然了, 也可以在hdfs-site.xml中配置.

然后再进行select查询, 就会打印出mapreduce的过程.
1
2
3
4
5
6
7
8
9
10
11
12
使用过滤进行查询:
1. 执行where的查询条件.
   select * from student where sid = 1;
   select * from student where sid < 10 and sage > 20;
   可以通过查看查询计划, 来查看具体的查找过程
   explain select * from student where sid = 1;
   模糊查询, 查询名字是以d开头的学生信息. %表示任意多字符. _表示任'一' 字符.
   select * from student where sname like 'd%';

对查出的信息进行排序
即: 对查出来的学生按照年龄降序排序. desc降序, asc升序
1. select * from student order by sage desc;

hive中内置函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
数学函数: round, ceil, floor.等等
round四舍五入:
select round(4.234, 2) 对4.234四舍五入并且保留两位小数.
ceil向上取整
floor向下取整


字符函数:
1. lower select lower('Hello World'), upper('hello world')
下面类似, 就不写了.
2. upper 全部转成大写
3. length 获取长度
4. concat 字符串拼接
5. substr 去子串substr('hello',2), substr('hello', 2, 2)
6. trim 去除前后空格
7. lpad 左填充 lpad('hello', 10, '*') hello字符串 指定10位, 左边不够用*填充.
8. rpad 有填充


转换函数cast 类型转换.
select cast(1 as float)
select cast('2018-01-12' as date)


日期函数: (常用, ****)
1. to_date 转换成一个日期. select to_date('2018-12-12 12:12:12') 返回2018-12-12
2. year 返回年
3. month 返回月
4. day 返回天
5. weekofyear 一个日期在一年中的哪个星期数
6. datediff 两个日期想减, 返回相差的天数
7. date_add 在一个日期上加上多少天
8. date_sub 在一个日期上减去多少天



条件函数: case when
case a when b then c when d then e else f end
等价于
if(a == b) return c
else if(a == d) return e
else return f
如:
select sname, sage,
      case sname  when 'daejong' then sage + 10
                  when 'dottie'  then sage + 1
                  else  sage + 100
      end
from student04;


查询之前:
hive> select * from student04;
OK
1	dottie	22
2	daejong	23
3	apple	10
4	hello	23
5	world	24
6	helloworld	42

查询之后:
hive> select sname, sage,
    >       case sname  when 'daejong' then sage + 10
    >                   when 'dottie'  then sage + 1
    >                   else  sage + 100
    >       end
    > from student04;
OK
dottie	22	23
daejong	23	33
apple	10	110
hello	23	123
world	24	124
helloworld	42	142
Time taken: 0.369 seconds, Fetched: 6 row(s)

发现查出来的数据已经发生了变化.



聚合函数
1. count
2. sum
3. min
4. max
5. avg

select count(*), sum(sage), max(sage), min(sage), avg(sage) from student;

表的连接

1
2
3
4
5
6
1. 等值连接 where t1.sid=t2.sid
2. 不等值连接 where t1.sid!=t2.sid
3. 外连接 select xxxx from t1 right outer join t2 on t1.sid = t2.sid
   左外连接, 左边的表中即使不成立的数据, 也要输出;
   右外连接, 右边的表即使有不成立的数据, 也要输出.
4. 自然连接: 将同一张表通过使用别名变成多张表, 然后进行连接查询.

子查询.

1
2
3
4
5
在hive只支持where和from语句子查询.
示例:
select * from t1 where t1.sname not in (select sname from t2)
但是如何子查询中有空值就不可以使用not in
所以要在子查询中添加select sname from t2 where sname is not null