CREATE

CREATE 用于创建新的数据库或者表。

CREATE DATABASE

Syntax

创建新数据库:

  1. CREATE DATABASE [IF NOT EXISTS] db_name [WITH <options>]

如果 db_name 数据库已经存在,CREATE 语句的行为如下:

  • 不会创建新的数据库。
  • IF NOT EXISTS 子句被指定时,不会返回错误。
  • 否则,返回错误。

数据库也可以通过使用 WITH 关键字配置与 CREATE TABLE 语句类似的选项。表选项 中提供的所有选项也可以在此处使用。在创建表时,如果未提供相应的表选项,将使用在数据库级别配置的选项或者默认值。

示例

创建名为 test 的数据库:

  1. CREATE DATABASE test;
  1. Query OK, 1 row affected (0.05 sec)

使用 IF NOT EXISTS 再次创建:

  1. CREATE DATABASE IF NOT EXISTS test;

创建一个具有 7 天 TTL(数据存活时间)的数据库,也就是该数据库中的所有表如果没有单独设置 TTL 选项,都将继承此选项值。

  1. CREATE DATABASE test WITH (ttl='7d');

CREATE TABLE

Syntax

db 或当前数据库中创建新表:

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name
  2. (
  3. column1 type1 [NULL | NOT NULL] [DEFAULT expr1] [TIME INDEX] [PRIMARY KEY] [FULLTEXT | FULLTEXT WITH options] [COMMENT comment1],
  4. column2 type2 [NULL | NOT NULL] [DEFAULT expr2] [TIME INDEX] [PRIMARY KEY] [FULLTEXT | FULLTEXT WITH options] [COMMENT comment2],
  5. ...
  6. [TIME INDEX (column)],
  7. [PRIMARY KEY(column1, column2, ...)]
  8. ) ENGINE = engine WITH([TTL | storage | ...] = expr, ...)
  9. [
  10. PARTITION ON COLUMNS(column1, column2, ...) (
  11. <PARTITION EXPR>,
  12. ...
  13. )
  14. ]

表 schema 由 ENGINE 之前的括号指定,表 schema 是列的定义和表的约束。 列定义包括列名称和数据类型,以及可选的 NULLNOT NULLDEFAULT 等。

表约束

表约束包括以下内容:

  • TIME INDEX 指定时间索引列,每个表只能有一个时间索引列。它表示 GreptimeDB 的 数据模型 中的 Timestamp 类型。
  • PRIMARY KEY 指定表的主键列,它表示 GreptimeDB 的 数据模型 中的 Tag 类型。它不能包含时间索引列,但是它总是隐式地将时间索引列添加到键的末尾。
  • 其他列是 GreptimeDB 的 数据模型 中的 Field 类型。

CREATE - 图1注意

CREATE 语句中指定的 PRIMARY KEY 不是 传统关系数据库中的主键。 实际上,传统关系数据库中的 PRIMARY KEY 相当于 GreptimeDB 中的 PRIMARY KEYTIME INDEX 的组合。 换句话说,PRIMARY KEYTIME INDEX 一起构成了 GreptimeDB 中行的唯一标识符。

如果表已经存在且创建表时指定了 IF NOT EXISTSCREATE 语句不会返回错误;否则返回错误。

表选项

用户可以使用 WITH 添加表选项。有效的选项包括以下内容:

选项描述
ttl表数据的存储时间字符串值,例如 ‘60m’, ‘1h’ 代表 1 小时, ‘14d’ 代表 14 天等。支持的时间单位有:s / m / h / d
storage自定义表的存储引擎,存储引擎提供商的名字字符串,类似 S3Gcs 等。 必须在 [[storage.providers]] 列表里配置, 参考 configuration
compaction.typeCompaction 策略字符串值. 只支持 twcs。你可以阅读这篇文章来了解 twcs compaction 策略
compaction.twcs.max_active_window_files当前活跃时间窗口内的最大文件数字符串值,如 ‘8’。只在 compaction.typetwcs 时可用
compaction.twcs.max_inactive_window_files非活跃时间窗口内的最大文件数字符串值,如 ‘1’。只在 compaction.typetwcs 时可用
compaction.twcs.time_windowCompaction 时间窗口字符串值,如 ‘1d’ 表示 1 天。该表会根据时间戳将数据分区到不同的时间窗口中。只在 compaction.typetwcs 时可用
memtable.typememtable 的类型字符串值,支持 time_seriespartition_tree
append_mode该表是否时 append-only 的字符串值。默认值为 ‘false’,根据 ‘merge_mode’ 按主键和时间戳删除重复行。设置为 ‘true’ 可以开启 append 模式和创建 append-only 表,保留所有重复的行
merge_mode合并重复行的策略字符串值。只有当 append_mode 为 ‘false’ 时可用。默认值为 last_row,保留相同主键和时间戳的最后一行。设置为 last_non_null 则保留相同主键和时间戳的最后一个非空字段。
comment表级注释字符串值.

创建指定 TTL 的表

例如,创建一个存储数据 TTL(Time-To-Live) 为七天的表:

  1. CREATE TABLE IF NOT EXISTS temperatures(
  2. ts TIMESTAMP TIME INDEX,
  3. temperature DOUBLE DEFAULT 10,
  4. ) engine=mito with(ttl='7d');

创建自定义存储的表

或者创建一个表单独将数据存储在 Google Cloud Storage 服务上:

  1. CREATE TABLE IF NOT EXISTS temperatures(
  2. ts TIMESTAMP TIME INDEX,
  3. temperature DOUBLE DEFAULT 10,
  4. ) engine=mito with(ttl='7d', storage="Gcs");

创建自定义 compaction 参数的表

创建带自定义 twcs compaction 参数的表。这个表会尝试根据数据的时间戳将数据按 1 天的时间窗口分区。

  • 它会在最新时间窗口内的文件超过 8 个时合并该窗口的文件
  • 它会将非最新窗口内的文件合并为一个文件
  1. CREATE TABLE IF NOT EXISTS temperatures(
  2. ts TIMESTAMP TIME INDEX,
  3. temperature DOUBLE DEFAULT 10,
  4. )
  5. engine=mito
  6. with(
  7. 'compaction.type'='twcs',
  8. 'compaction.twcs.time_window'='1d',
  9. 'compaction.twcs.max_active_window_files'='8',
  10. 'compaction.twcs.max_inactive_window_files'='1',
  11. );

创建 Append-Only 表

创建一个 append-only 表来关闭去重

  1. CREATE TABLE IF NOT EXISTS temperatures(
  2. ts TIMESTAMP TIME INDEX,
  3. temperature DOUBLE DEFAULT 10,
  4. ) engine=mito with('append_mode'='true');

创建带有 merge 模式的表

创建一个带有 last_row merge 模式的表,这是默认的 merge 模式。

  1. create table if not exists metrics(
  2. host string,
  3. ts timestamp,
  4. cpu double,
  5. memory double,
  6. TIME INDEX (ts),
  7. PRIMARY KEY(host)
  8. )
  9. engine=mito
  10. with('merge_mode'='last_row');

last_row 模式下,表会通过保留最新的行来合并具有相同主键和时间戳的行。

  1. INSERT INTO metrics VALUES ('host1', 0, 0, NULL), ('host2', 1, NULL, 1);
  2. INSERT INTO metrics VALUES ('host1', 0, NULL, 10), ('host2', 1, 11, NULL);
  3. SELECT * from metrics ORDER BY host, ts;
  4. +-------+-------------------------+------+--------+
  5. | host | ts | cpu | memory |
  6. +-------+-------------------------+------+--------+
  7. | host1 | 1970-01-01T00:00:00 | | 10.0 |
  8. | host2 | 1970-01-01T00:00:00.001 | 11.0 | |
  9. +-------+-------------------------+------+--------+

创建带有 last_non_null merge 模式的表。

  1. create table if not exists metrics(
  2. host string,
  3. ts timestamp,
  4. cpu double,
  5. memory double,
  6. TIME INDEX (ts),
  7. PRIMARY KEY(host)
  8. )
  9. engine=mito
  10. with('merge_mode'='last_non_null');

last_non_null 模式下,表会通过保留每个字段的最新值来合并具有相同主键和时间戳的行。

  1. INSERT INTO metrics VALUES ('host1', 0, 0, NULL), ('host2', 1, NULL, 1);
  2. INSERT INTO metrics VALUES ('host1', 0, NULL, 10), ('host2', 1, 11, NULL);
  3. SELECT * from metrics ORDER BY host, ts;
  4. +-------+-------------------------+------+--------+
  5. | host | ts | cpu | memory |
  6. +-------+-------------------------+------+--------+
  7. | host1 | 1970-01-01T00:00:00 | 0.0 | 10.0 |
  8. | host2 | 1970-01-01T00:00:00.001 | 11.0 | 1.0 |
  9. +-------+-------------------------+------+--------+

列选项

GreptimeDB 支持以下列选项:

选项描述
NULL列值可以为 null
NOT NULL列值不能为 null
DEFAULT expr该列的默认值是 expr,其类型必须是该列的类型
COMMENT comment列注释,必须为字符串类型
FULLTEXT创建全文索引,可以加速全文搜索操作。仅适用于字符串类型列

表约束 TIME INDEXPRIMARY KEY 也可以通过列选项设置,但是它们只能在列定义中指定一次,在多个列选项中指定 PRIMARY KEY 会报错:

  1. CREATE TABLE system_metrics (
  2. host STRING PRIMARY KEY,
  3. idc STRING PRIMARY KEY,
  4. cpu_util DOUBLE,
  5. memory_util DOUBLE,
  6. disk_util DOUBLE,
  7. ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  8. TIME INDEX(ts)
  9. );

会得到报错:

  1. Illegal primary keys definition: not allowed to inline multiple primary keys in columns options

正确的做法是使用 PRIMARY KEY() 来指定多个列作为主键:

  1. CREATE TABLE system_metrics (
  2. host STRING,
  3. idc STRING,
  4. cpu_util DOUBLE,
  5. memory_util DOUBLE,
  6. disk_util DOUBLE,
  7. ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX,
  8. PRIMARY KEY(host, idc),
  9. );
  1. Query OK, 0 rows affected (0.01 sec)

FULLTEXT 列选项

FULLTEXT 用于创建全文索引,加速全文搜索操作。该选项只能应用于字符串类型的列。

使用 FULLTEXT WITH 可以指定以下选项:

  • analyzer:设置全文索引的分析器语言,支持 EnglishChinese
  • case_sensitive:设置全文索引是否区分大小写,支持 truefalse

如果不带 WITH 选项,FULLTEXT 将使用默认值:

  • analyzer:默认 English
  • case_sensitive:默认 true

例如,要创建一个带有全文索引的表,配置 log 列为全文索引,并指定分析器为 Chinese 且不区分大小写:

  1. CREATE TABLE IF NOT EXISTS logs(
  2. host STRING PRIMARY KEY,
  3. log STRING FULLTEXT WITH(analyzer = 'Chinese', case_sensitive = 'false'),
  4. ts TIMESTAMP TIME INDEX
  5. ) ENGINE=mito;

更多关于全文索引和全文搜索的使用,请参阅 日志查询文档

Region 分区规则

请参考 分区 章节.

CREATE EXTERNAL TABLE

Syntax

db 或当前数据库中创建新的文件外部表:

  1. CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name
  2. [
  3. (
  4. column1 type1 [NULL | NOT NULL] [DEFAULT expr1] [TIME INDEX] [PRIMARY KEY] [COMMENT comment1],
  5. column2 type2 [NULL | NOT NULL] [DEFAULT expr2] [TIME INDEX] [PRIMARY KEY] [COMMENT comment2],
  6. ...
  7. [TIME INDEX (column)],
  8. [PRIMARY KEY(column1, column2, ...)]
  9. )
  10. ] WITH (
  11. LOCATION = url,
  12. FORMAT = { 'CSV' | 'JSON' | 'PARQUET' | 'ORC' }
  13. [,PATTERN = regex_pattern ]
  14. [,REGION = region ]
  15. [,ENDPOINT = uri ]
  16. [,ACCESS_KEY_ID = key_id ]
  17. [,SECRET_ACCESS_KEY = access_key ]
  18. [,ENABLE_VIRTUAL_HOST_STYLE = { TRUE | FALSE }]
  19. [,SESSION_TOKEN = token ]
  20. ...
  21. )

表选项

选项描述是否必需
LOCATION外部表的位置,例如 s3://<bucket>[<path>], /<path>/[<filename>]
FORMAT目标文件的格式,例如 JSON,CSV,Parquet, ORC
PATTERN使用正则来匹配文件,例如 *_today.parquet可选

S3

选项描述是否必需
REGIONAWS region 名称,例如 us-east-1
ENDPOINTThe bucket endpoint可选
ACCESS_KEY_ID用于连接 AWS S3 兼容对象存储的访问密钥 ID可选
SECRET_ACCESS_KEY用于连接 AWS S3 兼容对象存储的秘密访问密钥可选
ENABLE_VIRTUAL_HOST_STYLE如果你想要使用 virtual hosting 来定位 bucket,将其设置为 true可选
SESSION_TOKEN用于连接 AWS S3 服务的临时凭证可选

时间索引列

在利用 CREATE EXTERNAL TABLE 语句创建外部表时,要求使用 TIME INDEX 约束来指定一个时间索引列。

示例

你可以在创建外部表时不带有列定义,列定义将会被自动推断:

  1. CREATE EXTERNAL TABLE IF NOT EXISTS city WITH (location='/var/data/city.csv',format='csv');

在这个例子中,我们没有明确定义表的列,为满足外边表必须指定时间索引列的要求,CREATE EXTERNAL TABLE 语句会依据下述规则推断出时间索引列:

  1. 如果可以从文件元数据中推断出时间索引列,那么就用该列作为时间索引列。
  2. 如果存在名为 greptime_timestamp 的列(该列的类型必须为 TIMESTAMP,否则将抛出错误),那么就用该列作为时间索引列。
  3. 否则,将自动创建名为 greptime_timestamp 的列作为时间索引列,并添加 DEFAULT '1970-01-01 00:00:00+0000' 约束。

或者带有列定义:

  1. CREATE EXTERNAL TABLE city (
  2. host string,
  3. ts timestamp,
  4. cpu float64 default 0,
  5. memory float64,
  6. TIME INDEX (ts),
  7. PRIMARY KEY(host)
  8. ) WITH (location='/var/data/city.csv', format='csv');

在这个例子中,我们明确定义了 ts 列作为时间索引列。如果在文件中没有适合的时间索引列,你也可以创建一个占位符列,并添加 DEFAULT expr 约束。

创建 Flow

  1. CREATE [OR REPLACE] FLOW [ IF NOT EXISTS ] <flow-name>
  2. SINK TO <sink-table-name>
  3. [ EXPIRE AFTER <expr> ]
  4. [ COMMENT = "<string>" ]
  5. AS
  6. <SQL>;

用于创建或更新 Flow 任务,请阅读Flow 管理文档

创建 View

  1. CREATE [OR REPLACE] VIEW [ IF NOT EXISTS ] <view-name>
  2. AS select_statement

用于创建或更新视图,请阅读视图用户指南