​
+++ title = “时序表”

hidden=true

weight = 20 pre = “20. ”

+++

在我的团队中，我们每天处理数TB的数据，约有数十亿条记录存储到我们的Redshift数据仓库中。面对如此庞大的数据量，尝试优化查询性能和维护数据成为一项艰巨的任务。

时间序列

创建时间序列表与其他形式的架构设计没有太大区别。每个表应该保存一部分数据，并通过逻辑关系连接。对于时间序列表，您可以基于某个日期/时间逻辑地分离数据。如果您经常每小时/天/周插入数据（这在大数据领域很常见），每个小时/天/周可以分离到自己的表中。这就是创建时间序列表的基本思想。

使用场景

在我的团队中，我们的客户最关注整天的数据。理想情况下，我们会为每天创建单独的表。然而，由于我们的28天数据保留策略，将一个表拆分为28个时间序列表变成了一场维护噩梦，特别是当我们正在为4-5个表迁移到时间序列表时。

对我们来说，折中的方案是创建能够保存一周数据的时间序列表。简单地联合所有每周表就能为客户提供他们承诺的完整28天数据。

优势

我们看到了转向时间序列表的两大好处。第一个好处是更容易删除数据。Redshift只标记要删除的记录，所以您需要调用"vacuum/vacuum delete only"查询来释放磁盘空间。然而，处理数十亿/万亿条记录时，没有什么是简单的。对我们来说，仅仅释放已删除记录的磁盘空间就需要超过24小时。不仅如此，vacuum查询对集群性能有明显影响。通过使用时间序列表，我们只需在为当前周创建新表时删除最旧的表。24小时的数据删除变成了5秒的查询。

第二个好处是管理Redshift的数据维护。简而言之，Redshift通过排序数据作为其"索引"方式，而插入到Redshift的所有数据通常是未排序的。需要明确调用"Vacuum"表来排序数据，这大大提高了查询性能。如果不排序数据，Redshift必须对整个数据集进行顺序扫描，这非常昂贵。

当我们尝试对保存30天数据的单个表进行vacuum时，估计需要超过5天才能完成数据排序。当我们让它运行时，我们很快就耗尽了内存。当我们尝试"深度复制"作为替代方案（相当于排序）时，即使我们只使用了25%的磁盘容量，我们也耗尽了磁盘空间。我们根本没有足够的资源一次性完全vacuum一个月的数据。

通过拥有更小的表，vacuum和深度复制现在成为可能，而且更易于管理。通过这种排序数据的努力，需要完全顺序扫描的10分钟查询变成了只搜索一小部分数据的30秒查询。

首先，时间序列表不是纯粹的优化技术。它必须适合您的使用场景，并且有自己的挑战。将其视为在考虑架构设计时工具箱中的另一个工具。

对我们来说，处理的大量数据以及由此产生的扩展/维护问题导致我们不得不探索更具体的设计技术，而不仅仅是通常对大多数团队来说足够的典型星型架构设计。

时间序列数据，即按时间顺序排列的数据点序列，在分析中经常出现。特别是当我们开始处理用户生成的事件时。用户与我们产品的交互是一系列时间很重要的事件。如果我们考虑收件人对我们MailChimp活动的互动 ，情况也是如此。

处理按时间排序的数据有一些独特的挑战，在设计数据仓库解决方案时应该考虑这些挑战。

在这篇文章中，我们将看到如何使用Redshift作为数据仓库和来自用户与我们产品交互触发的事件的数据，有效地处理时间序列数据。

我们将看到优化数据模型的选择，以及Redshift在其武器库中有哪些工具来优化数据并实现更快的查询时间。

时间序列数据因多种原因而独特。首先，更新行很少发生。当我们处理时间序列数据时，我们预计在数据仓库上有一个不断增长的表。

从数据仓库维护的角度来看，这很重要。首先，我们需要以某种方式保证无论我们想要分析数据的时间点如何，都能有可预测的查询时间。 其次，较旧的数据在未来可能不那么相关，或者我们在任何情况下都不应该使用它，因为它可能会扭曲我们的分析结果。因此，应该容易丢弃较旧的数据，这也与运行我们基础设施的成本有关。

时间序列数据的第二个特点是其结构通常相当简单。例如，如果我们考虑用户事件，我们需要的最少信息如下：

  • 发生的事件
  • 事件发生的时间
  • 与事件相关的用户

上面的简单三元组，作为时间序列，包含足够的信息帮助我们理解用户的行为。

当然，可以添加更多数据，比如我们想要用来丰富时间序列的自定义属性，以进行更深入的分析。但在大多数情况下，时间序列中点的整体结构将是扁平的。

Mixpanel的时间序列示例

让我们看一下Mixpanel事件的结构，这是一个跟踪用户事件的典型服务案例。

{  
  "event": "Viewed report"，
  "properties": {
    "distinct_id": "foo"，
    "time": 1329263748，
    "origin": "invite"，
    "origin_referrer": "http://mixpanel.com/projects/"，
    "$initial_referring_domain": "mixpanel.com"，
    "$referrer": "https://mixpanel.com/report/3/stream/"，
    "$initial_referrer": "http://mixpanel.com/"，
    "$referring_domain": "mixpanel.com"，
    "$os": "Linux"，
    "origin_domain": "mixpanel.com"，
    "tab": "stream"，
    "$browser": "Chrome"，
    "Project ID": "3"，
    "mp_country_code": "US"
  }
}

从上面的例子中，我们看到数据围绕事件结构化，同时还有一组与之相关的属性，当然包括事件生成的时间。

其余的属性是元数据，可以帮助我们更好地理解事件和触发它的用户。

Amazon Redshift很棒；它允许您快速启动一个可扩展到PB级数据的数据仓库。像任何其他技术一样，要充分利用它，我们应该根据我们将使用的技术特性和我们计划对数据执行的查询来相应地建模我们的数据库。

对于Amazon Redshift，我们需要记住以下几点。

删除大量数据在Amazon Redshift中不是一项简单的任务。因此，由于我们计划处理不断增长的时间依赖数据集，最好有一个明确的数据删除策略。

在Amazon Redshift中删除大量数据不是一项简单的任务

Amazon Redshift是一个分布式数据仓库，因此数据分布在不同的节点上。可以更改数据分布方式。如果可能，您应该始终争取最大的数据局部性。

使用Amazon Redshift，如果可能，您应该始终争取最大的数据局部性

最后但同样重要的是，排序键的定义可能会影响查询性能。查询优化器也使用排序键来制定最佳查询计划。

注意Amazon Redshift实例中排序键的定义。

考虑到Amazon Redshift的上述三个特性，我们可以为具有类似上面Mixpanel数据结构的时间序列数据设计最佳表模型。

我们预计我们的分析将围绕生成的事件，因此，来自同一事件的数据应尽可能存储在同一节点中。出于这个原因，一个好的选择是选择**“键分布"样式**，其中数据将基于**预定义的键**（在本例中为**事件列**）聚集在一起。

这样，可以保证来自同一事件类型的数据将被分配在一起。

关于排序键

根据定义，由于我们处理的是时间依赖的数据，应该在时间戳列上创建排序键。对于Mixpanel数据，自然的选择是将properties_time列创建为主要排序键。这个键将保证我们的数据始终按事件生成的时间排序，从而更好地符合时间序列数据的性质。

为了进一步优化表，由于我们预计会大量使用事件，为events字段创建另一个排序键是个好主意。但确保这个排序键在time字段之后。处理时间序列数据时，时间应该始终是第一个排序键。

关于排序键和分布策略的上述内容是处理用户事件数据时的最低要求。根据我们计划执行的分析类型，我们还可以使用描述事件的其余属性添加更多排序键和分布键。

例如，在归因分析中，我们可能希望将initial_referring_domain列添加为分布和排序键。只需记住，虽然将来可以更改分布策略和排序键，但这是一个繁琐的过程，因此最好在开始向Amazon Redshift提供时间序列数据之前明确定义需求。

为了给出一个具体的例子，如果我们考虑行event和properties_time，一个创建表的查询，该表将基于时间和事件排序，同时基于事件类型分布数据，将如下所示：

CREATE TABLE events (  
  Properties_time timestamp sortkey
  event varchar(200) sortkey distkey
) diststyle key;

关于删除旧数据

如果您不必处理大量行（数亿行），您可能不必太担心如何删除较旧的数据。但由于我们正在处理时间序列数据，在某些时候我们可能需要实施一种策略，允许我们有效地删除较旧的数据。

对此推荐的策略是使用时间序列表。

例如，为每个月创建一个新表，并在其中存储相应的数据。如果您的数据保留期小于一个月，您可以创建每周表。通过这样做，您可以通过执行DROP TABLE命令删除数据，这比任何DELETE命令都快得多，并且在删除大量数据时可以很好地扩展。

轮到您了

上述简单原则应该指导您在为时间序列数据设计Amazon Redshift表时的选择。遵循这些原则将帮助您充分利用Amazon Redshift集群，并在需要考虑集群升级之前给您足够的里程。

本文将介绍如何在Redshift 中处理时间序列/日期时间数据。

时间序列数据包含日期和/或时间信息，与字符串或数字数据不同，因为它有我们可能想要访问/比较/修改的组件，如日、月、年、小时等。

例如，我们可能想要查看五月份的销售情况，或者将两个日期之间的差异计算为月数。

Redshift 是作为AWS （Amazon Web Services）一部分提供的数据库，基于Postgres 数据库，但有一些差异 。像大多数其他关系数据库，如MySQL 、SQL Server 、Oracle 、DB2 等，Redshift可以处理和操作时间序列数据。

让我们看看如何操作。

日期时间类型

首先，让我们看看Redshift支持哪些时间序列的数据类型，以及它们可以保存什么样的数据。

  • **DATE：**不包含时间部分的日期
  • **TIME：**仅时间部分
  • **TIMEZ：**带有时区信息的时间（由于纽约的时间比东京晚，例如，时区反映了这种差异）
  • **TIMESTAMP：**包括日期和时间
  • **TIMESTAMPZ：**带有时区信息的时间戳

更多信息，请查看AWS文档 。

获取日期时间结果

现在我们知道了可以获取的不同类型的时间序列查询结果。但我们如何获取这些类型的查询结果？它总是必须来自存储在列中的数据吗？

不，我们可以通过两种方式获取日期时间结果：

  • 在查询中选择日期时间列
  • 使用函数 从其他输入创建日期时间

让我们看一个示例查询。

当前日期时间示例

select current_date today， current_timestamp now， current_time time_now， to_date( '28-10-2010'， 'DD-MM-YYYY')

today_from_str， current_date + 1 tomo

current datetime example

在上面的示例中，我们使用了Redshift的内置函数来获取今天的日期和时间。

我们使用**TO_DATE**从字符串创建日期，并使用日期算术从今天获取明天的日期。注意"now"列同时包含日期和时间。

**SYSDATE**和**GETDATE**也可以用于当前时间戳，但要注意**SYSDATE**返回当前事务的时间戳。

处理时区

我们上面看到了可以保存时区信息的**TIMESTAMPZ**类型，但这是什么，为什么需要它？

由于地球的旋转，任何给定日期的X上午/下午在世界不同地点可能不同；我们有时区来捕获这些信息。

例如，当日本是日出时，英国仍然在黑暗中，等待地球的旋转使其面向太阳。因此，日本和英国处于不同的时区。

**TIMESTAMPZ**数据类型与时间戳一起捕获时区。如果在**TIMESTAMPZ**类型中未指定时区，则假定为UTC 。

那么首先，我们如何获取**TIMESTAMPZ**值？

我们可以用包含时区的字符串文字（例如，‘2021-10-31 13:00:00 IST’）填充**TIMESTAMPZ**列，或者使用TIMEZONE函数从时间戳创建**TIMESTAMPZ**值。在下面的示例中，我们指定了EST作为时区。

select TIMEZONE( 'EST'， TIMESTAMP '2021-10-31 10:00:00')

timezone

结果显示为UTC。UTC比EST早5小时，所以UTC时间是10+5=15。

假设我在美国的老板希望我在2021年10月31日星期五晚上7点参加在线会议。在印度会是什么时间？

**CONVERT_TIMEZONE**函数将一个时区转换为另一个时区，这里我们想从源EST转换为目标IST。不幸的是，Redshift似乎将IST与以色列时间混淆了，所以我还必须使用Asia字符串。

select CONVERT_TIMEZONE( 'EST'， 'Asia/Kolkata'， TIMESTAMP '2021-10-31 07:00:00 PM')

convert_timezone

哦不！我必须在周六早上5:30起床？:(

创建示例数据和示例

接下来，让我们创建一些数据来运行更多查询。

在这个场景中，我们假设我们有一个业务，我们正在查看我们的产品在特定时间范围内的销售情况。

对于下面的示例，假设我们有一个名为"sales"的表，其列标题如下：product_name、sale_date（时间戳）和amount（十进制）。然后，我们可以创建如下所示的示例数据：

create table sales (id integer， product_name varchar(100)， purchase_date timestamp， sale_date timestamp， 
amount integer);

insert into sales values (1， 'Kellogs'， null， to_timestamp('2021-10-29 07:00:00'， 'YYYY-MM-DD HH:MI:SS') 
- interval '12 weeks'， 20);
insert into sales values (2， 'Kellogs'， null， to_timestamp('2021-10-29 08:00:00'， 'YYYY-MM-DD HH:MI:SS') 
+ interval '2 hours'， 10);
insert into sales values (3， 'Kellogs'， null， to_timestamp('2021-10-29 08:00:00'， 'YYYY-MM-DD HH:MI:SS')， 10);
insert into sales values (4， 'Milk'， null， to_timestamp('2021-10-29 08:00:00'， 'YYYY-MM-DD HH:MI:SS')， 5);

update sales set purchase_date = dateadd( months， -id， sale_date);

我们使用了**TO_TIMESTAMP**从字符串解析日期。使用24小时时间格式时，应使用HH24表示小时。

注意使用**INTERVAL**关键字使用特定时间间隔执行日期算术。

Redshift在某些上下文中可能不支持间隔中的月、年。此外，我们通过使用**DATEADD**函数从sale_date中减去ID数月来填充purchase_date列。

也可以通过将**ROW_NUMBER**函数的结果添加到某个初始日期来生成此类日期。

上午销售示例

使用我们上面创建的示例数据，让我们了解有关产品的更多信息。

例如，要找出哪些产品在上午8点至10点之间销售，我们可以编写以下内容：

select product_name， sum(amount)

from sales

where date_part( 'hour'， sale_date) between 8 and 10

group by product_name

order by 2 desc;

这里我们使用**DATE_PART**函数提取小时部分。请注意，在索引列上使用函数意味着无法使用索引。相反，必须在表达式上构建单独的函数索引。

因此，在上面的示例中，即使sale_date列有索引，也不会使用它，因为我们没有直接使用sale_date。

当前季度销售示例

要查询当前季度的销售情况，请运行以下内容：

select *

from sales

where date_part( 'quarter'， sale_date) = date_part( 'quarter'， current_date)

and date_part( 'year'， sale_date) = date_part( 'year'， current_date)

在这里，我们比较销售日期的季度和年份与当前日期的季度和年份。

月初示例

要获取月初的销售情况，我们可以使用**DATE_TRUNC**函数。

它将截断比指定时间部分不太重要的日期/时间部分。在这里，年份最重要，微秒最不重要。

在下面的情况下，如果我们截断到月份，那么比月份不太重要的日、小时和秒部分都将被清除/截断。同样，我们可以获取年初或周初。

有一个单独的**LAST_DAY**函数，它返回给定日期的月末。

select date_trunc('month'， sale_date ) first_day， last_day(sale_date) from sales;