InfluxDB入門 - 闘うITエンジニアの覚え書き

オープンソースの時系列データベースである InfluxDB の簡単な使用方法等について記載する。

ここでは公開されているdockerイメージを使用する事とする。

作業用ディレクトリ作成

mkdir myinfluxdb
cd myinfluxdb

influxdb 及び chronograf 用のディレクトリ作成

mkdir influxdb
mkdir etc/myinfluxdb
mkdir chronograf

influxdb 用の設定ファイル作成
※デフォルト設定でよければ作らなくても良い。

./etc/influxdb/influxdb.conf

[meta]
  dir = "/var/lib/influxdb/meta"

[data]
  dir = "/var/lib/influxdb/data"
  engine = "tsm1"
  wal-dir = "/var/lib/influxdb/wal"

[http]
  auth-enabled = true

docker-compose.yml

version: "3" 

services:
  influxdb:
    image: influxdb
    volumes:
      - ./influxdb:/var/lib/influxdb
      - ./etc/influxdb:/etc/influxdb
    ports:
      - 8086:8086

  chronograf:
    image: chronograf:alpine
    volumes:
      - ./chronograf:/var/lib/chronograf
    ports:
      - 8888:8888
    links:
      - influxdb
    depends_on:
      - influxdb

起動

docker-compose up

手っ取り早く必要なものが揃うサンドボックス用の docker-compose.yml が公開されているので、そちらを使用しても良い。
https://github.com/influxdata/sandbox

コンテナIDを確認

docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
baeed6a9bce9        chronograf:alpine   "/entrypoint.sh chro…"   4 minutes ago       Up 3 minutes        0.0.0.0:8888->8888/tcp   influxdb_chronograf_1
e66c42c48178        influxdb            "/entrypoint.sh infl…"   4 minutes ago       Up 3 minutes        0.0.0.0:8086->8086/tcp   influxdb_influxdb_1

コンテナに入る

docker exec -it e66c42c48178 /bin/bash

InfluxDB shellを起動する

influx -precision rfc3339
Connected to http://localhost:8086 version 1.7.9
InfluxDB shell version: 1.7.9
> 

※ユーザ作成後は influx -username 'user名' -password 'パスワード' -precision rfc3339 で接続可能。

以降は InfluxDB shell から InfluxQL を使用して各種操作を行う事ができる。
詳細は、以下の URL 及び 後述を参照。

データベースの作成 データの登録/検索まで †

> create database mydb1

> show databases
name: databases
name
----
_internal
mydb1

> use mydb1
Using database mydb1

> insert sample,server=server1,ym=201912 load_average=0.5

> select * from sample
name: sample
time                         load_average server  ym
----                         ------------ ------  --
2019-12-08T11:00:54.1441902Z 0.5          server1 201912

https://docs.influxdata.com/influxdb/v1.7/concepts/glossary/

Retention policy †

Measurement †

Series †

Tag †

Field †

Timestamp †

データ型 †

https://docs.influxdata.com/influxdb/v1.7/administration/authentication_and_authorization/
https://docs.influxdata.com/influxdb/v1.7/query_language/database_management/
https://docs.influxdata.com/influxdb/v1.7/query_language/schema_exploration/
https://docs.influxdata.com/influxdb/v1.7/tools/shell/

ユーザの作成 †

CREATE USER <username> WITH PASSWORD '<password>' WITH ALL PRIVILEGES  # 管理者ユーザの場合

データベースの作成 †

CREATE DATABASE <database_name> [WITH [DURATION <duration>] [REPLICATION <n>] [SHARD DURATION <duration>] [NAME <retention-policy-name>]]

> create database mydb1
> show databases
name: databases
name
----
_internal
mydb1

データベースの削除 †

DROP DATABASE <database_name>

> drop database mydb1
> show databases
name: databases
name
----
_internal

計測データの作成 †

INSERT INTO [retention policy] <measurement>[,<tag_key>=<tag_value>[,<tag_key>=<tag_value>]] <field_key>=<field_value>[,<field_key>=<field_value>] [<timestamp>]

insert sample,type=1 value=1 1576407071041296896
> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-15T10:51:11.041296896Z 1    1

> insert sample,type=1 value=100 1576407071041296896
> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-15T10:51:11.041296896Z 1    100

> insert sample,type=2 value=1 1576407071041296896
> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-15T10:51:11.041296896Z 1    100
2019-12-15T10:51:11.041296896Z 2    1

計測データの削除 †

DROP MEASUREMENT <measurement_name>
もしくは
DELETE FROM <measurement_name> where ....

RETENTION POLICY の作成 †

CREATE RETENTION POLICY <retention_policy_name> ON <database_name> DURATION <duration> REPLICATION <n> [SHARD DURATION <duration>] [DEFAULT]

RETENTION POLICY の削除 †

DROP RETENTION POLICY <retention_policy_name> ON <database_name>

https://docs.influxdata.com/influxdb/v1.7/query_language/data_exploration/

計測データの検索 †

SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]

計測データから別の計測データを作成する †

SELECT_clause INTO <measurement_name> FROM_clause [WHERE_clause] [GROUP_BY_clause]

help を入力すると使用できるコマンドが表示される。
使用頻度の高い use や show などはもちろん、表示/整形に有用な pretty や format なども覚えておきたい。

https://docs.influxdata.com/influxdb/v1.7/tools/shell/
https://docs.influxdata.com/influxdb/v1.7/query_language/schema_exploration/

> help
Usage:
        connect    connects to another node specified by host:port
        auth                  prompts for username and password
        pretty                toggles pretty print for the json format
        chunked               turns on chunked responses from server
        chunk size      sets the size of the chunked responses.  Set to 0 to reset to the default chunked size
        use          sets current database
        format  
       specifies the format of the server responses: json, csv, or column
        precision  
    specifies the format of the timestamp: rfc3339, h, m, s, ms, u or ns
        consistency    sets write consistency level: any, one, quorum, or all
        history               displays command history
        settings              outputs the current settings for the shell
        clear                 clears settings such as database or retention policy.  run 'clear' for help
        exit/quit/ctrl+d      quits the influx shell

        show databases        show database names
        show series           show series information
        show measurements     show measurement information
        show tag keys         show tag key information
        show field keys       show field key information

        A full list of influxql commands can be found at:
        https://docs.influxdata.com/influxdb/latest/query_language/spec/

以下、サンプルをいくつか記載する。

データベースの一覧 †

show databases

measurement の一覧 †

show measurements

tag の一覧 †

show tag keys
show tag keys from <measurement>

field の一覧 †

show field keys
show field keys from <measurement>

retention policy の一覧 †

show retention policies

https://docs.influxdata.com/influxdb/v1.7/administration/backup_and_restore/#backup

influxd backup
    [ -database <db_name> ]
    [ -portable ]
    [ -host <host:port> ]
    [ -retention <rp_name> ] | [ -shard <shard_ID> -retention <rp_name> ]
    [ -start <timestamp> [ -end <timestamp> ] | -since <timestamp> ]
    <path-to-backup>

influxd restore [ -db <db_name> ]
    -portable | -online
    [ -host <host:port> ]
    [ -newdb <newdb_name> ]
    [ -rp <rp_name> ]
    [ -newrp <newrp_name> ]
    [ -shard <shard_ID> ]
    <path-to-backup-files>

確認用の measurement にデータを登録しておく。(デフォルトのリテンションポリシーと別のリテンションポリシーの２種類のデータを登録)

> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-07T10:04:30.755184896Z 9    9
2019-12-08T10:04:30.755184896Z 8    8
2019-12-09T10:04:30.755184896Z 7    7
2019-12-10T10:04:30.755184896Z 6    6
2019-12-11T10:04:30.755184896Z 5    5
2019-12-12T10:04:30.755184896Z 4    4
2019-12-13T10:04:30.755184896Z 3    3
2019-12-14T10:04:30.755184896Z 2    2
2019-12-15T10:04:30.755184896Z 1    1
> select * from one_month.sample
name: sample
time                           type value
----                           ---- -----
2019-12-07T10:04:30.755184896Z 90   90
2019-12-08T10:04:30.755184896Z 80   80
2019-12-09T10:04:30.755184896Z 70   70
2019-12-10T10:04:30.755184896Z 60   60
2019-12-11T10:04:30.755184896Z 50   50
2019-12-12T10:04:30.755184896Z 40   40
2019-12-13T10:04:30.755184896Z 30   30
2019-12-14T10:04:30.755184896Z 20   20
2019-12-15T10:04:30.755184896Z 10   10

create database mydb1
use mydb1
create retention policy one_month on mydb1 duration 31d replication 1
insert sample,type=1 value=1 1576404270755184896
insert sample,type=2 value=2 1576317870755184896
insert sample,type=3 value=3 1576231470755184896
insert sample,type=4 value=4 1576145070755184896
insert sample,type=5 value=5 1576058670755184896
insert sample,type=6 value=6 1575972270755184896
insert sample,type=7 value=7 1575885870755184896
insert sample,type=8 value=8 1575799470755184896
insert sample,type=9 value=9 1575713070755184896
insert into one_month sample,type=10 value=10 1576404270755184896
insert into one_month sample,type=20 value=20 1576317870755184896
insert into one_month sample,type=30 value=30 1576231470755184896
insert into one_month sample,type=40 value=40 1576145070755184896
insert into one_month sample,type=50 value=50 1576058670755184896
insert into one_month sample,type=60 value=60 1575972270755184896
insert into one_month sample,type=70 value=70 1575885870755184896
insert into one_month sample,type=80 value=80 1575799470755184896
insert into one_month sample,type=90 value=90 1575713070755184896

from datetime import datetime
import time

if __name__ == '__main__':

    print("create retention policy one_month on mydb1 duration 31d replication 1")

    now_ts = datetime.now()
    ts = int(now_ts.timestamp() * 1000000000)
    for i in range(1, 10):
        print(f'insert sample,type={i} value={i} {ts}')
        ts = ts - (60 * 60 * 24 * 1000000000)

    ts = int(now_ts.timestamp() * 1000000000)
    for i in range(1, 10):
        print(f'insert into one_month sample,type={i*10} value={i*10} {ts}')
        ts = ts - (60 * 60 * 24 * 1000000000)

DB名だけ指定してバックアップを取ってみる。

influxd backup -database mydb1 -portable /tmp/mydb1_all_dump
2019/12/15 10:14:18 backing up metastore to mydb1_all_dump/meta.00
2019/12/15 10:14:18 backing up db=mydb1
2019/12/15 10:14:18 backing up db=mydb1 rp=autogen shard=137 to mydb1_all_dump/mydb1.autogen.00137.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backing up db=mydb1 rp=autogen shard=136 to mydb1_all_dump/mydb1.autogen.00136.00 since 0001-01-01T00:00:00Z
:
2019/12/15 10:14:18 backing up db=mydb1 rp=autogen shard=124 to mydb1_all_dump/mydb1.autogen.00124.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backing up db=mydb1 rp=autogen shard=123 to mydb1_all_dump/mydb1.autogen.00123.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backing up db=mydb1 rp=one_month shard=147 to mydb1_all_dump/mydb1.one_month.00147.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backing up db=mydb1 rp=one_month shard=146 to mydb1_all_dump/mydb1.one_month.00146.00 since 0001-01-01T00:00:00Z
:
2019/12/15 10:14:18 backing up db=mydb1 rp=one_month shard=139 to mydb1_all_dump/mydb1.one_month.00139.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backing up db=mydb1 rp=one_month shard=138 to mydb1_all_dump/mydb1.one_month.00138.00 since 0001-01-01T00:00:00Z
2019/12/15 10:14:18 backup complete:
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.meta
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s137.tar.gz
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s136.tar.gz
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s135.tar.gz
:
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s140.tar.gz
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s139.tar.gz
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.s138.tar.gz
2019/12/15 10:14:18 	mydb1_all_dump/20191215T101418Z.manifest

今回のパラメータ指定だと、内部的にはDBとリテンションポリシー毎にバックアップを取っている様子。
尚、バックアップデータは以下のフォルダ構成で作成されていた。

.
└── mydb1_all_dump
    ├── 20191215T101418Z.manifest
    ├── 20191215T101418Z.meta
    ├── 20191215T101418Z.s123.tar.gz
    ├── 20191215T101418Z.s124.tar.gz
                              :
    ├── 20191215T101418Z.s146.tar.gz
    └── 20191215T101418Z.s147.tar.gz

リテンションポリシー毎に別のDBにリストアしてみる。

influxd restore -db mydb1 -portable -newdb mydb1_backup_autogen -rp autogen /tmp/mydb1_all_dump
influxd restore -db mydb1 -portable -newdb mydb1_backup_month -rp one_month /tmp/mydb1_all_dump

リストア結果を確認してみる。

まずはデータベースを確認

> show databases
name: databases
name
----
_internal
mydb1
mydb1_backup_autogen
mydb1_backup_month

mydb1_backup_autogen のデータを確認

> use mydb1_backup_autogen
Using database mydb1_backup_autogen
> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-07T10:04:30.755184896Z 9    9
2019-12-08T10:04:30.755184896Z 8    8
2019-12-09T10:04:30.755184896Z 7    7
2019-12-10T10:04:30.755184896Z 6    6
2019-12-11T10:04:30.755184896Z 5    5
2019-12-12T10:04:30.755184896Z 4    4
2019-12-13T10:04:30.755184896Z 3    3
2019-12-14T10:04:30.755184896Z 2    2
2019-12-15T10:04:30.755184896Z 1    1
> select * from one_month.sample
ERR: retention policy not found: one_month       ...   リテンションポリシー(one_month)がリストアしていないので当然エラーになる。

次に mydb1_backup_month 側のデータを確認

> use mydb1_backup_month
Using database mydb1_backup_month
> select * from sample
name: sample
time                           type value
----                           ---- -----
2019-12-07T10:04:30.755184896Z 90   90
2019-12-08T10:04:30.755184896Z 80   80
2019-12-09T10:04:30.755184896Z 70   70
2019-12-10T10:04:30.755184896Z 60   60
2019-12-11T10:04:30.755184896Z 50   50
2019-12-12T10:04:30.755184896Z 40   40
2019-12-13T10:04:30.755184896Z 30   30
2019-12-14T10:04:30.755184896Z 20   20
2019-12-15T10:04:30.755184896Z 10   10

なぜかデフォルトのリテンションポリシーのデータがいる？

リテンションポリシーに one_month を指定して確認してみる。

> select * from one_month.sample
name: sample
time                           type value
----                           ---- -----
2019-12-07T10:04:30.755184896Z 90   90
2019-12-08T10:04:30.755184896Z 80   80
2019-12-09T10:04:30.755184896Z 70   70
2019-12-10T10:04:30.755184896Z 60   60
2019-12-11T10:04:30.755184896Z 50   50
2019-12-12T10:04:30.755184896Z 40   40
2019-12-13T10:04:30.755184896Z 30   30
2019-12-14T10:04:30.755184896Z 20   20
2019-12-15T10:04:30.755184896Z 10   10

同じデータがいる。

リテンションポリシーの一覧を確認。

> show retention policies
name      duration shardGroupDuration replicaN default
----      -------- ------------------ -------- -------
one_month 744h0m0s 24h0m0s            1        true

リテンションポリシーを指定してリストアした為、
one_month がデフォルトのリテンションポリシーとしてリストアされた模様。

ちなみに、既に存在するDBをリストアするとエラーになる。

influxd restore -db mydb1 -portable ./mydb1_all_dump
XXXX/XX/XX XX:XX:XX error updating meta: DB metadata not changed. database may already exist
restore: DB metadata not changed. database may already exist

以下のようなデータをが登録してある場合、

> select * from sample_detail
name: sample_detail
time                     key value
----                     --- -----
2019-12-08T11:21:48.345Z 1   10
2019-12-08T11:21:48.345Z 2   1
2019-12-08T11:21:58.345Z 1   20
2019-12-08T11:21:58.345Z 2   2
2019-12-08T11:22:08.345Z 1   30
2019-12-08T11:22:08.345Z 2   3
2019-12-08T11:22:18.345Z 1   40
2019-12-08T11:22:18.345Z 2   4
2019-12-08T11:22:28.345Z 1   50
2019-12-08T11:22:28.345Z 2   5
2019-12-08T11:22:38.345Z 1   60
2019-12-08T11:22:38.345Z 2   6
2019-12-08T11:22:48.345Z 1   70
2019-12-08T11:22:48.345Z 2   7
2019-12-08T11:22:58.345Z 1   80
2019-12-08T11:22:58.345Z 2   8
2019-12-08T11:23:08.345Z 1   90
2019-12-08T11:23:08.345Z 2   9
2019-12-08T11:23:18.345Z 1   100
2019-12-08T11:23:18.345Z 2   10

insert sample_detail,key=1 value=10  1575804108345000000
insert sample_detail,key=1 value=20  1575804118345000000
insert sample_detail,key=1 value=30  1575804128345000000
insert sample_detail,key=1 value=40  1575804138345000000
insert sample_detail,key=1 value=50  1575804148345000000
insert sample_detail,key=1 value=60  1575804158345000000
insert sample_detail,key=1 value=70  1575804168345000000
insert sample_detail,key=1 value=80  1575804178345000000
insert sample_detail,key=1 value=90  1575804188345000000
insert sample_detail,key=1 value=100 1575804198345000000
insert sample_detail,key=2 value=1   1575804108345000000
insert sample_detail,key=2 value=2   1575804118345000000
insert sample_detail,key=2 value=3   1575804128345000000
insert sample_detail,key=2 value=4   1575804138345000000
insert sample_detail,key=2 value=5   1575804148345000000
insert sample_detail,key=2 value=6   1575804158345000000
insert sample_detail,key=2 value=7   1575804168345000000
insert sample_detail,key=2 value=8   1575804178345000000
insert sample_detail,key=2 value=9   1575804188345000000
insert sample_detail,key=2 value=10  1575804198345000000

以下の通り実行する事で、キー単位 かつ 1分毎に集約したデータを作成する事ができる。

> select mean(value) as "平均", max(value) as "最大", min(value) as "最小" into sample_summary from sample_detail group by time(1m), "key"
name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 6

集約結果

> select * from sample_summary
name: sample_summary
time                 key 平均     最大     最小
----                 --- ------ ------ ------
2019-12-08T11:21:00Z 1   15     20     10
2019-12-08T11:21:00Z 2   1.5    2      1
2019-12-08T11:22:00Z 1   55     80     30
2019-12-08T11:22:00Z 2   5.5    8      3
2019-12-08T11:23:00Z 1   95     100    90
2019-12-08T11:23:00Z 2   9.5    10     9

尚、Influxdb には、CONTINUOUS QUERY という定期的に実行されるクエリを登録する事ができる為、自動的に集約データを作成する事が出来る。

CREATE CONTINUOUS QUERY "cq_summary" ON "mydb1" 
    BEGIN 
        select mean(value) as "平均", max(value) as "最大", min(value) as "最小"
            into sample_summary
          from sample_detail
        group by time(1m), "key"
    END

項目(Field)毎にダウンサンプリングを行う方法 †

select mean(value) as "平均" into sample_summary2 from sample_detail group by time(1m), "key"
select max(value) as "最大" into sample_summary2 from sample_detail group by time(1m), "key"
select min(value) as "最小" into sample_summary2 from sample_detail group by time(1m), "key"

> select * from sample_summary2
name: sample_summary2
time                 key 平均     最大     最小
----                 --- ------ ------ ------
2019-12-08T11:21:00Z 1   15     20     10
2019-12-08T11:21:00Z 2   1.5    2      1
2019-12-08T11:22:00Z 1   55     80     30
2019-12-08T11:22:00Z 2   5.5    8      3
2019-12-08T11:23:00Z 1   95     100    90
2019-12-08T11:23:00Z 2   9.5    10     9

INSERT時に文字列をクォーテーションで囲む必要はない。 †

> insert sample,server=server1,ym=201912 load_average=0.5
> insert sample,server='server1',ym=201912 load_average=1.0
>
> select * from sample
name: sample
time                         load_average server    ym
----                         ------------ ------    --
2019-12-08T11:11:48.5337018Z 0.5          server1   201912
2019-12-08T11:11:57.214271Z  1            'server1' 201912

> select * from sample where server='server1'
name: sample
time                         load_average server  ym
----                         ------------ ------  --
2019-12-08T11:11:48.5337018Z 0.5          server1 201912

> select * from sample where server='\'server1\''
name: sample
time                        load_average server    ym
----                        ------------ ------    --
2019-12-08T11:11:57.214271Z 1            'server1' 201912

同じ field に別のデータ型は登録できない †

> insert sample,server=server1,ym=201912 load_average='0.5'
ERR: {"error":"unable to parse 'sample,server=server1,ym=201912 load_average='0.5'': invalid boolean"}

> insert sample,server=server1,ym=201912 load_average="0.5"
ERR: {"error":"partial write: field type conflict: input field \"load_average\" on measurement \"sample\" is type string, already exists as type float dropped=1"}

FROM句の Retention policy を省略した場合はデフォルトの Retention policy が使用される。 †

> CREATE RETENTION POLICY one_day ON mydb1 DURATION 1d REPLICATION 1
> insert into one_day sample,ym=201912 value=0.5
> insert sample,ym=201912 value=1.0

> select * from sample
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:48:21.8258805Z 1     201912
>
> select * from mydb1..sample
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:48:21.8258805Z 1     201912

> select * from mydb1.one_day.sample
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:47:50.9271556Z 0.5   201912

> select * from sample, mydb1.one_day.sample    
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:47:50.9271556Z 0.5   201912
2019-12-08T11:48:21.8258805Z 1     201912

DELETE 時に Retention policy が指定できない †

> delete from mydb1.one_day.sample
ERR: error parsing query: retention policy not supported at line 1, char 1

後からデフォルトのリテンションポリシーを変更した場合 †

データ登録後にデフォルトのリテンションポリシーを変更した場合、リテンションポリシーを指定していない select で検索されるデータが変わる。

> CREATE RETENTION POLICY one_day ON mydb1 DURATION 1d REPLICATION 1
> insert into one_day sample,ym=201912 value=0.5
> insert sample,ym=201912 value=1.0

リテンションポリシーを指定せずに検索するとデフォルトのリテンションポリシーのデータが検索される。

> select * from sample
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:48:21.8258805Z 1     201912

デフォルトのリテンションポリシーを one_day に変更する

> ALTER RETENTION POLICY one_day ON mydb1 DEFAULT
> show retention policies
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        false
one_day 24h0m0s  1h0m0s             1        true

リテンションポリシーを指定せずに検索すると変更後のデフォルトのリテンションポリシーのデータが検索される。

> select * from sample
name: sample
time                         value ym
----                         ----- --
2019-12-08T11:47:50.9271556Z 0.5   201912

measurement の複製を select into で行う場合は "group by *" が必要。 †

insert sample_org,key=1 value=1
insert sample_org,key=1 value=2
insert sample_org,key=1 value=3
insert sample_org,key=1 value=4
insert sample_org,key=1 value=5

> show series from sample_org
key
---
sample_org,key=1

> select * from sample_org
name: sample_org
time                         key value
----                         --- -----
2019-12-11T10:42:29.0181793Z 1   1
2019-12-11T10:42:29.0630682Z 1   2
2019-12-11T10:42:29.0656965Z 1   3
2019-12-11T10:42:29.067036Z  1   4
2019-12-11T10:42:29.7491858Z 1   5

> select * into sample_copy1 from sample_org
name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 5

> show series from sample_org, sample_copy1
key
---
sample_copy1
sample_org,key=1

> select * into sample_copy2 from sample_org group by *
name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 5

> show series from sample_org, sample_copy2
key
---
sample_copy2,key=1
sample_org,key=1

登録後すぐに retention policy に引っかかるようなデータは登録できない。 †

> select * from sample1
name: sample1
time                 key value
----                 --- -----
2019-12-01T22:10:10Z 1   1
2019-12-02T22:10:10Z 1   2
2019-12-03T22:10:10Z 1   3
2019-12-04T22:10:10Z 1   4
2019-12-05T22:10:10Z 1   5
2019-12-06T22:10:10Z 1   6
2019-12-07T22:10:10Z 1   7
2019-12-08T22:10:10Z 1   8
2019-12-09T22:10:10Z 1   9
2019-12-10T22:10:10Z 1   10

> /* 保持期間7日のリテンションポリシーを作成 */
> CREATE RETENTION POLICY "one_week" ON "mydb1" DURATION 7d REPLICATION 1
>
> select * into "one_week".sample_copy from sample1
ERR: partial write: points beyond retention policy dropped=3       /* 3レコード登録されなかった */

> select * from "one_week".sample_copy
name: sample_copy
time                 key value
----                 --- -----
2019-12-04T22:10:10Z 1   4
2019-12-05T22:10:10Z 1   5
2019-12-06T22:10:10Z 1   6
2019-12-07T22:10:10Z 1   7
2019-12-08T22:10:10Z 1   8
2019-12-09T22:10:10Z 1   9
2019-12-10T22:10:10Z 1   10

リテンションポリシーを削除した場合はデータも消える †

> CREATE RETENTION POLICY rp_dummy ON mydb1 DURATION 1d REPLICATION 1
> insert m_dummy,key=1 value=1
> insert m_dummy,key=2 value=2
> insert into rp_dummy m_dummy,key=3 value=3
> insert into rp_dummy m_dummy,key=4 value=4

> select * from m_dummy
name: m_dummy
time                key value
----                --- -----
1576154271123422400 1   1
1576154274313463800 2   2

> select * from rp_dummy.m_dummy
name: m_dummy
time                key value
----                --- -----
1576154289085026700 3   3
1576154293159653800 4   4

drop retention policy rp_dummy on mydb1

> select * from m_dummy
name: m_dummy
time                key value
----                --- -----
1576154271123422400 1   1
1576154274313463800 2   2

select * from rp_dummy.m_dummy
ERR: retention policy not found: rp_dummy

> show series
key
---
m_dummy,key=1
m_dummy,key=2
m_dummy,key=3
m_dummy,key=4

> drop series from "m_dummy" where "key" = '3'
> drop series from "m_dummy" where "key" = '4'
>
> show series
key
---
dummy,key=1
m_dummy,key=1
m_dummy,key=2