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

〇〇 · 发表于 2021-12-2 15:58

还是duckdb快
D select symbol,sum(price) from t group by symbol order by length(symbol),symbol limit 10;
┌────────┬──────────────────┐
│ SYMBOL │ sum(price) │
├────────┼──────────────────┤
│ A    │ 7416381.01953125 │
│ B    │ 9926.5          │
│ C    │ 5255911.97265625 │
│ D    │ 429701.453125 │
│ E    │ 106474.4375    │
│ F    │ 2166202.9921875  │
│ G    │ 1342301.36328125 │
│ H    │ 245460.4**5625  │
│ J    │ 13203.75       │
│ K    │ 265140.27734375  │
└────────┴──────────────────┘
Run Time: real 0.829 user 6.598842 sys 0.078001

〇〇 · 发表于 2021-12-2 16:01

questdb的自动识别存在问题，把第一列字符串做成单个字符了

qdb=> select distinct symbol from CT_200006.csv;
symbol
--------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
(26 行记录)

时间：2845.892 ms (00:02.846)

〇〇 · 发表于 2021-12-2 16:21

〇〇发表于 2021-12-2 16:01
questdb的自动识别存在问题，把第一列字符串做成单个字符了qdb=> select distinct symbol from CT_200006.c ...

先建表，然后重新导入
drop table "CT_200006.csv";

873.021 ms
create table "CT_200006.csv" (
SYMBOL STRING,
  DATE DATE  ,
  TIME TIMESTAMP  ,
PRICE DOUBLE,
  SIZE    INT,
  G127    INT,
  CORR    INT,
  COND    CHAR,
EX    CHAR,
  TSEQ    INT);

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  |                STRING  |          0  |
|             1  |                                           DATE  |                   DATE  |          0  |
|             2  |                                           TIME  |             TIMESTAMP  |          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  |
+-----------------------------------------------------------------------------------------------------------------+
---group by太慢了，怀疑jvm的保留内存太小了
qdb=> select symbol,sum(price) from CT_200006.csv group by symbol order by symbol limit 10;
symbol |    sum
--------+------------------
A    | 7416381.01953125
AA    | 1246970.23828125
AAABB  |       714.375
AABC |       226.0625
AAC |    541.3359375
AACB |    775.984375
AACE |       8608.375
AAE |    9300.4140625
AAG |    1151.03125
AAGP |    2260.234375
(10 行记录)

时间：113902.190 ms (01:53.902)

〇〇 · 发表于 2021-12-3 16:53

〇〇发表于 2021-12-2 16:21
先建表，然后重新导入drop table "CT_200006.csv";873.021 mscreate table "CT_200006.csv" (SYMBOL STRI ...

数据类型问题，用symbol代替string可以用整数来代替字符
Symbol
QuestDB introduces a data type called SYMBOL; a data structure used to store repetitive strings. Internally, SYMBOL types are stored as a table of integers and their corresponding string values.

Advantages of symbol types#
Greatly improved query performance as string operations compare and write int types instead of string.
Greatly improved storage efficiency as int maps to string types.
Unobtrusive to the user because SQL execution has the same result as handling string values.
Reduced complexity of database schemas by removing the need for explicit additional tables or joins.

qdb=> drop table "CT_200006.csv";
OK
时间：23.794 ms
qdb=> create table "CT_200006.csv" (
qdb(> SYMBOL SYMBOL,
qdb(> DATE DATE  ,
qdb(> TIME TIMESTAMP  ,
qdb(>  PRICE DOUBLE,
qdb(> SIZE    INT,
qdb(> G127    INT,
qdb(> CORR    INT,
qdb(> COND    CHAR,
qdb(>    EX    CHAR,
qdb(> TSEQ    INT);
OK
时间：26.032 ms

D:\>cd sai

D:\sai>D:\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  |                SYMBOL  |          0  |
|             1  |                                           DATE  |                   DATE  |          0  |
|             2  |                                           TIME  |             TIMESTAMP  |          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  |
+-----------------------------------------------------------------------------------------------------------------+

Kernel  Time = 16.411 = 22%
User Time =    0.998 = 1%
Process Time = 17.409 = 23% Virtual  Memory =    3 MB
Global  Time = 72.562 =  100% Physical Memory =    8 MB

qdb=> select symbol,count(1) from CT_200006.csv where symbol like 'A%'group by symbol order by symbol limit 10;
symbol | count
--------+--------
A    | 102460
AA    |  33379
AAABB  | 114
AABC |    35
AAC |    70
AACB |    65
AACE | 648
AAE | 149
AAG |    66
AAGP | 519
(10 行记录)

时间：3750.122 ms (00:03.750)
qdb=> select symbol,sum(price) from CT_200006.csv where symbol like 'A%'group by symbol order by symbol limit 10;
symbol |    sum
--------+------------------
A    | 7416381.01953125
AA    | 1246970.23828125
AAABB  |       714.375
AABC |       226.0625
AAC |    541.3359375
AACB |    775.984375
AACE |       8608.375
AAE |    9300.4140625
AAG |    1151.03125
AAGP |    2260.234375
(10 行记录)

时间：3400.368 ms (00:03.400)

〇〇 · 发表于 2021-12-8 11:00

中文文档
https://github.com/questdb/quest ... 18n/README.zh-cn.md

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

浏览过的版块