mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-20 23:10:37 +00:00
c9ad8c7101
* feat: add show create table for pg in parser * feat: implement show create table operation * fix: adopt upstream changes
303 lines
13 KiB
Plaintext
303 lines
13 KiB
Plaintext
CREATE TABLE system_metrics (
|
|
id INT UNSIGNED,
|
|
host STRING,
|
|
cpu DOUBLE,
|
|
disk FLOAT COMMENT 'comment',
|
|
ts TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
TIME INDEX (ts),
|
|
PRIMARY KEY (id, host)
|
|
)
|
|
PARTITION ON COLUMNS (id) (
|
|
id < 5,
|
|
id >= 5 AND id < 9,
|
|
id >= 9
|
|
)
|
|
ENGINE=mito
|
|
WITH(
|
|
ttl = '7d',
|
|
write_buffer_size = 1024
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
SHOW CREATE TABLE system_metrics;
|
|
|
|
+----------------+-----------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+----------------+-----------------------------------------------------------+
|
|
| system_metrics | CREATE TABLE IF NOT EXISTS "system_metrics" ( |
|
|
| | "id" INT UNSIGNED NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | "disk" FLOAT NULL COMMENT 'comment', |
|
|
| | "ts" TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(), |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("id", "host") |
|
|
| | ) |
|
|
| | PARTITION ON COLUMNS ("id") ( |
|
|
| | id < 5, |
|
|
| | id >= 9, |
|
|
| | id >= 5 AND id < 9 |
|
|
| | ) |
|
|
| | ENGINE=mito |
|
|
| | WITH( |
|
|
| | ttl = '7days', |
|
|
| | write_buffer_size = '1.0KiB' |
|
|
| | ) |
|
|
+----------------+-----------------------------------------------------------+
|
|
|
|
SHOW CREATE TABLE system_metrics FOR POSTGRES_FOREIGN_TABLE;
|
|
|
|
+----------------+------------------------------------------+
|
|
| Table | Create Table |
|
|
+----------------+------------------------------------------+
|
|
| system_metrics | CREATE FOREIGN TABLE ft_system_metrics ( |
|
|
| | "id" INT4, |
|
|
| | "host" VARCHAR, |
|
|
| | "cpu" FLOAT8, |
|
|
| | "disk" FLOAT4, |
|
|
| | "ts" TIMESTAMP |
|
|
| | ) |
|
|
| | SERVER greptimedb |
|
|
| | OPTIONS (table_name 'system_metrics') |
|
|
+----------------+------------------------------------------+
|
|
|
|
DROP TABLE system_metrics;
|
|
|
|
Affected Rows: 0
|
|
|
|
create table table_without_partition (
|
|
ts TIMESTAMP TIME INDEX NOT NULL DEFAULT current_timestamp()
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table table_without_partition;
|
|
|
|
+-------------------------+-----------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-------------------------+-----------------------------------------------------------+
|
|
| table_without_partition | CREATE TABLE IF NOT EXISTS "table_without_partition" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(), |
|
|
| | TIME INDEX ("ts") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | |
|
|
+-------------------------+-----------------------------------------------------------+
|
|
|
|
drop table table_without_partition;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE not_supported_table_storage_option (
|
|
id INT UNSIGNED,
|
|
host STRING,
|
|
cpu DOUBLE,
|
|
disk FLOAT,
|
|
ts TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
TIME INDEX (ts),
|
|
PRIMARY KEY (id, host)
|
|
)
|
|
PARTITION ON COLUMNS (id) (
|
|
id < 5,
|
|
id >= 5 AND id < 9,
|
|
id >= 9
|
|
)
|
|
ENGINE=mito
|
|
WITH(
|
|
storage = 'S3'
|
|
);
|
|
|
|
Error: 1004(InvalidArguments), Object store not found: S3
|
|
|
|
CREATE TABLE phy (ts timestamp time index, val double) engine=metric with ("physical_metric_table" = "");
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE t1 (ts timestamp time index, val double, host string primary key) engine = metric with ("on_physical_table" = "phy");
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table phy;
|
|
|
|
+-------+------------------------------------+
|
|
| Table | Create Table |
|
|
+-------+------------------------------------+
|
|
| phy | CREATE TABLE IF NOT EXISTS "phy" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "val" DOUBLE NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=metric |
|
|
| | WITH( |
|
|
| | physical_metric_table = '' |
|
|
| | ) |
|
|
+-------+------------------------------------+
|
|
|
|
show create table t1;
|
|
|
|
+-------+-----------------------------------+
|
|
| Table | Create Table |
|
|
+-------+-----------------------------------+
|
|
| t1 | CREATE TABLE IF NOT EXISTS "t1" ( |
|
|
| | "host" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "val" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=metric |
|
|
| | WITH( |
|
|
| | on_physical_table = 'phy' |
|
|
| | ) |
|
|
+-------+-----------------------------------+
|
|
|
|
SHOW CREATE TABLE t1 FOR POSTGRES_FOREIGN_TABLE;
|
|
|
|
+-------+------------------------------+
|
|
| Table | Create Table |
|
|
+-------+------------------------------+
|
|
| t1 | CREATE FOREIGN TABLE ft_t1 ( |
|
|
| | "host" VARCHAR, |
|
|
| | "ts" TIMESTAMP, |
|
|
| | "val" FLOAT8 |
|
|
| | ) |
|
|
| | SERVER greptimedb |
|
|
| | OPTIONS (table_name 't1') |
|
|
+-------+------------------------------+
|
|
|
|
drop table t1;
|
|
|
|
Affected Rows: 0
|
|
|
|
drop table phy;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE IF NOT EXISTS "phy" (
|
|
"ts" TIMESTAMP(3) NOT NULL,
|
|
"val" DOUBLE NULL,
|
|
"__table_id" INT UNSIGNED NOT NULL,
|
|
"__tsid" BIGINT UNSIGNED NOT NULL,
|
|
"host" STRING NULL,
|
|
"job" STRING NULL,
|
|
TIME INDEX ("ts"),
|
|
PRIMARY KEY ("__table_id", "__tsid", "host", "job")
|
|
)
|
|
ENGINE=mito
|
|
WITH(
|
|
physical_metric_table = '',
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table phy;
|
|
|
|
+-------+-------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-------+-------------------------------------------------------+
|
|
| phy | CREATE TABLE IF NOT EXISTS "phy" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "val" DOUBLE NULL, |
|
|
| | "__table_id" INT UNSIGNED NOT NULL, |
|
|
| | "__tsid" BIGINT UNSIGNED NOT NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "job" STRING NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("__table_id", "__tsid", "host", "job") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | WITH( |
|
|
| | physical_metric_table = '' |
|
|
| | ) |
|
|
+-------+-------------------------------------------------------+
|
|
|
|
drop table phy;
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table numbers;
|
|
|
|
Error: 1001(Unsupported), Show create table only for base table. greptime.public.numbers is TEMPORARY
|
|
|
|
show create table information_schema.columns;
|
|
|
|
Error: 1001(Unsupported), Show create table only for base table. greptime.information_schema.columns is TEMPORARY
|
|
|
|
CREATE TABLE "specify_invereted_index_cols" (
|
|
"ts" TIMESTAMP(3) NOT NULL,
|
|
"val" DOUBLE NULL,
|
|
"host" STRING NULL,
|
|
"job" STRING NULL,
|
|
TIME INDEX ("ts"),
|
|
PRIMARY KEY ("host", "job"),
|
|
INVERTED INDEX ("job")
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table specify_invereted_index_cols;
|
|
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| specify_invereted_index_cols | CREATE TABLE IF NOT EXISTS "specify_invereted_index_cols" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "val" DOUBLE NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "job" STRING NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host", "job"), |
|
|
| | INVERTED INDEX ("job") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
|
|
drop table specify_invereted_index_cols;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE "specify_empty_invereted_index_cols" (
|
|
"ts" TIMESTAMP(3) NOT NULL,
|
|
"val" DOUBLE NULL,
|
|
"host" STRING NULL,
|
|
"job" STRING NULL,
|
|
TIME INDEX ("ts"),
|
|
PRIMARY KEY ("host", "job"),
|
|
INVERTED INDEX ()
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
show create table specify_empty_invereted_index_cols;
|
|
|
|
+------------------------------------+-------------------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+------------------------------------+-------------------------------------------------------------------+
|
|
| specify_empty_invereted_index_cols | CREATE TABLE IF NOT EXISTS "specify_empty_invereted_index_cols" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "val" DOUBLE NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "job" STRING NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host", "job"), |
|
|
| | INVERTED INDEX () |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | |
|
|
+------------------------------------+-------------------------------------------------------------------+
|
|
|
|
drop table specify_empty_invereted_index_cols;
|
|
|
|
Affected Rows: 0
|
|
|