mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-17 05:20:37 +00:00
c83868c4eb
* basic impl Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * reuse collider Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * simplify range helpers Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * notes Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update unit test resule Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
289 lines
10 KiB
Plaintext
289 lines
10 KiB
Plaintext
CREATE TABLE alter_repartition_table(
|
|
device_id INT,
|
|
area STRING,
|
|
ty STRING,
|
|
ts TIMESTAMP TIME INDEX,
|
|
PRIMARY KEY(device_id)
|
|
) PARTITION ON COLUMNS (device_id, area) (
|
|
device_id < 100,
|
|
device_id >= 100 AND device_id < 200,
|
|
device_id >= 200
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
ALTER TABLE alter_repartition_table REPARTITION (
|
|
device_id < 100
|
|
) INTO (
|
|
device_id < 100 AND area < 'South',
|
|
device_id < 100 AND area >= 'South'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
SHOW CREATE TABLE alter_repartition_table;
|
|
|
|
+-------------------------+--------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-------------------------+--------------------------------------------------------+
|
|
| alter_repartition_table | CREATE TABLE IF NOT EXISTS "alter_repartition_table" ( |
|
|
| | "device_id" INT NULL, |
|
|
| | "area" STRING NULL, |
|
|
| | "ty" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("device_id") |
|
|
| | ) |
|
|
| | PARTITION ON COLUMNS ("device_id", "area") ( |
|
|
| | device_id < 100 AND area < 'South', |
|
|
| | device_id >= 100 AND device_id < 200, |
|
|
| | device_id >= 200, |
|
|
| | device_id < 100 AND area >= 'South' |
|
|
| | ) |
|
|
| | ENGINE=mito |
|
|
| | |
|
|
+-------------------------+--------------------------------------------------------+
|
|
|
|
ALTER TABLE alter_repartition_table MERGE PARTITION (
|
|
device_id < 100 AND area < 'South',
|
|
device_id < 100 AND area >= 'South'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
SHOW CREATE TABLE alter_repartition_table;
|
|
|
|
+-------------------------+--------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-------------------------+--------------------------------------------------------+
|
|
| alter_repartition_table | CREATE TABLE IF NOT EXISTS "alter_repartition_table" ( |
|
|
| | "device_id" INT NULL, |
|
|
| | "area" STRING NULL, |
|
|
| | "ty" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("device_id") |
|
|
| | ) |
|
|
| | PARTITION ON COLUMNS ("device_id", "area") ( |
|
|
| | device_id < 100, |
|
|
| | device_id >= 100 AND device_id < 200, |
|
|
| | device_id >= 200 |
|
|
| | ) |
|
|
| | ENGINE=mito |
|
|
| | |
|
|
+-------------------------+--------------------------------------------------------+
|
|
|
|
-- FIXME(weny): Object store is not configured for the test environment,
|
|
-- so staging manifest may not be applied in some cases.
|
|
-- invalid: empty source clause
|
|
ALTER TABLE alter_repartition_table REPARTITION () INTO (
|
|
device_id < 100
|
|
);
|
|
|
|
Error: 2000(InvalidSyntax), Invalid SQL syntax: sql parser error: Expected expression inside REPARTITION clause, found: )
|
|
|
|
-- invalid: more than one INTO clause
|
|
ALTER TABLE alter_repartition_table REPARTITION (
|
|
device_id < 100
|
|
) INTO (
|
|
device_id < 50
|
|
), (
|
|
device_id >= 50
|
|
) INTO (
|
|
device_id >= 50
|
|
);
|
|
|
|
Error: 2000(InvalidSyntax), Invalid SQL syntax: sql parser error: Expected end of REPARTITION clause, found: ,
|
|
|
|
DROP TABLE alter_repartition_table;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE alter_repartition_table_with_options(
|
|
device_id INT,
|
|
area STRING,
|
|
ty STRING,
|
|
ts TIMESTAMP TIME INDEX,
|
|
PRIMARY KEY(device_id)
|
|
) PARTITION ON COLUMNS (device_id, area) (
|
|
device_id < 100,
|
|
device_id >= 100 AND device_id < 200,
|
|
device_id >= 200
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
-- SQLNESS REPLACE ([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-4[0-9a-fA-F]{3}-[89abAB][0-9a-fA-F]{3}-[0-9a-fA-F]{12}) PROC_ID
|
|
ALTER TABLE alter_repartition_table_with_options REPARTITION (
|
|
device_id < 100
|
|
) INTO (
|
|
device_id < 100 AND area < 'South',
|
|
device_id < 100 AND area >= 'South'
|
|
) WITH (
|
|
TIMEOUT = '5m',
|
|
WAIT = false
|
|
);
|
|
|
|
+--------------------------------------+
|
|
| Procedure ID |
|
|
+--------------------------------------+
|
|
| PROC_ID |
|
|
+--------------------------------------+
|
|
|
|
DROP TABLE alter_repartition_table_with_options;
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Metric engine repartition test
|
|
CREATE TABLE metric_physical_table (
|
|
ts TIMESTAMP TIME INDEX,
|
|
host STRING,
|
|
cpu DOUBLE,
|
|
PRIMARY KEY(host)
|
|
)
|
|
PARTITION ON COLUMNS (host) (
|
|
host < 'h1',
|
|
host >= 'h1' AND host < 'h2',
|
|
host >= 'h2'
|
|
)
|
|
ENGINE = metric
|
|
WITH (
|
|
physical_metric_table = "true"
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE logical_table_v1 (
|
|
ts TIMESTAMP TIME INDEX,
|
|
host STRING PRIMARY KEY,
|
|
cpu DOUBLE,
|
|
)
|
|
ENGINE = metric
|
|
WITH (
|
|
on_physical_table = "metric_physical_table"
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE logical_table_v2 (
|
|
ts TIMESTAMP TIME INDEX,
|
|
host STRING PRIMARY KEY,
|
|
cpu DOUBLE,
|
|
)
|
|
ENGINE = metric
|
|
WITH (
|
|
on_physical_table = "metric_physical_table"
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Split physical table partition
|
|
ALTER TABLE metric_physical_table SPLIT PARTITION (
|
|
host < 'h1'
|
|
) INTO (
|
|
host < 'h0',
|
|
host >= 'h0' AND host < 'h1'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
SHOW CREATE TABLE metric_physical_table;
|
|
|
|
+-----------------------+------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-----------------------+------------------------------------------------------+
|
|
| metric_physical_table | CREATE TABLE IF NOT EXISTS "metric_physical_table" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | PARTITION ON COLUMNS ("host") ( |
|
|
| | host < 'h0', |
|
|
| | host >= 'h1' AND host < 'h2', |
|
|
| | host >= 'h2', |
|
|
| | host >= 'h0' AND host < 'h1' |
|
|
| | ) |
|
|
| | ENGINE=metric |
|
|
| | WITH( |
|
|
| | physical_metric_table = 'true' |
|
|
| | ) |
|
|
+-----------------------+------------------------------------------------------+
|
|
|
|
-- Verify select * works and returns empty
|
|
SELECT * FROM metric_physical_table;
|
|
|
|
++
|
|
++
|
|
|
|
SELECT * FROM logical_table_v1;
|
|
|
|
++
|
|
++
|
|
|
|
SELECT * FROM logical_table_v2;
|
|
|
|
++
|
|
++
|
|
|
|
-- Merge physical table partition
|
|
ALTER TABLE metric_physical_table MERGE PARTITION (
|
|
host < 'h0',
|
|
host >= 'h0' AND host < 'h1'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
SHOW CREATE TABLE metric_physical_table;
|
|
|
|
+-----------------------+------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+-----------------------+------------------------------------------------------+
|
|
| metric_physical_table | CREATE TABLE IF NOT EXISTS "metric_physical_table" ( |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "host" STRING NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | PARTITION ON COLUMNS ("host") ( |
|
|
| | host < 'h1', |
|
|
| | host >= 'h1' AND host < 'h2', |
|
|
| | host >= 'h2' |
|
|
| | ) |
|
|
| | ENGINE=metric |
|
|
| | WITH( |
|
|
| | physical_metric_table = 'true' |
|
|
| | ) |
|
|
+-----------------------+------------------------------------------------------+
|
|
|
|
-- Verify select * works and returns empty
|
|
SELECT * FROM metric_physical_table;
|
|
|
|
++
|
|
++
|
|
|
|
SELECT * FROM logical_table_v1;
|
|
|
|
++
|
|
++
|
|
|
|
SELECT * FROM logical_table_v2;
|
|
|
|
++
|
|
++
|
|
|
|
DROP TABLE logical_table_v1;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE logical_table_v2;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE metric_physical_table;
|
|
|
|
Affected Rows: 0
|
|
|