Clickhouse使用学习

Aug 14, 2020 in UNDEFINED
undefined
1 min read

Table of Content

机器环境：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