数据仓库总结

大家好，欢迎来到IT知识分享网。

​ 数据仓库概述 

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,……)];

（2）修改数据库

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

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

（3）删除数据库

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

2、Hive数据表操作

（1）创建表

TEMPOPARY：创建临时表，若未指定，则默认创建的是普通表

EXTERNAL：创建外部表，若未指定，则默认创建的是内部表

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目录下

（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命令

Hive内部表和外部表

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

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

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

内部表&外部表的区别：

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

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

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

二、Hive内部表操作

1、创建内部表

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、创建外部表

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、创建一个带分桶定义的表（分桶表）

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

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 元数据特点和作用

此外，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	创建时间
DB_ID	数据库ID	1
LAST_ACCESS_TIME	上次访问时间
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	属性值	、、TRUE

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

表字段	说明	示例数据
TBL_GRANT_ID	授权ID	1
CREATE_TIME	授权时间
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	分区创建时间
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，

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

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（生成随机数）。

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

1. 字符串切割

 2. 字符串拼接

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（获取月份）。

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

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

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

 SELECT cast(salary AS string) FROM employees;

Hive视图概念与常用操作 

1、Hive视图概念

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

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

2、Hive常用操作 

1.创建视图

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

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

SELECT * FROM view_name;

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

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

DROP VIEW view_name;

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

DESCRIBE view_name;

6.视图与表的差异

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

注意事项