文档HOT
中文
注册
我要评分
文档获取效率
文档正确性
内容完整性
文档易理解
提交
在线提单
论坛求助

在ClickHouse引擎的应用

OmniMV物化视图在ClickHouse引擎共有五种应用场景，可解析SQL得到相应的视图，请根据实际情况查看相对应的使用示例。

OmniMV物化视图在ClickHouse引擎共有五种应用场景，使用方法如下：

场景一：从ClickHouse执行日志中获取SQL，直接解析SQL语句得到projection，解析推荐步骤不占用ClickHouse服务端资源。

分布式表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
database = ssb #配置目标数据库
work_path = ./clickhouse #配置OmniMV SQL文件、视图文件、模型文件等工作文件的存储路径
ck_host = 0.0.0.0 #配置ClickHouse服务IP地址
ck_port = 9000 #配置ClickHouse服务端口
cluster_name = mycluster #配置集群名称
projection_wait_time = 3600 #创建projection超时时间
is_distributed_table = True #配置ClickHouse表类型，False表示为本地表，True表示为分布式表
clickhouse_tables = lineorder_flat_all #配置目标表，多个表使用','分割，要求表必须是目标数据库中的表
query_log_table = system.query_log #提取SQL的日志表
sql_running_time_threshold = 100 #配置SQL执行时间阈值，单位：ms,低于此阈值的SQL不会被处理
query_limit_each_table = 15 #配置为每个目标表分析至多多少条SQL
projection_limit_each_table = 3 #配置每张表创建projection的数量
projection_with_conditional_functions = False #是否为包含条件的聚合函数推荐Projection
allows_materialize_projection = True #是否为新建的projection物化历史数据

本地表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
database = ssb #配置目标数据库
work_path = ./clickhouse #配置OmniMV SQL文件、视图文件、模型文件等工作文件的存储路径
ck_host = 0.0.0.0 #配置ClickHouse服务IP地址
ck_port = 9000 #配置ClickHouse服务端口
projection_wait_time = 3600 #创建projection超时时间
is_distributed_table = False #配置ClickHouse表类型，False表示为本地表，True表示为分布式表
clickhouse_tables = lineorder_flat #配置目标表，多个表使用','分割，要求表必须是目标数据库中的表
query_log_table = system.query_log #提取SQL的日志表
sql_running_time_threshold = 100 #配置SQL执行时间阈值，单位：ms,低于此阈值的SQL不会被处理
query_limit_each_table = 15 #配置为每个目标表分析至多多少条SQL
projection_limit_each_table = 3 #配置每张表创建projection的数量
projection_with_conditional_functions = False #是否为包含条件的聚合函数推荐Projection
allows_materialize_projection = True #是否为新建的projection物化历史数据

执行步骤：

  1. 进入OmniMV物化视图安装目录。
  2. 初始化数据库信息。
    1
    python main.pyc clickhouse init_database
  3. 从query_log中提取包含目标表查询的SQL。
    1
    python main.pyc clickhouse extract_sqls
  4. 解析提取的SQL，获得推荐出的projection。
    1
    python main.pyc clickhouse parse_sql_text
  5. 在目标表中创建推荐出来的projection。
    1
    python main.pyc clickhouse create_projections

场景二：从ClickHouse执行日志中获取SQL，解析SQL语句得到projection，需要连接数据库执行压缩比查询，推荐算法使用贪心算法。解析推荐步骤占用ClickHouse服务端较多资源。

分布式表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
database = ssb #配置目标数据库
work_path = ./clickhouse #配置OmniMV SQL文件、视图文件、模型文件等工作文件的存储路径
ck_host = 0.0.0.0 #配置ClickHouse服务IP地址
ck_port = 9000 #配置ClickHouse服务端口
cluster_name = mycluster #配置集群名称
projection_wait_time = 3600 #创建projection超时时间
projection_compress_ratio = 0.8 #压缩比阈值，数据压缩比大于次阈值的Projection不推荐
is_distributed_table = True #配置ClickHouse表类型，False表示为本地表，True表示为分布式表
clickhouse_tables = lineorder_flat_all #配置目标表，多个表使用','分割，要求表必须是目标数据库中的表
query_log_table = system.query_log #提取SQL的日志表
sql_running_time_threshold = 100 #配置SQL执行时间阈值，单位：ms,低于此阈值的SQL不会被处理
query_limit_each_table = 15 #配置为每个目标表分析至多多少条SQL
projection_limit_each_table = 3 #配置每张表创建projection的数量
projection_with_conditional_functions = False #是否为包含条件的聚合函数推荐Projection
allows_materialize_projection = True #是否为新建的projection物化历史数据
candidate_strategy = compress_ratio #选择compress_ratio表示按照projection压缩比来排序，选择SQL，表示按照projection可以匹配SQL的数量来排序

本地表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
database = ssb #配置目标数据库
work_path = ./clickhouse #配置OmniMV SQL文件、视图文件、模型文件等工作文件的存储路径
ck_host = 0.0.0.0 #配置ClickHouse服务IP地址
ck_port = 9000 #配置ClickHouse服务端口
projection_wait_time = 3600 #创建projection超时时间
projection_compress_ratio = 0.8 #压缩比阈值，数据压缩比大于次阈值的Projection不推荐
is_distributed_table = False #配置ClickHouse表类型，False表示为本地表，True表示为分布式表
clickhouse_tables = lineorder_flat #配置目标表，多个表使用','分割，要求表必须是目标数据库中的表
query_log_table = system.query_log #提取SQL的日志表
sql_running_time_threshold = 100 #配置SQL执行时间阈值，单位：ms,低于此阈值的SQL不会被处理
query_limit_each_table = 15 #配置为每个目标表分析至多多少条SQL
projection_limit_each_table = 3 #配置每张表创建projection的数量
projection_with_conditional_functions = False #是否为包含条件的聚合函数推荐Projection
allows_materialize_projection = True #是否为新建的projection物化历史数据
candidate_strategy = compress_ratio #选择compress_ratio表示按照projection压缩比来排序，选择SQL，表示按照projection可以匹配SQL的数量来排序
  1. 进入OmniMV物化视图安装目录。
  2. 初始化数据库信息。
    1
    python main.pyc clickhouse init_database
  3. 从query_log中提取包含目标表查询的SQL。
    1
    python main.pyc clickhouse extract_sqls
  4. 解析提取的SQL，获得推荐出的projection。
    1
    python main.pyc clickhouse generate_views
  5. 在目标表中创建推荐出来的projection。
    1
    python main.pyc clickhouse create_projections

场景三：从ClickHouse执行日志中获取SQL，解析SQL语句得到Projection，推荐算法使用AI模型，模型训练步骤占用ClickHouse服务端较多资源。

分布式表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
database = ssb
work_path = ./clickhouse
ck_host = 0.0.0.0
ck_port = 19000
cluster_name = mycluster
num_round = 5
projection_wait_time = 3600
projection_compress_ratio = 0.8
sql_running_time_threshold = 100
projection_limit_each_table = 5
is_distributed_table = True
clickhouse_tables = lineorder_flat_all
query_log_table = system.query_log
query_limit_each_table = 15
projection_with_conditional_functions = False
allows_materialize_projection = True
max_data_count = 20
epochs = 100
estimators = 50
max_depth = 5
learning_rate = 0.05
candidate_strategy = compress_ratio

本地表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
database = ssb
work_path = ./clickhouse
ck_host = 0.0.0.0
ck_port = 19000
num_round = 5
projection_wait_time = 3600
projection_compress_ratio = 0.8
sql_running_time_threshold = 100
projection_limit_each_table = 5
is_distributed_table = False
clickhouse_tables = lineorder_flat
query_log_table = system.query_log
query_limit_each_table = 15
projection_with_conditional_functions = False
allows_materialize_projection = True
max_data_count = 20
epochs = 100
estimators = 50
max_depth = 5
learning_rate = 0.05
candidate_strategy = compress_ratio
  1. 进入OmniMV物化视图安装目录。
  2. 初始化数据库信息。
    1
    python main.pyc clickhouse init_database
  3. 从query_log中提取包含目标表查询的SQL。
    1
    python main.pyc clickhouse extract_sqls
  4. 解析提取的SQL，获得推荐出的projection。
    1
    python main.pyc clickhouse generate_views
  5. 进行代价评估模型训练。
    1
    python main.pyc clickhouse cost_estimation
  6. 进行推荐模型训练。
    1
    python main.pyc clickhouse recommend
  7. 在目标表中创建模型推荐出来的projection。
    1
    python main.pyc clickhouse create_projections_model

场景四：从ClickHouse执行日志中再次获取新SQL，解析SQL语句得到Projection，使用训练好的AI模型推荐Projection，解析推荐步骤不占用ClickHouse服务端资源。

分布式表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
database = ssb
work_path = ./clickhouse
ck_host = 0.0.0.0
ck_port = 19000
cluster_name = mycluster
projection_wait_time = 3600
sql_running_time_threshold = 100
projection_limit_each_table = 5
is_distributed_table = True
clickhouse_tables = lineorder_flat_all
query_log_table = system.query_log
query_limit_each_table = 15
projection_with_conditional_functions = False
allows_materialize_projection = True
candidate_strategy = compress_ratio

本地表场景下的配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
database = ssb
work_path = ./clickhouse
ck_host = 0.0.0.0
ck_port = 19000
projection_wait_time = 3600
sql_running_time_threshold = 100
projection_limit_each_table = 5
is_distributed_table = False
clickhouse_tables = lineorder_flat
query_log_table = system.query_log
query_limit_each_table = 15
projection_with_conditional_functions = False
allows_materialize_projection = True
candidate_strategy = compress_ratio
  1. 进入OmniMV物化视图安装目录。
  2. 提取新SQL。
    1
    python main.pyc clickhouse extract_sqls_incremental
  3. 解析新SQL获取projection视图。
    1
    python main.pyc clickhouse parse_sql_text_incremental
  4. 使用训练好的模型直接推荐。
    1
    python main.pyc clickhouse recommend
  5. 可选：可以用新SQL对AI模型进行增量训练，需要占用ClickHouse服务端较多资源。
    1
    python main.pyc clickhouse cost_estimation_incremental

场景五：对于多表查询的SQL，解析直接得到物化视图，无推荐策略。

多表场景下的配置文件：

1
2
3
4
5
6
7
database = imdbload
work_path = ./clickhouse
ck_host = 0.0.0.0
ck_port = 19000
clickhouse_tables = all
query_log_table = system.query_log
query_limit_mutil_table = 15
  1. 进入OmniMV物化视图安装目录。
  2. 初始化数据库信息。
    1
    python main.pyc clickhouse init_database
  3. 捞取多表查询SQL并解析出物化视图。
    1
    python main.pyc clickhouse generate_materialize_view