0

VectorSQL

VectorSQL是一款免费的物联网与大数据分析DBMS，兼容ClickHouse。「VectorSQL is a free analytics DBMS for IoT & Big Data, compatible with ClickHouse.」

官方网站
Github 资源库
主要指标

Github星跟踪图

VectorSQL

VectorSQL 是一款免费的物联网与大数据分析DBMS，兼容 ClickHouse。

特性

  • 高效能
  • 高可扩展性
  • 高可靠性

服务器

$git clone https://github.com/vectorengine/vectorsql
$cd vectorsql
$make build
$./bin/vectorsql-server -c conf/vectorsql-default.toml
 2020/01/27 19:02:39.245654         [DEBUG]     Database->Attach Table:system.tables, engine:SYSTEM_TABLES <attachTable@database_system.go:116>
 2020/01/27 19:02:39.245670         [DEBUG]     Database->Attach Table:system.databases, engine:SYSTEM_DATABASES <attachTable@database_system.go:116>
 2020/01/27 19:02:39.245680         [INFO]     Database->Load Database:system <loadSystemDatabases@databases.go:110>
 2020/01/27 19:02:39.245794         [INFO]     Listening for connections with native protocol (tcp)::9000 <Start@server.go:33>
 2020/01/27 19:02:39.245806         [INFO]     Servers start... <main@server.go:62>

Client

  • clickhouse-client
$clickhouse-client --compression=0
VectorSQL :) SELECT SUM(IF(status!=200, 1, 0)) AS errors, SUM(IF(status=200, 1, 0)) as success, (errors/COUNT(server)) AS error_rate,(success/COUNT(server)) as success_rate, (SUM(response_time)/COUNT(server)) AS load_avg, MIN(response_time), MAX(response_time), path, server FROM logmock(rows->15) GROUP BY server, path HAVING errors>0 ORDER BY server ASC, load_avg DESC;
SELECT 
    SUM(IF(status != 200, 1, 0)) AS errors, 
    SUM(IF(status = 200, 1, 0)) AS success, 
    errors / COUNT(server) AS error_rate, 
    success / COUNT(server) AS success_rate, 
    SUM(response_time) / COUNT(server) AS load_avg, 
    MIN(response_time), 
    MAX(response_time), 
    path, 
    server
FROM logmock(rows -> 15)
GROUP BY 
    server, 
    path
HAVING errors > 0
ORDER BY 
    server ASC, 
    load_avg DESC
┌─errors─┬─success─┬─error_rate─┬─success_rate─┬─load_avg─┬─MIN(response_time)─┬─MAX(response_time)─┬─path───┬─server──────┐
│      2 │       1 │     0.6667 │       0.3333 │       12 │                 10 │                 13 │ /login │ 192.168.0.1 │
│      1 │       5 │     0.1667 │       0.8333 │  11.1667 │                 10 │                 12 │ /index │ 192.168.0.1 │
│      1 │       3 │       0.25 │         0.75 │    11.25 │                 10 │                 14 │ /index │ 192.168.0.2 │
│      1 │       1 │        0.5 │          0.5 │       11 │                 10 │                 12 │ /login │ 192.168.0.2 │
└────────┴─────────┴────────────┴──────────────┴──────────┴────────────────────┴────────────────────┴────────┴─────────────┘
↓ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) 
4 rows in set. Elapsed: 0.005 sec.
  • http-client
curl -XPOST http://127.0.0.1:8123 -d "SELECT SUM(IF(status!=200, 1, 0)) AS errors, SUM(IF(status=200, 1, 0)) as success, (errors/COUNT(server)) AS error_rate,(success/COUNT(server)) as success_rate, (SUM(response_time)/COUNT(server)) AS load_avg, MIN(response_time), MAX(response_time), path, server FROM logmock(rows->15) GROUP BY server, path HAVING errors>0 ORDER BY server ASC, load_avg DESC"
2    1    0.6667    0.3333    12.0000    10    13    /login    192.168.0.1
1    5    0.1667    0.8333    11.1667    10    12    /index    192.168.0.1
1    3    0.2500    0.7500    11.2500    10    14    /index    192.168.0.2
1    1    0.5000    0.5000    11.0000    10    12    /login    192.168.0.2

查询语言特性

Query language Current version Future versions Example
Scans by Value + + SELECT a,b
Scans by Expression + + SELECT IF(a>2,a,b),SUM(a)
Filter by Value + + WHERE a>10
Filter by Expression + + WHERE a>(b+10)
Group-Aggregate by Value + + GROUP BY a
Group-Aggregate by Expression + + GROUP BY (a+1)
Group-Having by Value + + HAVING count_a>2
Group-Having by Expression + + HAVING (count_a+1)>2
Order by Value + + ORDER BY a desc
Order by Expression + + ORDER BY (a+b)
Window Functions - +
Common Table Expressions - +
Join - +

性能

  • Dataset: 10,000,000 (10 Million)
  • Hardware: 16vCPUx16G KVM Cloud Instance
  • Benchmark
Query Cost(second)
SELECT COUNT(id) FROM testdata 0.269s
SELECT COUNT(id) FROM testdata WHERE id!=0 0.438s
SELECT SUM(data1) FROM testdata 0.287s
SELECT SUM(data1) AS sum, COUNT(data1) AS count, sum/count AS avg FROM testdata 1.814s
SELECT MAX(id), MIN(id) FROM testdata 0.473s
SELECT COUNT(data1) AS count, data1 FROM testdata GROUP BY data1 ORDER BY count DESC LIMIT 10 0.728s
SELECT email FROM testdata WHERE email like '%20@example.com%' LIMIT 1 0.076s
SELECT COUNT(email) FROM testdata WHERE email like '%20@example.com%' 1.470s
SELECT data1 AS x, x - 1, x - 2, x - 3, count(data1) AS c FROM testdata GROUP BY x, x - 1, x - 2, x - 3 ORDER BY c DESC LIMIT 10 2.396s

指标

http://localhost:8080/debug/metrics

主要指标

概览
名称与所有者vectorengine/vectorsql
主编程语言Go
编程语言Makefile (语言数: 5)
平台Linux, Mac
许可证Apache License 2.0
所有者活动
创建于2020-01-19 14:11:49
推送于2021-10-16 08:44:06
最后一次提交2021-10-16 16:44:06
发布数0
用户参与
星数292
关注者数11
派生数53
提交数153
已启用问题?
问题数28
打开的问题数11
拉请求数17
打开的拉请求数0
关闭的拉请求数10
项目设置
已启用Wiki?
已存档?
是复刻?
已锁定?
是镜像?
是私有?

Build Status
codecov.io

VectorSQL

VectorSQL is a free analytics DBMS for IoT & Big Data, compatible with ClickHouse.

Features

  • High Performance
  • High Scalability
  • High Reliability

Server

$git clone https://github.com/vectorengine/vectorsql
$cd vectorsql
$make build
$./bin/vectorsql-server -c conf/vectorsql-default.toml
	
 2020/01/27 19:02:39.245654    	 [DEBUG] 	Database->Attach Table:system.tables, engine:SYSTEM_TABLES <attachTable@database_system.go:116>
 2020/01/27 19:02:39.245670    	 [DEBUG] 	Database->Attach Table:system.databases, engine:SYSTEM_DATABASES <attachTable@database_system.go:116>
 2020/01/27 19:02:39.245680    	 [INFO] 	Database->Load Database:system <loadSystemDatabases@databases.go:110>
 2020/01/27 19:02:39.245794    	 [INFO] 	Listening for connections with native protocol (tcp)::9000 <Start@server.go:33>
 2020/01/27 19:02:39.245806    	 [INFO] 	Servers start... <main@server.go:62>

Client

  • clickhouse-client
$clickhouse-client --compression=0
VectorSQL :) select sum(c1) as c1_sum, count(c1) as c1_count, c1_sum/c1_count as c1_avg, c2, c3 from randtable(rows->1000, c1->'UInt32', c2->'UInt32', c3->'String') where c1>80 and (c1+c2)<500 group by c3 order by c1_count desc, c3 asc limit 10;

SELECT 
    sum(c1) AS c1_sum, 
    count(c1) AS c1_count, 
    c1_sum / c1_count AS c1_avg, 
    c2, 
    c3
FROM randtable(rows -> 1000, c1 -> 'UInt32', c2 -> 'UInt32', c3 -> 'String')
WHERE (c1 > 80) AND ((c1 + c2) < 500)
GROUP BY c3
ORDER BY 
    c1_count DESC, 
    c3 ASC
LIMIT 10

┌─c1_sum─┬─c1_count─┬─c1_avg─┬──c2─┬─c3─────────┐
│    660 │        3 │    220 │ 326 │ string-363 │
│    295 │        1 │    295 │ 175 │ string-1   │
│    110 │        1 │    110 │ 302 │ string-100 │
│    165 │        1 │    165 │ 273 │ string-112 │
│    105 │        1 │    105 │ 241 │ string-125 │
│    132 │        1 │    132 │ 252 │ string-126 │
│    283 │        1 │    283 │  60 │ string-131 │
│    207 │        1 │    207 │ 194 │ string-143 │
│    116 │        1 │    116 │ 251 │ string-144 │
│    125 │        1 │    125 │  67 │ string-15  │
└────────┴──────────┴────────┴─────┴────────────┘
← Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) 
10 rows in set. Elapsed: 0.006 sec.
  • http-client
 curl -XPOST http://127.0.0.1:8123 -d "select sum(c1) as c1_sum, count(c1) as c1_count, c1_sum/c1_count as c1_avg, c2, c3 from randtable(rows->1000, c1->'UInt32', c2->'UInt32', c3->'String') where c1>80 and (c1+c2)<500 group by c3 order by c1_count desc, c3 asc limit 5"
590	2	295	90	string-431
243	2	121.5	346	string-433
239	1	239	255	string-13
108	1	108	318	string-15
187	1	187	78	string-173

Metrics

http://localhost:8080/debug/metrics