数据仓库-CSDN博客

本文链接：https://blog.csdn.net/weixin_63676253/article/details/136498835

一.数据仓库概述

1.什么是数据仓库？

Bill Inmon：数据仓库是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，用于对管理决策过程的支持。

Ralph Kimball：数据仓库是一个将源系统数据抽取、清洗、规格化、提交到维度数据存储的系统，为决策的制定提供查询和分析功能的支持与实现。

数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持。

2.数据仓库基本概念

1.数据源

从外部为数据仓库系统提供进行分析的数据，这些数据来源称为数据仓库的数据源。数据源并不局限于传统数据库，也可以是非结构化的信息数据。

2.ETL
ETL 是将业务系统中的数据经过抽取（Extract）、清洗转换（Transform）和加载（Load）到数据仓库的过程，目的是将企业中的分散、凌乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

ETL 处理分为五大模块，分别是：

数据抽取
数据清洗
数据转换
规则检查
数据装载。
各模块之间灵活组合，形成 ETL 处理流程。下面简单介绍一下各模块之间的功能。

3.数据抽取
数据仓库既然是面向主题的，那么在数据源中，只有那些与主题相关的内容才是必需的、有使用价值的。因此，必须以主题的需求为依据，对数据源的内容进行有目的地选择，这一过程被称为“数据抽取”（Data Extraction）。对于数据的抽取，是从各个不同的数据源抽取到 ODS（Operational Data Store，操作型数据存储）中。

（1）对于与存放 DW 的数据库系统相同的数据源处理方法

一般情况下，DBMS（Mysql、SQLServer）都会提供数据库连接功能，在 DW 数据库服务器和原业务系统之间建立直接的连接关系，接下来就可以写查询语句直接访问。

（2）对于与存放 DW 的数据库系统不同的数据源处理方法

一种是通过工具将数据源导出成 .txt 或者 .xls 文件，然后再将这些源系统文件导入到 ODS 中。另一种方法是通过程序接口来完成。

（3）对于文件类型数据源（.txt/.xls）

业务人员可以利用数据库工具将这些数据导入到指定的数据库，然后从指定的数据库中抽取。或者业务人员借助工具实现。

（4）增量更新问题

一般情况，业务系统会记录业务发生的时间，可以用作增量的标志，每次抽取之前首先判断 ODS 中记录最大的时间，然后根据这个时间去业务系统取大于这个时间的所有记录。

4.数据仓库具有以下特点：

集成性（Integration）：数据仓库集成了来自多个不同来源的数据，包括内部和外部数据源，使得用户可以在一个统一的平台上访问和分析数据。
主题性（Subject-Oriented）：数据仓库按照特定的主题或业务领域进行组织和存储，而不是按照应用系统的结构来组织数据。这使得用户可以根据业务需求来理解和分析数据。
时态性（Time-Variant）：数据仓库存储的数据包含了历史数据，用户可以分析和了解数据随时间的变化趋势，支持时间序列分析和历史数据的比较。
非易失性（Non-Volatile）：数据一旦进入数据仓库就不会被轻易修改或删除，而是保留在其中作为历史记录，确保数据的完整性和可追溯性。
一致性（Consistency）：数据仓库中的数据经过清洗、集成和转换，保证了数据的一致性和准确性，使用户可以信任数据进行决策和分析。
易用性（Ease of use）：数据仓库提供了灵活、直观的界面和工具，使用户可以方便地访问、查询和分析数据，而无需深入了解数据的底层结构和技术细节。
高性能（High Performance）：数据仓库通过优化存储结构、查询处理和索引技术，提供了高性能的数据查询和分析能力，以满足用户对实时和大规模数据处理的需求。

二.HIVE数据库及表操作

1.hive数据类型

原始数据类型：
- TINYINT: 一个字节的有符号整数，取值范围为-128到127。
- SMALLINT: 两个字节的有符号整数，取值范围为-32768到32767。
- INT/INTEGER: 四个字节的有符号整数，取值范围为-2147483648到2147483647。
- BIGINT: 八个字节的有符号整数，取值范围为-9223372036854775808到9223372036854775807。
- FLOAT: 单精度浮点数，取值范围为-3.4028235E38到3.4028235E38。
- DOUBLE: 双精度浮点数，取值范围为-1.7976931348623157E308到1.7976931348623157E308。
- BOOLEAN: 代表布尔值，取值为true或false。
- STRING: 字符串类型，支持任意长度的字符串。
- VARCHAR: 可变长度的字符串类型，可以指定最大长度。
- CHAR: 固定长度的字符串类型，需要指定长度。
日期/时间类型：
- DATE: 代表日期，以"YYYY-MM-DD"的格式存储。
- TIMESTAMP: 代表时间戳，存储日期和时间信息。
复杂数据类型：
- ARRAY: 数组类型，可以包含多个相同类型的元素。
- MAP: 映射类型，类似于字典，包含键值对。
- STRUCT: 结构类型，可以包含多个字段。
二进制类型：
- BINARY: 二进制数据类型，存储二进制数据。

2.hive的DDL操作

创建数据库：
```
CREATE DATABASE database_name;
```
使用数据库：
```
USE database_name;
```
展示数据库列表：
```
SHOW DATABASES;
```

创建表：

CREATE TABLE table_name (
    column1_name column1_data_type,
    column2_name column2_data_type,
    ...
) [ROW FORMAT ...] [STORED AS ...] [LOCATION ...];

展示表列表：
```
SHOW TABLES;
```
查看表结构：
```
DESC[RIBE] table_name;
```
删除表：
```
DROP TABLE table_name;
```

创建分区表：

CREATE TABLE table_name (
    column1_name column1_data_type,
    column2_name column2_data_type,
    ...
) PARTITIONED BY (partition_column_name partition_column_data_type, ...);

添加分区：

ALTER TABLE table_name ADD PARTITION (partition_column=value, ...);

删除分区：

ALTER TABLE table_name DROP PARTITION (partition_column=value, ...);

....

三.hive元数据

1.hive元数据介绍

Hive中的元数据是指描述和管理存储在Hive中的数据的数据，也称为表的元数据。这些元数据包括表的结构（列名、数据类型、分区等）、表的位置（存储位置）、表的属性（例如压缩格式、存储格式）以及其他相关信息。Hive使用元数据来管理数据和执行查询。

元数据通常存储在Hive的元存储数据库中，默认情况下是Derby数据库，但也可以配置为使用其他数据库，如MySQL或PostgreSQL。元数据由Hive的元数据管理服务（Metastore）来管理和维护。

通过元数据，用户可以轻松地了解Hive中可用的表、表的结构、存储位置等信息。元数据还可以帮助Hive优化查询执行，例如通过元数据了解表的分区信息，可以避免扫描整个表来执行查询，从而提高查询性能。

总的来说，Hive中的元数据对于管理和查询存储在Hive中的数据非常重要，它提供了关于数据的结构和位置的关键信息，为用户提供了方便的数据管理和查询功能。

2.元数据概念：

元数据的概念指的是描述数据的数据。简单来说，元数据是关于数据的信息，而不是数据本身。它提供了关于数据的特征、结构、属性以及其它相关信息的描述。元数据通常用于帮助组织、管理和理解数据。

在计算机科学领域，元数据扮演着重要的角色，它们在各种系统和应用中被广泛使用。例如，在数据库中，元数据描述了表的结构、列的数据类型、索引信息等；在文件系统中，元数据描述了文件的大小、创建时间、修改时间等属性；在数据仓库中，元数据描述了数据集的来源、格式、变换规则等。

元数据对于数据管理、数据分析、数据治理等方面都至关重要。它们可以帮助用户理解数据的含义和结构，提高数据的可发现性和可理解性，同时也为数据集成、数据质量控制等提供支持。

四.Hive高级操作

一.Hive 的高级操作
Hive 提供了丰富的功能来处理和分析大规模数据集。除了基本的 CRUD（创建、读取、更新、删除）操作，Hive 还支持一些高级操作，能够满足更复杂的数据处理需求。

1. 分区和分桶
分区 (Partitioning)
分区是将表的数据分成更小的部分，以提高查询性能和管理效率。分区可以基于一个或多个列，例如日期、国家等。使用分区后，Hive 只需扫描相关分区中的数据，从而减少扫描的数据量。

创建分区表示例：

CREATE TABLE sales ( item_id INT, amount DOUBLE ) PARTITIONED BY (country STRING, year INT, month INT, day INT);

加载数据到分区表：

LOAD DATA INPATH 'hdfs:///path/to/data' INTO TABLE sales PARTITION (country='USA', year=2023, month=6, day=1);

分桶 (Bucketing)
分桶是将数据进一步划分成更小的部分，常用于分区表。每个分区可以包含多个桶，桶的数量是预先定义的，数据根据哈希值分配到不同的桶中。

创建分桶表示例：

CREATE TABLE bucketed_sales ( item_id INT, amount DOUBLE ) CLUSTERED BY (item_id) INTO 10 BUCKETS;

2. 动态分区插入
动态分区插入允许在插入数据时自动创建分区，而不需要提前手动创建分区。

开启动态分区：

SET hive.exec.dynamic.partition = true; SET hive.exec.dynamic.partition.mode = nonstrict;

动态分区插入示例：

INSERT INTO TABLE sales PARTITION (country, year, month, day) SELECT item_id, amount, country, year, month, day FROM staging_sales;

3. 窗口函数
窗口函数在数据分析中非常有用，能够在指定的数据窗口（如行或分区）内执行复杂的计算。

窗口函数示例：

SELECT item_id, amount, RANK() OVER (PARTITION BY country ORDER BY amount DESC) as rank FROM sales;

4. 视图和物化视图
视图是基于查询定义的虚拟表，物化视图则存储查询的结果，提高查询性能。

创建视图示例：

CREATE VIEW high_sales AS SELECT item_id, amount FROM sales WHERE amount > 1000;

创建物化视图示例：

CREATE MATERIALIZED VIEW monthly_sales_summary AS SELECT year, month, SUM(amount) as total_sales FROM sales GROUP BY year, month;

5. 外部表
外部表将 Hive 表映射到外部存储（如 HDFS 上的文件），删除外部表时数据不会被删除。

创建外部表示例：

CREATE EXTERNAL TABLE external_sales ( item_id INT, amount DOUBLE ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION 'hdfs:///path/to/external/data';

6. UDF、UDAF 和 UDTF
Hive 支持用户自定义函数（UDF）、用户自定义聚合函数（UDAF）和用户自定义表生成函数（UDTF），以扩展 Hive 的功能。

创建 UDF 示例（Java 代码）：

package com.example.hive.udf; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.Text; public class UpperCaseUDF extends UDF { public Text evaluate(Text input) { if (input == null) { return null; } return new Text(input.toString().toUpperCase()); } }

注册和使用 UDF：

ADD JAR /path/to/udf.jar; CREATE TEMPORARY FUNCTION upper_case as 'com.example.hive.udf.UpperCaseUDF'; SELECT upper_case(name) FROM users;

7. 数据类型转换和操作
Hive 支持多种数据类型，并提供丰富的数据转换和操作函数。

日期函数示例：

SELECT TO_DATE('2023-06-01') as date; SELECT DATE_ADD('2023-06-01', 10) as date_plus_10_days;

字符串函数示例：

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users; SELECT SUBSTR(name, 1, 3) as name_prefix FROM users;

8. 索引
索引可以加快查询速度，虽然在 Hive 中索引使用较少，但对于特定查询场景，索引可以显著提高性能。

创建索引示例：

CREATE INDEX idx_item_id ON TABLE sales (item_id) AS 'COMPACT' WITH DEFERRED REBUILD;

重建索引：

ALTER INDEX idx_item_id ON sales REBUILD;

五.hive函数与Streaming

Hadoop Streaming提供了一个便于进行MapReduce编程的工具包，使用它可以基于一些可执行命令、脚本语言或其他编程语言来实现Mapper和 Reducer，Streaming方式是基于Unix系统的标准输入输出来进行MapReduce Job的运行。

任何支持标准输入输出特性的编程语言都可以使用Streaming方式来实现MapReduce Job，基本原理就是输入从Unix系统标准输入，输出使用Unix系统的标准输出。

Streaming的实现需要TRANSFORM()函数和USING关键字，TRANSFORM()的参数是表的列名，USING关键字用于指定脚本

六.Hive视图与索引

1、Hive视图基本介绍
视图是一个虚拟表，其内容由查询定义。Hive 中的视图和RDBMS中视图的概念一致，都是一组数据的逻辑表示，本质上就是一条SELECT语句的结果集。视图是纯粹的逻辑对象，没有关联的存储(Hive 3.0.0引入的物化视图除外)，当查询引用视图时，Hive可以将视图的定义与查询结合起来，例如将查询中的过滤器推送到视图中。

2、Hive视图特点
（1）不支持物化视图；

（2）只能查询，不能做加载数据操作；

（3）视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询；

（4）view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行了ORDER BY/LIMIT语句操作，view当中定义的优先级更高；

（5）Hive视图支持迭代视图。

3、Hive视图的意义
（1）使用视图使数据操作更简便，相当于封装了sql语句，类似于关系型数据库中的存储过程。

（2）使用视图，基表中的数据有了一定的安全性，使用视图可以基于限制条件过滤数据，通过视图限制数据访问可以保护信息不被随意查询。

4、Hive视图的创建和删除
--创建视图：
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ... ;
--查询视图：
select colums from view;
--删除视图：
DROP VIEW [IF EXISTS] [db_name.]view_name;
案例：

CREATE VIEW gal_view IF NOT EXISTS AS SELECT * FROM product1 p LEFT JOIN logistic l ON p.sku=l.lsku;
5、视图的使用
SELECT sku,price FROM gal_view;
6、查询所有的hive视图
(1) show tables可以查看到新增的视图

（2）Hive的元数据信息一般存储在mysql，Hive的视图信息存储在mysql hive数据库中的 TBLS 表中，通过如下查询语句可以查询得到Hive中的所有视图：

SELECT * FROM TBLS WHERE TBL_TYPE='VIRTUAL_VIEW';

Hive索引
hive的索引可以提高数据的检索效率，避免全表扫描和资源浪费，对于含有group by的语句，对group by的字段添加索引可以提高查询速度。

1、Hive索引的实现原理
在指定列上建立索引，会产生一张索引表（Hive的一张物理表），里面的字段包括，索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量。

在执行索引字段查询时候，首先额外生成一个MR job，根据对索引列的过滤条件，从索引表中过滤出索引列的值对应的hdfs文件路径及偏移量，输出到hdfs上的一个文件中，然后根据这些文件中的hdfs路径和偏移量，筛选原始input文件，生成新的split,作为整个job的split,这样就达到不用全表扫描的目的。

2、创建索引
创建索引的字段最好使用不更新的字段，以免频繁重建索引数据。每次建立、更新数据后，都要重建索引以构建索引表。

删除索引

DROP INDEX IF EXISTS product1_index2 ON product1;

七.Hive调优

hive调优是比较大的专题，需要结合实际的业务，数据的类型，分布，质量状况等来实际的考虑如何进行系统性的优化，hive底层是mapreduce，所以hadoop调优也是hive调优的一个基础,hvie调优可以分为几个模块进行考虑，数据的压缩与存储，sql的优化，hive参数的优化，解决数据的倾斜等。

一，数据的压缩与存储格式
对分析的数据选择合适的存储格式与压缩方式能提高hive的分析效率：

1）压缩方式

压缩可以节约磁盘的空间，基于文本的压缩率可达40%+; 压缩可以增加吞吐量和性能量(减小载入内存的数据量)，但是在压缩和解压过程中会增加CPU的开销。所以针对IO密集型的jobs(非计算密集型)可以使用压缩的方式提高性能。几种压缩算法：

注意：选择压缩算法的时候需要考虑到是否可以分割，如果不支持分割（切片的时候需要确定一条数据的完整性），则一个map需要执行完一个文件，如果文件很大，则效率很低。一般情况下hdfs一个块（128M）就是一个map的输入切片，而block是按物理切割的，可能一条数据会被切到两个块中去，而mapde 切片如何确保一条数据在一个切片中呢？这就是看压缩算法支不支持分割了，具体的实现机制需要看源码研究。

2）存储格式（行存与列存）

1. TextFile

Hive数据表的默认格式，存储方式：行存储。可以使用Gzip压缩算法，但压缩后的文件不支持split 在反序列化过程中，必须逐个字符判断是不是分隔符和行结束符，因此反序列化开销会比SequenceFile高几十倍。

2.Sequence Files

Hadoop中有些原生压缩文件的缺点之一就是不支持分割。支持分割的文件可以并行的有多个mapper程序处理大数据文件，大多数文件不支持可分割是因为这些文件只能从头开始读。Sequence File是可分割的文件格式，支持Hadoop的block级压缩。 Hadoop API提供的一种二进制文件，以key-value的形式序列化到文件中。存储方式：行存储。 sequencefile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，RECORD是默认选项，通常BLOCK会带来较RECORD更好的压缩性能。优势是文件和hadoop api中的MapFile是相互兼容的

3. RCFile

存储方式：数据按行分块，每块按列存储。结合了行存储和列存储的优点：

首先，RCFile 保证同一行的数据位于同一节点，因此元组重构的开销很低其次，像列存储一样，RCFile 能够利用列维度的数据压缩，并且能跳过不必要的列读取数据追加：RCFile不支持任意方式的数据写操作，仅提供一种追加接口，这是因为底层的 HDFS当前仅仅支持数据追加写文件尾部。行组大小：行组变大有助于提高数据压缩的效率，但是可能会损害数据的读取性能，因为这样增加了 Lazy 解压性能的消耗。而且行组变大会占用更多的内存，这会影响并发执行的其他MR作业。

二、创建分区表，桶表，拆分表
1）创建分区表：（分区表相当于hive的索引，加快查询速度）

CREATE external TABLE table_name
(col1 string, col2 double)
partitioned by (date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' Stored AS TEXTFILE
location ‘xxxxx’;

alter table table_name add partitions(key = value) location 'xxxx' (收到设置分区，静态分区)
设置动态分区

set hive.exec.dynamic.partition=true;(可通过这个语句查看：set hive.exec.dynamic.partition;)
set hive.exec.dynamic.partition.mode=nonstrict; （它的默认值是strick，即不允许分区列全部是动态的）
SET hive.exec.max.dynamic.partitions=100000;(如果自动分区数大于这个参数，将会报错)
SET hive.exec.max.dynamic.partitions.pernode=100000;
2）创建桶表：

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

create table bucketed_user(id int,name string) clustered by (id)
sorted by(name) into 4 buckets row format delimited fields terminated by '\t'
stored as textfile;

3）拆分表：当你需要对一个很大的表做分析的时候，但不是每个字段都需要用到，可以考虑拆分表，生成子表，减少输入的数据量。并且过滤掉无效的数据，或者合并数据，进一步减少分析的数据量