This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

SQL 语法

1: 数据模型

1.1: 数据类型
1.2: 特殊字段

2: 标识符
3: 表结构操作

3.1: ALTER TABLE
3.2: DROP TABLE
3.3: 创建表

4: 数据操作

4.1: INSERT
4.2: SELECT

5: 常用 SQL
6: 配置项
7: 标量函数
8: 聚合函数

本章介绍 HoraeDB 的 SQL 使用语法。

1 - 数据模型

本章介绍 HoraeDB 的数据模型。

1.1 - 数据类型

HoraeDB 实现了 Table 模型，支持的数据类型和 MySQL 比较类似。下列表格列出了 HoraeDB 的数据类型和 MySQL 的数据类型的对应关系。

支持的数据类型 (大小写不敏感)

SQL	HoraeDB
null	Null
timestamp	Timestamp
double	Double
float	Float
string	String
Varbinary	Varbinary
uint64	UInt64
uint32	UInt32
uint16	UInt16
uint8	UInt8
int64/bigint	Int64
int32/int	Int32
int16/smallint	Int16
int8/tinyint	Int8
boolean	Boolean
date	Date
time	Time

1.2 - 特殊字段

HoraeDB 的表的约束如下：

必须有主键
主键必须包含时间列，并且只能包含一个时间列
主键不可为空，并且主键的组成字段也不可为空

Timestamp 列

HoraeDB 的表必须包含一个时间戳列，对应时序数据中的时间，例如 OpenTSDB/Prometheus 的 timestamp。时间戳列通过关键字 timestamp key 设置，例如 TIMESTAMP KEY(ts)。

Tag 列

Tag 关键字定义了一个字段作为标签列，和时序数据中的 tag 类似，例如 OpenTSDB 的 tag 或 Prometheus 的 label。

主键

主键用于数据去重和排序，由一些列和一个时间列组成。主键可以通过以下一些方式设置：

使用 primary key 关键字
使用 tag 来自动生成 TSID，HoraeDB 默认将使用 (TSID,timestamp) 作为主键。
只设置时间戳列，HoraeDB 将使用 (timestamp) 作为主键。

注意：如果同时指定了主键和 Tag 列，那么 Tag 列只是一个额外的信息标识，不会影响主键生成逻辑。

 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
CREATE TABLE with_primary_key(
  ts TIMESTAMP NOT NULL,
  c1 STRING NOT NULL,
  c2 STRING NULL,
  c4 STRING NULL,
  c5 STRING NULL,
  TIMESTAMP KEY(ts),
  PRIMARY KEY(c1, ts)
) ENGINE=Analytic WITH (ttl='7d');

CREATE TABLE with_tag(
    ts TIMESTAMP NOT NULL,
    c1 STRING TAG NOT NULL,
    c2 STRING TAG NULL,
    c3 STRING TAG NULL,
    c4 DOUBLE NULL,
    c5 STRING NULL,
    c6 STRING NULL,
    TIMESTAMP KEY(ts)
) ENGINE=Analytic WITH (ttl='7d');

CREATE TABLE with_timestamp(
    ts TIMESTAMP NOT NULL,
    c1 STRING NOT NULL,
    c2 STRING NULL,
    c3 STRING NULL,
    c4 DOUBLE NULL,
    c5 STRING NULL,
    c6 STRING NULL,
    TIMESTAMP KEY(ts)
) ENGINE=Analytic WITH (ttl='7d');

TSID

如果建表时没有设置主键，并且提供了 Tag 列，HoraeDB 会自动生成一个 TSID 列和时间戳列作为主键。TSID 由所有 Tag 列的 hash 值生成，本质上这是一种自动生成 ID 的机制。

2 - 标识符

HoraeDB 中表名、列名等标识符不能是保留关键字或以数字和标点符号开始，不过 HoraeDB 允许用反引号引用标识符（`）。在这种情况下，它可以是任何字符串，如 00_table 或 select。

3 - 表结构操作

本章介绍表结构相关 SQL 语句：

3.1 - ALTER TABLE

使用 ALTER TABLE 可以改变表的结构和参数 .

变更表结构

例如可以使用 ADD COLUMN 增加表的列 :

1
2
3
-- create a table and add a column to it
CREATE TABLE `t`(a int, t timestamp NOT NULL, TIMESTAMP KEY(t)) ENGINE = Analytic;
ALTER TABLE `t` ADD COLUMN (b string);

变更后的表结构如下：

-- DESCRIBE TABLE `t`;

name    type        is_primary  is_nullable is_tag

t       timestamp   true        false       false
tsid    uint64      true        false       false
a       int         false       true        false
b       string      false       true        false

变更表参数

例如可以使用 MODIFY SETTING 修改表的参数 :

1
2
3
-- create a table and add a column to it
CREATE TABLE `t`(a int, t timestamp NOT NULL, TIMESTAMP KEY(t)) ENGINE = Analytic;
ALTER TABLE `t` MODIFY SETTING write_buffer_size='300M';

上面的 SQL 用来更改 writer_buffer 大小，变更后的建表如下：

1
CREATE TABLE `t` (`tsid` uint64 NOT NULL, `t` timestamp NOT NULL, `a` int, PRIMARY KEY(tsid,t), TIMESTAMP KEY(t)) ENGINE=Analytic WITH(arena_block_size='2097152', compaction_strategy='default', compression='ZSTD', enable_ttl='true', num_rows_per_row_group='8192', segment_duration='', storage_format='AUTO', ttl='7d', update_mode='OVERWRITE', write_buffer_size='314572800')

除此之外，我们可以修改其 ttl 为 10 天：

1
ALTER TABLE `t` MODIFY SETTING ttl='10d';

3.2 - DROP TABLE

基础语法

删除表的基础语法如下:

1
DROP TABLE [IF EXISTS] table_name

Drop Table 用来删除一个表，请谨慎使用这个语句，因为会同时删除表的定义和表的数据，并且无法恢复。

3.3 - 创建表

基础语法

建表的基础语法如下 ( [] 之间的内容是可选部分):

1
2
3
4
CREATE TABLE [IF NOT EXISTS]
    table_name ( column_definitions )
    ENGINE = engine_type
    [WITH ( table_options )];

列定义的语法 :

1
column_name column_type [[NOT] NULL] {[TAG] | [TIMESTAMP KEY] | [PRIMARY KEY]} [DICTIONARY] [COMMENT '']

表选项的语法是键-值对，值用单引号（'）来引用。例如：

1
... WITH ( enable_ttl='false' )

IF NOT EXISTS

添加 IF NOT EXISTS 时，HoraeDB 在表名已经存在时会忽略建表错误。

定义列

一个列的定义至少应该包含名称和类型部分，支持的类型见这里。

列默认为可空，即 “NULL " 关键字是隐含的；添加 NOT NULL 时列不可为空。

1
2
3
4
5
6
7
-- this definition
a_nullable int
-- equals to
a_nullable int NULL

-- add NOT NULL to make it required
b_not_null NOT NULL

定义列时可以使用相关的关键字将列标记为特殊列。

对于 string 的 tag 列，推荐设置为字典类型来减少内存占用：

1
`tag1` string TAG DICTIONARY

引擎设置

HoraeDB 支持指定某个表使用哪种引擎，目前支持的引擎类型为 Analytic。注意这个属性设置后不可更改。

分区设置

仅适用于集群部署模式

CREATE TABLE ... PARTITION BY KEY

下面这个例子创建了一个具有 8 个分区的表，分区键为 name：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE `demo` (
    `name` string TAG COMMENT 'client username',
    `value` double NOT NULL,
    `t` timestamp NOT NULL,
    timestamp KEY (t)
)
    PARTITION BY KEY(name) PARTITIONS 8
    ENGINE=Analytic
    with (
    enable_ttl='false'
)

4 - 数据操作

本章介绍数据操作相关的 SQL.

4.1 - INSERT

基础语法

写入数据的基础语法如下：

1
2
3
INSERT [INTO] tbl_name
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }

写入一行数据的示例如下：

1
INSERT INTO demo(`timestamp`, tag1) VALUES(1667374200022, 'horaedb')

4.2 - SELECT

基础语法

数据查询的基础语法如下：

1
2
3
4
5
6
7
SELECT select_expr [, select_expr] ...
    FROM table_name
    [WHERE where_condition]
    [GROUP BY {col_name | expr} ... ]
    [ORDER BY {col_name | expr}
    [ASC | DESC]
    [LIMIT [offset,] row_count ]

数据查询的语法和 mysql 类似，示例如下：

1
SELECT * FROM `demo` WHERE time_stamp > '2022-10-11 00:00:00' AND time_stamp < '2022-10-12 00:00:00' LIMIT 10

5 - 常用 SQL

HoraeDB 中有许多实用的 SQL 工具，可以辅助表操作或查询检查。

查看建表语句

1
SHOW CREATE TABLE table_name;

SHOW CREATE TABLE 返回指定表的当前版本的创建语句，包括列定义、表引擎和参数选项等。例如：

 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
-- create one table
CREATE TABLE `t` (a bigint, b int default 3, c string default 'x', d smallint null, t timestamp NOT NULL, TIMESTAMP KEY(t)) ENGINE = Analytic;
-- Result: affected_rows: 0

-- show how one table should be created.
SHOW CREATE TABLE `t`;

-- Result DDL:
CREATE TABLE `t` (
    `t` timestamp NOT NULL,
    `tsid` uint64 NOT NULL,
    `a` bigint,
    `b` int,
    `c` string,
    `d` smallint,
    PRIMARY KEY(t,tsid),
    TIMESTAMP KEY(t)
) ENGINE=Analytic WITH (
    arena_block_size='2097152',
    compaction_strategy='default',
    compression='ZSTD',
    enable_ttl='true',
    num_rows_per_row_group='8192',
    segment_duration='',
    ttl='7d',
    update_mode='OVERWRITE',
    write_buffer_size='33554432'
)

查看表信息

1
DESCRIBE table_name;

DESCRIBE 语句返回一个表的详细结构信息，包括每个字段的名称和类型，字段是否为 Tag 或主键，字段是否可空等。此外，自动生成的字段 tsid 也会展示在结果里。

例如：

1
2
3
CREATE TABLE `t`(a int, b string, t timestamp NOT NULL, TIMESTAMP KEY(t)) ENGINE = Analytic;

DESCRIBE TABLE `t`;

返回结果如下：

name    type        is_primary  is_nullable is_tag

t       timestamp   true        false       false
tsid    uint64      true        false       false
a       int         false       true        false
b       string      false       true        false

解释执行计划

1
EXPLAIN query;

EXPLAIN 语句结果展示一个查询如何被执行。例如：

1
EXPLAIN SELECT max(value) AS c1, avg(value) AS c2 FROM `t` GROUP BY name;

结果如下：

logical_plan
Projection: #MAX(07_optimizer_t.value) AS c1, #AVG(07_optimizer_t.value) AS c2
  Aggregate: groupBy=[[#07_optimizer_t.name]], aggr=[[MAX(#07_optimizer_t.value), AVG(#07_optimizer_t.value)]]
    TableScan: 07_optimizer_t projection=Some([name, value])

physical_plan
ProjectionExec: expr=[MAX(07_optimizer_t.value)@1 as c1, AVG(07_optimizer_t.value)@2 as c2]
  AggregateExec: mode=FinalPartitioned, gby=[name@0 as name], aggr=[MAX(07_optimizer_t.value), AVG(07_optimizer_t.value)]
    CoalesceBatchesExec: target_batch_size=4096
      RepartitionExec: partitioning=Hash([Column { name: \"name\", index: 0 }], 6)
        AggregateExec: mode=Partial, gby=[name@0 as name], aggr=[MAX(07_optimizer_t.value), AVG(07_optimizer_t.value)]
          ScanTable: table=07_optimizer_t, parallelism=8, order=None

6 - 配置项

建表时可以使用下列的选项配置引擎：

enable_ttl：布尔类型，默认为 true，当一个表配置 TTL 时，早于 ttl 的数据不会被查询到并且会被删除。
ttl：duration 类型，默认值为7d，此项定义数据的生命周期，只在 enable_ttl 为 true 的情况下使用。
storage_format： string 类型，数据存储的格式，有两种可选:
- columnar, 默认值
- hybrid, 注意：此功能仍在开发中，将来可能会发生变化。

上述两种存储格式详见存储格式部分。

存储格式

HoraeDB 支持两种存储格式，一个是 columnar, 这是传统的列式格式，一个物理列中存储表的一个列。

1
2
3
4
5
6
7
8
9
| Timestamp | Device ID | Status Code | Tag 1 | Tag 2 |
| --------- |---------- | ----------- | ----- | ----- |
| 12:01     | A         | 0           | v1    | v1    |
| 12:01     | B         | 0           | v2    | v2    |
| 12:02     | A         | 0           | v1    | v1    |
| 12:02     | B         | 1           | v2    | v2    |
| 12:03     | A         | 0           | v1    | v1    |
| 12:03     | B         | 0           | v2    | v2    |
| .....     |           |             |       |       |

另一个是 hybrid, 当前还在实验阶段的存储格式，用于在列式存储中模拟面向行的存储，以加速经典的时序查询。

在经典的时序场景中，如 IoT 或 DevOps，查询通常会先按系列 ID（或设备 ID）分组，然后再按时间戳分组。为了在这些场景中实现良好的性能，数据的物理布局应该与这种风格相匹配， hybrid 格式就是这样提出的。

1
2
3
4
5
 | Device ID | Timestamp           | Status Code | Tag 1 | Tag 2 | minTime | maxTime |
 |-----------|---------------------|-------------|-------|-------|---------|---------|
 | A         | [12:01,12:02,12:03] | [0,0,0]     | v1    | v1    | 12:01   | 12:03   |
 | B         | [12:01,12:02,12:03] | [0,1,0]     | v2    | v2    | 12:01   | 12:03   |
 | ...       |                     |             |       |       |         |         |

在一个文件中，同一个主键（例如设备 ID）的数据会被压缩到一行。
除了主键之外的列被分成两类：
- collapsible, 这些列会被压缩成一个 list，常用于时序表中的field字段。
  - 注意: 当前仅支持定长的字段
- non-collapsible, 这些列只能包含一个去重值，常用于时序表中的tag字段。
  - 注意: 当前仅支持字符串类型
另外多加了两个字段，minTime 和 maxTime，用于查询中过滤不必要的数据。
- 注意: 暂未实现此能力

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE `device` (
    `ts` timestamp NOT NULL,
    `tag1` string tag,
    `tag2` string tag,
    `value1` double,
    `value2` int,
    timestamp KEY (ts)) ENGINE=Analytic
  with (
    enable_ttl = 'false',
    storage_format = 'hybrid'
);

这段语句会创建一个混合存储格式的表, 这种情况下用户可以通过 parquet-tools查看数据格式. 上面定义的表的 parquet 结构如下所示：

message arrow_schema {
  optional group ts (LIST) {
    repeated group list {
      optional int64 item (TIMESTAMP(MILLIS,false));
    }
  }
  required int64 tsid (INTEGER(64,false));
  optional binary tag1 (STRING);
  optional binary tag2 (STRING);
  optional group value1 (LIST) {
    repeated group list {
      optional double item;
    }
  }
  optional group value2 (LIST) {
    repeated group list {
      optional int32 item;
    }
  }
}

7 - 标量函数

HoraeDB SQL 基于 DataFusion 实现，支持的标量函数如下。更多详情请参考： Datafusion

数值函数

函数	描述
abs(x)	绝对值
acos(x)	反余弦
asin(x)	反正弦
atan(x)	反正切
atan2(y, x)	y/x 的反正切
ceil(x)	小于或等于参数的最接近整数
cos(x)	余弦
exp(x)	指数
floor(x)	大于或等于参数的最接近整数
ln(x)	自然对数
log10(x)	以 10 为底的对数
log2(x)	以 2 为底的对数
power(base, exponent)	幂函数
round(x)	四舍五入
signum(x)	根据参数的正负返回 -1、0、+1
sin(x)	正弦
sqrt(x)	平方根
tan(x)	正切
trunc(x)	截断计算，取整（向零取整）

条件函数

函数	描述
coalesce	如果它的参数中有一个不为 null，则返回第一个参数，如果所有参数均为 null，则返回 null。当从数据库中检索数据用于显示时，它经常用于用默认值替换 null 值。
nullif	如果 value1 等于 value2，则返回 null 值；否则返回 value1。这可用于执行与 coalesce 表达式相反的操作

字符函数

函数	描述
ascii	返回参数的第一个字符的 ascii 数字编码。在 UTF8 编码下，返回字符的 Unicode 码点。在其他多字节编码中，参数必须是 ASCII 字符。
bit_length	返回字符串的比特位数。
btrim	从字符串的开头和结尾删除给定字符串中的字符组成的最长字符串
char_length	等效于 length。
character_length	等效于 length。
concat	将两个或多个字符串合并为一个字符串。
concat_ws	使用给定的分隔符组合两个值。
chr	根据数字码返回字符。
initcap	将字符串中每个单词的首字母大写。
left	返回字符串的指定最左边字符。
length	返回字符串中字符的数量。
lower	将字符串中的所有字符转换为它们的小写。
lpad	使用特定字符集将字符串左填充到给定长度。
ltrim	从字符串的开头删除由字符中的字符组成的最长字符串（默认为空格）。
md5	计算给定字符串的 MD5 散列值。
octet_length	等效于 length。
repeat	返回一个由输入字符串重复指定次数组成的字符串。
replace	替换字符串中所有子字符串的出现为新子字符串。
reverse	反转字符串。
right	返回字符串的指定最右边字符。
rpad	使用特定字符集将字符串右填充到给定长度。
rtrim	从字符串的结尾删除包含 characters 中任何字符的最长字符串。
digest	计算给定字符串的散列值。
split_part	按指定分隔符拆分字符串，并从结果数组中返回
starts_with	检查字符串是否以给定字符串开始
strpos	搜索字符串是否包含一个给定的字符串，并返回位置
substr	提取子字符串
translate	把字符串翻译成另一种字符集 Translates one set of characters into another.
trim	移除字符串两侧的空白字符或其他指定字符。
upper	将字符串中的所有字符转换为它们的大写。

正则函数

函数	描述
regexp_match	判断一个字符串是否匹配正则表达式
regexp_replace	使用新字符串替换正则匹配的字符串中内容

时间函数

函数	描述
to_timestamp	将字符串转换为 Timestamp(Nanoseconds，None)类型。
to_timestamp_millis	将字符串转换为 Timestamp(Milliseconds，None)类型。
to_timestamp_micros	将字符串转换为 Timestamp(Microseconds，None)类型。
to_timestamp_seconds	将字符串转换为 Timestamp(Seconds，None)类型。
extract	从日期/时间值中检索年份或小时等子字段。
date_part	从日期/时间值中检索子字段。
date_trunc	将日期/时间值截断到指定的精度。
date_bin	将日期/时间值按指定精度进行分组。
from_unixtime	将 Unix 时代转换为 Timestamp(Nanoseconds，None)类型。
now	作为 Timestamp(Nanoseconds，UTC)返回当前时间。

其他函数

Function	描述
array	创建有一个数组
arrow_typeof	返回内置的数据类型
in_list	检测数值是否在 list 里面
random	生成随机值
sha224	sha224
sha256	sha256
sha384	sha384
sha512	sha512
to_hex	转换为 16 进制

8 - 聚合函数

HoraeDB SQL 基于 DataFusion 实现，支持的聚合函数如下。更多详情请参考： Datafusion

常用

函数	描述
min	最小值
max	最大值
count	求行数
avg	平均值
sum	求和
array_agg	把数据放到一个数组

统计

函数	描述
var / var_samp	返回给定列的样本方差
var_pop	返回给定列的总体方差
stddev / stddev_samp	返回给定列的样本标准差
stddev_pop	返回给定列的总体标准差
covar / covar_samp	返回给定列的样本协方差
covar_pop	返回给定列的总体协方差
corr	返回给定列的相关系数

估值函数

函数	描述
approx_distinct	返回输入值的近似去重数量（HyperLogLog）
approx_median	返回输入值的近似中位数，它是 approx_percentile_cont(x, 0.5) 的简单写法
approx_percentile_cont	返回输入值的近似百分位数（TDigest），其中 p 是 0 和 1（包括）之间的 float64，等同于 approx_percentile_cont_with_weight(x, 1, p)
approx_percentile_cont_with_weight	返回输入值带权重的近似百分位数（TDigest），其中 w 是权重列表达式，p 是 0 和 1（包括）之间的 float64