3.Hive基本SQL操作

表结构

1,小明1,lol-book-movie,dalian:ganjingzi-shenyang:sujiatun
2,小明2,lol-book-movie,dalian:ganjingzi2-shenyang:sujiatun2

对于Hive多了以下几种类型

#基本数据类型
string

#复杂数据类型
#array和map要求数据类型一致
array	#集合
map		#map
struct	#相当于类

创建表（DDL）

自定义分隔符

create table psn(
    id int,
    name string,
    likes ARRAY<string>,
    address map<string,string>
)row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
--属性之间','分割
--集合之间'-'分割
--map之间':'分割

默认分隔符

#')'后面不写那些就是默认分隔符

#默认值
属性：^A
集合：^B
map：^C

分隔符从^A到^F共8个

#Linux查看文件所有信息（包括隐藏字符）
#这些隐藏字符在vi里输入，要按住Ctrl再按字符，表示Ascii码的意思
#例如vim中（Ctrl+A）A的Ascll码
cat -A filename

老的大数据公司你可能会见到
\001对应^A
\002对应^B
\003对应^C
老公司有这么用的，不过不要这么用

查看字段
desc psn;
查看详细信息
desc formatted psn;

内部表与外部表

--创建hive的外部表(需要添加external和location的关键字)
create external table psn4(
	id int,
	name string,
	likes array<string>,
	address map<string,string>
)row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/data/local';

--默认情况下是内部表

/*
 * 内部表跟外部表的区别：
 *     1.hive内部表创建的时候数据存储在hive的默认存储目录中，外部表在创建的时候需要制定额外的目录
 *     2.hive内部表删除的时候，会将元数据和数据都删除，而外部表只会删除元数据，不会删除数据
 */

--元数据在mysql的hive库里的TBLS表里能看见

/*
 * 应用场景:
 *     内部表:需要先创建表，再向表中添加数据，适合做中间表的存储。
 *     外部表：可以先创建表，再添加数据，也可以先有数据，再创建表，本质上是将hdfs的某一个目录的数据跟hive的表关联映射起来，因此适合原始数据的存储，不会因为误操作将数据给删除掉。
 */

分区表

hive默认将表数据保存在某个hdfs的存储目录下
当需要检索某些数据的时候，需要全量遍历数据，io量很大，效率低
因此可以采用分而治之的思想，将符合某些条件的数据放置在某一个目录
此时检索的时候只需要搜索指定目录即可，不需要全量遍历数据。

创建单分区表

create table psn5(
	id int,
	name string,
	likes array<string>,
	address map<string,string>
)
--gender会自动生成一个列
partitioned by(gender string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';


--插入数据的时候自动给gender列添加man值
--分区字段列必须有值
load data local inpath '/root/data/data.txt' into table psn partition(gender=man);

创建多分区表

--多分区就是多添加个字段
partitioned by(gender string,age int)

--实际上在HDFS中是在gender=man目录下创建了age=12目录
load data local inpath '/root/data/data.txt' into table psn partition(gender=man,age=12);

--分区字段不用按照顺序

单独操作分区表的值

--给分区表单独添加分区列的值
--partition()列数量要写全
alter table table_name add partition(gender=girl,age=12)

--删除分区列的值
--partition()列数量不用写全
--所有age=12的目录全部删除，如果是大目录，那么目录下的所有目录也一并级联删除
alter table table_name drop partition(age=12)

修复分区

我们手动创建目录、上传数据、并且创建外部表后，是查询不到数据的
因为mysql元数据里并没有更新分区表的元数据
所以我们要修复分区，更新mysql元数据，才能查询到数据

--我们进行如下操作

--在hdfs创建目录
/tongyuzhe/data/age=10
/tongyuzhe/data/age=20

--上传数据文件
/tongyuzhe/data/age=10/data.txt
/tongyuzhe/data/age=20/data.txt
	
--创建外部表
create external table psn7(
	id int,
	name string,
	likes array<string>,
	address map<string,string>
)
partitioned by(age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/tongyuzhe/data';
	
--查询结果（没有数据）
select * from psn7;
--修复分区
msck repair table psn7;
--查询结果（有数据）
select * from psn7;

动态分区

以上面的方式创建hive的分区表会存在问题，每次插入的数据都是人为指定分区列的值，我们更加希望能够根据记录中的某一个字段来判断将数据插入到哪一个分区目录下，此时利用我们上面的分区方式是无法完成操作			的，需要使用动态分区来完成相关操作，现在学的知识点无法满足，后续讲解。

写入数据

利用load写入数据

#local加上表示从本地上传，不加表示从hdfs移动
#overwrite加上表示覆盖，否则表示追加
load data local inpath '/root/data/data.txt' overwrite into table psn;

#1.load操作不会对数据做任何的转换修改操作
#2.从本地linux load数据文件是复制文件的过程
#3.从hdfs load数据文件是移动文件的过程
#4.load操作也支持向分区表中load数据，只不过需要添加分区列的值

实际上这些数据只是按照指定分隔符分割的文本文档，手动将指定文本格式的文档put到hdfs数据库目录下，select也可以读出数据。

查询数据插入结果表

--从表中查询数据插入结果表
INSERT OVERWRITE TABLE psn9 SELECT id,name FROM psn

--多表批量导入
--这种复杂语句企业用的非常的多
from psn
insert overwrite table psn9
select id,name 
insert into table psn10
select id

将查询到的结果插入到文件系统中

--注意：路径千万不要填写根目录，会把所有的数据文件都覆盖

#local加上表示从本地上传，不加表示从hdfs移动
#overwrite加上表示覆盖，否则表示追加

--将查询到的结果导入到hdfs文件系统中
insert overwrite directory '/result' select * from psn;

--将查询的结果导入到本地文件系统中
insert overwrite local directory '/result' select * from psn;

传统方式插入数据

--使用传统关系型数据库的方式插入数据，效率较低
insert into psn values(1,'zhangsan')

事务

--支持数据更新和删除
--不支持提交、回滚
--其实就是将文件取出来，修改好，删除源文件，重新写入回去

其他

/*
 * mysql是写时检查
 * hive是读时检查
 */

/*
 * 所有sql都可以转成mapreduce。
 * 不过hive内部有优化器，有些简单场景可以不通过mapreduce。
 */
 
/*
 * 题外话：大数据其实就是数据分析，提前将分析好的结果放到关系数据库内，再通过web读取sql进行展示，直接调取大数据是很慢的
 */