一个俄国的列式数据库clickhouse

〇〇 · 发表于 2019-12-27 09:36

本帖最后由〇〇于 2019-12-27 10:14 编辑

定长字符串诸多不便
[actian@localhost ~]$ clickhouse-client --query="select Carrier from datasets.ontime where Carrier like 'N2A%' limit 1"
Received exception from server (version 19.17.5):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type FixedString(2) of argument of function like.
[actian@localhost ~]$ clickhouse-client --query="select Carrier from datasets.ontime where substr(Carrier , 'N2A')>0 limit 1"
Received exception from server (version 19.17.5):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type String of second argument of function substring.
[actian@localhost ~]$ clickhouse-client --query="select Carrier from datasets.ontime where locate(Carrier , 'N2A')>0 limit 1"
Received exception from server (version 19.17.5):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type FixedString(2) of argument of function position.
[actian@localhost ~]$ clickhouse-client
ClickHouse client version 19.17.5.18 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 19.17.5 revision 54428.

localhost

select locate('ABC','A');

SELECT locate('ABC', 'A')

┌─locate('ABC', 'A')─┐
│ 1 │
└────────────────────┘

1 rows in set. Elapsed: 0.014 sec.

localhost

select UniqueCarrier from datasets.ontime where empty(UniqueCarrier)=0 limit 1;

SELECT UniqueCarrier
FROM datasets.ontime
WHERE empty(UniqueCarrier) = 0
LIMIT 1

┌─UniqueCarrier─┐
│ AS │
└───────────────┘

1 rows in set. Elapsed: 0.064 sec. Processed 3.54 million rows, 24.77 MB (55.04 million rows/s., 385.27 MB/s.)

localhost

select length(UniqueCarrier) from datasets.ontime where empty(UniqueCarrier)=0 limit 1;

SELECT length(UniqueCarrier)
FROM datasets.ontime
WHERE empty(UniqueCarrier) = 0
LIMIT 1

┌─length(UniqueCarrier)─┐
│ 7 │
└───────────────────────┘

1 rows in set. Elapsed: 0.052 sec. Processed 3.41 million rows, 23.86 MB (65.33 million rows/s., 457.32 MB/s.)

localhost

select length(toString(UniqueCarrier)),length(UniqueCarrier) from datasets.ontime where empty(UniqueCarrier)=0 limit 1;

SELECT
length(toString(UniqueCarrier)),
length(UniqueCarrier)
FROM datasets.ontime
WHERE empty(UniqueCarrier) = 0
LIMIT 1

┌─length(toString(UniqueCarrier))─┬─length(UniqueCarrier)─┐
│ 2 │ 7 │
└─────────────────────────────────┴───────────────────────┘

1 rows in set. Elapsed: 0.097 sec. Processed 2.56 million rows, 17.89 MB (26.22 million rows/s., 183.56 MB/s.)

〇〇 · 发表于 2019-12-27 20:59

开源OLAP引擎哪个快？ (Presto、HAWQ、ClickHouse、GreenPlum)
https://zhuanlan.zhihu.com/p/54907288
Clickhouse,TiDB,Greenplum哪个更适合作为AWS redshift 的替代品?
https://www.zhihu.com/question/67356221

〇〇 · 发表于 2019-12-27 21:08

http://jackpgao.github.io/2018/02/04/ClickHouse-Use-MySQL-Data/
使用ClickHouse一键接管MySQL数据分析

http://jackpgao.github.io/2018/0 ... 12-13-improvements/
ClickHouse 18.12.13-2018-09-10版本新特性解析

https://fashengba.com/post/http- ... ing-clickhouse.html
使用ClickHouse对每秒6百万次请求进行HTTP分析

〇〇 · 发表于 2020-6-25 00:11

清华源
https://mirrors.tuna.tsinghua.edu.cn/clickhouse/rpm/stable/x86_64

Clickhouse 镜像使用帮助
Debian/Ubuntu 用户
新建 /etc/apt/sources.list.d/clickhouse.list，内容为

deb https://mirrors.tuna.tsinghua.edu.cn/clickhouse/deb/stable/ main/
RHEL/CentOS 用户
新建 /etc/yum.repos.d/clickhouse.repo，内容为

[repo.yandex.ru_clickhouse_rpm_stable_x86_64]
name=clickhouse stable
baseurl=https://mirrors.tuna.tsinghua.edu.cn/clickhouse/rpm/stable/x86_64
enabled=1

〇〇 · 发表于 2020-6-25 13:43

做星型测试
https://clickhouse.tech/docs/zh/ ... tasets/star-schema/
下载源代码
[root@VM_0_13_centos tmp]# wget https://github.com/vadimtk/ssb-dbgen/archive/master.zip
--2020-06-25 13:23:31--  https://github.com/vadimtk/ssb-dbgen/archive/master.zip
Resolving github.com (github.com)... 13.250.177.223
Connecting to github.com (github.com)|13.250.177.223|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/vadimtk/ssb-dbgen/zip/master [following]
--2020-06-25 13:23:32--  https://codeload.github.com/vadimtk/ssb-dbgen/zip/master
Resolving codeload.github.com (codeload.github.com)... 13.229.189.0
Connecting to codeload.github.com (codeload.github.com)|13.229.189.0|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: ‘master.zip’

[                <=>                                                                                                          ] 103,046    8.56KB/s in 12s

2020-06-25 13:23:48 (8.59 KB/s) - ‘master.zip’ saved [103046]

[root@VM_0_13_centos tmp]# mkdir ssb
[root@VM_0_13_centos tmp]# cd ssb
[root@VM_0_13_centos ssb]# unzip ../master.zip
Archive:  ../master.zip
0741e06d4c3e811bcec233378a39db2fc0be5d79
creating: ssb-dbgen-master/
  inflating: ssb-dbgen-master/.gitignore
  inflating: ssb-dbgen-master/BUGS
  inflating: ssb-dbgen-master/CHANGES
  inflating: ssb-dbgen-master/HISTORY
  inflating: ssb-dbgen-master/PORTING.NOTES
  inflating: ssb-dbgen-master/README
  inflating: ssb-dbgen-master/TPCH_README
  inflating: ssb-dbgen-master/bcd2.c
  inflating: ssb-dbgen-master/bcd2.h
  inflating: ssb-dbgen-master/bm_utils.c
  inflating: ssb-dbgen-master/build.c
  inflating: ssb-dbgen-master/config.h
  inflating: ssb-dbgen-master/dists.dss
  inflating: ssb-dbgen-master/driver.c
  inflating: ssb-dbgen-master/dss.ddl
  inflating: ssb-dbgen-master/dss.h
  inflating: ssb-dbgen-master/dss.ri
  inflating: ssb-dbgen-master/dsstypes.h
  inflating: ssb-dbgen-master/history.html
  inflating: ssb-dbgen-master/load_stub.c
  inflating: ssb-dbgen-master/makefile
  inflating: ssb-dbgen-master/makefile.suite
  inflating: ssb-dbgen-master/makefile_win
  inflating: ssb-dbgen-master/permute.c
  inflating: ssb-dbgen-master/permute.h
  inflating: ssb-dbgen-master/print.c
  inflating: ssb-dbgen-master/qgen.c
  inflating: ssb-dbgen-master/rnd.c
  inflating: ssb-dbgen-master/rnd.h
  inflating: ssb-dbgen-master/shared.h
  inflating: ssb-dbgen-master/speed_seed.c
  inflating: ssb-dbgen-master/text.c
  inflating: ssb-dbgen-master/tpcd.h
  inflating: ssb-dbgen-master/varsub.c
[root@VM_0_13_centos ssb]# ls
ssb-dbgen-master
[root@VM_0_13_centos ssb]# cd ssb*
编译
[root@VM_0_13_centos ssb-dbgen-master]# make
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o build.o build.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o driver.o driver.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o bm_utils.o bm_utils.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o rnd.o rnd.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o print.o print.c
print.c: In function ‘dbg_print’:
print.c:138:27: warning: cast from pointer to integer of different size [-Wpointer-to-int-cast]
fprintf(target, "%c ", (char)data);
                        ^
print.c:140:26: warning: cast from pointer to integer of different size [-Wpointer-to-int-cast]
fprintf(target, "%c", (char)data);
                        ^
In file included from print.c:18:0:
print.c: In function ‘pr_line’:
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:265:9: note: in expansion of macro ‘PR_INT’
      PR_INT(fp_l, o->lineorders.linenumber);
      ^
print.c: In function ‘pr_date’:
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:663:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->year);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:664:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->yearmonthnum);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:666:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->daynuminweek);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:667:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->daynuminmonth);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:668:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->daynuminyear);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:669:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->monthnuminyear);
   ^
dss.h:510:48: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
#define PR_INT(f, str) dbg_print(DT_INT, f, (void *)str, 0, 1)
                                             ^
print.c:670:5: note: in expansion of macro ‘PR_INT’
   PR_INT(d_fp, d->weeknuminyear);
   ^
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o load_stub.o load_stub.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o bcd2.o bcd2.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o speed_seed.o speed_seed.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o text.o text.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o permute.o permute.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM  -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o -lm
bm_utils.o: In function `yes_no':
bm_utils.c.text+0x3c): warning: the `gets' function is dangerous and should not be used.
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o qgen.o qgen.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM -c -o varsub.o varsub.c
gcc -O -DDBNAME=\"dss\" -DLINUX -DDB2  -DSSBM  -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o -lm
bm_utils.o: In function `yes_no':
bm_utils.c.text+0x3c): warning: the `gets' function is dangerous and should not be used.
[root@VM_0_13_centos ssb-dbgen-master]# ll
total 672
-rw-r--r-- 1 root root  5688 Apr 11  2017 bcd2.c
-rw-r--r-- 1 root root 493 Apr 11  2017 bcd2.h
-rw-r--r-- 1 root root  4000 Jun 25 13:25 bcd2.o
-rw-r--r-- 1 root root 13464 Apr 11  2017 bm_utils.c
-rw-r--r-- 1 root root 10712 Jun 25 13:25 bm_utils.o
-rw-r--r-- 1 root root 27733 Apr 11  2017 BUGS
-rw-r--r-- 1 root root 20182 Apr 11  2017 build.c
-rw-r--r-- 1 root root 15448 Jun 25 13:25 build.o
-rw-r--r-- 1 root root 769 Apr 11  2017 CHANGES
-rw-r--r-- 1 root root  5256 Apr 11  2017 config.h
-rwxr-xr-x 1 root root 61808 Jun 25 13:25 dbgen
-rw-r--r-- 1 root root 11439 Apr 11  2017 dists.dss
-rw-r--r-- 1 root root 28561 Apr 11  2017 driver.c
-rw-r--r-- 1 root root 37672 Jun 25 13:25 driver.o
-rw-r--r-- 1 root root  3875 Apr 11  2017 dss.ddl
-rw-r--r-- 1 root root 16191 Apr 11  2017 dss.h
-rw-r--r-- 1 root root  2072 Apr 11  2017 dss.ri
-rw-r--r-- 1 root root  7960 Apr 11  2017 dsstypes.h
-rw-r--r-- 1 root root 23726 Apr 11  2017 HISTORY
-rw-r--r-- 1 root root 25982 Apr 11  2017 history.html
-rw-r--r-- 1 root root  4126 Apr 11  2017 load_stub.c
-rw-r--r-- 1 root root  6088 Jun 25 13:25 load_stub.o
-rw-r--r-- 1 root root  4109 Apr 11  2017 makefile
-rw-r--r-- 1 root root  4095 Apr 11  2017 makefile.suite
-rw-r--r-- 1 root root  3016 Apr 11  2017 makefile_win
-rw-r--r-- 1 root root  3296 Apr 11  2017 permute.c
-rw-r--r-- 1 root root  2974 Apr 11  2017 permute.h
-rw-r--r-- 1 root root  3176 Jun 25 13:25 permute.o
-rw-r--r-- 1 root root  9178 Apr 11  2017 PORTING.NOTES
-rw-r--r-- 1 root root 22264 Apr 11  2017 print.c
-rw-r--r-- 1 root root 18736 Jun 25 13:25 print.o
-rwxr-xr-x 1 root root 57576 Jun 25 13:25 qgen
-rw-r--r-- 1 root root 13904 Apr 11  2017 qgen.c
-rw-r--r-- 1 root root 31968 Jun 25 13:25 qgen.o
-rw-r--r-- 1 root root  2974 Apr 11  2017 README
-rw-r--r-- 1 root root  6848 Apr 11  2017 rnd.c
-rw-r--r-- 1 root root  4117 Apr 11  2017 rnd.h
-rw-r--r-- 1 root root  5768 Jun 25 13:25 rnd.o
-rw-r--r-- 1 root root  2739 Apr 11  2017 shared.h
-rw-r--r-- 1 root root  7835 Apr 11  2017 speed_seed.c
-rw-r--r-- 1 root root  7288 Jun 25 13:25 speed_seed.o
-rw-r--r-- 1 root root  6478 Apr 11  2017 text.c
-rw-r--r-- 1 root root  3968 Jun 25 13:25 text.o
-rw-r--r-- 1 root root  3079 Apr 11  2017 tpcd.h
-rw-r--r-- 1 root root 17256 Apr 11  2017 TPCH_README
-rw-r--r-- 1 root root  9918 Apr 11  2017 varsub.c
-rw-r--r-- 1 root root 17200 Jun 25 13:25 varsub.o
生成数据
[root@VM_0_13_centos ssb-dbgen-master]# ./dbgen -s 1000 -T c
SSBM (Star Schema Benchmark) Population Generator (Version 1.0.0)
Copyright Transaction Processing Performance Council 1994 - 2000
[root@VM_0_13_centos ssb-dbgen-master]# ll c*
-rw-r--r-- 1 root root    5256 Apr 11  2017 config.h
-rw-r--r-- 1 root root 3345252364 Jun 25 13:26 customer.tbl
[root@VM_0_13_centos ssb-dbgen-master]# clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.

VM_0_13_centos CREATE TABLE customer
:-] (
:-]       C_CUSTKEY    UInt32,
:-]       C_NAME       String,
:-]       C_ADDRESS    String,
:-]       C_CITY       LowCardinality(String),
:-]       C_NATION       LowCardinality(String),
:-]       C_REGION       LowCardinality(String),
:-]       C_PHONE       String,
:-]       C_MKTSEGMENT LowCardinality(String)
:-] )
:-] ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE customer
(
`C_CUSTKEY` UInt32,
`C_NAME` String,
`C_ADDRESS` String,
`C_CITY` LowCardinality(String),
`C_NATION` LowCardinality(String),
`C_REGION` LowCardinality(String),
`C_PHONE` String,
`C_MKTSEGMENT` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY C_CUSTKEY

Ok.

0 rows in set. Elapsed: 0.005 sec.

VM_0_13_centos exit;
Bye.
加载数据
[root@VM_0_13_centos ssb-dbgen-master]# clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
查询
[root@VM_0_13_centos ssb-dbgen-master]# clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.

VM_0_13_centos select count(*) form customer;

Syntax error: failed at position 22:

select count(*) form customer;

Expected one of: LIMIT, WHERE, HAVING, INTO OUTFILE, GROUP BY, PREWHERE, Comma, ORDER BY, SETTINGS, UNION ALL, FROM, FORMAT, WITH, token

VM_0_13_centos select count(*)cnt from customer;

SELECT count(*) AS cnt
FROM customer

┌──────cnt─┐
│ 30000000 │
└──────────┘

1 rows in set. Elapsed: 0.002 sec.

VM_0_13_centos select
:-] count(distcnt C_NAME)cnt_name,
:-] count(distcnt C_CITY)cnt_city,
:-] count(distcnt C_NATION)cnt_nation,
:-] count(distcnt C_REGION)cnt_region
:-] from customer;

Syntax error: failed at position 22:

select count(distcnt C_NAME)cnt_name, count(distcnt C_CITY)cnt_city, count(distcnt C_NATION)cnt_nation, count(distcnt C_REGION)cnt_region from customer;

Expected one of: LIKE, GLOBAL NOT IN, AS, IS, OR, QuestionMark, BETWEEN, NOT LIKE, AND, Comma, alias, IN, Dot, NOT, Arrow, token, NOT IN, GLOBAL IN

VM_0_13_centos select
:-] count(distinct C_NAME)cnt_name,
:-] count(distinct C_CITY)cnt_city,
:-] count(distinct C_NATION)cnt_nation,
:-] count(distinct C_REGION)cnt_region
:-] from customer;

SELECT
countDistinct(C_NAME) AS cnt_name,
countDistinct(C_CITY) AS cnt_city,
countDistinct(C_NATION) AS cnt_nation,
countDistinct(C_REGION) AS cnt_region
FROM customer

┌─cnt_name─┬─cnt_city─┬─cnt_nation─┬─cnt_region─┐
│ 30000000 │    250 │       25 │       5 │
└──────────┴──────────┴────────────┴────────────┘

1 rows in set. Elapsed: 2.973 sec. Processed 30.00 million rows, 904.85 MB (10.09 million rows/s., 304.31 MB/s.)

VM_0_13_centos

〇〇 · 发表于 2020-6-25 13:51

代码中太多笑脸符号，截个图

第一次见这样的union all

〇〇 · 发表于 2020-6-26 17:08

本帖最后由〇〇于 2020-6-27 18:14 编辑

clickhouse 还支持mysql客户端
[root@VM_0_13_centos ~]# mysql --protocol tcp -u default -P 9004
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 20.4.5.36-ClickHouse 67108864

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------------------+
| name                         |
+--------------------------------+
| _temporary_and_external_tables |
| default                      |
| system                      |
+--------------------------------+
3 rows in set (0.00 sec)
Read 3 rows, 316.00 B in 0.000 sec., 8203 rows/sec., 843.89 KiB/sec.

mysql> use default;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select
-> left(toString(ad_code),2) code,
->  sum(case when (create_time > '2020-04-30 23:59:59') then 1 else 0 end ) new_r_s,
->  sum(case admin_status when 4 then 1 else 0 end ) report_r_s,
->  sum(case admin_status when 3 then 1 else 0 end ) audit_success_r_s,
->  sum(case admin_status when 2 then 1 else 0 end ) audit_failed_r_s,
->  sum(case admin_status when 1 then 1 else 0 end ) not_reported_r_s
->  from household_short
-> group by left(toString(ad_code),2)
-> order by code
-> ;
31 rows in set (2.16 sec)
Read 377653290 rows, 4.57 GiB in 2.155 sec., 175238155 rows/sec., 2.12 GiB/sec.
mysql> select
-> floor(ad_code/10000) code,
->  sum(case when (create_time > '2020-04-30 23:59:59') then 1 else 0 end ) new_r_s,
->  sum(case admin_status when 4 then 1 else 0 end ) report_r_s,
->  sum(case admin_status when 3 then 1 else 0 end ) audit_success_r_s,
->  sum(case admin_status when 2 then 1 else 0 end ) audit_failed_r_s,
->  sum(case admin_status when 1 then 1 else 0 end ) not_reported_r_s
->  from household_short
-> group by floor(ad_code/10000)
-> order by code
-> ;
31 rows in set (1.35 sec)
Read 377653290 rows, 4.57 GiB in 1.345 sec., 280761516 rows/sec., 3.40 GiB/sec.

〇〇 · 发表于 2020-6-27 18:20

本帖最后由〇〇于 2020-6-27 18:27 编辑

还支持连接mysql数据库，并把其数据与clickhouse交互，相当于dblink
VM_0_13_centos :) CREATE DATABASE lutaomy8 ENGINE = MySQL('ip:port', 'db', 'user', 'pass');

CREATE DATABASE lutaomy8
ENGINE = MySQL('ip:port', 'db', 'user', 'pass')

Ok.

0 rows in set. Elapsed: 0.154 sec.
--读mysql表
VM_0_13_centos :) select * from lutaomy8.dzm limit 8;

SELECT *
FROM lutaomy8.dzm
LIMIT 8

┌-code---┬-name--------┬-index_id-┬-depart_id-┬-p_code-┐
└--------┴-------------┴----------┴-----------┴--------┘

8 rows in set. Elapsed: 0.076 sec.
--把字面值插入mysql表
VM_0_13_centos :) insert into lutaomy8.dzm (code,name,p_code) values(888888,'8888',0);

INSERT INTO lutaomy8.dzm (code, name, p_code) VALUES

Ok.

1 rows in set. Elapsed: 0.037 sec.

--查看插入的数据
VM_0_13_centos :) select * from lutaomy8.dzm where code >'6' limit 5;

SELECT *
FROM lutaomy8.dzm
WHERE code > '6'
LIMIT 5

┌-code---┬-name-┬-index_id-┬-depart_id-┬-p_code-┐
│ 888888 │ 8888 │ ???? │ ???? │ 0 │
└--------┴------┴----------┴-----------┴--------┘

1 rows in set. Elapsed: 0.022 sec.
用clickhouse的表中数据插入mysql表
VM_0_13_centos :) insert into lutaomy8.dzm (code,name,p_code) select code,name,'0' from dzm where code >=650000;

INSERT INTO lutaomy8.dzm (code, name, p_code) SELECT
code,
name,
'0'
FROM dzm
WHERE code >= 650000

Ok.

0 rows in set. Elapsed: 0.014 sec. Processed 3.21 thousand rows, 82.66 KB (224.00 thousand rows/s., 5.77 MB/s.)
--显示是0行，实际上已插入
--clickhouse表中数据
VM_0_13_centos :) select code,name,'0' from dzm where code >=650000 limit 5;

SELECT
code,
name,
'0'
FROM dzm
WHERE code >= 650000
LIMIT 5

┌---code-┬-name-------------┬-'0'-┐
└--------┴------------------┴-----┘

5 rows in set. Elapsed: 0.002 sec. Processed 3.21 thousand rows, 82.66 KB (1.68 million rows/s., 43.22 MB/s.)

--mysql表中数据
VM_0_13_centos :) select * from lutaomy8.dzm where code >'6' limit 5;

SELECT *
FROM lutaomy8.dzm
WHERE code > '6'
LIMIT 5

┌-code---┬-name-------------┬-index_id-┬-depart_id-┬-p_code-┐
└--------┴------------------┴----------┴-----------┴--------┘

5 rows in set. Elapsed: 0.025 sec.

yanbin999888 · 发表于 2021-3-15 17:27

请问博主：我现在安装后，启动命令时，也是出现了第一页您启动时的那个问题，想问一下您当时是怎么解决的呢？

〇〇 · 发表于 2021-3-16 13:45

service clickhouse-server start不行就
systemctl start clickhouse-server

参考https://clickhouse.tech/docs/en/getting-started/install/