大数据开发复习Hive篇_hive sqlserver

Hive

4.1、hive的介绍

​ Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

​ 本质是将SQL转换为MapReduce程序。

​ 主要用途：用来做离线数据分析，比直接用MapReduce开发效率更高。

4.2、hive的架构

用户接口：包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

元数据存储：通常是存储在关系数据库如 mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

解释器、编译器、优化器、执行器:完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。

4.3、Hive 数据模型

Hive中所有的数据都存储在HDFS中，没有专门的数据存储格式

在创建表时指定数据中的分隔符，Hive 就可以映射成功，解析数据。

Hive中包含以下数据模型：

**db：**在hdfs中表现为hive.metastore.warehouse.dir目录下一个文件夹

**table：**在hdfs中表现所属db目录下一个文件夹

**external table：**数据存放位置可以在HDFS任意指定路径

**partition：**在hdfs中表现为table目录下的子目录

**bucket：**在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

4.4、常用操作

4.4.1、数据库相关

Hive配置单元包含一个名为 default 默认的数据库.

• —创建数据库

create database [if not exists] ；

• –显示所有数据库

show databases;

• –删除数据库

drop database if exists [restrict|cascade];

​ 默认情况下，hive不允许删除含有表的数据库，要先将数据库中的表清空才能drop，否则会报错
–加入cascade关键字，可以强制删除一个数据库

hive> drop database if exists users cascade;
1

• –切换数据库

use ;

4.4.2、内部表外部表

建内部表
create table 
student(Sno int,Sname string,Sex string,Sage int,Sdept string) 
row format delimited fields terminated by ',';
建外部表
create external table 
student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) 
row format delimited fields terminated by ',' location '/stu';
1
2
3
4
5
6
7
8

内、外部表加载数据：
load data local inpath '/root/hivedata/students.txt' overwrite into table student;

load data inpath '/stu' into table student_ext;
1
2
3
4

4.4.3、创建分区表

• 分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
• 单分区建表语句

create table day_table (id int, content string) partitioned by (dt string);
单分区表，按天分区，在表结构中存在id，content，dt三列。
1
2

• 双分区建表语句

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);
双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
1
2

 导入数据
 load data local inpath '/root/hivedata/dat_table.txt' into table day_table partition(dt='2017-07-07');
 
 load data local inpath '/root/hivedata/dat_table.txt' into table day_hour_table partition(dt='2017-07-07', hour='08');
 
 基于分区的查询：

 SELECT day_table.* FROM day_table WHERE day_table.dt = '2017-07-07';

 查看分区
 show partitions day_hour_table;  

总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。
1
2
3
4
5
6
7
8
9
10
11
12
13

• 指定分隔符

  —指定分隔符创建分区表

  create table day_table (id int, content string) partitioned by (dt string) row format delimited fields terminated by ',';
  1

  —复杂类型的数据表指定分隔符

  数据如下

  zhangsan	beijing,shanghai,tianjin,hangzhou
  wangwu	shanghai,chengdu,wuhan,haerbin
  1
  2

  建表语句

  create table
  complex_array(name string,work_locations array<string>) 
  row format delimited fields terminated by '\t' 
  collection items terminated by ',';
  1
  2
  3
  4

4.4.4、增删分区

• 增加分区

alter table t_partition add partition (dt='2008-08-08') location 'hdfs://node-21:9000/t_parti/';

执行添加分区  /t_parti文件夹下的数据不会被移动。并且没有分区目录dt=2008-08-08 
1
2
3

• 删除分区

alter table t_partition drop partition (dt='2008-08-08');

执行删除分区时/t_parti下的数据会被删除并且连同/t_parti文件夹也会被删除

注意区别于load data时候添加分区:会移动数据 会创建分区目录
1
2
3
4
5

4.4.5、hive中的join

准备数据
1,a
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp



建表：
create table a(id int,name string)
row format delimited fields terminated by ',';

create table b(id int,name string)
row format delimited fields terminated by ',';

导入数据：
load data local inpath '/root/hivedata/a.txt' into table a;
load data local inpath '/root/hivedata/b.txt' into table b;


实验：
** inner join
select * from a inner join b on a.id=b.id;

+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 7     | y       | 7     | yy      |
+-------+---------+-------+---------+--+





**left join   
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
+-------+---------+-------+---------+--+





**right join
select * from a right join b on a.id=b.id;

select * from b right join a on b.id=a.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 7     | y       | 7     | yy      |
| NULL  | NULL    | 9     | pp      |
+-------+---------+-------+---------+--+




**
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
| NULL  | NULL    | 9     | pp      |
+-------+---------+-------+---------+--+


**hive中的特别join
select * from a left semi join b on a.id = b.id;

select a.* from a inner join b on a.id=b.id;
+-------+---------
| a.id  | a.name  
+-------+---------
| 2     | b       
| 3     | c       
| 7     | y       
+-------+---------
相当于
select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率极低

select a.id,a.name from a join b on (a.id = b.id);

select * from a inner join b on a.id=b.id;


cross join（##慎用）
返回两个表的笛卡尔积结果，不需要指定关联键。
select a.*,b.* from a cross join b;
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

4.4.6、json解析

1、先加载rating.json文件到hive的一个原始表 rat_json
样例：{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}

create table rat_json(line string) row format delimited;
load data local inpath '/root/hivedata/rating.json' into table rat_json;

2、需要解析json数据成四个字段，插入一张新的表 t_rating
drop table if exists t_rating;
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';

3、json表数据解析到rating表中
insert overwrite table t_rating
select 
get_json_object(line,'$.movie') as moive,
get_json_object(line,'$.rate') as rate,
get_json_object(line,'$.timeStamp') as timestring, get_json_object(line,'$.uid') as uid 
from rat_json limit 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.5、常用函数

4.5.1、数值函数

• 指定精度取整函数 : round

  语法: round(double a, int d)

  返回值: DOUBLE

  说明: 返回指定精度d的double类型

  举例：

  hive> select round(3.1415926,4) from dual;
  
  3.1416
  1
  2
  3

• 向下取整函数 : floor

  语法: floor(double a)

  返回值: BIGINT

  说明: 返回等于或者小于该double变量的最大的整数

  举例：

  hive> select floor(3.1415926) from dual;
  3
  
  hive> select floor(25) from dual;
  25
  1
  2
  3
  4
  5

• 向上取整函数 : ceil

  语法: ceil(double a)

  返回值: BIGINT

  说明: 返回等于或者大于该double变量的最小的整数

  举例：

  hive> select ceil(3.1415926) from dual;
  4
  
  hive> select ceil(46) from dual;
  46
  1
  2
  3
  4
  5

• 取随机数函数 : rand

  语法: rand(),rand(int seed)

  返回值: double

  说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

  举例：

  hive> select rand() from dual;
  
  0.5577432776034763
  1
  2
  3

• 绝对值函数 : abs

  语法: abs(double a) abs(int a)

  返回值: double int

  说明: 返回数值a的绝对值

  举例：

  hive> select abs(-3.9) from dual;
  3.9
  
  hive> select abs(10.9) from dual;
  10.9
  1
  2
  3
  4
  5

4.5.2、日期函数

• to_date(string timestamp):返回时间字符串中的日期部分,
  • 如to_date(‘1970-01-01 00:00:00’)=‘1970-01-01’
• current_date:返回当前日期
• year(date)：返回日期date的年,类型为int
  • 如year(‘2019-01-01’)=2019
• month(date)：返回日期date的月,类型为int,
  • 如month(‘2019-01-01’)=1
• day(date): 返回日期date的天,类型为int,
  • 如day(‘2019-01-01’)=1
• weekofyear(date1)：返回日期date1位于该年第几周。
  • 如weekofyear(‘2019-03-06’)=10
• datediff(date1,date2):返回日期date1与date2相差的天数
  • 如datediff(‘2019-03-06’,‘2019-03-05’)=1
• date_add(date1,int1):返回日期date1加上int1的日期
  • 如date_add(‘2019-03-06’,1)=‘2019-03-07’
• date_sub(date1,int1):返回日期date1减去int1的日期
  • 如date_sub(‘2019-03-06’,1)=‘2019-03-05’
• months_between(date1,date2):返回date1与date2相差月份
  • 如months_between(‘2019-03-06’,‘2019-01-01’)=2
• add_months(date1,int1):返回date1加上int1个月的日期，int1可为负数
  • 如add_months(‘2019-02-11’,-1)=‘2019-01-11’
• last_day(date1):返回date1所在月份最后一天
  • 如last_day(‘2019-02-01’)=‘2019-02-28’
• next_day(date1,day1):返回日期date1的下个星期day1的日期。day1为星期X的英文前两字母
  • 如next_day(‘2019-03-06’,‘MO’) 返回’2019-03-11’
• **trunc(date1,string1)
  声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/码创造者/article/detail/776693