内容


TimeSeries DataBlade 简介

解决日期戳数据

Comments

走向本垒板 —— 准备

Informix TimeSeries DataBlade 设计用于帮助 Informix 用户更好地处理与时间相关的数据。通过使用内置在 Informix Dynamic Server 中的对象-关系技术,TimeSeries DataBlade 可以缩小数据的大小,加快数据的处理速度,并提供了一组新的工具来分析数据。正如 User's Guide 所说的,TimeSeries DataBlade 带来的性能提升相当可观。通过它可以节省 55% 的空间,处理速度最多可以加快 30 倍。对于一个特定的数据问题,它的确是一项有用的技术。

TimeSeries DataBlade 可以处理很多类型的基于日期-时间的数据。例如:股票/金融数据(这在 User's Guide 中是基本的例子)、审计追踪或日志、监视的统计信息、气象、地震监测和帐单信息。TimeSeries DataBlade 可以和 Real-Time Loader 一道来处理大量的流式的实时数据。由于 TimeSeries 数据和模式的迁移比较困难,TimeSeries 最好和已知的不大变化的数据模式一起使用。

对于本文中的例子,我将使用自 1882 年以来每年对所有棒球运动员的技术统计作为数据。我使用的是由 Baseball Archive(http://www.baseball1.com/)提供的棒球统计。下面展示了 TimeSeries 对数据模式的处理。注意,一个表可能有多个 TimeSeries 列。

清单 1. 一个标准的有索引的日期-时间表
CREATE TABLE playerstats (
    playerID char(9),
    year int, --(or date or datetime)
    atbats int,
    hits int,
    runs int,
    walks int,
    etc...
);
CREATE UNIQUE INDEX ix_playerstats on playerstats (playerID, year);
清单 2. 带 TimeSeries 列的 Playerstats 表
CREATE TABLE playerstats (
    playerID char(9),
    hitting TimeSeries(batstat_t),
    pitching TimeSeries (pitchstat_t)
);
CREATE UNIQUE INDEX ix_playerstats on playerstats (playerID);

球队 —— 基本 TimeSeries 结构

有四种基本的结构可用于构建 TimeSeries。这四种结构是:Calendar 和 Calendar Pattern、Subtype (或 ROW 类型)、Container 和 TimeSeries 本身。TimeSeries 可以是定时的(regular)或者非定时的(irregular)。定时 TimeSeries 包含已知的、定义好的时间间隔内的数据。对于每个时间间隔,有一个对应的条目。而非定时 TimeSeries 用于不在已知时间间隔内重新出现的数据。在下面关于棒球的例子中,我将使用定时 TimeSeries。对于每一年,在 10 月 1 日,我记录了每个运动员全年的统计。但是,如果您的数据是捕捉股票交易事务,那么它很可能就是非定时 TimeSeries。在创建 TimeSeries 之前,必须确定它的类型。

TimeSeries 数据更小一些,并且访问起来也更快一些,因为它并不是存储真正的日期和时间数据。所有数据都是相对于一个已知的开始日期的偏移量,这个开始日期称作起点(origin)。每个 TimeSeries 列可以有一个不同的起点。DataBlade 可以快速地在 DateTimes 和偏移值之间进行转换。因此,整个数据列和相关的索引不是存储在数据库中。这正是使 DataBlade 得以提高性能的基本思想。

偏移量由 Calendar 和 Calendar Pattern 控制。Calendar Pattern 定义数据的有效间隔。而 Calendar 则将 Pattern 与尽可能早的起始日期组合起来。您可以根据数据的需要使用足够多的 Calendar 和 Calendar Pattern。它们只是系统表中由 TimeSeries DataBlade 创建的行。下面是一些例子:

清单 3. calendar 和 calendar pattern
INSERT INTO CalendarPatterns VALUES (‘weekly’, 
 ‘{1 on, 6 off}, day’)
INSERT INTO CalendarPatterns VALUES (‘work_hours’,
 ‘{24 off, 120 on, 24 off}, hour’)
INSERT INTO CalendarTable VALUES (‘week’, ‘startdate(
 1998-01-01 00:00:00.00000), pattstart(
 1998-01-02 00:00:00.00000), pattname (weekly)’)

这里有一些细节需要注意。首先,在 TimeSeries DataBlade 中,不管 calendar pattern 的精度如何,所有日期时间都按照从年精确到秒后面的小数位(5位)的形式处理。其次,TimeSeries DataBlade 提供的函数和表通常使用特殊格式的 lvarchar 字符串作为参数或列值。这使得语法检查更加复杂。为确保正确地使用它们,可以查看文档。最后,注意 calendar 和 calendar pattern 可以始于不同的日期。

接下来的一种基本结构是子类型或 ROW 类型。这是一种用户定义数据类型,它定义了存储在 TimeSeries 中的列或数据的元素。正是在这一点上,TimeSeries DataBlade 利用了 IDS 对象-关系架构的优点。这种 ROW 类型必须从一个日期时间列(从年精确到秒后面 5 位)开始。这个列并不真正存储在数据库中,但是为了让 DataBlade 能够在偏移量和日期时间之间相互转换,需要这样的一个列。ROW 类型的其他部分可以是任意数据类型的列,包括其他用户定义类型。清单 4 给出了一个例子:

清单 4. 创建 ROW 类型
CREATE ROW TYPE batstat_t (
    statyear datetime year to fraction(5),
    atbats int,
    hits int,
    runs int,
    walks int,
    etc...);

作为命名约定,我在类型名上添加了一个 "_t",这样就可以知道它是一种 ROW 类型。在很多 TimeSeries 函数中,往往既有 TimeSeries 类型的参数,又有 TimeSeries 列名作为参数。文档中的例子不够清晰,因为它们没有遵从任何命名约定。在目前版本的 IDS (版本 10)中,没有 ALTER ROWTYPE 语句。正是由于这个原因,一开始就采用正确的类型模式是至关重要的。否则,就不得不构建一个新的 ROW 类型和一个新表来存放更新的 TimeSeries 模式。

最后一种用于构建 TimeSeries 数据的基本结构是 Container。TimeSeries Container 描述数据如何存储在磁盘上。它是 dbspace 中一个有名称的空间,用于存放某种 TimeSeries ROW 类型的数据。它有一个初始的大小和一个增长参数。下面是创建容器的语法:EXECUTE PROCEDURE TSContainerCreate( ‘name_cont’, ‘dbspace_name’, ‘subtype_t’, initial_size_int_kb, growth_increment_int_kb);。 TimeSeries 数据一直位于表中,直到超出表的行宽(或用户定义的限制)。当超出一定大小后,TimeSeries 数据将被转移到 Container。一旦被转移,该数据将一直处在 Container 中。

User's Guide 对于 Container 提出了一些与性能相关的建议。建议包括:每个物理磁盘应该有一个容器,每个容器上只能有一个并发更新的用户,并且每个容器应该在一个不同的 dbspace 中。显然,应用程序的需求将决定可接受的性能。由于我的进程是在每个周末运行一次的批处理进程,因此虽然所有 Container 都在同一个 dbspace 中,我依然能够得到可以接受的性能。

阵容 —— 组装 TimeSeries

下面的例子囊括了所有的基本结构。然后我们看看利用这些数据可以做些什么。

清单 5. 构建棒球例子
-- Create the calendar pattern and calendar
INSERT INTO CalendarPatterns VALUES (‘annual’, ‘{1 on}, year’);
INSERT INTO CalendarTable (c_name, c_calendar) VALUES (‘yearly’,
  ‘startdate(1882-10-01 00:00:00.00000),
  pattstart(1882-10-01 00:00:00.00000), pattname (annual)’);
-- Create the row types describing the data
CREATE ROW TYPE batstat_t (statyear datetime year to fraction(5),
  atbats int, hits int, walks int, ...);
CREATE ROW TYPE pitchstat_t (statyear datetime year to fraction(5),
  inningspitched inning_t, hits int, earnedruns int, ...);
-- Create the TimeSeries Container
EXECUTE PROCEDURE TSContainerCreate( 'ts_baseball_bat',
  'tscontainer', 'batstat_t', 1000, 1000);
-- Create the TimeSeries table
CREATE TABLE playerstats (playerid int, 
  hitting TimeSeries(batstat_t), pitching TimeSeries(pitchstat_t) );

现在必须将数据装载到 TimeSeries 表中。棒球数据来自一个大型的数据文件。我编写了一个 .NET 程序来解析这个文件并为数据构建 INSERT 语句。User's Guide 中描述了将数据装载到 TimeSeries 的很多其他方法。作为我的生产应用程序的一个标准,我首先插入带有一个空 TimeSeries 的行。然后我使用 DataBlade 提供的 PutElemNoDups() 函数。清单 6 是一个例子:

清单 6. 将新的行插入到 TimeSeries 表中
INSERT INTO playerstats (playerid, hitting) VALUES (new_id, 
  TSCreate (‘yearly’, origin_date, threshold_num, 0, 
  initial_element_num, ‘container’));
UPDATE playerstats
  SET hitting = PutElemNoDups(hitting, 
   ROW('2004-10-01 00:00:00.00000','SFN',147,373,...)::batstat_t)
WHERE playerid = 'bondsba01';

开球! —— 访问 TimeSeries 数据

现在如何来获取存放在 TimeSeries 中的数据呢?下面给出了从 TimeSeries 中检索某个运动员在某一年的元素的方法:

清单 7. 选择一个 TimeSeries 元素
SELECT GetElem(hitting,'2004-10-01 00:00:00.00000').hr
  FROM playerstats
  WHERE playerid = 'bondsba01';
Results: 45

使用点号(".hr")来检索 batstat_t ROW 类型中的一个元素。如果没有包含元素名,则 GetElem 将返回整个 batstat_t ROW 类型。DataBlade 还提供了其他一些检索函数,例如:GetFirstElem()、GetLastElem()、GetClosestElem() 和 GetNextValid()。还有一个有用的 Clip() 函数,该函数用于提取一定范围的部分 TimeSeries,作为另一个 TimeSeries。

并非只有 SQL 函数和命令才能操纵 TimeSeries 数据。DataBlade 还包括大量的 Java 和 C 函数。C 函数是最完整的,能提供最快的访问。Java 和 C 函数可以在外部程序中使用,或者直接在引擎中与用户定义例程(UDR)一起使用。在这篇介绍中,我只讨论 SQL 函数。请参阅 User's Guide 和参考资料,以获得关于这些高级话题的更多帮助。

下面是一个更复杂的 SELECT 语句,该语句用于计算和检索 2004 年上场击球超过 300 次的所有运动员的击球率:

清单 8. 选择一组数据
SELECT playerid, (GetElem(hitting,'2004-10-01 0:00:00.00000’ ).hits /
   GetElem( hitting, '2004-10-01 00:00:00.00000').atbats) as ba
  FROM playerstats
  WHERE GetElem(hitting,'2004-10-01 00:00:00.00000').atbats > 300;

这里有一些重大的限制。GetElem 要求一个日期,因此该查询不会遍及所有年份和所有运动员。在 GetElem 函数的语法中,常数的重复输入显得冗长而烦人。对此有一些解决办法,但是首先我们还是来看 TimeSeries 的一些威力。DataBlade 提供了一个全能的 Apply 函数。Apply 可以在一个 TimeSeries 上执行一个 SQL 表达式或函数,然后返回另一个 TimeSeries。那么这有什么作用呢?下面是计算击球率的另一种方法:

清单 9. 使用 apply() —— 复杂方法
SELECT * FROM table ((SELECT TSColNumToList(
   (Apply('$hits/$atbats', hitting)::TimeSeries(one_real_t)),
    1)::list (real not null)
  FROM playerstats
  WHERE playerid = 'bondsba01'));
Results:	
0.22276029
0.261343
0.2825279
0.24827586
...

是的,这里混杂着 TimeSeries 函数、强制类型转换(:: 操作符)和其他凌乱的语法。这些类型的 TimeSeries 查询不容易编写。像 TSColNumToList 之类的函数只返回一列数据。所以必须找到一种更好的方法。实际上,好方法有两种。一种方法是使用下面清单 10 中极为有用的 TSSetToList 函数。另一种方法是使用下一节中讨论的虚表。

清单 10. TSSetToList() —— 有用的
SELECT * FROM table
  ((SELECT TSSetToList(hitting)::list(batstat_t not null)
    FROM playerstats WHERE playerid = 'bondsba01'));
Results:
   statyear               team    games    atbats  runs  hits  doubles ...
1986-10-01 00:00:00.00000  PIT    113      413     72    92    26
1987-10-01 00:00:00.00000  PIT    150      551     99   144    34
1988-10-01 00:00:00.00000  PIT    144      538     97   152    30
1989-10-01 00:00:00.00000  PIT    159      580     96   144    34
...

这个查询返回定时的表列数据,理解起来并不难。基本上,内部的选择创建一个 LIST (一个 Informix 集合类型),它被定义为 batstat_t ROW 类型。SELECT FROM TABLE 语法将 LIST 转换成一个表。

是好还是坏?

TimeSeries DataBlade 提供另一种工具来降低处理 TimeSeries 数据的复杂性 —— 这就是用于构建虚表的例程。该功能建立在 IDS 提供的虚表接口(VTI)之上。虚表使我们可以像 “常规的” 表那样处理 TimeSeries 数据。它看上去像一个视图,并且底层数据不存在重复。虚表还可以用于装载 TimeSeries 数据。虚表支持 SELECT 和 INSERT 语句,但是不支持 UPDATE 或 DELETE 语句。但是,如果数据已经存在,那么 INSERTS 相当于是更新语句。因此,只有 DELETE 是受限制的。另一个限制是,不能在 TimeSeries 虚表上创建索引。清单 11 展示了如何为棒球例子构建虚表:

清单 11. 创建虚表
EXECUTE PROCEDURE TSCreateVirtualTab(‘playerhitting_virt’, ‘playerstats’,
  ‘calendar(yearly), origin(1882-10-01 00:00:00.00000), container(
  ts_baseball_bat)’, 0, ‘hitting’);
EXECUTE PROCEDURE TSCreateVirtualTab(‘playerpitching_virt’, ‘playerstats’,
  ‘calendar(yearly), origin(1882-10-01 00:00:00.00000), container(
  ts_baseball_pitch)’, 0, ‘pitching’);
SELECT * FROM playerhitting_virt;
Result:
Playerid    Statyear                   Team Games AtBats
aardsda01   2004-10-01 00:00:00.00000   SFN 11    0 ...
aaronha01   1954-10-01 00:00:00.00000   ML1 122   468 ... 
aaronha01   1955-10-01 00:00:00.00000   ML1 153   602 ...

TSCreateVirtualTab() 函数创建虚表。第一个参数是新的表名。同样,我创造了一个命名约定。在虚表的名称后面,我添加一个 "_virt",这样就可以知道它是一个虚表。第二个参数(以黑体显示)是 TimeSeries 表名。第三个参数(以黑体显示)是一个可选的描述,用于在将新的行插入到虚表时创建的空 TimeSeries。同样,它是一个特殊格式的 lvarchar。注意,这个例子为每个 TimeSeries 列创建一个单独的虚表。有了这些虚表,就可以构建传统的查询。下面是一个查询,用于计算自 1905 年以来所有运动员的击球率和多垒安打率:

清单 12. 虚表上的查询
SELECT playerid, year(statyear), round(hits/atbats, 3) as ba,
   round(((hits + 2* doubles + 3*triples + 4*hr)/atbats),3) as slg
  FROM playerhitting_virt
  WHERE atbats > 300
   AND year(statyear) > 1905
  ORDER BY 4 DESC;
Results:
playerid   (expression)  ba      slg
bondsba01   2001        0.328   1.088
bondsba01	2004        0.362   1.013
bondsba01	2002        0.370   0.995
gehrilo01   1927        0.373   0.966

看过了 TimeSeries 函数和强制类型转换以及其他元素之后,虚表看上去是一种自然的选择。 然而,虚表也存在一些缺点。主要缺点是性能。虚表上的查询可能非常慢,尤其是在 WHERE 子句中使用某个 TimeSeries 元素时更是如此。在我的测试中,虚表上的查询比做同样查询的 TimeSeries 函数要慢一个数量级。而且,虚表查询偶尔还会返回奇怪的结果,而在使用 TimeSeries 函数时不会碰到这种情况。虚表在快速查询、装载数据和用于报告编写程序及其他终端用户工具等方面很出色(如果性能尚能接受的话)。但是,要获得更好的速度,则必须精心构造更复杂的 TimeSeries 查询。像 ServerStudioJE 的 Advanced SQL Editor 之类的工具会很有帮助。

结束语

在这篇 TimeSeries 简介的最后,我想根据 3 年多使用 TimeSeries DataBlade 的经验,为您提供 4 点实现方面的提示。这里姑且将其称作把您送到一垒位置的故意保送上垒。第一球:确保模式无误。如前所述,ALTER ROW TYPE 这样的语句是不存在的。从一个 TimeSeries 模式到另一个模式的迁移是错综复杂的,需要一定的时间。第二球:观察 TimeSeries 系统表,例如 tsinstance。这些系统表可能很快地增长,并蔓延到很多区段。应该用更合适的区段大小重建这些表。第三球:在不要求速度的情况下,为了方便可以使用虚表。第四球:构建存储过程来处理创建和更新 TimeSeries 的细节。有一些步骤需要走对,有一些与性能相关的参数需要记住;把这些东西写下来,并理解透彻。好了,击球吧!


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=98638
ArticleTitle=TimeSeries DataBlade 简介
publish-date=11102005