Hive基础知识

一、metastore元数据存储对mysql的位置

DBS表：存储数据库信息(在hdfs上的存储路径)
TBLS表：存储表信息的
COLUMNS_V2表：存储表字段
hive建立一张表的内在机制：

1. 在mysql中记录这张表的定义；
2. 在hdfs中创建目录；
3. 只要把数据文件都到目录下，就可以在hive中进行查询了；
4. 因此，不同的hive只要是操作的同一个mysq，同一个hdfs集群，看到的数据是一致的；

二、hive使用方式

2.1 基本的使用方式

1. 让提示符显示当前库()：
   hive>set hive.cli.print.current.db=true;

2. 显示查询结果时显示表的字段名称：
   hive>set hive.cli.print.header=true;

以上设置都仅仅在该会话中有效，结束会话后就失效，解决方式：
在linux的当前用户主目录中，编辑一个.hiverc（隐藏文件）文件，将参数写入其中：
vi .hiverc（hive启动的时候会自动去当前用户目录下加载这个文件）

set hive.cli.print.header=true;
set hive.cli.print.current.db=true;

2.2 hive执行脚本

两个命令：
hive -e
hive -f
hive -e 使用方式 hive -e "insert into table t_dest select * from t_src;"在任何地方(没有进入hive会话的情况)
hive -f 使用情况 hive -f ftl.sql(当sql语句较多时，写入脚本中，运行脚本即可)

#!/bin/bash
hive -e "select * from db_order.t_order"
hive -e "select * from default.t_user"
hql="create table  default.t_bash as select * from db_order.t_order"
hive -e "$hql"

2.3 hive 使用

1、创建数据库
hive中有一个默认的库：
　　库名： default
　　库目录：hdfs://hdp20-01:9000/user/hive/warehouse

新建库：
　　create database db_order;
　　库名：库建好后，在hdfs中会生成一个库目录（库名.db）：
　　库目录：hdfs://hdp20-01:9000/user/hive/warehouse/db_order.db

2、建表
基本建表语句

use db_order;
create table t_order(id string,create_time string,amount float,uid string);

表建好后，会在所属的库目录中生成一个表目录
/user/hive/warehouse/db_order.db/t_order
只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

正确的建表语句为：

create table t_order(id string,create_time string,amount float,uid string)
row format delimited
fields terminated by ',';

这样就指定了，我们的表数据文件中的字段分隔符为 ","

3、删除表
drop table t_order;

删除表的效果是：
　　hive会从元数据库中清除关于这个表的信息；
　　hive还会从hdfs中删除这个表的表目录；

4、 外部表和内部表

• 内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中
• 外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定

create external table t_access(ip string,url string,access_time string)
row format delimited fields terminated by ',' location '/access/log';

外部表和内部表的特性差别：
　　1、内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定
　　2、drop一个内部表时：hive会清除相关元数据，并删除表数据目录
　　3、drop一个外部表时：hive只会清除相关元数据；
外部表的作用：对接最原始的数据目录，至于后面查询生成的新表，用内部表就好。一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；

5、分区表
分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。

创建带分区的表：

create table t_access(ip string,url string,access_time string)
partitioned by(dt string)
row format delimited
fields terminated by ',';

注意：分区字段不能是表定义中的已存在字段，否组会冲突；以为分区字段也会被当成数组字段值被返回，其实这是一个伪字段；

显示分区：
show partitioned t_access;

向分区中导入数据:
hive提供了数据导入命令load，导入的时候需要指定分区，如果不指定直接导入主目录下，本质同hadoop的hdfs上传文件是一样的。

load data local inpath '/root/access.log.2017-08-04.log' into table t_access partition(dt='20170804');

load data local inpath '/root/access.log.2017-08-05.log' into table t_access partition(dt='20170805');

多个分区字段：
建表：

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

导入数据：

load data local inpath '/root/p1.dat' into table t_partition partition(department='xiangsheng',sex='male',howold=20);

6、CTAS建表语法:

可以通过已存在表来建表：
create table t_user_2 like t_user;
新建的t_user_2表结构定义与源表t_user一致，但是没有数据

在建表的同时插入数据:
创建的表的字段与查询语句的字段是一样的

create table t_access_user 
as
select ip,url from t_access;

7、数据导入导出:

1. 导入数据的一种方式：
   手动用hdfs命令，将文件放入表目录；
2. 在hive的交互式shell中用hive命令来导入本地数据到表目录
   load data local inpath '/root/order.data.2' into table t_order;
3. 用hive命令导入hdfs中的数据文件到表目录
   load data inpath '/access.log.2017-08-06.log' into table t_access partition(dt='20170806');

将hive表中的数据导出到指定路径的文件
将hive表中的数据导入HDFS的文件:

insert overwrite directory '/root/access-data'
row format delimited fields terminated by ','
select * from t_access;

将hive表中的数据导入本地磁盘文件:
insert overwrite local directory '/root/access-data'
row format delimited fields terminated by ','
select * from t_access limit 100000;

三、Hive的数据模型

Hive支持多种基本数据类型，具体如下表：

类型	描述	示例
TINYINT	一字节整数， -128 ~ 127	12
SMALLINT	二字节整数，-32768 ~ 32767	255
INT/INTEGER	4字节整数 -2,147,483,648 ~ 2,147,483,647	2555
BIGINT	4字节整数，-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	-250 000 000 000
FLOAT	4字节单精度小数	3.1415
DOUBLE	8字节双精度小数	3.141529
DECIMAL	任意数字	10
STRING	字符串	"abc"
VARCHAR	字符串，字符串长度只能为1~65355	"abc"
CHAR	字符串，字符串长度只能为1~255	"abc"
TIMESTAMP	时间戳，格式为yyyy-mm-dd HH:mm:ss	2019-2-28 13:25:25
DATE	日期，格式为yyyy-mm-dd	2019-2-28
arrays	数组，ARRAY(data_type)
maps	键值对，Map(primitive_type, data_type)
structs	结构体，STRUCTcol_name : data_type)
union	联合体，UNIONTYPE(data_type, data_type)

3.1 array数组类型

数据 movies.dat：

战狼2,吴京:吴刚:龙母,2017-08-16
三生三世十里桃花,刘亦菲:痒痒,2017-08-20

创建表：

create table t_movie(moive_name string,actors array<string>,first_show date)
row format delimited fields terminated by ','
collection items terminated by ':';

导入数据：
load data local inpath '/root/movie.dat' into table t_movie;

数组查询:

select moive_name,actors[0] from t_movie;
-- xx演员参演的电影
select moive_name,actors from t_movie where array_contains(actors,'吴刚');
-- 每部电影有几个主演
select moive_name,size(actors) from t_movie;

3.2 map类型

数据：

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22

建表语句:

create table t_person(id int,name string,family_members map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

导入数据
load data local inpath '/root/hivetest/fm.dat' into table t_family;

查询：

-- 取map字段的指定key的值
select id,name,family_members['father'] as father from t_person;
-- 取map字段的所有key
-- 得到的是数组
select id,name,map_keys(family_members) as relation from t_person;
-- 取map字段的所有value
-- 得到的是数组
select id,name,map_values(family_members) from t_person;
-- 综合：查询有brother的用户信息
select id,name,father 
from 
(select id,name,family_members['brother'] as father from t_person) tmp
where father is not null;

3.3 struct类型

数据：

1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai

建表:

create table t_person_struct(id int,name string,info struct<age:int,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';

查询：
select id,name,info.age from t_person_struct;