0

VectorSQL

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

Official Site
Github repo
Main metrics

Github stars Tracking Chart

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

Main metrics

Overview
Name With Ownervectorengine/vectorsql
Primary LanguageGo
Program languageMakefile (Language Count: 5)
PlatformLinux, Mac
License:Apache License 2.0
所有者活动
Created At2020-01-19 14:11:49
Pushed At2021-10-16 08:44:06
Last Commit At2021-10-16 16:44:06
Release Count0
用户参与
Stargazers Count292
Watchers Count11
Fork Count53
Commits Count153
Has Issues Enabled
Issues Count28
Issue Open Count11
Pull Requests Count17
Pull Requests Open Count0
Pull Requests Close Count10
项目设置
Has Wiki Enabled
Is Archived
Is Fork
Is Locked
Is Mirror
Is Private

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