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