【数据工程】6. 数据库、数据仓库与数据湖 (Databases, Data Warehouses and Data Lakes)
数据库、数据仓库与数据湖 (Databases, Data Warehouses and Data Lakes)
在现代数据工程中，理解数据来源、存储与管理方式至关重要。企业的数据来源多样，包括文件、数据库、API、日志及消息流，而数据的有效管理和共享则依赖数据库、数据仓库或数据湖的结构化方法。本篇博客将完整介绍数据获取方式、数据库方法及其优势，关系型数据库基础，以及各种数据库系统及使用场景。
数据获取 (Data Acquisition – Where does data come from?)
企业的数据来源多种多样，常见途径包括：
-
文件访问 (File Access)
- 企业已有文件或数据集
- 在线下载数据，例如 data.gov.au
- 常见文件格式：CSV、Excel，有时也包括 XML 或 JSON
- 也可能是非结构化文件，如文本或图像
-
数据库访问 / 应用数据库 (Database Access / Application Databases)
- 通过编程或 API 获取数据
- 示例：网页爬取（HTML）或使用 Web 服务 / IoT 设备 API 获取数据（XML/JSON）
-
变更数据捕获 (Change Data Capture, CDC) / 日志
- 消息与数据流
- 发布/订阅模式（Publish/Subscribe）
数据工程的第一步是明确数据来源，确定获取途径，以便后续清洗、分析和可视化操作。
数据库方法 (The Database Approach)
数据库方法是企业常用的数据管理方式，其核心理念是通过数据库管理系统（DBMS）集中管理数据。
-
中心化数据存储
数据被集中存放在共享的中央仓库中，避免数据分散和重复。 -
DBMS 管理
数据由数据库管理软件进行管理，保证数据一致性、完整性和安全性。 -
通过应用或用户访问
所有数据访问必须通过 DBMS，而非直接操作文件或表格，这确保了操作的规范性。
数据库方法在数据工程中的优势 (Advantages of Database-approach to Data Engineering)
采用数据库方法可以为数据工程带来多方面优势：
-
数据质量可控
- 数据由 DBMS 管理，可通过完整性约束、数据验证规则确保数据质量
-
改善数据共享
- 不同用户可以获得不同的数据视图
- 支持高效的并发访问，避免冲突
-
标准化强制执行
- 所有数据访问按照统一方式执行
- 便于维护和扩展，同时保证数据一致性
-
更好的数据可访问性与响应性
- 使用标准查询语言（SQL）进行数据访问
- 查询高效，易于实现复杂分析
-
安全性、备份与恢复、并发处理
- DBMS 提供权限管理和安全机制
- 灾难恢复更易实施
- 支持多用户同时操作而不产生数据冲突
关系型数据库 (Relational Databases)
关系型数据库是当前最广泛使用的数据模型，其主要概念是 关系 (relation)，本质上是一张带有行和列的表格。每个关系都有 模式 (schema)，描述表的列或字段。与电子表格不同，关系型数据库在规范化、约束和数据一致性方面更为严格。
关系型数据库系统 (Relational Database Systems)
- 数据存储在表格中，每行包含多个属性
- 同一格式的行组成“表格”（关系：一组元组）
- 每个表都有模式描述列名及类型
- 关系型数据库是这些表的集合，通常通过 键属性（Key Attributes）关联表
- 常见数据库系统：MySQL、PostgreSQL、Oracle、SQL Server
主键 (Primary Key)
- 主键是表中唯一标识一行的属性（或属性组合）
- 通常是自动递增 ID，永不为 NULL
- 数据库中 NULL 表示“未知”或“未提供”
外键 (Foreign Key)
- 当需要引用其他表的记录时，使用外键
- 外键在第二张表中定义，但引用第一张表的主键或唯一键
- 实质上是“逻辑指针”，实现表之间的关联
示例：关系型键 (Relational Keys)
假设有学生课程数据：
Students 表
| sid | name |
|---|---|
| 31013 | John |
Enrollments 表
| sid | ucode | grade |
|---|---|---|
| 31013 | I2120 | CR |
sid是 Students 表的主键- Enrollments 表的
sid是外键，指向 Students 表 - 可以组合多个列形成 复合主键 (Composite Primary Key)
模式图 (Schema Diagrams)
- 规范化的关系型数据库尽量避免冗余
- 每个事实（数据点）尽量只存储一次
- 不同图形符号用于可视化数据库模式
- 常见表示法：ER 图（Entity-Relationship Diagrams）
WaterInfo 示例：关系型数据库设计
为了更直观地理解关系型数据库设计，这里以水文站数据 WaterInfo 为例。
设计选项 1：直观 1:1 映射 (Option 1 – 1:1 Mapping)
| stationid | obsdate | level_water | discharge | temperature | ec25 | basin | site | sitename | long | lat | commence | cease | orga |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1001 | 2025-09-01 | 3.2 | 120 | 18.5 | 250 | Murray | S1 | Murray Sta1 | 145.0 | -36.5 | 2010-01-01 | WaterDept1 | |
| 1002 | 2025-09-01 | 20.1 | 210 | Darling | S2 | Darling Sta2 | 147.2 | -35.9 | 2012-05-10 | WaterDept2 | |||
| 1003 | 2025-09-01 | 2.8 | 80 | 200 | Murray | S3 | Murray Sta3 | 145.5 | -36.8 | 2015-03-20 | WaterDept1 |
问题：大量 NULL 值、冗余信息，不易扩展新的测量类型。
设计选项 2：规范化关系型模式 (Option 2 – Normalised Schema)
事实表：Measurements
| measurement_id | stationid | obsdate | sensor_id | obsvalue |
|---|---|---|---|---|
| 1 | 1001 | 2025-09-01 | 1 | 3.2 |
| 2 | 1001 | 2025-09-01 | 2 | 120 |
| 3 | 1002 | 2025-09-01 | 2 | 210 |
| 4 | 1003 | 2025-09-01 | 1 | 2.8 |
维度表：Sensors
| sensor_id | description | metric |
|---|---|---|
| 1 | Water Level | meters |
| 2 | Discharge | m³/s |
| 3 | Temperature | °C |
| 4 | Electrical Cond. | μS/cm |
维度表：Stations
| stationid | sitename | basin | site | long | lat | commence | cease | orga_id |
|---|---|---|---|---|---|---|---|---|
| 1001 | Murray Sta1 | Murray | S1 | 145.0 | -36.5 | 2010-01-01 | 1 | |
| 1002 | Darling Sta2 | Darling | S2 | 147.2 | -35.9 | 2012-05-10 | 2 | |
| 1003 | Murray Sta3 | Murray | S3 | 145.5 | -36.8 | 2015-03-20 | 1 |
维度表：Organisations
| orga_id | organisation |
|---|---|
| 1 | WaterDept1 |
| 2 | WaterDept2 |
各种数据库系统 (Various Database Systems)
现代数据库系统可按用途、结构和技术特点分类：
-
DBMS 类型
- 关系型数据库 (Relational Databases)
- 专用 / NoSQL 数据库 (Specialized / NoSQL Databases)
-
应用类型
- 分析型数据库 (Analytical)
- 事务型数据库 (Operational)
-
授权模式
- 商业数据库 (Commercial)
- 开源数据库 (Open-source)
-
技术平台
- 传统磁盘存储 (Disk-based)
- 内存数据库 (Main-memory)
- 云数据库 (Cloud-based)
主流数据库系统 (The Main Players)
-
商业 DBMS
- IBM DB2
- Oracle
- Microsoft SQL Server
- SAP ASE
-
分析型 DBMS
- Snowflake
- Teradata
- Vertica
-
开源 DBMS
- MySQL
- PostgreSQL
- SQLite
-
NoSQL 系统
- MongoDB
- Couchbase
- Redis
专用 DBMS 示例 (Specialised DBMS Examples)
-
文档型数据库 (Document Databases)：用于半结构化 JSON 数据
- MongoDB, Couchbase
-
图数据库 (Graph Databases)：管理节点和关系
- Neo4J
-
时序数据库 (Time Series Databases)：时间序列数据
- InfluxDB, TimescaleDB
-
可视化与内存数据库
- Tableau, Redis
与 DBMS 工作 (Work with DBMS)
-
数据管理系统选型多样，取决于基础设施、需求、预算与偏好
-
决策点：
- 构建 DBMS 无关的解决方案（DBMS-agnostic）
- 或紧密集成特定 DBMS
-
将计算逻辑推入 DBMS 可提高扩展性，但可能导致技术锁定
数据库作为数据源 (Databases as a Data Source – Data Gathering)
数据库不仅用于数据管理，也是数据分析的重要来源。获取数据的方式主要有两种：
-
在数据库中执行查询 (Push Queries into DBMS)
- 将查询语句推入 DBMS，由数据库执行计算，然后只获取查询结果
- 优点：充分利用数据库优化器、索引和并行计算
-
批量提取数据 (Extract Large Chunks or All Data)
- 将数据大块或整表提取到本地进行分析
- 优点：便于在 Python、R 等工具中自由处理
- 缺点：占用内存，数据库优化功能无法完全利用
在以下示例中，我们以 PostgreSQL 作为演示平台，但原则适用于任何数据库系统。
从 Python 访问数据库 (Example: Accessing Databases from Python)
要访问数据库，通常需要：
- 安装数据库驱动，例如
psycopg2（PostgreSQL 驱动） - 使用数据库凭证进行连接
- 将连接逻辑封装成函数，方便复用
使用 psycopg2 连接 PostgreSQL
import psycopg2
def connect():
conn = psycopg2.connect(
host="localhost",
database="waterinfo",
user="your_user",
password="your_password",
port=5432
)
return conn
conn = connect()
print("Connected:", conn)
conn.close()
使用 SQLAlchemy create_engine
SQLAlchemy 提供更高层次的接口：
from sqlalchemy import create_engine
# 创建 engine
engine = create_engine('postgresql+psycopg2://your_user:your_password@localhost:5432/waterinfo')
# 建立连接
conn = engine.connect()
print("Engine connected:", conn)
# 关闭连接
conn.close()
create_engine()可以让我们使用 Pandas 或其他工具直接操作数据库。
从 Python 查询 PostgreSQL (Querying PostgreSQL from Python)
可以直接在连接上执行 SQL 语句，也可以用 Pandas 读取结果为 DataFrame。
基本查询
query_stmt = "SELECT * FROM measurements LIMIT 5;"
# 使用 Pandas 读取 SQL 查询结果
import pandas as pd
df = pd.read_sql(query_stmt, conn)
print(df.head())
封装查询函数
def query(conn, sql_statement):
"""
执行 SQL 查询并返回 DataFrame
"""
return pd.read_sql(sql_statement, conn)
# 使用示例
df = query(conn, "SELECT * FROM stations WHERE basin='Murray';")
print(df.head())
conn.close()
从 SQL 数据库进行数据可视化 (Data Visualisation from SQL in Python)
可以直接用 Pandas + Matplotlib / Seaborn 对数据库查询结果进行可视化：
import matplotlib.pyplot as plt
import seaborn as sns
# 查询水位数据
df = query(conn, "SELECT obsdate, obsvalue FROM measurements WHERE sensor_id=1 LIMIT 100;")
# 时间序列折线图
plt.figure(figsize=(10,5))
plt.plot(pd.to_datetime(df['obsdate']), df['obsvalue'], marker='o')
plt.title("Water Level over Time")
plt.xlabel("Date")
plt.ylabel("Water Level (meters)")
plt.xticks(rotation=45)
plt.show()
# 直方图
sns.histplot(df['obsvalue'], bins=20, color='skyblue')
plt.title("Distribution of Water Levels")
plt.xlabel("Water Level (meters)")
plt.ylabel("Frequency")
plt.show()
这样可以直接把数据库作为数据源，无需先导出 CSV，再进行分析和可视化。
批量加载数据 (Bulk-Load Data from Storage)
除了按需查询，还可以一次性将整个表加载到 Python：
# 使用 Pandas 读取整张表
df_measurements = pd.read_sql_table('measurements', conn)
print(df_measurements.head())
优缺点
-
优点：
- 处理简单，直接在 Python 中分析
-
缺点：
- DBMS 优化无法发挥作用
- 数据量需能装入内存
高级用法
-
将计算逻辑推入 DBMS：
- 使用 SQL JOIN、索引或聚合函数
- 用户自定义函数 / 存储过程（Stored Procedures）
-
优势：可扩展到大型数据集
-
注意：过度依赖 DBMS 功能可能导致平台锁定（Lock-in）
通过以上方法，我们可以灵活选择“按需查询”还是“批量加载”，并结合 Python 强大的数据分析与可视化工具，实现完整的数据工程工作流。
数据库作为数据存储（Databases as Sink Data Storage）
到目前为止，我们主要讨论数据库作为数据源的情况。然而，数据库也常用作 数据存储的终点（Sink），用于存储清洗、分析或生成的数据。主要方式包括：
- 使用外部 GUI 或命令行工具
- 通过 SQL 程序化插入数据
- 使用 Pandas 的
to_sql()半自动加载
方法 1：外部 GUI / 命令行工具
- PostgreSQL 提供命令行工具
psql，可以直接从 CSV 文件加载数据：
\COPY tablename FROM 'filename.csv' CSV HEADER NULL '…';
-
也可以使用 GUI 工具，如 pgAdmin 的 “Import CSV…” 功能。
-
优点：
- 快速且直观
- 无需编程
-
缺点：
- 仅支持 1:1 的 CSV 到表映射
- 无法进行数据清洗或转换
- 遇到第一条错误记录时会停止
方法 2：通过 SQL 程序化插入
可以用脚本生成 INSERT 语句，或者直接在 Python 中执行：
生成 SQL 文件（Unix/awk 示例）
awk 'BEGIN {FS=","}
{print "INSERT INTO Stations VALUES ("$1","$2","$3","$4","$5","$6","$7","$8");"}' \
Stations_raw.csv > Stations.sql
Python 插入示例
insert_stmt = """
INSERT INTO Measurements (stationid, obsdate, sensor, obsvalue)
VALUES (%(station)s, CURRENT_DATE, %(sensor)d, %(value)d)
"""
for row in data:
params = {
'station': row['stationid'],
'sensor': row['sensor'],
'value': row['obsvalue']
}
success = query(conn, insert_stmt, params, False)
-
优点：
- 可针对数据特点优化
- 可灵活进行数据清洗和转换
-
缺点：
- 需要开发时间
- 数据或表结构变动可能需要手动调整
方法 3：Pandas to_sql()
Pandas 提供直接将 DataFrame 加载到数据库的功能：
import pandas as pd
# 假设已经有 DataFrame df
# 连接数据库
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:pass@localhost:5432/waterinfo')
conn = engine.connect()
# 将 DataFrame 写入数据库
df.to_sql('measurements', conn, if_exists='append', index=False)
conn.close()
-
优点：
- 灵活，可结合 Pandas 的数据清洗和转换工具
-
缺点：
- 需要手工编写脚本
- 默认生成表缺少外键、约束，且规范化程度依赖原始 DataFrame
数据库模式设计（Schema Design）
-
使用 GUI 或
to_sql()自动生成的模式通常：- 没有外键关系
- 没有完整性约束
- 规范化程度有限
-
可选方案：
- 先设计模式，再加载数据
- 使用 SQL DDL（Data Definition Language）创建表，并定义类型、约束
WaterInfo 数据库示例 DDL
CREATE SCHEMA WaterInfo;
CREATE TABLE Organisations (
code CHAR(3) PRIMARY KEY,
organisation VARCHAR(80)
);
CREATE TABLE Stations (
stationid INT PRIMARY KEY,
sitename VARCHAR(40) NOT NULL,
commence DATE,
cease DATE,
orga CHAR(3) REFERENCES Organisations(code)
);
CREATE TABLE Sensors (
sensor VARCHAR(6) PRIMARY KEY,
description TEXT,
metric TEXT
);
CREATE TABLE Measurements (
stationid INT REFERENCES Stations(stationid),
obsdate DATE,
sensor VARCHAR(6) REFERENCES Sensors(sensor),
obsvalue NUMERIC,
PRIMARY KEY (stationid, obsdate, sensor)
);
使用自定义模式可以增加完整性约束、外键关系和数据类型控制，但加载原始数据时可能需要先清洗。
非关系型数据库（Non-relational Databases）
尽管表格模型在很多场景下最自然，但还有其他存储方案：
- Key/Value 数据库
- 时间序列数据库
- 空间数据库
- 文档数据库 (JSON)
- 图数据库
这些数据库在加载和使用上有不同方式，例如：
- MongoDB / Couchbase：JSON 文档直接插入
- InfluxDB / TimescaleDB：时间序列直接写入
- neo4j：通过节点和关系插入图数据
总体来说，数据加载原则类似，但实现方式与 SQL 数据库有所差异，需要针对具体系统调整。
码道开发者社区，聚焦华为云码道 CodeArts 代码智能体，沉淀 Agent、Skill、鸿蒙开发实战内容，供开发者查阅资料、交流技术、分享工程实践
更多推荐
20
0- 0
已为社区贡献1条内容
所有评论(0)