Query method, device and equipment based on offline data warehouse and medium

Abstract

The invention relates to the field of data processing, in particular to a query method and device based on an off-line data warehouse, equipment and a medium, and aims to solve the problems that the performance for coping with complex queries is insufficient, and high-concurrency business queries cannot be supported. In the method, in response to a query request, if a query range only comprises real-time hot data, query is performed in a service database, and a first target query result is determined; and if the query range comprises the real-time hot data and the historical cold data, fusing a first query result obtained by querying in the business database and a second query result obtained by querying in the historical database to obtain a second target query result. The updating mode of the historical database is as follows: synchronizing the historical cold data to the offline data warehouse; and in the offline data warehouse, according to a plurality of preset query scenes, performing statistics and calculation on the historical cold data, and synchronizing generated result data to a historical database. The method and the device are used for improving query performance and supporting high-concurrency business query.

Description

Technical Field

[0001] This application relates to the field of data processing, specifically to a query method, apparatus, device, and medium based on an offline data warehouse. Background Technology

[0002] As business systems develop and are applied, the scale of actual business continues to expand, resulting in a large amount of transaction data and an ever-increasing volume of data stored in the database. When the data volume becomes extremely large, query performance degrades, query time increases, user experience is affected, and even the system's operational efficiency is impacted.

[0003] Especially for scenarios involving complex multi-dimensional queries between large tables in daily online transactions, as business volume increases, the database's performance in handling complex queries becomes insufficient, failing to support high-concurrency business queries and posing potential risks to production operations and maintenance. Summary of the Invention

[0004] This application provides a query method, apparatus, device, and medium based on an offline data warehouse to address the problem that existing data query methods are insufficient in handling complex queries and cannot support high-concurrency business queries.

[0005] In a first aspect, embodiments of this application provide a query method based on an offline data warehouse, including: In response to a received query request, determine whether the query scope includes real-time hot data and historical cold data; If the query scope only includes real-time hot data, then query the business database to determine the first target query result; If the query scope includes real-time hot data and historical cold data, perform a query in the business database to determine the first query result; and perform a query in the historical database to obtain the second query result; merge the first query result and the second query result to obtain the second target query result; The historical database is updated in the following way: Historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing. In the offline data warehouse, based on various pre-defined query scenarios, historical cold data is statistically analyzed and processed in multiple dimensions to generate pre-calculated result data; Synchronize the pre-calculated results to the historical database.

[0006] In this embodiment, hot and cold data are separated. Hot data is stored in a business database, while cold data is processed and synchronized to a historical database. When a query request is received, if the query scope only includes real-time hot data, the query can be performed directly in the business database to determine the first target query result. If the query scope includes both real-time hot data and historical cold data, the first query result is determined in the business database, and the second query result is determined in the historical database. The first and second query results are then merged to obtain the second target query result. Furthermore, during the updating of the historical database, an offline data warehouse based on a massively parallel processing (MPP) architecture can be used to process historical cold data, and the generated pre-calculated results are synchronized to the historical database. Applying the technical solution of this embodiment allows for the separation of hot and cold data, ensuring that the amount of hot data queried frequently is not large, while low-frequency cold data is pre-processed by the MPP offline data warehouse according to different business scenarios through multi-dimensional calculations, improving query efficiency and reducing database storage pressure. When the query scope involves both hot and cold data, the query results returned by the historical database and the business database are integrated and displayed. This solves the problems of low query efficiency and high database storage pressure caused by complex multi-dimensional processing of massive amounts of data.

[0007] In one optional implementation, historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing, including: Historical cold data generated by the business system is pushed to the object storage service according to a T+1 time limit; The data synchronization component collects data in batches from the object storage service and loads it into an offline data warehouse based on a massively parallel processing (MPP) architecture.

[0008] The above embodiments push historical cold data to the storage object service according to a T+1 timeliness, which can ensure the timeliness of historical cold data transmission and improve the efficiency of loading into the offline data warehouse.

[0009] In one alternative implementation, the method further includes: Historical cold data generated by the business system is collected through batch processing at the end of each day to ensure that the timeliness of synchronizing historical cold data to the offline data warehouse is T+1.

[0010] The above embodiments ensure the timeliness of synchronizing historical cold data to the offline data warehouse.

[0011] In one optional implementation, synchronizing the pre-calculated result data to the historical database includes: The pre-calculated structured data is synchronized to the historical database by applying incremental slicing algorithm, full slicing algorithm, zipper algorithm, or point-in-time snapshot algorithm, as well as data table splitting method.

[0012] The above embodiments provide diverse ways to synchronize pre-calculated results data to the historical database.

[0013] In one optional implementation, the first query result includes real-time detailed data, and the second query result includes pre-calculated statistical data; By combining the results of the first and second queries, we obtain the second target query result: The system associates and overlays real-time detailed data from the business database with pre-calculated statistical data from the historical database.

[0014] The above embodiments integrate the query results of cold data and hot data to improve real-time query efficiency and solve the problem of rapid data growth.

[0015] In one optional implementation, multi-dimensional statistical and processing calculations are performed on historical cold data, including: Perform metric statistics, data aggregation, and dimensional splitting on historical cold data based on business scenarios.

[0016] The above embodiments enable multi-dimensional statistics and processing calculations of historical cold data, ensuring the accuracy of cold data queries.

[0017] In one alternative implementation, the business database is an OLTP database that supports online transaction processing, and the offline data warehouse is an OLAP database that supports online analytical processing.

[0018] The above embodiments fully utilize the online transaction processing capabilities of OLTP databases and the online analytical processing capabilities of OLAP databases.

[0019] Secondly, this application provides a query device based on an offline data warehouse, comprising: The judgment module is used to: respond to the received query request and determine whether the query scope includes real-time hot data and historical cold data; The query module is used to: if the query scope only includes real-time hot data, then perform a query in the business database to determine the first target query result; The query module is also used to: if the query range includes real-time hot data and historical cold data, perform a query in the business database to determine the first query result; and perform a query in the historical database to obtain the second query result; and merge the first query result and the second query result to obtain the second target query result. The processing module is used to update the historical database in the following ways: Historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing. In the offline data warehouse, based on various pre-defined query scenarios, historical cold data is statistically analyzed and processed in multiple dimensions to generate pre-calculated result data; Synchronize the pre-calculated results to the historical database.

[0020] Thirdly, this application provides an electronic device, comprising: Memory, used to store program instructions; A processor for calling program instructions stored in memory and executing the steps included in any of the methods in the first aspect according to the obtained program instructions.

[0021] Fourthly, this application provides a computer-readable storage medium storing a computer program, the computer program including program instructions, which, when executed by a computer, cause the computer to perform the method of any one of the first aspects.

[0022] Fifthly, this application provides a computer program product comprising: computer program code, which, when run on a computer, causes the computer to perform any of the methods described in the first aspect. Attached Figure Description

[0023] Figure 1 An application scenario diagram of a query method based on an offline data warehouse provided in this application embodiment; Figure 2 A flowchart illustrating a query method based on an offline data warehouse, provided as an embodiment of this application; Figure 3 A system architecture diagram for executing a query method based on an offline data warehouse, provided for embodiments of this application; Figure 4 A structural diagram of a query method apparatus based on an offline data warehouse provided in an embodiment of this application; Figure 5 This is a structural diagram of an electronic device provided in an embodiment of this application. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application. Unless otherwise specified, the embodiments and features in the embodiments of this application can be arbitrarily combined with each other. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0025] The terms "first" and "second" in the specification, claims, and accompanying drawings of this application are used to distinguish different objects, not to describe a specific order. Furthermore, the term "comprising" and any variations thereof are intended to cover non-exclusive protection. For example, a process, method, system, product, or device that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products, or devices. The term "multiple" in this application can mean at least two, for example, two, three, or more, and the embodiments of this application do not impose limitations.

[0026] The following description, in conjunction with the accompanying drawings, illustrates exemplary embodiments of this application, including various details to aid understanding. These embodiments should be considered merely exemplary. Therefore, those skilled in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope of this application. Similarly, for clarity and brevity, descriptions of well-known functions and structures are omitted in the following description. It should be noted that in the embodiments of this application, certain existing industry solutions such as software, components, and models may be mentioned. These should be considered exemplary, intended only to illustrate the feasibility of implementing the technical solutions of this application, and do not imply that the applicant has already used or necessarily used such solutions.

[0027] The acquisition, transmission, storage, and use of data in this application all comply with the requirements of relevant national laws and regulations.

[0028] Before introducing the query method based on offline data warehouse provided in the embodiments of this application, the technical background of the embodiments of this application will be described in detail below for ease of understanding.

[0029] For ease of understanding, the technical terms used in this application are as follows: (1) Massively Parallel Processing (MPP): A distributed computing architecture mainly used for efficiently processing large-scale data and complex computing tasks. Its core idea is to decompose tasks into multiple independent processing units (usually multiple servers or nodes) for parallel execution, thereby improving overall computing power through collaborative work.

[0030] (2) Offline Data Warehouse (ODW): A structured data storage system used to store, process, and analyze historical data, primarily targeting non-real-time data analysis needs. It cleans, transforms, and integrates data through batch processing, providing enterprises with a stable and reliable foundation for data analysis and decision support.

[0031] (3) Hot Data and Cold Data: In the field of data management, hot data and cold data are two categories of data classified based on factors such as data access frequency, timeliness requirements, and storage costs. They are mainly used to optimize data storage strategies and system performance. Hot data refers to data that is frequently accessed and requires a fast response, and usually has high timeliness requirements. Cold data refers to data that is accessed infrequently and has low timeliness requirements, but still needs to be stored for a long time. It is mainly used for archiving, compliance requirements, or occasional historical analysis.

[0032] (4) Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) are two different data processing methods with many differences in application scenarios, data models, and performance requirements. OLTP is mainly used to support the daily transaction processing of enterprises, emphasizing the real-time nature, consistency, and integrity of data to ensure that business operations can be executed efficiently and accurately. OLAP is mainly used to support data analysis and decision-making. By performing multidimensional analysis on a large amount of historical data, it helps decision-makers observe and understand the data from different angles and levels, and discover trends, patterns, and relationships in the data.

[0033] The number of any elements in the accompanying drawings is for illustrative purposes only and not as a limitation, and any naming is for distinction only and has no limiting meaning.

[0034] As business systems grow and are used, project scale expands, accumulating large amounts of transaction data. The amount of data stored in the database increases significantly, gradually becoming a bottleneck for the business system. When the data volume is extremely large, query performance degrades, query time increases, impacting user experience and even affecting system efficiency. This is especially true for scenarios involving complex, multi-dimensional queries across large tables in daily online transactions. With increasing business volume, the database's performance in handling complex queries becomes insufficient, failing to support high-concurrency business queries and posing potential risks to production operations.

[0035] For online query scenarios, related technologies primarily rely on databases to provide query capabilities, enabling real-time querying and processing of massive amounts of data. Applications call Application Programming Interfaces (APIs) to provide query services. Query conditions are automatically combined based on query requirements, and query efficiency is optimized and speed improved through methods such as adding indexes, using sorting, aggregate functions, and database sharding. This approach offers advantages such as simplicity, flexibility, strong practicality, and ease of development.

[0036] However, when dealing with large amounts of data, the relevant technologies suffer from drawbacks such as slow query speeds and high database pressure. Specifically, these drawbacks include: for scenarios where queries have no time limit, the database needs to store a large amount of data, leading to high database storage pressure; when faced with scenarios involving large amounts of data, the database's query capabilities are limited, resulting in slow query speeds and transaction timeouts; and as the amount of data increases, the database faces significant challenges in data computation and I / O.

[0037] To address this, this application provides a query method based on an offline data warehouse. This method separates hot and cold data, reducing the storage pressure on existing databases, ensuring that the volume of frequently queried hot data remains small, and allowing for continuous storage of real-time data to provide users with the latest query results. Simultaneously, it solves the problem that online databases are not adept at processing massive amounts of data in multiple dimensions by using an offline data warehouse to process historical data according to different query scenarios, thereby improving real-time query efficiency.

[0038] After introducing the design concept of the embodiments of this application, the following is a brief introduction to the application scenarios to which the technical solutions of the embodiments of this application can be applied. It should be noted that the application scenarios described below are only for illustrating the embodiments of this application and are not intended to limit the scope. In specific implementation, the technical solutions provided by the embodiments of this application can be flexibly applied according to actual needs.

[0039] refer to Figure 1 This is an application scenario diagram of a query method based on an offline data warehouse provided in the embodiments of this application. Figure 1This demonstrates the processing flow for different types of query requests. When a query request is received, it is processed differently depending on the data type it contains: If the query request only contains real-time hot data, it will query the business database and get query result 1; if the query request only contains historical cold data, it will query the historical data warehouse determined based on the MMP offline data warehouse and get query result 2; if the query request contains both real-time hot data and historical cold data, it will query both the business database and the historical data warehouse determined based on the MMP offline data warehouse, and finally integrate them to get query result 3 and query result 4.

[0040] Of course, the methods provided in the embodiments of this application are not limited to... Figure 1 The application scenarios shown can also be used in other possible scenarios, and this application does not impose any limitations. Figure 1 The functions that each device in the application scenario shown can achieve will be described in subsequent method embodiments, and will not be elaborated on here.

[0041] To further illustrate the technical solutions provided in the embodiments of this application, a detailed description is provided below in conjunction with the accompanying drawings and specific implementation methods. Although the embodiments of this application provide method operation steps as shown in the following embodiments or drawings, the method may include more or fewer operation steps based on conventional or non-inventive methods. In steps where there is no logically necessary causal relationship, the execution order of these steps is not limited to the execution order provided in the embodiments of this application.

[0042] The following is combined Figure 1 The application scenarios shown illustrate the technical solutions provided in the embodiments of this application.

[0043] refer to Figure 2 This application provides a query method based on an offline data warehouse, including the following steps: S201: In response to the received query request, determine whether the query scope includes real-time hot data and historical cold data.

[0044] S202: If the query scope only includes real-time hot data, then query the business database to determine the first target query result.

[0045] S203: If the query range includes real-time hot data and historical cold data, perform a query in the business database to determine the first target query result; and perform a query in the historical database to obtain the first query result; merge the first target query result and the first query result to obtain the second target query result.

[0046] In this embodiment, hot and cold data are separated. Hot data is stored in a business database, while cold data is processed and synchronized to a historical database. When a query request is received, if the query scope only includes real-time hot data, the query can be performed directly in the business database to determine the first target query result. If the query scope includes both real-time hot data and historical cold data, the first query result is determined in the business database, and the second query result is determined in the historical database. The first and second query results are then merged to obtain the second target query result. Furthermore, during the updating of the historical database, an offline data warehouse based on a massively parallel processing (MPP) architecture can be used to process historical cold data, and the generated pre-calculated results are synchronized to the historical database. Applying the technical solution of this embodiment allows for the separation of hot and cold data, ensuring that the amount of hot data queried frequently is not large, while low-frequency cold data is pre-processed by the MPP offline data warehouse according to different business scenarios through multi-dimensional calculations, improving query efficiency and reducing database storage pressure. When the query scope involves both hot and cold data, the query results returned by the historical database and the business database are integrated and displayed. This solves the problems of low query efficiency and high database storage pressure caused by complex multi-dimensional processing of massive amounts of data.

[0047] In S201, a query request is received and responded to, and it is determined whether the query scope includes real-time hot data and historical cold data.

[0048] Regarding S202 and S203, to reduce the pressure on the existing database and provide better data services, hot and cold data will be separated. Frequently queried hot data will be stored in the business database, while infrequently queried cold data will be stored in the historical database. Optionally, the business database will be an OLTP database supporting online transaction processing, and the offline data warehouse will be an OLAP database supporting online analytical processing.

[0049] The query process mainly includes the following two scenarios: Case 1: If the query range only includes real-time hot data.

[0050] In this case, a direct query is performed in the business database to determine the primary target query result.

[0051] Scenario 2: If the query range includes real-time hot data and historical cold data.

[0052] In this scenario, a query is performed in the business database to determine the first target query result; a query is also performed in the historical database to obtain the first query result; the first target query result and the first query result are then merged to obtain the second target query result.

[0053] In a specific example, the first query result includes real-time detailed data, and the second query result includes pre-calculated statistical data.

[0054] Optionally, when merging the first query result and the second query result to obtain the second target query result, real-time detailed data from the business database and pre-calculated statistical data from the historical database can be correlated and overlaid.

[0055] In addition, the following situations may also be included in practical applications: The query scope only includes historical cold data. In this case, you can query the historical database to obtain the third target query results.

[0056] The following explains the process of updating the historical database through steps A1-A3: A1: Synchronize historical cold data generated in the business system to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing.

[0057] This process can be achieved through steps A11-A12.

[0058] A11: Push historical cold data generated by the business system to the object storage service according to the T+1 time limit.

[0059] For example, historical cold data generated by the business system is collected through batch processing at the end of the day to ensure that the timeliness of synchronizing historical cold data to the offline data warehouse is T+1.

[0060] A12: The data synchronization component is used to collect data in batches from the object storage service and load it into an offline data warehouse based on a massively parallel processing (MPP) architecture.

[0061] The offline data warehouses in the following embodiments are all offline data warehouses based on the massively parallel processing (MPP) architecture.

[0062] A2: In the offline data warehouse, based on various pre-defined query scenarios, historical cold data is statistically analyzed and processed in multiple dimensions to generate pre-calculated result data.

[0063] In the multi-dimensional statistics and processing calculations, historical cold data can be processed by indicator statistics, data aggregation and dimensional splitting based on business scenarios.

[0064] A3: Synchronize the pre-calculated results to the historical database.

[0065] The synchronization process can employ incremental slicing algorithm, full slicing algorithm, chaining algorithm, point-in-time snapshot algorithm, or data table splitting method.

[0066] In order to improve the technical solution of this application, Figure 3 This application provides a system architecture diagram for executing a query method based on an offline data warehouse.

[0067] See Figure 3 This demonstrates the processing flow of an offline data warehouse. The complete steps are as follows: Data Sources and Initial Storage: Data generated from query channels such as mobile apps, web systems, and mini-programs is processed by application components and stored in the database DB. Then, data files are generated through end-of-day data unloading operations.

[0068] Data Acquisition: With the help of the SDK, data files enter the data acquisition layer. The data acquisition layer acquires data through both streaming and batch acquisition methods, providing a data foundation for subsequent processing.

[0069] Data processing: The collected data stream is transferred to the data processing layer, where stream computing, data integration, and offline computing are performed to process and integrate the data.

[0070] Data archiving and services: Part of the processed data is stored in the archive database DB, while the other part flows to the data service layer. The data service layer provides basic services and batch subscription services to support various data application needs.

[0071] Therefore, the application calls the big data cloud interface to push business data to the storage service via the agent SDK according to a T+1 timeliness. The data synchronization component synchronizes the batch-collected data to the offline data warehouse, realizing loading, unloading, and copying from the source to the target. Data integration is achieved using incremental slicing algorithms, full slicing algorithms, chaining algorithms, point-in-time snapshot algorithms, and data table splitting. For the data in the offline data warehouse, the offline computing component performs statistical, processing, and calculation processing on the data according to different dimensions. The data synchronization component synchronizes the processed data in the offline data warehouse to the historical database.

[0072] In summary, this application embodiment, in order to reduce the pressure on existing databases and provide better data services, separates hot and cold data. Hot data with high-frequency queries is stored in the database, while cold data with low-frequency queries is sent to an offline data warehouse for multi-dimensional processing in advance. When the query scope involves both hot and cold data, only the hot data needs to be queried, and then the historical statistical data is integrated and displayed to the customer, thereby improving real-time query efficiency and solving the problem of rapid data growth.

[0073] To meet the above requirements, a big data platform was introduced, and an offline data warehouse was built based on MPP. The business system batch processes and unloads data at the end of each day, aggregating the data into the MPP offline data warehouse according to a T+1 timeliness. After processing by each layer of the MPP offline data warehouse, the data is synchronized to different components to provide data query services. At the same time, the processed data is also synchronized to the historical database for historical data archiving.

[0074] Based on the same inventive concept, embodiments of this application provide a query method and apparatus based on an offline data warehouse. Please refer to... Figure 4 The device includes a judgment module 41, a query module 42, and a processing module 43.

[0075] The judgment module 41 is used to: in response to the received query request, determine whether the query range includes real-time hot data and historical cold data; The query module 42 is used to: if the query range only includes real-time hot data, then perform a query in the business database to determine the first target query result; The query module 42 is also used to: if the query range includes real-time hot data and historical cold data, perform a query in the business database to determine the first query result; and perform a query in the historical database to obtain the second query result; and merge the first query result and the second query result to obtain the second target query result. Processing module 43 is used to update the historical database in the following ways: Historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing. In the offline data warehouse, based on various pre-defined query scenarios, historical cold data is statistically analyzed and processed in multiple dimensions to generate pre-calculated result data; Synchronize the pre-calculated results to the historical database.

[0076] In one alternative implementation, the processing module 43 is specifically used for: Historical cold data generated by the business system is pushed to the object storage service according to a T+1 time limit; The data synchronization component collects data in batches from the object storage service and loads it into an offline data warehouse based on a massively parallel processing (MPP) architecture.

[0077] In an optional implementation, the processing module 43 is further configured to: Historical cold data generated by the business system is collected through batch processing at the end of each day to ensure that the timeliness of synchronizing historical cold data to the offline data warehouse is T+1.

[0078] In one alternative implementation, the processing module 43 is specifically used for: The pre-calculated structured data is synchronized to the historical database by applying incremental slicing algorithm, full slicing algorithm, zipper algorithm, or point-in-time snapshot algorithm, as well as data table splitting method.

[0079] In one optional implementation, the first query result includes real-time detailed data, and the second query result includes pre-calculated statistical data; Query module 42 is specifically used for: The system associates and overlays real-time detailed data from the business database with pre-calculated statistical data from the historical database.

[0080] In one alternative implementation, the processing module 43 is specifically used for: Perform metric statistics, data aggregation, and dimensional splitting on historical cold data based on business scenarios.

[0081] In one alternative implementation, the business database is an OLTP database that supports online transaction processing, and the offline data warehouse is an OLAP database that supports online analytical processing.

[0082] Based on the same inventive concept, embodiments of this application provide an electronic device that can realize the functions of the query method device based on offline data warehouses discussed above. Please refer to... Figure 5 The device includes a processor 501 and a memory 502.

[0083] Processor 501 can be a general-purpose processor, such as a central processing unit (CPU), digital signal processor (DSP), application-specific integrated circuit (ASIC), field-programmable gate array (FPGA), or other programmable logic device, discrete gate or transistor logic device, or discrete hardware component, capable of implementing or executing the methods, steps, and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this application can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.

[0084] Memory 502, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. Memory may include at least one type of storage medium, such as flash memory, hard disk, multimedia card, card-type memory, random access memory (RAM), static random access memory (SRAM), programmable read-only memory (PROM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), magnetic memory, magnetic disk, optical disk, etc. Memory is any other medium capable of carrying or storing desired program code in the form of instructions or data structures that can be accessed by a computer, but is not limited thereto. Memory 502 in this embodiment may also be a circuit or any other device capable of implementing storage functions for storing program instructions and / or data.

[0085] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium. The computer program product includes computer program code, which, when executed on a computer, causes the computer to perform any of the query methods based on an offline data warehouse discussed above. Since the principle by which the above-described computer-readable storage medium solves the problem is similar to that of the query methods based on an offline data warehouse, the implementation of the above-described computer-readable storage medium can be found in the implementation of the method; repeated details will not be elaborated further.

[0086] Based on the same inventive concept, this application also provides a computer program product, which includes computer program code. When the computer program code is run on a computer, it causes the computer to execute any of the query methods based on offline data warehouses discussed above. Since the principle by which the above computer program product solves the problem is similar to that of the query method based on offline data warehouses, the implementation of the above computer program product can refer to the implementation of the method, and repeated details will not be described again.

[0087] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0088] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0089] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0090] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of user-operated steps to be executed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0091] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A query method based on an offline data warehouse, characterized in that, include: In response to a received query request, determine whether the query scope includes real-time hot data and historical cold data; If the query scope only includes real-time hot data, then a query is performed in the business database to determine the first target query result; If the query scope includes real-time hot data and historical cold data, the query is performed in the business database to determine the first query result; The system then queries the historical database to obtain a second query result; the first query result and the second query result are then combined to obtain the second target query result. The historical database is updated in the following manner: Historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing. In the offline data warehouse, based on various pre-defined query scenarios, the historical cold data is subjected to multi-dimensional statistical analysis and processing calculations to generate pre-calculated result data; The pre-calculated results are synchronized to the historical database.

2. The method according to claim 1, characterized in that, The process of synchronizing historical cold data generated in the business system to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing includes: Historical cold data generated by the business system is pushed to the object storage service according to a T+1 time limit; The data synchronization component collects data in batches from the object storage service and loads it into the offline data warehouse based on the massively parallel processing (MPP) architecture.

3. The method according to claim 2, characterized in that, The method further includes: Historical cold data generated by the business system is collected through batch processing at the end of the day to ensure that the timeliness of synchronizing the historical cold data to the offline data warehouse is T+1.

4. The method according to claim 1, characterized in that, The step of synchronizing the pre-calculated results data to the historical database includes: The pre-calculated structural data is synchronized to the historical database using incremental slicing algorithm, full slicing algorithm, zipper algorithm, or point-in-time snapshot algorithm, as well as data table splitting method.

5. The method according to claim 1, characterized in that, The first query result includes real-time detailed data, and the second query result includes pre-calculated statistical data; The first query result and the second query result are merged to obtain the second target query result: The real-time detailed data from the business database is correlated and overlaid with the pre-calculated statistical data from the historical database.

6. The method according to claim 1, characterized in that, The multi-dimensional statistical and processing calculations performed on the historical cold data include: The historical cold data is processed by statistical analysis of indicators, data aggregation, and dimensional splitting based on business scenarios.

7. The method according to any one of claims 1 to 6, characterized in that, The business database is an OLTP database that supports online transaction processing, and the offline data warehouse is an OLAP database that supports online analytical processing.

8. A query device based on an offline data warehouse, characterized in that, include: The judgment module is used to: respond to the received query request and determine whether the query scope includes real-time hot data and historical cold data; The query module is used to: if the query range only includes real-time hot data, then perform a query in the business database to determine the first target query result; The query module is also used to: if the query range includes real-time hot data and historical cold data, perform a query in the business database to determine a first query result; The system then queries the historical database to obtain a second query result; the first query result and the second query result are then combined to obtain the second target query result. The processing module is used to update the historical database in the following ways: Historical cold data generated in the business system is synchronized to an offline data warehouse based on a massively parallel processing (MPP) architecture in batch processing. In the offline data warehouse, based on various pre-defined query scenarios, the historical cold data is subjected to multi-dimensional statistical analysis and processing calculations to generate pre-calculated result data; The pre-calculated results are synchronized to the historical database.

9. An electronic device, characterized in that, include: Memory, used to store program instructions; A processor is configured to invoke program instructions stored in the memory and execute the steps of the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, the computer program including program instructions that, when executed by a computer, cause the computer to perform the method according to any one of claims 1-7.

11. A computer program product, characterized in that, The computer program product includes: computer program code, which, when run on a computer, causes the computer to perform the method described in any one of claims 1-7.

Images