Hive

Hive

一、是什么

Hive是一个Hadoop生态的数据仓库工具
Hive可以将HDFS中的文件映射为关系表
并将客户端提交的HiveSQL语句编译成MR任务并提交 到Hadoop集群执行

二、安装

1. 安装mysql

hive需要将hive中的元数据(库和表的结构信息)存储在关系型数据库中
hive内嵌轻量级数据库derby，生产环境通常将derby替换为MySQL、Oracle、PostgraSQL

2. 安装Hive

1. 上传解压

   将mysql的jdbc驱动包 上传到hive的lib目录中

2. 修改配置文件

hive-env.sh

HADOOP_HOME=/opt/hadoop-2.7.7
export HIVE_CONF_DIR=/opt/hive-2.3.9/conf
JAVA_HOME=/opt/jdk1.8

hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
  <property>
   <name>javax.jdo.option.ConnectionURL</name>
   <value>jdbc:mysql://bd0801:3306/metastore</value>
  </property>
  <property>
   <name>javax.jdo.option.ConnectionDriverName</name>
   <value>com.mysql.cj.jdbc.Driver</value>
  </property>
  <property>
   <name>javax.jdo.option.ConnectionUserName</name>
   <value>root</value>
  </property>
  <property>
   <name>javax.jdo.option.ConnectionPassword</name>
   <value>123456</value>
  </property>
  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
    <description>Whether to include the current database in the Hive prompt.</description>
  </property>
</configuration>

3. 配置环境变量

echo 'export HIVE_HOME=/opt/hive-2.3.9' >> /etc/profile
echo 'export PATH=$HIVE_HOME/bin:$PATH' >> /etc/profile
source /etc/profile

4. 初始化元数据库

   1. 在mysql中创建metastore库
   2. 使用工具初始化元数据库
      schematool -initSchema -dbType mysql

5. 启动客户端

hive

三、Hive使用

1. 数据库与数据仓库

数据库->数据仓库->数据湖->数据中心

2. Hive的SQL操作

2.0 特殊用法

• hive允许在控制台中使用shell命令

-- ！ shell命令 ;
!clear;

• hive允许在控制台中使用hdfs命令
  因为hive的客户端内部会维护一个HDFS的客户端实例

-- dfs -命令  xxx ;
dfs -ls /;

2.1 DDL

• 库

-- 查看包含哪些数据库
show database;
-- 创建数据库
create database 库名 comment;
-- 查看数据库信息
describe database 库名;
describe database extended 库名;
-- 删除数据库
drop database 库名 [cascade];

• 表

-- 查看表
show tables;
-- 查看表结构
describe 表名;
describe extended 表名;
describe formatted 表名;
-- 查看建表语句
show create table 表名;
-- 删除表
drop table 表名;

• 建表

--      外部
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
--         数据类型    列备注
[(col_name data_type [COMMENT col_comment], ...)]
-- 表备注
[COMMENT table_comment]
-- 创建分区
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
-- 设置表中分桶的依据
[CLUSTERED BY (col_name, col_name, ...)
-- 设置桶内按照某些字段提前排序                 桶的个数
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
-- 行格式化
[ROW FORMAT
-- 限制
DELIMITED
-- 字段   结束      于 字符  ' ' '\t'  ','
[FIELDS TERMINATED BY char]
-- 集合      元素  结束       于  ','
[COLLECTION ITEMS TERMINATED BY char]
-- 映射中  key和value的分割符  ':'
[MAP KEYS TERMINATED BY char]
-- 数据中每行数据的分割符 '\n'
[LINES TERMINATED BY char] ]
-- 设置表的文件格式
--  hive默认使用text(文本格式)
--  hive也支持存储和查询性能更好的ORC 列式存储 格式
--  查询速度
--      orc>>text
-- TB   2~3个数量级的差异

--  占用空间大小
--  orc<<text
--    超过一个数量级
[STORED AS file_format]
-- 指定表的数据存储的hdfs路径
--  通常生产环境，为了避免采集和处理的强耦合，
--     采集端将数据按照规范存放在集群
--     数据处理端使用外部表 执行location的方式共享同一份原始数据
--     通常将原始表清洗后 以orc等更优的存储格式分别管理
[LOCATION hdfs_path]

2.2 DML

• 数据导入
  1. load

load data [local] inpath 'filePath' [overwrite] into table 表名
partition (parcol='parvalue',...)

2. insert

insert into table 表名 values(1,'amos');
-- 需要提前创建表，注意字段的匹配
insert into table 表名 select * from xxx;

3. as select

-- 通常用于数据应用层  ，不需要提前创建表结构，根据查询结果自动创建
create table 表名 as select * from xxx

4. 操作文件系统
   hive底层使用HDFS存储表内容，所以可以直接将数据put到表所在的HDFS路径
   需要注意行格式化

• 数据导出

-- 将查询的结果导出到本地
insert overwrite local directory '/root/data/export/student'
select * from student;
-- 将查询的结果格式化导出到本地  |
insert overwrite local directory '/root/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from student;
-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/root/hive/warehouse/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from student;
-- Hadoop命令导出到本地
dfs -get /user/hive/warehouse/student/month=201809/*/root/data/export/student3/;
-- Hive Shell 命令导出
bin/hive -e 'select * from default.student;' > /root/data/export/student4.txt;
-- Export导出到HDFS上
export table default.student to '/user/hive/warehouse/export/student';
import table student2 partition(month=‘202005') from '/user/hive/warehouse/export/student';

2.3 DQL

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

• 修炼函数内功
  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

• 自定义函数

  • UDF 、UDTF 、UDAF
  1. 创建maven工程导入依赖hive-exec
  2. 创建类继承org.apache.hadoop.hive.ql.exec.UDF类
  3. 创建evaluate方法，用于实现函数的具体操作
  4. 将工程打包并上传到HDFS
  5. 在hive中创建函数

-- 创建udf
create [temporary] function my_dt_fmt1 as 'com.xahj.bd218.hive.udf.UDF_DateFormat'
using jar 'hdfs://bd0801:8020/user/hive/udf/hive-udfs.jar';
-- 删除udf

四、Hive的核心原理

4.1 管理表和外部表

• 管理表又叫内部表

• 为了方式开发过程中出现数据误删，数仓规范要求所有的公共数据表均使用外部表

4.2 Hive表的分区

• 由于Hive底层执行MR任务，所以在进行数据加载时，MR会直接读取Hive表所在HDFS路径的所有文件

• 如果表中存放的数据量过大，每次查询部分数据时，也需要将全表数据加载到内存，查询效率极差

• Hive提供了对表进行分区的操作

  • 本质上就是将Hive表分为多个子目录

create table t_weblog_par (line string)
partitioned by (dt string);

create table t_weblog_par2 (line string)
partitioned by (year string,month string,day string);

4.3 Hive表的分区

• hive表的分桶操作

  • 在hive中对海量数据进行全表的分组和排序动作，是一个重操作
  • 由于数据量巨大，有可能全局的排序会造成程序崩溃
  • 通常我们会将需要进行排序和分组操作的字段在建表时设计分桶规则，
  • 并在插入数据时自动保证数据有序分配到各自的桶中

4.4 Hive提供类似关系型数据库的标准JDBC连接

• jdbc:hive2://host:10000

• 启动hiveserver2服务

  1. 开启hdfs代理访问权限
     修改hdfs的core-site.xml

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

重启hdfs服务

2. 启动hiveserver2服务

nohup hiveserver2 > 日志文件 &