clickhouse使用

clickhouse介绍

• 启动方式

clickhouse-server --daemon --pid-file=/var/run/clickhouse-server/clickhouse-server.pid --config-file=/etc/clickhouse-server/config.xml

• 查看当前监听端口

ps -aux | grep clickhouse

• 启用客户端

clickhouse-client --port=9008

• 常用语法

show tables;
select now();

• 关闭clickhouse服务

ps -aux | grep clickhouse-server
kill -9 $pid

• 后台托管启动服务

nohup clickhouse-server --config-file=/etc/clickhouse-server/config.xml >null 2>&1 &

库和表格的创建

• 库的创建

CREATE DATABASE [IF NOT EXISTS] db_firewall_log

• 表的创建

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = engine


CREATE TABLE IF NOT EXISTS all_hits ON CLUSTER cluster (p Date, i Int32) ENGINE = Distributed(cluster, default, hits)


CREATE TABLE code_province( \
    state_province        String, \
    province_name         String, \
    create_date           date \
) ENGINE = MergeTree(create_date, (state_province), 8192);



CREATE TABLE m_tb_alert_http_url_log_20180403 ENGINE = MergeTree
ORDER BY id AS SELECT * FROM mysql('127.0.0.1:3307','db_firewall_log','m_tb_alert_http_url_log_20180403', 'root', 'bd_123456')



CREATE TABLE `m_tb_alert_http_url_log_20180403` (`id` UInt64,`sAppProto` String,`sSrcMac` String,`sSrcIP` String,`iSrcPort` UInt64,`sDstMac` String,`sDstIP` String,`iDstPort` UInt64,`sType` String,`sSiteName` String,`sHost` String,`sUri` String,`sMethod` String,`sField` String,`sUser` String,`sDept` String,`sScc` String,`sSccFlag` String,`sURL` String,`sTitle` String,`sCategory` String,`sAlertDetail` String,`sAlertStatus` String,`sAlertKeyword` String,`sDeviceID` String,`iAlertFlag` String,`sAlertLevel` String) ENGINE = MergeTree(id, (sSiteName), 8192);



CREATE TABLE m_tb_alert_http_url_log_20180403 ENGINE = MergeTree AS SELECT * FROM mysql('127.0.0.1:3306', 'db_firewall_log', 'm_tb_alert_http_url_log_20180403', 'root', 'bd_123456')