docker虚拟化容器使用教程(Docker安装ClickHouse并初始化数据测试)
类别:服务器 浏览量:1761
时间:2021-10-07 00:08:11 docker虚拟化容器使用教程
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的资料请关注开心学习网其它相关文章!
标签:Docker ClickHouse
您可能感兴趣
- docker的配置与使用(docker之docker-machine用法详解)
- docker无法访问宿主机ip(解决Mac下 docker 无法 ping 通宿主机的问题)
- 如何执行docker镜像(Docker镜像管理常用操作代码示例)
- dockergitlab服务器搭建(基于Docker部署GitLab环境搭建的方法步骤)
- docker管理脚本(Docker可视化ui管理工具Portainer安装及使用解析)
- docker查找redis配置文件(解决docker重启redis,mysql数据丢失的问题)
- springboot docker 部署(详解docker部署SpringBoot及替换jar包的方法)
- docker容器重启配置没了(docker修改未启动容器的配置信息操作)
- dockerexec详解(docker中的run/cmd/entrypoint的区别详解)
- 查看docker运行状态命令(使用docker命令不需要敲sudo的操作)
- docker修改镜像配置文件(Docker配置本地镜像与容器的存储位置)
- docker容器使用redis服务(docker安装redis设置密码并连接的操作)
- docker容器端口和内部进程(Docker动态给容器Container暴露端口操作)
- docker如何查看容器目录(docker 查看容器的挂载目录操作)
- docker部署redis集群查看版本(docker下的 redis 之持久化存储详解)
- docker 镜像mysql(解决docker拉取mysql镜像太慢的情况)
- 《九牛之人降魔传》开机 演员祁高坤化身九牛之人除魔卫道(九牛之人降魔传开机)
- 王铲铲的致富之路无限金币卡法攻略教学(王铲铲的致富之路无限金币卡法攻略教学)
- 文明6金币太少怎么办 文明6无限刷钱教程(文明6金币太少怎么办)
- 开国中将,王牌军63军首任政委,两个连襟一个上将一个少将传为佳话(王牌军63军首任政委)
- 臭名昭著的731部队最高负责人 石井四郎(臭名昭著的731部队最高负责人)
- 王牌部队,你看的剧情我看的时尚(你看的剧情我看的时尚)
热门推荐
- thinkphp5对接支付宝扫码支付(ThinkPHP框架下整合支付宝支付功能图文教程)
- cssdisplay覆盖规律(css解决display:inline-block;产生的缝隙间隙的方法)
- sqlserver2012知识点(SQL Server 2012 安全概述)
- php八大模式详解(php设计模式之工厂模式用法经典实例分析)
- dedecms变量的使用(dedecms实现游客可以提问、回答及查看的方法)
- css中background属性介绍
- react基础知识详解(如何深入理解React的ref 属性)
- vue路由跳转的方法(Vue路由监听实现同页面动态加载的示例)
- selenium是否登录成功(使用selenium模拟登录解决滑块验证问题的实现)
- angular 常用模块(详解Angular之路由基础)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9