- 机器环境:centos7 2G内存
- clickhouse文档
安装
# 需要cpu支持 SSE 4.2
grep -q sse4_2 /proc/cpuinfo &&{
echo "SSE 4.2 supported"
yum install -y yum-utils
rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/stable/x86_64
yum install clickhouse-server clickhouse-client
service clickhouse-server start
service clickhouse-server status
echo "config @ /etc/clickhouse-server/config.xml"
echo
} || echo "SSE 4.2 not supported"
注:clickhouse的服务是用的/etc/init.d
下的启动脚本
测试安装是否成功
clickhouse-client
select 1
效果如下图
测试
和很多数据库管理系统一样,clickhouse也有database和table的概念
创建数据库
CREATE DATABASE IF NOT EXISTS test
use test
创建表
创建表和mysql也差不多,需要指定以下内容:
- 表名
- 表scheme:字段名和字段类型
- Table engine and its settings——决定查询怎么被执行
CREATE TABLE tutorial.hits_v1
(
`WatchID` UInt64,
`JavaEnable` UInt8,
`Title` String,
`GoodEvent` Int16,
`EventTime` DateTime,
`EventDate` Date,
`CounterID` UInt32,
`ClientIP` UInt32,
................
`URLHash` UInt64,
`CLID` UInt32,
`YCLID` UInt64,
`ShareService` String,
`ShareURL` String,
`ShareTitle` String,
`ParsedParams` Nested(
Key1 String,
Key2 String,
Key3 String,
Key4 String,
Key5 String,
ValueDouble Float64),
`IslandID` FixedString(16),
`RequestNum` UInt32,
`RequestTry` UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
查询
SELECT
StartURL AS URL,
AVG(Duration) AS AvgDuration
FROM tutorial.visits_v1
WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'
GROUP BY URL
ORDER BY AvgDuration DESC
LIMIT 10