TimeScaleDB 简介

  • 技术不断的创新是为了更好的服务于业务,当前业务系统有各种的需求传统的数据库不能很好的满足,例如:物联网数据采集、用户的行为分析、大规模的监控系统等 ,这类系统我们面临如下特点:
    • 持续的大批量数据点的写入
    • 对上亿数据的分组聚合运算

面对如上需求时传统数据库不太能很好进行支持

TimescaleDB是基于PostgreSQL数据库打造的一款时序数据库支持完整SQL语言,插件化的形式,随着PostgreSQL的版本升级而升级,不会因为另立分支带来麻烦。

TimeScaleDB特点

  1. 基于时序优化

  2. 自动分片(按时间、空间自动分片(chunk))

  3. 全SQL接口

  4. 支持垂直于横向扩展

  5. 支持时间维度、空间维度自动分区。空间维度指属性字段(例如传感器ID,用户ID等)。

  6. 支持多个SERVER,多个CHUNK的并行查询。分区在TimeScaleDB中被称为chunk。

  7. 自动调整CHUNK的大小。

  8. 内部写优化(批量提交、内存索引、事务支持、数据倒灌)。
    内存索引,因为chunk size比较适中,所以索引基本上都不会被交换出去,写性能比较好。
    数据倒灌,因为有些传感器的数据可能写入延迟,导致需要写以前的chunk,TimeScaleDB允许这样的事情发生(可配置)。

  9. 复杂查询优化(根据查询条件自动选择chunk,最近值获取优化(最小化的扫描,类似递归收敛),limit子句pushdown到不同的server,chunks,并行的聚合操作)。

  10. 利用已有的PostgreSQL特性(支持GIS,JOIN等),方便的管理(流复制、PITR)。

  11. 支持自动的按时间保留策略(自动删除过旧数据)。

一.Postgresql安装TimeScaleDB插件

  • 在Linux环境下,TimeScaleDB以插件的形式存在于pg数据库中,首先我们需要安装相应的rpm包。
    1. wget https://timescalereleases.blob.core.windows.net/rpm/timescaledb-0.8.0-postgresql-9.6-0.x86_64.rpm
    2. # For PostgreSQL 10:
    3. wget https://timescalereleases.blob.core.windows.net/rpm/timescaledb-0.8.0-postgresql-10-0.x86_64.rpm
    4. yum install timescaledb*.rpm
  • 更新postgresql.conf
    添加或修改下面的参数
    1. shared_preload_libraries = 'timescaledb'
  • 创建时序数据库(或者在已经存在的数据库中)
    1. create database timescale;
  • 在timescale数据库下创建EXTENSION(需要是超级用户postgres)
    1. CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
  • 创建时序表
  1. CREATE TABLE "locations"(
  2. device_id TEXT,
  3. location TEXT,
  4. environment TEXT
  5. );
  6. DROP TABLE IF EXISTS "conditions";
  7. CREATE TABLE "conditions"(
  8. time TIMESTAMP WITH TIME ZONE NOT NULL,
  9. device_id TEXT,
  10. temperature NUMERIC,
  11. humidity NUMERIC
  12. );
  13. CREATE INDEX ON "conditions"(time DESC);
  14. CREATE INDEX ON "conditions"(device_id, time DESC);
  15. -- 86400000000 is in usecs and is equal to 1 day
  16. SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
  • 我们会发现和创建普通表并没有多大的区别,成为时序表主要依赖于创建函数。
    1. SELECT create_hypertable('readings', 'time', chunk_time_interval => 86400000000);
  • 最重要的一步,制定划分chunks的规则,通过函数create_hypertable实现。
  • 其中chunk_time_interval表示分区时间间隔,以微秒作为单位,86400000000表示一天,意思是第二天的数据会自动生成新的分区,也可以将其换成 interval ‘1 day’。
  • 如果device_id值很多,也可以SELECT create_hypertable(‘readings’, ‘time’,’deviced_id’,4),添加space partition。

二. 查询数据

Timescaledb首先支持常见的DML,DDL等语句,与其他数据库SQL语句相比并无多大差异,但不同的是timescaledb提供了更加全面,侧重于功能性查询的SQL,最主要的还是依赖其丰富的函数,通过几个实际会用到的例子来展示下TimeScaledb的特点:

  • 按照固定的时间间隔显示查询结果,还可以求出间隔内各种数据的平均值,下面是按每15分钟为间隔:
  1. SELECT time_bucket('15 minutes', time) AS fifteen_min,
  2. device_id, COUNT(*),
  3. MAX(temperature) AS max_temp,
  4. avg(temperature) AS avg_temp,
  5. MAX(humidity) AS max_hum
  6. FROM conditions
  7. WHERE time > '2016-12-06 00:00:00'
  8. and device_id='weather-pro-000446'
  9. GROUP BY fifteen_min, device_id
  10. ORDER BY fifteen_min, max_temp DESC;
  11. fifteen_min | device_id | count | max_temp | avg_temp | max_hum
  12. ------------------------+--------------------+-------+-------------------+---------------------+-------------------
  13. 2016-12-06 00:00:00+08 | weather-pro-000446 | 7 | 87.00000000000023 | 86.9571428571430886 | 80.99999999999967
  14. 2016-12-06 00:15:00+08 | weather-pro-000446 | 7 | 87.4000000000002 | 87.2142857142859286 | 80.79999999999967
  15. 2016-12-06 00:30:00+08 | weather-pro-000446 | 8 | 87.6000000000002 | 87.5125000000002000 | 80.99999999999969
  16. 2016-12-06 00:45:00+08 | weather-pro-000446 | 7 | 87.90000000000018 | 87.8142857142858986 | 81.49999999999969
  17. 2016-12-06 01:00:00+08 | weather-pro-000446 | 8 | 88.10000000000016 | 87.9375000000001763 | 82.19999999999969
  18. 2016-12-06 01:15:00+08 | weather-pro-000446 | 7 | 88.30000000000015 | 88.2142857142858714 | 82.4999999999997
  19. 2016-12-06 01:30:00+08 | weather-pro-000446 | 8 | 88.50000000000014 | 88.3625000000001475 | 83.0999999999997
  20. 2016-12-06 01:45:00+08 | weather-pro-000446 | 7 | 88.60000000000014 | 88.5857142857144257 | 83.19999999999969 |
  • 查询温度的中间值(0.5表示前50%)
  1. SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY temperature) from conditions;
  2. percentile_cont
  3. -----------------
  4. 70
  5. (1 row)
  • 求每个设备记录温度的总值(累计求和)
  1. SELECT device_id, sum(sum(temperature)) OVER(ORDER BY device_id)
  2. FROM conditions
  3. GROUP BY device_id;
  4. device_id | sum
  5. --------------------+----------------------------
  6. weather-pro-000000 | 1004378.000000000010660
  7. weather-pro-000001 | 2321003.700000002756870
  8. weather-pro-000002 | 3613164.900000005044690
  9. weather-pro-000003 | 4910957.300000007725370
  10. weather-pro-000004 | 6206754.800000009968280
  11. weather-pro-000005 | 6786614.600000009301897
  12. weather-pro-000006 | 8103056.600000011852107
  13. weather-pro-000007 | 9419788.700000014585027
  • 查询每个设备记录的第一个温度值和最后(最新)的值。

    1. SELECT device_id, first(temperature, time),last(temperature, time)
    2. FROM conditions
    3. GROUP BY device_id;
    4. device_id | first | last
    5. --------------------+--------------------+--------------------
    6. weather-pro-000000 | 71.9 | 60.2
    7. weather-pro-000001 | 89.7 | 83.80000000000041
    8. weather-pro-000002 | 73.9 | 82.50000000000043
    9. weather-pro-000003 | 76.7 | 83.0000000000004
    10. weather-pro-000004 | 74.1 | 81.40000000000049
    11. weather-pro-000005 | 31.2 | 36.59999999999992
    12. weather-pro-000006 | 80.3 | 82.40000000000049
    13. weather-pro-000007 | 88.7 | 84.60000000000036
  • 下面的查询涉及到了histogram(直方图)这个函数,histogram(temperature, 60.0, 85.0, 5)表示会计算七个范围内值的数量(undefined

    1. SELECT device_id, COUNT(*),
    2. histogram(temperature, 60.0, 85.0, 5)
    3. FROM conditions
    4. WHERE time > '2016-12-1 00:00:00'
    5. GROUP BY device_id;
    6. device_id | count | histogram
    7. --------------------+-------+--------------------------
    8. weather-pro-000000 | 4079 | {0,1560,1498,1021,0,0,0}
    9. weather-pro-000001 | 4079 | {0,0,0,0,0,871,3208}
    10. weather-pro-000002 | 4079 | {0,0,0,0,0,1321,2758}
    11. weather-pro-000003 | 4079 | {0,0,0,0,0,1160,2919}
    12. weather-pro-000004 | 4079 | {0,0,0,0,0,1384,2695}
    13. weather-pro-000005 | 4079 | {4079,0,0,0,0,0,0}
    14. weather-pro-000006 | 4079 | {0,0,0,0,0,938,3141}
    15. weather-pro-000007 | 4079 | {0,0,0,0,0,909,3170}
    16. weather-pro-000008 | 4079 | {3730,349,0,0,0,0,0}
  • 如果我们查询一个设备某一天记录的总的温度,设定了查询时间间隔后,如2016-11-13到2016-12-08,但是只能显示出11-15到12-06,因为其他的时间没有记录,应当显示为0,当应用到资产记录或者交易金额时,显然需要人工添加记录0,可以通过pg的generate_series和coalesce函数来解决。

    1. WITH data AS (
    2. SELECT
    3. time_bucket('1 day', time)::date AS date,
    4. sum(temperature) AS temperature
    5. FROM conditions
    6. WHERE device_id='weather-pro-000226' and
    7. time >= '2016-11-13' AND time < '2016-12-08'
    8. GROUP BY date ),
    9. period AS (
    10. SELECT date::date
    11. FROM generate_series(date '2016-11-13', date '2016-12-08', interval '1 day') date)
    12. SELECT period.date, coalesce(sum(data.temperature), 0) AS temperature
    13. FROM period
    14. LEFT JOIN data ON period.date = data.date
    15. GROUP BY period.date
    16. ORDER BY period.date DESC;
    17. date | temperature
    18. ------------+-----------------------
    19. 2016-12-08 | 0
    20. 2016-12-07 | 0
    21. 2016-12-06 | 15977.60000000004670
    22. 2016-12-05 | 48200.30000000012515
    23. 2016-12-04 | 48740.90000000009441
    24. 2016-12-03 | 48263.20000000012173
    25. 2016-12-02 | 47165.00000000018387
    26. 2016-12-01 | 48883.90000000008618
    27. 2016-11-30 | 49680.59999999991651
    28. 2016-11-29 | 46707.200000000026689

三. 迁移数据

一个普通表有数据是无法成为时序表的,所以必须创建新的一样结构的空表。

  1. #比如有旧表infos,我们可以先
  2. CREATE TABLE new_infos (LIKE infos INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
  3. #Excluding indexes表示不创建和原表一样的索引
  4. #接着
  5. CREATE INDEX on new_infos (time DESC);
  6. CREATE INDEX on new_infos (location,time DESC);
  7. #然后
  8. SELECT create_hypertable('new_infos', 'time');
  9. SELECT create_hypertable('new_infos', 'time','location',4);
  10. #表示另加一个分区条件,以location(例如设备id,用户id等)做为分区依据,再划分成4个chunks。
  11. #最后
  12. INSERT INTO new_infos SELECT * FROM infos;
  13. #也可以用copy命令以csv文件的格式导入其他数据库的数据。

四.相关管理的函数

具体参考地址:http://docs.timescale.com/v0.8/api

  • add_dimension( ) 添加chunks范围。

    1. SELECT create_hypertable('conditions', 'time');
    2. SELECT add_dimension('conditions', device_id, number_partitions => 4);

    表必须为空表,相当于 SELECT create_hypertable(‘conditions’, ‘time’, device_id,4);

  • attach_tablespace( )

    1. SELECT attach_tablespace('disk1', 'conditions');
    2. #这个如果是已经创建好了且有数据的时序表,无法在转移到指定的表空间中。
    3. SELECT detach_tablespace('disk1', 'conditions');
    4. #下次有新的数据更新时,新产生的chunks将不会存入disk1这个表空间中。
    5. SELECT detach_tablespaces('conditions');
    6. #对于conditions这张时序表,下次更新数据产生新的chunks将会存入默认空间。
  • drop_chunks()

    1. SELECT drop_chunks(interval '3 months');
    2. SELECT drop_chunks(interval '3 months', 'conditions');
    3. SELECT drop_chunks('2017-01-01'::date, 'conditions');
    4. SELECT drop_chunks(1483228800000, 'conditions');

    值得注意的是,timescaledb只会以chunks(块)的单位删除,比如我们要删除24小时以前的数据,第一个块是36小时前的,第二个是12-36小时,第三个是0-12小时,数据库只会删除第一个,尽管第二个也包含了24小时前的,简单点来说,chunks中所有数据必须满足条件才可以删除。
    其次,我们也可以在crontab中添加周期任务或者系统定时器来删除旧数据,如

    1. 0 3 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1
  • set_chunk_time_interval( ) 设定时间间隔

    1. SELECT set_chunk_time_interval('conditions', interval '24 hours');
    2. SELECT set_chunk_time_interval('conditions', 86400000000);

    假设原先chunks的时间间隔为24小时,改为48小时后,原来的chunks不会合并,只是以后insert 新的数据,会按照48小时来创建chunks,值得注意的是,删除某段时间间隔的数据并不会同时删除所在的chunks,需要使用drop_chunks( )。

  • 查看hypertable或chunks信息的函数

    1. #显示时序表各个chunks的信息,如大小。
    2. SELECT chunk_table, table_bytes, index_bytes, total_bytes FROM chunk_relation_size('conditions');
    3. #下面的函数会显示常见的大小格式,如KB,MB,GB。
    4. SELECT chunk_table, table_size, index_size, total_size FROM chunk_relation_size_pretty('conditions');
    5. #显示整张时序表的总大小
    6. SELECT table_bytes, index_bytes, toast_bytes, total_bytes FROM hypertable_relation_size('conditions');
    7. SELECT table_size, index_size, toast_size, total_size FROM hypertable_relation_size_pretty('conditions');
    8. #查询时序表索引的大小
    9. SELECT * FROM indexes_relation_size('conditions');
    10. SELECT * FROM indexes_relation_size_pretty('conditions');
0 评论  
添加一条新评论