Oracle removing method based on Clickhome

Abstract

The invention discloses an Oracle removing method based on Clickhouse, which comprises the steps of data model design, real-time and off-line data complete storage and Clickhouse complete monitoring, and is characterized by comprising the following steps of: 1, designing a data model based on a materialized view of the Clickhouse; 2, developing offline and real-time tasks based on Spark and Flink, and respectively writing results into Hive and Topic; step 3, completely synchronizing the off-line and real-time data bins to the Clickhouse; and step 4, on the basis of the Grafana and the Prometheus, the Clickhome is monitored, and the Clickhome is monitored on the basis of the Grafana and the Prometheus. And a set of online query analysis model construction method for replacing Oracle from a data model to data storage, data analysis and Clickhome monitoring is formed.

Description

Technical Field

[0001] The present invention relates to the application field of big data OLAP engines, and in particular to an Oracle-free method based on Clickhouse. Background Art

[0002] With the rapid development of the Internet, a large amount of user data is generated, and big data computing capabilities are also developing rapidly. This poses a huge challenge to traditional Internet companies that have been using Oracle as a database engine for online query and analysis models in terms of query speed, usage cost, and data timeliness. Traditional relational databases such as Oracle are already stretched to the limit for processing massive amounts of data, and Oracle also has high copyright fees and disk storage costs. In addition, it is difficult for Oracle to perform real-time queries. Therefore, in this field, Oracle-free databases are usually referred to as "de-Oracle". Based on the background of de-Oracle, systems for massive data analysis have emerged, among which big data platforms built on Hadoop and Hive are the most widely used.

[0003] Hive’s disk-based computing feature means that it can only be used for offline data analysis. Although Apache Spark integrates Hadoop and Hive well, it cannot achieve real-time data storage.

[0004] The solutions based on Impala and Kudu also have obvious defects. As the amount of data in Kudu increases, it takes a long time for the Node to recover after Kudu is restarted, and the operation and maintenance cost is high.

[0005] At the same time, the industry generally uses the method of directly importing the underlying data files of the Hive table into Clickhouse and Spark's Clickhouse Jdbc connection to synchronize data from the offline Hive table to Clickhouse. The former involves the direct exchange of files between machines, which is cumbersome and difficult to roll back. The latter has a simpler data synchronization method, but if Spark's parallel parameters are used for data synchronization, the synchronization speed is faster, but the synchronized Clickhouse data will be duplicated. If single-threaded synchronization is used, data synchronization is time-consuming.

[0006] ClickHouse is a column-based database management system (DBMS) for online analytical processing (OLAP). It is widely used in today's big data analysis field and has strong advantages in query speed, data timeliness, and maintenance costs. The design of building a data model based on ClickHouse, real-time, offline data storage, data deduplication query and complete monitoring of ClickHouse are very necessary for companies to reduce costs and increase efficiency. Existing patents focus on the advantages of ClickHouse and explain many methods for ClickHouse to process traffic data:

[0007] Patent CN116501778A-Real-time user behavior data analysis method based on ClickHouse belongs to the field of data analysis technology, including event analysis, funnel analysis, retention analysis, attribution analysis, user path analysis, attribute analysis and interval analysis, among which, funnel analysis is used to calculate the performance of target users in a series of set click events, retention analysis is used to calculate the user retention in a specified time period, and attribution analysis is used to calculate the number of clicks, the number of valid users and the conversion value of the event to be attributed; user path analysis aims to understand the combination of different click events, attribute analysis can filter specific user sets according to set attributes or labels, and obtain the appearance ratio of each attribute or label; interval analysis is used to calculate the user's conversion time and the interval between existing information, so as to deeply explore the meaning and rules behind the user behavior data, and provide a more comprehensive analysis method for real-time user behavior management. The patent lists in detail the use of each analysis model in Clickhouse, but does not introduce the situation of Clickhouse data storage, monitoring and other aspects.

[0008] Patent CN116010451A-A business data query method, system and medium based on Clickhouse database, the method includes: according to the business processing request sent by the user, determine the request type corresponding to the business processing request; when the request type is a data change type, obtain the required original data from the preset original database according to the business processing request; process the original data to obtain the processed target data, and reorganize the business data processing logic corresponding to the business processing request to obtain the reorganized target business data processing logic; write the target data to the Clickhouse database according to the target business data processing logic; receive the business data query request sent by the user, and merge the specified business data query request in the business data query request, obtain the business data corresponding to the business data query request through the Clickhouse database, and return the business data to the user. This patent is a typical use of Clickhouse as a business backend query library, the front end sends a request to the back end, and then returns the results of the Clickhouse query, replacing the traditional relational database Oracle and Mysql as the backend query library.

[0009] Patent CN116089535A-Data synchronization method, device, equipment and storage medium, provides a data synchronization method, device, equipment and storage medium. The method identifies the fields to be synchronized based on the request scenario; obtains the field information of the fields to be synchronized from the Hive data warehouse; creates a local data table in the ClickHouse database based on the field information; creates a Hive engine table in the ClickHouse database based on the location information of the fields to be synchronized in the Hive data warehouse; generates a query statement based on the traversal of the Hive engine table; based on the query statement, queries the Hive data warehouse to obtain query data, and writes the query data into the local data table, which can achieve data synchronization efficiently and accurately. However, for large amounts of data, the query performance of the Clickhouse table of the Hive engine is very poor.

[0010] Based on the above existing technologies, there is a need for a more efficient and applicable method for building an online query and analysis model that replaces Oracle, from data model to data warehousing, data analysis and Clickhouse monitoring. Summary of the invention

[0011] The technical problem to be solved by the present invention is to overcome the shortcomings of the prior art. There are few prior arts that can safely, quickly and completely import offline Hive table data into Clickhouse, which makes Clickhouse less applicable than Oracle in practice. The present invention provides a method for building an online query analysis model based on Clickhouse to replace Oracle. Based on Clickhouse, the entire process from data model to data storage, data analysis and Clickhouse monitoring is described, and the focus is on importing data from offline Hive tables or HDFS files into CLickhouse safely and quickly while ensuring data integrity. The method has both advantages in the read and write performance of large amounts of data and great advantages in reducing maintenance costs.

[0012] In order to solve the above technical problems, the present invention provides a method for removing O based on Clickhouse, which is characterized by comprising the following steps:

[0013] Step 1: Design the Clickhouse business database table based on business requirements and Clickhouse's ReplicatedReplacingMergeTree table engine;

[0014] Step 2: Offline data is calculated through SparkSql and stored in the Hive table; real-time data is calculated in real time through Flink, and the real-time calculation results are sent to the Topic;

[0015] Step 3: Synchronize offline data in Hive tables to Clickhouse. Create corresponding external tables in Clickhouse for the Hive tables and HDFS files that need to be synchronized. Use the insert into statement in Clickhouse to synchronize Clickhouse external table data to Clickhouse. When using Hive and HDFS engine tables of Cilckhouse, modify the Clickhouse configuration file according to the Clickhouse documentation and create a

[0016] / etc / clickhouse-server / config.d / hdfs.xml file, and place the hdfs-site.xml file in the Hadoop cluster at

[0017] In / etc / hadoop / conf / , select ENGINE = HIVE / HDFS when creating a Hive / HDFS engine table. Real-time data is stored through Clickhouse's Kafka table engine, connected to the Topic in step 2.

[0018] Step 4: Perform maintenance monitoring and alerting on Clickhouse based on Prometheus and Grafana. The maintenance monitoring includes Clickhouse cluster machines and daily query monitoring.

[0019] In the step 1, in the solution of designing Clickhouse to replace Oracle, when designing the data model, based on the ReplicatedReplacingMergeTree table engine of Clickhouse, the data details and various dimensions are combined into a table at the bottom layer, and the upper layer query uses materialized views.

[0020] In step 3, for offline data, after establishing external tables of Hive and HDFS engines in Clickhouse, a connection is established between Clickhouse and Hive tables, so that Hive table data can be read in the Clickhouse cluster by directly querying Hive external tables of Clickhouse. Finally, the data read from the external table in the Clickhouse cluster is inserted into the target Clickhouse business library table in step 1 to complete data synchronization. For real-time data, Clickhouse has a special Kafka engine to connect to the Topic. After the real-time data developed by Flink is connected to the Topic, the Kafka engine table of Clickhouse is established, and the Clickhouse materialized view is established to persist the Kafka engine table data in the Clickhouse business library table.

[0021] In step 4, the operation and maintenance monitoring also includes: modifying the configuration files of Clickhouse and Prometheus so that Prometheus obtains Clickhouse indicators, and the alarm includes displaying the dashboard and early warning of indicators through Grafana.

[0022] The beneficial effects achieved by the present invention are: a whole process construction method from data model to data storage, data analysis and Clickhouse monitoring, thereby replacing the application of traditional relational databases in online queries, based on ClickHouse, offline and real-time data can be quickly stored, indicator calculation and query are realized based on materialized views, and finally the monitoring and operation of Clickhouse are performed through Prometheus+Grafana. The purpose of reducing costs and increasing efficiency is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 A flow chart of method steps of an exemplary embodiment of the present invention.

[0024] Figure 2 The figure is a flow chart of a method for removing Oracle from Clickhouse in an exemplary embodiment of the present invention.

[0025] Figure 3 It is a schematic diagram of a de-Oracle system based on Clickhouse in an exemplary embodiment of the present invention. DETAILED DESCRIPTION

[0026] The present invention will be further described below in conjunction with the accompanying drawings and exemplary embodiments:

[0027] When designing a Clickhouse solution to replace Oracle, synchronizing Hive offline data to Clickhouse is a difficult point. When synchronizing Hive offline data to Clickhouse, there is a way to directly import the underlying data files of the Hive table into Clickhouse. This solution involves direct file exchange between machines, and even file exchange between machines across clusters. The process is cumbersome and the operation is not easy to roll back. Alternatively, the Spark+JDBC method is used to synchronize data in parallel. The data synchronization speed is faster, but because Spark imports data in parallel, if the parallel subtask fails halfway, when another subtask is restarted, because the previous subtask has already imported part of the data into Clickhouse, all synchronized Clickhouse data will be duplicated. If it is single-threaded synchronization, synchronizing data is time-consuming. Clickhouse's external table has a dedicated Hive table engine and HDFS engine, which correspond to Hive's partitioned table and non-partitioned table respectively. Using Clickhouse external tables to synchronize Hive offline data, combined with the characteristics of Clickhouse column storage, can perfectly solve the above problems.

[0028] like Figure 1 The step flow of a Clickhouse-based O removal method in the exemplary embodiment of the present invention shown includes:

[0029] Step 1: Based on business requirements and Clickhouse's ReplicatedReplacingMergeTree table engine, design Clickhouse business library tables; in the solution of designing Clickhouse to replace Oracle, when designing the data model, for offline data, based on Clickhouse's ReplicatedReplacingMergeTree table engine, combined with the characteristics of Clickhouse column storage, the data details and various dimensions are combined into a table at the bottom layer, and the upper layer query uses materialized views; for real-time data, it is also necessary to create a Kafka engine table, and create a materialized view to persist the Kafka engine table data to the Clickhouse business library table. Compared with the direct storage of Oracle database, the ReplicatedReplacingMergeTree table engine can automatically back up the imported data and deduplicate the joint primary key data, thereby improving the security and accuracy of the data.

[0030] Step 2: Offline data is calculated through SparkSql and stored in the Hive table; real-time data is calculated in real time through Flink, and the real-time calculation results are sent to the Topic; Spark and Flink technologies are commonly used to develop data indicators for offline data and real-time data. Spark has a higher batch processing speed and Flink has a higher stream processing performance.

[0031] Step 3: Synchronize offline data in Hive table to Clickhouse. Create corresponding external tables in Clickhouse for the Hive tables and HDFS files that need to be synchronized. Use insert into statement in Clickhouse to synchronize the data of Clickhouse external table to Clickhouse. Real-time data is connected to the Topic in step 2 through Clickhouse's Kafka table engine for data storage. For offline data, Clickhouse's external tables have special Hive table engines and HDFS engines, corresponding to Hive partitioned tables and non-partitioned tables respectively. After the external tables of Hive and HDFS engines are established in Clickhouse, the connection between Clickhouse and Hive tables is established in the Clickhouse cluster, so that the Hive table data can be read in the Clickhouse cluster by directly querying the Hive external table of Clickhouse. Finally, the data read from the external table in the Clickhouse cluster is inserted into the target Clickhouse business library table in step 1 to complete the data synchronization. All the above operations are completed in the Clickhouse cluster and can be completed directly using SQL. There is no need to connect to the Hadoop cluster first in development, use Hive JDBC to read the data, and then connect to the Clickhouse cluster / Oracle database to write the read data into the Clickhouse / Oracle table using Clickhouse / Oracle JDBC. Compared with the general Hive data import solution to Clickhouse and Oracle, the operation is simpler and the synchronization speed is faster; for real-time data, Clickhouse has a special Kafka engine to connect to Topic. After the real-time data developed by Flink is connected to Topic, the Kafka engine table of Clickhouse is established, and the Clickhouse materialized view is established to persist the Kafka engine table data to the Clickhouse business library table. Oracle database is not suitable for real-time data writing.

[0032] Step 4: Based on Prometheus and Grafana, clickhouse is operated and monitored and alarmed. The operation and maintenance monitoring includes clickhouse cluster machines and daily query monitoring, and also includes: modifying the configuration files of clickhouse and Prometheus so that Prometheus can obtain clickhouse indicators. The alarm includes displaying the big picture and early warning of indicators through Grafana. This set of clickhouse cluster machines and daily query monitoring system is relatively mature. In particular, the monitoring of daily queries is more complete than Oracle.

[0033] like Figure 2 The process of removing O based on Clickhouse in the exemplary embodiment of the present invention shown in the figure includes the following specific steps:

[0034] S11: Based on business needs, for offline data, create the ReplicatedReplacingMergeTree engine table of Clickhouse. The table creation statement is as follows:

[0035] CREATE TABLE test.test_table ON CLUSTER'ck_replica1' (

[0037] etl_time Datetime64,

[0038] perf_id String,

[0039] payment_amount Nullable(Decimal64(4)) ...... ...... ...... )

[0044] ENGINE=ReplicatedReplacingMergeTree(' / clickhouse / table / all / test / test_table','{replica}',etl_time)

[0045] ORDER BY xxHash64(perf_id)

[0046] SAMPLE BY xxHash64(perf_id)

[0047] SETTINGS storage_policy='test';

[0048] To create a table for real-time data, first create a Clickhouse Topic engine table, such as:

[0049] CREATE TABLE test.kafka_test_table ON CLUSTER'ck_replica1'(

[0050] etl_time Datetime64,

[0051] perf_id String,

[0052] payment_amount Nullable(Decimal64(4)) ...... ......

[0055] )ENGINE=Kafka()

[0056] SETTINGS

[0057] KAFKA_BROKER_LIST='127.0.0.1:9092',

[0058] KAFKA_TOPIC_LIST='topic_name',

[0059] KAFKA_GROUP_NAME='group_name',

[0060] KAFKA_NUM_CONSUMERS=1,

[0061] KAFKA_FORMAT='JSONEachRow';

[0062] Finally, create a Clickhouse materialized view to persist the data of the Kafka engine table test.kafka_test_table to the Clickhouse business library table test.test_table, such as:

[0063] CREATE MATERIALIZEDVIEW test.view_test_table ON CLUSTER'ck_replica1'

[0064] TO test.test_table AS

[0065] SELECT *

[0066] FROM test.kafka_test_table;.

[0067] S12: Process offline data. First, use Sparksql to develop data. After development, store the data in the Hive table. Real-time data development uses Flink technology for real-time calculation and sends the calculation results to Topic.

[0068] S13: When using Hive and HDFS engine tables of Clickhouse, you need to modify the configuration first according to the Clickhouse documentation.

[0069] (1) Create the / etc / clickhouse-server / config.d / hdfs.xml file and grant permissions: chownclickhouse:clickhouse hdfs.xml. The file content is as follows:

[0070] <clickhouse>

[0071] <hdfs>

[0072] <libhdfs3_conf> / etc / hadoop / conf / hdfs-site.xml< / libhdfs3_conf>

[0073] < / hdfs>

[0074] < / clickhouse>

[0075] (2) Place the hdfs-site.xml file in / etc / hadoop / conf / and grant permissions: chownclickhouse:clickhousehdfs-site.xml.

[0076] (3) Modify / etc / hosts to add hdfs cluster hosts.

[0077] (4) Restart the service: systemctl restart clickhouse-server.

[0078] Then, according to the requirements of Clickhouse's documentation, determine whether the Hive table is a partitioned table, create the Hive engine table and the HDFS engine table. The creation statement sample is as follows:

[0079]

[0080] Finally, data synchronization is performed in the Clickhouse cluster, data is read from the Clickhouse external table, and written to the target table.

[0081] The following are sample execution statements:

[0082] INSERT INTO TABLE DEFAULT.CK_TEST SELECT*FROM DEFAULT.CK_TEST_HIVES14: Use Prometheus+Grafana to monitor Clickhouse. Edit the config.xml file of Clickhouse, release the comments about Prometheus, and then change the prometheus.yml file, add the IP address of the Clickhouse machine to be monitored to the prometheus.yml file, restart prometheus and check the page to find the monitored indicators. In addition, select the Prometheus data source in Grafana, you can configure the Clickhouse-related indicator monitoring dashboard, and for special indicators, you can also configure alarms in Grafana, which is very convenient.

[0083] like Figure 3 The schematic diagram of the de-Oracle system based on Clickhouse in the exemplary embodiment of the present invention shown includes:

[0084] For offline data, the indicator calculation of offline data is processed in the SpqrkSql module, and then the data is stored in the Hive data source module. Then the Hive / HDFS engine table of Clickhouse is used to import the data into the business library table in the Clickhouse module. For real-time data, the indicator calculation of real-time data is processed in the Flink module, and then the data is sent to the Topic module in real time. Then the Kafka engine table and materialized view of Clickhouse are used to persist the real-time data in the business library table in the Clickhouse module. The front-end module passes the query Sql to the back-end module, and the back-end module calls the business library table from the Clickhouse module and passes the query data to the front-end for display. In the Prometheus module, Prometheus monitors the Clickhouse machine and query indicator data, and passes the indicator to the Grafana module for Grafana visualization.

[0085] The present invention is mainly used to provide a method for removing O based on Clickhouse. Based on ClickHouse, it replaces the function of traditional relational database in back-end query, and forms a set of standardized processes from data model to data storage, data analysis and Clickhouse monitoring. It can not only provide traffic data for the business quickly and stably, but also timely discover alarms in operation and maintenance to ensure the accuracy of data. Thereby improving business efficiency and achieving cost reduction and efficiency improvement. At the same time, when Hive offline data is imported into Clickhouse table, Clickhouse's Hive and HDFS external table functions are used, and simple Sql statements are used to quickly and safely import data from Hive table into Clickhouse. Different from the traditional method of using Hive / Clickhouse JDBC, it operates across clusters and constructs a fast and effective Clickhouse offline data import method.

[0086] The present invention may also have many other implementation modes. The above embodiments do not limit the present invention in any way. Without departing from the spirit and essence of the present invention, technicians familiar with the field may make various corresponding changes and modifications based on the present invention. All other improvements and applications made to the above embodiments in an equivalent transformation manner should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A method for removing Oracle based on Clickhouse, characterized in that: The following steps are involved: Step 1: Design the Clickhouse business database table based on business requirements and Clickhouse's ReplicatedReplacingMergeTree table engine; Step 2: Offline data is calculated through SparkSql and stored in the Hive table; real-time data is calculated in real time through Flink, and the real-time calculation results are sent to the Topic; Step 3: Synchronize offline data in Hive tables to Clickhouse. Create corresponding external tables in Clickhouse for the Hive tables and HDFS files that need to be synchronized. Use the insert into statement in Clickhouse to synchronize Clickhouse external table data to Clickhouse. When using Hive and HDFS engine tables of Cilckhouse, modify the Clickhouse configuration file according to the Clickhouse documentation and create a / etc / clickhouse-server / config.d / hdfs.xml file, and put the hdfs-site.xml file in the Hadoop cluster under / etc / hadoop / conf / . When creating the Hive / HDFS engine table, select ENGINE=HIVE / HDFS. Real-time data is stored through Clickhouse's Kafka table engine, connected to the Topic in step 2. Step 4: Perform maintenance monitoring and alerting on Clickhouse based on Prometheus and Grafana. The maintenance monitoring includes Clickhouse cluster machines and daily query monitoring.

2. The method for removing Oracle based on Clickhouse as claimed in claim 1, characterized in that: In the design of the Clickhous business library table, when designing the data model, based on the ReplicatedReplacingMergeTree table engine of Clickhouse, the data details and various dimensions are combined into a table at the bottom layer, and the upper-level query uses materialized views.

3. The method for removing Oracle based on Clickhouse as claimed in claim 2, characterized in that: In step 3, for offline data, after establishing external tables of Hive and HDFS engines in Clickhouse, a connection is established between Clickhouse and Hive tables, so that Hive table data can be read in the Clickhouse cluster by directly querying Hive external tables of Clickhouse. Finally, the data read from the external table in the Clickhouse cluster is inserted into the target Clickhouse business library table in step 1 to complete data synchronization. For real-time data, Clickhouse has a special Kafka engine to connect to the Topic. After the real-time data developed by Flink is connected to the Topic, the Kafka engine table of Clickhouse is established, and the Clickhouse materialized view is established to persist the Kafka engine table data in the Clickhouse business library table.

4. The method for removing Oracle based on Clickhouse as claimed in claim 3, characterized in that: In step 4, the operation and maintenance monitoring also includes: modifying the configuration files of Clickhouse and Prometheus so that Prometheus obtains Clickhouse indicators, and the alarm includes displaying the dashboard and early warning of indicators through Grafana.

Images