处理度量数据/分析的动态维度

Question

我们正在实现一个允许用户存储度量的特性，其中包含任意维度。这些尺寸，虽然是任意的，但在整个系统中使用。

因此，我们制作了两个表来存储维度：

class Dimension():
        """ Stores the `type` of the dimension """
        __tablename__ = 'dimensions'
        id: str
        title: str
        description: str

class DimensionValue():
        """ Stores the dimension itself, each dimension has a type """
        __tablename__ = 'dimension_values'
        id: str
        title: str
        description: str
        type_id: str = ForeignKey('dimension.id')

然后我们有这样的东西：

regions = Dimension(id=1, title="Region", description="Regional Activities")
department = Dimension(id=2, title="Departments", description="Activities by department")

us = DimensionValue(title="US", description="US Activities", type_id=1)
europe = DimensionValue(title="Europe", description="Europe Activities", type_id=1)

marketing = DimensionValue(title="Marketing", description="Activities related to the marketing department", type_id=2)
technology = DimensionValue(title="Technology", description="Activities related to the tech department", type_id=2)

我们还没有经常看到这种情况，我们通常会看到预先定义的维度，但是额外的灵活性对我们来说很重要。现在，我们有了这样的度量标准：

class Metric():
        """ Stores the metric metadata """
        __tablename__ = 'metrics'
        id: str
        title: str
        description: str
        unit: str
        periodicity: str

class MetricValues():
        """ Stores the dimension itself, each dimension has a type """
        __tablename__ = 'metrics_values'
        id: str
        timestamp: str
        value: float

class MetricValuesDimensions():
        """ Stores the dimension itself, each dimension has a type """
        __tablename__ = 'metrics_values_dimensions'
        metric_id: str = ForeignKey('metrics.id')
        dimension_id: str = ForeignKey('dimension_values.id')

不过，我们对这种做法并不确定。一个度量值可以有N个维度，惟一性只能通过一个基于联接的复杂键(时间戳、metric_id和维度)来保证。

除此之外，我们的关系发生在“价值”层面，这种关系似乎是不存在的，尽管我们并不期望有大量的数据(我们通常会处理每日/每周/每月的数据)，但似乎有很多重复的信息--想象一下150个数据点(每周数据的大约3年)，维度/价值关系将被复制150倍。

因此，我们想在度量中添加另一个层，称为MetricsSeries。一个metric会有N个Series和一个Series N Values，现在我们可以将Dimension关系转移到Series中，这样就可以减少复制(我们不必每次对每个值重复这种关系)。

现在，这将使“ETL”成为一种痛苦--我想，因为我必须用未知的维度来平平度量表，所以它将变成一些复杂的递归样式的工作流。开始看起来太复杂了。

我们可以想到的另一种方法(我们并不真正喜欢)是拥有一组预定义的维度“槽”，比如5维，然后有用户定义的维度标题。因此，按照这个例子，如下所示：

class Dimension():
        """ Stores the mapping between the generic # dimension and the user defined title """
        __tablename__ = 'dimension'
        id: str
        title: str
        description: str

class DimensionValue():
        """ Stores the values of the dimensios """
        __tablename__ = 'dimension_values'
        id: str
        values: str

class Metric():
        """ Stores the metric metadata """
        __tablename__ = 'metrics'
        id: str
        title: str
        description: str
        unit: str
        periodicity: str

class MetricSeries():
        """ Stores the metric metadata """
        __tablename__ = 'metrics_series'
        id: str
        dimension_1: str = ForeignKey('dimension_values')
        dimension_2: str = ForeignKey('dimension_values')
        dimension_3: str = ForeignKey('dimension_values')
        dimension_4: str = ForeignKey('dimension_values')
        dimension_5: str = ForeignKey('dimension_values')

class MetricSeriesValues():
        """ Stores the dimension itself, each dimension has a type """
        __tablename__ = 'metrics_series_values'
        id: str
        timestamp: str
        value: str = ForeignKey('metrics_series')

这将使我们的生活变得更容易，因为多维数据集已经准备好了，我们可以直接查询并过滤到系列表中。我们在本例中看到的问题是，MetricSeries表中会有大量的NULL。它将确保系列级别上的唯一性(在维度上是平坦的)，并使OLAP非常直接。

缺点显然是系统在维度#方面变得多么僵化。一个不可能有数百个维度，所以有一些限制是可以的，我们可以很容易地对20个维度做同样的事情。我猜会有很多空行/列，但同样，这会使分析性查询更容易。

最后，我们想知道是否应该利用JSONB/数组列类型。我想人们不太喜欢这种方法，但是我们已经在应用程序级别上确保了模式和数据类型，所以这种方法至少会有一些缺点。不过，我不知道什么是表演。

其他一些相关信息:我们假设维度(和维度值)的数量相当低，每个租户将有10个，也许15个维度。每个维度也有大约10-15个值。由于我们正在处理的是每周/每月/每年的数据，因此数据量也应该很低。我们重视查询的灵活性(考虑到最终用户的枢轴表)和性能。插入性能不太重要，因为它们可以异步完成，不会有太多问题。我们不介意一开始就有一些限制，但我们希望避免重大的设计错误。这是一个多租户应用程序，因此它必须考虑到无论发生什么情况，租户总是会过滤它。任何其他的细节，我可能会错过，我很乐意进一步编辑这一点。

希望能得到一些反馈，我们在分析平台等方面几乎没有任何经验。

非常感谢

Answer 1

，我想这会让“ETL”有点痛苦

会的，我保证，还有更多。在“递归样式工作流”方面，您已经确定了原因。我会用数据库术语来描述它:它通过隐藏数据之间的关系来破坏SQL应用关系代数的能力。

尺寸，虽然是任意的，但在整个系统中使用。

我相信你是认真的，但我怀疑。一些代码--查询、应用程序、报表--必须引用这些维度。每一个都有一定的意义和范围。没有维度是自动使用的，除非它的某些方面是暗示的。证明:如果我添加维度"arbitration_time"，在定义术语之前，数据将如何使用？

您预期使用多维查询的事实表明，它们在关系模型中有意义。当你说“额外的灵活性很重要”的时候，你真正要说的是你还没有把你的手臂放在问题领域。您不知道维度是什么(或者您关心的维度)，所以您希望将它们作为数据随意添加，并在稍后处理余波。

设计数据库的正确方法是通过数据分析的迭代。每个属性都被仔细检查，以确定其正确的位置和属性。这是一项高技能的努力，取决于复杂程度可能需要几个月。这些天来，管理层和技术团队通常都不愿意这样做，而DBA则被降职于捕捉任何被扔在墙上的东西。

不理解问题的解决方案通常是某种实体-属性值(EAV)的“设计”。它是灵活的，但您最好不要使用DBMS，因为查询是一场噩梦，DBMS不能强制执行约束。

数据库管理系统中的EAV润滑脂是JSON。JSON只是以一种“灵活”的方式将属性嵌入数据库的另一种方式，这种方式与SQL执行数据一致性的能力背道而驰。您的DBMS可以在JSON属性上定义与列相等的精确约束吗？如果是的话，为什么不直接使用一个列呢？如果没有，为什么不直接使用列呢？

该怎么办呢？我的建议是:保持严格的灵活性。向正确性迭代。

如果您不能使用先验知识提前设计数据库，则使用累积知识“实时”设计数据库。给你认为应该命名的事物命名，并把它们放在你认为应该去的地方，使用3NF作为基线。如果发现某个东西位于不应该的键中，请重新定义该键。如果某件事似乎是独一无二的，那就让它如此；如果结果并非如此，放松约束。

慷慨地创建表和列。将重复的表合并成一个表要比消除共享列(重载其含义)的混合数据更容易，因为不允许某人创建适当的列。一些DBMS允许用户在自己隔离的命名空间中创建数据库对象。它可以用作试验用的沙箱，然后将其并入主共享表中。

让某人负责名字，并就一些约定达成一致。如果一个表具有acct和另一个Account_Number，则不为任何人提供服务。避免在数据库名称中使用缩写，因为它们缺乏上下文。与数据库碰巧选择的缩写相比，整个英文名称不那么含糊，也更容易记住。(从你的例子来看，这似乎已经得到了控制。)

没有无限的维度。现在看来是这样的。也许有100，但1000？把东西放在它们似乎属于的地方，并随着知识的提高而调整。这样，就可以迭代到每个人都能达成一致的正确模型。

我所描述的过程涉及到一些搅动。不断变化的架构不可避免地会中断查询和加载进程。这就是没有预先设计过程的代价。

但另一种选择则更糟糕。我看到了一个完全灵活的EAV模型的结果，该模型由一个既不了解关系模型，也不了解他们正在处理的问题域的组实现。其结果是一堆复杂的、不可维护的代码(数据库和应用程序都是如此)和性能噩梦。从好的方面来说，这是对任何加入球队的人来说都是一份充分就业合同。