hive内部表，外部表，分区表，桶表，函数及自定义函数

Hive的概念：
Hive是大数据领域中的数据仓库的基础框架
Hive是一个SQL解析引擎
Hive操作方式：
hive -S -f hive-srcipt.hql > result.csv
hive -S -e "" > result.csv
Hive表的操作：
创建的方式和目前mysql一致
数据的加载方式：load data local inpath 'linux_path' into table tableName;
数据类型：
符合数据类型：
array ------>java中的array|arrayList
调用方式arr[index] index从0开始
map ------>java中的map
调用方式map["key"]
struct ------>java中的object
调用方式obj.property
分隔符：
行的默认分隔符：\n
列的默认分隔符：\001 ---->ctrl+v ctrl+a
array的默认分隔符：\002 ---->ctrl+v ctrl+b
map中的k和v之间的默认分隔符：\003 ---->ctrl+v ctrl+c

创建一张完整的包涵了各种数据类型的表，以及有相应自定义分隔符的表的定义：
create table emp(
empid int,
ename string,
salary float,
subordinate array<int>,
tax map<string, float>,
address struct<province:string, city:string, zip:int>
) row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

常见的分隔符：| , ## \t \001 \002 \003
不要出现：& $

两种数据加载的模式：
读模式
写模式

hive采用的是读模式
-----------------------------------------------------------------------------------------------
Hive表的分类
分为两种表：内部表和外部表
分为两种功能表：分区表和桶表

内部表
所谓的内部表，指的是表的数据的存在与否受到了表定义(元数据)的影响，当删除表定义的时候，表中的数据随之一并被删除。
内部表，MANAGED_TABLE，也罢内部表称之为受控表
外部表
所谓的外部表，指的是表的数据的存在不受表定义(元数据)的影响，当删除表定义的时候，表中的数据依然存在，表中的数据
相当于是表对数据的一个引用，当删除表的时候，删除了表和数据之间的一个引用关系。
外部表，EXTERNAL_TABLE
外部表的DDL：
create external table t5_external(
id int
);
删除外部表
drop table t5_external;
加载数据：
（不建议）load data local inpath '/opt/data/hive/hive-t5-external.txt' into table t5_external;
引用的方式：
alter table t5_external set location 'hdfs_path';
eg:
alter table t5_external set location '/input/hive';
在创建表的时候指定数据：
create external table t5_external_1(
id int
) location '/input/hive';
作用：
可以提高数据的安全性
通过对数据的引用，达到多部门共同使用同一份数据，而不用做冗余
在hive和外部框架进行集成的时候，多用外部表去关联操作相应框架，比如hbase
内部表和外部表之间进行转换：
内部表----->外部表
alter table t set tblproperties('EXTERNAL'='TRUE');
外部表----->内部表
alter table t set tblproperties('EXTERNAL'='FALSE');
分区表：
为了提高单表的处理能力，满足只操作一部分数据的需求，而不全量进行操作，所以产生了分区表。
分区需要指定分区字段及其对应的分区值。
DDL
创建：
create table t6_partition(
id int,
name string,
birthday date,
online boolean
) partitioned by(dt date comment "partition field day time");

查看分区：
show partitions t6_partition;
增加分区：
alter table t6_partition add partition(dt="2017-07-20");
删除分区：
alter table t6_partition drop partition(dt="2017-07-20");
DML
加载数据：
load data local inpath '/opt/data/hive/hive-t6-partion.txt' into table t6_partition partition(dt='2017-07-19');
DQL
查询操作和之前的一模一样，将分区字段作为表中的一列来进行对待即可。
如果有多个统计维度的时候，可以采用多个分区来设置
create table t6_partition_1(
id int,
name string,
birthday date,
online boolean
) partitioned by(year int, class string);


桶表：（分区表搞不定分区数据量大小相差不大）（本地模式不起作用）
在hive中，将数据进行打散的另一种方式就是分桶，将数据打散到一个个的文件，这一个个文件称
之为桶文件，在创建表的时候，需要预先设定需要几个桶来进行存储数据，同时需要明确指定是按照哪
一个字段进行分桶操作。本地模式不起作用
DDL：
create table t7_bucket(id int) clustered by (id) into 3 buckets;
查看信息：desc extended t7_bucket
DML:
加载数据：
不要执行load
字段类型要一致
insert into t7_bucket select id from t5_external_1;
作用：（图）
提高多表关联的查询效率
做数据的抽样采集
视图：
创建一个视图：
create view t8_view as select * from t1;
执行查询操作和操作table一样的

复制一张表(复制的表的结构)：
create table tt like t1;
Hive的数据加载方式总结
[]==>可选，<> ==>必须
加载
load
load data [local] inpath 'path' [overwrite] into table [partition_psc];
local：
有==>从linux本地加载数据
无==>从hdfs加载数据，相当于h7dfs dfs -mv 移动数据
overwrite
有==>覆盖掉表中原来的数据
无==>在原来的基础上追加新的数据
从其他表加载
insert <overwrite|into> t_des select [...] from t_src [...];
overwrite
有==>覆盖掉表中原来的数据
无==>在原来的基础上追加新的数据
==>会转化成为MR执行
需要注意的地方：t_des中列要和select [...] from t_src这里面的[...]一一对应起来。
创建表的时候加载
create table t_des as select [...] from t_src [...];
这样会创建一张表，表结构为select [...] from t_src中的[...]

动态分区的加载
快速复制表结构
create table t_d_partition like t_partition_1;
hive (default)> show partitions t_partition_1;
OK
partition
year=2015/class=bigdata
year=2015/class=linux
year=2016/class=bigdata
year=2016/class=linux
要将2016的数据都到入到t_d_partition的相关的分区中
insert into table t_d_partition partition(class, year=2016) select id, name, class from t_partition_1 where year=2016;
要将t_partition_1中所有数据都到入到t_d_partition的相关的分区中
insert overwrite table t_d_partition partition(year, class) select id, name, year, class from t_partition_1;
从hdfs上面删除的数据，并没有删除表结构，我们show partitions t_d_partition;是从metastore中查询出来的内容，如果你
之手动删除的hdfs上面数据，它的元数据信息依然在。
需要将hive.exec.dynamic.partition.mode设置为nonstrict
<property>
<name>hive.exec.max.dynamic.partitions</name>
<value>1000</value>
<description>Maximum number of dynamic partitions allowed to be created in total.</description>
</property>
import
import table stu from '/data/stu';
导出
1°、在hdfs的直接上操作
hadoop fs -cp src_uri dest_uri
或者
hive> export table tblName to 'hdfs_uri';
2°、在终端使用directory
insert overwrite [local] directory 'linux_fs_path' select ...from... where ...;
Hive的函数&自定义函数
查看Hive提供的函数
show functions;
查看具体的函数：
desc function [extended] func_name;
count
if
case when
split
explode
row_number
collect_set

使用case when对结果进行分类输出
select id, case id
when 1 then "TEACHER"
when 2 then "STUDENT"
when 3 then "SALER"
when 4 then "OFFICER"
else "OTHER" end
from t7_bucket;
使用hql编写wordcount的案例(alter table t rename to t_wc;修改表名)
列名line
hello you
hello me
hello xiaomei

---->
count(1) group by line
---->
hello
you
hello
me
hello
xiaomei
------------
hello you.split(" ") ---->["hello", "you"]
---->hello
you
step 1.将表中某一列的每一行数据使用split函数切割成一个个的单词
select split(line, " ") from t_wc;
["hello","you"]
["hello","xiao","mei"]
["mei","i","hate","you"]
step 2.需要将这个数组中的数据，立起来，相当于行列的转置
select explode(split(line, " ")) as word from t_wc;
hello
you
hello
xiao
mei
mei
i
hate
you
step 3.在step2的基础之上统计，每一个单词出现的次数
select w.word, count(w.word) as count
from (select explode(split(line, " ")) as word from t_wc) w
group by w.word
order by count desc;
在工作过程中，尽量少用order by进行排序，
sort by column ---->按照column进行局部排序
distribute by column 按照某一列进行分开
distribute by column sort by column;这个就和order by是等效的
-----上述的简写方式
cluster by column;
说明这种排序默认就是asc，不支持在最后面跟asc和desc
对表中的数据进行分组排序
row_number
根据员工、部分、薪资，这三张表，
1、分组显示每一个部分员工的信息（启动显示部分名称，员工姓名，员工性别[男|女]，员工薪资），同时分组按照员工薪资降序排序
select
e.name, if(sex == 0, '女', '男') as gender, d.name, s.salary,
row_number() over(partition by e.deptid order by s.salary desc) rank
from t_dept d
left join t_employee e on d.id = e.deptid
left join t_salary s on e.id = s.empid
where s.salary is not null;
2、获取显示部门薪资top2的员工信息
select
tmp.*
from
(select
e.name, if(sex == 0, '女', '男') as gender, d.name, s.salary,
row_number() over(partition by e.deptid order by s.salary desc) rank
from t_dept d
left join t_employee e on d.id = e.deptid
left join t_salary s on e.id = s.empid
where s.salary is not null) tmp
where tmp.rank < 3;
当Hive提供的函数满足不了业务需求的时候，就需要自定义函数来满足业务需要(UDF User Define Function, UDTF User Define Table Function, UDAF User Define Aggregation Function)。
UDF:一个输入，一个输出，比如year，date_sub
UDAF：多个输入，一个输出，比如count，sum，avg
UDTF：操作行列，比如explode
步骤：
1、编写一个类继承UDF
2、覆盖其中的evaluate()方法
3、打成jar包，上传到部署hive的及其上面
4、添加进hive的运行时classpath
hive> add jar /opt/jars/hive/zc.jar
5、创建一个临时函数并调用之
create temporary function z_c as 'com.uplooking.bigdata.udf.ZodiacAndConstellationUDF';
6、销毁临时函数(只在当前会话hive客户端有效)---》一般

创建永久函数：extends UDFE ，在相应地方进行注册 hive项目重新编译打包。
maven依赖：
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<hive-api.version>2.1.0</hive-api.version>
<hadoop-api.version>2.6.4</hadoop-api.version>
<hadoop-core.version>1.2.1</hadoop-core.version>
</properties>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>${hadoop-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-mapreduce-client-core</artifactId>
<version>${hadoop-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-core</artifactId>
<version>${hadoop-core.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-serde</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-service</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-metastore</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-common</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-cli</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>${hive-api.version}</version>
</dependency>
<dependency>
<groupId>org.apache.thrift</groupId>
<artifactId>libfb303</artifactId>
<version>0.9.0</version>
</dependency>
</dependencies>
Hive的JDBC
6个步骤：
1、注册驱动
2、获取Connection
3、获得执行器Statment
4、执行SQL
5、封装结果集ResultSet
6、关闭资源(rs.close(), st.close(), con.close())
小的操作技巧：
在hive终端可以执行hdfs shell 和linux的命令：
hdfs shell操作方式：
hive> dfs xxxx;
eg. hive> dfs -ls /input;
linux命令：
hive> ! linux_cmd;
eg.hive> !ls;