InfluxDb中的数据查询语法Select(一)

上节课,我们简单的介绍了一下新建数据库,查询数据库,这一节课我们将全面讲解InfluxQL语言。

InfluxQL是一种类似SQL的查询语言,用于与InfluxDB中的数据进行交互。以下部分详细介绍了InfluxQL的SELECT语句有关查询语法。


国家海洋和大气管理局示例数据库

首先需要一个数据库作为数据查询的数据,本文使用国家海洋和大气管理局(NOAA)海洋作业和服务中心的公开数据。请参阅示例数据页面下载数据,并按照以下部分中的示例查询进行跟踪。开始之后,先大致了解h2o_feet这个measurement表中的数据样本:

表名: h2o_feet

time  level description location water_level
2015-08-18T00:00:00Z    between 6 and 9 feet        coyote_creek    8.12
2015-08-18T00:00:00Z     below 3 feet         santa_monica      2.064
2015-08-18T00:06:00Z   between 6 and 9 feet       coyote_creek   8.005
2015-08-18T00:06:00Z     below 3 feet         santa_monica      2.116
2015-08-18T00:12:00Z     between 6 and 9 feet        coyote_creek     7.887
2015-08-18T00:12:00Z     below 3 feet        santa_monica      2.028

h2o_feet这个measurement中的数据以六分钟的时间间隔进行采样记录。

  • measurement具有一个tag key(location),它具有两个tag value:土狼溪coyote_creek和圣莫尼卡santa_monica。土狼溪coyote_creek和圣莫尼卡santa_monica都是美国地名。

  • measurement还有两个field:level description用字符串类型和water_level浮点型。所有这些数据都在NOAA_water_database数据库中。

上面查询出的内容很奇怪,tag和filed没有在一起,理论上tag(location)不应该在field(level description、water_level)之间的。


将数据插入InfluxDb数据库

在学习下面的知识前,请将数据插入数据库,插入数据库有多种方式,本课使用其中一种方式,请看:

首先下载 h2o_feet数据。

然后执行下面的influxdb导入语句:

wget http://66-ai.com/download/influxdb/NOAA_data.txt  -O NOAA_data.txt
influx -import -path=NOAA_data.txt -database=NOAA_water_database

执行结果:

root@3f37-0003:~# influx -import -path=NOAA_data.txt -database=NOAA_water_database
2021/01/28 15:09:05 Processed 1 commands
2021/01/28 15:09:05 Processed 76290 inserts
2021/01/28 15:09:05 Failed 0 inserts

注意,我们这里influxdb的版本是:1.8.3 ,其他版本可能会出错


基本的SELECT语句

SELECT语句从特定的measurement表中查询数据。


SELECT语法说明

SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

意为:从一个或者多个表中查询需要的数据。

  • SELECT语句需要一个SELECTFROM子句。
  • SELECT支持指定数据的几种格式:
  1. SLECT *,返回所有的field和tag。
  2. SELECT "<field_key>"返回特定的field。
  3. SELECT "<field_key>","<field_key>"返回多个field。
  4. SELECT "<field_key>","<tag_key>"返回特定的field和tag, SELECT在包括一个tag时,必须至少指定一个field
  5. SELECT "<field_key>"::field,"<tag_key>"::tag返回特定的field和tag,::[field | tag]语法指定标识符的类型。 使用此语法来区分具有相同名称的field key和tag key。

from说明

单表

FROM子句支持几种用于指定表measurement的格式: FROM <measurement_name>从单个measurement返回数据。如果使用命令行,需要先用USE指定数据库,并且使用的DEFAULT存储策略。

多表

FROM <measurement_name>,<measurement_name>

从多个measurement中返回数据。注意InfluxDb是不推荐从多张表获取数据的,因为这会很慢。

指定保留策略

FROM <database_name>.<retention_policy_name>.<measurement_name>

从一个完全指定的measurement中返回数据,这个完全指定是指指定了数据库和存储策略。

FROM <database_name>..<measurement_name>

从一个用户指定的数据库中返回存储策略为DEFAULT的数据。

引号:如果标识符包含除[A-z,0-9,_]之外的字符,如果它们以数字开头,或者如果它们是InfluxQL关键字,那么它们必须用双引号。虽然并不总是需要,我们建议您双引号标识符。


例一:从单个measurement查询所有的field和tag

$ inlfux
> SELECT * FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

该查询从h2o_feetmeasurement中选择所有field和tag。这个查询要查出70000多条数据,大约要花5秒钟。

如果您使用命令行(CLI),请确保在运行查询之前输入USE NOAA_water_database。CLI查询USE的数据库并且存储策略是DEFAULT的数据。如果使用HTTP API,请确保将db查询参数设置为NOAA_water_database。如果没有设置rp参数,则HTTP API会自动选择数据库的DEFAULT存储策略。

注意,本例是把表中的所有数据查出来,比较费时间哦。


例二:从单个measurement中查询特定tag和field

SELECT "level description","location","water_level" FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

该查询field level description,tag location和field water_leval

请注意,SELECT子句在包含tag时必须至少指定一个field,如下面的查询是不会返回数据的,因为不包含一个field。

SELECT "location" FROM "h2o_feet"

tag 和 field是比较难以理解的概念,后面我们将详细介绍


例三:从单个measurement中选择特定的tag和field,并提供其标识符类型

> SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

查询从measurement h2o_feet中选择field level description,tag location和field water_leval:: [field | tag]语法指定标识符是field还是tag。使用:: [field | tag]以区分相同的field key和tag key。

这个语法除了我们阅读的时候,方便一点,其实似乎没什么作用。不过阅读起来方便已经是最好的作用了。


例四:从单个measurement查询所有field

> SELECT *::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      water_level
2015-08-18T00:00:00Z   below 3 feet           2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   4.938

该查询从measurement h2o_feet中选择所有field。SELECT子句支持将*语法与::语法相结合。


例五:从measurement中选择一个特定的field并执行基本计算

> SELECT ("water_level" * 2) + 4 from "h2o_feet"

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   20.24
2015-08-18T00:00:00Z   8.128
[...]
2015-09-18T21:36:00Z   14.132
2015-09-18T21:42:00Z   13.876

该查询将water_level字段值乘以2,并加上4。请注意,InfluxDB遵循一般编程语言的运算符顺序。


例六:从多个measurement中查询数据

> SELECT * FROM "h2o_feet","h2o_pH"

name: h2o_feet
--------------
time                   level description      location       pH   water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938

name: h2o_pH
------------
time                   level description   location       pH   water_level
2015-08-18T00:00:00Z                       santa_monica   6
2015-08-18T00:00:00Z                       coyote_creek   7
[...]
2015-09-18T21:36:00Z                       santa_monica   8
2015-09-18T21:42:00Z                       santa_monica   7

该查询从两个measurement h2o_feeth2o_pH中查询所有的field和tag,多个measurement之间用逗号,分割。

查询结构会按顺序显示出来。


例七:从完全限定的measurement中选择所有数据

> SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

该查询选择数据库NOAA_water_database中的数据,autogen为存储策略,h2o_feet为measurement。存储策略后面在介绍。

在命令行中,可以直接这样来代替USE指定数据库,以及指定DEFAULT之外的存储策略。 在HTTP API中,如果需要完全限定使用dbrp参数来指定。


例八:从特定数据库中查询measurement的所有数据

> SELECT * FROM "NOAA_water_database".."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

该查询选择数据库NOAA_water_database中的数据,DEFAULT为存储策略和h2o_feet为measurement。 ..表示指定数据库的默认(DEFAULT)存储策略。


SELECT语句中常见的问题


问题一:在SELECT语句中查询tag key

一个查询在SELECT子句中至少需要一个field key来返回数据。如果SELECT子句仅包含单个tag key或多个tag key,则查询返回一个空的结果。

例如:

下面的查询不会返回结果,因为在SELECT子句中只指定了一个tag key(location):

SELECT "location" FROM "h2o_feet"

要想有任何有关tag key为location的数据,SELECT子句中必须至少有一个field(water_level):

> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3
name: h2o_feet
time                   water_level  location
----                   -----------  --------
2015-08-18T00:00:00Z   8.12         coyote_creek
2015-08-18T00:00:00Z   2.064        santa_monica
[...]
2015-09-18T21:36:00Z   5.066        santa_monica
2015-09-18T21:42:00Z   4.938        santa_monica

带`fill(previous)`:

SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(previous)


name: h2o_feet

time max 2015-09-18T16:00:00Z 3.599 2015-09-18T16:12:00Z 3.402 2015-09-18T16:24:00Z 3.235 2015-09-18T16:36:00Z 3.235


##### `fill()`的问题 ##### 问题一:`fill()`当没有数据在查询时间范围内时 目前,如果查询的时间范围内没有任何数据,查询会忽略`fill()`。 这是预期的行为。GitHub上的一个开放[feature request](https://github.com/influxdata/influxdb/issues/6967)建议,即使查询的时间范围不包含数据,`fill()`也会强制返回值。 例子: 以下查询不返回数据,因为`water_level`在查询的时间范围内没有任何点。 请注意,`fill(800)`对查询结果没有影响。

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'coyote_creek' AND time >= '2015-09-18T22:00:00Z' AND time <= '2015-09-18T22:18:00Z' GROUP BY time(12m) fill(800)

``bash ##### 问题二:fill(previous)当前一个结果超出查询时间范围 当前一个结果超出查询时间范围,fill(previous)`不会填充这个时间间隔。

例子:

以下查询涵盖2015-09-18T16:24:00Z2015-09-18T16:54:00Z之间的时间范围。 请注意,fill(previos)2015-09-18T16:24:00Z的结果填写到了2015-09-18T16:36:00Z中。

> SELECT MAX("water_level") FROM "h2o_feet" WHERE location = 'coyote_creek' AND time >= '2015-09-18T16:24:00Z' AND time <= '2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous)

name: h2o_feet
--------------
time                   max
2015-09-18T16:24:00Z   3.235
2015-09-18T16:36:00Z   3.235
2015-09-18T16:48:00Z   4

下一个查询会缩短上一个查询的时间范围。 它现在涵盖2015-09-18T16:36:00Z2015-09-18T16:54:00Z之间的时间。请注意,fill(previos)不会用2015-09-18T16:24:00Z的结果填写到2015-09-18T16:36:00Z中。因为2015-09-18T16:24:00Z的结果在查询的较短时间范围之外。

> SELECT MAX("water_level") FROM "h2o_feet" WHERE location = 'coyote_creek' AND time >= '2015-09-18T16:36:00Z' AND time <= '2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous)

name: h2o_feet
--------------
time                   max
2015-09-18T16:36:00Z
2015-09-18T16:48:00Z   4

问题三:fill(linear)当前一个结果超出查询时间范围

当前一个结果超出查询时间范围,fill(linear)不会填充这个时间间隔。

例子:

以下查询涵盖2016-11-11T21:24:00Z2016-11-11T22:06:00Z之间的时间范围。请注意,fill(linear)使用2016-11-11T21:24:00Z2016-11-11T22:00:00Z时间间隔的值,填充到2016-11-11T21:36:00Z2016-11-11T21:48:00Z时间间隔中。

> SELECT MEAN("tadpoles") FROM "pond" WHERE time > '2016-11-11T21:24:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)

name: pond
time                   mean
----                   ----
2016-11-11T21:24:00Z   3
2016-11-11T21:36:00Z   4
2016-11-11T21:48:00Z   5
2016-11-11T22:00:00Z   6

下一个查询会缩短上一个查询的时间范围。 它现在涵盖2016-11-11T21:36:00Z2016-11-11T22:06:00Z之间的时间。请注意,fill()不会使用2016-11-11T21:24:00Z2016-11-11T22:00:00Z时间间隔的值,填充到2016-11-11T21:36:00Z2016-11-11T21:48:00Z时间间隔中。因为2015-09-18T16:24:00Z的结果在查询的较短时间范围之外。

> SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:36:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)
name: pond
time                   mean
----                   ----
2016-11-11T21:36:00Z
2016-11-11T21:48:00Z
2016-11-11T22:00:00Z   6

INTO子句

INTO子句将查询的结果写入到用户自定义的measurement中。


语法

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

语法描述

INTO支持多种格式的measurement。

INTO <measurement_name>

写入到特定measurement中,用CLI时,写入到用USE指定的数据库,保留策略为DEFAULT,用HTTP API时,写入到db参数指定的数据库,保留策略为DEFAULT

INTO <database_name>.<retention_policy_name>.<measurement_name>

写入到完整指定的measurement中。

INTO <database_name>..<measurement_name>

写入到指定数据库保留策略为DEFAULT

INTO <database_name>.<retention_policy_name>.:MEASUREMENT FROM /<regular_expression>/

将数据写入与FROM子句中正则表达式匹配的用户指定数据库和保留策略的所有measurement。 :MEASUREMENT是对FROM子句中匹配的每个measurement的反向引用。


例子


例一:重命名数据库

> SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *

name: result
time written
---- -------
0    76290

在InfluxDB中直接重命名数据库是不可能的,因此INTO子句的常见用途是将数据从一个数据库移动到另一个数据库。 上述查询将NOAA_water_databaseautogen保留策略中的所有数据写入copy_NOAA_water_database数据库和autogen保留策略中。

反向引用语法(:MEASUREMENT)维护目标数据库中的源measurement名称。 请注意,在运行INTO查询之前,copy_NOAA_water_database数据库及其autogen保留策略都必须存在。

GROUP BY *子句将源数据库中的tag留在目标数据库中的tag中。以下查询不为tag维护series的上下文;tag将作为field存储在目标数据库(copy_NOAA_water_database)中:

SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/

当移动大量数据时,我们建议在WHERE子句中顺序运行不同measurement的INTO查询并使用时间边界。这样可以防止系统内存不足。下面的代码块提供了这些查询的示例语法:

SELECT * 
INTO <destination_database>.<retention_policy_name>.<measurement_name> 
FROM <source_database>.<retention_policy_name>.<measurement_name>
WHERE time > now() - 100w and time < now() - 90w GROUP BY *

SELECT * 
INTO <destination_database>.<retention_policy_name>.<measurement_name> 
FROM <source_database>.<retention_policy_name>.<measurement_name>} 
WHERE time > now() - 90w  and time < now() - 80w GROUP BY *

SELECT * 
INTO <destination_database>.<retention_policy_name>.<measurement_name> 
FROM <source_database>.<retention_policy_name>.<measurement_name>
WHERE time > now() - 80w  and time < now() - 70w GROUP BY *

例二:将查询结果写入到一个measurement

> SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'

name: result
------------
time                   written
1970-01-01T00:00:00Z   7604

> SELECT * FROM "h2o_feet_copy_1"

name: h2o_feet_copy_1
---------------------
time                   water_level
2015-08-18T00:00:00Z   8.12
[...]
2015-09-18T16:48:00Z   4

该查询将其结果写入新的measurement:h2o_feet_copy_1。如果使用CLI,InfluxDB会将数据写入USEd数据库和DEFAULT保留策略。 如果您使用HTTP API,InfluxDB会将数据写入参数db指定的数据库和rp指定的保留策略。如果您没有设置rp参数,HTTP API将自动将数据写入数据库的DEFAULT保留策略。

响应显示InfluxDB写入h2o_feet_copy_1的点数(7605)。 响应中的时间戳是无意义的; InfluxDB使用epoch 0(1970-01-01T00:00:00Z)作为空时间戳等价物。


例三:将查询结果写入到一个完全指定的measurement中

> SELECT "water_level" INTO "where_else"."autogen"."h2o_feet_copy_2" FROM "h2o_feet" WHERE "location" = 'coyote_creek'

name: result
------------
time                   written
1970-01-01T00:00:00Z   7604

> SELECT * FROM "where_else"."autogen"."h2o_feet_copy_2"

name: h2o_feet_copy_2
---------------------
time                   water_level
2015-08-18T00:00:00Z   8.12
[...]
2015-09-18T16:48:00Z   4

例四:将聚合结果写入到一个measurement中(采样)

> SELECT MEAN("water_level") INTO "all_my_averages" FROM "h2o_feet" WHERE "location" = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)

name: result
------------
time                   written
1970-01-01T00:00:00Z   3

> SELECT * FROM "all_my_averages"

name: all_my_averages
---------------------
time                   mean
2015-08-18T00:00:00Z   8.0625
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:24:00Z   7.5675

查询使用InfluxQL函数和GROUP BY time()子句聚合数据。它也将其结果写入all_my_averagesmeasurement。

该查询是采样的示例:采用更高精度的数据,将这些数据聚合到较低的精度,并将较低精度数据存储在数据库中。 采样是INTO子句的常见用例。


例五:将多个measurement的聚合结果写入到一个不同的数据库中(逆向引用采样)

> SELECT MEAN(*) INTO "where_else"."autogen".:MEASUREMENT FROM /.*/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:06:00Z' GROUP BY time(12m)

name: result
time                   written
----                   -------
1970-01-01T00:00:00Z   5

> SELECT * FROM "where_else"."autogen"./.*/

name: average_temperature
time                   mean_degrees   mean_index   mean_pH   mean_water_level
----                   ------------   ----------   -------   ----------------
2015-08-18T00:00:00Z   78.5

name: h2o_feet
time                   mean_degrees   mean_index   mean_pH   mean_water_level
----                   ------------   ----------   -------   ----------------
2015-08-18T00:00:00Z                                         5.07625

name: h2o_pH
time                   mean_degrees   mean_index   mean_pH   mean_water_level
----                   ------------   ----------   -------   ----------------
2015-08-18T00:00:00Z                               6.75

name: h2o_quality
time                   mean_degrees   mean_index   mean_pH   mean_water_level
----                   ------------   ----------   -------   ----------------
2015-08-18T00:00:00Z                  51.75

name: h2o_temperature
time                   mean_degrees   mean_index   mean_pH   mean_water_level
----                   ------------   ----------   -------   ----------------
2015-08-18T00:00:00Z   63.75

查询使用InfluxQL函数和GROUP BY time()子句聚合数据。它会在与FROM子句中的正则表达式匹配的每个measurement中聚合数据,并将结果写入where_else数据库和autogen保留策略中具有相同名称的measurement中。请注意,在运行INTO查询之前,where_elseautogen都必须存在。

该查询是使用反向引用进行下采样的示例。它从多个measurement中获取更高精度的数据,将这些数据聚合到较低的精度,并将较低精度数据存储在数据库中。使用反向引用进行下采样是INTO子句的常见用例。