数据仓库---Hive（第一篇）

以上压缩格式中 ORC 和 Parquet 的综合性能突出，使用较为广泛，推荐使用这两种格式。

通常在创建表的时候使用 STORED AS 参数指定：

CREATE TABLE page_view(viewTime INT, userid BIGINT)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '01'
   COLLECTION ITEMS TERMINATED BY '02'
   MAP KEYS TERMINATED BY '03'
 STORED AS SEQUENCEFILE;

各个存储文件类型指定方式如下：

STORED AS TEXTFILESTORED AS SEQUENCEFILESTORED AS ORCSTORED AS PARQUETSTORED AS AVROSTORED AS RCFILE 四：数据库和数据库表的相关 *** 作： 4.1：数据库的相关 *** 作 4.1.1：创建数据库：

create database if not exists `数据库名`;

说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

hive.metastore.warehouse.dir
/user/hive/warehouse

创建数据库并指定hdfs存储位置

create database  if not exists`表名` localtion 'hdfs路径'; 
-- 例如：创建数据库myhive到hdfs的'/myhive2目录下：
create database if not exists `myhive` localtion '/myhive2'

desc databse `数据库名`;
-- 例如：查看数据库myhive的详细信息
desc database myhive;

删除一个空的数据库，如果数据库下边还有数据表，那么就会报错

drop database `数据库名`;

强制删除一个数据库，包含数据库下面的表一起删除

drop database `数据库名` cascade;

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] 
   [COMMENT table_comment] 
   [PARTITIonED BY (col_name data_type [COMMENT col_comment], ...)] 
   [CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

说明：

1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、LIKE允许用户复制现有的表结构，但是不复制数据。

4、ROW FORMAT DELIMITED 可用来指定行分隔符

5、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 来指定该表数据的存储格式，hive中，表的默认存储格式为TextFile。

6、CLUSTERED BY对于每一个表（table）进行分桶(MapReuce中的分区），桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN *** 作。对于JOIN *** 作两个表有一个相同的列，如果对这两个表都进行了桶 *** 作。那么将保存相同列值的桶进行JOIN *** 作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

7、LOCATION: 指定表在HDFS上的存储位置。

内部表又叫做管理表 (Managed/Internal Table)，创建表时不做任何指定，默认创建的就是内部表。想要创建外部表 (External Table)，则需要使用 External 进行修饰。 内部表和外部表主要区别如下：

-- 创建一个数据库myhive
create database `myhive`;
-- 使用myhive数据库
use myhive;
--创建一个stu的内部表
create table stu(id int,name string);
-- 查询这个内部表stu
select * from stu;

create table if not exists stu2(id int,name string)   
row format delimited fields terminated by 't'  --字段之间使用't'进行分隔
stored as textfile       -- 存储格式为textfile
location '/user/stu2';    --  HDFS上的存储路径为'/user/stu2'

create table stu3 as select * from stu2;

create table stu4 like stu2;

desc formatted stu2;

drop table stu2;

Load命令用于将外部数据加载到Hive表中
语法:

load data [local] inpath '/export/servers/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];

参数:
1、load data:表示加载数据

2、local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

3、inpath:表示加载数据的路径

4、overwrite:表示覆盖表中已有数据，否则表示追加

5、into table:表示加载到哪张表

6、student:表示具体的表

7、partition:表示上传到指定分区

分别创建老师与学生表外部表，并向表中加载数据

源数据如下:

student.txt

01 赵雷 1990-01-01 男 
02 钱电 1990-12-21 男 
03 孙风 1990-05-20 男 
04 李云 1990-08-06 男 
05 周梅 1991-12-01 女 
06 吴兰 1992-03-01 女 
07 郑竹 1989-07-01 女 
08 王菊 1990-01-20 女 

teacher.txt

01 张三 
02 李四 
03 王五 

1、创建老师表：

create external table teacher (t_id string,t_name string) 
row format delimited fields terminated by 't';

2、创建学生表：

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by 't';

3、从本地文件系统向表中加载数据

load data local inpath '/export/servers/hivedatas/student.txt' into table student;

4、加载数据并覆盖已有数据

load data local inpath '/export/servers/hivedatas/student.txt' overwrite  into table student;

5、从hdfs文件系统向表中加载数据
需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的 *** 作

cd /export/servers/hivedatas
hadoop fs -mkdir -p /hivedatas
hadoop fs -put teacher.txt /hivedatas/
load data inpath '/hivedatas/teacher.txt' into table teacher;

注意,如果删掉student表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了,因为我们的student表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了

desc formatted student;  
-- 从查到的信息中可以看到如下信息
Table Type:MANAGED_TABLE

alter table student set tblproperties('EXTERNAL'='TRUE');

desc formatted student;
-- 从查到的信息中可以看到如下信息（strudent就变成了外部表）
Table Type:EXTERNAL_TABLE

alter table student set tblproperties('EXTERNAL'='FALSE');

desc formatted student;  
-- 从查到的信息中可以看到如下信息
Table Type:MANAGED_TABLE

注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次 *** 作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去 *** 作小的文件就会容易得多了。

Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。

分区为 HDFS 上表目录的子目录，数据按照分区存储在子目录中。如果查询的 where 字句的中包含分区条件，则直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。

这里说明一下分区表并 Hive 独有的概念，实际上这个概念非常常见。比如在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时也可以对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。

通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。

创建分区表语法

create table score(s_id string,c_id string, s_score int)
partitioned by (month string) 
row format delimited fields terminated by 't';

创建一个表带多个分区

create table score2 (s_id string,c_id string, s_score int)
partitioned by (year string,month string,day string)
row format delimited fields terminated by 't';

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.txt' into table score partition (month='202006');

加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.txt' into table score2 partition(year='2020',month='06',day='01');

查看分区

show partitions score;

添加一个分区

alter table score add partition(month='202005');

同时添加多个分区

alter table score add partition(month='202004') partition(month = '202003');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

删除分区

alter table score drop partition(month = '202006');

需求描述：现在有一个文件score.txt文件，存放在集群的这个目录下/scoredatas/month=202006，这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除。

1、数据准备：

hadoop fs -mkdir -p /scoredatas/month=202006
hadoop fs -put score.txt/scoredatas/month=202006/

2、创建外部分区表，并指定文件数据存放目录

create external table score4(s_id string, c_id string,s_score int)
partitioned by (month string) 
row format delimited fields terminated by 't' 
location '/scoredatas';

2、进行表的修复,说白了就是建立我们表与我们数据文件之间的一个关系映射

msck repair table score4;

修复成功之后即可看到数据已经全部加载到表当中去了

第二种实现方式，上传数据之后手动添加分区即可
数据准备：

hadoop fs -mkdir -p /scoredatas/month=202005
hadoop fs -put score.txt/scoredatas/month=202005

修改表，进行手动添加方式

alter table score4 add partition(month='202005');

分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。

分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。

单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

当调用 HashMap 的 put() 方法存储数据时，程序会先对 key 值调用 hashCode() 方法计算出 hashcode，然后对数组长度取模计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图：

在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶列，并通过 SORTED BY 指定桶中数据的排序参考列。

开启hive的桶表功能

set hive.enforce.bucketing=true;

设置reduce的个数

set mapreduce.job.reduces=3;  

创建通表

create table course (c_id string,c_name string,t_id string) 
clustered by(c_id) into 3 buckets   -- 分桶字段为c_id，按照分桶字段分三个桶
row format delimited fields terminated by 't';

桶表的数据加载，由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不好使，只能通过insert overwrite

创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

创建普通表：

create table course_common (c_id string,c_name string,t_id string) 
row format delimited fields terminated by 't';

普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.txt' into table course_common;

通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);

分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例：

CREATE TABLE page_view_bucketed(
	viewTime INT, 
    userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING )
 PARTITIonED BY(dt STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '01'
   COLLECTION ITEMS TERMINATED BY '02'
   MAP KEYS TERMINATED BY '03'
 STORED AS SEQUENCEFILE;

此时导入数据时需要指定分区：

INSERT OVERWRITE page_view_bucketed
PARTITION (dt='2009-02-25')
SELECt * FROM page_view WHERe dt='2009-02-25';

源数据:
说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

zhangsan	  beijing,shanghai,tianjin,hangzhou
wangwu   	changchun,chengdu,wuhan,beijin

建表语句

create table hive_array(name string, work_locations array)
row format delimited fields terminated by 't'
COLLECTION ITEMS TERMINATED BY ',';

导入数据（从本地导入，同样支持从HDFS导入）

load data local inpath '/export/servers/hivedatas/work_locations.txt' overwrite into table hive_array;

常用查询：

-- 查询所有数据
select * from hive_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from hive_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from hive_array;
-- 查询location数组中包含tianjin的信息
select * from hive_array where array_contains(work_locations,'tianjin'); 

源数据:
说明：字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

建表语句

create table hive_map(
id int, name string, members map, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#' 
MAP KEYS TERMINATED BY ':'; 

导入数据

load data local inpath '/export/servers/hivedatas/hive_map.txt' overwrite into table hive_map;

常用查询

select * from hive_map;
select id, name, members['father'] father, members['mother'] mother, age from hive_map;
select id, name, map_keys(members) as relation from hive_map;
select id, name, map_values(members) as relation from hive_map;
select id,name,size(members) num from hive_map;
select * from hive_map where array_contains(map_keys(members), 'brother');
select id,name, members['brother'] brother from hive_map where array_contains(map_keys(members), 'brother');

源数据：
说明：字段之间#分割，第二个字段之间冒号分割

192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70

建表语句

create table hive_struct(
ip string, info struct
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

导入数据

load data local inpath '/export/servers/hivedatas/hive_struct.txt' into table hive_struct;

常用查询

select * from hive_struct;
select ip, info.name from hive_struct;

通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。

  CREATE EXTERNAL TABLE emp_skewed(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    SKEWED BY (empno) ON (66,88,100)  --指定 empno 的倾斜值 66,88,100
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "t"
    LOCATION '/hive/emp_skewed';   

临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：

不支持分区列；不支持创建索引。

  CREATE TEMPORARY TABLE emp_temp(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "t";

​ 基本语法：

alter table old_table_name rename to new_table_name;

– 把表score4修改成score5

alter table score4 rename to score5;

– 1:查询表结构

desc score5;

– 2:添加列

alter table score5 add columns (mycol string, mysco string);

– 3:查询表结构

desc score5;

– 4:更新列

alter table score5 change column mysco mysconew int;

– 5:查询表结构

desc score5;

drop table score5;

只能清空管理表，也就是内部表

truncate table score6;

SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERe where_condition]
[GROUP BYcol_list]
[HAVINg where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

解释:

1、ORDER BY用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。

2、sort by用于分区内排序，即每个Reduce任务内排序**。**，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、**distribute by(**字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、**cluster by(**字段) 除了具有Distribute by的功能外，还兼具sort by的排序功能。

因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by。

select * from score;

select s_id ,c_id from score;

select s_id as myid ,c_id from score;

1）求总行数（count）

 select count(1) from score;

2）求分数的最大值（max）

 select max(s_score) from score;

3）求分数的最小值（min）

 select min(s_score) from score;

4）求分数的总和（sum）

 select sum(s_score) from score;

5）求分数的平均值（avg）

 select avg(s_score) from score;

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

1）使用WHERe 子句，将不满足条件的行过滤掉。

2）WHERe 子句紧随 FROM 子句。

3）案例实 ***

查询出分数大于60的数据

select * from score where s_score > 60;

-- （1）查询分数等于80的所有的数据
select * from score where s_score = 80;
-- （2）查询分数在80到100的所有数据
select * from score where s_score between 80 and 100;
-- （3）查询成绩为空的所有数据
select * from score where s_score is null;
-- （4）查询成绩是80和90的数据
select * from score where s_score in(80,90);

-- （1）查找以8开头的所有成绩
select * from score where s_score like '8%';
-- （2）查找第二个数值为9的所有成绩数据
select * from score where s_score like '_9%';
-- （3）查找成绩中含9的所有成绩数据
select * from score where s_score rlike '[9]';		

select * from score where s_score >80 and s_id = '01';

select * from score where s_score > 80 or s_id = '01';

select * from score where s_id not in ('01','02');

select s_id ,avg(s_score) from score group by s_id;

select s_id ,max(s_score) from score group by s_id;

select s_id ,avg(s_score) from score group by s_id;

select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

select * from teacher t right join course c on t.t_id = c.t_id;

select * from teacher t inner join course c on t.t_id = c.t_id;

select * from teacher t left join course c on t.t_id = c.t_id;

SELECt * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

select * from teacher t 
left join course c 
on t.t_id = c.t_id
left join score s 
on s.c_id = c.c_id
left join student stu 
on s.s_id = stu.s_id;

SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

select s_id ,avg(s_score) avg from score group by s_id order by avg;

select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

 set mapreduce.job.reduces=3;

 set mapreduce.job.reduces;

 select * from score sort by s_score;

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

set mapreduce.job.reduces=7;

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

select * from score cluster by s_id; 
select * from score distribute by s_id sort by s_id;