以Mysql为平台，设计、建立数据库后，创建星型模型，并依次为基础创建数据仓库

字数

2599 字

阅读时间

12 分钟

为了在MySQL、PostgreSQL 或 openGauss 平台上设计并创建一个星型模型，并以此为基础创建数据仓库，以下是详细的步骤。

步骤 1: 选择数据库平台

选择一个合适的数据库平台：MySQL、PostgreSQL 或 openGauss。以 MySQL 为例，其他数据库平台的具体命令略有差异。

步骤 2: 安装与配置数据库

确保数据库环境已安装和配置，以下是基本安装步骤：

• MySQL: 安装可以通过 apt-get 或 yum 在 Linux 上完成，Windows 上可使用 MySQL Installer。
• PostgreSQL: 可通过 apt-get install postgresql 或使用 PostgreSQL 安装程序进行安装。
• openGauss: 可以从 openGauss 官方网站下载并安装数据库。

启动数据库服务：

# For MySQL
service mysql start

# For PostgreSQL
sudo service postgresql start

# For openGauss
gs_ctl start -D /your/path/to/data

步骤 3: 创建数据库

1. MySQL 示例：

   CREATE DATABASE sales_dw;
   USE sales_dw;

2. PostgreSQL 示例：

   CREATE DATABASE sales_dw;
   \c sales_dw;  -- 连接到数据库

步骤 4: 创建星型模型的表

在星型模型中，事实表位于中心，维度表环绕在周围。我们以“销售数据”为例创建这些表。

1. 创建事实表 sales_fact：

事实表记录了销售的度量信息，如销售数量、销售金额等，并与多个维度表建立外键关系。

CREATE TABLE sales_fact (
    sales_id SERIAL PRIMARY KEY,
    time_key INT,
    item_key INT,
    branch_key INT,
    location_key INT,
    units_sold INT,
    dollars_sold DECIMAL(10, 2),
    avg_sales DECIMAL(10, 2)
);

2. 创建时间维度表 time_dim：

时间维度表包含每个销售日期的详细信息，例如年份、月份、季度等。

CREATE TABLE time_dim (
    time_key INT PRIMARY KEY,
    day INT,
    day_of_the_week VARCHAR(10),
    month INT,
    quarter INT,
    year INT
);

3. 创建产品维度表 item_dim：

产品维度表记录了销售商品的信息，例如商品名称、类型等。

CREATE TABLE item_dim (
    item_key INT PRIMARY KEY,
    item_name VARCHAR(255),
    brand VARCHAR(100),
    type VARCHAR(100),
    supplier_type VARCHAR(100)
);

4. 创建分支维度表 branch_dim：

分支维度表记录了销售分支机构的信息。

CREATE TABLE branch_dim (
    branch_key INT PRIMARY KEY,
    branch_name VARCHAR(255),
    branch_type VARCHAR(100)
);

5. 创建地理维度表 geo_dim：

地理维度表包含与销售相关的地理位置，如城市、国家等。

CREATE TABLE geo_dim (
    location_key INT PRIMARY KEY,
    street VARCHAR(255),
    city VARCHAR(100),
    state_or_province VARCHAR(100),
    country VARCHAR(100)
);

步骤 5: 设置外键关系

在事实表中，我们需要为每个维度表的键设置外键关系：

ALTER TABLE sales_fact ADD FOREIGN KEY (time_key) REFERENCES time_dim(time_key);
ALTER TABLE sales_fact ADD FOREIGN KEY (item_key) REFERENCES item_dim(item_key);
ALTER TABLE sales_fact ADD FOREIGN KEY (branch_key) REFERENCES branch_dim(branch_key);
ALTER TABLE sales_fact ADD FOREIGN KEY (location_key) REFERENCES geo_dim(location_key);

步骤 6: 插入示例数据

为了演示数据仓库的查询，我们插入一些示例数据。

插入时间维度数据：

INSERT INTO time_dim (time_key, day, day_of_the_week, month, quarter, year)
VALUES (1, 15, 'Monday', 1, 1, 2024),
       (2, 16, 'Tuesday', 2, 1, 2024),
       (3, 17, 'Wednesday', 3, 1, 2024);

插入商品维度数据：

INSERT INTO item_dim (item_key, item_name, brand, type, supplier_type)
VALUES (1, 'Laptop', 'BrandA', 'Electronics', 'Local'),
       (2, 'Tablet', 'BrandB', 'Electronics', 'Imported');

插入分支维度数据：

INSERT INTO branch_dim (branch_key, branch_name, branch_type)
VALUES (1, 'BranchA', 'Retail'),
       (2, 'BranchB', 'Wholesale');

插入地理维度数据：

INSERT INTO geo_dim (location_key, street, city, state_or_province, country)
VALUES (1, '123 Main St', 'New York', 'NY', 'USA'),
       (2, '456 Maple Rd', 'Toronto', 'ON', 'Canada');

插入事实表数据：

INSERT INTO sales_fact (time_key, item_key, branch_key, location_key, units_sold, dollars_sold, avg_sales)
VALUES (1, 1, 1, 1, 5, 500.00, 100.00),
       (2, 2, 2, 2, 3, 750.00, 250.00);

步骤 7: 执行 SQL 查询

1. 查询总销售额：

   SELECT SUM(dollars_sold) AS total_sales
   FROM sales_fact;

2. 查询2024年不同季度的销售额：

   SELECT t.quarter, SUM(s.dollars_sold) AS sales_per_quarter
   FROM sales_fact s
   JOIN time_dim t ON s.time_key = t.time_key
   WHERE t.year = 2024
   GROUP BY t.quarter;

3. 查询不同产品在不同年份的销售额：

   SELECT t.year, i.item_name, SUM(s.dollars_sold) AS sales_per_product_year
   FROM sales_fact s
   JOIN time_dim t ON s.time_key = t.time_key
   JOIN item_dim i ON s.item_key = i.item_key
   GROUP BY t.year, i.item_name;

步骤 8: 创建索引和优化

为了提高查询效率，可以为关键字段创建索引：

CREATE INDEX idx_time_key ON sales_fact(time_key);
CREATE INDEX idx_item_key ON sales_fact(item_key);
CREATE INDEX idx_branch_key ON sales_fact(branch_key);

步骤 9: 构建数据仓库

数据仓库的基本功能可以通过 ETL（Extract, Transform, Load）工具来实现，比如使用 Talend 或 Apache Nifi 进行数据抽取、清洗与加载。数据仓库的核心是对多维数据进行快速的查询和分析。

结论

通过以上步骤，我们在 MySQL、PostgreSQL 或 openGauss 平台上创建了星型模型并构建了一个简单的数据仓库。此架构适合用于 OLAP（在线分析处理），并支持高效的多维查询。

使用ETL工具Kettle（Pentaho Data Integration, PDI）来继续创建数据仓库可以帮助你自动化数据抽取、转换、加载（ETL）流程。以下是使用Kettle的具体步骤来完成以上数据库和数据仓库创建任务的详细流程：

步骤 1: 下载并安装Pentaho Data Integration（Kettle）

1. 访问Pentaho官方下载页面，下载Kettle的安装包。
2. 解压安装包，运行 Spoon.bat（Windows） 或 Spoon.sh（Linux/Mac） 启动Kettle的图形用户界面。

步骤 2: 连接到源数据库（例如 MySQL/PostgreSQL）

在 Kettle 中，我们首先要连接到源数据系统，比如MySQL、PostgreSQL或openGauss：

1. 打开Kettle的Spoon界面。
2. 在左侧的View窗口中，右键点击Database connections，选择New来创建新的数据库连接。
3. 输入数据库连接信息，包括：
   • Name: 自定义连接的名称（例如 Source_MySQL）。
   • Connection Type: 选择适合的数据库类型，例如MySQL或PostgreSQL。
   • Host Name: 数据库服务器的IP地址或主机名（例如 localhost）。
   • Database Name: 数据库名称（例如 sales_dw）。
   • Port Number: 数据库端口号（MySQL默认是3306，PostgreSQL默认是5432）。
   • Username和Password: 数据库的用户名和密码。
4. 测试连接，确保成功连接到源数据库。

步骤 3: 创建目标数据库连接（数据仓库）

同样的方式，创建一个新的数据库连接来连接到目标数据库（数据仓库）。假设目标数据库仍然是MySQL或PostgreSQL。

1. 在Database connections中，再次右键选择New，并输入新的目标数据库连接信息。
   • Name: 例如Target_MySQL_DW。
   • 填写数据库连接信息（类似源数据库）。

步骤 4: 创建ETL转换（Transformation）

1. 在Kettle界面顶部，点击File -> New -> Transformation来创建一个新的转换。
2. 在主工作区的左侧Design选项卡中，可以看到Input、Output、Transform等组件。

4.1: 数据抽取（Extract）

我们需要从源数据库中提取数据：

1. 从Input组件中，拖动一个Table Input组件到工作区。
2. 双击Table Input，在弹出的窗口中选择之前创建的源数据库连接Source_MySQL。
3. 在SQL框中编写查询来选择源表的数据。例如，选择时间维度表数据：

   SELECT * FROM time_dim;

4. 点击Preview来查看是否成功提取数据。

4.2: 数据转换（Transform）

我们可以根据需要在这里进行数据清洗和转换。

1. 从Transform组件中，拖动一个Select Values组件。
2. 双击Select Values，可以选择要保留或转换的字段。例如，可以选择只传递time_key、day、month等字段。

4.3: 数据加载（Load）

将转换后的数据加载到目标数据仓库。

1. 从Output组件中，拖动一个Table Output组件到工作区。
2. 双击Table Output，选择目标数据库连接Target_MySQL_DW。
3. 在Target Table中选择目标表（如time_dim）。如果目标表还不存在，你可以点击SQL按钮生成自动创建表的SQL语句，或者手动创建表。
4. 将转换的数据与目标表连接。

4.4: 连接组件

通过右键点击Table Input，选择Hopl来连接Table Input和Select Values，并连接Select Values和Table Output。这就完成了数据从源系统到目标仓库的ETL流程。

步骤 5: 创建Job并调度ETL任务

1. 保存你的转换文件（例如 extract_time_dim.ktr）。
2. 在主界面，点击File -> New -> Job来创建一个Job。
3. 从左侧工具栏中拖动Start组件到工作区，作为Job的启动点。
4. 从General选项卡中拖动Transformation组件到工作区，并双击它来关联到你刚创建的ETL转换（例如extract_time_dim.ktr）。
5. 连接Start和Transformation，形成一个完整的Job。
6. 保存Job，并通过调度器（Scheduler）来定时执行该Job。

步骤 6: 重复创建其他维度和事实表的ETL流程

为其他维度表（item_dim、branch_dim、geo_dim）和事实表（sales_fact）重复以上ETL流程，创建单独的ETL转换文件。

步骤 7: 执行Job并查看结果

1. 在Spoon界面，点击运行按钮来执行你的ETL流程。
2. 执行后，你可以进入目标数据库，查询是否成功加载了数据：

   SELECT * FROM time_dim;

步骤 8: 优化与调度

1. 优化：根据数据量大小和查询复杂性，优化转换和加载过程。例如，可以使用批量加载、索引等技术。
2. 调度：你可以通过Kettle自带的Spoon调度器或系统的cron任务来定期执行ETL任务。

结论

通过Kettle（Pentaho Data Integration），我们可以轻松实现数据从源系统到数据仓库的ETL流程。这个过程涉及数据的抽取、转换和加载，并且可以通过图形界面直观地管理ETL流程。最终，你可以基于这些数据进行多维分析和商业智能查询，构建数据驱动的决策支持系统。

贡献者

freeway348

文件历史

最后编辑于 大约 1 个月前查看完整历史

01996-OS