Real-time data warehouse design method, device, equipment and storage medium based on Doris

Abstract

The present invention specifically discloses a real-time data warehouse design method, device, equipment and storage medium based on Doris. It includes: importing business real-time data into a Kakfa queue, and putting it into an adapted topic tag, providing at least one consumption task for consuming the data in the topic tag, and setting an adapted Routine Load task monitoring; the Routine Load task monitoring reads in real time the offset used to indicate the location information of the current data consumed in the topic tag; if the offset does not increase within a preset time, restart the Routine Load task monitoring, and continue to execute the consumption task from the offset; import the Stream Load data stream after the consumption task is executed into Doris. The present invention further includes a technical means for importing Doris into real-time data, offline data, fixed data, etc. that require secondary business logic processing, which is of great significance to the construction of data warehouses in improving business support efficiency and reducing maintenance costs.

Description

Technical Field

[0001] The present invention belongs to the technical field of data warehouses for logistics platforms, and in particular relates to a real-time data warehouse design method, device, equipment, and storage medium for Doris. Background Art

[0002] With the growing express delivery industry, the daily volume of logistics and express delivery data has reached billions. Faced with such a large amount of data, including not only ticket data but also monitoring data from smart hardware, such as vehicle cameras, etc., processing this data requires an interactive analysis platform that allows business personnel to execute some Adhoc (multi-hop network) commands to query different data. It must also support common SQL (Structured Query Language) syntax to reduce learning costs and ensure fast queries.

[0003] Hanging or blocking often occurs during real-time data processing.

[0004] Furthermore, the daily data volume reaches over 10 billion records, requiring not only fast performance from the analysis platform but also the ability to join multiple detailed tables for various ticket quantities with dimension tables representing hundreds of millions of records. Furthermore, accurate deduplication of ticket quantities across multiple tables is required, ensuring data accuracy. Furthermore, the platform must support the export of multiple reports and the import of multiple data sources. Existing data warehouses on logistics platforms are unable to meet these requirements. Summary of the Invention

[0005] The present invention provides a real-time data warehouse design method, device, equipment and storage medium based on Doris, which solves the problem of hanging or blocking during real-time data import in the technology, and also provides a solution that supports importing more data sources.

[0006] This method includes:

[0007] Import real-time business data into the Kakfa queue and place it in an adapted topic tag. Provide at least one consumer task to consume the data in the topic tag and set up adapted Routine Load task monitoring.

[0008] The Routine Load task monitors and reads in real time the offset indicating the location of the current data consumed in the topic tag;

[0009] If the offset does not increase within a preset time, restarting the Routine Load task monitoring and continuing to execute the consumption task from the offset;

[0010] Import the Stream Load data stream after the consumption task is executed into Doris.

[0011] Furthermore, importing the Stream Load data after the execution of the consumption task into Doris further includes: creating a sink in Doris: flink-connector-dorisdb, on the real-time computing cluster Flink, to output the final location of the Stream Load data flow; and directly importing the Stream Load data into Doris via Http through caching and batches.

[0012] This solution also includes real-time import of data processed by secondary business logic, including:

[0013] After the Stream Load data stream after the execution of the consumption task is imported into Doris, it also includes:

[0014] Find the real-time data that needs secondary business logic processing, first perform the secondary business logic processing on the real-time data through Flink, then cache the data after the logic processing, and finally import the cached data into Doris through Stream Load. The secondary business logic processing of at least one real-time data that needs secondary business logic processing first through Flink further includes: the secondary business logic of at least one real-time data is set as a task management, the task management submits the adapted task job to the JobManager, the JobManager is responsible for the scheduling and resource allocation of the job, the JobManager assigns the job to the corresponding TaskManager, after receiving the task job, the TaskManager starts a thread to execute, and reports the task status and its own running status to the JobManager; when the task execution is completed, the JobManager will receive a notification and send the data processed by the secondary business logic to the corresponding task management.

[0015] In this example, at least one real-time data that requires secondary business logic processing first passes through Flink's secondary business logic processing, further including:

[0016] First, consume data from the ODS log topic, and then divide the behavioral data in the data into three types based on user behavior: page logs, exposure logs, and startup logs;

[0017] A Flink state variable is defined as time to filter the data of new users and determine whether they are new users. If they are new users, the corresponding state is empty and the current time is updated to the state. Otherwise, the state is not empty and the current user is changed to an old user.

[0018] The filtered data is split into two streams. The startup log and the exposure data are written to the side output stream. The page data is written to the main stream and the corresponding value is obtained through json. The start is written in start to start the test output stream. The startup log is removed and all the page logs are written. The page logs are written to the main stream and then the exposure logs are written to the exposure side output stream.

[0019] The method also includes importing offline business data, which includes:

[0020] In the offline business scenario, Broker Load is used to import the offline business data from HDFS into Doris;

[0021] DolphinSchedule is used to configure scheduling tasks in SQL mode, and the offline business data is processed in batches and then aggregated into a master table.

[0022] This method also includes relatively fixed data import, which also includes:

[0023] In actual business scenarios, relatively fixed data is directly connected to the data service hall, which is then connected to the Power BI tool and front-end reports.

[0024] Based on the same concept, the present invention also provides a real-time data warehouse design device based on Doris, comprising:

[0025] Routine Load task monitoring setting module: used to import real-time business data into the Kakfa queue and put it into the adapted topic tag, then provide at least one consumer task to consume the data in the topic tag and set the adapted Routine Load task monitoring;

[0026] Routine Load task monitoring processing module: used for the Routine Load task monitoring to read in real time the offset indicating the location of the current data consumed in the topic tag;

[0027] The Routine Load task monitoring startup module is configured to restart the Routine Load task monitoring if the offset does not increase within a preset time, and continue to execute the consumption task from the offset;

[0028] Import module: used to import the Stream Load data stream after the consumption task is executed into Doris.

[0029] Based on the same concept, the present invention further provides a computer device, comprising:

[0030] a memory for storing a processing program;

[0031] A processor, wherein when executing the processing program, the processor implements the above-mentioned Doris-based real-time data warehouse design method.

[0032] Based on the same concept, the present invention also provides a readable storage medium, on which a processing program is stored. When the processing program is executed by a processor, the above-mentioned Doris-based real-time data warehouse design method is implemented.

[0033] After adopting the above technical solution, compared with the prior art, the present invention has the following advantages:

[0034] This invention implements a custom Routine Load monitor to read its offset in real time. If the offset stops increasing, the monitoring system manually stops the Routine Load and restarts it with the previous offset. This ensures that data is not lost or duplicated, while also restoring the Routine Load. This invention improves the efficiency and security of real-time data import processing, making it less prone to blockage. This eliminates data backlogs and ensures smooth operation during peak data access times.

[0035] The present invention supports the export of multiple reports and the import of multiple data sources. It is of great significance for data warehouse construction in improving business support efficiency and reducing maintenance costs. For example, in complex business logic, real-time business data is first processed by Flink, and then cached by Redis or PIKA. After processing, it can be imported into Doris using the Stream Load method; in offline business scenarios, business data is imported from HDFS into Doris using Broker Load, and then batch processed using DolphinScheduler using SQL, and finally summarized into a master table; in actual business scenarios, it is directly connected to the data service hall, and the data service hall is connected to the Power BI tool and the front-end report. The number of tickets in multiple tables is accurately deduplicated, and the accuracy of the data is guaranteed; it can fully support the export of multiple reports and the import of multiple data sources. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The specific embodiments of the present invention are further described in detail below with reference to the accompanying drawings, wherein:

[0037] Figure 1 This is a schematic diagram of the first example of the real-time data warehouse design method based on Doris of the present invention;

[0038] Figure 2This is a functional block diagram of the real-time data warehouse design system based on Doris.

[0039] Figure 3 This is a flow chart of the second example of the real-time data warehouse design method based on Doris of the present invention;

[0040] Figure 4 This is a schematic diagram of an embodiment of a real-time data warehouse design device based on Doris in the present invention;

[0041] Figure 5 Schematic diagram of an embodiment of a computer device of the present invention. DETAILED DESCRIPTION

[0042] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments. The advantages and features of the present invention will become more apparent from the following description and claims. It should be noted that the drawings are greatly simplified and not to exact ratios, and are intended solely to facilitate and clearly illustrate the embodiments of the present invention.

[0043] It should be noted that all directional indications in the embodiments of the present invention (such as up, down, left, right, front, back, etc.) are only used to explain the relative position relationship, movement status, etc. between the various components under a certain specific posture (as shown in the accompanying drawings). If the specific posture changes, the directional indication will also change accordingly.

[0044] Example 1

[0045] In the era of big data, data processing capabilities have greatly increased, but significant bottlenecks remain in the final link: data application services. In the era of business databases, high concurrency and high flexibility were a contradiction. Enabling frontline staff to flexibly customize SQL (Structured Query Language) analysis, even when dragging and dropping generates unsightly SQL, while ensuring the query engine maintains strong performance metrics and customer satisfaction in terms of both concurrency and query time, is a significant challenge. Currently, there are numerous query and analysis engines, and they continue to emerge. These engines, such as ADB, Hologres, Presto, Kylin, and HBase (a few database names), essentially trade resources for time, or space for time. Essentially, these products improve performance through computational restructuring, hardware acceleration (such as SSDs), indexing speedups (such as bitmaps), and spatial transformations (such as pre-computation). The continuous emergence and development of new technologies also confirms that query engines still face numerous challenges and unresolved pain points, but also demonstrate significant potential for improvement. Doris is a database system that has recently been adapted to real-time analysis scenarios and has indeed solved the pain points of query engines in some scenarios.

[0046] Doris consists of the core components of FrontEnd DorisDB (front-end node) and BackEnd DorisDB (back-end node). The front-end node is responsible for managing metadata, managing client connections, query planning and scheduling, etc.; the back-end node is responsible for data storage, calculation execution, replica management, etc.; it also includes DorisManager (Doris management tool) and Broker (broker). DorisManager management tool is responsible for providing visualization tools for cluster management, online query, fault query, monitoring and alarm; Broker is responsible for auxiliary functions such as exporting and importing data with external storage (HDFS, Hadoop Distributed File System or object storage); Doris can be directly accessed through the MySQL client.

[0047] The core components and operation modes of Doris are as follows (FrontEnd DorisDB is referred to as FE, BackEnd DorisDB is referred to as BE):

[0048] First, let me introduce FE.

[0049] 1) Manage metadata, execute SQL DDL commands, and use catalogs, tables, partitions, tablet replicas, and other information.

[0050] 2) FE high-availability deployment uses a replication protocol to elect a leader and synchronize metadata between master and slave nodes. All metadata modifications are performed by the FE leader node (FE master node), while FE follower nodes (FE tracking nodes) can perform read operations. Metadata reads and writes meet sequential consistency. The number of FE nodes is 2n+1, which can tolerate n node failures. If the FE leader fails, a new leader is elected from the existing follower nodes to complete the failover.

[0051] 3) FE's SQL layer parses, analyzes, rewrites, performs semantic analysis and relational algebra optimization on SQL submitted by users, and produces logical execution plans.

[0052] 4) FE's Planner (SQL physical plan) is responsible for converting the logical plan into a physical plan that can be executed in a distributed manner and distributing it to a group of BEs.

[0053] 5) FE supervises BE, manages BE's online and offline, and maintains the number of tablets (replicas) based on BE's survival and health status.

[0054] 6) FE coordinates data import to ensure the consistency of data import.

[0055] Next, let me introduce BE.

[0056] 1) BE manages tablets (replicas). Tablets are subtables formed by partitioning and bucketing a table and are stored in columnar format.

[0057] 2) BE is guided by FE to create or delete sub-tables.

[0058] 3) BE receives the physical execution plan distributed by FE and specifies the BE coordinator node (BE coordination node). Under the scheduling of BEcoordinator, it collaborates with other BE workers (BE execution nodes) to complete the execution.

[0059] 4) BE reads data from the local column storage engine and quickly filters the data through indexes and predicate sinking.

[0060] 5) The BE backend performs compaction tasks to reduce read amplification during queries.

[0061] Taking query as an example, through the organization, coordination, and control of FE, the submitted SQL is parsed, analyzed, rewritten, optimized, and planned to generate a distributed execution plan, which is then executed by several BEs. A coordinator is selected from the BEs, and the collaborator coordinates n BEs to perform local calculations, which are then returned to the collaborator. The collaborator summarizes the results and returns them to the FE, which finally provides the final results to the end user.

[0062] This technical solution provides a first example principle diagram of a real-time data warehouse design method based on Doris (see Figure 1 ), including importing various data into the Doris data warehouse in different ways:

[0063] S1: Importing real-time data: By importing real-time business data from Flume (a massive log collection, aggregation and transmission system) or Canal (Alibaba's open source middleware) into Kafka (a high-throughput distributed publish-subscribe messaging system), the real-time business data in Kafka (a distributed log system) undergoes multiple data processing and is then imported into Doris using SQL.

[0064] S2: Importing complex real-time data: Generally, real-time data that requires secondary logical processing is called complex real-time data business logic. This type of business real-time data is first processed through Flink (a distributed processing engine for stream and batch data, i.e., a real-time computing cluster), and then cached using Redis or PIKA (a Redis-like storage system open sourced by Qihoo). Once processed, it can be imported into Doris using Stream Load (a synchronous import method). Specifically, first find the real-time data that requires secondary business logic processing, first perform the secondary business logic processing on the real-time data through Flink, then cache the data after the logic processing, and finally import the cached data into Doris using Stream Load.

[0065] S13: Importing offline business data: Business data is imported from HDFS (Hadoop Distributed File System) into Doris using Broker Load (an asynchronous import method). Then, through SQL, DolphinScheduler (batch processing plan) is used to batch process complex business data and finally aggregate it into a master table. Alternatively, if the data source is Hive / HDFS, Broker Load is recommended for import. If importing many data tables is cumbersome, consider using Hive external table direct join queries. While performance may be worse than Broker Load import, it can avoid data migration. If the data volume of a single table is particularly large, or a global data dictionary is required for accurate deduplication, consider SparkLoad import.

[0066] S14: Importing fixed data (such as special tables): In actual business scenarios, this directly connects to the data service center, which then connects to the Power BI tool and front-end reporting. This allows for precise deduplication of multiple tables and ensures data accuracy. This proposal supports the export of multiple reports and the import of multiple data sources, eliminating data backlogs and ensuring smooth operation even during peak data volumes.

[0067] In the present invention, an import operation can be divided into several stages:

[0068] 1) LOADING

[0069] In this phase, the data is cleaned and converted before being sent to BE for processing. Once all the data has been imported, the job enters the waiting phase, during which the import job status remains in the LOADING state.

[0070] 2) FINISHED

[0071] After all data involved in the import job has taken effect, the job status changes to FINISHED. After FINISHED, all imported data can be queried. FINISHED is the final status of the import job.

[0072] 3)CANCELLED

[0073] Before an import job changes to FINISHED, it may be canceled and enter the CANCELLED state at any time, for example, if the user cancels the job manually or an import error occurs. CANCELLED is also the final state of an import job.

[0074] The import operation of the present invention can be applied to the following scenarios:

[0075] 1) HDFS import

[0076] When the source data is stored in HDFS and the data volume is tens to hundreds of GB, the Broker Load method can be used to import data into DorisDB. In this case, the deployed Broker process must be able to access the HDFS data source. The data import job is executed asynchronously, and users can view the import results through the SHOW LOAD command.

[0077] When the source data is stored in HDFS and the data volume reaches the TB level, the Spark Load method can be used to import data into DorisDB. In this case, the deployed Spark process must be able to access the HDFS data source. The data import job is executed asynchronously, and users can view the import results using the SHOW LOAD command.

[0078] For other external data sources, as long as the Broker or Spark process can read the corresponding data source, you can also use the Broker Load or Spark Load method to import data.

[0079] 2) Local file import

[0080] The data is stored in local files, and the data volume is less than 10GB. The Stream Load method can be used to quickly import the data into the DorisDB system. The HTTP protocol is used to create an import job, and the job is executed synchronously. The user can determine whether the import is successful by the return value of the HTTP request.

[0081] 3) Kafka import

[0082] When data comes from streaming data sources such as Kafka and needs to be imported into the DorisDB system in real time, the Routine Load method can be used. Users create routine import jobs through the MySQL protocol, and DorisDB continuously reads and imports data from Kafka.

[0083] 4)Insert Into

[0084] The Insert Into method can be used to write data into a DorisDB table during manual testing and temporary data processing. The INSERT INTO tbl SELECT... statement reads data from a DorisDB table and imports it into another table; the INSERT INTO tbl VALUES(...) statement inserts a single row of data into a specified table.

[0085] 5) Other methods include importing JSON data (for semi-structured data such as JSON, we can use stream load or routine load. Stream load: For JSON data stored in text files, we can use stream load to import. Routine load: For JSON data in Kafka, we can use routine load to import), flink-connector-dorisdb (the internal implementation is to cache and batch import by stream load), and DataX-dorisdb-writer (the DorisWriter plug-in implements the function of writing data to the destination table of DorisDB. At the underlying implementation, DorisWriter imports data into DorisDB in CSV or JSON format through stream load. The data read by the reader is cached internally and then imported into DorisDB in batches to improve write performance. The overall data flow is source->reader->DataX channel->writer->DorisDB.)

[0086] Data export (Export) is a function provided by DorisDB to export data and store it on other media. This function can export the data of user-specified tables or partitions in text format to remote storage such as HDFS / Alibaba Cloud OSS / AWS S3 (or object storage compatible with the S3 protocol) through the Broker process.

[0087] After a user submits an export job, DorisDB counts all the tablets involved in the job, groups them, and generates a unique query plan for each group. This query plan reads the data on the included tablets and writes the data to the specified path in the remote storage through the Broker. The processing flow mainly includes: 1) The user submits an export job to the FE.

[0088] 2) The FE's export scheduler executes an export job in two phases: 3) PENDING: The FE generates an ExportPendingTask, sends a snapshot command to the BE, takes a snapshot of all involved tablets, and generates multiple query plans. 4) EXPORTING: The FE generates an ExportExportingTask and begins executing the query plans one by one.

[0089] Example 2

[0090] See also Figure 2 , which is a principle system diagram of a real-time data warehouse design example based on Doris in the present invention. It includes a data center 11, a Doris data warehouse 12, a Flink real-time computing cluster 14 (hereinafter referred to as Flink), Kafka 13, and HDFS 15. The data warehouse as a whole is built with Doris 12 as the core to build an enterprise-level data warehouse (through a data collection system, multiple data collection methods, including MySQL Binlog parsing (Cannal), log collection can realize the collection of various heterogeneous data through Flume (Doris audit log), buried point interface, etc., and the collected data is unified through the message queue (Kafka13) to complete high-concurrency data throughput, while realizing the decoupling of data warehouse and computing engine. Flink14 completes the ETL processing of data and real-time data statistics. The data middle platform 11 completes the management and control of the above matters, including data quality management and data management of the entire life cycle of data storage and data services. It realizes data management from data collection to data ETL processing, data storage and data service life cycle, including metadata, data quality, data specifications, data security, etc. HDFS15 realizes the storage of offline data and is used to import offline data into Doris11. The data middle platform 11 can be implemented by many server clusters or servers. For the convenience of explanation, it is referred to as server.

[0091] There are many principle systems for implementing real-time data warehouse design, and the above is just an example.

[0092] See also Figure 3 , a flowchart of a real-time data warehouse design method based on Doris. It includes:

[0093] S110: Import the real-time business data into the Kakfa queue and put it into the adapted topic tag, provide at least one consumption task for consuming the data in the topic tag, and set the adapted Routine Load task monitoring;

[0094] S120: Routine Load task monitoring reads in real time the offset indicating the location of the current data consumed in the topic tag;

[0095] S130: If the offset does not increase within a preset time, restart the Routine Load task monitoring and continue to execute the consumption task from the offset;

[0096] S140: Import the Stream Load data stream after the consumption task is executed into Doris.

[0097] This process is for real-time business data that does not require special and complex secondary processing and is imported into Doris in real time. Set multiple topic tags in the Kakfa queue (such as Figure 1As shown, it includes a first topic tag 131, a second topic tag 132, ... an Nth topic tag 133). Business real-time data is imported into the Kakfa queue on demand or by category and placed in its adapted topic tag. If the Stream Load data stream of the direct consumption topic is directly imported into Doris, it will be found that the data will be blocked and often hangs up. For this reason, our technical staff learned through analysis that the problem of backlog data was discovered due to the large amount of imported data and the large number of transactions. For this reason, our company sets up Routine Load task monitoring on the server. The consumption task that provides the consumption of data in the topic tag is generally adapted to a Routine Load task monitoring. In the present invention, in the first topic tag 131, the second topic tag 132, ... the Nth topic tag 133, there may be multiple consumption tasks that provide the consumption of data in a topic tag. For example, the first topic tag 131 may have multiple consumption tasks, and new consumption tasks will be created in real time according to the processing or statistical data required. In the present invention, a corresponding Routine Load task monitoring is created for the message task. When the offset in the Routine Load task monitoring does not change within a preset time (such as a few seconds), the Routine Load task monitoring is restarted, and the consumption task is continued to be executed according to the offset, that is, the corresponding Stream Load data flow in the consumer task is restarted and imported into Doris. In this example, a Routine Load task monitoring group can be established, and a Routine Load task monitoring group is set for each topic tag 131. If there are multiple groups of Load task monitoring groups whose offsets do not change within a preset time, various Routine Load task monitorings can be started separately. When this situation occurs multiple times within a preset time, a notification that requires manual processing can be issued. This setting further improves the efficiency of processing and improves its security. This example uses a custom Routine Load monitor to read its offset in real time. If the offset stops increasing, the monitoring system manually stops the Routine Load and restarts it using the previous offset. This ensures that data is not lost or duplicated, and that the Routine Load is restored.

[0098] In addition, in this example, the most commonly used Stream Load can be achieved by customizing Flink's sink. This operation is also relatively simple, and the data can be directly imported into Doris using HTTP. That is, importing the Stream Load data after the consumer task is executed into Doris further includes:

[0099] Create a sink in the Flink real-time computing cluster to output the Stream Load data flow to Doris: flink-connector-dorisdb;

[0100] Stream Load data is cached and imported directly into the Doris in batches via Http.

[0101] Specifically, you must first download the connector jar package and put it in the lib / directory of flink, then enter the cli and execute the following command to create a sink to dorisdb. Create a flink-connector-dorisdb: Stream Load data flow to the final output location of the sink in Doris. Adding an exactly-once data sink requires a two-phase commit mechanism of the external system. Since DorisDB does not have this mechanism, the applicant needs to rely on flink's checkpoint-interval to save the cached data group and its label at each checkpoint, and block the flush of all cached data in all states when subsequent data is passed in, so as to achieve accurate once. However, if DorisDB hangs up, it will cause the user's flink sink stream operator to be blocked for a long time, and cause flink's monitoring alarm or forced kill. The present invention can use csv format for import by default. If the import is still stopped, the present invention can also increase the memory of the flink task to improve work efficiency.

[0102] Example 3

[0103] The above example 2 is mainly for the import of real-time business data. This example 3 can be based on this to add Doris to the import of real-time data after secondary business logic processing. Before real-time import, there are still many data or tasks that require various secondary processing before importing. In this case, the present invention adopts the following solution to achieve:

[0104] At least one real-time data that requires secondary business logic processing is first processed by Flink's secondary business logic, then cached, and then the processed Stream Load data is imported into Doris. Specifically, it also includes: finding the real-time data that requires secondary business logic processing, first performing the secondary business logic processing on the real-time data through Flink, then caching the data after the logic processing, and finally importing the cached data into Doris through Stream Load. For example, if there are a large number of duplicate data records in the data, the secondary business logic processing can include checking and deduplicating the data, and then caching the duplicate checked and deduplicated data first, and then importing it into Doris through Stream Load.

[0105] At least one piece of real-time data that requires secondary business logic processing first passes through Flink's secondary business logic processing, further including:

[0106] At least one secondary business logic of the real-time data is set as a task management, and the task management submits the adapted task job to the JobManager, and the JobManager is responsible for the scheduling and resource allocation of the job,

[0107] The JobManager assigns the job to the corresponding TaskManager. After receiving the task job, the TaskManager starts a thread to execute it and reports the task status and its own running status to the JobManager.

[0108] When the task is completed, the JobManager will receive a notification and send the data processed by the secondary business logic to the corresponding task manager.

[0109] For example, at least one piece of real-time data that requires secondary business logic processing first passes through Flink's secondary business logic processing, which further includes:

[0110] First, consume data from the ODS log topic, and then divide the behavioral data in the data into three types based on user behavior: page logs, exposure logs, and startup logs;

[0111] By defining a Flink state variable as time, we can filter the data of new users: we can determine whether the user is a new user. If it is their first visit, their state is empty and the current time is updated into the state. Otherwise, if the state is not empty, the current user is changed to an old user.

[0112] The filtered data is diverted, the startup log and the exposure data are written to the side output stream, the page data is written to the mainstream and the corresponding value is obtained through json, the startup test output stream is written in start, the startup log is removed and all are page logs, the page log is written to the mainstream, and then it is determined whether it is an exposure log, and then written to the exposure side output stream.

[0113] Example 4

[0114] Compared to Example 2 or Example 3, Example 4 includes processing for the offline business import solution. It further includes: in the offline business scenario, using Broker Load to import the offline business data from HDFS into Doris; using SQL to configure scheduling tasks using DolphinScheduler, batch processing is completed to complete the offline business data processing, and then aggregating it into a master table.

[0115] Configure the scheduling task in DolphinScheduler, that is, which offline business needs to import the configured scheduling task, and then import the corresponding data to Doris through HDFS, and summarize it into a summary table as needed. In addition, query the import results through the following command

[0116] show load where label="ts_202111221719";

[0117] CANCELLED: The import failed. You can check the http link for the error reason.

[0118] LOADING: Importing normally.

[0119] FINISHED: Import completed

[0120] Use the above command to track and feedback each scheduling task, and promptly know the import results and proceed.

[0121] Example 5

[0122] This example also includes: relatively fixed data in actual business scenarios, directly connected to the data service hall, and the data service hall is connected to the Power BI tool and the front-end report. The purpose of this step is to directly output some relatively fixed data to the front-end report. For Adhoc data, BI tools are provided for business personnel to query. Power BI is an extraordinary product from Microsoft. Power BI is based on the advanced functions of Excel, and has extended and developed a variety of functional points as a collection, and finally created this BI tool. Power BI can directly import and analyze data in conventional file formats, such as Excel, TXT, etc. that everyone is familiar with, and can also directly connect to traditional databases and multidimensional databases. It goes without saying that as a work of a large manufacturer, Power BI is very stable and easy to use. In addition, PowerBI provides some operating options for the visual interface, combined with M language and DAX functions, data processing can be performed.

[0123] Example 6

[0124] like Figure 4 As shown, based on the same concept, the present invention also provides a real-time data warehouse design device based on Doris, including:

[0125] Routine Load task monitoring setting module 401: used to import real-time business data into the Kakfa queue and put it into the adapted topic tag, then provide at least one consumption task that consumes the data in the topic tag and set the adapted Routine Load task monitoring;

[0126] Routine Load task monitoring processing module 402: used for the Routine Load task monitoring to read in real time the offset indicating the location of the current data consumed in the topic tag;

[0127] The Routine Load task monitoring starting module 403 is configured to restart the Routine Load task monitoring if the offset does not increase within a preset time, and continue to execute the consumption task from the offset;

[0128] Import module 404: used to import the Stream Load data stream after the consumption task is executed into Doris.

[0129] The real-time data warehouse design device can be integrated into the above-mentioned data warehouse, or can be a separate server.

[0130] Example 7

[0131] like Figure 5 As shown, based on the same concept, the present invention also provides a computer device 600, which may have relatively large differences due to different configurations or performances, and may include one or more processors 610 (central processing units, CPU) 710 (for example, one or more processors) and a memory 620, and one or more storage media 630 (for example, one or more mass storage devices) storing application programs 633 or data 632. The memory 620 and the storage medium 630 may be temporary storage or permanent storage. The program stored in the storage medium 630 may include one or more modules (not shown in the figure), each module may include a series of instruction operations in the computer device 600. Furthermore, the processor 610 may be configured to communicate with the storage medium 630 to execute the series of instruction operations in the storage medium 630 on the computer device 600.

[0132] The computer device 600 may further include one or more power supplies 640, one or more wired or wireless network interfaces 650, one or more input and output interfaces 660, and / or one or more operating systems 631, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc. It will be appreciated by those skilled in the art that Figure 5 The illustrated computer device structure does not limit the computer device and may include more or fewer components than shown in the figure, or combine certain components, or arrange components differently.

[0133] When the computer-readable instructions are executed by the processor, real-time business data is imported from Flume or Canal into Kafka. The real-time business data in Kafka undergoes multiple data processing steps and is then imported into Doris using SQL. In complex business logic, real-time business data is first processed through Flink and then cached using Redis or PIKA. After processing, it can be imported into Doris using the Stream Load method. In offline business scenarios, business data is imported from HDFS into Doris using Broker Load, and then batch-processed using DolphinScheduler using SQL, and finally summarized into a master table. In actual business scenarios, it is directly connected to the data service hall, which is then connected to the Power BI tool and front-end reports. The number of tickets in multiple tables is accurately deduplicated, and the accuracy of the data is guaranteed. It supports the export of multiple reports and the import of multiple data sources. There is no data backlog, ensuring smooth operation during peak data access times.

[0134] In one embodiment, a computer-readable storage medium is proposed. When the computer-readable instructions are executed by one or more processors, the one or more processors execute the above-mentioned Doris-based real-time data warehouse design method. The specific steps are not repeated here.

[0135] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0136] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), magnetic disk or optical disk, etc., various media that can store program code.

[0137] As described above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that the technical solutions described in the above embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A real-time data warehouse design method based on Doris, characterized in that: include: Import the real-time business data into the Kafka queue and put it into the adapted topic tag, provide at least one consumption task for consuming the data in the topic tag, and set the adapted Routine Load task monitoring; The Routine Load task monitors and reads in real time the offset of the location information of the current data consumed in the topic tag; If the offset does not increase within the preset time, restart the Routine Load task monitoring and continue to execute the consumption task from the offset; Import the Stream Load data stream after the execution of the consumption task into Doris; After the Stream Load data stream after the execution of the consumption task is imported into Doris, the following steps are also included: finding the real-time data that needs secondary business logic processing, firstly performing the logic processing of the secondary business on the real-time data through Flink, then caching the data after the logic processing, and finally importing the cached data into Doris through Stream Load; The real-time data is first subjected to the logic processing of the secondary business through Flink, wherein the logic processing of the secondary business further includes: first consuming data from the ods log topic, and then dividing the behavior data in the data into three types according to user behavior: page log, exposure log, and startup log; A Flink state variable is defined as time to filter the data of new users and determine whether they are new users. If they are new users for the first time, the corresponding state is empty and the current time is updated to the state. Otherwise, if the state is not empty, the current user is changed to an old user. The data after filtering is diverted, the startup log and exposure data are written to the side output stream, the page data is written to the mainstream and the corresponding value is obtained through json, the startup test output stream is written in start, the startup log is removed and all are the page logs, the page logs are written to the mainstream, and then the exposure logs are written to the exposure side output stream.

2. The real-time data warehouse design method based on Doris according to claim 1, characterized in that: The step of importing the Stream Load data after the execution of the consumption task into Doris further includes: creating a final output location of the Stream Load data flow on the real-time computing cluster Flink to the Doris ; The Stream Load data is directly imported into the Doris via Http through caching and batches.

3. The real-time data warehouse design method based on Doris according to claim 1, characterized in that: The first step of performing the logic processing of the secondary business on the real-time data through Flink further includes: setting the secondary business logic of the real-time data into a task management, the task management submitting the adapted task job to the JobManager, the JobManager being responsible for the scheduling and resource allocation of the job, the JobManager assigning the job to the corresponding TaskManager, and after receiving the task job, the TaskManager starting a thread to execute it, and reporting the task status and its own running status to the JobManager; When the task is completed, the JobManager will receive a notification and send the data processed by the secondary business logic to the corresponding task manager.

4. The real-time data warehouse design method based on Doris according to claim 1, characterized in that: After the Stream Load data stream after the execution of the consumption task is imported into Doris, the method further includes: in an offline business scenario, using BrokerLoad to import offline business data from HDFS into Doris; Through SQL, use DolphinScheduler to configure the scheduling task, batch process the offline business data, and then summarize it into a general table.

5. The real-time data warehouse design method based on Doris according to claim 1, characterized in that: After the Stream Load data stream after the execution of the consumption task is imported into Doris, it also includes: the relatively fixed data in the actual business scenario is directly connected to the data service hall, and the data service hall is connected with the Power BI tool and the front-end report.

6. A real-time data warehouse design device based on Doris, characterized in that: include: Routine Load task monitoring setting module: used to import real-time business data into the Kafka queue and put it into the adapted topic tag, then provide at least one consumption task for consuming the data in the topic tag, and set the adapted RoutineLoad task monitoring; Routine Load task monitoring processing module: used for the Routine Load task monitoring to read in real time the offset indicating the location information of the current data consumed in the topic tag; Routine Load task monitoring startup module: used to restart the Routine Load task monitoring if the offset does not increase within a preset time, and continue to execute the consumption task from the offset; Import module: used to import the Stream Load data stream after the execution of the consumption task into Doris; In the import module, after the Stream Load data stream after the execution of the consumption task is imported into Doris, it also includes: finding the real-time data that needs secondary business logic processing, first performing the logic processing of the secondary business on the real-time data through Flink, then caching the data after the logic processing, and finally importing the cached data into Doris by Stream Load; The real-time data is first subjected to the logic processing of the secondary business through Flink, wherein the logic processing of the secondary business further includes: first consuming data from the ods log topic, and then dividing the behavior data in the data into three types according to user behavior: page log, exposure log, and startup log; A Flink state variable is defined as time to filter the data of new users and determine whether they are new users. If they are new users for the first time, the corresponding state is empty and the current time is updated to the state. Otherwise, if the state is not empty, the current user is changed to an old user. The data after filtering is diverted, the startup log and exposure data are written to the side output stream, the page data is written to the mainstream and the corresponding value is obtained through json, the startup test output stream is written in start, the startup log is removed and all are the page logs, the page logs are written to the mainstream, and then the exposure logs are written to the exposure side output stream.

7. A computer device, characterized in that: include: A memory, the memory being used to store a processing program; A processor, wherein when executing the processing program, the processor implements the Doris-based real-time data warehouse design method as described in any one of claims 1 to 5.

8. A readable storage medium, characterized in that: The readable storage medium stores a processing program, and when the processing program is executed by the processor, the Doris-based real-time data warehouse design method according to any one of claims 1 to 5 is implemented.

Images