数据仓库总结

数据仓库概述

1、数据仓库的定义

数据仓库是一种面向商务智能 (BI) 活动（尤其是分析）的数据管理系统，它仅适用于查询和分析。

一个数据仓库通常包含以下要素：1、一个用于存储和管理数据的关系数据库。2、一个用于分析前数据准备的提取、加载和转换 (ELT) 解决方案 。3、统计分析、报告和数据挖掘功能 。4、用于数据可视化和展现的客户端分析工具

2、数据仓库的优势

面向主题：数据仓库可以高效分析关于特定主题或职能领域（例如销售）的数据。      

集成：数据仓库可在不同来源的不同数据类型之间建立一致性。    

相对稳定：进入数据仓库后，数据将保持稳定，不会发生改变。    

反映历史变化：数据仓库分析着眼于反映历史变化。

3、数据仓库的架构

数据仓库架构特点：简单、通过暂存区简化数据准备、星型结构、沙盒

4、数据仓库的进化

从数据分析到 AI 和机器学习

5、云数据仓库

云数据仓库使用云技术来提取和存储不同数据源的数据。

云数据仓库优势：1、提供弹性的可扩展支持，可满足大型或可变计算或存储需求。2、简单易用。3、易于管理。4、节约成本

现代数据仓库包括：融合数据库，可简化所有数据类型的管理并支持多种数据使用方法     自助式数据提取和转换服务     支持 SQL、机器学习、图形和空间处理     多种分析选项，可轻松使用数据而无需移动数据     自动化流程，可简化供应、扩展和管理

如何设计数据仓库：1、具体的数据内容。2、各数据组内部及相互之间的关系。3、支撑数据仓库的系统环境。4、数据转换类型。5、数据刷新频率

Hive环境配置

Hive产生背景与框架设计

一、Hive的基础

1、Hive产生的背景

为解决使用MapReduce的高门槛，Hive在Facebook诞生。

2、什么是Hive

由Apache软件基金会维护的开源项目，基于Hadoop的数据仓库工具，用来处理存储在Hadoop上的海量数据，提供一系列功能可以方便进行数据ETL（提取、转化、加载）。

Hive使用HQL代替MapReduce，使传统数据库开发人员更容易使用Hadoop。

Hive依赖Hadoop的HDFS和YARN。

数据仓库与数据库区别：数据仓库适合联机分析处理（OLAP），数据库适合联机事务处理（OLTP）。

3、Hive在Hadoop生态系统的位置

Apache Hadoop Ecosystem（Apache Hadoop 生态系统）
Ambari（安装部署组件）
zookeeper（分布式协调服务，保证各组件间的一致性）	0ozie（作业调度工具）
	Hbase（分布式数据库）	Mahout（机器学习库）	Hive（数据仓库工具）	Pig（数据分析工具）	Sqoop（数据库ETL工具）
		Yarn&MapReduce（分布式计算框架）
	HDFS（分布式文件系统）				Flume（日志收集）

4、Hive与传统关系型数据库

对比项	Hive	RDBMS
查询语言	HQL	SQL
数据存储	HDFS	块设备、本地文件系统
执行	MapReduce	Executor
执行延迟	高	低
处理数据规模	大	小
事务	0.14版本后加入	支持
索引	0.8版本后加入	索引复杂

5、Hive的优势

可扩展：Hive可以自由扩展集群规模；

可延展：Hive支持用户自定义函数；

可容错：Hive良好的容错性使得节点出现问题时SQL仍可以完成执行。

二、Hive框架设计

1、Hive框架

（1）Hive客户端（Hive Clients）；（2）Hive服务端（Hive Services）；（3）Hive存储与计算（Hive Storage and Computing）。

2、Hive工作流程

1.Execute Query：hive界面如命令行或Web UI将查询发送到Driver (任何数据库驱动程序如JDBC、ODBC,等等)来执行。

2.Get Plan:Driver根据查询编译器解析query语句,验证query语句的语法,查询计划或者查询条件。

3.Get Metadata：编译器将元数据请求发送给Metastore(任何数据库)。

4.Send Metadata：Metastore将元数据作为响应发送给编译器。

5.Send Plan：编译器检查要求和重新发送Driver的计划。到这里,查询的解析和编译完成。

6.Execute Plan:Driver将执行计划发送到执行引擎。

7.Execute Job:hadoop内部执行的是mapreduce工作过程,任务执行引擎发送一个任务到资源管理节点(resourcemanager)，资源管理器分配该任务到任务节点，由任务节点上开始执行mapreduce任务。

7.1Metadata Ops：在执行引擎发送任务的同时,对hive的元数据进行相应操作。

8.Fetch Result：执行引擎接收数据节点(data node)的结果。

9.Send Results:执行引擎发送这些合成值到Driver。

10.Send Results：Driver将结果发送到hive接口。

3、Hive场景技术特点

（1）为超大数据集设计了计算与扩展功能；

（2）支持SQL like查询语言；

（3）支持多表的join操作；

（4）支持非结构化数据的查询与计算；

（5）提供数据存取的编程接口，支持JDBC、ODBC。

Hive存储模型和数据类型

一、Hive存储方式与模型

1、Hive存储格式

（1）TEXTFILE

默认存储格式，按行存储，内容为普通的文本格式，常见的txt、CSV、tsv等文件，一般使用cat命令直接查看。

（2）SEQUENCEFILE

一种由二进制序列化过的key/value字节流组成的文本存储文件格式，仅支持追加，无法直接导入，可分割的文件格式，支持三种压缩选项：（1）NONE：不压缩；（2）RECORD（默认选项）：记录级压缩，压缩率低；（3）BLOCK：块级压缩，压缩率高。

（3）RCFILE

专门面向列的数据存储格式，遵循先水平划分，再垂直划分。

（4）ORCFILE

对RCFILE的优化，特点：支持压缩比很高的压缩算法，文件可切分，提供多种索引，支持复杂的数据结构。

2、Hive数据单元

（1）数据库

（2）表

可以进行过滤、映射、连接、联合操作，分为内部表和外部表，内部表由Hive管理，外部表的真实数据不被Hive管理。

（3）分区

每个表按指定键分为多个分区，提高查询效率。

（4）分桶

根据表中某一列的哈希值可将数据划分为多个分桶。

3、Hive存储模型

（1）/数据仓库地址/数据库名称/表名称/数据文件（或分桶数据文件）

（2）//数据仓库地址/数据库名称/表名称/分区键/数据文件（或分桶数据文件）

二、Hive基本数据类型

1、基本数据类型

整数：TINYINT、SMALLINT、INT、BIGINT

小数：FLOAT、DOUBLE、DECIMAL

文本：STRING、CHAR、VARCHAR

布尔：BOOLEAN

二进制：BINARY

时间：DATE、TIMESTAMP、INTERVAL

2、复杂数据类型

（1）ARRAY和MAP

ARRAY是具有相同类型变量的集合，这些变量称为数组的元素。

MAP是一对键值对集合

（2）STRUCT

封装了一组有名字的字段，任意基本类型。

（3）UNIONTYPE

可以保存指定数据类型中的任意一种。

数据库表操作

一、Hive DDL 操作

1、hive数据库操作

（1）创建数据库

CREATE (DATABASE|SCHEMA)[IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (propety_name=property_value,......)];

DATABASE|SCHEMA：用于限定创建数据库或数据库模式
IF NOT EXISTS：目标对象不存在时才执行创建操作（可选）
COMMENT：起注释说明作用
LOCATION：指定数据库位于HDFS上的存储路径。若未指定，将使用${hive.metastore.warehouse.dir}定义值作为其上层路径位置
WITH DBPROPERTIES：为数据库提供描述信息，如创建database的用户或时间

（2）修改数据库

ALTER （DATABASE|SCHEMA）database_name SET DBPROPERTIES (property_name=property_value,…);

只能修改数据库的键值对属性值。数据库名和数据库所在的目录位置不能修改

（3）删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

DATABASE|SCHEMA：用于限定删除的数据库或数据库模式
IF EXISTS：目标对象存在时才执行删除操作（可选）
RESTRICT|CASCADE：RESTRICT为 Hive 默认操作方式，当database_name中不存在任何数据库对象时才能执行DROP操作；CASCADE 采用强制DROP方式，汇联通存在于database_name中的任何数据库对象和database_name一起删除（可选）

2、Hive数据表操作

（1）创建表

CREATE [TEMPOPARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] 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]
[SKEWED BY (col_name,col_name,…) ON ([(col_value,col_value,…),…|col_value,col_value,…]) [STORED AS DIRECTORIES] ]
[
[ROW FORMAT DELIMITED [FIFLDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value,…)]
]
　 [STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (…)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value,…)]
[AS select_statement];

参数说明如下：

TEMPOPARY：创建临时表，若未指定，则默认创建的是普通表
EXTERNAL：创建外部表，若未指定，则默认创建的是内部表
IF NOT EXISTS：若表不存在才创建，若未指定，当目标表存在时，创建操作抛出异常
db_name.：前缀，指定表所属于的数据库。若未指定且当前数据库非db_name，则使用default数据库
COMMENT：添加注释说明，注释内容位于单引号内
PARTITIONED BY：针对存储有大量数据集的表，根据表内容所具有的某些共同特征定义一个标签，将这类数据存储在该标签所标识的位置，可以提高表内容的查询速度。PARTITIONED BY中的列名为伪列或标记列，不能与表中的实体列名相同，否则 hive 表创建操作报错
CLUSTERED BY：根据列之间的相关性指定列聚类在相同桶中（BUCKETS），可以对表内容按某一列进行升序（ASC）或降序（DESC）排序（SORTED BY关键字）
SKEWED BY：用于过滤掉特定列col_name中包含值col_value(ON(col_value,…)关键字指定的值）的记录，并单独存储在指定目录（STORED AS DIRECTORIES）下的单独文件中
ROW FORMAT：指定 hive 表行对象（ROW Object）数据与 HDFS 数据之间进行传输的转换方式（HDFS files -> Deserializer ->Row object以及Row object ->Serializer ->HDFS files），以及数据文件内容与表行记录各列的对应。在创建表时可以指定数据列分隔符（FIFLDS TERMINATED BY 子句）、对特殊字符进行转义的特殊字符（ESCAPED BY 子句）、符合数据类型值分隔符（COLLECTION ITEMS TERMINATED BY 子句）、MAP key-value 类型分隔符（MAP KEYS TERMINATED BY）、数据记录行分隔符（LINES TERMINATED BY）、定义NULL字符（NULL DEFINED AS），同时可以指定自定义的SerDE（Serializer和Deserializer，序列化和反序列化），也可以指定默认的SerDE。如果ROW FORMAT 未指定或指定为ROW FORMAT DELIMITED，将使用内部默认SerDe
STORED AS：指定 hive 表数据在 HDFS 上的存储方式。file_format值包括TEXTFILE（普通文本文件，默认方式）、SEQUENCEFILE（压缩模式）、ORC（ORC文件格式）和AVRO（AVRO文件格式）
STORED BY：创建一个非本地表，如创建一个 HBase 表
LOCATION：指定表数据在 HDFS 上的存储位置。若未指定，db_name数据库将会储存在${hive.metastore.warehouse.dir}定义位置的db_name目录下
TBLPROPERTIES：为所创建的表设置属性（如创建时间和创建者，默认为当前用户和当前系统时间）
AS select_statement：使用select子句创建一个复制表（包括select子句返回的表模式和表数据）

（2）修改表

ALTER TABLE table_name RENAME TO new_table_name;

（3）其他常用命令

二、Hive DML 操作

1、数据装载与插入

load命令不对数据进行任何转换，只简单的将数据复制或者移动到Hive表；

insert命令将会执行MapReduce作业并将数据插入到Hive表。

（1）load命令

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

Load是将文件复制到Hive对应的存储路径下面；

Local 为可选项，当指定Local时，filepath为本地目录，当不指定Local时，filepath则为hdfs的目录

OVERWRITE 为可选项，当指定了OVERWRITE时，则表示覆盖table中的内容（清空，插入），如果没有指定OVERWRITE则在原表数据基础上面进行追加。

PARTITION 指定的是要将数据写入到那个分区中！

（2）insert命令

1、Insert可以将查询到的结果插入到表中

INSERT [OVERWRIT]E TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_table

2、Insert 可以将查到到的结果导出到文件中

INSERT OVERWRITE [LOCAL] DIRECTORY ‘filepath’ SELECT * FROM tbl;

查询tbl表数据内容，导出到本地/HDFS文件中。

LOCAL为可选项，指定LOCAL时表明导出到本地目录中，不指定LOCAL时，表明导出到HDFS目录中。

2、数据导入、导出

（1）export命令

EXPORT TABLE table_name [PARTITON(partcol=partval,......)] TO 'export_target_path'

（2）import命令

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
  FROM 'source_path'  [LOCATION 'import_target_path']

Hive内部表和外部表

一、Hive 内外部表的定义、区别以及数据管理方式

内部表(managed table): 未被external修饰的表，也叫管理表。

外部表(external table):被external修饰的表。

内部表&外部表的区别：

内部表：删除表会删除表的元数据(metadata)和表数据（data）。  

外部表：删除表会删除表的元数据(metadata)，但不会删除表数据（data）。

数据管理方式： 数据(data)都存储在HDFS上，内部表由Hive自身管理，外部表数据由HDFS管理； 元数据(metadata)都存储在mysql上，由mysql管理。

二、Hive内部表操作

1、创建内部表

create table student1(
id int,
name string)
row format delimited fields terminated by '\t'
stored as textfile;

2、查看表格式化数据

desc formatted student1;

3、表的修改

alter table table_name add columns(column_name datatype)

4、上传表数据并查询表数据存储在HDFS的文件

dfs -put /opt/module/data/student.txt /user/hive/warehouse/temp.db/student1；

5、查询表存储数据（DATA）

select * from  student1;

6、查询表的元数据（METADATA），元数据在mysql

 drop table student1;

三、Hive外部表操作

1、创建外部表

create external table  student2(
id int,
name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/tmp/student';

2、查看表格式化数据

desc formatted student2;

3、表的修改

alter table table_name add columns(column_name datatype)

4、上传表数据并查询表数据存储在HDFS的文件

dfs -put /opt/module/data/student.txt /user/hive/warehouse/temp.db/student2；

5、查询表存储数据（DATA）

select * from  student2;

6、查询表的元数据（METADATA），元数据在mysql

 drop table student2;

四、内外部表的相互转换。

1、将外部表转换成内部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

2、将内部表转化成外部表

alter table student1 set tblproperties('EXTERNAL'='FALSE');

Hive的分区与分桶

一、分桶表

1、创建一个带分桶定义的表（分桶表）

--创建分桶表，指定分桶的字段，不指定数据的排序规则

create table if not exists buc1(
uid int,
uname string,
uage int
)
clustered by (uid) into 4 buckets
row format delimited
fields terminated by ','
;

--创建分桶表，指定分桶的字段，指定数据的排序规则

create table if not exists buc2(
uid int,
uname string,
uage int
)
clustered by (uid) 
sorted by (uid desc) into 4 buckets
row format delimited
fields terminated by ','
;

2、加载数据

--第一种方式：直接load一个文档里面的数据到分桶表里面;

load data local inpath '/usr/local/hive/test/3.txt' into table buc1;

--第二种方式：使用insert into(overwrite)方式来加载，前提是先有 buc_temp（只是一般表），而且字段个数一致，并且buc_temp指定分桶字段

insert overwrite table buc1 select uid,uname,uage from buc_temp cluster by (uid);

--第三种方式：依然使用insert into(overwrite)方式来加载，只不过可以指定数据的排序规则（cluster by（与第二种方式相同）或distribute by () sort by()可以相同字段也可以不同字段，指定asc或desc）

insert overwrite table buc2 select uid,uname,uage from buc_temp cluster by (uid);
insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uid asc);
insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uid desc);
insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uage desc);

3、分桶表的查询

--1、查询全部：

select * from buc2;
select * from buc2 tablesample(bucket 1 out of 1) 

--查询第几桶：

select * from buc3 tablesample(bucket 1 out of 4 on uid);  //除4余0
select * from buc3 tablesample(bucket 1 out of 2 on uid);  

二、分区表 

1. 创建分区表

CREATE TABLE partitioned_table (id INT, name STRING)
PARTITIONED BY (year INT, month INT);

2. 加载数据到分区表中

LOAD DATA INPATH '/path/to/data' INTO TABLE partitioned_table PARTITION (year=2022, month=10);

3. 查询分区表

SELECT * FROM partitioned_table WHERE year=2022 AND month=10;

4. 添加新分区

ALTER TABLE partitioned_table ADD PARTITION (year=2023, month=1);

5. 删除分区

ALTER TABLE partitioned_table DROP PARTITION (year=2022, month=10);

6. 查看分区信息

SHOW PARTITIONS partitioned_table;

7. 重命名分区

ALTER TABLE partitioned_table RENAME PARTITION (year=2022, month=10) TO PARTITION (year=2023, month=10);

8. 合并分区

ALTER TABLE partitioned_table MERGE PARTITIONS (year=2022, month=10), (year=2022, month=11) INTO PARTITION (year=2022, month=12);

9. 修复分区

MSCK REPAIR TABLE partitioned_table;

Hive 元数据概念与表的结构

一、Hive元数据的概念

1、Hive 元数据

Hive的元数据是关于数据的组织、结构和定义的信息。

Hive元数据包括hive库信息、表信息（表的属性、表的名称、表的列、分区及其属性）以及表数据所在的目录等。

Hive的元数据，默认是存储在derby中的，但是我们一般会修改其存储在关系型数据库比如MYSQL中（其可以在hive配置中修改），在关系型数据库中会有一个hive库，存放相应的表。

2、Hive 元数据特点和作用

1. 存储结构信息：元数据中包含了数据库和表的结构信息，如数据库名称、表名称、列名及其数据类型等。
2. 映射关系：它描述了表与物理文件之间的对应关系，以及列和字段之间的对应关系，这样Hive才能正确地解析和查询数据。
3. 服务功能：元数据服务（Metastore）使得上层服务能够基于结构化的库表信息构建计算框架，而不需要直接与裸数据文件交互。
4. 数据管理：通过元数据系统可以管理和维护数据，比如描述数据仓库中的数据、记录数据抽取的时间安排、检测系统数据的一致性要求等。
5. 发现和定位：元数据还具有帮助用户发现和定位数据的功能，提供关于数据资源的描述信息。
6. 数据质量衡量：元数据能够帮助衡量数据质量，确保数据的准确性和可靠性。
7. 配置控制：在软件构建领域，元数据可以通过值的改变来改变程序的行为，控制程序的运行方式。

此外，Hive的元数据通常存储在关系型数据库中，如内置的Derby或第三方数据库如MySQL等。通过操作这些元数据，用户可以获取到Hive表的建表语句(DDL)，从而对表结构进行管理和操作。了解Hive的元数据对于理解和使用Hive进行数据管理和分析至关重要。

二、Hive数据表的结构

1、version（存储Hive版本的元数据表）

VER_ID	SCHEMA_VERSION	VERSION_COMMENT
ID主键	Hive版本	版本说明
1	0.12.0	Set by MetaStore hadoop@192.168.137.130

2、Hive数据库相关的元数据表

主要有DBS和DATABASE_PARAMS，这两张表通过DB_ID字段关联。

（1）DBS：该表存储Hive中所有数据库的基本信息

表字段	说明	示例数据
DB_ID	数据库ID	1
DESC	数据库描述	Default Hive database
DB_LOCATION_URI	数据HDFS路径	hdfs://193.168.1.75:9000/test-warehouse
NAME	数据库名	default
OWNER_NAME	数据库所有者用户名	public
OWNER_TYPE	所有者角色	ROLE

（2）DATABASE_PARAMS：该表存储数据库的相关参数，在CREATE DATABASE时候用WITH DBPROPERTIES(property_name=property_value, …)指定的参数。

表字段	说明	示例数据
DB_ID	数据库ID	1
PARAM_KEY	参数名	createdby
PARAM_VALUE	参数值	root

3、Hive表和视图相关的元数据表 

主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。

（1）TBLS:该表中存储Hive表，视图，索引表的基本信息

表字段	说明	示例数据
TBL_ID	表ID	21
CREATE_TIME	创建时间	1447675704
DB_ID	数据库ID	1
LAST_ACCESS_TIME	上次访问时间	1447675704
OWNER	所有者	root
RETENTION	保留字段	0
SD_ID	序列化配置信息	41，对应SDS表中的SD_ID
TBL_NAME	表名	ex_detail_ufdr_30streaming
TBL_TYPE	表类型	EXTERNAL_TABLE
VIEW_EXPANDED_TEXT	视图的详细HQL语句
VIEW_ORIGINAL_TEXT	视图的原始HQL语句

（2）TABLE_PARAMS:该表存储表/视图的属性信息 

表字段	说明	示例数据
TBL_ID	表ID	1
PARAM_KEY	属性名	totalSize，numRows，EXTERNAL
PARAM_VALUE	属性值	970107336、21231028、TRUE

（3）TBL_PRIVS：该表存储表/视图的授权信息

表字段	说明	示例数据
TBL_GRANT_ID	授权ID	1
CREATE_TIME	授权时间	1436320455
GRANT_OPTION		0
GRANTOR	授权执行用户	root
GRANTOR_TYPE	授权者类型	USER
PRINCIPAL_NAME	被授权用户	username
PRINCIPAL_TYPE	被授权用户类型	USER
TBL_PRIV	权限	Select、Alter
TBL_ID	表ID	21，对应TBLS表的TBL_ID

4、Hive
文件存储信息相关的元数据表

主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种格式去读写HDFS文件，而这些信息就保存在这几张表中。

（1）SDS

该表保存文件存储的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联，可以获取Hive表的存储信息。

表字段	说明	示例数据
SD_ID	存储信息ID	41
CD_ID	字段信息ID	21，对应CDS表
INPUT_FORMAT	文件输入格式	org.apache.hadoop.mapred.TextInputFormat
IS_COMPRESSED	是否压缩	0
IS_STOREDASSUBDIRECTORIES	是否以子目录存储	0
LOCATION	HDFS路径	hdfs://193.168.1.75:9000/detail_ufdr_streaming_test
NUM_BUCKETS	分桶数量	0
OUTPUT_FORMAT	文件输出格式	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
SERDE_ID	序列化类ID	41，对应SERDES表

（2）SD_PARAMS

该表存储Hive存储的属性信息，在创建表时候使用STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定。

表字段	说明	示例数据
SD_ID	存储配置ID	41
PARAM_KEY	存储属性名
PARAM_VALUE	存储属性值

（3）SERDES

该表存储序列化使用的类信息

表字段	说明	示例数据
SERDE_ID	序列化类配置ID	41
NAME	序列化类别名	NULL
SLIB	序列化类	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

（4）SERDE_PARAMS

该表存储序列化的一些属性、格式信息，比如:行、列分隔符

表字段	说明	示例数据
SERDE_ID	序列化类配置ID	41
PARAM_KEY	属性名	field.delim
PARAM_VALUE	属性值	|

5、Hive表字段相关的元数据表

主要涉及COLUMNS_V2

COLUMNS_V2：该表存储表对应的字段信息

表字段	说明	示例数据
CD_ID	字段信息ID	21
COMMENT	字段注释	NULL
COLUMN_NAME	字段名	air_port_duration
TYPE_NAME	字段类型	bigint
INTEGER_IDX	字段顺序	119

6、Hive表分分区相关的元数据表

主要涉及PARTITIONS、PARTITION_KEYS、PARTITION_KEY_VALS、PARTITION_PARAMS

（1）PARTITIONS:该表存储表分区的基本信息

表字段	说明	示例数据
PART_ID	分区ID	21
CREATE_TIME	分区创建时间	1450861405
LAST_ACCESS_TIME	最后一次访问时间	0
PART_NAME	分区名	hour=15/last_msisdn=0
SD_ID	分区存储ID	43
TBL_ID	表ID	22
LINK_TARGET_ID		NULL

（2）PARTITION_KEYS:该表存储分区的字段信息

表字段	说明	示例数据
TBL_ID	表ID	22
PKEY_COMMENT	分区字段说明	NULL
PKEY_NAME	分区字段名	hour
PKEY_TYPE	分区字段类型	int
INTEGER_IDX	分区字段顺序	0

（5）PARTITION_KEY_VALS:该表存储分区字段值

表字段	说明	示例数据
PART_ID	分区ID	21
PART_KEY_VAL	分区字段值	0
INTEGER_IDX	分区字段值顺序	1

（6）PARTITION_PARAMS:该表存储分区的属性信息

表字段	说明	示例数据
PART_ID	分区ID	21
PARAM_KEY	分区属性名	numFiles，numRows
PARAM_VALUE	分区属性值	1，502195

Hive 元数据定义与操作存储 

一、Hive元数据定义

Hive的元数据指的是描述和存储有关数据和表结构的信息，包括数据库、表、列、分区等的定义和属性。

Hive的元数据在数据仓库中扮演着核心角色，它们帮助Hive理解和操作存储在底层文件系统（如HDFS）中的数据。这些元数据不仅包括了表和列的名称、数据类型等基本信息，还包含了其他重要的属性信息，比如：

- 数据库信息：包含数据库定义、名称、所有者、创建时间等。
- 表的信息：每个表的元数据涵盖了列名、列的数据类型、分区信息、存储格式、所有者等信息。
- 列的信息：列的元数据则包括列名称、数据类型、注释等细节。
- 分区信息：对于分区表来说，元数据还包括分区列、分区值、分区路径等关键信息。

此外，Hive的元数据存储在一个特殊的数据库中，这个数据库可以是Hive内置的Derby，或者是外部的关系型数据库如MySQL。元数据服务（Metastore）是Hive用来管理这些库表元数据的服务。通过Metastore服务，用户无需直接访问物理存储即可获取到结构化的库表信息，从而构建计算框架。

总的来说，Hive的元数据是其能够有效管理和查询大规模数据集的关键。它不仅是Hive内部操作的基础，也是用户与Hive交互的重要桥梁。

二、Hive数据操作存储

1、创建数据库、表、视图 

① 创建数据库

语法格式：

create database [if not exists] <数据库名>;

②创建表

语法格式：

create table [if not exists] <表名> row format delimited fields terminated by '分隔符'  [location  '外部表地址'];

注：如果不指定分隔符，hive表默认分隔符为 ^A (\001) ，这是一种特殊的分隔符，使用的是 ASCII 编码的值，键盘是打不出来的，故一般需要手动添加分隔符。[location  '外部表地址']创建一张外部表，指定地址，如果不指定location地址，则会默认存储到该数据库中。

③ 创建视图

语法格式：

create view <视图名> as select <视图的列，使用逗号隔开> from <表名>;

 2、删除数据库、表、视图

① 删除数据库

语法格式：

drop database [if exists] <数据库名>

注：如果该数据不是一个空的数据库，则需要删除数据库内的内容方可删除，或者强制删除数据库

强制删除（慎用）：

drop database <数据库名> cascade;

② 删除表

语法格式：

drop table [if exists] <表名>

③ 删除视图

语法格式：

drop view [if exists] <视图名>

3、修改数据库、表、视图

① 修改数据库

语法格式：

alter database <数据库名> ....

hive> alter database student_db set dbprtperties('edited-by'='steven'); # 修改数据库属性

② 修改表

语法格式：

alter table <表名> ....

hive> alter table student rename to studentinfo;  # 将student表名更改为studentinfo（重命名）
hive> alter table studentinfo add columns(address string);  # 为studentinfo添加一列address

4、 查看数据库、表、视图

① 查看数据库表

hive> show databases;
hive> show databases like 'h.*' # 查看和h开头的所有数据库

② 查看表和视图

hive> show tables;  # 查看表和视图
hive> show tables in student_db like 'v.*'; # 查看在student_db中的所有以u开头的表和视图

③ 查看数据库、表、视图的信息

hive> desc database student_db; # 查看数据的描述性信息
hive> desc extended student;    # 查看表信息
hive> desc extended stu_external;   # 查看外部表信息
hive> desc viewstudent; # 查看视图

5、向表装载数据

语法格式：

load data [local] inpath '数据路径' [overwrite] into table <表名>;

local--从本地虚拟机中获取数据，不过不使用local，则从HDFS中获取数据。

overwrite--覆盖表的原数据，如果不使用overwrite，则会在表末尾追加数据。

hive> load data local inpath '/root/userinfo.txt' overwrite into table student; # 将userinfo.txt的数据覆盖到student表中
hive> load data local inpath '/root/userinfo_append.txt' into table student;        # 将userinfo_append.txt数据追加到student表中

 Hive 数据关联操作

一、Hive数据关联概念

Hive支持多种数据关联操作，包括内连接（INNER JOIN）、左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

1. 内连接（INNER JOIN）：这是最常见的一种连接方式，只有当两个表中都存在与连接条件相匹配的数据时，这些数据才会出现在结果集中。
2. 左外连接（LEFT OUTER JOIN）：这种连接方式会返回左表的所有记录，即使右表中没有匹配的记录，右表中的字段将返回NULL值。
3. 右外连接（RIGHT OUTER JOIN）：与左外连接相反，右外连接返回右表的所有记录，如果没有匹配的记录，左表中的字段将返回NULL值。
4. 全外连接（FULL OUTER JOIN）：返回两个表中所有记录，如果没有匹配的记录，对应的字段将返回NULL值。

此外，在Hive中，关联操作通常通过JOIN语句实现，且只支持等值连接，不支持非等值连接。在进行关联查询时，可以使用ON子句来指定连接条件。例如，SELECT e.id, e.name, e_a.city, e_a.street FROM employee e INNER JOIN employee_address e_a ON e.id = e_a.id;这个查询将会返回employee表和employee_address表中id字段相匹配的所有记录。

总的来说，Hive的数据关联操作是处理和分析大规模数据集时的常用技术，它允许用户根据特定条件将来自不同表的数据结合起来，以便进行更深入的数据分析。

二、Hive数据关联操作

1、内联接（INNER JOIN）: 返回两个表中有匹配的记录。

语法：table_reference [inner] join table_factor [join_condition]

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.common_column = b.common_column;

 2、左外联接（LEFT OUTER JOIN）: 返回左表的所有记录，即使右表中没有匹配。

语法：table_referrence  left[outer] join table_factor [join_condition]

SELECT a.*, b.*
FROM table_a a
LEFT OUTER JOIN table_b b ON a.common_column = b.common_column;

3、右外联接（RIGHT OUTER JOIN）: 返回右表的所有记录，即使左表中没有匹配。

语法：table_reference right [outer] join table_factor [join_condition]

SELECT a.*, b.*
FROM table_a a
RIGHT OUTER JOIN table_b b ON a.common_column = b.common_column;

 4、全外联接（FULL OUTER JOIN）: 返回两表中任意一个表的所有记录。

语法：table_reference full [outer]  join table_factor [join_condition] 

SELECT a.*, b.*
FROM table_a a
FULL OUTER JOIN table_b b ON a.common_column = b.common_column;

 Hive 聚合操作

一、Hive聚合方法

Hive提供了一系列的聚合函数来执行数据分析和统计计算，这些函数可以对一组值进行计算并返回单个结果。常用的聚合函数包括：

• COUNT：用于统计行数。COUNT(*)返回总行数，而COUNT(column)返回指定列非空值的行数。
• SUM：计算指定列的总和。
• AVG：返回指定列的平均值。
• MIN：找出指定列中的最小值。
• MAX：找出指定列中的最大值。

此外，Hive还支持高级分组聚合，如GROUPING SETS、CUBE和ROLLUP，这些方法可以简化SQL语句并提升性能。例如，使用GROUPING SETS可以在一个GROUP BY语句中指定多个分组聚合列，这通常可以用UNION连接的多个GROUP BY查询逻辑来表示。

在实际应用中，聚合函数通常与GROUP BY子句一起使用，以便对指定字段进行分组统计。在一些场景中，可能需要对分组字段进行不同组合的分组统计，这时就可以用到聚合增强函数，如GROUPING SETS等。

总的来说，Hive的聚合方法为处理和分析大规模数据集提供了强大的工具，使得用户能够方便地进行数据统计和分析。

二、Hive聚合操作

1、聚合函数

（1）sum，max，min，avg。

查询员工的最大、最小、平均工资及所有工资的和 

hive> select max(salary),min(salary),avg(salary),sum(salary) from emp;

（2）count

查询记录数

hive> select count(*) from emp;
hive> select count(1) from emp;

2、分组函数（Group by）

（1）按照部门进行分组

hive> select deptno from emp group by deptno;

（2）查询每个部门的平均工资

hive> select deptno,avg(salary) avg_sal from emp group by deptno; 

（3）查询平均工资大于2000的部门（使用having子句限定分组查询）

hive>  select deptno,avg(salary) from emp group by deptno having avg(salary) > 2000;

（4）按照部门和入职时间进行分组（先按照部门进行分组，然后针对每组按照入职时间进行分组）

hive> select deptno,hiredate from emp group by deptno,hiredate;

（5）按照部门和入职时间进行分组并计算出每组的人数

hive> select deptno,hiredate,count(ename) from emp group by  deptno,hiredate;

3、case when then end

查询员工的姓名和工资等级，按如下规则显示
salary小于等于1000，显示LOWER
salaray大于1000且小于等于2000，显示MIDDLE
salaray大于2000小于等于4000，显示MIDDLE
sal大于4000，显示highest

select ename, salary, 
case
when salary > 1 and salary <= 1000 then 'LOWER'
when salary > 1000 and salary <= 2000 then 'MIDDLE'
when salary > 2000 and salary <= 4000 then 'HIGH'
ELSE 'HIGHEST'
end
from emp;

Hive 窗口函数

一、Hive窗口函数介绍

窗口函数是指，在指定的数据滑动窗口中，实现各种统计分析的操作。

在日常的使用中，窗口函数是与分析函数一起使用，或按照专用窗口函数使用，组成比如：窗口聚合函数、窗口排序函数等实用函数。

语法：

分析函数/专用窗口函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

1 什么是分析函数和专用窗口函数？

常用的分析函数：sum()、max()、min()、avg()、count()、......

专用窗口函数：row_number()、rank()、dense_rank()......

2 什么是窗口函数？

over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

窗口函数的3个组成部分可以单独使用，也可以混合使用，也可以全都不用。

• partition by 字段

对指定的字段进行分组，后续都会以组为单位，把每个分组单独作为一个窗口进行统计分析操作。

• order by 字段

order by 与 partition by 连用的时候，可以对各个分组内的数据，按照指定的字段进行排序。如果没有 partition by 指定分组字段，那么会对全局的数据进行排序。

• rows between 开始位置 and 结束位置

rows between 是用来划分窗口中，函数发挥作用的数据范围。我们用如下例子加深 rows between 的理解。

rows between 常用的参数如下：

① n preceding：往前

② n following：往后

③ current row：当前行

④ unbounded：起点/终点（一般结合preceding，following使用）

rows between unbounded preceding and current row（表示从起点到当前行的数据进行）
rows between current row and unbounded following（表示当前行到终点的数据进行）
rows between unbounded preceding and unbounded following （表示起点到终点的数据）
rows between 1 preceding and 1 following（表示往前1行到往后1行的数据）
rows between 1 preceding and current row（表示往前1行到当前行）

二、Hive 窗口函数使用

 1、排序窗口函数

（1）排序并产生自增编号，自增编号不重复且连续

我们可以使用函数：row_number() over()

语法：

row_number() over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

 （2）排序并产生自增编号，自增编号会重复且不连续

我们可以使用函数：rank() over()

语法：

rank() over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

（3）排序并产生自增编号，自增编号会重复且连续

我们可以使用函数：dense_rank() over()

语法：

 dense_rank() over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

2、聚合窗口函数

（1） 求窗口中的累计值

我们可以使用：sum() over()

（2）求窗口中 的平均价格

我们可以使用 avg() over()

（3）求分组中的最大值/最小值

我们可以使用 min() max()

（4）求分组中的总记录数

我们可以使用 count()

3、位移窗口函数

（1）获取分组中往前 n 行的值

基础语法：

lead(field,n,default_value) over()

语法解析：

1. field 是指定的列名

2. n 是往前的行数

3. 行往前导致的，最后的 n 行值为 null，可以用 default_value 代替。

（2）获取分组中往后 n 行的值

基础语法：

lag(field,n, default_value) over()

语法解析：

1. field 是指定的列名

2. n 是往前的行数

3. 行往后导致的，前面的 n 行值为 null，可以用 default_value 代替。

4、极值窗口函数

（1）获取分组内第一行的值

我们可以使用 first_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，第一个值。

 注意：

1. 当第二个参数为 true 的时候，会跳过空值
2. 当 over() 中不指定排序的时候，会默认使用表中数据的原排序。

（2）获取分组内最后一行的值

我们可以使用 last_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，最后一个值。所以，如果使用 order by 排序的时候，想要取最后一个值，需要与 rows between unbounded preceding and unbounded following 连用。

注意：

1. 当第二个参数为 true 的时候，会跳过空值
2. 当 over() 中不指定排序的时候，会默认使用表中数据的原排序。
3. 当 over() 中指定排序的时候，要与 rows between unbounded preceding and unbounded following 连用

三、（练习）订单任务

任务1-订单关联查询并保存

create table order_details as
select order_items.order_item_order_id,
orders.order_date,
customers.customer_name,
customers.customer_city,
products.product_name,
categories.category_name,
order_items.order_item_quantity,
order_items.order_item_product_price
from
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id
join products on order_items.order_item_product_id=products.product_id
join categories on products.product_category_id=categories.category_id
limit 10;

任务2-零售商品业务数据查询

select a.id,sum(a.subtotal) total
from
(select orders.order_customer_id id,order_items.order_item_subtotal subtotal
from
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id) a
group by a.id order by total desc
limit 10;

任务3-使用窗口函数查询

#统计每日订单量
select substring(orders.order_date,0,10) daystr,
count(order_id) over(partition by substring(orders.order_date,0,10))as row_count
from orders order by row_count desc limit 10;
#统计每日销售额排行
select b.* from (
select a.daystr days,sum(a.subtotal) total,
RoW_NUMBER() over(partition by a.daystr order by sum(a.subtotal) desc) as rn
from
(select substring(orders.order_date,0,10) daystr,
order_items.order_item_subtotal subtotal
from orders join order_items on orders.order_id=order_items.order_item_order_id) a
group by a.daystr) b
where b.rn<=10;

Hive 内置函数

一、Hive内置函数概念

Hive是一个构建在Hadoop之上的数据仓库，它提供了类似SQL的查询语言HiveQL，让不熟悉MapReduce开发者也能编写数据查询语句。Hive的一个主要优势是它提供了一系列的内置函数（或称为内置操作符），这些函数可以直接在HiveQL查询中使用，以完成数据的处理和分析。

Hive内置函数主要用于集合函数、数学函数、日期函数、字符串函数和条件判断函数等方面。例如：

条件判断函数：IF、WHEN、CASE、COALESCE等。

字符串函数：LENGTH、SUBSTR、CONCAT、TRIM、LOWER、UPPER等；

集合函数：SUM、MAX、MIN、AVG、COUNT等；

数学函数：ROUND、EXP、LOG、SIGN等；

日期函数：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等；

二、Hive内置函数使用

1、数学函数: 提供了一些常用的数学运算，例如sqrt（计算平方根），rand（生成随机数）。

select rand();      -- 0.0 ~ 1.0, 包左不包右.
select round(4.3);  -- 四舍五入, 原理: +0.5, 然后求地板数.
 
select abs(-10);    -- 求绝对值
select ceil(5.3);   -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身)      6
select ceil(5.0);   -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身)      5
select floor(5.6);  -- 地板数, 比该数小的所有数字中, 最大的那个整数(包括本身)        5

2、字符串函数: 提供了一些用于字符串操作的函数。

1. 字符串切割

-- spilt(参数1,参数2)     参数1: 要操作的参数.  参数2: 切割符
select spilt("aa,bb,cc",",");        -- ["aa","bb","cc"]
 
-- substr(参数1,参数2,参数3) 和 substring(参数1,参数2,参数3) 
-- 参1: 要操作的字符串.  参2: 起始索引, 参数3: 个数
-- 效果一样, 没有区别
select substr('2024-01-13 11:51:27', 1, 10);
select substring('2024-01-13 11:51:27', 1, 10);

 2. 字符串拼接

-- concat() 拼接符默认为空
select concat('aa','bb','cc');   aabbcc
 
-- concat_ws(参数1,参数2) 可以指定拼接符  参数1:拼接符   参数2:要拼接的内容
select concat_ws('-','aa','bb','cc');   aa-bb-cc

3. 获取字符串长度

select length('abc');

 4. 转大小写

select lower('ABC'); -- abc 转小写

select upper('abc'); -- ABC 转大写

5. 移除首尾空格

select trim('    aa   bb    ');     -- 'aa   bb'

6. 正则替换, 参数1:要被处理的字符串. 参数2:正则表达式. 参数3:用来替换的内容

 select regexp_replace('100-200', '\\d+', '夯哥');

3、日期函数: 提供了一些用于日期操作的函数，例如year（获取年份），month（获取月份）。

select current_date();      -- 获取当前时间, 年月日格式
 
select unix_timestamp();    -- 获取当前时间, unix格式, 即: 从时间原点(1970-01-01 00:00:00)截止到当前时间的 秒值.
 
select unix_timestamp('2024/01/12', 'YYYY/mm/dd');  -- 根据指点的时间, 获取其unix时间.
 
-- 根据秒值(unix时间) 获取其对应的 日期.
select from_unixtime(1684477644);       -- 2023-05-19 06:27:24
 
-- 获取年月日.
select year('2023-05-19 14:31:02');     -- 2023
select month('2023-05-19 14:31:02');     -- 5
select day('2023-05-19 14:31:02');     -- 19
 
-- 时间相关操作, 增加, 减少, 比较.
select date_add('2023-05-19', 2);       -- 2023-05-21
select date_add('2023-05-19', -2);       -- 2023-05-17
select datediff('2023-05-19', '2023-05-20');    -- 前 - 后,  -1
 

 4、聚合函数: 提供了一些聚合操作，例如sum（求和），count（计数）。

SELECT sum(salary) FROM employees;
SELECT count(DISTINCT id) FROM employees; 

5、条件函数: 提供了类似于if-else的条件判断功能，例如CASE WHEN THEN ELSE END。

SELECT CASE WHEN age < 20 THEN 'youth' 
            WHEN age >= 20 AND age < 40 THEN 'middle age' 
            ELSE 'elderly' 
       END 
FROM people;

 6、转换函数: 提供了类型转换的功能，例如cast

 SELECT cast(salary AS string) FROM employees;

练习 

SELECT product_name,
       substr(product_name, 1, 10 + locate(' ', substr(product_name, 10))) as short_product_name
FROM products;

Hive视图概念与常用操作 

1、Hive视图概念

 Hive 中的视图（View）是一个虚拟表，它基于一个或多个表的查询结果。视图不存储数据，它只存储查询语句的定义。当你查询视图时，Hive 会根据视图的定义执行查询，并返回结果。视图可以简化复杂的查询，提供数据的安全性（因为用户只能看到视图定义中的数据），以及提供数据的逻辑抽象。

1. 虚拟表：视图是一个虚拟表，其内容由查询结果定义，不占用存储空间。
2. 数据封装：视图可以简化复杂查询，将它们封装成简单的表结构，便于用户使用。
3. 动态数据：视图在每次查询时都会执行定义它的查询语句，以获取最新的数据。
4. 数据安全：视图可以限制用户访问数据的一部分，提供额外的数据安全层。

2、Hive常用操作 

1.创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

通过上述语句，可以创建一个名为view_name的视图，其内容是基于table_name表的一个查询结果。 

2.查询视图： 与查询普通表一样，使用SELECT语句查询视图

SELECT * FROM view_name;

3.修改视图： Hive 不直接支持视图的修改，但是可以通过DROP和CREATE操作间接实现

DROP VIEW view_name;
CREATE VIEW view_name AS
-- 新的查询语句

4.删除视图： 使用DROP VIEW语句可以删除视图

DROP VIEW view_name;

5.查看视图结构： 使用DESCRIBE语句可以查看视图的结构

DESCRIBE view_name;

6.视图与表的差异

视图是虚拟的，不存储数据；表是实际存储数据的。
视图的创建基于已有的表或视图；表是直接创建的。
对视图的查询实际上是执行了视图定义中的查询语句；对表的查询直接从表中读取数据。
注意事项

视图定义中的查询不能包含 ORDER BY 和 LIMIT 子句。
视图不能包含 INSERT、UPDATE 或 DELETE 操作。

 练习

 

CREATE VIEW orders_products_items_view AS
SELECT
    o.order_id,
    o.order_date,
    o.order_status,
    p.product_name,
    p.product_price,
    oi.product_quantity
FROM
    order_items oi
JOIN
    orders o ON oi.order_id = o.order_id
JOIN
    products p ON oi.product_id = p.product_id;