E-commerce analytics Retail Games analytics Video streaming analytics Media & news analytics Social recommendations Classifieds. Dating Search engine optimization Telecom traffic analysis DPI analysis • Data Hub Support For Semistructured Data 27 JSO data type: CREATE TABLE games (data JSON) ENGINE = MergeTree; • You can insert arbitrary nested JSONs • Types are automatically inferred on INSERT games (data String) ENGINE = MergeTree ORDER BY tuple(); SELECT JSONExtractString(data, 'teams', 1, 'name') FROM games; — 0.520 sec. CREATE TABLE games (data JSON) ENGINE = MergeTree; SELECT data

E-commerce analytics Retail Games analytics Video streaming analytics Media & news analytics Social recommendations Classifieds. Dating Search engine optimization Telecom traffic analysis DPI analysis • Data Hub Support For Semistructured Data 27 JSO data type: CREATE TABLE games (data JSON) ENGINE = MergeTree; • You can insert arbitrary nested JSONs • Types are automatically inferred on INSERT games (data String) ENGINE = MergeTree ORDER BY tuple(); SELECT JSONExtractString(data, 'teams', 1, 'name') FROM games; — 0.520 sec. CREATE TABLE games (data JSON) ENGINE = MergeTree; SELECT data

数据治理 预测分析与机器学习 CHAPTER 众安集智平台与clickhouse 02 集智平台 X-Brain AI 开放平台 计算框架 Hadoop, JStorm, Spark Streaming, Flink 离线/实时任务监控 数据、模型存储 Hive, HBase, Clickhouse, Kylin 数据接入 消 息 中 间 件 模型、 算法 模版 机器学习平台 Antron

EventTime DateTime, BannerID UInt64, Cost UInt64, CounterType Enum('Hit'=0, 'Show'=1, 'Click'=2) ) ENGINE = HDFS('hdfs://hdfs1:9000/event_log.parq', 'Parquet') 50 / 97 In ClickHouse: DDL CREATE TABLE EventTime DateTime, BannerID UInt64, Cost UInt64, CounterType Enum('Hit'=0, 'Show'=1, 'Click'=2) ) ENGINE = HDFS('hdfs://hdfs1:9000/event_log.parq', 'Parquet') Ok. 0 rows in set. Elapsed: 0.004 sec. 51 75 mln rows. 53 / 97 In ClickHouse: Local Log Copy CREATE TABLE EventLogLocal AS EventLogHDFS ENGINE = MergeTree() ORDER BY BannerID; Ok. INSERT INTO EventLogLocal SELECT * FROM EventLogHDFS; Ok

DateTime, `Name` String, `Age` UInt8, ..., `HeartRate` UInt8, `Humidity` Float32, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(Time) ORDER BY (Name, Time, Age, ...); ► Column-Orient Model `Name` LowCardinality(String), `Age` UInt8, ..., `HeartRate` UInt8, `Humidity` Float32, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(Time) ORDER BY (Name, Time, Age, ...); ► Column-Orient Model UInt8, ..., `time_series` AggregateFunction( groupArray, Tuple(DateTime, Float64)) ) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(time_series_interval) ORDER BY (metric_name, time_series_interval)

to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time ( pickup_datetime DateTime ) ENGINE = MergeTree ORDER BY sipHash64(pickup_datetime) -- Primary Key SAMPLE BY sipHash64(pickup_datetime) You can store model as aggregate function state in a separate table Example CREATE TABLE models ENGINE = MergeTree ORDER BY tuple() AS SELECT stochasticLinearRegressionState(total_amount, trip_distance) function state in ClickHouse You can save aggregate function result into table. CREATE TABLE tab ENGINE = Memory AS SELECT sumState(number) AS x FROM numbers(5) Use sumMerge to get final result SELECT

to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time ( pickup_datetime DateTime ) ENGINE = MergeTree ORDER BY sipHash64(pickup_datetime) -- Primary Key SAMPLE BY sipHash64(pickup_datetime) You can store model as aggregate function state in a separate table Example CREATE TABLE models ENGINE = MergeTree ORDER BY tuple() AS SELECT stochasticLinearRegressionState(total_amount, trip_distance) function state in ClickHouse You can save aggregate function result into table. CREATE TABLE tab ENGINE = Memory AS SELECT sumState(number) AS x FROM numbers(5) Use sumMerge to get final result SELECT

DataMore大数据实时决策能力 一切以用户价值为依归 17 业务应用实践 iData 2 新大数据分析引擎2.0 业界传统 大数据分析 引擎 大数据分析引擎&存储 Analytical Engine & Database 大数据仓库 Hadoop Data Lake 计算引擎 MR & Spark Data Warehouse OLTP Big Data Analysis Generator Execute Engine Data Mapper Col- 1 Col- 1 Col… Aggregate Merger Executor-1 Bitmap Filter Builder Dynamic Bitmap Index Cache Bitmap Index Generator Execute Engine Data Mapper Executor-2 Bitmap Filter Builder Dynamic Bitmap Index Cache Bitmap Index Generator Execute Engine Data Mapper Col- 1 Col- 1 Col… Aggregate Merger Executor-3 一切以用户价值为依归 Data Extract

直达ClickHouse的Bulkload稳定性，性能都更佳 Unique Engine v ⽬标：⽀持UpSert，Delete操作，提升查询性能 v 设计：delete on insert Unique Engine v write-write冲突依靠table level lock控制 v write-merge冲突： Unique Engine v 常驻内存模式对内存消耗很⼤ v ⾮常驻内存模式index

CRUD directly Can’t update/delete table frequently in Clickhouse Possible Solutions 2. MySQL Engine Not suitable for big tables Not suitable for MongoDB Possible Solutions 3. Reinit whole table ● Mutations are stuck (KILL MUTATION) ● Zookeeper OOM because of SQL length (Put ids in a Memory Engine temp table) Final Product ● Only one config file needed for a new Clickhouse table ● Init and