hive基础学习

1、创建数据库

create database mydb;

重点语句：

create database if no exists mydb;       
create database if no exists mydb location "/aa/bb";

在创建hive的库的时候，要注意：其实可以指定该数据库的数据存储目录在HDFS的那个目录

hive的底层数据/真实数据存储在hdfs上
hive的元数据/描述数据存储在mysql中

使用hive创建了一张表，这张表一定是属于一个数据库的，那么这张表当然也有自己的HDFS数据存储目录
创建表可以指定目录
创建库也可以指定目录

可以使用location的语法来指定创建库和创建表的时候的数据存储目录
使用频率很低

2、查询数据库

两个需求：
1、我想知道hive中到底有那些数据库？
2、我想知道某一个数据库的详细的信息？
3、这个数据库是怎么创建的呢

查询库列表：show databases;
查询库详细信息：desc database [extended] mydb;
查询建库的详细信息：show create database mydb;

关于location指定数据库和数据表的存储目录的问题：
规格：
hdfs_schema/hive的默认仓库路径/数据库.db/数据表/文件
hdfs://hadoop02:9000/user/hive/warehouse/myhive.db/student/student.txt

hdfs_schema                    hdfs://hadoop02:9000
hive的默认仓库路径            /user/hive/warehouse/
数据库.db                     /myhive.db/
数据表                        /student/
数据文件                           /student.txt

数据文件不一定是txt，可以任意格式的数据
1、普通文本文件
txt,csv,tsv 解析的时候记得指定行和列的分隔符

2、常见的行式，列式存储文件
parquet,rcfile,orcfile

3、企业常用
json

4、hadoop支持的数据格式
sequencefile

5、压缩文件
压缩普通文本文件

3、删除数据库

drop database mydb;

核心重点：

drop database if exists mydb;   
drop database if exists mydb [restrict|cascade];

是否级联删除
1、默认是：restrict 严格模式，不级联删除
如果库中有表存在，也不删除该库
2、casecade
如果这个数据库中还有数据表存在，但是也一并删除

4、先进入我们要操作的数据库/切换库

use mydb;

5、查看数据库里面的表

show tables;
show tables in mydb;

6、添加表 / 创建表

表有四种表:
1、内部表
2、外部表
3、分区表
4、分桶表

关系：
1、一张表如果是内部表了，就不是外部表
一张表如果是外部表了，就不是内部表
2、如果一张表是内部表，但是同时也可以是分区表和分桶表

总结：
1、普通的内部表
2、普通的外部包
3、普通的分区表
4、普通的分桶表
5、分区的内部表
6、分区的外部表
7、分桶的内部表
8、分桶的外部表
9、既分区，又分桶的内部表
10、既分区，又分桶的外部表

创建内部表（Managered_Table）

create table mingxing_mng(id int, name string, sex string, age int,department string) row format delimited fields terminated by ','; 

show create table mingxing;

创建外部表（External_Table）

create external table mingxing_ext(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',' location '/home/hadoop/hivedata';

注意：创建外部表的时候指定location的位置必须是目录，不能是单个文件

小总结：

1、创建内部表：

create table table_name ()

2、创建外部表：

create external table_name ()

怎么选择到底是创建内部表和外部表呢？
1、他们的区别
内部表删除的时候，会删除真实数据，和元数据
但是
外部表删除的时候，只会删除元数据，不会删除真实数据
2、如果现在有一份数据已经存在了HDSF上的某个目录，而且不是谁谁一个人在用
那么创建这个表的时候，要创建成外部表，而且创建表的时候要指定location

不管是内部表，还是外部表，数据都是存储在HDFS上的。不是在linux当中。

具体的案例：
1、创建内部表

create table mingxing(id int, name string, age int) row format delimited fields terminated by ",";

2、创建外部表
操作步骤：
先创建一个文件
再创建一个HDFS目录
上传这个文件到这个HDFS目录
再创建一个外部表来管理这个文件

create external table mingxing_ext(id int, name string, age int) row format delimited fields terminated by "," location "/aa/bb/cc/";

跟内部表对比：
1、在创建表的时候指定关键字： external
2、一般来说，创建外部表，都需要指定一个外部路径

不管是创建外部表还是内部表，都可以指定数据存储目录
默认的目录：
hdfs://hadoop02:9000/user/hive/warehouse/myhive.db/student/student.txt

为什么要有分区表和分桶表这两个概念呢？
分区表？用来提高效率的
如果现在有一张hive表，数据量非常大。
那么如果每次写一个SQL都只需要操作其中的一部分数据，但是又没有做明显的数据分区的话，就需要做一个全表扫描！

分区表就是按照某种规则，把一张表的所有的分区，分成几个子文件夹
每个子文件夹都管理这张表中的一部分数据

以后如果需要只操作其中的一部分数据，就可以避免全表扫描

启动HDFS的垃圾服务。如果不小心删掉了hive的一张内部表，数据是可以通过hdfs的垃圾服务找回来！
分区表，就是把一份很大的数据变成多份很小的数据，放置在多个不同的目录中进行存储

分桶表：
分桶表，也可以按照一定的规则把数据进行分散，这些分散了的数据，都是存储在不同的文件中，每个桶就是一个文件，但是这些桶文件都在这个表的目录中

分区表和分桶表，都是把一个大文件拆分成多个小文件来存储。到底怎么拆分是可以指定规则的
分区表，是把这些拆分出来的不同的文件，放置在不同的文件夹中
分桶表，是把这些拆分出来的不同的文件，直接放置在分桶表的目录中

分区表，和分通表在创建的时候，都得要指定分区字段/分桶字段
1、分区表：分区字段，必须不能是分区表中的字段
2、分桶表：分桶字段，必须是分桶表中的表字段

创建分区表

create table mingxing_ptn(id int, name string, sex string, age int, department string) partitioned by (city string) row format delimited fields terminated by ',';

注意：partitioned里的字段不是能是表中声明的字段,，必须是一个新字段
表字段
分区字段

如果往分区表中导入数据，一定要导入到这个分区表中的某一个分区中，而不是直接到入到表中
添加分区
查询某个表的分区有那些？
导入数据：

load data local inpath "/home/hadoop/student.txt" into table mingxing_ptn partition(city="beijing");

分区有手动手分区，也有自动分区
1、静态分区插入
2、动态分区插入
优势 劣势

创建分桶表

create table mingxing_bck(id int, name string, sex string, age int, department string) clustered by(id) sorted by(age desc) into 4 buckets row format delimited fields terminated by ',';

注意：clustered里的字段必须要是表字段中出现的字段
分桶字段
表字段
分桶字段和排序字段可以不一样，分桶字段和排序字段都必须是表字段中的一部分
分桶的原理和MapReduce的HashPartitioner的原理一致

为了减少全表扫描的次数，为了提高查询分析的效率
分桶表最大的作用：做join操作

做统计分析的时候join查询的出现频率非常高

7、删除表

drop table mingxing;
drop table if exists mingxing;

8、对表进行重命名

alter table mingxing rename to student;

9、对表的字段进行操作（增加add，删除drop，修改change，替换replace）

增加字段：

alter table mingxing add columns (province string);
alter table mingxing add columns (province string, salary bigint);

删除字段：

drop（不支持） XXXXX

修改字段：

alter table mingxing change age newage string;          // 修改字段的定义
alter table mingxing change age newage string after id;     // 修改字段的定义 + 顺序
alter table mingxing change age newage string first;        // 修改age字段为第一个字段

替换字段

alter table mingxing replace columns(id int, name string, sex string);  // 替换所有字段

10、对表中的分区进行操作

增加分区：

alter table mingxing_ptn add partition(city='beijing');
alter table mingxing_ptn add partition(city='beijing') partition(city='tianjin');
alter table mingxing_ptn add partition(city='beijing', email="abc@163.com");
alter table mingxing_ptn add partition(city='beijing', email="abc@163.com") partition(city='tianjin', email="cba@163.com");

删除分区：

alter table mingxing_ptn drop partition(city='beijing');
alter table mingxing_ptn drop partition(city='beijing'), partition(city='tianjin');
alter table mingxing_ptn add partition(city='beijing', email="abc@163.com");
alter table mingxing_ptn add partition(city='beijing', email="abc@163.com"), partition(city='tianjin', email="cba@163.com");

修改分区路径：

alter table mingxing_ptn partition(city="beijing") set location "/mingxing_beijing";

11、查询显示命令

查看库：show databases;
查看表：show tables;
查看建表完整语法：show create table mingxing_mng;
查看内置函数库：show functions;
查看函数的详细手册：desc function extended concat;
查看分区：show partitions mingxing_ptn;
查看表的字段：desc mingxing_mng;
查看表的详细信息：desc extended mingxing_mng;
查看表的格式化了之后的详细信息：desc formatted mingxing_mng;

12、load方式导入数据

导入本地相对路径的数据

load data local inpath './student.txt' into table mingxing;
load data local inpath './student.txt' overwrite into table mingxing;       //(覆盖导入)

导入本地绝对路径数据：

load data local inpath '/home/hadoop/hivedata/student.txt' into table mingxing;

导入HDFS上的简便路径数据：

load data inpath '/home/hadoop/hivedata/student.txt' into table mingxing;

导入HDFS上的全路径模式下的数据：

load data inpath'hdfs://hadoop01:9000/home/hadoop/hivedata/student.txt' into table mingxing;

导入本地数据和导入HDFS上的数据的区别：
1、导入HDFS上的数据到hive表，表示截切，移动
2、导入本地数据，相当于复制或者上传

13、利用insert关键字往表中插入数据

单条数据插入：

insert into table mingxing values(001,'huangbo','male',50,'MA');

单重插入模式： insert ... select ....

insert into table student select id,name,sex,age,department from mingxing;

注意：查询出的字段必须是student表中存在的字段

多重插入模式：

from mingxing
insert into table student1 select id,name,sex,age
insert into table student2 select id,department;

from mingxing2
insert into table student1 partition(department='MA') select id,name,sex ,age where department='MA'
insert into table student1 partition(department='CS') select id,name,sex ,age where department='CS'; 

静态分区插入：
需要手动的创建分区

alter table student add partition (city="zhengzhou")
load data local inpath '/root/hivedata/student.txt' into table student partition(city='zhengzhou');

动态分区插入：
打开动态分区的开关：

set hive.exec.dynamic.partition = true;

设置动态分区插入模式：

set hive.exec.dynamic.partition.mode = nonstrict

create table student(name string, department string) partitioned by (id int) .....

insert into table student partition(id) select name,department,id from mingxing2;

student表字段：name,department， 分区字段是id
查询字段是：name,department,id，分区字段
注意：动态分区插入的分区字段必须是查询语句当中出现的字段中的最后一个
CTAS(create table ... as select ...)(直接把查询出来的结果存储到新建的一张表里)

create table student as select id,name,age,department from mingxing;

注意：自动新建的表中的字段和查询语句出现的字段的名称，类型，注释一模一样

限制：
1、不能创建外部表
2、不能创建分区表
3、不能创建分桶表
分桶插入：
创建分桶表：

create table mingxing(id int, name string, sex string, age int, department string) 
clustered by(id) sorted by(age desc) into 4 buckets 
row format delimited fields terminated by ',';

插入数据：

insert into table mingxing select id,name,sex,age,department from mingxing2
distribute by id sort by age desc;

注意：查询语句中的分桶信息必须和分桶表中的信息一致

14、like关键字使用：复制表结构

create table student like mingxing;

15、利用insert导出数据到本地或者hdfs

单模式导出数据到本地：

insert overwrite local directory '/root/outputdata' select id,name,sex,age,department from mingxing;

多模式导出数据到本地：

from mingxing
insert overwrite local directory '/root/outputdata1' select id, name
insert overwrite local directory '/root/outputdata2' select id, name,age

简便路径模式导出到hdfs：

insert overwrite directory '/root/outputdata' select id,name,sex,age,department from mingxing;

全路径模式查询数据到hdfs：

insert overwrite directory 'hdfs://hadoop01:9000/root/outputdata1' select id,name,sex,age,department from mingxing;

local ：导出到本地目录
overwrite ：表示覆盖

16、清空数据库表中的数据

truncate table mingxing2;

17、select查询

order by : 全局排序
如果一个HQL语句当中设置了order by，那么最终在HQL语句执行过程中设置的
set mapreduce.job.reduces = 4 不起作用。

sort by ：局部排序 一般来说，要搭配 分桶操作使用 distribute by id sort by age desc;

distribute by : 纯粹就是分桶 在使用distribute by的时候：要设置reduceTask的个数

cluster by ： 既分桶，也排序 cluster by age = distribute by age sort by age;
distribute by age sort by age,id != cluster by age sort by id

cluster by 和 sort by 不能同时使用

18、join查询

限制:
支持 等值连接， 不支持 非等值连接
支持 and 操作， 不支持 or
支持超过2个表的连接

经验：
当出现多个表进行连接时，最好把小表放置在前面！！ 把大表放置在最后

join分类；
inner join
left outer join
right outer join
full outer join
left semi join
它是in、exists的高效实现

select a.*  from a left semi join b on a.id = b.id;
等价于：        
select a.* from a where a.id in (select b.id from b);