Skip to content

Commit fe3db4d

Browse files
b41shb41sh
authored
feat(query): Show Statistics add Virtual Column Stats and Min/Max Fields (databendlabs#18849)
feat(query): Show Statistics add virtual columns
1 parent 692f0ce commit fe3db4d

7 files changed

Lines changed: 99 additions & 20 deletions

File tree

‎src/query/service/tests/it/storages/testdata/columns_table.txt‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -244,10 +244,12 @@ DB.Table: 'system'.'columns', Table: columns-table_id:1, ver:0, Engine: SystemCo
244244
| 'level' | 'system' | 'settings' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
245245
| 'license' | 'system' | 'credits' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
246246
| 'location' | 'system' | 'query_cache' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
247+
| 'max' | 'system' | 'statistics' | 'Nullable(String)' | 'VARCHAR' | '' | '' | 'YES' | '' |
247248
| 'memory_usage' | 'system' | 'processes' | 'Int64' | 'BIGINT' | '' | '' | 'NO' | '' |
248249
| 'message' | 'system' | 'notification_history' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
249250
| 'message_source' | 'system' | 'notification_history' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
250251
| 'metric' | 'system' | 'metrics' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
252+
| 'min' | 'system' | 'statistics' | 'Nullable(String)' | 'VARCHAR' | '' | '' | 'YES' | '' |
251253
| 'miss' | 'system' | 'caches' | 'UInt64' | 'BIGINT UNSIGNED' | '' | '' | 'NO' | '' |
252254
| 'mode' | 'system' | 'streams' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |
253255
| 'mode' | 'system' | 'streams_terse' | 'String' | 'VARCHAR' | '' | '' | 'NO' | '' |

‎src/query/sql/src/planner/binder/ddl/table.rs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -369,6 +369,8 @@ impl Binder {
369369
.with_column("actual_row_count")
370370
.with_column("distinct_count")
371371
.with_column("null_count")
372+
.with_column("min")
373+
.with_column("max")
372374
.with_column("avg_size")
373375
.with_column("histogram");
374376

‎src/query/storages/fuse/src/io/write/stream/cluster_statistics.rs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,7 @@ impl ClusterStatisticsBuilder {
5555
}
5656

5757
let input_schema: Arc<DataSchema> = DataSchema::from(source_schema).into();
58-
let input_filed_len = input_schema.fields.len();
58+
let input_field_len = input_schema.fields.len();
5959

6060
let cluster_keys = table.linear_cluster_keys(ctx.clone());
6161
let mut cluster_key_index = Vec::with_capacity(cluster_keys.len());
@@ -70,7 +70,7 @@ impl ClusterStatisticsBuilder {
7070
Expr::ColumnRef(ColumnRef { id, .. }) => *id,
7171
_ => {
7272
exprs.push(expr);
73-
let offset = input_filed_len + extra_key_num;
73+
let offset = input_field_len + extra_key_num;
7474
extra_key_num += 1;
7575
offset
7676
}

‎src/query/storages/system/src/statistics_table.rs‎

Lines changed: 50 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,8 @@ struct TableColumnStatistics {
6161
actual_row_count: Option<u64>,
6262
distinct_count: Option<u64>,
6363
null_count: Option<u64>,
64+
min: Option<String>,
65+
max: Option<String>,
6466
avg_size: Option<u64>,
6567
histogram: String,
6668
}
@@ -87,6 +89,8 @@ impl StatisticsTable {
8789
"null_count",
8890
TableDataType::Number(NumberDataType::UInt64).wrap_nullable(),
8991
),
92+
TableField::new("min", TableDataType::String.wrap_nullable()),
93+
TableField::new("max", TableDataType::String.wrap_nullable()),
9094
TableField::new(
9195
"avg_size",
9296
TableDataType::Number(NumberDataType::UInt64).wrap_nullable(),
@@ -210,10 +214,49 @@ impl StatisticsTable {
210214
actual_row_count,
211215
distinct_count: column_statistics.and_then(|v| v.ndv),
212216
null_count: column_statistics.map(|v| v.null_count),
213-
histogram,
217+
min: column_statistics
218+
.and_then(|s| s.min.clone())
219+
.map(|v| v.to_string().unwrap()),
220+
max: column_statistics
221+
.and_then(|s| s.max.clone())
222+
.map(|v| v.to_string().unwrap()),
214223
avg_size: columns_statistics.average_size(column_id),
224+
histogram,
215225
})
216226
}
227+
// add virtual column statistics
228+
let table_info = table.get_table_info();
229+
if let Some(virtual_schema) = &table_info.meta.virtual_schema {
230+
for virtual_field in virtual_schema.fields() {
231+
if let (Ok(source_field), Some(column_statistics)) = (
232+
schema.field_of_column_id(virtual_field.source_column_id),
233+
columns_statistics.column_statistics(virtual_field.column_id),
234+
) {
235+
let column_name =
236+
format!("{}{}", source_field.name, virtual_field.name);
237+
rows.push(TableColumnStatistics {
238+
database_name: database.clone(),
239+
table_name: table.name().into(),
240+
column_name,
241+
stats_row_count,
242+
actual_row_count,
243+
distinct_count: column_statistics.ndv,
244+
null_count: Some(column_statistics.null_count),
245+
min: column_statistics
246+
.min
247+
.clone()
248+
.map(|v| v.to_string().unwrap()),
249+
max: column_statistics
250+
.max
251+
.clone()
252+
.map(|v| v.to_string().unwrap()),
253+
avg_size: columns_statistics
254+
.average_size(virtual_field.column_id),
255+
histogram: "".to_string(),
256+
})
257+
}
258+
}
259+
}
217260
}
218261
}
219262
}
@@ -253,6 +296,8 @@ impl AsyncSystemTable for StatisticsTable {
253296
let mut actual_row_counts = Vec::with_capacity(rows.len());
254297
let mut distinct_counts = Vec::with_capacity(rows.len());
255298
let mut null_counts = Vec::with_capacity(rows.len());
299+
let mut mins = Vec::with_capacity(rows.len());
300+
let mut maxes = Vec::with_capacity(rows.len());
256301
let mut avg_sizes = Vec::with_capacity(rows.len());
257302
let mut histograms = Vec::with_capacity(rows.len());
258303
for row in rows {
@@ -263,6 +308,8 @@ impl AsyncSystemTable for StatisticsTable {
263308
actual_row_counts.push(row.actual_row_count);
264309
distinct_counts.push(row.distinct_count);
265310
null_counts.push(row.null_count);
311+
mins.push(row.min);
312+
maxes.push(row.max);
266313
avg_sizes.push(row.avg_size);
267314
histograms.push(row.histogram);
268315
}
@@ -275,6 +322,8 @@ impl AsyncSystemTable for StatisticsTable {
275322
UInt64Type::from_opt_data(actual_row_counts),
276323
UInt64Type::from_opt_data(distinct_counts),
277324
UInt64Type::from_opt_data(null_counts),
325+
StringType::from_opt_data(mins),
326+
StringType::from_opt_data(maxes),
278327
UInt64Type::from_opt_data(avg_sizes),
279328
StringType::from_data(histograms),
280329
]))

‎tests/sqllogictests/suites/base/06_show/06_0025_show_statistics.test‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -13,30 +13,30 @@ CREATE TABLE showstatistics.t1(c1 int not null) ENGINE = Null;
1313
query T
1414
show statistics from table showstatistics.t1;
1515
----
16-
showstatistics t1 c1 NULL NULL NULL NULL NULL (empty)
16+
showstatistics t1 c1 NULL NULL NULL NULL NULL NULL NULL (empty)
1717

1818
statement ok
1919
CREATE TABLE IF NOT EXISTS showstatistics.t2(c1 int);
2020

2121
query T
2222
show statistics;
2323
----
24-
showstatistics t1 c1 NULL NULL NULL NULL NULL (empty)
25-
showstatistics t2 c1 0 0 NULL NULL NULL (empty)
24+
showstatistics t1 c1 NULL NULL NULL NULL NULL NULL NULL (empty)
25+
showstatistics t2 c1 0 0 NULL NULL NULL NULL NULL (empty)
2626

2727
statement ok
2828
insert into showstatistics.t2 values(1),(2);
2929

3030
query T
3131
show statistics from table showstatistics.t2;
3232
----
33-
showstatistics t2 c1 2 2 2 0 4 (empty)
33+
showstatistics t2 c1 2 2 2 0 1 2 4 (empty)
3434

3535
query T
3636
show statistics from database showstatistics;
3737
----
38-
showstatistics t1 c1 NULL NULL NULL NULL NULL (empty)
39-
showstatistics t2 c1 2 2 2 0 4 (empty)
38+
showstatistics t1 c1 NULL NULL NULL NULL NULL NULL NULL (empty)
39+
showstatistics t2 c1 2 2 2 0 1 2 4 (empty)
4040

4141
statement ok
4242
set enable_analyze_histogram=1;
@@ -50,7 +50,7 @@ analyze table showstatistics.t2;
5050
query T
5151
show statistics from table showstatistics.t2;
5252
----
53-
showstatistics t2 c1 2 2 2 0 4 [bucket id: 0, min: "1", max: "1", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0]
53+
showstatistics t2 c1 2 2 2 0 1 2 4 [bucket id: 0, min: "1", max: "1", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0]
5454

5555
statement ok
5656
DROP DATABASE showstatistics

‎tests/sqllogictests/suites/base/09_fuse_engine/09_0020_analyze.test‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -199,17 +199,17 @@ delete from t2 where a=1;
199199
query T
200200
show statistics from table db_09_0020.t2;
201201
----
202-
db_09_0020 t2 a 3 3 2 0 4 (empty)
203-
db_09_0020 t2 b 3 3 2 0 4 (empty)
202+
db_09_0020 t2 a 3 3 2 0 2 4 4 (empty)
203+
db_09_0020 t2 b 3 3 2 0 2 4 4 (empty)
204204

205205
statement ok
206206
analyze table t2;
207207

208208
query T
209209
show statistics from table db_09_0020.t2;
210210
----
211-
db_09_0020 t2 a 3 3 2 0 4 [bucket id: 0, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 2, min: "4", max: "4", ndv: 1.0, count: 1.0]
212-
db_09_0020 t2 b 3 3 2 0 4 [bucket id: 0, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 2, min: "4", max: "4", ndv: 1.0, count: 1.0]
211+
db_09_0020 t2 a 3 3 2 0 2 4 4 [bucket id: 0, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 2, min: "4", max: "4", ndv: 1.0, count: 1.0]
212+
db_09_0020 t2 b 3 3 2 0 2 4 4 [bucket id: 0, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 1, min: "2", max: "2", ndv: 1.0, count: 1.0], [bucket id: 2, min: "4", max: "4", ndv: 1.0, count: 1.0]
213213

214214
query I
215215
select count() from fuse_snapshot('db_09_0020','t2');
@@ -278,26 +278,26 @@ update t4 set a = 4 where b = 'c';
278278
query T
279279
show statistics from table db_09_0020.t4;
280280
----
281-
db_09_0020 t4 a 5 4 4 0 4 (empty)
282-
db_09_0020 t4 b 5 4 3 0 13 (empty)
281+
db_09_0020 t4 a 5 4 4 0 1 4 4 (empty)
282+
db_09_0020 t4 b 5 4 3 0 a c 13 (empty)
283283

284284
statement ok
285285
alter table t4 set options(enable_auto_analyze = 1);
286286

287287
query T
288288
show statistics from table db_09_0020.t4;
289289
----
290-
db_09_0020 t4 a 4 4 4 0 4 (empty)
291-
db_09_0020 t4 b 4 4 3 0 13 (empty)
290+
db_09_0020 t4 a 4 4 4 0 1 4 4 (empty)
291+
db_09_0020 t4 b 4 4 3 0 a c 13 (empty)
292292

293293
statement ok
294294
delete from t4 where a = 4;
295295

296296
query T
297297
show statistics from table db_09_0020.t4;
298298
----
299-
db_09_0020 t4 a 3 3 3 0 4 (empty)
300-
db_09_0020 t4 b 3 3 2 0 13 (empty)
299+
db_09_0020 t4 a 3 3 3 0 1 3 4 (empty)
300+
db_09_0020 t4 b 3 3 2 0 a b 13 (empty)
301301

302302
statement ok
303303
DROP TABLE t4 all;

‎tests/sqllogictests/suites/ee/01_ee_system/01_0002_virtual_column.test‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -335,6 +335,21 @@ data['likes'] 2 0 1 (empty)
335335
data['tags'][0] 2 0 3 (empty)
336336
data['tags'][1] 2 0 4 (empty)
337337

338+
query TTTIIIITTIT
339+
SHOW STATISTICS FROM TABLE test_virtual_column.tweets;
340+
----
341+
test_virtual_column tweets data 10 10 NULL NULL NULL NULL NULL (empty)
342+
test_virtual_column tweets data['create'] 10 10 10 0 1/08 6/07 16 (empty)
343+
test_virtual_column tweets data['id'] 10 10 10 0 1 10 8 (empty)
344+
test_virtual_column tweets data['likes'] 10 10 2 0 10 25 1 (empty)
345+
test_virtual_column tweets data['replies'] 10 10 7 3 0 9 8 (empty)
346+
test_virtual_column tweets data['tags'][0] 10 10 2 0 good new 3 (empty)
347+
test_virtual_column tweets data['tags'][1] 10 10 2 0 interesting popular 4 (empty)
348+
test_virtual_column tweets data['text'] 10 10 7 0 a z 13 (empty)
349+
test_virtual_column tweets data['user']['id'] 10 10 6 0 1 7 8 (empty)
350+
test_virtual_column tweets id 10 10 10 0 1 10 4 (empty)
351+
352+
338353
statement ok
339354
UPDATE tweets SET data = '{"id":4, "create": "1/08", "text": "aa", "user": {"id": 1}, "replies": 10}' WHERE id = 4;
340355

@@ -348,6 +363,17 @@ data['replies'] 7 3 8 (empty)
348363
data['text'] 8 0 13 (empty)
349364
data['user']['id'] 4 0 8 (empty)
350365

366+
query TTTIIIITTIT
367+
SHOW STATISTICS FROM TABLE test_virtual_column.tweets;
368+
----
369+
test_virtual_column tweets data 10 10 NULL NULL NULL NULL NULL (empty)
370+
test_virtual_column tweets data['create'] 10 10 10 0 1/08 6/07 16 (empty)
371+
test_virtual_column tweets data['id'] 10 10 10 0 1 10 8 (empty)
372+
test_virtual_column tweets data['replies'] 10 10 7 3 0 10 8 (empty)
373+
test_virtual_column tweets data['text'] 10 10 8 0 a z 13 (empty)
374+
test_virtual_column tweets data['user']['id'] 10 10 4 0 1 7 8 (empty)
375+
test_virtual_column tweets id 10 10 10 0 1 10 4 (empty)
376+
351377
query IITTIITFIT
352378
select id, data['id'], data['create'], data['text'], data['user']['id'], data['replies'], data['geo'], data['geo']['lat'], data['likes'], data['tags'] from tweets order by id;
353379
----

0 commit comments

Comments
 (0)