ClickHouse
[TOC]
安装
ClickHouse
- 环境
| 操作系统 | 硬盘 | 内存 | CPU核心 |
|---|---|---|---|
| centos7.6 | 500G数据盘 | 8G | 2C |
- 基础环境组件安装
yum -y install wget it vim net-tools- 数据盘挂载及分区
mkdir /data/
fdisk /dev/vdb #/dev/vdb是数据盘，可以使用fdisk查看
依次输入n、p、1、Enter、Enter
mkfs.ext4 /dev/vdb1
mount /dev/vdb1 /data/- 安装
#安装clickhouse
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-server clickhouse-client
#修改clickhouse配置文件把数据的存储路径改到/data/clickhouse
vim /etc/clickhouse-server/config.xml
找到<path>/var/lib/clickhouse/</path>把中间的/var/log/clickhouse/改为/data/clickhouse/
#启动
service clickhouse-server start
#配置开机自启
systemctl enable clickhouse-server.service- 配置文件路径
/etc/clickhouse-server/config.xml- 命令
service clickhouse-server start #启动
service clickhouse-server stop #停止- 测试是否能进入
clickhouse-clientclickhouse 亿级数据性能测试
生成测试数据
- 编译dbgen
git clone git@github.com:vadimtk/ssb-dbgen.git
cd ssb-dbgen
make
#如果下载不了可以去https://github.com/vadimtk/ssb-dbgen下载zip的压缩包上传到服务器- 生成数据
cd ssb-dbgen
./dbgen -s 1000 -T c
./dbgen -s 1000 -T l
./dbgen -s 1000 -T p
./dbgen -s 1000 -T s
#大概生成 6 亿行（67 GB）- 创建数据表
#最好每次单张表的创建
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 lineorder
(
LO_ORDERKEY UInt32,
LO_LINENUMBER UInt8,
LO_CUSTKEY UInt32,
LO_PARTKEY UInt32,
LO_SUPPKEY UInt32,
LO_ORDERDATE Date,
LO_ORDERPRIORITY LowCardinality(String),
LO_SHIPPRIORITY UInt8,
LO_QUANTITY UInt8,
LO_EXTENDEDPRICE UInt32,
LO_ORDTOTALPRICE UInt32,
LO_DISCOUNT UInt8,
LO_REVENUE UInt32,
LO_SUPPLYCOST UInt32,
LO_TAX UInt8,
LO_COMMITDATE Date,
LO_SHIPMODE LowCardinality(String)
)
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
CREATE TABLE part
(
P_PARTKEY UInt32,
P_NAME String,
P_MFGR LowCardinality(String),
P_CATEGORY LowCardinality(String),
P_BRAND LowCardinality(String),
P_COLOR LowCardinality(String),
P_TYPE LowCardinality(String),
P_SIZE UInt8,
P_CONTAINER LowCardinality(String)
)
ENGINE = MergeTree ORDER BY P_PARTKEY;
CREATE TABLE supplier
(
S_SUPPKEY UInt32,
S_NAME String,
S_ADDRESS String,
S_CITY LowCardinality(String),
S_NATION LowCardinality(String),
S_REGION LowCardinality(String),
S_PHONE String
)
ENGINE = MergeTree ORDER BY S_SUPPKEY;- 导入数据
clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl
clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl- 将“星型模式”转换为非规范化的“平面模式”：
SET max_memory_usage = 20000000000;
CREATE TABLE lineorder_flat
ENGINE = MergeTree ORDER BY (LO_ORDERDATE, LO_ORDERKEY)
AS SELECT
l.LO_ORDERKEY AS LO_ORDERKEY,
l.LO_LINENUMBER AS LO_LINENUMBER,
l.LO_CUSTKEY AS LO_CUSTKEY,
l.LO_PARTKEY AS LO_PARTKEY,
l.LO_SUPPKEY AS LO_SUPPKEY,
l.LO_ORDERDATE AS LO_ORDERDATE,
l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
l.LO_QUANTITY AS LO_QUANTITY,
l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
l.LO_DISCOUNT AS LO_DISCOUNT,
l.LO_REVENUE AS LO_REVENUE,
l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
l.LO_TAX AS LO_TAX,
l.LO_COMMITDATE AS LO_COMMITDATE,
l.LO_SHIPMODE AS LO_SHIPMODE,
c.C_NAME AS C_NAME,
c.C_ADDRESS AS C_ADDRESS,
c.C_CITY AS C_CITY,
c.C_NATION AS C_NATION,
c.C_REGION AS C_REGION,
c.C_PHONE AS C_PHONE,
c.C_MKTSEGMENT AS C_MKTSEGMENT,
s.S_NAME AS S_NAME,
s.S_ADDRESS AS S_ADDRESS,
s.S_CITY AS S_CITY,
s.S_NATION AS S_NATION,
s.S_REGION AS S_REGION,
s.S_PHONE AS S_PHONE,
p.P_NAME AS P_NAME,
p.P_MFGR AS P_MFGR,
p.P_CATEGORY AS P_CATEGORY,
p.P_BRAND AS P_BRAND,
p.P_COLOR AS P_COLOR,
p.P_TYPE AS P_TYPE,
p.P_SIZE AS P_SIZE,
p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;运行查询
- 1.1
test :) SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25)
Query id: 63ed7fcc-56f1-43bc-b48a-a8090cfb84ea
┌────────revenue─┐
│ 44652567249651 │
└────────────────┘
1 row in set. Elapsed: 0.656 sec. Processed 91.02 million rows, 728.10 MB (138.79 million rows/s., 1.11 GB/s.)
test :)
#扫描了9100万行，用时0.656秒，查询列数：2，结果行数：1- 1.2
test :) SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE (toYYYYMM(LO_ORDERDATE) = 199401) AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6)) AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35))
Query id: 83344e75-2880-4f19-905e-4d619371486e
┌───────revenue─┐
│ 9624332170119 │
└───────────────┘
1 row in set. Elapsed: 0.079 sec. Processed 7.76 million rows, 61.97 MB (97.78 million rows/s., 781.15 MB/s.)
#扫描了776万行，用时0.079秒，查询列数2，返回行数1- 2.2
test :) SELECT
sum(LO_REVENUE),
toYear(LO_ORDERDATE) AS year,
P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
year,
P_BRAND
ORDER BY
year,
P_BRAND;
SELECT
sum(LO_REVENUE),
toYear(LO_ORDERDATE) AS year,
P_BRAND
FROM lineorder_flat
WHERE (P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228') AND (S_REGION = 'ASIA')
GROUP BY
year,
P_BRAND
ORDER BY
year ASC,
P_BRAND ASC
Query id: b3864522-e8ef-484c-984c-f5a9aafd0f3d
┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
│ 66450349438 │ 1992 │ MFGR#2221 │
│ 65423264312 │ 1992 │ MFGR#2222 │
│ 66936772687 │ 1992 │ MFGR#2223 │
│ 64047191934 │ 1992 │ MFGR#2224 │
│ 65744559138 │ 1992 │ MFGR#2225 │
│ 66993045668 │ 1992 │ MFGR#2226 │
│ 67411226147 │ 1992 │ MFGR#2227 │
│ 69390885970 │ 1992 │ MFGR#2228 │
│ 66819757447 │ 1993 │ MFGR#2221 │
│ 67805601887 │ 1993 │ MFGR#2222 │
│ 67208412655 │ 1993 │ MFGR#2223 │
│ 64222070981 │ 1993 │ MFGR#2224 │
│ 66159498618 │ 1993 │ MFGR#2225 │
│ 68387963965 │ 1993 │ MFGR#2226 │
│ 68470823598 │ 1993 │ MFGR#2227 │
│ 70176992353 │ 1993 │ MFGR#2228 │
│ 66201929022 │ 1994 │ MFGR#2221 │
│ 66601352347 │ 1994 │ MFGR#2222 │
│ 67149651412 │ 1994 │ MFGR#2223 │
│ 64508853727 │ 1994 │ MFGR#2224 │
│ 66008726728 │ 1994 │ MFGR#2225 │
│ 66358870053 │ 1994 │ MFGR#2226 │
│ 67912269895 │ 1994 │ MFGR#2227 │
│ 69071503806 │ 1994 │ MFGR#2228 │
│ 65269818712 │ 1995 │ MFGR#2221 │
│ 65566595895 │ 1995 │ MFGR#2222 │
│ 65980940491 │ 1995 │ MFGR#2223 │
│ 63741007905 │ 1995 │ MFGR#2224 │
│ 64701224302 │ 1995 │ MFGR#2225 │
│ 67771832811 │ 1995 │ MFGR#2226 │
│ 67898964035 │ 1995 │ MFGR#2227 │
│ 70977692364 │ 1995 │ MFGR#2228 │
│ 67169465617 │ 1996 │ MFGR#2221 │
│ 67121666299 │ 1996 │ MFGR#2222 │
│ 66485923436 │ 1996 │ MFGR#2223 │
│ 64410797788 │ 1996 │ MFGR#2224 │
│ 65788692665 │ 1996 │ MFGR#2225 │
│ 68193662121 │ 1996 │ MFGR#2226 │
│ 67904649725 │ 1996 │ MFGR#2227 │
│ 69705348599 │ 1996 │ MFGR#2228 │
│ 66839293911 │ 1997 │ MFGR#2221 │
│ 65623735495 │ 1997 │ MFGR#2222 │
│ 66608624781 │ 1997 │ MFGR#2223 │
│ 64127451073 │ 1997 │ MFGR#2224 │
│ 66071861556 │ 1997 │ MFGR#2225 │
│ 68517706654 │ 1997 │ MFGR#2226 │
│ 67632192229 │ 1997 │ MFGR#2227 │
│ 70029520291 │ 1997 │ MFGR#2228 │
│ 39646973602 │ 1998 │ MFGR#2221 │
│ 38969579899 │ 1998 │ MFGR#2222 │
│ 38767988496 │ 1998 │ MFGR#2223 │
│ 38020572188 │ 1998 │ MFGR#2224 │
│ 38328423898 │ 1998 │ MFGR#2225 │
│ 38705033272 │ 1998 │ MFGR#2226 │
│ 39907545239 │ 1998 │ MFGR#2227 │
│ 40654201840 │ 1998 │ MFGR#2228 │
└─────────────────┴──────┴───────────┘
56 rows in set. Elapsed: 3.657 sec. Processed 600.04 million rows, 5.61 GB (164.10 million rows/s., 1.53 GB/s.)
#扫描了6亿行，用时3.657秒，查询列数3，返回行数56- 3.2
test :) SELECT
C_CITY,
S_CITY,
toYear(LO_ORDERDATE) AS year,
sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY
C_CITY,
S_CITY,
year
ORDER BY
year ASC,
revenue DESC;
SELECT
C_CITY,
S_CITY,
toYear(LO_ORDERDATE) AS year,
sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)
GROUP BY
C_CITY,
S_CITY,
year
ORDER BY
year ASC,
revenue DESC
Query id: c134110d-9a1c-4aab-b0cc-90a84a44be57
┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐
│ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 │
│ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 │
│ UNITED ST7 │ UNITED ST4 │ 1993 │ 5259994796 │
│ UNITED ST7 │ UNITED ST9 │ 1993 │ 5069079378 │
│ UNITED ST2 │ UNITED ST5 │ 1994 │ 5293437025 │
│ UNITED ST3 │ UNITED ST2 │ 1994 │ 5290106433 │
│ .......... │ .......... │ .... │ .......... │
│ UNITED ST7 │ UNITED ST8 │ 1995 │ 5566692127 │
│ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 │
│ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │
└────────────┴────────────┴──────┴────────────┘
600 rows in set. Elapsed: 5.086 sec. Processed 546.69 million rows, 5.57 GB (107.48 million rows/s., 1.09 GB/s.)
test :)
#扫描了5.46亿行，用时5.086秒，查询列数4，返回行数600- 4.1
test :) SELECT
toYear(LO_ORDERDATE) AS year,
C_NATION,
sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
GROUP BY
year,
C_NATION
ORDER BY
year ASC,
C_NATION ASC
SELECT
toYear(LO_ORDERDATE) AS year,
C_NATION,
sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
GROUP BY
year,
C_NATION
ORDER BY
year ASC,
C_NATION ASC
Query id: ff327646-221e-4ab5-8b82-783308cbe142
┌─year─┬─C_NATION──────┬────────profit─┐
│ 1992 │ ARGENTINA │ 1041983042066 │
│ 1992 │ BRAZIL │ 1031193572794 │
│ 1992 │ CANADA │ 1032094614252 │
│ 1992 │ PERU │ 1037331491440 │
│ 1992 │ UNITED STATES │ 1031593944407 │
│ 1993 │ ARGENTINA │ 1034515265588 │
│ 1993 │ BRAZIL │ 1028249774691 │
│ 1993 │ CANADA │ 1030633924190 │
│ 1993 │ PERU │ 1032888811548 │
│ 1993 │ UNITED STATES │ 1030241613033 │
│ 1994 │ ARGENTINA │ 1035059347804 │
│ 1994 │ BRAZIL │ 1029788284729 │
│ 1994 │ CANADA │ 1028314868119 │
│ 1994 │ PERU │ 1025406236588 │
│ 1994 │ UNITED STATES │ 1035441439980 │
│ 1995 │ ARGENTINA │ 1036878482604 │
│ 1995 │ BRAZIL │ 1032846705883 │
│ 1995 │ CANADA │ 1031488804141 │
│ 1995 │ PERU │ 1034460048487 │
│ 1995 │ UNITED STATES │ 1034988860577 │
│ 1996 │ ARGENTINA │ 1041240509801 │
│ 1996 │ BRAZIL │ 1030467525021 │
│ 1996 │ CANADA │ 1035089775664 │
│ 1996 │ PERU │ 1029765730730 │
│ 1996 │ UNITED STATES │ 1032384751840 │
│ 1997 │ ARGENTINA │ 1036752881505 │
│ 1997 │ BRAZIL │ 1036482571346 │
│ 1997 │ CANADA │ 1025775840777 │
│ 1997 │ PERU │ 1031380143878 │
│ 1997 │ UNITED STATES │ 1030570488847 │
│ 1998 │ ARGENTINA │ 607618915600 │
│ 1998 │ BRAZIL │ 603999739074 │
│ 1998 │ CANADA │ 601462066533 │
│ 1998 │ PERU │ 603980044827 │
│ 1998 │ UNITED STATES │ 605069471323 │
└──────┴───────────────┴───────────────┘
35 rows in set. Elapsed: 8.311 sec. Processed 600.04 million rows, 8.41 GB (72.20 million rows/s., 1.01 GB/s.)
test :)
#扫描了6亿行，用时8.311秒，查询列数4，返回结果35行扫描二维码，在手机上阅读
