Clickhouse-based method for replacing oracle

Abstract

Disclosed in the present invention is a ClickHouse-based method for replacing Oracle. The method comprises the design of a data model; real-time, offline and complete data warehousing; and complete monitoring via ClickHouse. The method is characterized in comprising: step 1, designing a data model on the basis of a materialized view of ClickHouse; step 2, developing a real-time offline task on the basis of Spark and Flink, and respectively writing results into Hive and Topic; step 3, fully synchronizing a real-time offline data warehouse to ClickHouse; and step 4, on the basis of Grafana and Prometheus, monitoring ClickHouse. A method for establishing a complete online query and analysis model which involves a data model, data warehousing, data analysis and ClickHouse monitoring is formed to replace Oracle.

Description

Clickhouse-based oracle-removing method TECHNICAL FIELD

[0001] The present application relates to the field of big data OLAP engine, in particular to a Clickhouse-based oracle-removing method. BACKGROUND

[0002] Under the background of rapid development of the current Internet, a large amount of user data is generated, and big data computing capability also develops rapidly. This poses a huge challenge to traditional Internet companies that have been using Oracle as an online query analysis model for database engines in terms of query speed, use cost, and data timeliness. Traditional relational databases such as Oracle have been struggling to handle massive amounts of data, and Oracle also has high usage copyright fees and disk storage costs. In addition, it is difficult for Oracle to achieve real-time query. Therefore, the field generally refers to the oracle-removing database as "GoO". Under the background of GoO, a system for massive data analysis has emerged, and the big data platform based on Hadoop and Hive is the most widely used.

[0003] Hive is based on disk computing and can only be used for offline data analysis. Apache Spark integrates Hadoop and Hive well, but cannot achieve real-time data storage.

[0004] The scheme based on Impala and Kudu has obvious defects. As the amount of data increases, Kudu takes a long time to recover after restarting, 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 the Clickhouse Jdbc connection mode of Spark to synchronize data from the offline Hive table to Clickhouse. The former involves direct exchange of files between machines, which is tedious and difficult to roll back. The latter is a simple data synchronization method, but if the parallel parameters of Spark are used for data synchronization, the synchronization speed is fast, but the synchronized Clickhouse data will be duplicated. If single-threaded synchronization is used, the synchronization data will take time.

[0006] ClickHouse is a columnar database management system (DBMS) for online analytical processing (OLAP) that is widely used in today's big data analysis field, and has strong advantages in query speed, data timeliness, and maintenance cost. Based on ClickHouse, a set of data model design, real-time and offline data warehousing, data deduplication query, and complete Clickhouse monitoring are necessary for cost reduction and efficiency improvement of the company. Existing patents describe many methods for ClickHouse to process traffic data based on the advantages of ClickHouse:

[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. 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 retention of users within a specified time period, and attribution analysis is used to calculate the number of clicks, effective users, and conversion values of events 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 the appearance proportion of each attribute or label can be obtained. Interval analysis is used to calculate the conversion time and interval of existing information of users, thereby deeply mining the meaning and rules behind user behavior data, and providing a more comprehensive analysis means for real-time user behavior management. The patent details the use of each analysis model in Clickhouse, but does not introduce Clickhouse data warehousing, monitoring, and other aspects.

[0008] Patent CN116010451A - A business data query method, system and medium based on Clickhouse database, the method comprises: according to the user sent business processing request, determine the request type corresponding to the business processing request; in the case of request type is data change type, according to the business processing request, obtain the required original data from the preset original database; process the original data to obtain the target data after processing, and reorganize the business data processing logic corresponding to the business processing request to obtain the reorganized target business data processing logic; according to the target business data processing logic, write the target data into the Clickhouse database; receive the user sent business data query request, and combine 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 Clickhouse as a business backend query library, the front end sends the request to the back end, and then returns the result of Clickhouse query, instead of traditional relational database Oracle, Mysql as the backend query library.

[0009] Patent CN116089535A - Data synchronization method, device, equipment and storage medium, provides a kind of data synchronization method, device, equipment and storage medium.This method is based on request scene and identifies the field to be synchronized;From the Hive data warehouse, obtain the field information of the field to be synchronized;Based on the field information, create local data table in the ClickHouse database;Based on the location information of the field to be synchronized in the Hive data warehouse, create Hive engine table in the ClickHouse database;Based on traversal to the Hive engine table, generate query statement;Based on the query statement, query the Hive data warehouse, obtain query data, and write the query data into the local data table, can efficiently and accurately realize data synchronization.But for large batch data volume, the Clickhouse table query performance of Hive engine is very poor.

[0010] In view of the above existing technologies, a more efficient and applicable online query analysis model construction method is needed to replace Oracle from data model to data warehousing, data analysis and Clickhouse monitoring. SUMMARY

[0011] The technical problem to be solved by the present application is to overcome the shortcomings of the prior art, and there are few existing technologies that can safely, quickly and completely import offline Hive table data into Clickhouse, so that the application scenario of Clickhouse in practice is not as good as Oracle. The present application provides an online query analysis model construction method based on Clickhouse instead of Oracle, which is based on Clickhouse and describes the entire process from data model to data warehousing, data analysis and Clickhouse monitoring. The method focuses on importing data from offline Hive table or HDFS file into Clickhouse under the condition of safety, rapidity and guarantee of data integrity. The method has advantages in read-write performance of large data volume and great advantages in reducing maintenance cost.

[0012] To solve the above technical problems, the present application provides a Clickhouse-based de-O method, characterized in that it comprises the following steps:

[0013] Step 1: Based on business requirements and Clickhouse ReplicatedReplacingMergeTree table engine, design Clickhouse business library table;

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

[0015] Step 3: Synchronize Hive table offline data to Clickhouse, create corresponding external table in Clickhouse for Hive table and HDFS file that need to be synchronized, synchronize Clickhouse external table data to Clickhouse using insert into statement, modify Clickhouse configuration file according to Clickhouse specification document when using Clickhouse Hive and HDFS engine table, create

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

[0017] / etc / hadoop / conf / under, create Hive / HDFS engine table with ENGINE = HIVE / HDFS; real-time data is stored by connecting Step 2 Topic through Clickhouse Kafka table engine;

[0018] Step 4: based on Prometheus and Grafana, Clickhouse is monitored and alarmed, and the monitoring includes Clickhouse cluster machines and daily query monitoring.

[0019] In step 1, in the scheme 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 to form a table at the bottom layer, and the materialized view is used for upper layer query.

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

[0021] In step 4, the operation and maintenance monitoring further includes: modifying the configuration files of Clickhouse and Prometheus to enable Prometheus to obtain Clickhouse indicators, and the alarm includes big board display and indicator early warning through Grafana.

[0022] The beneficial effects achieved by the application are: the whole process construction method from data model to data warehousing, data analysis and Clickhouse monitoring, so as to replace the application of traditional relational database on online query, based on ClickHouse, offline and real-time data can be quickly stored, based on materialized view, index calculation and query are realized, and finally Clickhouse is monitored and operated through Prometheus+Grafana. The purpose of reducing cost and increasing efficiency is achieved. BRIEF DESCRIPTION OF DRAWINGS

[0023] Fig. 1 is a method step flow chart of an exemplary embodiment of the application.

[0024] Figure 2 is a flow chart of the Clickhouse de-Oracle method in the exemplary embodiment of the present application.

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

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

[0027] When designing a Clickhouse alternative to Oracle scheme, Hive offline data synchronization to Clickhouse is a difficulty. When Hive offline data is synchronized to Clickhouse, there is a way to directly import Hive table underlying data file to Clickhouse. This scheme involves direct exchange of files between machines, and even cross-cluster file exchange between machines, which is cumbersome and difficult to roll back. Alternatively, a Spark+JDBC method can be used to synchronize data in parallel, which is faster. However, because Spark imports data in parallel, if a parallel sub-task fails, restarting another sub-task will result in duplicate data in Clickhouse because the previous sub-task has already imported some data into Clickhouse. If it is single-threaded synchronization, the synchronization data will take time. Clickhouse has a special Hive table engine and HDFS engine in the external table, which corresponds to Hive partitioned table and non-partitioned table respectively. Using the Clickhouse external table method to synchronize Hive offline data, combined with the characteristics of Clickhouse columnar storage, can perfectly solve the above problems.

[0028] The Clickhouse-based de-Oracle method step flow in the exemplary embodiment of the present application as shown in Figure 1 includes:

[0029] Step 1: Based on business requirements and Clickhouse ReplicatedReplacingMergeTree table engine, design Clickhouse business library table; in the design of Clickhouse instead of Oracle scheme, when designing data model, for offline data, based on Clickhouse ReplicatedReplacingMergeTree table engine, combined with the characteristics of Clickhouse column storage, the data details and each dimension are combined to form a table at the bottom layer, and the upper layer query uses materialized view; for real-time data, it is also necessary to create Kafka engine table and create materialized view to persist Kafka engine table data to Clickhouse business library table. ReplicatedReplacingMergeTree table engine stores data more directly than Oracle database, which can automatically backup and joint primary key data deduplication for imported data, improving data security and accuracy.

[0030] Step 2: Offline data is calculated by SparkSql and stored in Hive table; real-time data is calculated by Flink in real time, and the real-time calculation result is sent to Topic; for offline data and real-time data, Spark and Flink technologies are commonly used for data index development, and the batch processing speed of Spark and the stream processing performance of Flink are higher.

[0031] Step 3: Hive table offline data synchronization to Clickhouse, the Hive table and HDFS file to be synchronized are needed, the corresponding external table is established in Clickhouse, the data of the Clickhouse external table is synchronized to Clickhouse using the insert into statement in Clickhouse; real-time data is stored by connecting the Topic of step 2 through the Kafka table engine of Clickhouse; for offline data, there are special Hive table engines and HDFS engines in the external table of Clickhouse, which correspond to the partitioned table and non-partitioned table of Hive respectively. After establishing the external table of Hive and HDFS engine in Clickhouse, the connection between Clickhouse and Hive table in Clickhouse cluster is established, which enables the Hive table data to be read in Clickhouse cluster by directly querying the Hive external table of Clickhouse, and finally the data read from the external table in Clickhouse cluster is inserted into the target Clickhouse business database table in step 1, completing the data synchronization. All the above operations are completed in the Clickhouse cluster, and can be completed directly using SQL, without the need to connect the Hadoop cluster first in development, read the data using Hive JDBC, and then connect the Clickhouse cluster / Oracle database to write the read data to the Clickhouse / Oracle table using Clickhouse / Oracle JDBC. Compared with the general scheme of importing Hive data into Clickhouse and Oracle, the operation is simpler and the synchronization speed is faster; for real-time data, there is a special Kafka engine in Clickhouse to connect the Topic, after the real-time data developed by Flink is accessed to the Topic, the Kafka engine table of Clickhouse is established, and the Clickhouse materialized view is established, the Kafka engine table data is persisted to the Clickhouse business database table. Oracle database is not suitable for real-time data writing.

[0032] Step 4: Based on Prometheus and Grafana, the Clickhouse is monitored and alarmed, including the Clickhouse cluster machine and daily query monitoring, and also including: by modifying the configuration file of Clickhouse and Prometheus, so that Prometheus obtains Clickhouse indicators, and the alarm includes big board display and indicator early warning through Grafana. The Clickhouse cluster machine and daily query monitoring system is relatively mature. Especially for daily query monitoring, it is more perfect than Oracle.

[0033] One Clickhouse-based de-O method flow in the exemplary embodiment of the application as shown in FIG. 2, more specific steps include:

[0034] S11: Based on business requirements, for offline data, create a Clickhouse ReplicatedReplacingMergeTree engine table, and the table creation statement is as follows:

[0035] For real-time data table creation, first create a Clickhouse Topic engine table, such as:

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

[0037] CREATE MATERIALIZED VIEW test.view_test_table ON CLUSTER 'ck_replica1'

[0038] TO test.test_table AS

[0039] SELECT*

[0040] FROM test.kafka_test_table;.

[0041] S12: Process offline data, first use Sparksql for data development, and after development is completed, store the data in the Hive table. Real-time data development uses Flink technology for real-time calculation, and sends the calculation result to Topic.

[0042] S13: When using Hive and HDFS engine table of Cilckhouse, the configuration needs to be modified according to the instruction document of Clickhouse.

[0043] (1) Create / etc / clickhouse-server / config.d / hdfs.xml file and assign authority: chown clickhouse:clickhouse hdfs.xml. The file content is as follows:

[0044] (2) Place hdfs-site.xml file under / etc / hadoop / conf / and assign authority: chown clickhouse:clickhouse

[0045] hdfs-site.xml.

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

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

[0048] Then, according to the judgment of Hive table whether it is a partition table required by the instruction document of Clickhouse, create Hive engine table and HDFS engine table, and the creation statement sample is as follows:

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

[0050] The execution statement sample is as follows:

[0051] INSERT INTO TABLE DEFAULT.CK_TEST SELECT*FROM DEFAULT.CK_TEST_HIVE S14: the monitoring of Clickhouse uses Prometheus+Grafana. Edit the config.xml file of Clickhouse, release the annotation about Prometheus in it, then change the prometheus.yml file, add the ip of the Clickhouse machine to be monitored to the prometheus.yml file, restart prometheus, and view the page to find the monitored indicators. In addition, select the Prometheus data source in Grafana, and configure the Clickhouse related indicators to monitor the big board. For special indicators, alarms can also be configured in Grafana, which is very convenient.

[0052] The Clickhouse-based Oracle system in the exemplary embodiment of the application shown in FIG. 3 comprises:

[0053] For offline data, the index calculation of offline data is processed in the SpqrkSql module, then the data is stored in the Hive data source module, and then the data is imported into the business library table in the Clickhouse module by using the Hive / HDFS engine table of Clickhouse; for real-time data, the index calculation of real-time data is processed in the Flink module, then the data is sent to the Topic module in real time, and then the real-time data is persisted to the business library table in the Clickhouse module by using the Kafka engine table and the materialized view of Clickhouse. The front-end module transmits the query Sql to the back-end module, the back-end module calls the business library table from the Clickhouse module, and transmits the query data to the front-end for display. The Clickhouse machine and the query index data are monitored by Prometheus in the Prometheus module, and the indicators are transmitted to the Grafana module for visualization display by Grafana.

[0054] The application mainly provides a Clickhouse-based de-O method, based on ClickHouse, instead of the function of a traditional relational database in a backend query, from a data model to data warehousing, data analysis and Clickhouse monitoring, forming a set of standard processes. Not only can it quickly and stably provide traffic data for business, but also can timely discover alarms in operation and maintenance, and ensure the accuracy of data. Thus, the business efficiency is improved, and cost reduction and efficiency increase are realized. Meanwhile, when Hive offline data is imported into a Clickhouse table, the Hive and HDFS external table functions of Clickhouse are used, and simple Sql statements are used to quickly and safely import data from a Hive table into Clickhouse. Unlike the traditional Hive / Clickhouse JDBC mode, cross-cluster operation is performed, and a fast and effective Clickhouse offline data import mode is constructed.

[0055] The application also has other various implementation manners, and the above examples do not limit the application in any way. Those skilled in the art can make various corresponding changes and modifications according to the application without departing from the spirit and essence of the application. Any other improvement and application made by equivalent transformation to the above examples shall belong to the protection scope of the claims attached to the application.

Claims

1. A Clickhouse-based method for migrating from Oracle, characterized in that, Comprise the following steps: Step 1: based on business requirements and Clickhouse ReplicatedReplacingMergeTree table engine, design Clickhouse business library table; Step 2: offline data is calculated by SparkSql and stored in Hive table; real-time data is calculated by Flink in real time, and the real-time calculation result is sent to Topic; Step 3: Hive table offline data is synchronized to Clickhouse, and the Hive table and HDFS file that need to be synchronized are established in Clickhouse Corresponding external table, using insert into statement in Clickhouse, synchronize Clickhouse external table data to Clickhouse, when using Cilckhouse's Hive and HDFS engine table, modify Clickhouse configuration file according to Clickhouse specification document, create / etc / clickhouse-server / config.d / hdfs.xml file in Clickhouse cluster, and put hdfs-site.xml file in / etc / hadoop / conf under Hadoop cluster, create Hive / HDFS engine table ENGINE = HIVE / HDFS; Real-time data is stored by connecting the Topic of step 2 through Clickhouse Kafka table engine; Step 4: based on Prometheus and Grafana, Clickhouse is monitored and alarmed, and the operation and maintenance monitoring includes Clickhouse cluster machine and daily query monitoring.

2. The Clickhouse-based method for de-oracoling according to claim 1, wherein: In the design of Clickhous business library table, when designing the data model, based on Clickhouse ReplicatedReplacingMergeTree table engine, the data details and each dimension are combined to form a table in the bottom layer, and the upper layer query uses materialized view.

3. The Clickhouse-based method for de-oracoling according to claim 2, wherein: In step 3, for offline data, after establishing the external table of the Hive and HDFS engine in Clickhouse, the connection between the Clickhouse and Hive tables is established, so that the Hive table data can be read in the Clickhouse cluster by directly querying the Hive external table of Clickhouse, and finally the data read from the external table in the Clickhouse cluster is inserted into the target Clickhouse business database table in step 1, completing the synchronization of the data, for real-time data, there is a special Kafka engine in Clickhouse to connect Topic, after the real-time data developed by Flink is accessed to Topic, the Kafka engine table of Clickhouse is established, and the Clickhouse materialized view is established, and the data of the Kafka engine table is persisted to the Clickhouse business database table.

4. The Clickhouse-based method for de-oracoling according to claim 3, wherein: In step 4, the operation and maintenance monitoring further comprises: modifying the configuration files of Clickhouse and Prometheus, so that Prometheus obtains Clickhouse indicators, and the alarm comprises a big board display and an indicator early warning through Grafana.

Images