Real-time business data stream analysis processing method and system based on clickhouse

Abstract

The application relates to a clickhouse-based real-time business data stream analysis processing method and system. The method comprises the following steps: performing data analysis and filtering on real-time business data streams in each real-time data source to obtain structured data to be analyzed, performing data accuracy verification and data attribute verification on the structured data, obtaining structured real-time data that passes the verification, and importing the structured real-time data into a library table of a self-constructed clickhouse cluster. The structured real-time data in the clickhouse cluster is analyzed and processed by using a target processing engine matched with the structured real-time data, corresponding data analysis results are generated, and each data analysis result is stored in a target cache corresponding to the clickhouse cluster according to a data application scene. The method guarantees the consistency and accuracy of the structured real-time data, realizes the separation of data analysis processing and data analysis results, reduces the development cost in the processing process, and improves the accuracy of the data analysis results.

Description

Technical Field

[0001] This application relates to the field of big data processing technology, and in particular to a method and system for real-time business data stream analysis and processing based on ClickHouse. Background Technology

[0002] With the development of big data processing technology and the widespread application of various applications, platforms, or businesses, massive amounts of real-time business data have emerged. In order to effectively utilize the various business data, such as updating and improving the corresponding applications or businesses based on the business data, it is necessary to process and store the massive amounts of real-time business data in the database so that it can be used for data analysis or data query by the users or platform owners in the future.

[0003] Traditionally, when storing massive amounts of real-time business data, a large number of message queues are usually required to complete the storage operation. After storage, when providing data analysis or query functions to other users, data analysis is required on the massive amount of data, which requires a large amount of memory resources, has high development costs, and slow query and analysis speed. Furthermore, due to the existence of erroneous source data or data deviations in different storage stages, inaccurate data analysis results are prone to occur. Summary of the Invention

[0004] Therefore, it is necessary to provide a real-time business data stream analysis and processing method, system, computer equipment, computer-readable storage medium, and computer program product based on ClickHouse that can reduce the development cost in the process of massive data analysis and processing while ensuring the accuracy of data analysis results.

[0005] Firstly, this application provides a real-time business data stream analysis and processing method based on ClickHouse. The method includes:

[0006] The real-time business data streams from each real-time data source are obtained, and the data streams are parsed and filtered to obtain the structured data to be analyzed.

[0007] The structured data is subjected to data accuracy verification and data attribute verification to obtain the verified structured real-time data, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster.

[0008] Using a target processing engine that matches the structured real-time data, data analysis and processing are performed on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results; the target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0009] The data analysis results are stored in the target caches corresponding to the ClickHouse cluster according to the data application scenarios; the target caches are matched with the data application scenarios.

[0010] In one embodiment, before performing data analysis processing on the structured real-time data in the ClickHouse cluster using a target processing engine that matches the structured real-time data to generate corresponding data analysis results, the method further includes:

[0011] Receive data query requests and obtain the object permissions and data application scenarios carried in the data query requests;

[0012] Based on the object permissions and the data application scenario, a corresponding data query interface is matched; the data query interface is used to access the data analysis results corresponding to the data query request.

[0013] The target processing engine that matches the structured real-time data is invoked to execute a distributed timed task associated with the data application scenario.

[0014] In one embodiment, the step of performing data analysis processing on the structured real-time data in the ClickHouse cluster based on the target processing engine matching the structured real-time data, and generating corresponding data analysis results, includes:

[0015] Based on the target processing engine that matches the structured real-time data, execute the target script file corresponding to the distributed scheduled task;

[0016] By executing the target script file, the structured real-time data in the ClickHouse cluster is processed to perform data analysis, and the corresponding data analysis results are obtained.

[0017] In one embodiment, after storing each of the data analysis results into the respective target caches corresponding to the ClickHouse cluster according to the data application scenario, the method further includes:

[0018] Based on the data query interface, access the target cache corresponding to the data application scenario;

[0019] Obtain the data analysis results stored in the target cache and feed them back to the target object corresponding to the data query request.

[0020] In one embodiment, importing the structured real-time data into a database table of a self-built ClickHouse cluster includes:

[0021] Based on the data application scenarios of the structured real-time data, a target processing engine matching the structured real-time data is determined from the table engines corresponding to the ClickHouse cluster.

[0022] Based on the target processing engine and the business data characteristics corresponding to the structured real-time data, a database table corresponding to the structured real-time data is established in the ClickHouse cluster, and the structured real-time data is stored in the database table; wherein, when establishing the database table corresponding to the structured real-time data, the primary key, partition, and data retention period of the database table are determined based on the structured real-time data.

[0023] In one embodiment, the structured data undergoes data accuracy verification and data attribute verification to obtain verified structured real-time data, including:

[0024] The data attributes of each tuple in the structured data to be analyzed are obtained, and based on each data attribute, a set of different data attributes corresponding to each tuple in the structured data is obtained; the data attributes of each set of data attributes are independent of each other or related.

[0025] The data accuracy of each tuple is verified sequentially, and the first structured data that passes the data accuracy verification is obtained.

[0026] Based on the data application scenario, the target attributes of the first structured data are determined, and based on the target attributes of the first structured data, a probability distribution verification of the data attributes is performed to obtain structured real-time data that passes the probability distribution verification.

[0027] In one embodiment, the step of sequentially performing data accuracy verification on the data attribute sets corresponding to each tuple to obtain first structured data that passes the data accuracy verification includes:

[0028] Perform a Cartesian product on the set of data attributes corresponding to each tuple to obtain the set of possible attributes.

[0029] Obtain the expected attribute set and the actual attribute set corresponding to the data attribute set;

[0030] Based on the set of possible attributes, the set of expected attributes, and the set of actual attributes, data accuracy is verified to obtain the first structured data that passes the data accuracy verification.

[0031] In one embodiment, the step of determining the target attribute of the first structured data according to the data application scenario, and performing a probability distribution verification of the data attributes based on the target attribute of the first structured data to obtain structured real-time data that passes the probability distribution verification includes:

[0032] Based on the data application scenario, determine the target attributes of the first structured data;

[0033] Based on the data application scenario and the data scale of the real-time business data stream, the first structured data is sampled, and a preset number of rows of data are extracted.

[0034] Obtain the attribute category corresponding to the target attribute, and obtain the actual number of observations corresponding to each attribute category;

[0035] Based on the preset probability value of the preset row data falling into the corresponding attribute category, and the preset number of data extracted, the theoretical expected number of times corresponding to each attribute category is determined.

[0036] The corresponding chi-square value is determined based on the theoretical expected number of times and the actual number of observations for each attribute category.

[0037] Using pre-set confidence data, the probability distribution of data attributes is verified on the chi-square value to obtain structured real-time data that passes the probability distribution verification.

[0038] In one embodiment, the data application scenario includes a low-frequency query scenario; after the data analysis and processing of the structured real-time data in the ClickHouse cluster is performed according to the target processing engine matching the structured real-time data to generate corresponding data analysis results, the method further includes:

[0039] The data analysis results are obtained in real time based on the data query interface.

[0040] In one embodiment, the method further includes:

[0041] When there are multiple data query requests, the threshold number of target script files to be executed concurrently is determined based on the processing performance of the ClickHouse cluster.

[0042] Secondly, this application also provides a real-time business data stream analysis and processing system based on ClickHouse. The system includes:

[0043] The structured data generation module is used to acquire real-time business data streams from various real-time data sources, and to perform data parsing and filtering on each of the real-time business data streams to obtain structured data to be analyzed.

[0044] The data verification module is used to verify the accuracy and attributes of the structured data, obtain the verified structured real-time data, and import the structured real-time data into the database tables of the self-built ClickHouse cluster.

[0045] The data analysis result generation module is used to perform data analysis and processing on the structured real-time data in the ClickHouse cluster using a target processing engine that matches the structured real-time data, and generate corresponding data analysis results; the target processing engine is determined according to the data application scenario corresponding to the structured real-time data;

[0046] The data analysis result storage module is used to store each of the data analysis results into a target cache corresponding to the ClickHouse cluster, according to the data application scenario; the target cache is matched with the data application scenario.

[0047] Thirdly, this application also provides a computer device. The computer device includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to perform the following steps:

[0048] The real-time business data streams from each real-time data source are obtained, and the data streams are parsed and filtered to obtain the structured data to be analyzed.

[0049] The structured data is subjected to data accuracy verification and data attribute verification to obtain the verified structured real-time data, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster.

[0050] Using a target processing engine that matches the structured real-time data, data analysis and processing are performed on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results; the target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0051] The data analysis results are stored in the target caches corresponding to the ClickHouse cluster according to the data application scenarios; the target caches are matched with the data application scenarios.

[0052] Fourthly, this application also provides a computer-readable storage medium. The computer-readable storage medium stores a computer program thereon, which, when executed by a processor, performs the following steps:

[0053] The real-time business data streams from each real-time data source are obtained, and the data streams are parsed and filtered to obtain the structured data to be analyzed.

[0054] The structured data is subjected to data accuracy verification and data attribute verification to obtain the verified structured real-time data, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster.

[0055] Using a target processing engine that matches the structured real-time data, data analysis and processing are performed on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results; the target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0056] The data analysis results are stored in the target caches corresponding to the ClickHouse cluster according to the data application scenarios; the target caches are matched with the data application scenarios.

[0057] Fifthly, this application also provides a computer program product. The computer program product includes a computer program that, when executed by a processor, performs the following steps:

[0058] The real-time business data streams from each real-time data source are obtained, and the data streams are parsed and filtered to obtain the structured data to be analyzed.

[0059] The structured data is subjected to data accuracy verification and data attribute verification to obtain the verified structured real-time data, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster.

[0060] Using a target processing engine that matches the structured real-time data, data analysis and processing are performed on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results; the target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0061] The data analysis results are stored in the target caches corresponding to the ClickHouse cluster according to the data application scenarios; the target caches are matched with the data application scenarios.

[0062] The aforementioned real-time business data stream analysis and processing method, system, computer equipment, storage media, and computer program products based on ClickHouse involve acquiring real-time business data streams from various real-time data sources, parsing and filtering these streams to obtain structured data for analysis. Further, the structured data undergoes accuracy and attribute verification to obtain structured real-time data that passes attribute verification. This structured real-time data is then imported into a self-built ClickHouse cluster's database to ensure consistency and accuracy, thereby improving the accuracy of subsequent data analysis results. Finally, a target processing engine matched to the structured real-time data is used to analyze and process the data in the ClickHouse cluster, generating corresponding analysis results. These results are then stored in target caches corresponding to the ClickHouse cluster, according to their respective data application scenarios. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data, and the target caches are matched to these scenarios. This achieves storage-computation separation of data analysis and processing with data analysis results, thereby avoiding memory consumption on the ClickHouse cluster, reducing development costs in the process of processing and analyzing massive amounts of data, and improving the accuracy of the obtained data analysis results. Attached Figure Description

[0063] Figure 1 This is an application environment diagram of a real-time business data stream analysis and processing method based on ClickHouse in one embodiment;

[0064] Figure 2 This is a flowchart illustrating a real-time business data stream analysis and processing method based on ClickHouse in one embodiment.

[0065] Figure 3 This is a schematic diagram of the process for obtaining structured real-time data in one embodiment;

[0066] Figure 4 This is a flowchart illustrating a real-time business data stream analysis and processing method based on ClickHouse in another embodiment.

[0067] Figure 5 This is a schematic diagram of the query process for data analysis results in one embodiment;

[0068] Figure 6 This is a schematic diagram illustrating the real-time query results of advertising metrics in one embodiment;

[0069] Figure 7 This is a schematic diagram illustrating user funnel analysis based on ad reach in one embodiment;

[0070] Figure 8 This is a schematic diagram illustrating user activity observation data of the advertising platform in one embodiment;

[0071] Figure 9 This is a schematic diagram illustrating the process of obtaining structured real-time data that has passed data attribute verification in one embodiment;

[0072] Figure 10 This is a flowchart illustrating the real-time business data stream analysis and processing method based on ClickHouse in yet another embodiment;

[0073] Figure 11 This is a block diagram of a real-time business data stream analysis and processing system based on ClickHouse, as shown in one embodiment.

[0074] Figure 12 This is a block diagram of a real-time business data stream analysis and processing system based on ClickHouse, as described in another embodiment.

[0075] Figure 13 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0076] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0077] The real-time business data stream analysis and processing method based on ClickHouse provided in this application embodiment can be applied to, for example... Figure 1The application environment is shown. Terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or located in the cloud or on other network servers. Server 104 obtains real-time business data streams from various real-time data sources and performs data parsing and filtering on these streams to obtain structured data to be analyzed. The real-time business data streams from each real-time data source can be stored in the data storage system or in the local storage of different types of terminals 102. Server 104 performs data accuracy verification and data attribute verification on the structured data to obtain structured real-time data that passes the data attribute verification, and then imports the structured real-time data into the database tables of its self-built ClickHouse cluster. The ClickHouse cluster can be deployed on server 104. Furthermore, server 104 utilizes a target processing engine that matches the structured real-time data to perform data analysis on the structured real-time data in the ClickHouse cluster, generating corresponding data analysis results. These results are then stored in their respective target caches corresponding to the ClickHouse cluster, according to the data application scenario. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data, and the target cache matches the data application scenario. The target cache can be of different types, such as MySQL (relational database), Redis (non-relational database), and LocalCache (local cache). Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, smart voice interaction devices, IoT devices, and portable wearable devices. IoT devices can include smart home appliances (such as smart TVs, smart air conditioners, and smart refrigerators), smart speakers, and smart in-vehicle devices. Portable wearable devices can include smartwatches, smart bracelets, and head-mounted devices. Server 104 can be implemented using a standalone server or a server cluster composed of multiple servers.

[0078] This invention can be applied to various scenarios, including but not limited to cloud technology, artificial intelligence, big data processing, intelligent transportation, and assisted driving. For example, taking an advertising information push application scenario within a big data processing context, a large amount of business data is generated in real time during the push and delivery of advertising information. However, due to continuous changes in advertising delivery business or defects in business management logic, the business data may become distorted, erroneous, or inconsistent. Therefore, by parsing, filtering, verifying data accuracy, and performing probability distribution verification based on data attributes on the real-time business data stream generated during delivery and push, the accuracy and high consistency of the real-time data obtained after verification are ensured. This solves the problem of reduced accuracy of data analysis results due to erroneous data, thereby ensuring the accuracy of the data analysis results provided when subsequent data analysis and data analysis result queries are needed.

[0079] Furthermore, the types of data that different stakeholders in the process of pushing and delivering advertising information, such as advertisers and advertising platforms, focus on differ, and the types of data available for access and query also vary. For example, advertisers can query data analysis results including: the target delivery volume of the advertisement, the reach rate, exposure rate, click-through rate, and purchase rate of the promotional products on the platform. They can also query the actual conversion rate of each layer obtained through user funnel analysis, such as the actual conversion rate between the target delivery volume and the reach, and the actual conversion rate between the reach and the exposure. Based on the data analysis results obtained, they can further evaluate and predict potential problems or failures in the advertising delivery process, such as assessing the quality of the advertising creative itself, predicting whether there are interception errors on the platform, accurately locating problems in the advertising delivery process, and making timely adjustments and improvements to achieve the goal of effectively delivering advertising information.

[0080] In one embodiment, such as Figure 2 As shown, a real-time business data stream analysis and processing method based on ClickHouse is provided, which is then applied to... Figure 1 Taking the server in the example, the following steps are included:

[0081] Step S202: Obtain the real-time business data streams from each real-time data source, and perform data parsing and filtering on each real-time business data stream to obtain the structured data to be analyzed.

[0082] In this context, a real-time data source refers to the data source of business data streams that are generated and reported in real time. The specific real-time data source varies depending on the object requiring data parsing and filtering operations, such as advertisers or advertising platforms. Advertising platforms can be various application platforms, websites, and applications. The purpose and type of application differ, and these can include instant messaging software, audio / video software, shopping software, and games, all of which can serve as advertising platforms.

[0083] For example, real-time data sources can include various types such as Kafka (representing a distributed, partitioned, multi-replica, distributed messaging system coordinated by ZooKeeper (a distributed application coordination service software) for real-time processing of large amounts of data to meet various demand scenarios), RabbitMQ (representing an open-source message broker software that implements an advanced message queue protocol), Pulsar (representing a message middleware with high throughput, low latency, compute-storage separation, multi-tenancy, and geographically distributed replication capabilities), and NiFi (representing a streaming data processing and distribution system).

[0084] Specifically, due to the need to cater to different business scenarios, various real-time data sources continuously generate different types of business data streams. These business data streams can include unstructured data, semi-structured data, and structured data. During the data production process, factors such as business changes, logical defects, lack of standards, and unclear metrics can lead to data distortion, inaccuracy, errors, and inconsistencies. Subsequent analysis based on these problematic business data streams directly affects the accuracy of the analysis results. Therefore, it is necessary to parse and filter the real-time business data streams from each real-time data source. This involves filtering out invalid data, unifying the format of different data types, and extracting structured data to ultimately obtain the structured data to be analyzed. For example, `format.json-schema` (a tool for validating JSON data formats) can be used to parse JSON data into data with fixed tags, where JSON data represents a lightweight data exchange format.

[0085] Structured data, represented and stored using relational databases, is two-dimensional in form. Its characteristics include: data is organized by rows, with each row representing information about one entity, and all rows sharing the same attributes. Semi-structured data does not conform to the data model structure of relational databases or other tables, but it contains relevant tags to separate semantic elements and hierarchically organize records and fields. It is also known as a self-describing structure, such as XML or JSON formats. Unstructured data, on the other hand, represents data without a fixed structure and can include various documents, images, videos, or audio files.

[0086] Furthermore, specifically, it can be based on the Flink engine (representing a distributed processing engine for processing data streams), access different real-time data sources, and utilize the various APIs (Application Programming Interfaces) supported by the Flink engine to perform data parsing and filtering on the real-time business data streams in each real-time data source to obtain the structured data to be analyzed.

[0087] The Flink engine can be deployed in various cluster environments, including but not limited to YARN clusters (Yet Another Resource Negotiator, i.e., resource manager or dependency management tool), Mesos clusters (a general resource management platform for managing computing resources), Kubernetes clusters (a container cluster management platform), and bare metal clusters.

[0088] Step S204: Perform data accuracy verification and data attribute verification on the structured data to obtain structured real-time data that passes the data attribute verification, and import the structured real-time data into the database tables of the self-built ClickHouse cluster.

[0089] Specifically, data accuracy verification can be a Cartesian product test, while data attribute verification can be a chi-square test. The structured real-time data that passes both accuracy and attribute verification will be stored and imported into a self-built ClickHouse cluster's database. Data attribute verification can also take various forms, such as the T-test (Student's t-test, used to test the difference between two means in small samples (e.g., sample size less than 30), the Z-test (used to test the difference between means in large samples (e.g., sample size greater than 30), which uses the theory of standard normal distribution to infer the probability of difference and compare whether the difference between two means is significant), the F-test (joint hypothesis test, also known as the variance ratio test or homogeneity of variance test, used to analyze or test statistical models with more than one parameter to determine whether all or some of the parameters in the model are suitable for estimating the population), stratified sampling, and sampling surveys.

[0090] In this context, ClickHouse refers to a columnar database management system used for online analytical processing (OLAP). A ClickHouse cluster represents a physical cluster or a logical cluster consisting of multiple ClickHouse nodes. In a physical cluster, all ClickHouse nodes are managed by the same ZooKeeper cluster (a distributed application coordination service), and all DDL (Data Definition Language) operations on the data are effective for the entire cluster. A logical cluster represents physical clusters that do not have a fixed physical relationship. For example, three physical clusters that make up a logical cluster might be independent of each other. Data operations on one physical cluster are not apparent to the other two, but for the logical cluster as a whole, changes in data in any one of the physical clusters can be retrieved by the logical cluster itself through queries.

[0091] Specifically, by obtaining the data attributes of each tuple in the structured data, and by following each data attribute, a set of different data attributes corresponding to each tuple in the structured data is obtained. Then, the data accuracy of the set of data attributes corresponding to each tuple is verified in turn to obtain the first structured data that passes the data accuracy verification.

[0092] For example, taking advertising campaigns as an example, the data attributes of advertising campaign data can include ad placement, target volume, reach, impression rate, click-through rate, and purchase rate. For each tuple's set of data attributes in the advertising campaign data, the accuracy of each tuple is checked sequentially to determine if there is any erroneous data. If erroneous data is found, it needs to be reported to the data provider, such as the advertiser or the campaign platform, for correction or deletion, resulting in the first set of structured data that passes the accuracy check. Corrected erroneous data can then be used in the next cycle or batch of accuracy checks. Structured data that has already passed the accuracy check is not re-verified to avoid redundant verification operations and reduce resource consumption.

[0093] Furthermore, based on the data application scenario, the target attributes of the first structured data are determined, and based on the target attributes of the first structured data, a probability distribution verification of the data attributes is performed to obtain structured real-time data that passes the probability distribution verification. Here, the data application scenario refers to the specific application scenario of the data analysis results that the object needs to query when querying data analysis results. This can be a high-concurrency query scenario or a low-frequency query scenario. Specifically, whether it is a high-concurrency query scenario or a low-frequency query scenario can be determined based on performance indicators such as query volume, latency requirements, concurrency, throughput, number of threads, transactions per second, and response time.

[0094] For example, high-concurrency query scenarios can be massive online query services (such as flash sales on shopping platforms, ticket booking services during holidays, and trending news on news platforms), while low-frequency query scenarios can be automated queries by robots, website queries, data visualization scenarios for data dashboards (i.e., business intelligence dashboards, used to visualize data and show enterprises the status of metrics and key business indicators), that is, presenting data in a visual form, such as in charts or maps, and information display scenarios for BI tools (i.e., business intelligence analysis tools, used to transform complex business data into simple and intuitive information and display it).

[0095] In one embodiment, since the query volume, concurrency, and latency requirements are not fixed in different data application scenarios, data application scenarios such as automated robot queries or website queries, when the query volume or concurrency exceeds the query limits of the ClickHouse cluster itself (i.e., when data analysis results cannot be obtained by directly querying the ClickHouse cluster), also need to be classified as high-concurrency query scenarios. Similarly, data dashboard visualization scenarios and BI tool information display scenarios can also be classified as high-concurrency query scenarios based on performance indicators such as query volume or concurrency, and are not limited to low-frequency query scenarios.

[0096] To give a simple example, consider a flash sale event on a shopping platform within a massive online query service. The target attribute of the current data application scenario could be the product's click-through rate or purchase rate. Therefore, the product's click-through rate or purchase rate can be used as the target attribute of the first structured data. Based on the target attribute, data attribute validation is performed to obtain structured real-time data that passes the data attribute validation.

[0097] In one embodiment, such as Figure 3 As shown, a method for obtaining structured real-time data is provided, referring to... Figure 3As can be seen, by accessing various real-time data sources, such as data source 1, data source 2, ..., and data source N, and acquiring the real-time business data streams from each data source, the StreamETL application in the Flink engine is used to clean these real-time business data streams. Specifically, this involves data parsing and filtering to obtain the structured data to be analyzed. Further, the structured data undergoes accuracy and attribute verification to obtain verified structured real-time data. The accuracy and attribute verification can specifically include Cartesian product verification and chi-square verification. Finally, the verified structured real-time data is stored, specifically by importing the structured data into a self-built ClickHouse cluster's database table.

[0098] In one embodiment, importing structured real-time data into an existing ClickHouse cluster includes:

[0099] For data application scenarios based on structured real-time data, the target processing engine that matches the structured real-time data is determined from the table engines corresponding to the ClickHouse cluster.

[0100] Based on the target processing engine and the business data characteristics corresponding to the structured real-time data, a database table corresponding to the structured real-time data is created in the ClickHouse cluster, and the structured real-time data is stored in the database table. When creating the database table corresponding to the structured real-time data, the primary key, partition, and data retention period in the database table are determined based on the structured real-time data.

[0101] Specifically, different application scenarios for structured real-time data require different target processing engines. In other words, different application scenarios necessitate different data volumes and processing requirements. Therefore, it's necessary to determine the appropriate target processing engine in real-time from the table engines corresponding to the ClickHouse cluster, based on the application scenario of the structured real-time data. The target processing engine then analyzes and processes the matching structured real-time data. ClickHouse supports various table engines, which can be categorized according to their purpose, and each category includes different processing engines.

[0102] Furthermore, by creating a database within the ClickHouse cluster and establishing corresponding tables for the structured real-time data based on the target processing engine and the business data characteristics corresponding to the structured real-time data, the structured real-time data is stored in the corresponding tables. The business data characteristics corresponding to the structured real-time data can include different data features such as the business type, actual business content, application platform to which the business belongs, and the scope of business application.

[0103] When establishing the database tables corresponding to the structured real-time data, the primary key, partitions, and data retention period of the database tables are determined based on the structured real-time data. Taking advertising revenue data as an example, the primary key of the advertising revenue data can include advertising identifiers, time windows, user identifiers, and operation tags. For instance, different user operations correspond to different revenue data. When performing revenue data statistics and analysis based on user identifiers, different tags are used to distinguish the specific operations performed by the user, such as viewing, clicking, and downloading.

[0104] Here, partitions represent different storage partitions of structured real-time data in the ClickHouse cluster, and data retention period indicates the time that the corresponding structured real-time data can be stored in the ClickHouse cluster. If the corresponding data retention period is exceeded, the corresponding structured real-time data will be deleted.

[0105] Step S206: Using a target processing engine that matches the structured real-time data, perform data analysis and processing on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0106] The processing engine can be represented as the table engine supported by ClickHouse for different purposes. The table engines can be divided into different categories according to their different purposes, and each category of table engine includes a variety of different processing engines.

[0107] For example, the table engines supported by ClickHouse can include: 1) MergeTree series, which are general-purpose engines used for large-scale data analysis; 2) Log series, which are lightweight engines used for small table analysis; 3) Integration series, which are used for integration with other data storage and processing systems; 4) Special series, which can include a variety of special engines used for specific functions or scenarios.

[0108] The MergeTree series is primarily used for massive data analysis, supporting features such as data partitioning, ordered storage, primary key indexes, sparse indexes, and data TTL. It also supports all relevant SQL (Structured Query Language) syntax from ClickHouse, including statements for different functions such as data querying, data updating, data deletion, data insertion, creating databases, modifying databases, creating new tables, changing database tables, deleting tables, creating indexes, and deleting indexes.

[0109] Specifically, based on the data application scenario corresponding to the structured real-time data, the corresponding target processing engine is determined. In particular, depending on the different data application scenarios, when processing the data, it is necessary to select the target processing engine based on the requirements of whether the data in the corresponding data application scenario has redundancy, duplication, whether it needs to be aggregated in advance, and whether it needs to ensure security. At the same time, from the perspective of whether the data needs high availability, it is also possible to select whether to use replicas, that is, whether to use replicas to achieve data backup.

[0110] Furthermore, the MergeTree series can specifically include the following engines:

[0111] 1) The MergeTree engine is used for supporting data partitioning, ordered storage, primary key indexes, sparse indexes, and data TTL (Time To Live, used to specify the lifecycle of data; when the data expires, it will be automatically deleted). 2) The ReplacingMergeTree engine solves the problem of duplicate primary keys in the MergeTree engine; its main function is deduplication. 3) The CollapsingMergeTree engine implements asynchronous deletion (or folding). 4) The VersionedCollapsingMergeTree engine uses the Version column to correctly delete duplicate rows. 5) The SummingMergeTree engine sums multiple rows with the same primary key and replaces the summed rows with the new single row.

[0112] To give a simple example, taking advertising campaigns as an example, when processing advertising revenue data during the campaign process, the Replicated+ReplacingMergeTree engine is used. The selection of replicas is achieved by asynchronously synchronizing data to other tables through ZooKeeper (a distributed application coordination service software), thus backing up the data. At the same time, the deduplication feature of ReplacingMergeTree is used to solve the problem of duplicate data writing.

[0113] Step S208: Store the data analysis results into the target caches corresponding to the ClickHouse cluster according to the data application scenarios. The target caches are matched with the data application scenarios.

[0114] Specifically, the target cache can be a different type of cache corresponding to the ClickHouse cluster, such as MySQL (relational database), Redis (non-relational database), and LocalCache (local cache). Depending on the data application scenario, such as high-frequency concurrent query scenario, low-frequency query scenario, or data visualization scenario, the data analysis is stored in different target caches corresponding to the ClickHouse cluster.

[0115] Furthermore, for example, for massive online high-concurrency queries, the asynchronously updated data analysis results can be stored in the Redis cache, which can support massive, high-concurrency queries. If some application scenarios only require low-frequency queries, the ClickHouse cluster can be queried directly. When querying the ClickHouse cluster directly, the target processing engine needs to be called to perform data analysis and processing on the structured real-time data in the ClickHouse cluster, obtain the corresponding data analysis results in real time, and provide feedback or display.

[0116] In other words, when using the direct query method to the ClickHouse cluster, each query means that data analysis and processing needs to be performed once, and the corresponding data analysis and processing results are obtained in real time. However, if there are a large number of query requests, it will cause a large consumption of ClickHouse cluster resources and query timeouts. Therefore, when there are a large number of query requests, the data analysis results need to be stored in the target cache corresponding to the ClickHouse cluster first. By accessing the target cache, a large number of queries to the ClickHouse cluster can be avoided, thus improving data query efficiency.

[0117] Specifically, the method of writing data analysis results to the target cache depends on whether historical data needs to be saved; it can be either an overwrite or incremental write. If historical data does not need to be saved, it is directly overwritten; if it needs to be saved, new cache space is required. By adopting a storage-process separation approach for data analysis and data analysis results, the data analysis results are stored in the target cache corresponding to the ClickHouse cluster. Even if the ClickHouse cluster fails, the execution results of historical updates can still be stored in the target cache for querying.

[0118] The aforementioned real-time business data stream analysis and processing method in ClickHouse involves acquiring real-time business data streams from various real-time data sources, parsing and filtering these streams to obtain structured data for analysis. Further, the structured data undergoes accuracy and attribute verification to obtain structured real-time data that passes attribute verification. This structured real-time data is then imported into a self-built ClickHouse cluster's database to ensure consistency and accuracy, thereby improving the accuracy of subsequent data analysis results. Next, based on a target processing engine matching the structured real-time data, data analysis is performed on the structured real-time data in the ClickHouse cluster, generating corresponding data analysis results. These results are then stored in target caches corresponding to the ClickHouse cluster, according to their respective data application scenarios. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data, while the target cache is matched to that scenario. This achieves separation of storage and computation for data analysis processing and results, avoiding excessive memory usage in the ClickHouse cluster, reducing development costs during massive data processing and analysis, and simultaneously improving the accuracy of the obtained data analysis results.

[0119] In one embodiment, such as Figure 4 As shown, a real-time business data stream analysis and processing method based on ClickHouse is provided, referring to... Figure 4 It can be seen that the method includes:

[0120] Step S402: Receive a data query request and obtain the object permissions and data application scenarios carried in the data query request.

[0121] Specifically, it receives data query requests initiated by different objects through different means or channels, and obtains the object permissions carried in the data query requests, as well as the specific data application scenarios of the data to be queried.

[0122] Taking advertising placement as an example, the data query object can be either the advertiser or the placement platform. Accordingly, the object permissions of advertisers and placement platforms are different. For example, the object permissions of advertisers can include query data analysis results such as: the number of ad packages delivered, the target number of ad placements, the ad reach rate, exposure rate, click-through rate, and the purchase rate of promotional products on the placement platform at different stages. On the other hand, the object permissions of placement platforms can include query data analysis results such as: the number of registered users on the platform, the number of active users on the platform, the number of invalid users on the platform, the ad reach rate, the ad exposure rate, click-through rate, and the purchase rate of promotional products.

[0123] Furthermore, depending on the object permissions of the object initiating the data query request, the type of data analysis results required for the query will differ. Simultaneously, the specific data application scenarios for different types of data analysis results also vary. Specifically, the data application scenarios for data analysis results can be differentiated based on performance metrics such as query volume, latency requirements, concurrency, throughput, number of threads, transactions per second, and response time. These can be categorized into different types, such as high-concurrency query scenarios and low-frequency query scenarios, each of which can include different specific data application scenarios.

[0124] For example, depending on the query channel or query method, the corresponding data application scenarios will also be different. These may include massive online queries, such as flash sales on shopping platforms, ticket-grabbing services during holidays, and hot news on news platforms; automated robot queries (such as intelligent analysis robots in different applications, which are used to call related website content in real time to analyze according to user needs and obtain corresponding query results); website queries; data visualization display scenarios for data dashboards; and information display scenarios for BI tools (i.e., business intelligence analysis tools).

[0125] Since the query volume, concurrency, and latency requirements of different data application scenarios are not fixed, when classifying scenarios such as massive online query services, automated robot queries, website queries, data visualization display scenarios of data dashboards, and information display scenarios of BI tools, massive online query services can be classified as high-concurrency query scenarios, while other data application scenarios, including automated robot queries, website queries, data visualization display scenarios of data dashboards, and information display scenarios of BI tools, can be classified as low-frequency query scenarios.

[0126] Similarly, when the query volume or concurrency exceeds the query limit of the ClickHouse cluster itself, i.e., when data analysis results cannot be obtained by directly querying the ClickHouse cluster, scenarios such as automated robot queries, website queries, data visualization display scenarios in data dashboards, and information display scenarios in BI tools can be classified as high-concurrency query scenarios, and are not limited to low-frequency query scenarios.

[0127] Step S404: Match the corresponding data query interface based on object permissions and data application scenario.

[0128] Specifically, the data query interface is used to access the data analysis results corresponding to the data query request. That is, depending on the object permissions of the object that initiates the data query request and the data application scenario of the data analysis results to be queried, different data query interfaces will be matched, and then the data analysis results in different storage locations can be accessed based on the data query interface.

[0129] The storage location can be a different target cache corresponding to the ClickHouse cluster, such as MySQL (relational database), Redis (non-relational database), and LocalCache (local cache), or it can be the ClickHouse cluster itself.

[0130] For example, depending on the data application scenario, such as whether it is a high-concurrency query scenario or a low-frequency query scenario, the storage location of the data analysis results can be determined. That is, if it is a high-concurrency query scenario, the corresponding data analysis results can be stored in the target cache (such as Redis) corresponding to the ClickHouse cluster, so as to realize the separation of storage and computation between the analysis process and the data analysis results, so as to avoid the problem of excessive consumption of resources due to massive high-concurrency queries occupying a large amount of memory of the ClickHouse cluster.

[0131] Similarly, if the queries are infrequent and the ClickHouse cluster itself can meet the query volume and latency requirements for that scenario, the target processing engine can be directly invoked to perform data analysis on the structured real-time data in the ClickHouse cluster, obtaining the corresponding data analysis results in real time for feedback or display. In other words, in low-frequency query scenarios, when using the direct query method on the ClickHouse cluster, each query represents a data analysis process that needs to be performed to obtain the corresponding data analysis results in real time.

[0132] Step S406: Invoke the target processing engine corresponding to the structured real-time data to execute the distributed timed task associated with the data application scenario.

[0133] The distributed scheduled tasks are used to trigger the execution of corresponding SQL (Structured Query Language) scripts. These scripts contain one or more SQL statements representing different commands, such as data querying, updating, deleting, inserting, creating, and modifying databases. Each SQL statement is specifically constructed based on the database tables of the ClickHouse cluster, and its purpose is to perform data statistics and analysis.

[0134] Specifically, by calling the target processing engine corresponding to the structured real-time data matching, a distributed scheduled task is executed, and the SQL script corresponding to the distributed scheduled task is executed through the target processing engine. The execution result of the SQL script, that is, the obtained data analysis result, is stored in the target cache corresponding to the ClickHouse cluster, so as to avoid resource overhead and query timeout issues caused by multiple queries to ClickHouse.

[0135] Furthermore, different object permissions and data application scenarios are matched with different data query interfaces. Then, by executing the distributed scheduled tasks associated with the corresponding data application scenarios, the target processing engine that matches the structured real-time data can be called. In turn, the target processing engine can realize the data analysis and processing of the structured real-time data in the ClickHouse cluster.

[0136] In one embodiment, when there are multiple data query requests, a threshold for the number of target script files to be executed concurrently is determined based on the processing performance of the ClickHouse cluster.

[0137] Specifically, when there are multiple data query requests, the number of SQL scripts (i.e. target script files) executed during concurrent queries is controlled to avoid excessive concurrent execution of SQL scripts in the ClickHouse cluster, which could lead to high processor load and unavailability, and query timeouts.

[0138] Furthermore, based on the processing performance of the ClickHouse cluster, a threshold can be set for the number of SQL scripts that can be executed concurrently in the ClickHouse cluster. For example, the threshold for the number of concurrently executed script files can be set to 100, or it can be 150, 200, or other different values. This can be adjusted and modified according to actual needs and the processing performance of the ClickHouse cluster, and is not limited to a certain value.

[0139] Step S408: Execute the target script file corresponding to the distributed scheduled task according to the target processing engine that matches the structured real-time data.

[0140] Specifically, by calling the target processing engine corresponding to the structured real-time data matching, a distributed scheduled task corresponding to the data application scenario is executed, so that the target script file corresponding to the distributed scheduled task is executed through the target processing engine.

[0141] Specifically, the target script file can be an SQL script. By executing the target script file, the structured real-time data in the ClickHouse cluster is processed to obtain the corresponding data analysis results.

[0142] Step S410: By executing the target script file, the structured real-time data in the ClickHouse cluster is processed to obtain the corresponding data analysis results.

[0143] Specifically, by invoking the target processing engine that matches the structured real-time data, the target script file corresponding to the distributed scheduled task is executed. This could involve executing the corresponding SQL script to perform data analysis on the structured real-time data in the ClickHouse cluster and obtain the corresponding data analysis results. The SQL script can include one or more SQL statements containing SQL commands. Keywords in the ClickHouse cluster's SQL statements are not preserved and are case-sensitive. The ClickHouse cluster supports various data types, including integers, floating-point numbers, characters, dates, enumeration values, and arrays, as well as various commonly used functions and aggregate functions.

[0144] When multiple queries to the ClickHouse cluster are required, query timeouts occur due to processor load and disk I / O limitations, impacting data analysis efficiency. The following optimization methods were used to fine-tune the SQL statements:

[0145] 1) Set up indexes and select appropriate filtering conditions to reduce the size of the data traversed. 2) Set up corresponding storage partitions for different data types and set appropriate TTL (Time To Live) to reduce the size of the queried data. 3) During Join operations, place smaller tables on the right and larger tables on the left to reduce the number of data lookups. 4) Sort data during writes to reduce the impact of unordered data on data merging and compression. 5) Use more efficient functions; for example, when performing estimation statistics, use the less error-prone uniq function instead of the original count function. 6) Create materialized views to aggregate data during insertion. A materialized view represents a database object containing a query result, used as a local copy of remote data or to generate a summary table based on the sum of data tables. The data stored in the materialized view based on the remote table can also be called a snapshot of the remote data. Step S412: Store the data analysis results according to the data application scenario in the target cache corresponding to the ClickHouse cluster.

[0146] Specifically, data application scenarios can include various scenarios such as massive online query services, automated robot queries, website queries, data visualization for data dashboards, and information display for BI tools. Furthermore, performance metrics such as query volume, latency requirements, concurrency, throughput, number of threads, transactions per second, and response time can be used to determine whether each data application scenario is a high-concurrency query scenario or a low-frequency query scenario.

[0147] Furthermore, based on various data application scenarios, the data analysis results are stored in their respective target caches corresponding to the ClickHouse cluster. These target caches can be different types of caches corresponding to the ClickHouse cluster, such as MySQL (relational database), Redis (non-relational database), and LocalCache (local cache). Depending on the data application scenario, such as high-frequency concurrent query scenarios, low-frequency query scenarios, or data visualization scenarios, the data analysis is stored in different target caches corresponding to the ClickHouse cluster. For example, for massive online high-concurrency queries, asynchronously updated data analysis results can be stored in a Redis cache, which can support massive, high-concurrency queries.

[0148] Step S414: Based on the data query interface, access the target cache corresponding to the data application scenario.

[0149] The data query interface is used to access the data analysis results corresponding to the data query request. That is, different data query interfaces will be matched according to the different object permissions of the object that initiates the data query request and the different data application scenarios of the data analysis results to be queried. Then, the data analysis results in different storage locations can be accessed based on the data query interface.

[0150] Specifically, when the data application scenario is a high-concurrency query scenario, the target cache corresponding to the high-concurrency query scenario, such as Redis cache or MySQL cache, can be accessed based on the data query interface.

[0151] Furthermore, data application scenarios also include low-frequency query scenarios. That is, if only infrequent queries are required in certain application scenarios, the ClickHouse cluster can be queried directly. When directly querying the ClickHouse cluster, the target processing engine needs to be invoked to perform data analysis on the structured real-time data in the ClickHouse cluster, obtaining the corresponding data analysis results in real time for feedback or display. In these scenarios, real-time data application scenarios with different access methods or channels can obtain data analysis results by uniformly querying the target cache or directly querying the ClickHouse cluster, and then stably and efficiently using them in different business applications.

[0152] In one embodiment, such as Figure 5 As shown, a query process for data analysis results is provided, refer to... Figure 5As can be seen, firstly, by calling the target processing engine corresponding to the structured real-time data, the distributed scheduled tasks in the timed scheduling system are executed, and the target script files corresponding to the distributed scheduled tasks are executed. By executing the target script files, the structured real-time data in the ClickHouse cluster is processed for data analysis, and the corresponding data analysis results are obtained. Furthermore, the data analysis results are written to the target caches corresponding to the ClickHouse cluster, so that based on a unified query service, corresponding data query interfaces are provided for different data application scenarios. Through the data query interfaces, the target cache corresponding to the data application scenario is accessed, and the corresponding data analysis results are retrieved from the target cache.

[0153] Specifically, the target cache can be a different type of cache corresponding to the ClickHouse cluster, such as MySQL (relational database), Redis (non-relational database), and LocalCache (local cache). The unified query service supports data application scenarios including: massive online query services, robot automatic query, website query, data visualization for data dashboards, and information display for BI tools.

[0154] Furthermore, based on performance metrics such as query volume, latency requirements, concurrency, throughput, number of threads, transactions per second, and response time, the method for obtaining data analysis results in different data application scenarios is determined. For example, massive online query services belong to high-concurrency query scenarios and need to obtain data analysis results from the target cache. Another example is data visualization scenarios used for data dashboards. When the ClickHouse cluster supports the query volume or execution concurrency of the corresponding scenario, the method of directly querying the ClickHouse cluster is adopted to obtain data analysis results.

[0155] The system utilizes Redis, MySQL, and ZooKeeper to build a scheduled task management system. This system ensures the stable execution of the target script files for each distributed scheduled task. Specifically, it locks the scripts at the start of execution and releases the locks after successful data updates to the cache, preventing execution conflicts. For example, each target script file can have an expiration time, such as 10 seconds. A lock is acquired during the script's execution time (10 seconds), and upon expiration, the lock is released, freeing up memory for the next target script file. The execution period of the scheduled tasks can be determined based on the level of data updates, such as 10 seconds, 30 seconds, or 1 minute, allowing for timely data processing by tailoring the execution period to specific needs.

[0156] Step S416: Obtain the data analysis results stored in the target cache and feed them back to the target object corresponding to the data query request.

[0157] Specifically, the data analysis results stored in the target cache are obtained and then fed back to the target object that initiated the data query request. The target object may be an advertiser or an advertising platform.

[0158] In one embodiment, such as Figure 6 This provides a real-time query result illustration of advertising metrics, for reference. Figure 6 It can be seen that advertising metrics may include campaign time, campaign platform (such as different instant messaging platforms, shopping platforms, and games), schedule name, schedule identifier, number of packages delivered, target number of deliveries, campaign method, ad identifier, reach, outer layer impressions, outer layer clicks, inner layer impressions, inner layer clicks, and campaign impressions, etc.

[0159] Specifically, when querying the status of each ad placement, the query criteria can be ad identifiers, ad placements, ad creatives, linked referral activity links, etc. Through different query criteria, the ad placement metrics data in different dimensions can be obtained and displayed in a multi-dimensional aggregated manner, such as reach, exposure rate, click volume, etc.

[0160] For example, depending on the object permissions of the entity initiating the query request, the data types that can be displayed will differ. For instance, advertisers can query data analysis results including: ad delivery volume, target ad delivery volume, ad reach rate, impression rate, click-through rate, and product purchase rate at different stages on the platform. Conversely, platforms can query data analysis results including: the number of registered users, active users, invalid users, ad reach, ad impression rate, click-through rate, and product purchase rate. Figure 6 (Not all data types that can be queried by the platform are displayed in the text).

[0161] In one embodiment, such as Figure 7 As shown, this diagram illustrates a user funnel analysis based on ad reach. Figure 7As shown in Figure (A), user funnel analysis based on ad reach involves calculating the conversion rate for each ad metric at each level. The conversion rate at each level is then used to determine if any issues or errors occurred during the ad delivery process, allowing for timely adjustments. Specifically, by analyzing the conversion rate of each level compared to the previous level, the strengths and weaknesses of the ad itself (e.g., whether the creative style is unpopular) and whether there are platform problems (e.g., whether there were platform blocking errors) are assessed. This allows for timely identification and improvement of issues in the ad delivery process, ensuring effective ad delivery.

[0162] For example, adjustments can include: making changes to ad placement decisions, such as adjusting creative materials, creating new ads, or changing the ad placement platform. Specifically, for ad placement platforms, it's necessary to conduct evaluations and predictions from different perspectives based on the corresponding user funnel analysis. This includes considering whether the platform's own ad placement strategy and ad restriction strategy (i.e., the degree or extent of ad blocking) are reasonable, whether the push or delivery methods are appropriate, whether the platform's user activity is sufficient, and whether the platform needs to be expanded to meet higher placement demands.

[0163] Furthermore, referring to Figure 7 As shown in Figure (B), for user funnel analysis, for example, when placing an ad, the reach rate of the ad on the platform is calculated based on the target number of users and the number of users reached. For example, if the target number of users is to push the ad to 10 million users, but the actual number of users reached is 8 million, then funnel analysis is performed based on the target number of users and the number of users reached to obtain the reach rate of the ad on the platform and the conversion rate of the current layer.

[0164] Similarly, consider the conversion rate between actual user reach and outer exposure. Outer exposure indicates whether users directly see the advertised ad. This means that when an ad is placed on different pages, such as the location of the dynamic sharing page on a social media app, it determines whether the user scrolls to that location. If the user does not scroll to the ad placement location, that is, if they miss the ad placement location, it means that the advertised ad has not achieved outer exposure for that user.

[0165] Furthermore, referring to Figure 7As shown in Figure (B), this can also include, for example, the conversion rate between outer impressions and outer clicks, the conversion rate between outer clicks and inner impressions, the conversion rate between inner impressions and inner clicks, and the conversion rate between inner clicks and campaign impressions. Among these, an outer click indicates that a user directly sees the ad and clicks on it. An inner impression indicates that after clicking the ad, the user enters the ad link and sees the ad content included in the ad link. An inner click indicates that after seeing the ad content in the ad link, the user further clicks on the campaign link within the ad content. A campaign impression indicates that the user clicks the campaign link and chooses to jump to or access the corresponding promotional activity, such as clicking the campaign link to access other application interfaces. Depending on the application platform, for example, for a shopping platform, this could be participating in promotional activities, making purchases, or placing orders; for a gaming platform, it could be participating in game launch events, purchasing items, or enabling new users to download the game.

[0166] Among them, reference Figure 7 As shown in Figure (C), different advertising platforms have different restrictions, such as... Figure 7 The system includes first, second, third, and other restrictions. Based on the restrictions imposed by each platform, it allows for assessment of the platform's ad blocking capabilities, determining if there have been errors in the blocking process, and providing timely feedback or adjustments to the platform. The first restriction may include conditional restrictions, frequency control for already launched games, and issues related to game delisting. The number of users and the actual percentage of successful participation vary depending on the specific restrictions, and the actions that users on the game platform can perform also differ.

[0167] In one embodiment, such as Figure 8 As shown, this provides a schematic diagram of user activity observation data for a campaign platform. Figure 8 As shown in Figure (D), the activity level of each user on the current advertising platform can be observed and recorded. User activity can be reflected through user login counts, user registration counts, etc. For example, taking a game application platform as the advertising platform, users can register or log in by entering the game through the game center or by directly accessing the game application.

[0168] Among them, reference Figure 8As shown in Figure (E), the active users of the game platform can be observed based on different access methods and time periods, and this can be visually displayed using a line graph. For the advertising platform, it can obtain real-time information on the current user activity level, allowing it to push activity information to active users, enhance user-platform interaction, and increase platform revenue. Specifically, when advertising on the game platform, the platform can further locate and investigate problems arising during the advertising process based on different indicators such as user activity, ad reach, exposure rate, and click-through rate. It can also perform data analysis and platform characteristic assessment on the ad revenue data to provide access to the data analysis results later.

[0169] In this embodiment, a data query request is received, and the object permissions and data application scenario carried in the request are obtained. Based on the object permissions and the data application scenario, a corresponding data query interface is matched. The target processing engine corresponding to the structured real-time data is invoked to execute a distributed scheduled task associated with the data application scenario, triggering the execution of the target script file corresponding to the distributed scheduled task. Executing the target script file enables data analysis processing of the structured real-time data in the ClickHouse cluster, generating corresponding data analysis results. Each data analysis result is stored according to its data application scenario in a target cache corresponding to the ClickHouse cluster. The data query interface accesses the target cache corresponding to the data application scenario, retrieves the stored data analysis results, and feeds them back to the target object corresponding to the data query request. This achieves separation of data analysis processing and data analysis results storage during the data query process, avoiding frequent access to the ClickHouse cluster and memory consumption, reducing development costs in massive data processing and analysis, and decreasing the probability of access timeouts.

[0170] In one embodiment, such as Figure 9 As shown, the steps to obtain structured real-time data that has passed data attribute verification, namely, performing data accuracy verification and data attribute verification on the structured data to obtain structured real-time data that has passed data attribute verification, specifically include:

[0171] Step S902: Obtain the data attributes of each tuple in the structured data to be analyzed, and based on each data attribute, obtain different sets of data attributes corresponding to each tuple in the structured data. The data attributes of each set of data attributes are independent or related to each other.

[0172] Specifically, the data attributes of each tuple in the structured data to be analyzed are different. For example, if a tuple has the data attribute "ad placement," where ad placement can include different situations such as red dot prompts, announcement prompts, and interface ad prompts, then when the data attribute is "ad placement," the corresponding tuple has 3 data attributes. This can be understood as a set of 3-tuple attribute columns. For other data attributes, and the possible situations included in each data attribute, the data attribute set of the corresponding tuple is determined.

[0173] Step S904: Perform data accuracy verification on the data attribute set corresponding to each tuple in sequence to obtain the first structured data that passes the data accuracy verification.

[0174] Structured data can include multiple tuples, and the data attributes of each tuple do not overlap. For example, an ad placement can include different locations such as red dot prompts, announcement prompts, and interface ad prompts. For different ad placements, the reach, impressions, clicks, and downloads of each ad placement can all be used as the corresponding ad placement's data attributes.

[0175] Specifically, the Cartesian product is calculated on the data attribute set corresponding to each tuple to obtain the corresponding possible attribute set, and the expected attribute set and actual attribute set corresponding to the data attribute set are obtained. Based on the possible attribute set, expected attribute set and actual attribute set, the data accuracy is verified to obtain the first structured data that passes the data accuracy verification.

[0176] The Cartesian product is used to represent the Cartesian product of sets X and Y, denoted as X × Y. In this calculation, the first object is a member of X, and the second object is one of the members of all possible ordered pairs of Y. For example, if set A = {a, b} and set B = {0, 1, 2}, then the Cartesian product of the two sets is {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}.

[0177] In one embodiment, taking the n-ary attribute column set R and the m-ary attribute column set S of structured data as an example, the Cartesian product between the n-ary attribute column set R and the m-ary attribute column set S is calculated using the following formula (1) to obtain the possible attribute set T:

[0178] T=R×S={r∪s|r∈R,s∈S}; (1)

[0179] In this case, taking different ad placements such as red dot alerts, announcement alerts, and interface ad alerts (i.e., a 3-element attribute set R) and ad placements such as reach, impressions, clicks, and downloads (i.e., a 4-element attribute set S) as an example, when calculating the possible attribute set T, the Cartesian product of set R and set S yields the following possible attribute set T: {(red dot alert, reach), (red dot alert, impressions), (red dot alert, clicks), (red dot alert, downloads), (announcement alert, reach), (announcement alert, impressions), (announcement alert, clicks), (announcement alert, downloads), (interface ad alert, reach), (interface ad alert, impressions), (interface ad alert, clicks), (interface ad alert, downloads)}.

[0180] In one embodiment, the expected attribute set and the actual attribute set corresponding to the data attribute set are obtained, and the data accuracy is verified based on the possible attribute set, the expected attribute set, and the actual attribute set.

[0181] In this context, the expected attribute set M, corresponding to the data attribute set, represents the expected set of attributes that can be obtained. For example, if an ad placement can include different locations such as red dot prompts, announcement prompts, and interface ad prompts, which is a 3-element attribute column set R, and the ad placement's reach, impressions, clicks, and downloads, which is a 4-element attribute column set S, then when querying the ad revenue data during the ad campaign, the expected set of attributes that can be obtained is one where the reach, impressions, clicks, and downloads for each ad placement can be fully known. In the current case, the expected attribute set M should be equal to the possible attribute set T.

[0182] Similarly, for example, consider an ad placement that can include different locations such as red dot alerts, announcement alerts, and interface ad alerts, i.e., a 3-element attribute column set R. The ad placement's reach, impressions, clicks, and downloads, i.e., a 4-element attribute column set S, can be used as an example. The actual attribute set N represents the set of attributes actually present in the queried data. Therefore, during data querying, the actual attribute set N includes: {(red dot alert, reach), (red dot alert, impressions), (red dot alert, clicks), (red dot alert, downloads), (announcement alert, reach), (interface ad alert, downloads)}. Based on the data included in the actual attribute set N, it can be determined that there is currently a data gap, meaning that the reach, impressions, clicks, and downloads for each ad placement cannot be fully obtained.

[0183] Furthermore, when verifying the possible attribute set T, the expected attribute set M, and the actual attribute set N, the data accuracy is verified using the relationship shown in the following formula (2):

[0184]

[0185] Among them, the possible attribute set T, the expected attribute set M, and the actual attribute set N must simultaneously satisfy the relationship shown in formula (2) for the corresponding structured data to pass the data accuracy verification and obtain the first structured data that passes the data accuracy verification. That is to say, if any element in set N does not belong to set M, or if any element in set N does not belong to set T, or if any element in set M does not belong to set T, then the relationship shown in formula (2) is violated, and the real-time data source is considered to have generated erroneous data that cannot pass the data accuracy verification and needs to be corrected or deleted.

[0186] Step S906: Based on the data application scenario, determine the target attributes of the first structured data, and perform probability distribution verification of the data attributes based on the target attributes of the first structured data to obtain structured real-time data that passes the probability distribution verification.

[0187] The target attributes for data attribute validation differ depending on the data application scenario. Therefore, it's necessary to determine the target attributes of the first structured data based on the specific application scenario. For example, in a data visualization scenario like a data dashboard, the target attribute for data attribute validation could be an ad placement. Ad placements could be categorized into red dot alerts and interface ad prompts. In this case, when the target attribute is an ad placement, a probability distribution validation needs to be performed based on the ad placement to obtain structured real-time data that passes the probability distribution validation.

[0188] Specifically, based on the data application scenario, the target attributes of the first structured data are determined. Then, based on the data application scenario and the data scale of the real-time business data stream, the first structured data is sampled, and a preset number of rows of data are extracted. By obtaining the attribute categories corresponding to the target attributes, the actual number of observations for each attribute category, and based on the preset probability value of each row of data falling into its corresponding attribute category, as well as the preset number of extracted data, the theoretical expected number of observations for each attribute category is determined. Based on the theoretical expected number of observations for each attribute category and the actual number of observations, the corresponding chi-square value is determined. Finally, using pre-set reliability data, the chi-square value is used to verify the data attributes, obtaining structured real-time data that passes the probability distribution verification.

[0189] For example, taking an ad placement as the target attribute of the first structured data determined based on the data application scenario, and considering that ad placements include two categories: red dot prompts and interface ad prompts, the first structured data is sampled and a preset number of n rows of data are extracted based on the data application scenario and the data scale of the real-time business data stream. The preset number of n rows of data can be different values, such as 5,000, 10,000, or 20,000 rows, and needs to be set or adjusted according to the data application scenario and the data scale of the real-time business data stream; it is not limited to any specific value.

[0190] Here, k represents the attribute category corresponding to the target attribute. For example, if the ad placement includes two categories: red dot prompts and interface ad prompts, then k is 2. The actual number of observations x corresponds to each attribute category. i Specifically, this can include the actual number of observations x1 corresponding to the red dot prompt and the actual number of observations x2 corresponding to the interface advertisement prompt.

[0191] Furthermore, by obtaining a preset probability value P that the preset row data falls into the corresponding attribute category. i This can be a pre-defined theoretical probability value for different categories, such as a preset probability value P1 for a red dot alert and a preset probability value P2 for an interface ad alert, or a preset probability value P based on preset row data falling into the corresponding attribute category. i The preset probability value P1 for the red dot prompt, the preset probability value P2 for the interface advertisement prompt, and the preset number n for the extracted data are calculated using the following formula (3) to obtain the theoretical expected number m for each attribute category. i :

[0192] m i =nP i The restrictions are:

[0193] The sum of the preset probability value P1 corresponding to the red dot prompt and the preset probability value P2 corresponding to the interface advertisement prompt is 1. The sum of the theoretical expected number of times m1 corresponding to the red dot prompt and the theoretical expected number of times m2 corresponding to the interface advertisement prompt is equal to n. Similarly, the sum of the actual number of times x1 corresponding to the red dot prompt and the actual number of times x2 corresponding to the interface advertisement prompt is equal to n.

[0194] In one embodiment, as the preset number n of extracted data increases, i.e., the data scale increases, the limiting distribution of the corresponding target attribute tends to a chi-square distribution χ. 2 It is expressed using the following formula (4):

[0195]

[0196] In the data attribute validation process for the target attributes, the corresponding chi-square value is calculated based on the theoretical expected frequency and the actual number of observations for each attribute category. Specifically, during the validation process, as the sampled data size n increases, the theoretical expected frequency m... i If it is large enough, then χ 2 The calculated chi-square value follows a chi-square function with k-1 degrees of freedom. 2 Distribution. Assuming there are two categories of attributes, k, for example, ad placements include red dot prompts and interface ad prompts (k = 2), then the smaller the calculated chi-square value, the better. In other words, the smaller the chi-square value, the more accurate and effective the structured data.

[0197] Furthermore, using a chi-square table and corresponding pre-set confidence levels (such as 95% or 90% confidence levels, or other confidence levels, without specific limitations during the validation process), the chi-square values ​​are validated for the probability distribution of data attributes, resulting in structured real-time data that passes the probability distribution validation. Specifically, by validating the probability distribution of data attributes, it can be determined whether the actual number of observations conforms to the theoretical probability distribution, thereby assessing the validity of the generated structured data.

[0198] In one embodiment, during continuous periodic testing, the chi-square distribution of the target attribute over different time periods is continuously calculated to further determine whether the structured data in the business data stream is consistent before and after. And because χ²... 2 If the distribution is additive, then the χ² of multiple key attributes of structured data... 2 Similarly, this can be added, and by simultaneously judging the key attributes of multiple structured data, the accuracy and effectiveness of the overall structured data can be determined.

[0199] In this embodiment, the data attributes of each tuple in the structured data to be analyzed are obtained, and based on each data attribute, a set of different data attributes corresponding to each tuple in the structured data is obtained. By sequentially performing data accuracy verification on the data attribute sets corresponding to each tuple, first structured data that passes the data accuracy verification is obtained. Then, based on the data application scenario, the target attributes of the first structured data are determined. Data attribute verification is then performed based on the target attributes of the first structured data to obtain structured real-time data that passes the data attribute verification. This achieves accuracy verification and data attribute verification of the structured data, ensuring the consistency and accuracy of the structured real-time data subsequently imported into the ClickHouse cluster, and improving the accuracy of the subsequent data analysis results.

[0200] In one embodiment, such as Figure 10 As shown, a real-time business data stream analysis and processing method based on ClickHouse is provided, which specifically includes the following steps:

[0201] Step S1001: Obtain the real-time business data streams from each real-time data source, and perform data parsing and filtering on each real-time business data stream to obtain the structured data to be analyzed.

[0202] Step S1002: Obtain the data attributes of each tuple in the structured data to be analyzed, and obtain a set of different data attributes corresponding to each tuple in the structured data based on each data attribute.

[0203] Step S1003: Perform Cartesian product calculation on the data attribute set corresponding to each tuple to obtain the corresponding possible attribute set, and obtain the expected attribute set and the actual attribute set corresponding to the data attribute set.

[0204] Step S1004: Based on the possible attribute set, the expected attribute set, and the actual attribute set, perform data accuracy verification to obtain the first structured data that passes the data accuracy verification.

[0205] Step S1005: Based on the data application scenario, determine the target attributes of the first structured data, and sample the first structured data based on the data application scenario and the data scale of the real-time business data stream, extracting a preset number of rows of data.

[0206] Step S1006: Obtain the attribute category corresponding to the target attribute, as well as the actual number of observations corresponding to each attribute category. Based on the preset probability value of the preset row data falling into the corresponding attribute category and the preset number of data extracted, determine the theoretical expected number of observations corresponding to each attribute category.

[0207] Step S1007: Based on the theoretical expected number of times and the actual number of observations corresponding to each attribute category, determine the corresponding chi-square value, and use pre-set reliability data to perform probability distribution verification on the chi-square value to obtain structured real-time data that passes the probability distribution verification.

[0208] Step S1008: Based on the data application scenario of structured real-time data, determine the target processing engine that matches the structured real-time data from the table engines corresponding to the ClickHouse cluster.

[0209] Step S1009: Based on the business data characteristics corresponding to the target processing engine and the structured real-time data, establish a database table corresponding to the structured real-time data in the ClickHouse cluster in real time, and store the structured real-time data in the database table.

[0210] Step S1010: Receive a data query request and obtain the object permissions and data application scenarios carried in the data query request.

[0211] Step S1011: Match the corresponding data query interface according to the object permissions and data application scenario. The data query interface is used to access the data analysis results corresponding to the data query request.

[0212] Step S1012: Invoke the target processing engine corresponding to the structured real-time data to execute the distributed timed task associated with the data application scenario.

[0213] Step S1013: Execute the target script file corresponding to the distributed scheduled task according to the target processing engine that matches the structured real-time data.

[0214] Step S1014: By executing the target script file, the structured real-time data in the ClickHouse cluster is processed to obtain the corresponding data analysis results.

[0215] Step S1015: Based on the data query interface, access the target cache corresponding to the data application scenario.

[0216] Step S1016: Obtain the data analysis results stored in the target cache and feed them back to the target object corresponding to the data query request.

[0217] The aforementioned real-time business data stream analysis and processing method in ClickHouse involves acquiring real-time business data streams from various real-time data sources, parsing and filtering these streams to obtain structured data for analysis. Further, the structured data undergoes accuracy and attribute verification to obtain structured real-time data that passes attribute verification. This structured real-time data is then imported into a self-built ClickHouse cluster's database to ensure consistency and accuracy, thereby improving the accuracy of subsequent data analysis results. Next, a target processing engine matched to the structured real-time data is used to perform data analysis on the ClickHouse cluster, generating corresponding data analysis results. These results are then stored in target caches corresponding to the ClickHouse cluster, based on their respective data application scenarios. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data, while the target cache is matched to that scenario. This achieves separation of storage and computation for data analysis processing and results, avoiding excessive memory usage on the ClickHouse cluster, reducing development costs during massive data processing and analysis, and simultaneously improving the accuracy of the obtained data analysis results.

[0218] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0219] Based on the same inventive concept, this application also provides a ClickHouse-based real-time business data stream analysis and processing system for implementing the ClickHouse-based real-time business data stream analysis and processing method described above. The solution provided by this system is similar to the implementation scheme described in the above method. Therefore, the specific limitations of one or more ClickHouse-based real-time business data stream analysis and processing system embodiments provided below can be found in the limitations of the ClickHouse-based real-time business data stream analysis and processing method described above, and will not be repeated here.

[0220] In one embodiment, such as Figure 11 As shown, a real-time business data stream analysis and processing system based on ClickHouse is provided, including: a structured data generation module 1102, a data verification module 1104, a data analysis result generation module 1106, and a data analysis result storage module 1108, wherein:

[0221] The structured data generation module 1102 is used to acquire real-time business data streams from various real-time data sources, and to perform data parsing and filtering on each real-time business data stream to obtain structured data to be analyzed.

[0222] The data verification module 1104 is used to verify the accuracy and attributes of structured data, obtain structured real-time data that passes the data attribute verification, and import the structured real-time data into the database tables of the self-built ClickHouse cluster.

[0223] The data analysis result generation module 1106 is used to perform data analysis and processing on the structured real-time data in the ClickHouse cluster using a target processing engine that matches the structured real-time data, and generate corresponding data analysis results. The target processing engine is determined based on the data application scenario corresponding to the structured real-time data.

[0224] The data analysis result storage module 1108 is used to store each data analysis result into a target cache corresponding to the ClickHouse cluster according to the data application scenario. The target cache is matched with the data application scenario.

[0225] The aforementioned real-time business data stream analysis and processing system based on ClickHouse acquires real-time business data streams from various real-time data sources, parses and filters these streams to obtain structured data for analysis. Further, the structured data undergoes accuracy and attribute verification to obtain structured real-time data that passes attribute verification. This structured real-time data is then imported into the self-built ClickHouse cluster's database tables to ensure consistency and accuracy, thereby improving the accuracy of subsequent data analysis results. Next, a target processing engine matched to the structured real-time data is used to perform data analysis on the structured real-time data in the ClickHouse cluster, generating corresponding data analysis results. These results are then stored in target caches corresponding to the ClickHouse cluster, based on their respective data application scenarios. The target processing engine is determined according to the data application scenario corresponding to the structured real-time data, while the target cache is matched to that scenario. This achieves separation of storage and computation for data analysis processing and results, avoiding excessive memory usage on the ClickHouse cluster, reducing development costs during massive data processing and analysis, and simultaneously improving the accuracy of the obtained data analysis results.

[0226] In one embodiment, a real-time business data stream analysis and processing system based on ClickHouse is provided, the system comprising:

[0227] The data query request receiving module is used to receive data query requests and obtain the object permissions and data application scenarios carried in the data query request.

[0228] The data query interface matching module is used to match the corresponding data query interface based on object permissions and data application scenarios; the data query interface is used to access the data analysis results corresponding to the data query request.

[0229] The distributed scheduled task execution module is used to call the target processing engine corresponding to the structured real-time data and execute distributed scheduled tasks associated with the data application scenario.

[0230] The target script execution module executes the target script file corresponding to the distributed scheduled task, based on the target processing engine that matches the structured real-time data. The data analysis result generation module performs data analysis on the structured real-time data in the ClickHouse cluster by executing the target script file, and obtains the corresponding data analysis results.

[0231] The data analysis results storage module is used to store each data analysis result into the corresponding target cache of the ClickHouse cluster according to the data application scenario.

[0232] The target cache access module is used to access the target cache corresponding to the data application scenario based on the data query interface.

[0233] The data analysis result acquisition module is used to obtain the data analysis results stored in the target cache and feed them back to the target object corresponding to the data query request.

[0234] In one embodiment, a real-time business data stream analysis and processing system based on ClickHouse is provided, the system comprising:

[0235] The target processing engine determination module is used to determine the target processing engine that matches the structured real-time data from the table engines corresponding to the ClickHouse cluster for data application scenarios based on structured real-time data.

[0236] The structured real-time data storage module is used to create database tables corresponding to the structured real-time data in the ClickHouse cluster based on the target processing engine and the business data characteristics corresponding to the structured real-time data, and to store the structured real-time data in the database tables. When creating the database tables corresponding to the structured real-time data, the primary key, partition, and data retention period in the database tables are determined based on the structured real-time data.

[0237] In one embodiment, the data verification module is further configured to:

[0238] The process involves obtaining the data attributes of each tuple in the structured data to be analyzed, and based on each data attribute, obtaining different sets of data attributes corresponding to each tuple in the structured data; the data attributes in each set of data attributes are independent or related to each other; sequentially performing data accuracy verification on the data attribute sets corresponding to each tuple to obtain the first structured data that passes the data accuracy verification; determining the target attributes of the first structured data according to the data application scenario, and performing probability distribution verification on the data attributes based on the target attributes of the first structured data to obtain the structured real-time data that passes the probability distribution verification.

[0239] In one embodiment, the data verification module is further configured to:

[0240] Calculate the Cartesian product of the data attribute set corresponding to each tuple to obtain the corresponding possible attribute set; obtain the expected attribute set and the actual attribute set corresponding to the data attribute set; perform data accuracy verification based on the possible attribute set, expected attribute set and actual attribute set to obtain the first structured data that passes the data accuracy verification.

[0241] In one embodiment, the data verification module is further configured to:

[0242] Based on the data application scenario, the target attributes of the first structured data are determined; based on the data application scenario and the data scale of the real-time business data stream, the first structured data is sampled, and a preset number of rows of data are extracted; the attribute categories corresponding to the target attributes are obtained, and the actual observation counts corresponding to each attribute category are obtained; based on the preset probability value of the preset rows of data falling into the corresponding attribute category, and the preset number of extracted data, the theoretical expected counts corresponding to each attribute category are determined; based on the theoretical expected counts and actual observation counts corresponding to each attribute category, the corresponding chi-square value is determined; using pre-set reliability data, the distribution probability of the data attributes is verified on the chi-square value to obtain structured real-time data that passes the distribution probability verification.

[0243] In one embodiment, the data analysis result generation module is further configured to:

[0244] When there are multiple data query requests, the threshold for the number of target script files to be executed concurrently is determined based on the processing performance of the ClickHouse cluster.

[0245] In one embodiment, the data application scenario includes low-frequency query scenarios; the real-time business data stream analysis and processing system based on ClickHouse also includes:

[0246] The real-time data analysis results acquisition module is used to acquire data analysis results in real time based on the data query interface.

[0247] In one embodiment, such as Figure 12 As shown, a real-time business data stream analysis and processing system based on ClickHouse is provided, referring to... Figure 12 It can be seen that the system includes:

[0248] The data preprocessing module 1202 is used to perform data cleaning and data verification to obtain structured real-time data. Data cleaning includes data parsing and data filtering, while data verification includes data accuracy verification and data attribute verification.

[0249] The data storage module 1204 is used to build a ClickHouse cluster, select a target processing engine that matches the structured real-time data, and create a database table corresponding to the structured real-time data in the ClickHouse cluster in real time according to the target processing engine and the business data characteristics corresponding to the structured real-time data, and store the structured real-time data in the database table.

[0250] The data analysis module 1206 is used to construct SQL statements and optimize each SQL statement to obtain the optimized SQL script.

[0251] The data query module 1208 executes distributed scheduled tasks to perform data analysis on structured real-time data in the ClickHouse cluster, obtain corresponding data analysis results, and store these results in the target cache. It provides real-time query services, offering different data query interfaces based on object permissions and data application scenarios. Different data query interfaces access different target caches to retrieve data analysis results.

[0252] The modules in the aforementioned real-time business data stream analysis and processing system based on ClickHouse can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of the computer device in software form, so that the processor can call and execute the corresponding operations of each module.

[0253] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 13 As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores real-time business data streams, structured data to be analyzed, structured real-time data, target processing engines, data analysis results, data application scenarios, and target cache data. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communication with external terminals via a network connection. When the computer program is executed by the processor, it implements a real-time business data stream analysis and processing method based on ClickHouse.

[0254] Those skilled in the art will understand that Figure 13 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0255] In one embodiment, a computer device is also provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above method embodiments.

[0256] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.

[0257] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0258] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions. Specifically, when advertising is pushed, users on each platform or application can refuse or easily refuse advertising push information.

[0259] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0260] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0261] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for real-time business data stream analysis and processing based on ClickHouse, characterized in that, The method includes: The real-time business data streams from each real-time data source are obtained, and the data streams are parsed and filtered to obtain the structured data to be analyzed. The data attributes of each tuple in the structured data to be analyzed are obtained, and based on each data attribute, a set of different data attributes corresponding to each tuple in the structured data is obtained; the data attributes of each set of data attributes are independent of each other or related. The data accuracy of each tuple is verified sequentially, and the first structured data that passes the data accuracy verification is obtained. Based on the data application scenario, the target attributes of the first structured data are determined, and based on the target attributes of the first structured data, the probability distribution of the data attributes is verified to obtain the structured real-time data that passes the probability distribution verification, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster. Using a target processing engine that matches the structured real-time data, data analysis and processing are performed on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results; the target processing engine is determined based on the data application scenario corresponding to the structured real-time data. The data analysis results are stored in the target caches corresponding to the ClickHouse cluster according to the data application scenarios; the target caches are matched with the data application scenarios.

2. The method according to claim 1, characterized in that, Before performing data analysis and processing on the structured real-time data in the ClickHouse cluster using a target processing engine that matches the structured real-time data, and generating corresponding data analysis results, the method further includes: Receive data query requests and obtain the object permissions and data application scenarios carried in the data query requests; Based on the object permissions and the data application scenario, a corresponding data query interface is matched; the data query interface is used to access the data analysis results corresponding to the data query request. The target processing engine that matches the structured real-time data is invoked to execute a distributed timed task associated with the data application scenario.

3. The method according to claim 2, characterized in that, The target processing engine, based on the structured real-time data matching, performs data analysis and processing on the structured real-time data in the ClickHouse cluster to generate corresponding data analysis results, including: Based on the target processing engine that matches the structured real-time data, execute the target script file corresponding to the distributed scheduled task; By executing the target script file, the structured real-time data in the ClickHouse cluster is processed to perform data analysis, and the corresponding data analysis results are obtained.

4. The method according to claim 2 or 3, characterized in that, After storing the data analysis results according to the data application scenario into the respective target caches corresponding to the ClickHouse cluster, the method further includes: Based on the data query interface, access the target cache corresponding to the data application scenario; Obtain the data analysis results stored in the target cache and feed them back to the target object corresponding to the data query request.

5. The method according to any one of claims 1 to 3, characterized in that, Importing the structured real-time data into the database tables of the self-built ClickHouse cluster includes: Based on the data application scenarios of the structured real-time data, a target processing engine matching the structured real-time data is determined from the table engines corresponding to the ClickHouse cluster. Based on the target processing engine and the business data characteristics corresponding to the structured real-time data, a database table corresponding to the structured real-time data is established in the ClickHouse cluster, and the structured real-time data is stored in the database table; wherein, when establishing the database table corresponding to the structured real-time data, the primary key, partition, and data retention period of the database table are determined based on the structured real-time data.

6. The method according to any one of claims 1 to 3, characterized in that, The step of sequentially performing data accuracy verification on the data attribute sets corresponding to each tuple to obtain the first structured data that passes the data accuracy verification includes: Perform a Cartesian product on the set of data attributes corresponding to each tuple to obtain the set of possible attributes. Obtain the expected attribute set and the actual attribute set corresponding to the data attribute set; Based on the set of possible attributes, the set of expected attributes, and the set of actual attributes, data accuracy is verified to obtain the first structured data that passes the data accuracy verification.

7. The method according to any one of claims 1 to 3, characterized in that, The step of determining the target attributes of the first structured data according to the data application scenario, and performing probability distribution verification of the data attributes based on the target attributes of the first structured data to obtain structured real-time data that passes the probability distribution verification includes: Based on the data application scenario, determine the target attributes of the first structured data; Based on the data application scenario and the data scale of the real-time business data stream, the first structured data is sampled, and a preset number of rows of data are extracted. Obtain the attribute category corresponding to the target attribute, and obtain the actual number of observations corresponding to each attribute category; Based on the preset probability value of the preset row data falling into the corresponding attribute category, and the preset number of data extracted, the theoretical expected number of times corresponding to each attribute category is determined. The corresponding chi-square value is determined based on the theoretical expected number of times and the actual number of observations for each attribute category. Using pre-set confidence data, the probability distribution of data attributes is verified on the chi-square value to obtain structured real-time data that passes the probability distribution verification.

8. A real-time business data stream analysis and processing system based on ClickHouse, characterized in that, The system includes: The structured data generation module is used to acquire real-time business data streams from various real-time data sources, and to perform data parsing and filtering on each of the real-time business data streams to obtain structured data to be analyzed. The data verification module is used to obtain the data attributes of each tuple in the structured data to be analyzed, and to obtain different sets of data attributes corresponding to each tuple in the structured data based on each data attribute; the data attributes of each set of data attributes are independent or related to each other; the data accuracy of each set of data attributes corresponding to each tuple is verified sequentially to obtain first structured data that passes the data accuracy verification; the target attribute of the first structured data is determined according to the data application scenario, and the probability distribution verification of the data attributes is performed based on the target attribute of the first structured data to obtain structured real-time data that passes the probability distribution verification, and the structured real-time data is imported into the database table of the self-built ClickHouse cluster; The data analysis result generation module is used to perform data analysis and processing on the structured real-time data in the ClickHouse cluster using a target processing engine that matches the structured real-time data, and generate corresponding data analysis results; the target processing engine is determined according to the data application scenario corresponding to the structured real-time data; The data analysis result storage module is used to store each of the data analysis results into a target cache corresponding to the ClickHouse cluster, according to the data application scenario; the target cache is matched with the data application scenario.

9. The system according to claim 8, characterized in that, The system also includes: The data query request receiving module is used to receive data query requests and obtain the object permissions and data application scenarios carried in the data query request. The data query interface matching module is used to match the corresponding data query interface based on the object permissions and the data application scenario; the data query interface is used to access the data analysis results corresponding to the data query request. The distributed scheduled task execution module is used to call the target processing engine that matches the structured real-time data and execute the distributed scheduled task associated with the data application scenario.

10. The system according to claim 9, characterized in that, The system also includes: The target script file execution module is used to execute the target script file corresponding to the distributed timed task according to the target processing engine that matches the structured real-time data; The data analysis result generation module is used to perform data analysis and processing on the structured real-time data in the ClickHouse cluster by executing the target script file, and obtain the corresponding data analysis results.

11. The system according to claim 9 or 10, characterized in that, The system also includes: The target cache access module is used to access the target cache corresponding to the data application scenario based on the data query interface; The data analysis result acquisition module is used to acquire the data analysis results stored in the target cache and feed the data analysis results back to the target object corresponding to the data query request.

12. The system according to any one of claims 8 to 10, characterized in that, The system also includes: The target processing engine determination module is used to determine the target processing engine that matches the structured real-time data from the table engines corresponding to the ClickHouse cluster, based on the data application scenario of the structured real-time data. The structured real-time data storage module is used to establish a database table corresponding to the structured real-time data in the ClickHouse cluster based on the target processing engine and the business data characteristics corresponding to the structured real-time data, and to store the structured real-time data in the database table; wherein, when establishing the database table corresponding to the structured real-time data, the primary key, partition and data retention period of the database table are determined based on the structured real-time data.

13. The system according to any one of claims 8 to 10, characterized in that, The data verification module is also used for: Calculate the Cartesian product of the data attribute set corresponding to each tuple to obtain the corresponding possible attribute set; obtain the expected attribute set and the actual attribute set corresponding to the data attribute set; perform data accuracy verification based on the possible attribute set, the expected attribute set and the actual attribute set to obtain the first structured data that passes the data accuracy verification.

14. The system according to any one of claims 8 to 10, characterized in that, The data verification module is also used for: Based on the data application scenario, determine the target attribute of the first structured data; based on the data application scenario and the data scale of the real-time business data stream, sample the first structured data and extract a preset number of rows of data; obtain the attribute category corresponding to the target attribute, and obtain the actual number of observations corresponding to each attribute category; Based on the preset probability value of the preset row data falling into the corresponding attribute category, and the preset number of data extracted, the theoretical expected number of times corresponding to each attribute category is determined. Based on the theoretical expected number of times and the actual number of observations corresponding to each attribute category, the corresponding chi-square value is determined; using pre-set reliability data, the probability distribution of the data attributes is verified on the chi-square value to obtain structured real-time data that passes the probability distribution verification.

15. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

16. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

17. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

Images