Hive基础知识整理、简单的键分区表导入数据示例

一、Hive介绍

1.hive功能介绍

hive是基于Hadoop的数据仓库解决方案，他将结构化数据、
半结构化数据甚至是非结构化数据映射成为数据库表。
hive提供了类似sql的查询语句HQL。他是Apache的顶级项目之一。

2.hive与MapReduce的介绍

• MapReduce的执行效率更快，相比之下，hive要先将查询语句转成MapReduce所以运行速度相对变慢。
• Hive开放效率更快。

3.Hive的优势和特点

• 提供了一个简单的优化模型
• HQL类SQL语法，简化MR开发
• 支持在不同的计算框架上运行
• 支持在HDFS和HBase上临时查询数据
• 支持用户自定义函数、格式
• 常用于ETL操作和BI
• 稳定可靠（真实生产环境）的批处理
• 有庞大活跃的社区

4.Hive元数据管理

• 记录数据仓库中模型的定义、各层级间的映射关系
• 存储在关系数据库中
  • 默认Derby, 轻量级内嵌SQL数据库
    • Derby非常适合测试和演示
    • 存储在.metastore_db目录中
• 实际生产一般存储在MySQL中
  • 修改配置文件hive-site.xml
• HCatalog
  • 将Hive元数据共享给其他应用程序

二、hive操作

• hive有两种客户端工具一种是beeline，另一种是hive。
  对于beeline工具来说，使用前要启动hive --service hiveserver2 &
  进入beeline输入：!connect jdbc:hive2://hadoop101:10000
• hive操作有两种模式：命令行模式、窗口交互模式
  一般来说，命令行模式用的较少。

1.Hive操作-命令行模式

操作	HiveServer2 Beeline	HiveServer1 CLI
Server Connection	beeline –u < jdbcurl> -n < username> -p < password>	hive –h < hostname> -p < port>
Help	beeline -h or beeline --help	hive -H
Run Query	beeline -e < query in quote> beeline -f < query file name>	hive -e < query in quote> hive -f < query file name>
Define Variable	beeline --hivevar key=value	hive --hivevar key=value

2.Hive操作-交互模式

操作	HiveServer2 Beeline	HiveServer1 CLI
进入方式	beeline	hive
连接	!connect < jdbcurl>	N/A
List Tables	!table	show tables;
List Columns	!column < table_name>	desc table_name;
Save Result	!record < file_name> !record	N/A
Run Shell CMD	!sh ls	!ls;
Run DFS CMD	dfs -ls	dfs -ls ;
Run SQL File	!run < file_name>	source <file_name>;
Check Version	!dbinfo	!hive --version;
Quit Mode	!quit	quit;

三、hive数据类型

1.hive的基本数据类型

hive的基本数据类型与sql的数据类型基本一致，差别很小，例如SQL中varchar表示字符串，在hive中直接使用的string，与java类似，但是hive中也有varchar。

类型	示例	类型	示例
TINYINT	10	SMALLINT	10
INT	10	BIGINT	100L
FLOAT	1.342	DOUBLE	1.234
DECIMAL	3.14	BINARY	1010
BOOLEAN	TRUE	STRING	‘Book’ or “Book”
CHAR	‘YES’ or “YES”	VARCHAR	‘Book’ or “Book”
DATE	‘2013-01-31’	TIMESTAMP	‘2020-01-31 00:13:00.345’

2. hive的集合数据类型

hive还存在sql中所不存在的类型，叫做集合数据类型

• ARRAY：存储的数据为相同类型
• MAP：具有相同类型的键值对
• STRUCT：封装了一组字段

3.hive的数据结构

数据结构	描述	逻辑关系	物理存储（HDFS)
Database	数据库	表的集合	文件夹
Table	表	行数据的集合	文件夹
Partition	分区	用于分割数据	文件夹
Buckets	分桶	用于分布数据	文件
Row	行	行记录	文件中的行
Columns	列	列记录	每行中指定的位置
Views	视图	逻辑概念，可跨越多张表	不存储数据
Index	索引	记录统计数据信息	文件夹

四、数据表

介绍：

 hive数据表分为内部表和外部表
 内部表：HDFS中为所属数据库目录下的子文件夹，重要的是表中数据完全有hive管理，
 删除表（元数据）会删除数据
 外部表：数据保存在指定位置的HDFS路径中。
 Hive不完全管理数据，删除表(元数据)不会删除数据。

创建一个内部表的见表语句

create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'  --按制表符切割
stored as textfile				--文件存储格式
location '指向数据所在目录';

创建一个外部表，主要就关键字不同，但是创建的表的含义大有区别。

create external table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'  --按制表符切割
stored as textfile				--文件存储格式
location '指向数据所在目录';

创建分区表

create table dept_partition(
deptno int, 
dname string,
loc string )
partitioned by (month string)
row format delimited fields terminated by '\t';

静态分区操作

-- 添加分区
alter table dept_partition add partition(month='201906') ;
alter table dept_partition add partition(month='201905') partition(month='201904');
-- 删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');

Hive建表高阶语句

• CTAS – as select方式建表

CREATE TABLE ctas_employee as SELECT * FROM employee;

注释：

1.上面语句表示的是先查询，把查询的结果作为一张表建立。
2. CTAS不能创建partition, external, bucket table。

• CTE (CTAS with Common Table Expression)

CREATE TABLE cte_employee AS
WITH 
r1 AS  (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS  (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS  (SELECT name FROM employee  WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;

• like

CREATE TABLE employee_like LIKE employee;

表操作：

• 删除表

DROP TABLE IF EXISTS employee [With PERGE];
TRUNCATE TABLE employee; -- 清空表数据

• 修改表（Alter针对元数据）

ALTER TABLE employee RENAME TO new_employee;
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name, comments');
ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$');
ALTER TABLE c_employee SET FILEFORMAT RCFILE; -- 修正表文件格式
-- 修改表的列操作
ALTER TABLE employee_internal CHANGE old_name new_name STRING; -- 修改列名
ALTER TABLE c_employee ADD COLUMNS (work string); -- 添加列
ALTER TABLE c_employee REPLACE COLUMNS (name string); -- 替换列

• Hive分区操作 - 动态分区

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

五、数据从本地导到hdfs中的外部表，再从外部表传到内部分区表。

思路：

1. 数据获取：

• 从前端页面获取数据，可以用ajax进行获取。ajax相比于用表单获取数据，表单是需要用户提交的，ajax可以记录更加迅速，比如点击记录，等。
• 日志文件也是获取的数据。日志是半结构化数据。半结构化数据就是类似于结构化但不是结构化，像日志，基本上来说每一行的格式基本相同，但是他列于列之间并没有结构。
• 当然也可以通过一些技术手段去爬取数据，通过分析加工得到想要的数据。

2. 数据的导入

• 这里的案例第一步是将获取的数据导入到linux系统相应的文件夹中。
• 第二步是通过hdfs文件系统，将linux本地文件传输到hdfs目录上
  

将linux系统中opt目录下的users.csv表传输到hdfs文件系统上
hdfs dfs -put /opt/users.csv /wh

链接：user.csv的资源包
提取码：jkjy

• 第三步创建外部表和内部表

<--创建外部表-->
create external table userinfos(
userid string,
username string,
birthday string,
sex string
)
row format delimited fields terminated by ','
stored as textfile;

<--创建内部分区表-->
create table users(
userid string,
username string,
birthday string
)
partitioned by (sex string)
row format delimited fields terminated by ','

3. 将外部表数据传输到内部分区表，使用静态分区
静态分配，需要自己指定选定的分区，才能将想要的数据导入到相应的分区

insert into users partition(sex='male') select userid,username,birthday from userinfos
where sex='male';
insert into users partition(sex='female') select userid,username,birthday from userinfos
where sex='female';

4. 将外部表数据传输到内部分区表，使用动态分区
动态分区，可以自动的识别按什么字段进行分区导入数据。

insert into users partition(sex) select userid,username,birthday from userinfos;