微服务治理实战：Hadoop和Spark，都不及Clickhouse香

<web>    <interval>        <duration>3600</duration>        <queries>500</queries>        <errors>100</errors>        <result_rows>100</result_rows>        <read_rows>2000</read_rows>        <execution_time>5</execution_time>    </interval></web>
<web_user>    <password_double_sha1_hex>***</password_double_sha1_hex>    <networks incl="networks" replace="replace">        <ip>::/0</ip>    </networks>    <profile>default</profile>    <quota>web</quota>  // 引用用户策略    <access_management>1</access_management></web_user>

 CREATE TABLE {database}.{local_table} ON CLUSTER {cluster}(    `date` Date,    `timestamp` DateTime,    ...)ENGINE = MergeTreePARTITION BY dateORDER BY timestampTTL timestamp + toIntervalDay(30)SETTINGS index_granularity = 8192
CREATE TABLE {database}.{table} ON CLUSTER {cluster} AS {database}.{local_table}ENGINE = Distributed({cluster}, {database}, {table}, rand())

SELECT    (intDiv(toUInt32(timestamp), 1) * 1) * 1000 as t,    quantile(0.99)(consume) as p99FROM apm.trace_logs
WHERE    timestamp >= toDateTime(1605147889)    AND app_name = 'app'GROUP BY    tORDER BY t

# 本地表存储tracing日志 CREATE TABLE apm.local_trace_logs ON CLUSTER {cluster}(    `date` Date,    `timestamp` DateTime,    `trace_id` String, // uuid 随机32字符串    ...)ENGINE = MergeTreePARTITION BY dateORDER BY timestampTTL timestamp + toIntervalDay(30)SETTINGS index_granularity = 8192
# 分布式表DistributedCREATE TABLE apm.trace_logs ON CLUSTER {cluster} AS apm.local_trace_logsENGINE = Distributed({cluster}, apm, trace_logs, rand())
# 提取入口trace_id数据即level=1，生成物化视图，这个视图的数据只有原表的10%的量级。 CREATE MATERIALIZED VIEW apm.local_entrances(    `date` Date,    `timestamp` DateTime,    `trace_id` String,    ...)ENGINE = MergeTreePARTITION BY dateORDER BY timestampTTL timestamp + toIntervalDay(6)SETTINGS index_granularity = 8192 ASSELECT    date,    timestamp,    trace_id,    ...FROM apm.local_trace_logsWHERE level = '1'
# Distributed 暴露给外查询的引擎CREATE TABLE apm.entrances ON CLUSTER {cluster} AS apm.local_entrancesENGINE = Distributed({cluster}, apm, entrances, rand())

date:    2021-11-25name:    app_request_qpmtags:    ['app_name=demo','ttl=forever']val:     336608ts:      2021-11-26 23:00:00updated: 2021-11-26 23:00:22

SELECT sum(val) as totalFROM metricsWHERE (name = 'app_request_qpm') AND has(tags, 'app_name=demo')AND date=today() and ts>now()-300

# 本地表用于存储指标CREATE TABLE apm.local_metrics ON CLUSTER {CLUSTER}(    `date` Date DEFAULT toDate(0),    `name` String,    `tags` Array(String),    `val` Float64,    `ts` DateTime,    `updated` DateTime DEFAULT now())ENGINE = GraphiteMergeTree('graphite_metric')PARTITION BY dateORDER BY (name, tags, ts)TTL ts + toIntervalDay(30)
# 分布式表CREATE TABLE apm.metrics ON CLUSTER {CLUSTER}AS apm.local_metricsENGINE = Distributed('{CLUSTER}', 'apm', 'local_metrics', rand())

 CREATE MATERIALIZED VIEW apm.local_middleware_logs(    `date` Date,    `timestamp` DateTime,    `trace_id` String,    `event` String,    ...)ENGINE = MergeTreePARTITION BY dateORDER BY timestampTTL timestamp + toIntervalDay(6)SETTINGS index_granularity = 8192 ASSELECT    date,    timestamp,    trace_id,    event,    ...FROM apm.local_biz_logsWHERE event in ('mysql','rabbitmq'...)
CREATE TABLE apm.middleware_logs ON CLUSTER {cluster} AS apm.local_middleware_logsENGINE = Distributed({cluster}, apm, middleware_logs, rand())

# 物化视图 提取特定tags的指标CREATE MATERIALIZED VIEW apm.local_view_metrics ON CLUSTER {CLUSTER}(    `date` Date DEFAULT toDate(0),    `name` String,    `tags` Array(String),    `val` Float64,    `ts` DateTime,    `updated` DateTime DEFAULT now())ENGINE = GraphiteMergeTree('graphite_metric_view')PARTITION BY dateORDER BY (name, tags, ts)SETTINGS index_granularity = 8192 ASSELECT    date,    name,    tags,    val,    ts,    updatedFROM apm.local_metricsWHERE has(tags, 'ttl=forever') = 1
CREATE TABLE apm.view_metrics ON CLUSTER {CLUSTER}AS apm.local_view_metricsENGINE = Distributed('{cluster}', 'apm', 'local_view_metrics', rand())
# graphite_metric_view 配置策略    <graphite_metric_view>        <path_column_name>tags</path_column_name>        <time_column_name>ts</time_column_name>        <value_column_name>val</value_column_name>        <version_column_name>updated</version_column_name>        <default>            <function>avg</function>            <retention>                <age>0</age>                <precision>30</precision>            </retention>            <retention>                <age>604800</age>                <precision>3600</precision>            </retention>            <retention>                <age>2592000</age>                <precision>86400</precision>            </retention>        </default>        <pattern>            <regexp>rollup_sum</regexp>            <function>sum</function>            <retention>                <age>0</age>                <precision>30</precision>            </retention>            <retention>                <age>604800</age>                <precision>3600</precision>            </retention>            <retention>                <age>2592000</age>                <precision>86400</precision>            </retention>        </pattern>        ...    </graphite_metric_view>

# 分片副本<nshards_1replicas>    <shard>        <internal_replication>true</internal_replication>        <replica>            <host>clickhouse1</host>            <port>9000</port>            <user>user</user>            <password>password</password>        </replica>        <replica>            <host>clickhouse4</host>            <port>9000</port>            <user>user</user>            <password>password</password>        </replica>    </shard>    ...<nshards_1replicas>
# 变量用于建表时的占位符，策略将存在zookeeper中.<macros>    <layer>02</layer>    <shard>01</shard>    <replica>ck2-01-01</replica></macros>

CREATE TABLE default.local_tests ON CLUSTER nshards_1replicas(    `id` Int64,    `diseaseid` Int64,    `ctime` DateTime,    ...)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/local_tests', '{replica}')PARTITION BY toYYYYMMDD(ctime)ORDER BY (ctime, diseaseid)TTL ctime + toIntervalDay(7)SETTINGS index_granularity = 8192

CREATE TABLE default.tests ON CLUSTER nshards_1replicas AS {database}.local_testsENGINE = Distributed(nshards_1replicas, default, tests, rand())