EventLogHDFS; Ok. 0 rows in set. Elapsed: 106.350 sec. Processed 28.75 mln rows. 54 / 97 In ClickHouse: Query Local Copy SELECT countIf(CounterType='Show') as SumShows, countIf(CounterType='Click') as SumClicks BannerID FROM EventLogLocal GROUP BY BannerID ORDER BY SumClicks desc LIMIT 3; 55 / 97 In ClickHouse: Query Local Copy SELECT countIf(CounterType='Show') as SumShows, countIf(CounterType='Click') as SumClicks dynamic libraries › Requires driver manager › Link with executable in runtime 62 / 97 ODBC Engine: Drivers › Third-party dynamic libraries › Require driver manager › Link with executable in runtime ›

yandex/docs/en/data_types/nested_data_structures/aggregatefunction/ https://clickhouse.yandex/docs/en/query_language/agg_functions/reference/#groupbitmap 表示groupBitmap聚合函数的中间状态。 可以通过groupBitmapState创建。 ES对资源消耗比较大，属亍豪华型配置。  ES的DSL诧法对用户丌太友好，用户学习成 本高。 Kafka Flink 18 ClickHouse替换ES存储标签数据  ClickHouse Manager负责ClickHouse集群管理、元数据管理以及节点负载协调  tag-generate负责标签数据构建，保存到HDFS（MySQL中存储标签配置信息）  tag-loader向Cl 集群查询标签数据 Spark tag-generate tag-loader MySQL ClickHouse集群 ClickHouse1 ClickHouse Manager HDFS 用户画像平台 ClickHouse2 ClickHouseN to-ch-sql 19 标签数据表定义 20 String Integer Double

Partition2 DataN PartitionM … … app-2 … … app-n RPC DataNode NameNode MetaStore Controller manager Scheduler RPC app-1 Data1 Partition0 Data2 Partition2 DataN PartitionM … … app-2 … … iData 2 旧画像系统 Block 1 Block 2 Block … Storage Scheduler Data Stats Gather SQL Parser Query Optimizer Execution Plan Bitcode Emitter Scheduler Block 1 Block 2 Block … DataNode-2

test_insert test_query test insert_view calc_test_query Read Client Write Client ► Time-Series-Orient Model How we do test_insert test_query test insert_view calc_test_query Read Client Write Float64 ) ENGINE = Null ► Time-Series-Orient Model How we do test_insert test_query test insert_view calc_test_query Read Client Write Client CREATE MATERIALIZED VIEW demonstration.test_insert metric_name, Name, Age ► Time-Series-Orient Model How we do test_insert test_query test insert_view calc_test_query Read Client Write Client CREATE TABLE demonstration.test ( `time_series_interval`

import pandas as pd url = 'http://127.0.0.1:8123?query=' query = 'select * from trips limit 1000 format TSVWithNames' resp = requests.get(url, data=query) string_io = io.StringIO(resp.text) table = pd.read_csv(string_io OFFSET y Must specify an expression for sampling › Optimized by PK › Fixed dataset for fixed sample query › Only for MergeTree 11 / 62 How to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time FROM trips_sample_time 432992321 1 rows in set. Elapsed: 0.413 sec. Processed 432.99 million rows Query with sampling reads less rows! SELECT count() FROM trips_sample_time SAMPLE 1 / 3 OFFSET 1 / 3 144330770

import pandas as pd url = 'http://127.0.0.1:8123?query=' query = 'select * from trips limit 1000 format TSVWithNames' resp = requests.get(url, data=query) string_io = io.StringIO(resp.text) table = pd.read_csv(string_io OFFSET y Must specify an expression for sampling › Optimized by PK › Fixed dataset for fixed sample query › Only for MergeTree 11 / 62 How to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time FROM trips_sample_time 432992321 1 rows in set. Elapsed: 0.413 sec. Processed 432.99 million rows Query with sampling reads less rows! SELECT count() FROM trips_sample_time SAMPLE 1 / 3 OFFSET 1 / 3 144330770

随时调整服务器，新增/缩减服务器； 分布式： k8s的集群式部署 全球敏捷运维峰会 广州站 采用ClickHouse后平台的查询性能 system.query_log表，记录已经 执行的查询记录 query：执行的详细SQL，查询相关记录可以 根据SQL关键字筛选该字段 query_duration_ms：执行时间 memory_usage：占用内存 read_rows和read_bytes ：读取行数和大小 r 数据导入之前要评估好分区字段； • 数据导入时根据分区做好Order By； • 左右表join的时候要注意数据量的变化； • 是否采用分布式； • 监控好服务器的cpu/内存波动/`system`.query_log； • 数据存储磁盘尽量采用ssd； • 减少数据中文本信息的冗余存储； • 特别适用于数据量大，查询频次可控的场景，如数据分析，埋点日志系统； 全球敏捷运维峰会 广州站 StarRocks应用小结

PartitionManager() as pm: pm.partition_instances(node1, node2, port=9009) #drop connection node1.query("INSERT INTO tt \ SELECT * FROM hdfs('hdfs://hdfs1:9000/tt', 'TSV')") assert_with_retry(node2, "SELECT v_slow / v_fast AS ratio FROM ( SELECT PE.Names, query_duration_ms > 3000 AS slow, avg(PE.Values) AS value, FROM system.query_thread_log WHERE query = '...' GROUP BY PE.Names, slow ) HAVING ratio >

/2013/000000_0' | clickhouse-client --host=127.0.0.1 -- port=10000 -u user --password password --query="INSERT INTO Insight_zhongan.baodan_yonghushuju FORMAT CSV" 效果： 单进程：每分钟2600w条记录，client占用核数=1，s iostat -dmx 1: 查看磁盘io使用情况，每秒更新 • Clickhouse命令： • set send_logs_level = 'trace'：查看sql执行步骤详情 • 根据query_id查看内存使用情况，io情况等详细信息： system flush logs; select ProfileEvents.Names as name, match(name, 'Bytes|Chars') Values) : toString(ProfileEvents.Values) as value from system.query_log array join ProfileEvents where event_date = today() and type = 2 and query_id = '05ff4e7d-2b8c-4c41-b03d-094f9d8b02f2'; Thanks！

Pipeline = Directed Acyclic Graph (DAG) of modules Module = Input + Task + Output Task = Query or external program Query = “CREATE TABLE ... AS SELECT ...” A Database System and A ML Pipeline Begin Content