Hive基础

Hive基础

What’s Hive

由Facebook开源用于解决海量结构化日志的数据统计

基于Hadoop的一个数据仓库工具，可以将结构化的的数据文件映射为一张表，并提供累SQL查询功能

#Hive本质
	将HQL转化为MapReduce程序（当计算引擎为MapReduce时）
		Hive处理的数据存储在```HDFS```
		Hive分析数据底层的实现是MapReduce
		执行程序运行在Yarn上
# Hive组成：
	mysql : 有分布式存储，无分布式计算
		表分区，分表，分库
	元数据 : mysql
		DBS
		TBLS
		COLUMNS_V2
	结构
		库 ：文件夹(与库同名.db)
		表 ：文件夹(与表同名)
		分区：文件夹(分区字段名=分区字段值)
		    一级分区：文件夹（year=2012）
		        二级分区：文件夹（month=3）
		            三级分区：文件夹（weekofmonth=2）
# Hive元数据管理
	记录数据仓库中模型的定义、各层级间的映射关系
	存储在关系数据库中
		默认Derby，轻量级内嵌SQL数据库
			Derby非常适合测试和演示
			存储在.metastore_db目录中
		实际生产一般存储在MYSQL中
			修改配置文件hive-site.xml
# Hive 计算引擎：
	MapReduce
		Map :  清洗，列变形，列裁剪
		Map+Reducer	聚合
	Spark
# Hive特点
	优点
		可扩展性
			Hive 可以自由的扩展集群的规模，一般情况下不需要重启服务
		延展性
			Hive 支持自定义函数，用户可以根据自己的需求来实现自己的函数
		良好的容错性
			可以保障即使有节点出现问题，SQL语句仍可完成执行
	缺点
		Hive不支持记录级别的增删改查操作，但是用户可以通过查询生成新表或者将查询结果导入到文件中
		Hive 的查询延时很严重，因为 MapReduce Job 的启动过程消耗很长时间，所以不能用在交互查询系统中
		Hive 不支持事务（因为不没有增删改，所以主要用来做 OLAP（联机分析处理），而不是OLTP（联机事务处理）
# Hive的相关配置
set config_item=value        临时配置					1（个性化执行优化）
hive-site.xml                自定义配置					2（服务和通用优化配置）
hive-defalut.xml             默认配置（模版）            3

Hive体系架构

Client

CLI（Command Line Interface）

CLI指的是通过文本命令而不是图形用户界面（GUI）来与计算机程序或者操作系统进行交互的用户界面

在计算机领域，CLI提供了一个通过键入文本命令执行操作的方式，这些命令通常由操作系统或应用程序提供

对于Hive，它也提供了一个CLI，使用户能够通过命令行与Hive交互，Hive的CLI允许用户执行HiveQL查询和管理Hive数据库的元数据

HiveQL的相关介绍

Hive查询语言（HiveQL）是Hive的主要接口，它类似于SQL

用户可以使用HiveQL来定义表、加载数据、执行查询等操作，HiveQL查询被翻译成一系列的MapReduce任务，这些任务在Hadoop集群上运行

JDBC

java访问hive

Hive 的基于 JDBC操作提供的客户端，用户（开发员，运维人员）通过 这连接至 Hive server 服务

WEBUI

浏览器访问hive

Metastore

Hive使用 Metastore来存储表模式和其他元数据信息

Metastore是一个独立于Hive的数据库，可以是MySQL、PostgreSQL等

Metastore存储的元数据信息有：
表名，表所属的数据库（默认是default），表的拥有者，列/分区字段，表的类型（内部表，外部表），表的数据所在目录

驱动器（Driver）

解析器（SQL Parser）

Hive的解析器负责解析HiveQL查询，并将其转换成抽象语法树AST，一般都用第三方工具库完成。

解析器对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

编译器（Compiler）

将AST编译生成逻辑执行计划

优化器（Query Optimizer）

对逻辑执行计划进行优化

选择合适的连接策略、重排序操作等

执行器（Execution）

把逻辑执行计划转换成可以运行的物理计划

负责将HiveQL查询翻译成底层的MapReduce任务，并将这些任务提交到Hadoop集群上执行

Hadoop

使用HDFS进行存储，使用MapReduce进行计算

Hive和RDBMS的对比

Hive 具有 SQL 数据库的外表，但应用场景完全不同，Hive 只适合用来做海量离线数 据统计分析，也就是数据仓库

Hive DDL

日志的开启和关闭
开启日志： set hive.server2.logging.operation.level=verbose;
关闭日志： set hive.server2.logging.operation.level=NONE;

Hive数据类型

基础类型
   字符
       char
       varchar
       string
   整数
       tinyint
       smallint
       int
       bigint
   小数
       float
       double
       numeric(m,n)    通常也称为decimal(m,n)
       decimal(m,n)
   布尔
       boolean
   日期
       date
       timestamp
复杂类型
   数组      array<T>    存储相同类型的数据
   键值对    map<K,V>    具有相同类型的键值对
   结构体    struct<n1:T1,n2:T2,...>    类json，封装了一组字段

Hive数据结构

数据库操作

数据库是表的集合，HDFS中表现为一个文件夹
• 默认在hive.metastore.warehouse.dir属性目录下
• 如果没有指定数据库，默认使用default数据库

创建数据库
    create database if not exists database_name;
    create database if not exists database_namelocation 'hdfs路径';（指定数据库在HDFS上存放的位置）
查看数据库
    show databases;
    show databases like 'database_name*'（过滤数据库）
    desc database database_name;（数据库详情）
    select current_database();	# 查看当前所在数据库
切换数据库
    use database_name;
删除数据库
    drop database if exists database_name;
       
#### 补充
# 将默认库中的表移动到一个新建库中的表里面（旧库中的数据依然存在，需要手动删除）
创建新库
    create database NEW_DATABASE;
切换到新库
    use NEW_DATABASE
创建新表
    create table if not exist NEW_TABLE(
        name string,
        places array<string>,
        info struct<gender:string,age:int>,
        scores map<string,int>,
        dept_pos map<string,string>
    )
    row format delimited
    fields terminated by '|'
    collection items terminated by ','
    map keys terminated by ':'
    lines terminated by '\n'
    stored as textfile;
移动数据
    insert into NEW_DATABASE.NEW_TABLE
    select * 
    from defalut.OLD_TABLE;
验证移动
查询新库中的新表，确保数据已经移动成功
    select * from NEW_DATABASE.NEW_TABLE;
删除默认库中的旧表（如果需要的话，可以删除）
    truncate table defalut.OLD_TABLE;

数据表操作

根据数据的组织方式的不同，可将数据表分为结构化数据表、半结构化数据表、非结构化数据表
结构化数据表
    定义:
        一种使用明确定义的模式来组织和存储数据的表，表中的每个字段都有固定的数据类型，表的整体结构在创建时已经确定
    特点:
        表示数据的每个字段都有清晰的数据类型和约束，使得数据表更容易被查询和分析，关系型数据库中的表通常是结构化数据表
半结构化数据表
    定义:
        一种在数据表中允许包含具有不同结构或数据类型的字段的表，这些字段可以是嵌套的、重复的，或者包含各种数据类型的集合
    特点:
         半结构化数据表提供了更大的灵活性，适用于那些不适合传统关系型数据库模型的场景，如JSON或XML文档存储
非结构化数据表
    定义:
        一种不遵循固定模式或模式不明确的表，数据没有明确定义的结构
        这种类型的表通常包含无法通过常规手段进行解析的文本或二进制数据
    特点:
        非结构化数据表适用于那些没有固定结构或格式的数据，如文本文件、图像、音频、视频等

# 创建表
## 建表的基本语法
123,张三,1698736652123,true,26238.45,阅读;跑步;唱歌,java:98,mysql:65,province:江苏;city:南京
create table if not exists TABLE_NAME(
    id int,
    name string,
    time bigint,
    isPartMember boolean,
    expectSalary decimal(10,2),
    hobby array<string>,
    scores map<string,int>,
    address struct<province:string,city:string>
)
comment 'TABLE DESCRIPTION'

## 数据格式的描述
###数据交换，用于指定如何分隔和解析数据文件中的信息
row format delimited
------------------------delimited-------------------------------------------------
fields teriminated by ','
colllection items terminated by ';'
map keys terminated by ':'
lines terminated by '\n'

### 序列化和反序列化 Ser(ialize)De(serialize)
### 数据序列化和反序列化的配置描述，用于指定如何读取和写入不同格式的数据
row format serde 'CLASS_PATH'
------------------------serde-----------------------------------------------------
------------------------CSV-------------------------------
serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
    serparatorChar=',',
    quoteChar'"',
    escapeChar='\\'
)
------------------------regex------------------------------
serde 'orrg.apache.hadoop.hive.serde2.RegexSerde'
serde 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
with serdeproperties(
    # 正则表达式的输出：数据行应该以一系列数字开头，后跟任意字符，然后是数字，布尔值（true或false），以及可能的小数或整数
    'input.regex'='^(\\d+),(.*?),(\\d+),(true|falase),(\\d+\\.?\\d+?)$'
)
------------------------json-------------------------------
serde 'org.apache.hive.catalog.data.JsonSerDe'
------------------------hbase------------------------------
serde 'org.apache.hive.hbase.HbaseSerDe'
...

## 数据存储的文件格式
## 指定了数据可以存储在不同类型的文件格式中，
如文本文件、ORC（优化的列式存储格式）、Parquet（另一种列式存储格式）、SequenceFile（Hadoop的序列化文件格式）等
------------------------store-----------------------------------------------------
stored as FILE_FORMAT(textfile√,orc,parquet,sequencefile,...)

## 表的一些额外属性
------------------------tblpropertie---------------------------------------------
tblpropertie(
    # 读取CSV文件时跳过头部的行数为1
    'skp.header.line.count'='1'
    ...
)

# Hive 修改数据表的结构
alter table TABLE_NAME rename to NEW_NAME;// 重命名表
alter table TABLE_NAME set tblproperties('key'='value');// 修改表的属性
alter table TABLE_NAME set fileformat FORMAT;// 修改文件格式（FORMAT为新的文件格式）
alter table TABLE_NAME set location 'new_hdfs_path';// 修改表的存储位置（将标的数据移动到新的HDFS路径，原来位置的数据不会删除）
alter table TABLE_NAME set tblproperties('key'='value');// 修改表的属性
alter table TABLE_NAME change old_name new_name TYPE;// 修改字段名（TYPE为新字段名）
alter table TABLE_NAME add column(field_name TYPE);// 添加列（TYPE为新列的字段类型）

# 对 SerDe的补充
-- CSV SerDe
create external table if not exists hive_ext_emp_basic(
    emp_id int,
    emp_name string,
    job_title string,
    company string,
    start_date date,
    quit_date date
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
with serdeproperties(
    "separaorChar"=",",
    "quoteChar"='"',
    "escapeChar"="\\"
)
tblproperties('skip.header.line.count'='1')// 去表头的作用
location '/hive_data/hive_char01/emp_basic';// HDFS上路径

-- JsonSerDe  要注意：表字段名必须和Json数据中的属性名保持一致
create external table if not exists hive_ext_json_family(
    name string,
    age int,
    gender string,
    phone string
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile
location '/hive_data/hive_char01/json';

-- RegexSerDe ：重点正则表达式的分组提取
create external table if not exists hive_ext_regex_test1w(
    use_id int,
    use_gender string,
    order_time timestamp,
    order_amount decimal(10,2)		-- decimal(10,2)==>（该列总共有10位数字，其中包括小数点后的2位）
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
    'input.regex'='(\\d+);(.*?);(\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2});(\\d+\.?\\d+?)'
)
stored as textfile
location '/hive_data/hive_char01/test1w';

Hive DML

# 数据导入
## 向表中装载数据
load data [local] inpath '路径' [overwrite] into table 表名 [partition (partcol1=val1,…)];

加 local 关键字，表示原始文件位于虚拟机本地，执行后拷贝数据
没有 local 关键字，表示文件位于 HDFS 文件系统中，执行后为直接移动数据
overwrite 表示覆盖，overwrite 缺省 表示追加

## 使用 insert into 语句将数据插入表/分区
insert into(追加) table TABLE_NAME select ...;	
insert overwrite(覆盖) into table TABLE_NAME select ...;

insert 支持overwrite（覆盖）和into（追加）

Hive支持从同一个表中进行多次插入
例子：
from ctas_employee
insert overwrite table employee select *
insert overwrite table employee_internal select *;

insert into 中 table 关键字是可选的

insert into 可以指定插入到哪些字段中
insert into employee(name) select 'John' from test limit 1;	// 指定列进行插入

into 关键字在 insert into 语句中通常用于指定插入操作的目标表，而 overwrite 关键字用于指定是否覆盖现有数据

### overwrite 和 into 一起使用的一些情况
#### 覆盖目标表中的所有数据
INSERT INTO TABLE target_table OVERWRITE SELECT * FROM source_table;
#### 根据条件覆盖目标表中的数据
INSERT INTO TABLE target_table PARTITION (partition_column) OVERWRITE SELECT * FROM source_table WHERE condition;

## 查询语句中创建表并加载数据（As Select）
create table if not exists student3 as select id, name from student;

## 从外部数据源导入数据到 Hive 表（import 用于数据迁移场景，除数据库外，可导入所有数据和元数据）
import into TABLE_NAME from 'path/to/source_data';
import into TABLE_NAME from 'path/to/source_data' as select * from EXTERNAL_TABLE where CONDITION;

# 数据导出
## 将查询的结果以指定格式导出到【本地】上
insert overwrite local directory '/opt/data'
row format delimited
fields terminated by '|'
select * from HIVE_TABLE_NAME;

##  将查询的结果以指定格式导出到 【HDFS】上（没有 local 关键字）
insert overwrite directory 'hdfs_output_path'
row format delimited
fields terminated by '|'
select * from HIVE_TABLE_NAME;

## HDFS 命令导出到本地
dfs -get /opt/hive/warehouse/employee/employee.txt /opt/datas/dept2/dept.txt;

## export 导出到 HDFS 上
export table employee to '/tmp/output3';

# 清除表中数据
truncate table TABLE_NAME;
TRUNCATE 只能清除管理表，不能清除外部表中数据

Hive 中的一些表

内部表（管理表）

HDFS中为所属数据库目录下的子文件夹

数据完全由 Hive 管理，删除表（元数据）会删除数据

外部表（external）

数据保存在指定位置的 HDFS路径中

Hive 不完全管理系统，删除表（元数据）不会删除数据

hive中表的分类

MANAGED_TABLE

Hive中的普通表，它们的元数据和数据都存储在Hive的元数据中（MetaStore），数据文件的位置由 Hive 控制

使用 create table 创建表时，就是创建了一个 MANAGED_TABLE

对于这种类型的表，Hive 会管理其生命周期，包括元数据的存储和更新

drop table 时，Hive 会删除表的元数据以及与之关联的数据文件

EXTERNAL_TABLE

外部表，元数据存储在Hive的MetaStore中，但数据文件位于HDFS文件系统上，并且通常由外部应用程序或用户手动管理

create table 时需要指定 external 关键字

droptable 只会删除表的元数据，不会删除在HDFS上的数据文件

外部表和内部表的互相转换

内部表改为外部表
    alter table student set tblproperties('EXTERNAL'='TRUE')
外部表改为内部表
    alter table student set tblproperties('EXTERNAL'='FALSE')

临时表（temporary）

一次链接（会话session）内临时创建的表格，会话结束后自动删除

例子：
create temporary table hive_tmp_test2020 as
select * from hive_ext_regex_test1w where year(order_time)=2020;

视图（view）

本质上是一条较为复杂的公用的查询语句

视图是基于已有表的查询结果的逻辑表示，它不存储数据，而是在查询时动态生成数据。

视图可以简化复杂的查询，提高查询效率，使得用户不必每次都编写复杂的SQL语句。

视图可以用于数据脱敏、安全性控制和数据抽象。

例子：
create view hive_view_test2020 as
select * from hive_regex_test1w where year(order_time)=2020;

分区表（partition by）

概念：

将一份大的文件拆分成多份，每一份存储在HDFS上表空间内的一个专门文件夹内

文件包含了字段名和字段值：year=2012 => file

使用：

year 可以作为字段使用，但本质上 year 不是原始字段，是分区字段

优势：

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集，在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多

（图片侵权可删）

# 创建分区表
create table if not exists hive_internal_par_regex_test1w(
    use_id int,
    use_gender string,
    order_time timestamp,
    order_amount decimal(10,2)        -- decimal(10,2)==>（该列总共有10位数字，其中包括小数点后的2位）
)
partitioned by(year int)
row format delimited
fields terminated by ';'
lines terminated by '\n'
stored as textfile;

# 静态分区（客户按分区级别给数据）
-- 进行数据装载
load data local inpath '/root/hive/data/test2013.log'
overwrite into table hive_internal_par_regex_test1w patition by(year=2013);

# 动态分区（项目初期）Dynamic Partitioning
-- 动态分区是指在向分区表中插入数据时，根据插入语句中的特定列的值动态创建分区

set hive.exec.dynamic.partition=true; # 开启动态分区功能
set hive.exec.dynamic.partition.mode=nonstrict; # 设置动态分区的模式

当设置为 strict 时，表示动态分区模式为严格模式，在这种模式下，插入数据到分区表时，必须保证插入语句中包含所有的分区列。
如果插入语句缺少任何一个分区列，Hive 将会报错，不允许插入数据。
当设置为 nonstrict 时，表示动态分区模式为非严格模式。在这种模式下，插入数据到分区表时，
可以只在插入语句中包含部分分区列，缺失的分区列将由 Hive 自动生成。
这个模式允许用户在插入数据时，不必提供所有分区列，Hive 会自动创建缺失的分区。

-- 进行数据导入
insert overwrite table hive_internal_par_regex_test1w partition(year)
select *,year(order_time) from hive_ext_regex_test1w where year(order_time)>=2014;

# 查看分区信息
show partitions hive_internal_par_regex_test1w;

# 手动添加分区
alter table dept_partition add partition(month='201306') ;
alter table dept_partition add partition(month='201305') partition(month='201304');

# 手动删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');

分桶表（cluster by）

概念：

将同一个表或分区内的数据，拆分成更小的文件片段

分桶就是将整个数据内容按照某列属性值取 hash 值进行分区分，具有相同的 hash 值的数据进入到同一个文件中

使用：

分桶字段必须是表中已存在的原始字段

默认采用原始字段值的 hashcode% 分桶数列决定当前行数据会被拆分到几号桶

分桶数最好是2的n次方

优势：

数据采样

采样率：10% -> 桶数定义为10

（图片侵权可删）

# 创建分桶表
create external table if not exists hive_internal_par_cluster_regex_test1w(
    user_id int,
    user_gender int,
    order_time timestanp,
    order_amount decimal(10,2)	
)
partition by(year int)
clustered by(order_time) into 4 buckets
row format delimited
fields terminated by ','
lines terminated by '\n'
stroed as textfile;

# 数据装载
insert overwrite table hive_internal_par_cluster_regex_test1w partition(year)
select *, year(order_time) as year from hive_ext_regex_test1w where year(order_time)>=2014;

# 分桶只有动态分桶
set hive.enforce.bucketing = true

# 应用（从4个桶里面随机选3个桶的数据）
-- 随机抽样（基于整行数据）
-- on rand() 表示抽样是基于列 rand() 的值进行的，这里 rand() 是一个函数，它会为每一行生成一个随机数。
这意味着抽样操作将基于随机数来决定哪些桶将被包含在查询结果中。
select * from hive_internal_par_cluster_regex_test1w
tablesample(bucket 3 out of 4 on rand()) s;

-- 分桶字段抽样（基于指定列）
-- on order_time 指定了抽样的种子列，即 order_time 列。这意味着抽样操作将基于 order_time 列的值来决定哪些桶将被包含在查询结果中。
select * from hive_internal_par_cluster_regex_test1w
tablesample(bucket 3 out of 4 on order_time) s
group by year;