Docker安裝ClickHouse並初始化數據測試
clickhouse簡介
ClickHouse是一個面向列存儲的數據庫管理系統,可以使用SQL查詢實時生成分析數據報告,主要用於OLAP(在線分析處理查詢)場景。關於clickhouse原理以及基礎知識在以後學習中慢慢總結。
1、Docker安裝ClickHouse
docker run -d --name some-clickhouse-server \ -p 8123:8123 -p 9009:9009 -p 9091:9000 \ --ulimit nofile=262144:262144 \ -v /home/clickhouse:/var/lib/clickhouse \ yandex/clickhouse-server
2、下載SSBM工具
1、git clone https://github.com/vadimtk/ssb-dbgen.git 2、cd ssb-dbgen 3、make
3、生成數據
./dbgen -s 100 -T c ./dbgen -s 100 -T p ./dbgen -s 100 -T s ./dbgen -s 100 -T l ./dbgen -s 100 -T d
查看下數據
4、建表
CREATE TABLE default.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 default.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 default.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 default.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;
5、導入數據
準備工作:
先把ssb-dbgen(lineorder.tbl,customer.tbl,part.tbl,supplier.tbl)考到clickhouse-server容器裡面
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
註意:如果此處報錯,檢查clickhouse的配置(端口是否占用,是否設置用戶和密碼)
6、測試
編號 | 查詢語句SQL | 耗時(ms) |
---|---|---|
Q1 | SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(l.LO_ORDERDATE) = 1993 AND l.LO_DISCOUNT BETWEEN 1 AND 3 AND l.LO_QUANTITY < 25; | 36 |
Q2 | SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(l.LO_ORDERDATE) = 199401 AND l.LO_DISCOUNT BETWEEN 4 AND 6 AND l.LO_QUANTITYBETWEEN 26 AND 35; | 12 |
Q3 | SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toISOWeek(l.LO_ORDERDATE) = 6 AND toYear(l.LO_ORDERDATE) = 1994 AND l.LO_DISCOUNT BETWEEN 5 AND 7 AND l.LO_QUANTITY BETWEEN 26 AND 35; | 12 |
Q4 | SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_CATEGORY = ‘MFGR#12′ AND s.S_REGION = ‘AMERICA’ GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND; | 16 |
Q5 | SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_BRAND BETWEEN ‘MFGR#2221′ AND ‘MFGR#2228′ AND s.S_REGION = ‘ASIA’ GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND; | 21 |
Q6 | SELECT toYear(l.LO_ORDERDATE) AS year, s.S_CITY, p.P_BRAND, SUM(l.LO_REVENUE -l.LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE s.S_NATION = ‘UNITED STATES’ AND (year = 1997 OR year = 1998) AND p.P_CATEGORY = ‘MFGR#14′ GROUP BY year, s.S_CITY, p.P_BRAND ORDER BY year, s.S_CITY, p.P_BRAND; | 19 |
官網參考:
https://clickhouse.tech/docs/zh/getting-started/example-datasets/star-schema/#star-schema-benchmark
以上就是Docker創建ClickHouse 並初始化數據測試的詳細內容,更多關於Docker的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- 快速使用docker-compose部署clickhouse的教程
- python連接clickhouse數據庫的兩種方式小結
- 如何在mysql進行查詢緩存及失敗的解決方法
- MySQL特殊函數使用技巧梳理
- Java 嵌入數據引擎從 SQLite 到 SPL詳解