A traffic data warehouse construction method based on rule engine and Clickhouse

Abstract

The present invention discloses a method for building a traffic data warehouse based on a rule engine and Clickhouse. It is characterized by: by encapsulating and expanding the SDK, a set of tracking SDKs that collect multi-terminal user behavior data is formed, wherein the application end is Nginx, including a layer of transit Nginx for summarizing server logs; the traffic data warehouse uses the data of the offline data warehouse of the next day to cover the data of the real-time data warehouse of the day; the SQL queries of all analysis scenarios are based on ClickHouse, including business mathematical statistics; the preset data governance system performs full verification and real-time acceptance of the tracking points, and feeds back the tracking points that fail the acceptance to the developers; the rule engine is integrated into the tracking point management system to separate business decision logic and technical decision. It achieves the goal of achieving efficient traffic data warehouse construction, accurately collecting user behavior data and performing real-time processing and analysis to improve data processing efficiency and accuracy.

Description

Technical Field

[0001] The present invention relates to the field of traffic data warehouse construction using big data technology, and in particular to a traffic data warehouse construction method based on a rule engine and ClickHouse. Background Art

[0002] In the field of big data analysis, traditional big data analysis requires a combination of different frameworks and technologies to achieve ultimate results. Many small and medium-sized enterprises (SMEs) separate modules such as data collection, data cleaning and storage, data query, and data governance, or even lease services from large third-party companies for each module. This makes big data analysis expensive in terms of labor costs, technical capabilities, hardware costs, and maintenance costs. Traffic data is particularly difficult to process and manage due to its massive volume, high real-time requirements, and high levels of dirty data. Without a robust traffic data warehouse approach, it is difficult to process and manage massive amounts of traffic data.

[0003] ClickHouse is a column-based database management system (DBMS) for online analytical processing (OLAP) and is widely used in today's big data analysis field. Existing technologies have elaborated on many methods for ClickHouse to handle traffic data, focusing on its advantages:

[0004] Patent CN202010230307 - A real-time data alerting method based on a stream processing engine and a rule engine. This method utilizes Flink, Kafka, and the rule engine Drools to achieve real-time and rapid response to alert information. During Flink stream processing, Kafka can be used to monitor the relational database that stores the rule engine, such as MySQL's binlog, to dynamically update the processing rules in the stream processing process in real time, achieving rapid response capabilities for stream processing.

[0005] Patent CN202210779345 - Dynamic loading and updating method and related equipment of rule events based on rule engine, further elaborates on the use of Canal in Flink tasks to transmit the rule data added, deleted or updated in the Mysql database to the real-time analysis system through the message middleware Kafka, and broadcast the rule status information to the data analysis system for sharing. The dynamic loading technology of the overall system operation realizes non-stop system updates. After adding the rule engine and real-time data analysis transformation, tenants can add behavioral query conditions of multiple rule combinations according to personal needs, and can provide real-time feedback and updates to observe data status.

[0006] However, the above two patents do not involve real-time data query. For applications in data storage and data warehouses, a complete data warehouse system cannot be separated from data storage. If we want to make further breakthroughs in the performance of existing technologies, further improvements must be made.

[0007] Patent CN202111523688 - A Flink-based business data monitoring method, device, and storage medium. This patent utilizes a combination of tracking data, Flink, a logging system, a rules engine, and ClickHouse to ensure high concurrency and scalability for the business data monitoring system while reducing data storage costs. The tracking data, Flink, logging system, and rules engine enable highly concurrent log data processing. The rules engine facilitates modification of data processing logic, further ensuring the stability of stream processing. Utilizing the table engine integrated with ClickHouse and Kafka, app tracking data can be linked to ClickHouse tables. N tracking objects are linked to a structured table, with tracking fields mapped one-to-one with the fields in the structured table. This patent describes tracking data processing and storage but does not address data validation. Verification of tracking data is crucial for ensuring consistency between acquired tracking traffic data and expected data. If left unchecked, this can degrade the quality of the collected data and lead to inaccurate results in downstream data applications.

[0008] Based on the above existing technologies, a traffic data warehouse based on ClickHouse requires a more efficient and applicable data warehouse construction method that forms a closed-loop process integrating data collection, ETL, warehousing, query, and data governance. Summary of the Invention

[0009] The technical problem to be solved by the present invention is to overcome the deficiencies of the prior art and provide a data warehouse construction method based on a rule engine and Clickhouse. Based on Clickhouse, the entire process of traffic data warehouse construction is explained from data collection, data ETL, data warehousing, online query, and data governance system. The constructed traffic data warehouse meets the business requirements for data integrity and real-time performance, solves the pain point that developers cannot quickly and accurately iterate the version of the tracking SDK, and helps data personnel to quickly and intuitively locate abnormal data and perform data governance. At the same time, the rule engine separates business decisions from application code and uses predefined semantic modules to write business decisions. The use of the rule engine can reduce the complexity of the components that implement the complex business logic of the big data traffic data warehouse, effectively improve the maintainability of the code that implements the complex logic, and reduce the maintenance and scalability costs of the data warehouse application.

[0010] To solve the above technical problems, the present invention provides a traffic data warehouse construction method based on a rule engine and ClickHouse, which is characterized by comprising the following steps:

[0011] Step 1: Encapsulate the SDKs for each application based on the open-source tracking SDK. Send the data reported by the SDK to each business application. Use Flume to collect logs from each application, analyze them, and print the logs.

[0012] Step 2: Collect the printed logs through Flume and write them to HDFS files and send them to Kafka at the same time as the data source of the traffic data warehouse. The traffic data warehouse adopts the Lambda architecture, including an offline data warehouse based on Spark for parsing and processing T+1 data, and a real-time data warehouse based on Flink for parsing and sorting the real-time data of the day. The rule engine integrated in the tracking point management system is used to process the data parsing logic, and the structured data generated by the traffic data warehouse is stored in ClickHouse.

[0013] Step 3: Based on the materialized view function of ClickHouse, generate development reports for data analysis scenarios and provide a real-time query interface;

[0014] Step 4: Perform T+1 data full verification and real-time tracking data reporting and acceptance on the data stored in ClickHouse in step 2, and return the acceptance results to the developer;

[0015] Step 5: For step 2, use the rule engine to add or update the business logic in the Lambda architecture.

[0016] In step 1, the encapsulation of the SDK includes expanding the custom tracking function to form a tracking SDK that collects multi-terminal user behavior data. The application end is Nginx, and the Nginx application end also includes a layer of transit Nginx for aggregating server logs.

[0017] In step 2, the traffic data warehouse uses the data generated by the offline task of the offline data warehouse on the next day to overwrite the data generated by the real-time task of the real-time data warehouse on the same day.

[0018] In step 3, after the traffic data enters ClickHouse, all SQL queries for analysis scenarios are based on ClickHouse. The data analysis scenarios include business mathematical statistics.

[0019] In step 4, a data governance system for tracking point management is preset, and based on the definitions and rules of events and attributes made in advance in the data governance system, full verification of T+1 data and real-time tracking point acceptance are performed. The full verification of T+1 data includes verification of each event and attribute in the data governance system. The rules include new events, missing events, incorrect event names, and attribute enumeration values ​​that do not comply with the system settings. The real-time tracking point acceptance data is used to report data status in real time, using the real-time data source in ClickHouse, only accepting specified events and attributes, and feeding back tracking points that fail acceptance to the corresponding developers.

[0020] In step 5: the rule engine is integrated into the tracking point management system. The rule engine is preferably Drools, which is mapped into a method input during offline and real-time preprocessing. The etl task itself is not changed when the preprocessing logic is updated. The offline Spark task automatically reads the preprocessing rule table when it runs the next day. The real-time Flink task reads the preprocessing rule table during initialization and stores it in the state. The rule table is read regularly during task operation and the content of the preprocessing rules in the state is updated to separate the business decision logic of business decision makers and the technical decisions of application developers.

[0021] The beneficial effects achieved by the present invention are: a closed-loop traffic data warehouse construction methodology integrating data collection, data ETL, data warehousing, online query, and data governance systems is formed. Based on ClickHouse, offline and real-time data can be stored quickly, and historical and real-time data can be queried online at the same time based on materialized views. At the same time, ClickHouse provides corresponding data sources for data governance, and does not require too many big data components, thereby facilitating management, simplifying data links, improving business efficiency, and bringing into play greater value of data warehouses. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 A schematic diagram of a method flow of an exemplary embodiment of the present invention;

[0023] Figure 2 This is a schematic diagram of a ClickHouse-based traffic data warehouse construction system in an exemplary embodiment of the present invention. DETAILED DESCRIPTION

[0024] A rule engine, or BRMS (Business Rule Management System), is a component embedded in applications. Evolved from inference engines, a rule engine separates business decisions from application code. It uses predefined semantic modules (domain-specific languages) to write business decisions (business rules), which are then configured and managed by users or developers as needed.

[0025] The present invention will be further described below with reference to the accompanying drawings and exemplary embodiments:

[0026] like Figure 1 The data processing flow of a real-time data warehouse in a Flink-based real-time data warehouse construction method in an exemplary embodiment of the present invention is shown, and the specific steps are as follows:

[0027] S11: Based on the open source tracking SDK, the SDKs of various application terminals are encapsulated. The data reported by the SDK is sent to each business application terminal. Flume is used to collect logs from each application terminal for parsing and analysis, and the logs are printed. Specifically, the tracking SDKs based on the company's existing application terminals, such as web terminals, apps, servers, and mini-programs, are encapsulated based on the open source tracking SDK to collect tracking data from each application terminal. Some expansions such as customized tracking functions are performed to form a set of tracking SDKs that integrate multi-terminal user behavior data collection, avoiding the problem of repeated SDK development for different businesses. The SDKs are connected with the data governance system. In the data governance system, the relevant configuration of tracking, such as tracking master data and the definition of some common attributes, can be directly fed back to the tracking SDK, reducing development workload. The data reported by the SDK is sent to Nginx on each business application terminal, and then collected by Flume and sent to Kafka for subsequent data processing. If the company's business involves overseas, the buried data needs to be transmitted from overseas to domestic processing. For the convenience of management, the data of Nginx at home and abroad can be uniformly flowed to a domestic transit Nginx for data aggregation and log printing, and then collected uniformly by Flume.

[0028] S12: Adopting the lambda architecture, using the rule engine integrated in the tracking management system to process the data parsing logic, the structured data generated by the traffic data warehouse processing is stored in ClickHouse. Specifically: Based on the log data printed in S11, Flume is used for data collection. On the one hand, the data is written to the HDFS file as the data source for T+1 data etl and storage. On the other hand, the data is sent directly to Kafka as the data source for real-time data etl and storage. To prevent the loss of data when a single Flink real-time task is parsed, the lambda data warehouse architecture is adopted to ensure data accuracy and integrity. When the real-time data transmission link is interrupted, such as when Nginx is disconnected from the network when receiving data, the real-time data cannot be transmitted normally, which will lead to the loss of real-time data warehouse data. At this time, the logs written to the disk can be backfilled to the data warehouse offline to ensure data integrity. During the data ETL process, data preprocessing and general data regularization are required. This can be integrated with a data governance system. Preprocessing rules can be created as a rule configuration table. Leveraging the principles of a rule engine, these rules can be mapped into methods and passed in during offline, real-time preprocessing. Updating the preprocessing logic does not require modifying the ETL tasks themselves. Offline Spark tasks automatically read the preprocessing rule table when they run the next day. Real-time Flink tasks can read the preprocessing rule table during initialization and store it in the state. Subsequently, they periodically read the rule table during task execution and update the preprocessing rule content in the state. This achieves code decoupling. In data warehouses with large data traffic, the rule engine enables "non-invasive" updates to the data warehouse's business logic. Offline and real-time analysis share a common set of preprocessing rules, eliminating the need to maintain two sets of code, saving manpower and reducing data warehouse O&M costs. Offline and real-time processed data is aggregated into ClickHouse.

[0029] S13: Based on ClickHouse's materialized view functionality, reports are generated and developed for data analysis scenarios, providing a real-time query interface. Specifically, based on the structured data stored in ClickHouse in S12, optimizations can be made when querying data for business scenarios. For example, backend tables previously stored in Oracle or MySQL for data analysis systems, which require sharding due to large data volumes, can be converted to single tables with ClickHouse, eliminating the need for sharding logic and simplifying the existing sharding logic. CK-optimized syntax, such as ClickHouse's TopK and Limit nby Expression methods, can reduce the complexity of the original query. These are methods for optimizing query performance by rewriting SQL. If upper-level applications use the same underlying table multiple times, ClickHouse's materialized views can be used to configure reports based on business rules based on that table. In addition to materialized views, ClickHouse also provides dictionary tables and unified dimension integration, providing excellent support for daily business reporting in terms of both functionality and query speed. High-performance data warehouses with high query performance can be built around ClickHouse.

[0030] S14: Perform a full T+1 data validation and real-time tracking data reporting and acceptance on the data stored in ClickHouse in step 12, and return the acceptance results to the development team. Specifically, perform data validation on the data reported by SKD to ensure data quality. First, product personnel need to initialize the metadata of each tracking event and attribute in the data governance system, such as the event and attribute name definitions, the attributes included in the event, the attribute's value range, enumeration values, etc., that is, the validation rules that events and attributes must meet. Based on the T+1 data, the data governance system can display the reporting status of each event and attribute to date, such as whether there is data reporting, alarms, etc. In addition, it will perform event and attribute warning validation on the full data of the previous day and display it in the warning list. Based on real-time data, similar to the processing of T+1 data, tracking validation is performed in real time according to the validation rules. That is, when business and product teams accept tracking data, they can create acceptance tasks to perform real-time acceptance of specific events and attributes to verify the accuracy of the tracking data. This data is promptly fed back to the development team through the data governance system, and the traffic data warehouse is regularly refreshed to manage dirty data, forming a complete data governance closed loop.

[0031] S15: For step 12, use the rule engine to add or update the business logic in the lambda architecture, specifically: when you want to add or update the business logic of the data warehouse parsing code. Use the Drools rule engine in the tracking point management system to write the logic implementation method. Compared with other rule engines, Drools has lower development costs and faster deployment, and also has good community support. The rule engine updates the underlying data processing logic method table and triggers the Mysql Binlog log to notify the real-time task code of the business logic update in real time. The real-time data parsing logic is modified in real time, and the offline code will read the latest business logic when the offline task runs the next day.

[0032] like Figure 2 The diagram of a real-time data warehouse construction system based on Flink state in an exemplary embodiment of the present invention is shown. The specific steps are: based on the tracking SDKs of each end such as the web end, app end, server end, and applet end, user behavior traffic data is collected and sent to each application end such as Nginx 1, Nginx 2...Nginx n; after the transit Nginx summarizes the data of each application Nginx, the log is printed; Flume is used to collect the logs printed by the transit Nginx and enter Kafka; on the one hand, Flume is used to write Kafka data to HDFS for processing by the T+1 Spark task and then stored in Clickhouse; on the other hand, the Flink real-time task directly consumes Kafka data, writes it to Clickhouse after real-time processing, and the Spark and Flink tasks use the rule engine to implement changes in data processing logic during processing; finally, the tracking verification function in the data governance system verifies the tracking data after storage, and feeds back the verification results to the developer to update the tracking SDK of each end.

[0033] This invention primarily provides a method for building a traffic data warehouse based on a rules engine and ClickHouse. This method integrates data collection, data ETL, data storage, online query, and data governance into a closed-loop traffic data warehouse. This method not only provides traffic data to businesses quickly and stably, but also enables timely detection of dirty data and ensures data accuracy in data governance. This improves business efficiency and maximizes the value of the data warehouse. Furthermore, the use of a rules engine can effectively improve the maintainability of code that implements complex logic.

[0034] The above embodiments do not limit the present invention in any way. Any other improvements and applications made to the above embodiments in an equivalent manner fall within the scope of protection of the present invention.

Claims

1. A traffic data warehouse construction method based on rule engine and Clickhouse, characterized in that: The following steps are involved: Step 1: Encapsulate the SDKs for each application based on the open-source tracking SDK. Send the data reported by the SDK to each business application. Use Flume to collect logs from each application, analyze them, and print the logs. Step 2: Collect the printed logs through Flume and write them to HDFS files and send them to Kafka at the same time as the data source of the traffic data warehouse. The traffic data warehouse adopts the Lambda architecture, including an offline data warehouse based on Spark for parsing and processing T+1 data, and a real-time data warehouse based on Flink for parsing and sorting the real-time data of the day. The rule engine integrated in the tracking management system is used to process the data parsing logic, and the structured data generated by the traffic data warehouse is stored in ClickHouse. Step 3: Based on the materialized view function of ClickHouse, generate development reports for data analysis scenarios and provide a real-time query interface; Step 4: Perform T+1 data full verification and real-time tracking data reporting and acceptance on the data stored in ClickHouse in step 2, and return the acceptance results to the developer; Step 5: For step 2, use the rule engine to add or update the business logic in the lambda architecture; in step 5: the rule engine is integrated in the tracking point management system, and the rule engine is Drools. During offline and real-time preprocessing, it is mapped into a method input. When the preprocessing logic is updated, the etl task itself is not changed. The offline Spark task automatically reads the preprocessing rule table when it runs the next day. The real-time Flink task reads the preprocessing rule table during initialization and stores it in the state. The rule table is read regularly during task operation and the content of the preprocessing rules in the state is updated to separate the business decision logic of the business decision maker and the technical decision of the application developer.

2. The method for building a traffic data warehouse based on a rule engine and ClickHouse according to claim 1, characterized in that: In step 1, the encapsulation of the SDK includes expanding the custom tracking function to form a tracking SDK that collects multi-terminal user behavior data. The application end is Nginx, and the Nginx application end also includes a layer of transit Nginx for aggregating server logs.

3. The method for building a traffic data warehouse based on a rule engine and ClickHouse according to claim 2, characterized in that: In step 2, the traffic data warehouse uses the data generated by the offline task of the offline data warehouse on the next day to overwrite the data generated by the real-time task of the real-time data warehouse on the same day.

4. The method for building a traffic data warehouse based on a rule engine and ClickHouse according to claim 3, characterized in that: In step 3, after the traffic data enters ClickHouse, all SQL queries for analysis scenarios are based on ClickHouse; the data analysis scenarios include business mathematical statistics.

5. The method for building a traffic data warehouse based on a rule engine and ClickHouse according to claim 4, characterized in that: In step 4, a data governance system for tracking point management is preset, and based on the definitions and rules of events and attributes made in advance in the data governance system, full verification of T+1 data and real-time tracking point acceptance are performed. The full verification of T+1 data includes verification of each event and attribute in the data governance system. The rules include new events, missing events, incorrect event names, and attribute enumeration values ​​that do not comply with the system settings. The real-time tracking point acceptance data is used to report data status in real time, using the real-time data source in ClickHouse, only accepting specified events and attributes, and feeding back tracking points that fail acceptance to the corresponding developers.

Images