Paimon-based flow-batch integrated lake and reservoir construction method

Abstract

The invention provides a flow-batch integrated lake and warehouse construction method based on Paimon, and relates to the technical field of data warehouses, and the method comprises the steps: integrating a plurality of pieces of change data through creating Kafka themes, reading the change data from each Kafka theme through Flink operation, and storing the change data in the Flink operation; processing the change data to obtain change information, and outputting the change information to a lake cabin; the lake bin preprocesses the change information to obtain intermediate data, and the change information and the intermediate data are stored in a Paimon, so that the change information and the intermediate data are directly inquired; and the lake bin summarizes and aggregates the intermediate data to obtain primary aggregated data, aggregates the primary aggregated data again to obtain secondary aggregated data, and stores the primary aggregated data and the secondary aggregated data into StarRocks.

Description

Technical Field

[0001] The present application belongs to the field of data warehouse technology, and specifically relates to a method for constructing a data warehouse based on paimon's integrated flow-batch integration. Background Art

[0002] In today's big data era, data has become a crucial asset for business decision-making. With the explosive growth of data volumes, efficiently storing, processing, and analyzing this data has become a key challenge. In recent years, data lakes have garnered widespread attention as a modern data management architecture, enabling the storage of large amounts of data in their original format and supporting flexible data exploration and analysis. However, existing technologies for data lake applications, particularly in real-time big data analysis, face significant limitations.

[0003] While mainstream data lake formats, such as Iceberg, Hudi, and Delta Lake, are well-designed for batch processing, they lack real-time update capabilities. While combining stream computing engines (such as Flink) with OLAP systems (such as Presto or Kylin) can address the issue of real-time data updates to some extent, this architecture typically comes with high storage costs, and OLAP systems have limited ability to preserve long-term historical data. Furthermore, current technical solutions often struggle to simultaneously meet the data timeliness and cost control requirements of diverse business scenarios. Summary of the Invention

[0004] This application provides a method for building a data lake warehouse based on paimon's integrated stream and batch processing to solve the technical problems in the existing technology, such as the lack of real-time update capabilities of the data lake format and the difficulty in balancing stream and batch processing with efficient query.

[0005] The technical solutions adopted in this application are:

[0006] The present application embodiment provides a method for constructing a lake warehouse based on a paimon flow-batch integrated system, including:

[0007] Integrate multiple change data by creating Kafka topics, each of which corresponds to the change data. Use Flink jobs to read the change data from each Kafka topic, process the change data, and obtain change information, which is then output to the lake warehouse.

[0008] The lake warehouse pre-processes the change information to obtain intermediate data, and stores the change information and the intermediate data in Paimon so that the change information and the intermediate data can be directly queried;

[0009] The lake warehouse summarizes and aggregates the intermediate data to obtain primary aggregated data, aggregates the primary aggregated data again to obtain secondary aggregated data, and stores the primary aggregated data and the secondary aggregated data in StarRocks.

[0010] According to one embodiment of the present application, multiple change data are integrated by creating a Kafka topic, and the Kafka topic corresponds to the change data one by one. The change data is read from each Kafka topic through a Flink job, and the change data is processed to obtain change information, which is then output to the lake warehouse. Specifically,

[0011] Monitor the transaction log of the database through the CDC plug-in to capture the change data of the database;

[0012] Temporarily storing different change data through Kafka topics, wherein the Kafka topics correspond to the change data one by one;

[0013] The Kafka topic is read through Flink CDC, and the Kafka topic is parsed to obtain change information, where the change information includes the operation type and data before and after the change.

[0014] According to one embodiment of the present application, the lake warehouse pre-processes the change data to obtain intermediate data, and stores the processed data source and the intermediate data into Paimon so that the processed data source and the intermediate data can be directly queried, specifically:

[0015] The Paimon directly reads the change information in the Kafka topic and stores it in the ODS layer of the lake warehouse;

[0016] Processing the change information through Flink SQL, including data cleaning, conversion, and aggregation operations, to obtain the intermediate data;

[0017] The intermediate data is stored in the DWD layer of the lake warehouse.

[0018] According to one embodiment of the present application, the lake warehouse summarizes and aggregates the intermediate data to obtain aggregated data, specifically:

[0019] Grouping the intermediate data according to one or more dimensions based on business needs or analysis purposes;

[0020] In each of the groups, one or more aggregation functions are applied to calculate the data to obtain the primary aggregated data;

[0021] Each row in the primary aggregate data represents a result of a group, and the primary aggregate data includes an identifier of the group and a result calculated by an aggregate function.

[0022] According to one embodiment of the present application, the first aggregated data is aggregated again to obtain second aggregated data, and the first aggregated data and the second aggregated data are stored in StarRocks, specifically:

[0023] Determine the dimensions and indicators of the secondary aggregated data according to business needs, so as to regroup, calculate and analyze the primary aggregated data to obtain the secondary aggregated data;

[0024] The primary aggregated data and the secondary aggregated data are processed by an ETL tool, and the processed primary aggregated data and the secondary aggregated data are imported into the StarRocks. The ODW layer and the ADS layer of the lake warehouse are built on the StarRocks.

[0025] According to one embodiment of the present application, it also includes: performing data query on the lake warehouse.

[0026] According to an embodiment of the present application, the data query includes a simple query and an OLAP query; the simple query is a direct query of the Paimon table; the OLAP query is a query performed using StarRocks as a query engine.

[0027] In the second aspect, a computer program product containing instructions, when running on a device, enables the device to execute the steps in the paimon-based batch and flow integrated lake warehouse construction method.

[0028] In the third aspect, a computer-readable storage medium stores a program thereon, which, when executed by a processor, implements the steps in the paimon-based integrated flow-batch lake warehouse construction method.

[0029] In a fourth aspect, an electronic device includes a memory, a processor, and a program stored in the memory and executable on the processor. When the processor executes the program, the steps in the paimon-based integrated flow-batch lake warehouse construction method are implemented.

[0030] Due to the adoption of the above technical solution, the beneficial effects achieved by this application are as follows:

[0031] This application integrates multiple change data by creating Kafka topics, which correspond one-to-one with the change data. It uses Flink jobs to read and process the change data from each Kafka topic, and then directly stores the processed change information and the intermediate data obtained by preprocessing into Paimon. This process not only ensures the freshness and consistency of the data, but also provides efficient data query capabilities. By storing change information and intermediate data in Paimon, the system can support real-time updates, low-latency data access, and streaming subscriptions, thus meeting the needs of modern enterprises for data immediacy and flexibility.

[0032] In addition, the lake warehouse further performs summary and aggregation operations on the intermediate data to generate aggregated data, and then performs secondary aggregation based on this to form a deeper level of information extraction. These aggregation results are stored in StarRocks, and its powerful OLAP online query capabilities are used to achieve high-performance data analysis. This architectural design not only allows data to be written quickly and updated in real time, but also allows for the flexible selection of the balance between storage costs and data timeliness based on business needs, ensuring high efficiency in data processing while taking into account the depth and breadth of data analysis. In summary, this application provides an efficient, flexible, and powerful query-performance data processing solution, which greatly enhances the application value of data warehouses in real-time big data analysis scenarios and provides strong technical support for enterprise decision-making support. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0034] Figure 1 A schematic diagram of a process flow of a paimon batch-integrated lake warehouse construction method provided in an embodiment of the present application;

[0035] Figure 2 An embodiment of the present application provides a structural diagram of an electronic device based on the paimon flow-batch integrated lake warehouse construction method. DETAILED DESCRIPTION

[0036] In order to more clearly illustrate the overall concept of the present application, a detailed description is given below in an illustrative manner in conjunction with the accompanying drawings.

[0037] The following description sets forth many specific details to facilitate a thorough understanding of the present application. However, the present application may also be implemented in other ways than those described herein, and therefore, the scope of protection of the present application is not limited by the specific embodiments disclosed below. It should be noted that the embodiments of the present application and the features of each embodiment may be combined with each other unless there is a conflict.

[0038] In this application, unless otherwise expressly specified and limited, a first feature "above" or "below" a second feature may be that the first and second features are in direct contact, or the first and second features are in indirect contact through an intermediate medium. In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in an appropriate manner in any one or more embodiments or examples.

[0039] Example 1

[0040] like Figure 1 As shown, a method for constructing a lake warehouse based on paimon flow-batch integration includes:

[0041] Multiple change data are integrated by creating Kafka topics, each of which corresponds to the change data one by one. The change data are read from each Kafka topic through Flink jobs, and the change data are processed to obtain change information, which is then output to the lake warehouse.

[0042] Specifically, to create Kafka topics: First, create corresponding Kafka topics for different change data sources (such as tables in different databases). Each Kafka topic is dedicated to receiving change data streams from a specific data source, ensuring that data of different types or sources can be managed and processed independently.

[0043] Change Data Integration: Change Data Capture (CDC) tools are used to capture change events from various data sources. These change events include INSERT, UPDATE, and DELETE operation records. Whenever a new change occurs, the corresponding change data is sent to the associated Kafka topic in real time. This enables the effective integration of change information from multiple data sources.

[0044] Flink job reading and processing: Apache Flink, a powerful stream processing engine, plays a central role here. It continuously listens to all relevant Kafka topics and reads change data from them. Leveraging Flink's stream processing capabilities, it can efficiently process large numbers of concurrent data streams, ensuring low latency and high throughput. The Flink job performs necessary preprocessing, such as conversion and cleaning, on the read change data to generate change information that is easy to use.

[0045] Output to the lake warehouse: Change information processed by Flink is output to a data lake warehouse built on Paimon. Paimon, a data lake format that supports both batch and streaming updates, is well-suited to absorbing real-time data streams from Flink and supports real-time data updates and queries. This means that once data enters the lake warehouse, it not only immediately reflects the latest changes but also allows users to directly access the latest data through methods such as SQL, thus meeting the needs of real-time data analysis.

[0046] For example, define the change data source: suppose there are three key data tables in the system: orders, users, and inventory.

[0047] Create Kafka topics: Create a separate Kafka topic for each table to receive changes to that table. For example, create orders_topic, users_topic, and inventory_topic for each of the three tables.

[0048] Configure a CDC tool: Use a CDC tool (such as Debezium) to monitor changes to the aforementioned database tables. Whenever an INSERT, UPDATE, or DELETE operation occurs, the CDC tool captures these change events, converts them into messages in a specific format, and sends them to the corresponding Kafka topics. For example, when a new record is added to the orders table, the change is sent to the orders_topic; if a record is updated in the users table, the corresponding message is sent to the users_topic.

[0049] Use a Flink job to process the change data: Configure the corresponding job on the Flink platform and subscribe to all previously created Kafka topics. The Flink job will continuously read the change data stream from these topics.

[0050] The Flink job performs preprocessing logic on each received change message, which may include steps such as data cleaning and format conversion. For example, for each message in the orders_topic, the Flink job may extract key fields such as order ID, product ID, and purchase time, and convert them into a format suitable for further analysis.

[0051] The processed change information, namely "change information", will be output to the data lake warehouse built based on Paimon.

[0052] Output to the lake warehouse: Change information processed by the Flink job is written to Paimon. As the underlying storage engine, Paimon provides an efficient data update mechanism, allowing records to be quickly added or modified without affecting existing data.

[0053] In this way, once the data is written to Paimon, the latest change status can be immediately accessed through SQL queries or other means, thus supporting real-time data analysis needs.

[0054] Furthermore, anomaly detection can be introduced: by leveraging Flink's powerful stream processing capabilities, anomaly detection logic can be added to the change information processing process to automatically identify abnormal data patterns or business behaviors (such as abnormal surges in transaction volume) and trigger an alarm mechanism.

[0055] Furthermore, performance monitoring can be introduced: by integrating monitoring tools (such as Prometheus and Grafana) to track the message throughput of Kafka topics, the execution status of Flink jobs, and the performance indicators of data writing / querying in Paimon, ensuring the smooth operation of the entire data pipeline.

[0056] The lake warehouse pre-processes the change information to obtain intermediate data, and stores the change information and the intermediate data in Paimon so that the change information and the intermediate data can be directly queried.

[0057] Specifically, receiving change information: First, the lake warehouse receives the change information processed by the Flink job. This change information is data read from the Kafka topic and preliminarily processed, reflecting the latest changes in the source database.

[0058] Preprocessing to generate intermediate data: The lake warehouse performs a series of preprocessing operations on this change information. This may include but is not limited to data cleaning, format conversion, data validation, etc., with the aim of ensuring data quality and consistency.

[0059] Preprocessing may also involve simple calculations or aggregation operations, such as calculating the sum or average of certain fields, to generate intermediate data that is easier to use in subsequent analysis. This intermediate data is a refinement of the original change information, helping to accelerate certain types of queries or provide immediate business insights.

[0060] Storing change information and intermediate data: After processing, both change information and intermediate data are stored in Paimon. As a data lake format that supports both batch and streaming updates, Paimon can efficiently manage and store both types of data.

[0061] By storing change information directly in Paimon, the system maintains the real-time nature of the data, allowing users to immediately access the latest change records.

[0062] At the same time, intermediate data is also saved, so that when more complex data analysis is required, the partially processed data set can be directly used without having to process the original data from scratch each time, thereby improving query efficiency.

[0063] Support direct query: Since Paimon has powerful query capabilities, once the change information and intermediate data are stored, they can be directly accessed through SQL or other query languages.

[0064] Users can flexibly choose to query change information or intermediate data, or a combination of the two, to meet data analysis needs in different scenarios.

[0065] This design not only enables data to be written quickly, but also allows for quick responses to user query requests, achieving low latency and high timeliness in data processing.

[0066] For example, change information reception: Any changes to the order table of the e-commerce platform (such as new order generation, order status update, etc.) are captured by the CDC tool and sent to the orders_topic topic of Kafka.

[0067] The Flink job continuously monitors the orders_topic, reads change information, and performs preliminary processing, such as extracting key fields (order ID, customer ID, order status, etc.).

[0068] Preprocessing generates intermediate data: After receiving the change information output by the Flink job, the lake warehouse begins preprocessing. For example, for each order change record, the lake warehouse can perform the following operations:

[0069] Data cleaning: Remove invalid or incomplete records to ensure that each piece of data contains necessary information.

[0070] Format conversion: Unify date and time fields into a standard format to facilitate subsequent time series analysis.

[0071] Simple aggregation: calculate the number of orders or total amount within a specific time period to generate daily order statistics.

[0072] Through these preprocessing steps, Hucang not only saves the original change information, but also generates intermediate data such as daily order summary tables.

[0073] Store in Paimon: The original change information and the intermediate data obtained after preprocessing are all stored in Paimon. For example:

[0074] Change information: Each change record is stored as a separate data item, retaining a detailed order change history.

[0075] Intermediate data: The daily order summary table is stored as another set of data items, providing higher-level statistical data.

[0076] Paimon supports efficient streaming writing and querying capabilities, so it can maintain good performance even when data is frequently updated.

[0077] Direct query: Users can directly access change information and intermediate data in Paimon through SQL queries.

[0078] For example, if an analyst wants to view the entire order history of a customer, he or she can directly query the change information; if he or she needs to know the total number of orders on a certain day, he or she can directly query the intermediate data.

[0079] This design allows for quick access to both detailed historical records and summary statistics, meeting different types of query requirements.

[0080] Furthermore, data verification can be introduced: data quality check rules can be added to Flink jobs, such as verifying data integrity and consistency, to ensure that only data that meets the standards can be written to the lake warehouse.

[0081] Furthermore, data repair can be introduced: for the problematic data found, corresponding repair strategies can be designed, such as supplementing missing values, correcting incorrect formats, etc., to ensure the accuracy of downstream analysis results.

[0082] The lake warehouse summarizes and aggregates the intermediate data to obtain primary aggregated data, aggregates the primary aggregated data again to obtain secondary aggregated data, and stores the primary aggregated data and the secondary aggregated data in StarRocks.

[0083] Specifically, the summary and primary aggregation of intermediate data: The lake warehouse receives and stores pre-processed intermediate data, which may have undergone preliminary cleaning, formatting, and other processing.

[0084] Based on this, the lake warehouse performs the first aggregation operation. For example, if the intermediate data is the number of orders per hour, then an aggregation operation can be to summarize the number of orders per day to generate the total number of orders per day. This step usually involves simple statistical functions such as SUM and COUNT.

[0085] Through one aggregation, the original fine-grained data is converted into coarser-grained data, which facilitates subsequent higher-level analysis.

[0086] Secondary aggregation of primary aggregated data: Starting from the primary aggregated data, Hucang further performs a secondary aggregation operation. For example, if the primary aggregated data is the total number of daily orders, the secondary aggregation can be to summarize the total number of weekly or monthly orders to generate the total weekly or monthly order volume.

[0087] Secondary aggregations can include more complex calculations, such as compound growth rate, market share changes, etc., to provide deeper business insights.

[0088] Storing aggregated data in StarRocks: The generated primary and secondary aggregated data are stored in StarRocks, a high-performance OLAP database that is particularly suitable for processing complex queries on large data sets.

[0089] By storing this aggregated data in StarRocks, the system can support real-time high-performance queries and meet various data analysis needs, such as report generation and trend analysis.

[0090] StarRocks' MPP architecture and optimization technology ensure fast query performance even when facing PB-level data volumes.

[0091] For example, to read intermediate data from Paimon:

[0092] Assume that a daily order summary table has been stored in Paimon. This table contains information such as the number of orders and total amount for each category each day.

[0093] One-time aggregation: Summarize by category: Hucang first performs the first aggregation on the data in the daily order summary table, for example, summarizing the total number of orders and total amount per day by product category.

[0094] For example, for different categories such as electronics, clothing, and household goods, calculate the total number of orders and the total amount per day. This will produce a table of aggregated data grouped by category and date.

[0095] Secondary aggregation: Monthly summary: Based on the first aggregation data, Hucang performs a second aggregation, for example, summarizing the total number of orders and total amount of each category by month.

[0096] For example, calculate the total number of orders and the total amount of each category in each month, and generate a secondary aggregation data table grouped by month and category.

[0097] Store in StarRocks: Store the primary and secondary aggregated data in StarRocks, a high-performance OLAP database that supports complex queries and fast data retrieval.

[0098] For example, once aggregated data can be used to query the sales of a specific category on a certain day, while twice aggregated data can be used to analyze the monthly sales trend of a certain category.

[0099] Example of once aggregated data: Assume that there is the following once aggregated data:

[0100] On November 27, 2024, the total number of orders in the electronic products category was 500, with a total amount of RMB 100,000.

[0101] On November 27, 2024, the total number of orders in the clothing category was 800, with a total amount of RMB 120,000.

[0102] This data is stored in StarRocks and can be easily queried to obtain sales figures for a specific date and category.

[0103] Secondary aggregate data example: Based on the primary aggregate data, the following secondary aggregate data can be obtained:

[0104] In November 2024, the total number of orders in the electronic products category was 15,000, with a total amount of RMB 3,000,000.

[0105] In November 2024, the total number of orders in the clothing category was 20,000, with a total amount of RMB 4,000,000.

[0106] This data is also stored in StarRocks and can be used to analyze monthly sales trends, such as comparing sales growth in different months.

[0107] Furthermore, enhanced aggregation calculations can be introduced: in addition to basic primary and secondary aggregations, more complex statistical methods can be introduced, such as time series analysis and trend forecasting, to provide deeper insights for decision-making.

[0108] Furthermore, multi-dimensional cross-analysis can be introduced: comprehensive analysis can be conducted by combining multiple dimensions, such as considering the impact of time and region at the same time, to obtain a more comprehensive and detailed data perspective.

[0109] According to one embodiment of the present application, multiple change data are integrated by creating a Kafka topic, and the Kafka topic corresponds to the change data one by one. The change data is read from each Kafka topic through a Flink job, and the change data is processed to obtain change information, which is then output to the lake warehouse. Specifically,

[0110] Monitor the transaction log of the database through the CDC plug-in to capture the change data of the database;

[0111] Temporarily storing different change data through Kafka topics, wherein the Kafka topics correspond to the change data one by one;

[0112] The Kafka topic is read through Flink CDC, and the Kafka topic is parsed to obtain change information, where the change information includes the operation type and data before and after the change.

[0113] Specifically, the CDC plug-in monitors the database transaction log

[0114] Change Data Capture (CDC): Use a CDC plug-in (such as Debezium or Maxwell) to monitor the transaction log of the target database. The CDC plug-in can capture any change operations in the database in real time, including INSERT, UPDATE, and DELETE.

[0115] Transaction log parsing: The CDC plug-in parses the database transaction log and extracts the specific content of each change record, such as the timestamp of the change, the affected data rows, and the change type (insert, update, or delete).

[0116] Kafka topics temporarily store change data

[0117] Kafka topic creation: Create a corresponding Kafka topic for each database table that needs to be monitored. Each Kafka topic is dedicated to storing change data from a specific table.

[0118] Change Data Transfer: When the CDC plugin captures change data, it sends it to the corresponding Kafka topic. This way, change data from different tables is isolated in their own Kafka topics, ensuring data consistency and independence.

[0119] Message format: Messages sent to Kafka topics typically contain detailed information about the change, such as the data state before and after the change, the timestamp of the change, and the type of change.

[0120] Flink CDC reads and parses Kafka topics

[0121] Flink CDC connection: Configure the Flink job to connect to Kafka and subscribe to the corresponding Kafka topic. FlinkCDC is a component of Flink that is specifically used to read CDC data from Kafka.

[0122] Data parsing: Flash CDC reads change data from Kafka topics and parses it. The parsing process includes identifying the change type (INSERT, UPDATE, DELETE) and extracting the data values before and after the change.

[0123] Change information generation: After parsing, the Flink job generates change information. This change information includes not only the original change data, but also the change operation type (such as insert, update, or delete) and the data status before and after the change.

[0124] Output change information to the lake warehouse

[0125] Change information output: The Flink job outputs the processed change information to a lake warehouse built on Paimon. Paimon is a data lake format that supports batch and streaming updates and can efficiently manage and store this change information.

[0126] Data writing: Change information is written to Paimon, making it directly queryable. Paimon supports real-time updates and low-latency data access, ensuring data freshness and consistency.

[0127] Specific examples

[0128] Suppose there is an e-commerce platform whose order table (orders) and user table (users) need to track change data in real time.

[0129] CDC plug-in monitoring: Use the CDC plug-in to monitor the transaction logs of the orders table and users table to capture all change events.

[0130] Kafka topic creation: Create two Kafka topics, orders_topic and users_topic, to store change data of the orders and users tables, respectively.

[0131] Change data transmission: When a new order is generated or the order status is updated in the orders table, the CDC plug-in sends these change data to the orders_topic.

[0132] Similarly, when user information in the users table changes, the change data will be sent to users_topic.

[0133] Flink CDC reads and parses: The Flink job subscribes to orders_topic and users_topic and reads change data from these two topics.

[0134] Flink CDC parses this data, identifies the change type (such as new order generation, order status update, user information modification, etc.), and extracts the data values before and after the change.

[0135] Output change information: The processed change information is output to Paimon, which contains detailed information of each change, such as change time, change type, data values before and after the change, etc.

[0136] According to one embodiment of the present application, the lake warehouse pre-processes the change data to obtain intermediate data, and stores the processed data source and the intermediate data into Paimon so that the processed data source and the intermediate data can be directly queried, specifically:

[0137] The Paimon directly reads the change information in the Kafka topic and stores it in the ODS layer of the lake warehouse;

[0138] Processing the change information through Flink SQL, including data cleaning, conversion, and aggregation operations, to obtain the intermediate data;

[0139] The intermediate data is stored in the DWD layer of the lake warehouse.

[0140] Specifically, Paimon directly reads the change information in the Kafka topic

[0141] Direct reading: Paimon can read change information directly from Kafka topics. This means that Paimon can act as a data sink and obtain change data in Kafka topics in real time.

[0142] Storing in the ODS layer: The read change information is directly stored in the lake warehouse's ODS (Operational Data Store) layer. The ODS layer is the first layer of the data warehouse and is typically used to store raw or near-raw data to ensure data integrity and traceability. In this example, Paimon serves as the storage engine for the ODS layer, responsible for storing this unprocessed change information.

[0143] Flink SQL processes change information

[0144] Data cleaning: Use Flink SQL to clean the change information in the ODS layer. This step includes removing invalid or incomplete records and correcting format errors to ensure data quality.

[0145] Data conversion: Perform necessary data conversions based on business needs. For example, you can standardize date and time fields into a standard format or split certain fields into multiple subfields to facilitate subsequent analysis.

[0146] Data aggregation: Perform preliminary data aggregation operations to generate intermediate data that is easier to use for subsequent analysis. This may include calculating simple statistical indicators such as the total number of orders per day and the total amount.

[0147] Store intermediate data in the DWD layer

[0148] DWD layer storage: Intermediate data generated after Flash SQL processing is stored in the DWD (DataWarehouse Detail) layer of the lake warehouse. The DWD layer is the second layer of the data warehouse and typically contains data that has undergone preliminary processing and aggregation. This data has been structured and standardized to facilitate further analysis and reporting.

[0149] Data organization: Data in the DWD layer can be reasonably organized according to business logic, such as grouping by date, category, or other dimensions to facilitate subsequent query and analysis.

[0150] Specific examples

[0151] Suppose there is an e-commerce platform that needs to track changes in the order table (orders) and perform pre-processing and storage.

[0152] Paimon directly reads the change information in the Kafka topic: Paimon reads the change information from the orders_topic, which contains the insert, update, or delete operations of each order.

[0153] These change information are directly stored in Paimon's ODS layer, forming an original data set containing all change records.

[0154] Flink SQL processes change information: Use Flink SQL to process change information in the ODS layer:

[0155] Data cleaning: Remove invalid order records and ensure that each record contains necessary fields (such as order ID, customer ID, order status, etc.).

[0156] Data conversion: Unify date and time fields into a standard format, for example, convert all date and time fields into the format of YYYY-MM-DD HH:MM:SS.

[0157] Data aggregation: Calculate the total number of orders and total amount per day to generate daily order summary data.

[0158] Storing intermediate data in the DWD layer: The intermediate data (such as daily order summary data) processed by Flink SQL is stored in the DWD layer of Paimon.

[0159] The data in the DWD layer is organized by date and category to facilitate subsequent query and analysis.

[0160] According to one embodiment of the present application, the lake warehouse summarizes and aggregates the intermediate data to obtain aggregated data, specifically:

[0161] Grouping the intermediate data according to one or more dimensions based on business needs or analysis purposes;

[0162] In each of the groups, one or more aggregation functions are applied to calculate the data to obtain the primary aggregated data;

[0163] Each row in the primary aggregate data represents a result of a group, and the primary aggregate data includes an identifier of the group and a result calculated by an aggregate function.

[0164] Specifically, grouping by one or more dimensions

[0165] Select dimensions: Based on specific business needs or analysis objectives, select one or more dimensions to group intermediate data. Dimensions are typically key attributes in the data, such as time, region, or product category.

[0166] Grouping: Groups the intermediate data by the selected dimension. For example, if you want to analyze sales in different regions, you can select "Region" as the grouping dimension; if you want to analyze daily sales, you can select "Date" as the grouping dimension.

[0167] Apply aggregate function calculations

[0168] Aggregate functions: Within each group, one or more aggregate functions are applied to the data. Common aggregate functions include sum (SUM), count (COUNT), average (AVG), maximum (MAX), and minimum (MIN).

[0169] Calculation Results: These aggregation functions can be used to obtain statistical data within each group. For example, within the "Region" dimension, you can calculate the total sales and order quantity for each region; within the "Date" dimension, you can calculate the total sales and order quantity for each day.

[0170] Generate aggregate data once

[0171] Grouped results: Each row in the aggregated data represents the result of a group. Each row contains the group identifier (such as region name, date, etc.) and the result calculated by the aggregate function.

[0172] Data structure: Aggregate data is usually a two-dimensional table, where each row corresponds to a group, and each column represents the group identifier and the corresponding aggregation result. For example, a sales data grouped by region and date may contain the following columns:

[0173] region: region

[0174] date: date

[0175] total_sales: total sales

[0176] order_count: number of orders

[0177] Specific examples

[0178] Suppose there is an e-commerce platform that needs to analyze daily sales and order quantities in different regions.

[0179] Select dimension:

[0180] Select Region and Date as grouping dimensions.

[0181] Grouping operation:

[0182] Group the intermediate data by Region and Date. For example, all orders placed in Beijing on November 27, 2024, will be grouped together.

[0183] Apply aggregate functions to calculate:

[0184] Within each group, the following aggregate functions are applied:

[0185] SUM(sales_amount): Calculates the total sales amount for each group.

[0186] COUNT(order_id): Calculates the number of orders in each group.

[0187] Generate aggregate data once:

[0188] The generated aggregate data may look like this:

[0189] region date total_sales order_count

[0190] Beijing, November 27, 2024, 100,000, 500

[0191] Shanghai, November 27, 2024, 150,000, 700

[0192] Guangzhou, November 27, 2024, 80,000, 400

[0193] In this example, aggregated data shows the total sales and order quantity for each region on a specific date. This data structure makes subsequent analysis more intuitive and efficient, facilitating further data mining and decision support.

[0194] In this way, Hucang is able to extract valuable information from large amounts of intermediate data and generate aggregated data that is easy to understand and use, thereby supporting deeper business analysis and decision-making.

[0195] According to one embodiment of the present application, the first aggregated data is aggregated again to obtain second aggregated data, and the first aggregated data and the second aggregated data are stored in StarRocks, specifically:

[0196] Determine the dimensions and indicators of the secondary aggregated data according to business needs, so as to regroup, calculate and analyze the primary aggregated data to obtain the secondary aggregated data;

[0197] The primary aggregated data and the secondary aggregated data are processed by an ETL tool, and the processed primary aggregated data and the secondary aggregated data are imported into the StarRocks. The ODW layer and the ADS layer of the lake warehouse are built on the StarRocks.

[0198] Specifically, determine the dimensions and indicators of secondary aggregated data

[0199] Business Needs Analysis: First, based on your specific business needs or analysis objectives, identify the specific dimensions and metrics for the secondary aggregated data you need to generate. For example, if you need to analyze monthly sales trends for different product categories, you can select "Month" and "Product Category" as dimensions, and sales volume, order quantity, etc. as metrics.

[0200] Regrouping: Regroup the aggregated data according to the new dimension. This step may involve grouping the aggregated data according to the new dimension (such as month, product category).

[0201] Calculate and analyze secondary aggregate data

[0202] Apply aggregation functions: Within each new group, apply one or more aggregation functions to the data to perform calculations. For example, calculate the total sales, average sales, maximum sales, etc. for each product category in each month.

[0203] Generate secondary aggregate data: Through the above steps, generate secondary aggregate data. Each row of data represents a new grouping result, including the group identifier (such as month, product category) and the result calculated by the aggregation function.

[0204] Use ETL tools to process and import StarRocks

[0205] ETL processing: Use ETL (Extract, Transform, Load) tools to process primary and secondary aggregated data. ETL tools can help cleanse data, convert formats, perform complex transformation logic, and ultimately load the data into the target system.

[0206] Importing into StarRocks: The primary and secondary aggregated data processed by the ETL tool are imported into StarRocks. StarRocks is a high-performance OLAP (Online Analytical Processing) database that supports real-time query and analysis of large-scale data.

[0207] Hucang's ODW layer and ADS layer are built on StarRocks

[0208] ODW Layer: The ODW (Operational Data Warehouse) layer is the third layer in the data warehouse and typically contains further processed and aggregated data. In this example, the ODW layer is built on StarRocks and stores primary aggregated data. This data is already highly structured and suitable for more in-depth analysis.

[0209] ADS Layer: The ADS (Application Data Store) layer is the top layer of the data warehouse and typically contains highly aggregated and optimized data designed for specific applications or reporting. In this example, the ADS layer is also built on StarRocks and stores secondary aggregated data. This data can be directly used to generate reports, dashboards, and other advanced analytical applications.

[0210] Specific examples

[0211] Suppose there is an e-commerce platform that needs to analyze the monthly sales of different product categories.

[0212] Determine the dimensions and indicators of secondary aggregated data:

[0213] Dimensions: Month, Product Category.

[0214] Metrics: Total sales, number of orders.

[0215] Regroup, calculate and analyze:

[0216] Group the aggregated data by "month" and "product category".

[0217] Within each group, the following aggregate functions are applied:

[0218] SUM(total_sales): Calculates the total sales for each group.

[0219] COUNT(order_count): Calculates the number of orders in each group.

[0220] Generate secondary aggregate data:

[0221] The resulting secondary aggregated data might look like this:

[0222]

[0223] Use ETL tools to process and import into StarRocks:

[0224] Use ETL tools (such as Apache Flink, Apache Spark, etc.) to process the primary and secondary aggregated data, including data cleaning and format conversion.

[0225] The processed data is imported into StarRocks and stored in the ODW layer and ADS layer respectively.

[0226] The ODW and ADS layers of Hucang are built on StarRocks:

[0227] The ODW layer stores aggregated data to support deeper analysis.

[0228] The ADS layer stores secondary aggregated data and is directly used to generate reports and dashboards.

[0229] According to one embodiment of the present application, it also includes: performing data query on the lake warehouse.

[0230] Specifically, the purpose of data query

[0231] Business decision support: By querying the data in the lake warehouse, enterprises can obtain key business indicators and trends to make more informed decisions.

[0232] Report generation: Generate various business reports such as sales reports and financial reports regularly or on demand to provide management with the latest business overview.

[0233] Real-time monitoring: Real-time query can help monitor business operation status, detect abnormal situations in time and take corresponding measures.

[0234] Data analysis: Supports complex analytical tasks such as market trend analysis and customer behavior analysis, helping companies gain a deeper understanding of the market and customer needs.

[0235] Data query level

[0236] ODS layer query: Directly query the raw or near-raw data in the ODS (Operational Data Store) layer. This data typically contains detailed transaction records and logs, and is suitable for scenarios requiring fine-grained data.

[0237] DWD layer query: Query the intermediate data in the DWD (Data Warehouse Detail) layer. This data has been preliminarily cleaned and transformed, making it suitable for more structured analysis.

[0238] ODW layer query: Query the primary aggregated data in the ODW (Operational Data Warehouse) layer. This data has been further aggregated and processed, making it suitable for higher-level analysis.

[0239] ADS layer query: Query secondary aggregated data in the ADS (Application Data Store) layer. This highly aggregated data is suitable for generating reports and dashboards, supporting advanced analytical applications.

[0240] Query method

[0241] SQL query: Use standard SQL language for query. SQL is a powerful data query language that supports complex data operations and analysis functions.

[0242] Visualization tools: Use BI (Business Intelligence) tools such as Tableau and Power BI to create complex queries and reports through a drag-and-drop interface.

[0243] API interface: Access data in the lake warehouse through the API interface, support applications to directly call data services, and realize automated query and integration.

[0244] Custom queries: Write custom queries based on specific needs to meet complex data analysis requirements.

[0245] Query performance optimization

[0246] Indexing and Partitioning: Properly set indexing and partitioning strategies to improve query performance. For example, index frequently queried fields and partition data by time or region.

[0247] Caching mechanism: Use caching technology to cache commonly used query results, reduce repeated calculations, and speed up response.

[0248] Data preprocessing: Pre-calculate and store some commonly used data sets or views to reduce the amount of computation required during querying.

[0249] Resource management: Rationally allocate computing resources to ensure fast response times for query requests, especially in high-concurrency situations.

[0250] Application of query results

[0251] Report generation: Export query results as reports for internal or external use.

[0252] Dashboard display: Use visualization tools to create dynamic dashboards that intuitively display key indicators and trends.

[0253] Data export: Export query results to files (such as CSV, Excel) for further processing or sharing.

[0254] Data-driven decision-making: Develop business strategies based on query results, adjust operational directions, and optimize products and services.

[0255] According to an embodiment of the present application, the data query includes a simple query and an OLAP query; the simple query is a direct query of the Paimon table; the OLAP query is a query performed using StarRocks as a query engine.

[0256] Specifically, a simple query

[0257] Definition: A simple query is a query operation that retrieves data directly from a Paimon table. This type of query is typically used to retrieve specific records or perform simple filtering, sorting, and other operations.

[0258] Application scenarios: Suitable for situations where quick access to single or multiple records is required, or when the query conditions are relatively simple and do not require complex aggregation calculations.

[0259] Features:

[0260] Real-time: Paimon supports low-latency data access and can provide near-real-time data query capabilities.

[0261] Flexibility: SQL statements can be used directly for queries, and standard SQL syntax is supported, making it easy for users to write and understand.

[0262] Performance: For small-scale data sets or simple query requests, Paimon can provide high query performance.

[0263] OLAP query

[0264] Definition: OLAP (Online Analytical Processing) queries refer to complex data analysis queries performed using StarRocks as the query engine. These queries typically involve extensive data processing and complex aggregate calculations.

[0265] Application scenarios: Suitable for situations that require multi-dimensional analysis, complex aggregation, and large data processing, such as generating reports, dashboards, and trend analysis.

[0266] Features:

[0267] High performance: StarRocks is an MPP (massively parallel processing) architecture database designed specifically for OLAP. It can efficiently process PB-level data and support highly concurrent queries.

[0268] Complex Calculations: Supports complex SQL queries, including multi-table joins, window functions, subqueries, and other advanced features, enabling in-depth data analysis.

[0269] Real-time: Although mainly oriented towards batch processing, StarRocks also supports real-time data loading and querying, and can complete the processing of large amounts of data in a short time.

[0270] Scalability: It is easy to expand horizontally, and the processing capacity and query performance of the overall system can be improved by adding nodes.

[0271] Specific examples

[0272] Simple query

[0273] Suppose you need to query the order information of a specific user:

[0274] Query statement: SELECT * FROM orders WHERE user_id = '12345'

[0275] Execution process: Execute this query directly in the Paimon table to return all order records that meet the conditions.

[0276] Result: Returns all order details for user ID 12345.

[0277] OLAP query

[0278] Suppose you need to analyze the monthly sales of each region over the past year:

[0279] Query statement: SELECT region,DATE_TRUNC('month',order_date)AS month,SUM(order_amount)AS total_sales FROM orders WHERE order_date>=

[0280] '2023-01-01'GROUP BY region,DATE_TRUNC('month',order_date)

[0281] Execution process: The query is executed through the StarRocks query engine, which groups and aggregates a large amount of order data.

[0282] Result: Returns the total sales for each region for each month over the past year.

[0283] Furthermore, the present application discloses an electronic device. Figure 2 It is a structural diagram of an electronic device 20 shown according to an exemplary embodiment. The content in the figure cannot be regarded as any limitation on the scope of use of this application. The electronic device 20 may specifically include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input and output interface 25 and a communication bus 26. The memory 22 is used to store a computer program, and the computer program is loaded and executed by the processor 21 to implement the relevant steps in the paimon flow-batch integrated lake warehouse construction method disclosed in any of the aforementioned embodiments.

[0284] In addition, the electronic device 20 in this embodiment may specifically be an electronic computer.

[0285] In this embodiment, the power supply 23 is used to provide working voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and the external device, and the communication protocol it follows is any communication protocol that can be applied to the technical solution of this application, and is not specifically limited here; the input and output interface 25 is used to obtain external input data or output data to the outside world, and its specific interface type can be selected according to the specific application needs, and is not specifically limited here. In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, a disk or an optical disk, etc. The resources stored thereon can include an operating system 221, a computer program 222, etc., and the storage method can be temporary storage or permanent storage. Among them, the operating system 221 is used to manage and control each hardware device and computer program 222 on the electronic device 20, which can be Windows Server, Netware, Unix, Linux, etc. In addition to including a computer program that can be used to complete the paimon flow-batch integrated lake warehouse construction method disclosed by the electronic device 20 and executed by the electronic device 20 as disclosed in any of the aforementioned embodiments, the computer program 222 can further include a computer program that can be used to complete other specific tasks.

[0286] Furthermore, the present application also discloses a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by the processor, the aforementioned disclosed method for constructing a lake warehouse based on the paimon flow-batch integration is implemented. For the specific steps of the method, reference may be made to the corresponding contents disclosed in the aforementioned embodiments, which will not be repeated here. The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referred to each other. For the device disclosed in the embodiment, since it corresponds to the method disclosed in the embodiment, the description is relatively simple, and the relevant parts can be referred to the method part description. Professionals can further realize that the units and algorithm steps of the various examples described in combination with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two,

[0287] To clearly illustrate the interchangeability of hardware and software, the above descriptions have generally described the components and steps of each example by function. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this application.

[0288] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein may be implemented directly using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), internal memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0289] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or device comprising the element.

[0290] Furthermore, the present application also discloses a computer program product containing instructions, which, when run on a device, enables the device to execute the steps in the paimon batch-integrated lake warehouse construction method.

[0291] Anything not described in this application can be achieved by adopting or drawing on existing technologies.

[0292] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other. Each embodiment focuses on the differences from other embodiments.

[0293] The foregoing is merely an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should all be included within the scope of the claims of the present application.

Claims

1. A method for constructing a lake warehouse based on paimon flow-batch integration, characterized in that: include: Integrate multiple change data by creating Kafka topics, each of which corresponds to the change data. Use Flink jobs to read the change data from each Kafka topic, process the change data, and obtain change information, which is then output to the lake warehouse. The lake warehouse pre-processes the change information to obtain intermediate data, and stores the change information and the intermediate data in Paimon so that the change information and the intermediate data can be directly queried; The lake warehouse summarizes and aggregates the intermediate data to obtain primary aggregated data, aggregates the primary aggregated data again to obtain secondary aggregated data, and stores the primary aggregated data and the secondary aggregated data in StarRocks.

2. The method according to claim 1, characterized in that The multiple change data are integrated by creating a Kafka topic, and the Kafka topic corresponds to the change data one by one. The change data is read from each Kafka topic through a Flink job, and the change data is processed to obtain the change information, which is then output to the lake warehouse. Specifically: Monitor the transaction log of the database through the CDC plug-in to capture the change data of the database; Temporarily storing different change data through Kafka topics, wherein the Kafka topics correspond to the change data one by one; The Kafka topic is read through Flink CDC, and the Kafka topic is parsed to obtain change information, where the change information includes the operation type and data before and after the change.

3. The method according to claim 1, characterized in that The lake warehouse pre-processes the change data to obtain intermediate data, and stores the processed data source and the intermediate data into Paimon so that the processed data source and the intermediate data can be directly queried, specifically: The Paimon directly reads the change information in the Kafka topic and stores it in the ODS layer of the lake warehouse; Processing the change information through Flink SQL, including data cleaning, conversion, and aggregation operations, to obtain the intermediate data; The intermediate data is stored in the DWD layer of the lake warehouse.

4. The method according to claim 1, wherein The lake warehouse summarizes and aggregates the intermediate data to obtain aggregated data, specifically: Grouping the intermediate data according to one or more dimensions based on business needs or analysis purposes; In each of the groups, one or more aggregation functions are applied to calculate the data to obtain the primary aggregated data; Each row in the primary aggregate data represents a result of a group, and the primary aggregate data includes an identifier of the group and a result calculated by an aggregate function.

5. The method according to claim 1, wherein The first aggregated data is aggregated again to obtain second aggregated data, and the first aggregated data and the second aggregated data are stored in StarRocks, specifically: Determine the dimensions and indicators of the secondary aggregated data according to business needs, so as to regroup, calculate and analyze the primary aggregated data to obtain the secondary aggregated data; The primary aggregated data and the secondary aggregated data are processed by an ETL tool, and the processed primary aggregated data and the secondary aggregated data are imported into the StarRocks. The ODW layer and the ADS layer of the lake warehouse are built on the StarRocks.

6. The method according to claim 1, characterized in that Also includes: Perform data query on the lake warehouse.

7. The method according to claim 6, characterized in that The data query includes simple query and OLAP query; the simple query is to directly query the Paimon table; the OLAP query is to query using StarRocks as a query engine.

8. A computer program product comprising instructions, which, when executed on a device, is characterized in that: The device is enabled to execute the steps in the paimon flow-batch integrated lake warehouse construction method as described in any one of claims 1-7.

9. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by the processor, the steps in the paimon flow-batch integrated lake warehouse construction method are implemented as described in any one of claims 1-7.

10. An electronic device comprising a memory, a processor, and a program stored in the memory and executable on the processor, wherein: When the processor executes the program, it implements the steps in the paimon flow-batch integrated lake warehouse construction method as described in any one of claims 1-7.

Images