A data management system and method integrating a warehouse and a lake

Abstract

The application provides a data management system and method of warehouse lake integration. The system comprises: an operation management layer, which provides a visual interface for a user to configure a database type and a warehouse lake performance threshold, the database type being one of a data warehouse, a data lake and warehouse lake integration, and the warehouse lake performance including a query rate per second, a processing capacity per second and a data volume; a data access layer, which provides a data interface for accessing data and performing data standardization processing according to the configuration; and a basic layer, which adopts a corresponding data management component according to the current database type. The operation management layer further comprises a warehouse lake performance configuration unit configured to judge the current warehouse lake performance, and if the current warehouse lake performance exceeds the threshold of the warehouse lake performance, the corresponding prompt is outputted, and the current data management component and the database type are switched. The above scheme provides a data storage architecture type of a data management platform selected flexibly based on a strategy mode, thereby improving the accuracy and stability of data.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of data management, and particularly relates to a warehouse-lake integrated data management system and method. BACKGROUND

[0002] With the advent of the interconnection big data era, in the digital transformation, the application demand has a burst growth, and it is required to combine the technology with the existing business scene or the innovative business scene to quickly form visible and displayable business results. However, there are various software business systems, and the data sources and storages are different, and the demand frequently changes, which leads to complex system data and slow database analysis speed. The AP and CP library design based on the CAP theory of data storage requires a large amount of learning cost, thereby causing the data analysis efficiency to be blocked. In the big data era, the data grows in a geometric progression, and the scale, generation speed and complexity of the data are more and more complicated, and the efficiency requirement of data analysis is also higher and higher, and there are few integration schemes in the prior art that can meet the efficiency and storage planning. In order to collect, store and analyze big data, there are few existing solutions to solve the data quality problem, data storage planning and standard, and data standard in big data, and system architects need to integrate according to experience, which has no replicability and cannot be highly extended.

[0003] Under this technical background, data warehouse and data lake are two representatives of supporting big data components and are also widely used data bases. Among them, the data warehouse has been developed for a long time, and part of the data is discrete, metadata and data asset management. Generally, the standard data after processing is stored, and is mostly used for data analysis; the data lake is used for storing various structured, semi-structured and unstructured data, and the storage capacity is greater than that of the data warehouse. The above two mainstream data storage architectures have high difficulty in operation and high learning cost. Under the traditional chimney IT construction mode, various information systems are independently purchased or self-built, and many data islands are formed inside. The data foundation of most enterprises is still weak, and there are problems such as chaotic data standards, uneven data quality levels, poor usability and the like. There are many data types, a large amount of unstructured data, video data, time series data, GIS data and the like, leading to difficult data processing.

[0004] In existing technical solutions, if the underlying database selection is incorrect for big data scenarios, it is difficult to migrate to other databases later. If the initial selection cannot meet the performance requirements of big data, changing the database later is extremely difficult. Currently, most data organization is based on relational databases, which have unsatisfactory performance when dealing with massive amounts of data, are extremely difficult to scale, and have syntax incompatibility issues, requiring changes to the application layer, resulting in a situation where changes in one place lead to changes everywhere. At the same time, there is a lack of control over the storage standards and planning of the database, and a lack of data quality analysis of the entire database, resulting in the database storing some garbage data, thus affecting the stability of the entire application layer system. The existing architecture also has a certain degree of data redundancy, with raw data stored in a data lake and cleaned data stored in a data warehouse, resulting in excessive data storage, excessive operation and maintenance costs, and inability to adapt to system robustness. Furthermore, with the increasing informatization of various industries, enterprises are experiencing a surge in data volume and diversity, demanding higher real-time performance. Traditional databases cannot meet the demands of large-scale, rapid data analysis. To quickly adapt to current business needs, data warehouses and data lakes are often introduced. However, existing data warehouse and data lake platforms generally offer poor performance and lack visual management interfaces, requiring significant R&D expertise. In big data operations, the data volume is enormous, involving extensive data flow, such as data import and export from various databases, and data requests from various business domains. Under these complex circumstances, existing data processing platforms often suffer from chaotic data management, instability, and low efficiency, failing to achieve "short, efficient, and fast" data development. Summary of the Invention

[0005] To address the aforementioned problems, the first aspect of this application proposes an integrated data management system for warehouses and lakes, comprising:

[0006] The operations management layer provides a visual interface for users to configure metadata, which includes database type and warehousing / lake performance thresholds. Databases are built based on the metadata, and the database type can be one of data warehouse, data lake, or integrated warehousing / lake. Warehouse / lake performance includes queries per second, processing volume per second, and data volume.

[0007] The data access layer provides a data interface for accessing data and performs data standardization processing according to the configuration.

[0008] The base layer uses the corresponding data management component based on the current database type. The data management component is one of the following: data warehouse component, data lake component, and integrated data warehouse and lake component.

[0009] The operation management layer further includes a warehouse performance configuration unit configured to judge the current warehouse performance, and if the current warehouse performance exceeds a threshold value of the warehouse performance, output a corresponding prompt, switch the current data management component and database type, and migrate the stored historical data before the switch to the database after the switch or create a calling interface to call the stored historical data before the switch.

[0010] Further, the data of the query field configured by the warehouse lake integrated component is stored in the data warehouse, and all data is stored in the data lake.

[0011] Further, the database is deployed based on an ansible script.

[0012] Further, when the data management component is switched from the data warehouse component to the data lake component, the historical data is migrated to the data lake, or the historical data is retained in the data warehouse, and an RPC calling interface is provided for calling the historical data.

[0013] Further, when the data management component is switched from the data lake component to the warehouse lake integrated architecture, the query field in the historical data is migrated to the data warehouse architecture, or the historical data is retained in the data lake architecture, and an RPC calling interface is provided for calling the historical data.

[0014] Further, the base layer further includes a DataX data migration interface for historical data migration.

[0015] Further, the data access layer accesses data based on Flink Table&SQL.

[0016] Further, the operation management layer provides a visual interface for writing UDF functions and Flink SQL syntax required by Flink Table&SQL.

[0017] Further, the data access layer provides an RPC interface for application end data access.

[0018] The second aspect of the application provides a warehouse lake integrated data management method, which applies the data management system of any one of the first aspect to manage data.

[0019] The above scheme provides a user with a metadata management specification, a high standardization degree, and a strong usability visual platform, can flexibly select the data storage architecture type of the current data management platform based on the strategy mode, thereby improving the accuracy and stability of the data, and through the setting of the visual management interface, a global data management view is provided, and the technical stack research work burden of the technical personnel on the underlying warehouse lake is reduced. BRIEF DESCRIPTION OF DRAWINGS

[0020] The accompanying drawings are used to further understand the present application. The elements of the drawings are not necessarily in proportion to each other. For ease of description, only the parts related to the present application are shown in the drawings.

[0021] Figure 1 A step schematic diagram of the data management method of the warehouse-lake integration in an embodiment of the present application is shown in FIG. 1.

[0022] Figure 2 A schematic diagram of the data management system architecture of the warehouse-lake integration in another embodiment of the present application is shown in FIG. 2. DETAILED DESCRIPTION

[0023] The present application will be further described in detail below in conjunction with the drawings and embodiments. It can be understood that the specific embodiments described herein are only used to explain the related application, and not to limit the application.

[0024] Figure 1 A step schematic diagram of the data management system construction of the warehouse-lake integration in an embodiment of the present application is shown in FIG. 3, which specifically includes:

[0025] S1, configuring metadata, including table name, database type and warehouse performance threshold. Specifically, the steps include:

[0026] S11, based on the visual interface, inputting the table name, and configuring the field attribute;

[0027] Table 1 is a field attribute list configured by a user in an embodiment:

[0028] Table 1 Configured Field Attribute

[0029]

[0030]

[0031] The field attribute is specified as follows:

[0032] The field name is an English field, which is used for subsequent field creation;

[0033] The field type is a java standard data type, including string, int, float, double, date, long, boolean, byte and json;

[0034] Whether to query is the basis for subsequent verification of the query standard efficiency of the database; specifically, for the fields with the query field as yes, the background performs query based on the field at regular intervals, and if the query time exceeds the specified time, such as 5s, the current database is defined as not meeting the performance requirements in the subsequent, and the user is recommended to replace the data storage architecture type, such as data lake or warehouse-lake integration, or to delete historical data to maintain the efficiency stability and performance of the database.

[0035] Whether the update field is the basis for updating the field data in the subsequent data access process;

[0036] Whether the partition field is used to build data partition for subsequent data warehouse and data lake, which facilitates the improvement of subsequent data query efficiency; specifically, assuming that the data warehouse is selected, the partition field is the creation time, then the partition script of the database template is established based on the database corresponding to the data warehouse, which facilitates the improvement of subsequent query efficiency; similarly, assuming that the data lake is selected, the data lake will establish a partition creation statement based on the partition configuration field, which facilitates the optimization of subsequent query speed and improves the data query efficiency.

[0037] Whether the index field is used to build data index for subsequent data warehouse and data lake, which facilitates the improvement of subsequent data query efficiency;

[0038] Field standard regular is the regular check of data in subsequent data access, to reduce dirty data into the warehouse;

[0039] Whether the quality detection field is the standard for providing data quality display in subsequent data operation;

[0040] Whether the deduplication field is the basis for judging whether the data is repeated in the subsequent data access process.

[0041] S12, warehouse lake selection, determine the database type;

[0042] After S11 configuration is completed, the database type is selected as one of data warehouse, data lake and warehouse lake integration; if the user selects the data warehouse, the current database type adopts the data management component corresponding to the data warehouse, such as mongo, doris, mysql, sql server, pg, es, solr, clickhouse, etc.; if the user selects the data lake, the current database type adopts the data management component corresponding to the data lake, such as apache hudi, Delta Lake, Apache Iceberg, etc.; if the user selects the warehouse lake integration, according to the configured field attribute, the query field is stored in the data warehouse, and all fields are stored in the data lake.

[0043] S13, warehouse lake performance configuration, determine the warehouse lake performance threshold, wherein the warehouse lake performance includes query rate per second (QPS), transaction processing capacity per second (TPS) and data volume, which is used to judge whether the performance of the current database meets the corresponding requirements in subsequent system running. The warehouse lake performance threshold can be configured when the database type is selected as the data warehouse.

[0044] S2, build database, specifically including:

[0045] S21, according to the configuration of S1, generate an ansible script and a deployment package of a corresponding data warehouse or data lake, automatically deploy the data warehouse or data lake, and the ansible script automatically judges whether a data warehouse or data lake of a corresponding version exists, and if the data warehouse or data lake exists, the deployment is not continued;

[0046] S22, according to the configuration of S1, deploy an rpc program of a driving version corresponding to the data warehouse or data lake, to facilitate subsequent application and direct calling;

[0047] S23, according to the configuration of S1, construct a table structure and a corresponding field of a database, if the table structure exists, prompt a user whether to generate based on the table, otherwise, prompt the user to re-input a table name, if the field of the database exists, judge whether the field types are consistent, if the field types are not consistent, prompt the user to modify, verify whether the table is correct after creation, and at the same time, construct a partition strategy, if a partition field configured by S1 is of a date type or an integer type, perform sharding based on the field, and if the partition field is of a string type, perform sharding based on a hash bucketing strategy.

[0048] In a specific embodiment, data access of the system is performed based on a Flink Table&SQL interface, and specific steps include:

[0049] S31, based on a visual interface, judge whether a user-defined (UDF) function exists, if the UDF function does not exist, prompt a user to write the UDF function online and save;

[0050] S32, perform data access through the Flink Table&SQL interface, write a Flink SQL syntax based on the visual interface and input a corresponding UDF function to perform field data conversion;

[0051] S33, perform data access based on the Flink SQL syntax written by a user, in the process of access, perform corresponding data standardization processing based on field attributes configured by the user, for example, judge whether data is duplicated, if the data is duplicated, do not record; judge whether data types are matched, if the data types are not matched, convert the data types, if conversion fails, throw an error; judge a data field length, if the length is exceeded, perform truncation; judge whether a data format satisfies a data format field regular expression, if the data format does not satisfy the data format field regular expression, empty the data corresponding to the field.

[0052] In a specific embodiment, data access of the system is performed based on an RPC form. An application end performs standard SQL insertion or modification or deletion based on calling an RPC protocol, in the process of performing the standard SQL execution, similar to the foregoing embodiment, data access of the system is performed based on the Flink Table&SQL interface, and corresponding processing is performed based on field attributes configured by a user, and standard data is uniformly formatted and stored.

[0053] In another specific embodiment, the data management system integrated with the warehouse lake includes a warehouse lake performance configuration unit for determining the warehouse lake performance of the system at regular intervals, such as daily, the warehouse lake performance including the query per second (QPS), the transaction per second (TPS), and the data volume. If any of the warehouse lake performance reaches the maximum threshold, the user is prompted through the visualization interface that the current data management system cannot meet the corresponding warehouse lake performance, and whether to switch the type of data storage architecture or to perform historical data cleaning and the like. According to the feedback of the user to the prompt, the system switches the current data management component and the database type.

[0054] Specifically, when the user selects to switch the current database type from the data warehouse to the data lake, the process of constructing the data management system is performed again, the corresponding data lake type is configured, and the data lake is deployed based on the ansible script and the deployment package of the corresponding data lake. If the data lake already exists, it does not need to be deployed. The user can select whether to migrate the historical data into the data lake. After the data lake is deployed, the corresponding settings are performed according to the selection of the user. Specifically, if the user selects to migrate the historical data into the data lake, the historical data is migrated into the data lake through the DataX script, and the historical data in the original data warehouse is deleted; if the user selects not to migrate the historical data into the data lake, the historical data is called in the form of RPC, and the new data is recorded into the data lake through RPC.

[0055] Similarly, when the user selects to switch the current database type from the data lake to the warehouse lake integration, the data warehouse is deployed based on the ansible script and the deployment package of the corresponding data warehouse. If the data warehouse already exists, it does not need to be deployed. The user can select whether to migrate the query field in the historical data into the data warehouse. After the data warehouse is deployed, the corresponding settings are performed according to the selection of the user. Specifically, if the user selects to migrate the query field in the historical data into the data warehouse, the query field in the historical data is migrated into the data warehouse through the DataX script, and the corresponding data in the original data warehouse is deleted; if the user selects not to migrate, the historical data in the data lake is called in the form of RPC, and the new data is recorded through RPC. When recording, based on the switched warehouse lake integration architecture, the configured query field data is stored in the data warehouse according to the field properties configured by the user, and the data of all fields is stored in the data lake. After the switching is completed, the next query of RPC will query the data through the data warehouse, and then call the data of the data lake to return.

[0056] The operation and maintenance management interface synchronously marks the switching information of the type of data storage architecture each time.

[0057] The switching from the data warehouse architecture to the warehouse lake integration architecture is also similar to the above embodiment.

[0058] In specific embodiments, the warehouse-lake integrated data management system includes an operation management layer that displays database data volume, QPS, TPS, and table structure corresponding to each metadata, and data cloud query through a visual interface, for overall planning of the entire warehouse-lake integrated data storage infrastructure.

[0059] Figure 2 For another specific embodiment of the warehouse-lake integrated data management system of the application, the schematic diagram of the system architecture specifically includes:

[0060] The operation management layer provides a visual interface for managing metadata and data view, UDF function writing, and data quality control, etc. Users can edit basic configurations based on the visual interface, generate ansible scripts, and automatically deploy data warehouse and data lake.

[0061] The data access layer provides data interfaces for accessing various data of different protocols, such as data of file protocol, http protocol, database protocol, and mq protocol, and unifies the data access portal to convert data of different rules into unified format data for easy data cleaning and storage in the lower layer.

[0062] The basic layer accesses and applies data through standard SQL, and is used for unified data flow, data cleaning, and standard rules of data storage. Only data rule configuration is needed in the operation management layer, and the remaining data will be transferred to the basic layer according to the access layer. The basic layer serves as the total access portal for data processing and standardized storage execution. It also performs database storage architecture conversion between data warehouse architecture, data lake architecture, or warehouse-lake integrated architecture. The warehouse-lake logical layer in the figure is the warehouse-lake integrated architecture.

[0063] The method of the application has been applied to data infrastructure in various industries, and can automatically select the underlying data warehouse, data lake, or warehouse-lake integration based on the visual interface strategy mode. Metadata and data view are managed through the management platform, and data warehouse and data lake are automatically deployed according to the configuration of the management platform. Data is accessed through standard SQL, and the underlying data storage architecture is not perceived. UDF functions are encapsulated, and UDF functions are written based on the visual interface. The underlying automatically adapts to the corresponding data warehouse and data lake. The performance parameters such as QPS, TPS, and data volume of the current database are tested to determine whether the current performance meets the requirements. If it does not meet the requirements, the strategy mode configured based on the visual management interface can switch between data warehouse, data lake, and warehouse-lake integrated architecture. The system robustness is improved, the secondary rework of specific field requirements is reduced, and the efficiency is significantly improved compared with the commonly used data storage systems in the current market.

[0064] The overall storage specification architecture of the data warehouse and the data lake can facilitate small and medium-sized enterprises to quickly select and quickly build the underlying architecture of data, thereby reducing subsequent operation cost, and is also suitable for enterprise services with large data volume. Through the solution of the application, the optimized construction of the data storage platform is realized, a storage platform with strong ease of use is provided for users, so that data is efficiently analyzed. Through the method, flexible selection of the data warehouse, the data lake and the integrated architecture of the warehouse and the lake can be realized, thereby reducing the technical complexity of the system, enabling the R&D personnel to pay more attention to the data itself rather than the underlying data storage framework, realizing fine management, improving data stability and improving efficiency.

[0065] Although the content of the application is specifically shown and introduced in combination with the preferred embodiments, those skilled in the art should understand that various changes can be made to the application in form and details without departing from the spirit and scope of the application defined in the appended claims, without creative labor.

Claims

1. A data management system integrated with a reservoir lake, characterized in that, The data management system comprises: an operation management layer providing a visual interface for a user to configure metadata, the metadata including database types and warehouse performance thresholds, and a database is built according to the metadata, the database type being one of a data warehouse, a data lake and a warehouse-lake integration, and the warehouse performance including query per second, processing per second and data volume; a data access layer providing a data interface for accessing data and performing data standardization processing according to the configuration; a basic layer adopting a corresponding data management component according to the current database type, the data management component being one of a data warehouse component, a data lake component and a warehouse-lake integration component; wherein the operation management layer further comprises a warehouse performance configuration unit configured to judge the current warehouse performance, and if the current warehouse performance exceeds the threshold of the warehouse performance, output a corresponding prompt and switch the current data management component and the database type, and migrate the stored historical data before the switch to the database after the switch or create a calling interface to call the stored historical data before the switch.

2. The data management system of claim 1, wherein, The warehouse-lake integration component stores data configured as query fields in the data warehouse and stores all data in the data lake.

3. The data management system of claim 1, wherein, The database is built based on an ansible script.

4. The data management system of claim 1, wherein, When the data management component is switched from the data warehouse component to the data lake component, the historical data is migrated into the data lake, or the historical data is retained in the data warehouse, and an RPC calling interface is provided for calling the historical data.

5. The integrated data management system of claim 1, wherein, When the data management component is switched from the data lake component to the warehouse-lake integration architecture, the query fields in the historical data are migrated into the data warehouse architecture, or the historical data is retained in the data lake architecture, and an RPC calling interface is provided for calling the historical data.

6. The integrated data management system of a reservoir and a lake according to claim 4 or 5, characterized by, The basic layer further comprises a DataX data migration interface for migrating the historical data.

7. The integrated data management system of claim 1, wherein The data access layer accesses data based on Flink Table&SQL.

8. The integrated data management system of claim 7, wherein, The operation management layer provides a visual interface for writing UDF functions and Flink SQL syntax required by Flink Table&SQL.

9. The integrated data management system of claim 1, wherein, The data access layer provides an RPC interface for application end to access data.

10. A data management method of a reservoir-lake integrated system, characterized by, An application uses the data management system of any one of claims 1-9 for data management.

Images