数据仓库ETL速度慢 - 在维度中更改主键？

Question

数据仓库ETL速度慢 - 在维度中更改主键？

pythonmysqlprimary-keydata-warehousetalend

4

我有一个工作中的MySQL数据仓库，以星型模式组织，并使用Talend Open Studio for Data Integration 5.1创建ETL过程。我希望该过程每天运行一次。我估计一个维度表（dimUser）将有大约2百万条记录和23个列。

我在Talend中创建了一个小型测试ETL过程，但考虑到可能需要每天更新的数据量，当前性能无法满足要求。将ETL过程用于更新或插入1000条记录到dimUser需要4分钟。如果我假设记录数量和更新或插入所需时间之间存在线性关系，则ETL不可能在3-4小时内（我的期望），更别说一天。

由于我不熟悉Java，因此我将ETL编写为Python脚本，并遇到了同样的问题。尽管如此，我发现如果只插入，该过程会快得多。我相当确定瓶颈是由UPDATE语句引起的。

dimUser中的主键是自动递增整数。我的朋友建议我放弃这个主键并将其替换为多字段主键（在我的情况下是2-3个字段）。

在从我的仓库中删除测试数据并更改模式之前，是否可以提供与以下内容相关的建议或指导：

1. 数据仓库设计 2. ETL过程 3. 每天插入或更新几百万条记录的可行性 4. 我朋友的建议是否会显著有所帮助

如果您需要任何进一步的信息，请告诉我，我将发布它。

更新-附加信息：

mysql> describe dimUser;
Field                        Type                Null Key Default            Extra
user_key                     int(10) unsigned    NO   PRI NULL               auto_increment
id_A                         int(10) unsigned    NO       NULL
id_B                         int(10) unsigned    NO       NULL
field_4                      tinyint(4) unsigned NO       0
field_5                      varchar(50)         YES      NULL
city                         varchar(50)         YES      NULL
state                        varchar(2)          YES      NULL
country                      varchar(50)         YES      NULL
zip_code                     varchar(10)         NO       99999
field_10                     tinyint(1)          NO       0
field_11                     tinyint(1)          NO       0
field_12                     tinyint(1)          NO       0
field_13                     tinyint(1)          NO       1
field_14                     tinyint(1)          NO       0
field_15                     tinyint(1)          NO       0
field_16                     tinyint(1)          NO       0
field_17                     tinyint(1)          NO       1
field_18                     tinyint(1)          NO       0
field_19                     tinyint(1)          NO       0
field_20                     tinyint(1)          NO       0
create_date                  datetime            NO       2012-01-01 00:00:00
last_update                  datetime            NO       2012-01-01 00:00:00
run_id                       int(10) unsigned    NO       999

我使用替代键是因为我读到它是一种良好的实践。从业务角度来看，我希望保持对潜在欺诈活动的警惕（例如一个用户与X州关联了200天，然后第二天与Y州关联 - 他们可能已经搬家或者账户可能已被盗用），因此需要保存地理数据。字段id_B可能有几个与其相关的不同值id_A，但我只对不同的(id_A, id_B)元组感兴趣。在这些信息的背景下，我的朋友建议将(id_A, id_B, zip_code)作为主键。

对于大多数日常ETL过程（>80％），我只期望更新现有记录的以下字段：field_10-field_14，last_update和run_id（此字段是对我的etlLog表的外键，用于ETL审计目的）。

- Jubbles

如果您将主键更改为在更新期间可能更改的内容，则每次更新时都需要重新计算索引，这会使更新速度变慢。仅更新100行中等大小的表格需要4分钟似乎非常奇怪，也许您的脚本存在问题而不是模式。您是否尝试过直接在SQL中对更新进行基准测试，而不是从脚本中进行？ - limscoder

1

你是如何实现每天数百万的收入的？ - Damir Sudarevic

@Damir：这个两百万的值来自于历史观测数据。什么是关键管道？ - Jubbles

1

一定要读Kimball的书。它非常基础易懂。 - Damir Sudarevic

专注于“加载类型-2维度”。 - Damir Sudarevic

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- N West · Accepted Answer

以下是我对你的问题的看法。

1）仓库设计：

阅读 Ralph Kimball 的书：《数据仓库工具包》。

你的维度表有很多带有无意义名称的列。与其使用 field_5 这样的字段，不如给该列赋予一个具有业务含义的名称。数据仓库旨在为业务查询和报告人员提供便利。

我没有看到任何事实表。了解用户维度将用于什么非常重要。

2）ETL 过程

你是否确定 ETL 过程中的瓶颈在哪里？是在从源读取数据、转换数据还是写入数据库时？如果你每秒只能从 XML 数据源读取 1,000 行/秒，即使你以 40,000 行/秒的速度写入，也不会有太大进展。

你是否考虑先将更改的记录加载到数据库中的一个暂存表中，而不进行任何转换，然后使用 SQL 进行转换和更新数据？通常情况下，你会发现在数据库中执行操作的性能比将工作卸载到 ETL 工具中要好。

3) 如果硬件能够处理，每天更新几百万条记录是非常现实的。我认为重要的是要了解您是否只需要一个 Type 1 维度，您只需覆盖更改（在这种情况下，删除更改行，然后插入可能比更新/else/insert 更好）。

如果您正在保留类型 2 维度的更改历史记录，则可能需要考虑在单独的迷你维度中对要跟踪更改的字段进行雪花处理。当您拥有非常大的“客户”维度时，Kimball 讨论了这种技术。然后，您将使用周期性快照事实表，这将允许您随时间跟踪用户的更改。

4) 您的朋友建议使用自然业务键创建主键不适用于数据仓库环境。我们创建整数代理键，以便可以将其包含在事实表中，使它们保持精简，因为它们将比维度表大几个数量级。