表结构

openGemini大约 10 分钟约 2965 字

本章主要包括如下内容

SHOW TAG KEYS

查看表中所有TAG字段

语法

SHOW TAG KEYS [ON <database_name>] [FROM_clause] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

如果查询中没有包含ON <database_name>,您必须在CLI中使用USE <database_name>指定数据库,或者在openGemini API请求中使用参数db指定数据库。

示例

  • 运行带有ON子句的SHOW TAG KEYS查询
> SHOW TAG KEYS ON "NOAA_water_database"
name: average_temperature
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_feet
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_pH
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_quality
+----------+
| tagKey   |
+----------+
| location |
| randtag  |
+----------+
1 columns, 2 rows in set

name: h2o_temperature
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

该查询返回数据库NOAA_water_database中的所有表的tag key。

  • 运行不带有ON子句的SHOW TAG KEYS查询

使用USE <database_name>指定数据库:

> use NOAA_water_database
Elapsed: 452ns
> SHOW TAG KEYS
name: average_temperature
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_feet
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_pH
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

name: h2o_quality
+----------+
| tagKey   |
+----------+
| location |
| randtag  |
+----------+
1 columns, 2 rows in set

name: h2o_temperature
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set
  • 运行带有多个子句的SHOW TAG KEYS查询
> SHOW TAG KEYS ON "NOAA_water_database" FROM "h2o_quality" LIMIT 1 OFFSET 1
name: h2o_quality
+---------+
| tagKey  |
+---------+
| randtag |
+---------+
1 columns, 1 rows in set

该查询返回数据库NOAA_water_database中名为h2o_quality的measurement里的tag key。LIMIT子句将返回的tag key的个数限制为1,OFFSET子句将输出结果偏移一个。

  • 查看TAG的统计数量

在某些场景下,仅需要了解TAG数量,不关心具体的TAG,可以使用SHOW TAG KEY CARDINALITY命令,使用方式如下:

SHOW TAG KEY CARDINALITY [ON <database_name>] [FROM_CLAUSE]

例如:

> SHOW TAG KEY CARDINALITY
name: average_temperature
+-------+
| count |
+-------+
| 1     |
+-------+
1 columns, 1 rows in set

name: h2o_feet
+-------+
| count |
+-------+
| 1     |
+-------+
1 columns, 1 rows in set

name: h2o_pH
+-------+
| count |
+-------+
| 1     |
+-------+
1 columns, 1 rows in set

name: h2o_quality
+-------+
| count |
+-------+
| 2     |
+-------+
1 columns, 1 rows in set

name: h2o_temperature
+-------+
| count |
+-------+
| 1     |
+-------+
1 columns, 1 rows in set

> SHOW TAG KEY CARDINALITY FROM h2o_temperature
name: h2o_temperature
+-------+
| count |
+-------+
| 1     |
+-------+
1 columns, 1 rows in set
  • 运行带条件的SHOW TAG KEYS查询
> SHOW TAG KEYS FROM h2o_quality WHERE location=coyote_creek
name: h2o_quality
+----------+
| tagKey   |
+----------+
| location |
| randtag  |
+----------+
1 columns, 2 rows in set

> SHOW TAG KEYS FROM h2o_quality WHERE location=coyote_creek LIMIT 1
name: h2o_quality
+----------+
| tagKey   |
+----------+
| location |
+----------+
1 columns, 1 rows in set

SHOW TAG VALUES

返回数据库中指定tag key的tag value。

语法

SHOW TAG VALUES [ON <database_name>] [FROM_CLAUSE] WITH KEY [ [<operator> "<tag_key>" | <regular_expression>] | [IN ("<tag_key1>","<tag_key2")]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_CLAUSE] [OFFSET_CLAUSE]

ON <database_name>是可选的。如果查询中没有包含ON <database_name>,您必须在CLI中使用USE <database_name>指定数据库,或者在HTTP API请求中使用参数db指定数据库。

WITH子句是必须要有的,它支持指定一个tag key、一个正则表达式或多个tag key。

FROM子句、WHERE子句、LIMIT子句和OFFSET子句是可选的。WHERE子句支持tag比较;在SHOW TAG VALUES查询中,field比较是无效的。

WITH子句和WHERE子句中支持的操作符:

操作符含义
=等于
<>不等于
!=不等于
=~匹配
!~不匹配

请查阅DML章节获得关于FROM子句LIMIT、OFFSET子句、和正则表达式的介绍。

示例

  • 运行带有ON子句的SHOW TAG VALUES查询
> SHOW TAG VALUES ON "NOAA_water_database" WITH KEY = "randtag"

name: h2o_quality
key       value
---       -----
randtag   1
randtag   2
randtag   3

该查询返回数据库NOAA_water_database中的tag key randtag的所有tag value。SHOW TAG VALUES将查询结果按measurement的名字进行分组。

  • 运行不带有ON子句的SHOW TAG KEYS查询

使用USE <database_name>指定数据库:

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW TAG VALUES WITH KEY = "randtag"

name: h2o_quality
key       value
---       -----
randtag   1
randtag   2
randtag   3
  • 运行带有多个子句的SHOW TAG VALUES查询
> SHOW TAG VALUES ON "NOAA_water_database" WITH KEY IN ("location","randtag") WHERE "randtag" =~ /./ LIMIT 3

name: h2o_quality
key        value
---        -----
location   coyote_creek
location   santa_monica
randtag	   1

该查询从数据库NOAA_water_database的所有measurement中返回locationrandtag的tag value,并且返回的数据还需满足条件:randtag的tag value不为空。LIMIT子句将返回的tag value的个数限制为3。

SHOW FIELD KEYS

返回field key和field value的数据类型。

语法

SHOW FIELD KEYS [ON <database_name>] [FROM <measurement_name>]

ON <database_name>是可选的
FROM子句是可选的。请参考FROM子句的介绍。

如果查询中没有包含ON <database_name>,您必须在CLI中使用USE <database_name>指定数据库,或者在openGemini API请求中使用参数db指定数据库。

示例

  • 运行带有ON子句的SHOW FIELD KEYS查询
> SHOW FIELD KEYS ON "NOAA_water_database"

name: average_temperature
fieldKey            fieldType
--------            ---------
degrees             float

name: h2o_feet
fieldKey            fieldType
--------            ---------
level description   string
water_level         float

name: h2o_pH
fieldKey            fieldType
--------            ---------
pH                  float

name: h2o_quality
fieldKey            fieldType
--------            ---------
index               float

name: h2o_temperature
fieldKey            fieldType
--------            ---------
degrees             float

该查询返回数据库NOAA_water_database中每个measurement的field key以及对应的field value的数据类型。

  • 运行不带有ON子句的SHOW FIELD KEYS查询

使用USE <database_name>指定数据库:

> USE NOAA_water_database
Using database NOAA_water_database

> SHOW FIELD KEYS

name: average_temperature
fieldKey            fieldType
--------            ---------
degrees             float

name: h2o_feet
fieldKey            fieldType
--------            ---------
level description   string
water_level         float

name: h2o_pH
fieldKey            fieldType
--------            ---------
pH                  float

name: h2o_quality
fieldKey            fieldType
--------            ---------
index               float

name: h2o_temperature
fieldKey            fieldType
--------            ---------
degrees             float
  • 运行带有FROM子句的SHOW FIELD KEYS查询
> SHOW FIELD KEYS ON "NOAA_water_database" FROM "h2o_feet"

name: h2o_feet
fieldKey            fieldType
--------            ---------
level description   string
water_level         float

该查询返回数据库NOAA_water_database中measurement h2o_feet里的fields key以及对应的field value的数据类型。

SHOW SERIES

返回指定数据库的时间线。

语法

SHOW SERIES [ON <database_name>] [FROM_CLAUSE] [WHERE <tag_key> <operator> [ '<tag_value>' | <regular_expression>]] [LIMIT_CLAUSE] [OFFSET_CLAUSE]

ON <database_name>是可选的。如果查询中没有包含ON <database_name>,您必须在CLI中使用USE <database_name>指定数据库,或者在openGemini API请求中使用参数db指定数据库。

WHERE子句支持tag比较;在SHOW SERIES查询中,field比较是无效的。

WHERE子句中支持的操作符:

操作符含义
=等于
<>不等于
!=不等于
=~匹配
!~不匹配

参考FROM子句LIMIT、OFFSET子句、和正则表达式的介绍。

示例

  • 运行带有ON子句的SHOW SERIES查询
>>> SHOW SERIES ON NOAA_water_database
+---------------------------------------------+
| key                                         |
+---------------------------------------------+
| average_temperature,location=coyote_creek   |
| average_temperature,location=santa_monica   |
| h2o_feet,location=coyote_creek              |
| h2o_feet,location=santa_monica              |
| h2o_pH,location=coyote_creek                |
| h2o_pH,location=santa_monica                |
| h2o_quality,location=coyote_creek,randtag=1 |
| h2o_quality,location=coyote_creek,randtag=2 |
| h2o_quality,location=coyote_creek,randtag=3 |
| h2o_quality,location=santa_monica,randtag=1 |
| h2o_quality,location=santa_monica,randtag=2 |
| h2o_quality,location=santa_monica,randtag=3 |
| h2o_temperature,location=coyote_creek       |
| h2o_temperature,location=santa_monica       |
+---------------------------------------------+
1 columns, 14 rows in set

该查询的输出类似行协议格式。第一个逗号之前的所有内容是measurement的名字。第一个逗号之后的所有内容都是tag key或者tag value。数据库NOAA_water_database有五个不同的measurement和14个不同的系列。

  • 运行不带有ON子句的SHOW SERIES查询

使用USE <database_name>指定数据库:

> USE NOAA_water_database
Elapsed: 561ns
> SHOW SERIES
+---------------------------------------------+
| key                                         |
+---------------------------------------------+
| average_temperature,location=coyote_creek   |
| average_temperature,location=santa_monica   |
| h2o_feet,location=coyote_creek              |
| h2o_feet,location=santa_monica              |
| h2o_pH,location=coyote_creek                |
| h2o_pH,location=santa_monica                |
| h2o_quality,location=coyote_creek,randtag=1 |
| h2o_quality,location=coyote_creek,randtag=2 |
| h2o_quality,location=coyote_creek,randtag=3 |
| h2o_quality,location=santa_monica,randtag=1 |
| h2o_quality,location=santa_monica,randtag=2 |
| h2o_quality,location=santa_monica,randtag=3 |
| h2o_temperature,location=coyote_creek       |
| h2o_temperature,location=santa_monica       |
+---------------------------------------------+
1 columns, 14 rows in set
  • 运行带有多个子句的SHOW SERIES查询
> SHOW SERIES ON NOAA_water_database FROM "h2o_quality" WHERE "location" = 'coyote_creek' LIMIT 2
+---------------------------------------------+
| key                                         |
+---------------------------------------------+
| h2o_quality,location=coyote_creek,randtag=1 |
| h2o_quality,location=coyote_creek,randtag=2 |
+---------------------------------------------+
1 columns, 2 rows in set

该查询返回数据库NOAA_water_database中,与measurement h2o_quality和tag location = coyote_creek相关联的所有系列。LIMIT子句将返回的系列个数限制为2。

警告

SHOW SERIES返回表中所有时间线,对内存资源占用较大,慎用!

如需使用,一定要使用条件过滤

SHOW SERIES CARDINALITY

返回指定数据库的时间线数量,结果按时间分组

SHOW SERIES CARDINALITY [ON <database_name>] [FROM_clause]

示例

> SHOW SERIES CARDINALITY ON NOAA_water_database
+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-12T00:00:00Z | 2019-08-19T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-19T00:00:00Z | 2019-08-26T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-26T00:00:00Z | 2019-09-02T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-02T00:00:00Z | 2019-09-09T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-09T00:00:00Z | 2019-09-16T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-16T00:00:00Z | 2019-09-23T00:00:00Z | 14    |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

> SHOW SERIES CARDINALITY ON NOAA_water_database FROM h2o_quality
+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-12T00:00:00Z | 2019-08-19T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-19T00:00:00Z | 2019-08-26T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-08-26T00:00:00Z | 2019-09-02T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-02T00:00:00Z | 2019-09-09T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-09T00:00:00Z | 2019-09-16T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

+----------------------+----------------------+-------+
| startTime            | endTime              | count |
+----------------------+----------------------+-------+
| 2019-09-16T00:00:00Z | 2019-09-23T00:00:00Z | 6     |
+----------------------+----------------------+-------+
3 columns, 1 rows in set

SHOW SHARDS

返回指定数据库的分片信息

示例

> SHOW SHARDS
name: NOAA_water_database
+----+---------------------+------------------+-------------+----------------------+----------------------+----------------------+--------+------+------------------+
| id | database            | retention_policy | shard_group | start_time           | end_time             | expiry_time          | owners | tier | downSample_level |
+----+---------------------+------------------+-------------+----------------------+----------------------+----------------------+--------+------+------------------+
| 19 | NOAA_water_database | autogen          | 19          | 2019-08-12T00:00:00Z | 2019-08-19T00:00:00Z | 2019-08-19T00:00:00Z | 2      | warm | 0                |
| 20 | NOAA_water_database | autogen          | 20          | 2019-08-19T00:00:00Z | 2019-08-26T00:00:00Z | 2019-08-26T00:00:00Z | 2      | warm | 0                |
| 21 | NOAA_water_database | autogen          | 21          | 2019-08-26T00:00:00Z | 2019-09-02T00:00:00Z | 2019-09-02T00:00:00Z | 2      | warm | 0                |
| 18 | NOAA_water_database | autogen          | 18          | 2019-09-02T00:00:00Z | 2019-09-09T00:00:00Z | 2019-09-09T00:00:00Z | 2      | warm | 0                |
| 22 | NOAA_water_database | autogen          | 22          | 2019-09-09T00:00:00Z | 2019-09-16T00:00:00Z | 2019-09-16T00:00:00Z | 2      | warm | 0                |
| 23 | NOAA_water_database | autogen          | 23          | 2019-09-16T00:00:00Z | 2019-09-23T00:00:00Z | 2019-09-23T00:00:00Z | 2      | warm | 0                |
+----+---------------------+------------------+-------------+----------------------+----------------------+----------------------+--------+------+------------------+
10 columns, 6 rows in set

每一条数据表示一个数据库的分片信息,每个分片包含所使用的数据保留策略、分片起始时间等。相关阅读 数据保留策略

SHOW SHARD GROUPS

返回指定数据库的分片组信息

示例

> SHOW SHARD GROUPS
name: shard groups
+----+---------------------+------------------+----------------------+----------------------+----------------------+
| id | database            | retention_policy | start_time           | end_time             | expiry_time          |
+----+---------------------+------------------+----------------------+----------------------+----------------------+
| 19 | NOAA_water_database | autogen          | 2019-08-12T00:00:00Z | 2019-08-19T00:00:00Z | 2019-08-19T00:00:00Z |
| 20 | NOAA_water_database | autogen          | 2019-08-19T00:00:00Z | 2019-08-26T00:00:00Z | 2019-08-26T00:00:00Z |
| 21 | NOAA_water_database | autogen          | 2019-08-26T00:00:00Z | 2019-09-02T00:00:00Z | 2019-09-02T00:00:00Z |
| 18 | NOAA_water_database | autogen          | 2019-09-02T00:00:00Z | 2019-09-09T00:00:00Z | 2019-09-09T00:00:00Z |
| 22 | NOAA_water_database | autogen          | 2019-09-09T00:00:00Z | 2019-09-16T00:00:00Z | 2019-09-16T00:00:00Z |
| 23 | NOAA_water_database | autogen          | 2019-09-16T00:00:00Z | 2019-09-23T00:00:00Z | 2019-09-23T00:00:00Z |
+----+---------------------+------------------+----------------------+----------------------+----------------------+
6 columns, 6 rows in set

这里有6个分片组,结合SHOW SHARDS命令可以看出,每个分片组包含一个分片(SHARD)。openGemini单机默认初始化一个分片(SHARD),如果是三节点的集群,则初始化为三个分片(每个节点一个)。当一个SHARD GROUP到期后,系统会创建新的SHARD GROUP,并分配新的分片 (SHARD)。相关阅读SHARD GROUP DURATION