mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-18 05:50:41 +00:00
b2074e3863
* chore: upgrade DataFusion family Signed-off-by: luofucong <luofc@foxmail.com> * chore: switch to released version of datafusion-pg-catalog --------- Signed-off-by: luofucong <luofc@foxmail.com> Co-authored-by: Ning Sun <sunning@greptime.com> Co-authored-by: Ning Sun <sunng@protonmail.com>
186 lines
9.9 KiB
Plaintext
186 lines
9.9 KiB
Plaintext
-- Simplified schema and queries for TSID on physical table
|
|
CREATE TABLE phy (
|
|
ts TIMESTAMP(3) TIME INDEX,
|
|
v DOUBLE NULL,
|
|
tag1 STRING,
|
|
tag2 STRING,
|
|
le STRING,
|
|
tag4 STRING,
|
|
tag5 STRING,
|
|
tag6 STRING NULL,
|
|
tag7 STRING NULL,
|
|
tag8 STRING NULL,
|
|
PRIMARY KEY (
|
|
tag1,
|
|
tag2,
|
|
le,
|
|
tag4,
|
|
tag5,
|
|
tag6,
|
|
tag7,
|
|
tag8
|
|
)
|
|
) ENGINE = metric WITH ("physical_metric_table" = "");
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE IF NOT EXISTS test_tsid (
|
|
tag1 STRING,
|
|
tag2 STRING,
|
|
ts TIMESTAMP(3) NOT NULL,
|
|
v DOUBLE NULL,
|
|
le STRING,
|
|
tag4 STRING,
|
|
tag5 STRING,
|
|
tag6 STRING NULL,
|
|
tag7 STRING NULL,
|
|
tag8 STRING NULL,
|
|
TIME INDEX (ts),
|
|
PRIMARY KEY (
|
|
tag1,
|
|
tag2,
|
|
le,
|
|
tag4,
|
|
tag5,
|
|
tag6,
|
|
tag7,
|
|
tag8
|
|
)
|
|
) ENGINE=metric WITH(
|
|
on_physical_table = 'phy'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO test_tsid
|
|
(ts, v, tag1, tag2, le, tag4, tag5, tag8, tag6, tag7)
|
|
VALUES
|
|
('2026-01-23T03:40:00Z', 2.0, 'istio-ingressgateway', 'outbound', '0.5', 'svc-a', 'prod', 'peer.example', 'svc-b', 'prod'),
|
|
('2026-01-23T03:41:00Z', 5.0, 'istio-ingressgateway', 'outbound', '0.9', 'svc-a', 'prod', 'peer.example', 'svc-b', 'prod'),
|
|
('2026-01-23T03:41:30Z', 10.0, 'istio-ingressgateway', 'outbound', '+Inf', 'svc-a', 'prod', 'peer.example', 'svc-b', 'prod');
|
|
|
|
Affected Rows: 3
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
TQL EVAL (
|
|
timestamp '2026-01-23 03:30:00+00' + (now() - now()),
|
|
timestamp '2026-01-23 03:45:00+00' + (now() - now()),
|
|
'1m'
|
|
) histogram_quantile(
|
|
0.50,
|
|
sum by (le, tag4, tag5) (
|
|
avg_over_time(test_tsid[30m])
|
|
)
|
|
);
|
|
|
|
+-------+------+---------------------+-------------------------------------+
|
|
| tag4 | tag5 | ts | sum(prom_avg_over_time(ts_range,v)) |
|
|
+-------+------+---------------------+-------------------------------------+
|
|
| svc-a | prod | 2026-01-23T03:40:00 | NaN |
|
|
| svc-a | prod | 2026-01-23T03:41:00 | NaN |
|
|
| svc-a | prod | 2026-01-23T03:42:00 | 0.9 |
|
|
| svc-a | prod | 2026-01-23T03:43:00 | 0.9 |
|
|
| svc-a | prod | 2026-01-23T03:44:00 | 0.9 |
|
|
| svc-a | prod | 2026-01-23T03:45:00 | 0.9 |
|
|
+-------+------+---------------------+-------------------------------------+
|
|
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
-- SQLNESS REPLACE (RepartitionExec:.*) RepartitionExec: REDACTED
|
|
-- SQLNESS REPLACE phy.__table_id\s=\sUInt32\(\d+\) phy.__table_id=UInt32(REDACTED)
|
|
TQL EXPLAIN (
|
|
timestamp '2026-01-23 03:30:00+00' + (now() - now()),
|
|
timestamp '2026-01-23 03:45:00+00' + (now() - now()),
|
|
'1m'
|
|
) histogram_quantile(
|
|
0.50,
|
|
sum by (le, tag4, tag5) (
|
|
avg_over_time(test_tsid[30m])
|
|
)
|
|
);
|
|
|
|
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| plan_type | plan |
|
|
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| logical_plan | HistogramFold: le=le, field=sum(prom_avg_over_time(ts_range,v)), quantile=0.5 |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | Sort: test_tsid.le ASC NULLS LAST, test_tsid.tag4 ASC NULLS LAST, test_tsid.tag5 ASC NULLS LAST, test_tsid.ts ASC NULLS LAST |
|
|
| | Aggregate: groupBy=[[test_tsid.le, test_tsid.tag4, test_tsid.tag5, test_tsid.ts]], aggr=[[sum(prom_avg_over_time(ts_range,v))]] |
|
|
| | Filter: prom_avg_over_time(ts_range,v) IS NOT NULL |
|
|
| | Projection: test_tsid.ts, prom_avg_over_time(ts_range, v) AS prom_avg_over_time(ts_range,v), test_tsid.le, test_tsid.tag1, test_tsid.tag2, test_tsid.tag4, test_tsid.tag5, test_tsid.tag6, test_tsid.tag7, test_tsid.tag8 |
|
|
| | PromRangeManipulate: req range=[1769139000000..1769139900000], interval=[60000], eval range=[1800000], time index=[ts], values=["v"] |
|
|
| | PromSeriesNormalize: offset=[0], time index=[ts], filter NaN: [true] |
|
|
| | PromSeriesDivide: tags=["__tsid"] |
|
|
| | Sort: test_tsid.__tsid ASC NULLS FIRST, test_tsid.ts ASC NULLS FIRST |
|
|
| | Filter: test_tsid.ts >= TimestampMillisecond(1769137200001, None) AND test_tsid.ts <= TimestampMillisecond(1769139900000, None) |
|
|
| | Projection: test_tsid.v, test_tsid.le, test_tsid.tag1, test_tsid.tag2, test_tsid.tag4, test_tsid.tag5, test_tsid.tag6, test_tsid.tag7, test_tsid.tag8, test_tsid.__tsid, test_tsid.ts |
|
|
| | SubqueryAlias: test_tsid |
|
|
| | Filter: phy.__table_id=UInt32(REDACTED) |
|
|
| | TableScan: phy projection=[ts, v, tag1, tag2, le, tag4, tag5, tag6, tag7, tag8, __table_id, __tsid] |
|
|
| | ]] |
|
|
| physical_plan | HistogramFoldExec: le=@0, field=@4, quantile=0.5 |
|
|
| | SortExec: expr=[tag4@1 ASC NULLS LAST, tag5@2 ASC NULLS LAST, ts@3 ASC NULLS LAST, CAST(le@0 AS Float64) ASC NULLS LAST], preserve_partitioning=[true] |
|
|
| | RepartitionExec: REDACTED
|
|
| | MergeScanExec: REDACTED
|
|
| | |
|
|
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
CREATE FLOW IF NOT EXISTS test_tsid
|
|
SINK TO 'test_tsid_output'
|
|
EVAL INTERVAL '3600 s'
|
|
AS
|
|
TQL EVAL (
|
|
timestamp '2026-01-23 03:10:00+00' + (now() - now()),
|
|
timestamp '2026-01-23 03:50:00+00' + (now() - now()),
|
|
'1m'
|
|
)
|
|
histogram_quantile(
|
|
0.50,
|
|
sum by (le, tag4, tag5) (
|
|
avg_over_time(test_tsid[30m])
|
|
)
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
-- SQLNESS REPLACE (ADMIN\sFLUSH_FLOW\('\w+'\)\s+\|\n\+-+\+\n\|\s+)[0-9]+\s+\| $1 FLOW_FLUSHED |
|
|
ADMIN FLUSH_FLOW('test_tsid');
|
|
|
|
+-------------------------------+
|
|
| ADMIN FLUSH_FLOW('test_tsid') |
|
|
+-------------------------------+
|
|
| FLOW_FLUSHED |
|
|
+-------------------------------+
|
|
|
|
SELECT * FROM "test_tsid_output"
|
|
ORDER BY ts
|
|
LIMIT 5;
|
|
|
|
+-------+------+---------------------+-------------------------------------+
|
|
| tag4 | tag5 | ts | sum(prom_avg_over_time(ts_range,v)) |
|
|
+-------+------+---------------------+-------------------------------------+
|
|
| svc-a | prod | 2026-01-23T03:40:00 | NaN |
|
|
| svc-a | prod | 2026-01-23T03:41:00 | NaN |
|
|
| svc-a | prod | 2026-01-23T03:42:00 | 0.9 |
|
|
| svc-a | prod | 2026-01-23T03:43:00 | 0.9 |
|
|
| svc-a | prod | 2026-01-23T03:44:00 | 0.9 |
|
|
+-------+------+---------------------+-------------------------------------+
|
|
|
|
DROP FLOW test_tsid;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE IF EXISTS "test_tsid_output";
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE test_tsid;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE phy;
|
|
|
|
Affected Rows: 0
|
|
|