MemSQL 赞助了这篇文章。
在MemSQL，我们看到了对使用我们的数据库进行时间序列的浓厚兴趣。当组织经历以下情况时尤其如此：（1）高事件摄取率，（2）低延迟查询和（3）高并发查询率。
在下文中，我将展示如何将MemSQL用作功能强大的时间序列数据库，并通过简单查询和用户定义函数来说明这一点，该函数显示如何进行时间序列 - 频率转换，平滑等操作。
我还介绍了如何快速加载时间序列数据点，没有比例限制。
与大多数特定于时间序列的数据库不同，MemSQL支持标准SQL，包括内部和外部联接，子查询，公用表表达式（CTE），视图，用于日期和时间操作的丰富标量函数，分组，聚合和窗口函数。我们支持所有常见的SQL数据类型，包括具有微秒精度的datetime（6）类型，该类型非常适合作为时间序列时间戳。
赞助商注意
MemSQL是应用程序和分析的无限制数据库。MemSQL的分布式体系结构旨在实现最大的摄取，快速查询和高并发性，以帮助客户处理，分析和处理当今洞察力驱动的经济数据。MemSQL可以在任何公共云或本地运行。
金融交易系统中常见的时间序列分析类型是操纵股票价格。这是使用标准SQL进行此类计算的简单示例。我们使用一个包含多个股票时间序列的表格，并为每个股票生成高，低，开盘和收盘：
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE tick(ts datetime(6), symbol varchar(5), price numeric(18,4)); INSERT INTO tick VALUES ('2019-02-18 10:55:36.179760', 'ABC', 100.00), ('2019-02-18 10:57:26.179761', 'ABC', 101.00), ('2019-02-18 10:59:16.178763', 'ABC', 102.50), ('2019-02-18 11:00:56.179769', 'ABC', 102.00), ('2019-02-18 11:01:37.179769', 'ABC', 103.00), ('2019-02-18 11:02:46.179769', 'ABC', 103.00), ('2019-02-18 11:02:59.179769', 'ABC', 102.60), ('2019-02-18 11:02:46.179769', 'XYZ', 103.00), ('2019-02-18 11:02:59.179769', 'XYZ', 102.60), ('2019-02-18 11:03:59.179769', 'XYZ', 102.50); |
此查询使用标准SQL 窗口函数为表中的每个符号生成高，低，打开和关闭值，假设“ticks”包含最近交易日的数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | WITH ranked AS (SELECT symbol, RANK() OVER w as r, MIN(price) OVER w as min_pr, MAX(price) OVER w as max_pr, FIRST_VALUE(price) OVER w as first, LAST_VALUE(price) OVER w as last FROM tick WINDOW w AS (PARTITION BY symbol ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) SELECT symbol, min_pr, max_pr, first, last FROM ranked WHERE r = 1; |
1 2 3 4 5 6 7 | Results: +--------+----------+----------+----------+----------+ | symbol | min_pr | max_pr | first | last +--------+----------+----------+----------+----------+ | XYZ | 102.5000 | 103.0000 | 103.0000 | 102.5000 | | ABC | 100.0000 | 103.0000 | 100.0000 | 102.6000 | +--------+----------+----------+----------+----------+ |
类似的查询可用于创建“烛台图表”，这是一种流行的金融时间序列报表样式，如下图所示，可显示连续时间间隔内证券的开盘价，最高价，最低价和收盘价：
例如，此查询生成一个表，可以三分钟间隔直接转换为烛台图表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH ranked AS (SELECT symbol, ts, RANK() OVER w as r, MIN(price) OVER w as min_pr, MAX(price) OVER w as max_pr, FIRST_VALUE(price) OVER w as first, LAST_VALUE(price) OVER w as last FROM tick WINDOW w AS (PARTITION BY symbol, time_bucket('3 minute', ts) ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) SELECT symbol, time_bucket('3 minute', ts), min_pr, max_pr, first, last FROM ranked WHERE r = 1 ORDER BY 1, 2; |
结果：
1 2 3 4 5 6 7 8 9 | +--------+-----------------------------+----------+----------+----------+----------+ | symbol | time_bucket('3 minute', ts) | min_pr | max_pr | first | last | +--------+-----------------------------+----------+----------+----------+----------+ | ABC | 2019-02-18 10:54:00.000000 | 100.0000 | 100.0000 | 100.0000 | 100.0000 | | ABC | 2019-02-18 10:57:00.000000 | 101.0000 | 102.5000 | 101.0000 | 102.5000 | | ABC | 2019-02-18 11:00:00.000000 | 102.0000 | 103.0000 | 102.0000 | 102.6000 | | XYZ | 2019-02-18 11:00:00.000000 | 102.6000 | 103.0000 | 103.0000 | 102.6000 | | XYZ | 2019-02-18 11:03:00.000000 | 102.5000 | 102.5000 | 102.5000 | 102.5000 | +--------+-----------------------------+----------+----------+----------+----------+ |
平滑是管理时间序列中的另一个常见需求。此查询为库存“ABC”生成平滑的价格序列，平均最后三个价格的价格：
1 2 3 4 | SELECT symbol, ts, price, AVG(price) OVER (ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS smoothed_price FROM tick WHERE symbol = 'ABC'; |
结果：
1 2 3 4 5 6 7 8 9 10 11 | +--------+----------------------------+----------+----------------+ | symbol | ts | price | smoothed_price | +--------+----------------------------+----------+----------------+ | ABC | 2019-02-18 10:55:36.179760 | 100.0000 | 100.00000000 | | ABC | 2019-02-18 10:57:26.179761 | 101.0000 | 100.50000000 | | ABC | 2019-02-18 10:59:16.178763 | 102.5000 | 101.16666667 | | ABC | 2019-02-18 11:00:56.179769 | 102.0000 | 101.37500000 | | ABC | 2019-02-18 11:01:37.179769 | 103.0000 | 102.12500000 | | ABC | 2019-02-18 11:02:46.179769 | 103.0000 | 102.62500000 | | ABC | 2019-02-18 11:02:59.179769 | 102.6000 | 102.65000000 | +--------+----------------------------+----------+----------------+ |
埃里克汉森
Eric Hanson是MemSQL的首席产品经理，负责查询处理，可扩展性和地理空间特征领域。他是博士。毕业于加州大学伯克利分校，曾任美国空军军官，佛罗里达大学计算机科学教授，微软SQL Server首席项目经理和开发人员。
MemSQL支持用户定义的标量函数（UDF），聚合函数和存储过程（MPSQL语言）的可扩展性。MemSQL将UDF和存储过程编译为机器代码以实现高性能。我实际上使用了可扩展性来创建上一节中用作UDF 的time_bucket（）函数（如下面的附录所示）; 这为其他特定时间序列产品中的类似功能提供了相同的功能。您可以按时间间隔轻松创建函数或表达式，例如秒，分钟，小时或日。时间序列数据的共同需求是执行插值。
例如，假设您有一个时间序列，其中随机间隔的点平均间隔为30秒。可能有几分钟没有数据点。因此，如果您将原始（不规则）时间序列数据转换为具有一分钟点的常规时间序列，则可能存在间隙。如果要为没有间隙的绘图提供输出，则需要从间隙前后的值中插入间隙的值。通过获取查询结果并输出插入到临时表中的间隙的行集，可以直接在MemSQL中实现存储过程。
然后可以使用ECHO命令将其发送回客户端应用程序。此外，MemSQL支持用户定义的聚合函数，可用于实现有用的时间序列操作，例如获取序列中第一个和最后一个值的简写，而无需特定的窗口函数。根据一个名为FIRST（）的用户定义的聚合函数（UDAF），考虑此查询以在每三分钟的交易中获得股票ABC的第一个值：
1 2 3 4 5 | SELECT time_bucket('3 minute', ts), first(price, ts) FROM tick WHERE symbol = "ABC" GROUP BY 1 ORDER BY 1; |
结果：
+-----------------------------+------------------+| time_bucket('3 minute', ts) | first(price, ts) |+-----------------------------+------------------+| 2019-02-18 10:54:00.000000 | 100.0000 || 2019-02-18 10:57:00.000000 | 101.0000 || 2019-02-18 11:00:00.000000 | 102.0000 |+-----------------------------+------------------+
的实现FIRST（） ，和类似的LAST（） UDAF示于下面的参考文献部分。
MemSQL擅长处理时间序列事件的突发插入流量和节省空间很重要的历史时间序列信息。对于突发插入流量，您可以使用MemSQL行存储表来保存时间序列事件。对于已经老化的较大且寿命较长的时间序列事件集或较旧的时间序列数据集，因此它们不再被更新，MemSQL列存储是一种很好的格式，因为它非常有效地压缩时间序列数据。此外，它驻留在磁盘上，因此主内存大小不限制您可以存储的数据量。
构建时间序列应用程序时，数据可以来自许多来源，包括应用程序，文件系统，S3，HDFS，Azure Blob商店和Kafka队列。MemSQL可以从所有这些来源快速地获取数据。MemSQL Pipelines专门用于快速轻松地从这些源加载数据流，无需过程编码即可在MemSQL中建立快速的事件流。MemSQL可以以惊人的数据速率提取数据。在最近的测试中，我直接从应用程序每秒插入2,850,500个事件具有完整的事务完整性和持久性，使用两叶MemSQL集群，每个叶子在Intel Xeon Platinum 28-Core系统上运行。使用直接装载或Kafka管道可以获得可比较甚至更好的费率。如果你必须扩展更高，只需添加更多节点 - 没有实际限制。
我们已经看到时间序列数据管理市场分为时间序列的专用产品，它们有自己的专用语言，以及可以与使用SQL的标准报告和商业智能工具互操作的扩展SQL系统。MemSQL属于第二类。MemSQL适用于需要快速摄取，低延迟查询和高并发性的时间序列应用程序，没有扩展限制，并且受益于SQL语言功能和SQL工具连接。
许多特定于时间序列的产品在数据管理方面存在缺陷。有些人缺乏横向扩展，限制了他们可以解决的问题的规模，或者迫使应用程序开发人员在他们的代码中构建曲折的分片逻辑，以便跨多个实例分割数据，从而花费宝贵的劳动力资金，可以更好地投入到应用程序业务逻辑中。其他系统已经解释了查询处理器无法跟上像我们这样的最新查询执行实现。有些缺少SQL数据库常见的事务处理完整性功能。MemSQL让时间序列应用程序开发人员自信地前进，知道他们不会达到规模墙，他们可以使用他们熟悉的所有工具 - 任何可以连接到SQL数据库的工具。
MemSQL是一个用于管理时间序列数据的强大平台。它支持快速方便地加载事件流的能力，无限扩展。它支持完整的SQL，可以使用SQL 92的所有标准功能以及最近添加的窗口函数扩展来实现复杂的查询。它支持许多开发人员对各种应用程序（包括时间序列）所需的事务，高速并发更新和查询以及高可用性技术。您最喜欢的SQL兼容工具可以连接到MemSQL。这使得MemSQL成为时间序列的强大平台。立即免费下载MemSQL，并根据您的时间序列数据进行试用！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- Usage: first(value, timestamp_expr) -- Example: -- Get first value of x for each day from a time series in table -- t(x, ts) -- with timestamp ts. -- -- SELECT ts :> date, first(x, ts) FROM t GROUP BY 1 ORDER BY 1; DELIMITER // CREATE OR REPLACE FUNCTION first_init() RETURNS RECORD(v TEXT, d datetime(6)) AS BEGIN RETURN ROW("_empty_set_", '9999-12-31 23:59:59.999999'); END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION first_iter(state RECORD(v TEXT, d DATETIME(6)), v TEXT, d DATETIME(6)) RETURNS RECORD(v TEXT, d DATETIME(6)) AS DECLARE nv TEXT; nd DATETIME(6); nr RECORD(v TEXT, d DATETIME(6)); BEGIN -- if new timestamp is less than lowest before, update state IF state.d > d THEN nr.v = v; nr.d = d; RETURN nr; END IF; RETURN state; END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION first_merge(state1 RECORD(v TEXT, d DATETIME(6)), state2 RECORD(v TEXT, d DATETIME(6))) RETURNS RECORD(v TEXT, d DATETIME(6)) AS BEGIN IF state1.d < state2.d THEN RETURN state1; END IF; RETURN state2; END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION first_terminate(state RECORD(v TEXT, d DATETIME(6))) RETURNS TEXT AS BEGIN RETURN state.v; END // DELIMITER ; CREATE AGGREGATE first(TEXT, DATETIME(6)) RETURNS TEXT WITH STATE RECORD(v TEXT, d DATETIME(6)) INITIALIZE WITH first_init ITERATE WITH first_iter MERGE WITH first_merge TERMINATE WITH first_terminate; |
一个类似于FIRST（）的LAST（）UDAF，但返回按时间戳排序的序列中的最终值，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- Usage: last(value, timestamp_expr) -- Example: -- Get last value of x for each day from a time series in table t -- t(x, ts) -- with timestamp column ts. -- -- SELECT ts :> date, last(x, ts) FROM t GROUP BY 1 ORDER BY 1; DELIMITER // CREATE OR REPLACE FUNCTION last_init() RETURNS RECORD(v TEXT, d datetime(6)) AS BEGIN RETURN ROW("_empty_set_", '1000-01-01 00:00:00.000000'); END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION last_iter(state RECORD(v TEXT, d DATETIME(6)), v TEXT, d DATETIME(6)) RETURNS RECORD(v TEXT, d DATETIME(6)) AS DECLARE nv TEXT; nd DATETIME(6); nr RECORD(v TEXT, d DATETIME(6)); BEGIN -- if new timestamp is greater than largest before, update state IF state.d < d THEN nr.v = v; nr.d = d; RETURN nr; END IF; RETURN state; END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION last_merge(state1 RECORD(v TEXT, d DATETIME(6)), state2 RECORD(v TEXT, d DATETIME(6))) RETURNS RECORD(v TEXT, d DATETIME(6)) AS BEGIN IF state1.d > state2.d THEN RETURN state1; END IF; RETURN state2; END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION last_terminate(state RECORD(v TEXT, d DATETIME(6))) RETURNS TEXT AS BEGIN RETURN state.v; END // DELIMITER ; CREATE AGGREGATE last(TEXT, DATETIME(6)) RETURNS TEXT WITH STATE RECORD(v TEXT, d DATETIME(6)) INITIALIZE WITH last_init ITERATE WITH last_iter MERGE WITH last_merge TERMINATE WITH last_terminate; |
通过特征图像Pixabay。
联系客服
