时序数据库questdb,据他们自己说比clickhouse还快

显示全部楼层 · 2012-5-21 10:19:41

https://questdb.io/
https://www.sohu.com/a/469960964_374240

千问 · 2012-5-21 10:19:41

windows版使用方式
下载包含java rt的安装包，解压缩后，执行questdb.exe 让防火墙允许
在浏览器输入http://localhost:9000/
或者用postgresql的psql命令行工具
D:\pgsql141\bin>psql -h localhost -p 8812 -U admin -d qdb
口令输入quest即可

千问 · 2012-5-21 10:19:41

操作数据库命令参考 https://questdb.io/docs/get-started/first-database
select * from可以省去，比如
select * from a，只要输入a;
注意count不能带参数

千问 · 2012-5-21 10:19:41

显示图形的例子
http://blog.hubwiz.com/2021/04/1 ... um-data-to-questdb/

千问 · 2012-5-21 10:19:41

https://my.oschina.net/u/4865736/blog/5039323DolphinDB

千问 · 2012-5-21 10:19:41

https://my.oschina.net/u/4865736/blog/4783753 语言

千问 · 2012-5-21 10:19:41

用 curl执行sql，返回json
--建表
curl -G --data-urlencode "query=create table t(a string,b int);"http://localhost:9000/exec
{"ddl":"OK"}
--插入
curl -G --data-urlencode "query=insert into t values('a',1),('b',2),('c',3);"http://localhost:9000/exec
{"ddl":"OK"}
--查询
curl -G --data-urlencode "query=select * from t;"http://localhost:9000/exec
{"query":"select * from t;","columns":[{"name":"a","type":"STRING"},{"name":"b","type":"INT"}],"dataset":[["a",1]],"count":1}

千问 · 2012-5-21 10:19:41

用curl导入csv文件
先用duckdb产生一个csv文件
D copy (select i::varchar j,i from range(1000000)t(i))to 'd:/t1m.csv';
Run Time: real 0.193 user 0.187201 sys 0.015600
--导入文件，自动判断类型
curl -F data=@d:/t1m.csv http://localhost:9000/imp
+-----------------------------------------------------------------------------------------------------------------+
|Location:|
t1m.csv|Pattern| Locale|Errors|
| Partition by|
NONE|
| |
|
|Timestamp|
NONE|
| |
|
+-----------------------------------------------------------------------------------------------------------------+
| Rows handled|
1000000|
| |
|
|Rows imported|
1000000|
| |
|
+-----------------------------------------------------------------------------------------------------------------+
|
0|
f0|
INT| 0|
|
1|
f1|
INT| 0|
+-----------------------------------------------------------------------------------------------------------------+
--文件名就是导入后的表名
curl -G --data-urlencode "query=select sum(f1) from t1m.csv;"http://localhost:9000/exec
{"query":"select sum(f1) from t1m.csv;","columns":[{"name":"sum","type":"LONG"}],"dataset":[[499999500000]],"count":1}
--查询表中原始数据
D:\sai>curl -G --data-urlencode "query=select * from t1m.csv limit 2;"http://localhost:9000/exec
{"query":"select * from t1m.csv limit 2;","columns":[{"name":"f0","type":"INT"},{"name":"f1","type":"INT"}],"dataset":[[0,0],[1,1]],"count":2}

千问 · 2012-5-21 10:19:41

用psql访问
D:\pgsql141\bin>psql-h localhost -p 8812 -U admin -d qdb
用户 admin 的口令：
psql (14.1, 服务器 11.3)
输入 "help" 来获取帮助信息.
qdb=> select * from t;
a | b
---+---
a | 1
(1 行记录)

qdb=> \timing on
启用计时功能.
qdb=> select sum(f1) from t1m.csv;
sum
--------------
499999500000
(1 行记录)

时间：3.857 ms

千问 · 2012-5-21 10:19:41

本帖最后由〇〇于 2021-12-2 15:55 编辑
导入大文件
CT_200006.csv 2.63 GB (2,827,261,521 字节)
D:\sai>\timer64 curl -F data=@d:/nyx/CT_200006.csv http://localhost:9000/imp
+-----------------------------------------------------------------------------------------------------------------+
|Location:|
CT_200006.csv|Pattern| Locale|Errors|
| Partition by|
NONE|
| |
|
|Timestamp|
NONE|
| |
|
+-----------------------------------------------------------------------------------------------------------------+
| Rows handled|
65252260|
| |
|
|Rows imported|
65252260|
| |
|
+-----------------------------------------------------------------------------------------------------------------+
|
0|
SYMBOL|
CHAR| 0|
|
1|
DATE|
INT| 0|
|
2|
TIME|
STRING| 0|
|
3|
PRICE|
DOUBLE| 0|
|
4|
SIZE|
INT| 0|
|
5|
G127|
INT| 0|
|
6|
CORR|
INT| 0|
|
7|
COND|
CHAR| 0|
|
8|
EX|
CHAR| 0|
|
9|
TSEQ|
INT| 0|
+-----------------------------------------------------------------------------------------------------------------+

KernelTime =17.206 = 21%
UserTime = 1.045 =1%
Process Time =18.252 = 22%VirtualMemory =3 MB
GlobalTime =81.233 =100%Physical Memory =8 MB
D:\sai>d:\pgsql141\bin\psql-h localhost -p 8812 -U admin -d qdb
用户 admin 的口令：
psql (14.1, 服务器 11.3)
输入 "help" 来获取帮助信息.
qdb=> \timing on
启用计时功能.
qdb=> select count from CT_200006.csv;
count
----------
65252260
(1 行记录)

时间：2.336 ms
qdb=> select sum(price) from CT_200006.csv;
sum
----------------------
3.7367271698671875E9
(1 行记录)

时间：140.294 ms
qdb=> select symbol,sum(price) from CT_200006.csv group by symbol limit 10;
symbol | sum
--------+----------------------
A| 4.1250529360546875E8
B| 2.1783217472265625E8
C| 3.6344104596484375E8
D|7.864782123046875E7
E|1.467532962265625E8
F|5.3497011515625E7
G|8.639807705078125E7
H|6.423442971484375E7
I| 2.5674422417578125E8
J| 1.8671941389453125E8
(10 行记录)

时间：4337.346 ms (00:04.337)