--- title: OmniMV物化视图在ClickHouse引擎的应用 description: "OmniMV物化视图在ClickHouse引擎共有五种应用场景，使用方法如下：" url: https://www.hikunpeng.com/document/detail/zh/kunpengboostkithistory/230RC2/bds/kunpengbds_omniruntime_20_0073.html sourcePath: /source/zh/kunpengboostkithistory/230RC2/bds/kunpengbds_omniruntime_20_0073.html indexId: d937b9ae27a441922184138c46419c183f337adc74dc1b2bae524980a872d4f080 --- # OmniMV物化视图在ClickHouse引擎的应用 OmniMV物化视图在ClickHouse引擎共有五种应用场景，使用方法如下： 场景一：从ClickHouse执行日志中获取SQL，直接解析SQL语句得到projection，解析推荐步骤不占用ClickHouse服务端资源。 分布式表场景下的配置文件： ``` database = ssb #配置目标数据库 work_path = ./clickhouse #配置物化视图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物化历史数据 ``` 本地表场景下的配置文件： ``` database = ssb #配置目标数据库 work_path = ./clickhouse #配置物化视图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. 进入物化视图安装目录。 2. 初始化数据库信息。 ``` python main.pyc clickhouse init_database ``` 3. 从query_log中提取包含目标表查询的SQL。 ``` python main.pyc clickhouse extract_sqls ``` 4. 解析提取的SQL，获得推荐出的projection。 ``` python main.pyc clickhouse parse_sql_text ``` 5. 在目标表中创建推荐出来的projection。 ``` python main.pyc clickhouse create_projections ``` 场景二：从ClickHouse执行日志中获取SQL，解析SQL语句得到projection，需要连接数据库执行压缩比查询，推荐算法使用贪心算法。解析推荐步骤占用ClickHouse服务端较多资源。 分布式表场景下的配置文件： ``` database = ssb #配置目标数据库 work_path = ./clickhouse #配置物化视图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的数量来排序 ``` 本地表场景下的配置文件： ``` database = ssb #配置目标数据库 work_path = ./clickhouse #配置物化视图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. 初始化数据库信息。 ``` python main.pyc clickhouse init_database ``` 3. 从query_log中提取包含目标表查询的SQL。 ``` python main.pyc clickhouse extract_sqls ``` 4. 解析提取的SQL，获得推荐出的projection。 ``` python main.pyc clickhouse generate_views ``` 5. 在目标表中创建推荐出来的projection。 ``` python main.pyc clickhouse create_projections ``` 场景三：从ClickHouse执行日志中获取SQL，解析SQL语句得到Projection，推荐算法使用AI模型，模型训练步骤占用ClickHouse服务端较多资源。 分布式表场景下的配置文件： ``` 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 ``` 本地表场景下的配置文件： ``` 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. 初始化数据库信息。 ``` python main.pyc clickhouse init_database ``` 3. 从query_log中提取包含目标表查询的SQL。 ``` python main.pyc clickhouse extract_sqls ``` 4. 解析提取的SQL，获得推荐出的projection。 ``` python main.pyc clickhouse generate_views ``` 5. 进行代价评估模型训练。 ``` python main.pyc clickhouse cost_estimation ``` 6. 进行推荐模型训练。 ``` python main.pyc clickhouse recommend ``` 7. 在目标表中创建模型推荐出来的projection。 ``` python main.pyc clickhouse create_projections_model ``` 场景四：从ClickHouse执行日志中再次获取新SQL，解析SQL语句得到Projection，使用训练好的AI模型推荐Projection，解析推荐步骤不占用ClickHouse服务端资源。 分布式表场景下的配置文件： ``` 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 ``` 本地表场景下的配置文件： ``` 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。 ``` python main.pyc clickhouse extract_sqls_incremental ``` 3. 解析新SQL获取projection视图。 ``` python main.pyc clickhouse parse_sql_text_incremental ``` 4. 使用训练好的模型直接推荐。 ``` python main.pyc clickhouse recommend ``` 5. 可选：可以用新SQL对AI模型进行增量训练，需要占用ClickHouse服务端较多资源。 ``` python main.pyc clickhouse cost_estimation_incremental ``` 场景五：对于多表查询的SQL，解析直接得到物化视图，无推荐策略。 多表场景下的配置文件： ``` 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. 初始化数据库信息。 ``` python main.pyc clickhouse init_database ``` 3. 捞取多表查询SQL并解析出物化视图。 ``` python main.pyc clickhouse generate_materialize_view ```