数据仓库概述
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环境配置
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)。
- 内连接(INNER JOIN):这是最常见的一种连接方式,只有当两个表中都存在与连接条件相匹配的数据时,这些数据才会出现在结果集中。
- 左外连接(LEFT OUTER JOIN):这种连接方式会返回左表的所有记录,即使右表中没有匹配的记录,右表中的字段将返回NULL值。
- 右外连接(RIGHT OUTER JOIN):与左外连接相反,右外连接返回右表的所有记录,如果没有匹配的记录,左表中的字段将返回NULL值。
- 全外连接(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(),作用是:取分组内排序后,截止到当前行,第一个值。
注意:
- 当第二个参数为 true 的时候,会跳过空值
- 当 over() 中不指定排序的时候,会默认使用表中数据的原排序。
(2)获取分组内最后一行的值
我们可以使用 last_value(col,true/false) over(),作用是:取分组内排序后,截止到当前行,最后一个值。所以,如果使用 order by 排序的时候,想要取最后一个值,需要与 rows between unbounded preceding and unbounded following 连用。
注意:
- 当第二个参数为 true 的时候,会跳过空值
- 当 over() 中不指定排序的时候,会默认使用表中数据的原排序。
- 当 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;
Hive视图概念与常用操作
1、Hive视图概念
Hive 中的视图(View)是一个虚拟表,它基于一个或多个表的查询结果。视图不存储数据,它只存储查询语句的定义。当你查询视图时,Hive 会根据视图的定义执行查询,并返回结果。视图可以简化复杂的查询,提供数据的安全性(因为用户只能看到视图定义中的数据),以及提供数据的逻辑抽象。
- 虚拟表:视图是一个虚拟表,其内容由查询结果定义,不占用存储空间。
- 数据封装:视图可以简化复杂查询,将它们封装成简单的表结构,便于用户使用。
- 动态数据:视图在每次查询时都会执行定义它的查询语句,以获取最新的数据。
- 数据安全:视图可以限制用户访问数据的一部分,提供额外的数据安全层。
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 操作。