Efficient bank data query warehouse design method and system based on MPP architecture

Abstract

The invention provides a design method and system for efficiently querying a bank data warehouse based on an MPP architecture, and the method comprises the steps: S1, collecting and cleaning data from a plurality of bank business systems, and loading the data to a Greenplum distributed data warehouse; s2, performing hierarchical modeling in the warehouse, constructing a customer center wide table, and storing data in each node in a balanced manner by adopting partition and hash distribution strategies; s3, designing a batch processing task scheduling process, and improving the batch processing efficiency by utilizing parallel execution; s4, optimizing configuration indexes and materialized views according to the queries, and accelerating query response by utilizing parallel computing of the MPP architecture; and S5, monitoring a system load and dynamically and elastically scaling cluster resources to deal with service peak and valley demands. By means of the steps, high-performance batch processing and real-time query support of EB-level bank data are achieved on the GreenplumMPP cluster, the data processing efficiency and the query response speed are remarkably improved, and good expansibility is achieved so as to meet the service development requirement.

Description

Technical Field

[0001] The present invention relates to the fields of large data warehouses and distributed databases, and more specifically, to an efficient query bank data warehouse design method and system based on the MPP architecture. Background Art

[0002] In today's digital age, the banking industry generates a vast amount of data every day. For large banks, the historical full-volume data summary has reached the EB level scale. How to meet the performance requirements of daily batch processing jobs (batch running) on the same data platform and support high-concurrency queries by business departments and customers all day long is a major challenge faced by the data warehouse architecture design.

[0003] Traditional bank data warehouses usually adopt a centralized architecture or a solution based on a small-scale cluster. When the data volume and the number of concurrent user queries increase sharply, it is difficult for a centralized database to provide sufficient computing performance at a reasonable cost: on the one hand, a large number of daily scheduled ETL jobs may take too long, affecting business timeliness; on the other hand, real-time query reports or customer interaction requests during the day require quick responses, and traditional architectures often cannot balance batch processing throughput and query response speed at the same time. In some existing solutions, the single-machine performance may be improved by adding hardware, or batch processing and real-time queries may be split into different systems, but the former has limited scalability and high costs, and the latter introduces problems such as data inconsistency and maintenance complexity.

[0004] Therefore, there is an urgent need for an improved data warehouse design method that uses the MPP distributed architecture to improve scalability and support efficient batch running and efficient queries simultaneously in a large data environment, ensuring the continuity of banking operations and the timeliness of data analysis. Summary of the Invention

[0005] The present invention aims at the technical problems existing in the prior art and provides an efficient query bank data warehouse design method and system based on the MPP architecture, which can efficiently support batch processing and query services for data at the EB level scale. This method makes full use of the parallel processing ability and scalability of the MPP architecture, and through reasonable data modeling and system tuning, realizes a balanced improvement in the performance of batch processing jobs and interactive queries.

[0006] According to the first aspect of the present invention, there is provided an efficient query bank data warehouse design method based on the MPP architecture, including:

[0007] Step S1. Batch collect raw data from multiple banking business source systems, clean and format-convert the raw data, and then load it into the distributed data warehouse;

[0008] Step S2. Perform hierarchical modeling and storage design on the data in the distributed database, construct a customer center wide table, and evenly store the data on each node according to a predetermined key value by using partitioning and hash distribution strategies;

[0009] Step S3. Establish a scheduling mechanism for batch processing jobs and improve the batch processing efficiency by using parallel execution;

[0010] Step S4. Configure indexes and materialized views for query optimization, and accelerate query response by using the parallel computing of the MPP architecture;

[0011] Step S5. Monitor the load status of the data warehouse, dynamically adjust the resource configuration of the cluster according to a preset policy, and expand or shrink nodes as needed to cope with peak and valley business demands.

[0012] Based on the above technical solutions, the present invention can also be improved as follows.

[0013] Optionally, the performing hierarchical modeling and storage design on the data in the distributed database and constructing a customer center wide table includes:

[0014] Partition and store the historical data according to a time period, additionally establish a replica wide table for the query dimension, and partition or distribute the replica table according to the dimension key.

[0015] Optionally, the evenly storing the data on each node according to a predetermined key value by using partitioning and hash distribution strategies includes:

[0016] Use the hash distribution mechanism to evenly disperse the data to each node, select the primary key with frequent access as the distribution key, and adopt the full replication distribution strategy for small dimension tables to ensure that the storage of data on the MPP cluster not only meets the subject division and query requirements, but also makes full use of the parallel architecture to achieve load balancing and efficient I / O.

[0017] Optionally, the establishing a scheduling mechanism for batch processing jobs and improving the batch processing efficiency by using parallel execution includes:

[0018] When entering the batch processing window, the scheduling module first triggers the data extraction job. Once the source data is loaded, immediately start multiple subsequent processing tasks in parallel, arrange the execution order and concurrency of the data processing tasks according to the task dependency relationship and the MPP parallel processing ability, and complete the daily batch data processing.

[0019] Optionally, the establishing a scheduling mechanism for batch processing jobs and improving the batch processing efficiency by using parallel execution further includes:

[0020] Consider the load threshold of the database when scheduling batch tasks, reasonably control the number of large jobs executed simultaneously, and allocate batch processing jobs to resource queues to limit CPU memory occupancy.

[0021] Optionally, optimizing the configuration index and materialized view for queries and accelerating query responses using the parallel computing of the MPP architecture includes:

[0022] Pre-calculate the summary results of frequently used queries to form a materialized view. When the corresponding query occurs, directly read the pre-calculated results and use distributed caching to store the results of high-frequency queries, so as to reduce the access overhead to the underlying database during repeated queries.

[0023] Optionally, dynamically adjusting the resource configuration of the cluster according to a preset policy includes:

[0024] When it is monitored that the system load exceeds a predetermined threshold, add one or more nodes to the distributed data cluster and re-perform hash distribution balancing on the global data; when it is monitored that the system load is lower than the threshold and there is an excess of idle resources, remove some nodes and migrate the data to the remaining nodes to release resources.

[0025] Optionally, dynamically adjusting the resource configuration of the cluster according to a preset policy further includes:

[0026] During the off-peak period of business or when the data volume decreases, execute a capacity reduction strategy. In a containerized or cloud environment, take some nodes offline or put them into a standby state, redistribute the data back to the remaining nodes, and configure mirror nodes for critical nodes to automatically switch in case of a single point of failure.

[0027] According to the second aspect of the present invention, there is provided an efficient query bank data warehouse design system based on the MPP architecture, including: a data collection module, a data modeling and storage module, a task scheduling module, a query optimization module, and an elastic management module, wherein:

[0028] The data collection module is used to collect raw data and load the cleaned data into the distributed data warehouse;

[0029] The data modeling and storage module is used to perform data layering and library table modeling in the data warehouse, create a wide table containing multiple business metrics, and implement a partition and data distribution storage strategy for the data table;

[0030] The task scheduling module is used to manage the dependency relationships and execution plans of batch processing tasks, and use the MPP parallel processing ability to trigger parallel jobs to complete the batch data processing process;

[0031] The query optimization module is used to optimize the data warehouse according to query requirements, including index establishment, pre-calculation of materialized views, and accelerating query processing using a parallel query engine;

[0032] The elastic management module is used to monitor the running load of the data warehouse and dynamically adjust the node resources of the cluster according to the load change to ensure the optimal state of system performance and resource utilization.

[0033] Optionally, it further includes a monitoring module and an expansion control module. The monitoring module is used to collect the real-time performance metrics of the cluster; the expansion control module is used to automatically increase the node resources when detecting that the load exceeds the threshold and reduce the node resources when the load is low.

[0034] The technical effects and advantages of the present invention:

[0035] The present invention provides an efficient query bank data warehouse design method and system based on the MPP architecture. By implementing high-performance batch processing and real-time query support for EB-level bank data on the Greenplum MPP cluster, it significantly improves the data processing efficiency and query response speed, and has good scalability to meet the business development needs, solving the problems of low batch processing efficiency and slow query response in the prior art, and being able to provide strong support for banking business operation and decision-making analysis in the scenario of massive data processing. Brief Description of the Drawings

[0036] Figure 1 It is a flowchart of an efficient query bank data warehouse design method based on the MPP architecture provided by an embodiment of the present invention. Detailed Embodiment

[0037] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0038] It can be understood that, based on the defects in the background technology, an embodiment of the present invention proposes an efficient query bank data warehouse design method based on the MPP architecture, specifically as Figure 1 shown. The method includes the following steps:

[0039] Step S1. Data collection, batch obtaining the original data from multiple banking business source systems and loading the data into the staging layer of the Greenplum data warehouse;

[0040] Specifically, use ETL tools or data replication technologies to regularly extract data such as transaction details and account information from various business systems (such as core business systems, online banking systems, card systems, etc.). During the extraction process, perform necessary data cleaning and format conversion. For example, remove duplicate records, fill in missing fields, and unify the time format and encoding of the data. At the same time, load the cleaned data into the staging area tables of the Greenplum cluster according to the table structure requirements. Since Greenplum supports parallel data import, you can use external tables to read files in parallel or use the provided gpload tool to quickly import large volumes of data into each Segment node, thus ensuring high throughput during the data collection process. After completing this step, the incremental or full-volume data of each bank source system has entered the MPP cluster, preparing for subsequent processing.

[0041] Step S2. Storage modeling: perform hierarchical modeling and storage design on the data in a distributed database, construct a customer center wide table, and use partition and hash distribution strategies to evenly store the data on each node according to a predetermined key value;

[0042] In this embodiment, perform hierarchical storage and model design on the collected data in the data warehouse.

[0043] First, divide the data levels on the Greenplum cluster, such as ODS (Operational Data Store layer), DWD (Detail Data layer), and DM (Data Mart layer), in order to organize massive data orderly. In the detail layer, create fact tables and dimension tables according to the subject domain. When designing, follow the wide table modeling and anti-normalization principles to reduce the table join overhead during complex queries. For example, construct a wide table with the customer ID as the primary key in the common customer data mart (DM layer), and centrally store hundreds of commonly used indicator fields such as the customer's account balance, transaction times, loan balance, and repayment history in one table. This wide table model integrates various indicators centered around the customer, which is conducive to quickly responding to comprehensive queries at the customer granularity.

[0044] Meanwhile, to improve data access efficiency, a reasonable partitioning and distribution strategy is adopted in this step. In Greenplum, large fact tables are horizontally partitioned by time range, for example, partitioned by day or month, so that data analysis for the latest period only needs to scan the most recent partitions, significantly reducing I / O overhead. When queries involve a long historical period, only necessary partitions are scanned through the partition pruning mechanism to avoid full table scans. Additionally, for data with specific access patterns, vertical partitioning or replica table structures can also be designed. For example, for key large tables such as transaction details, the "dual partition table" strategy is adopted: one table partitioned by time is retained for batch statistics, and another table partitioned by hash or range partitioning on customer ID is created for queries by customer dimension. The data in these two tables is updated synchronously, so that there is a storage layout optimized for both time aggregation and customer filtering. Such partition design balances performance in different query scenarios.

[0045] In terms of data distribution, the hash distribution mechanism of Greenplum is used to evenly disperse data to each Segment node. Preferably, the frequently accessed primary key is selected as the distribution key. For example, the customer wide table uses the customer ID as the hash distribution key, so that data of the same customer will be located on the same node, facilitating reducing cross-node data movement during associative queries. At the same time, small dimension tables can adopt the full replication distribution strategy, that is, a complete copy is retained on each node, so that data does not need to be remotely fetched during dimension association, further improving parallel query performance. Through the above modeling design, it is ensured that the storage of data on the MPP cluster not only meets the subject division and query requirements, but also makes full use of the parallel architecture to achieve load balancing and efficient I / O.

[0046] Step S3. Task scheduling, establish a scheduling mechanism for batch processing jobs, use parallel execution to improve batch processing efficiency, and ensure the orderly and efficient execution of each data processing task;

[0047] The batch processing process of the bank data warehouse usually runs during the daily business low period (such as early morning), including multiple links such as data extraction, loading, summary calculation, and update. The embodiment of the present invention designs a set of task scheduling modules, which support defining dependency relationships and parallel execution strategies. When entering the batch processing window, the scheduling module first triggers the data extraction job in step S1. Once the source data is loaded, multiple subsequent processing tasks are immediately started in parallel. For example, jobs such as transaction detail summary, customer metric wide table refresh, and risk report generation can be run simultaneously as long as they have no mutual data dependencies. For tasks with dependencies (such as a certain report waiting for the wide table update to complete), the scheduling module triggers them serially according to the preset process order. In this way, using the parallel capabilities of the MPP cluster, batch processing subtasks are maximally run in parallel, fully compressing the overall batch processing duration.

[0048] In addition, the task scheduling module also combines resource management strategies to prevent batch jobs from affecting online queries. On the one hand, the database load threshold is considered when scheduling batch tasks, and the number of large jobs executed simultaneously is reasonably controlled to prevent a sudden drop in system performance due to resource contention; on the other hand, the resource queue / resource group mechanism provided by Greenplum can be used to isolate the resource usage of batch processing and queries, such as assigning batch jobs to specific resource queues to limit their CPU memory usage. The above measures ensure that even during batch running, online queries can still obtain certain resource guarantees and will not time out and fail. Through intelligent scheduling and resource allocation, this step ensures the stable and reliable execution of the batch process and minimizes its impact on daytime query services.

[0049] Step S4. Query optimization: configure indexes and materialized views for query optimization, and use parallel computing of the MPP architecture to accelerate query response;

[0050] In order to meet the performance requirements of banking users for real-time query analysis, the embodiment of the present invention adopts a variety of optimization technologies at the data warehouse query level. First, necessary indexes are created on the wide table and fact table generated in the modeling phase. For example, a B-tree index is established on the customer ID field of the customer wide table to speed up accurate queries by customer; a combined index is established on commonly used screening fields such as date and transaction type in the transaction details table to improve the query efficiency for specific time periods or transaction categories. The establishment of indexes enables the system to quickly locate target records through index scanning, greatly reducing the frequency of full table scans.

[0051] Secondly, give full play to the advantages of the Greenplum MPP parallel query engine. Whenever a user query request is received, Greenplum's optimizer will divide the SQL query into subtasks that are executed in parallel on each Segment node. Each node performs operations such as filtering and aggregation on the locally stored data, and then the master node summarizes the results from various locations. This parallel execution mechanism enables the query to utilize the CPU and I / O resources of all nodes in the cluster to achieve the purpose of acceleration. In order to optimize the performance of parallel queries, the present invention ensures the consistent distribution of keys for associated queries during data design to reduce data redistribution overhead; for unavoidable cross-node aggregation operations, a suitable redistribution strategy and memory parallel sorting algorithm are adopted to improve aggregation efficiency. In addition, by regularly updating statistical information, an efficient execution plan is generated using Greenplum's cost optimizer (ORCA) to avoid suboptimal join orders or unnecessary repeated scans.

[0052] Again, for common complex analyses, pre-computation and caching strategies are adopted for optimization. For example, for fixed reports or frequently repeated queries, the summary results can be pre-computed and stored as materialized views or summary tables after data loading, and the pre-computed results can be directly read during query to reduce the real-time computing overhead. A distributed caching mechanism can also be introduced to cache the high-frequency query results in memory (such as the application server memory or distributed cache nodes). When the same query occurs again, the cached results are directly returned, thus bypassing the repeated access to the database. Through these means, the present invention significantly improves the response speed of online queries, enabling business personnel to obtain the required data analysis results within seconds or even sub-seconds.

[0053] In the Greenplum database environment, the dynamic creation, deletion, and adjustment of indexes are carried out through automated machine learning algorithms (such as reinforcement learning). According to the data access pattern, indexes are automatically created or deleted, and the index structure is optimized to keep the query efficiency always in the optimal state while reducing the resource overhead of index maintenance.

[0054] Step S5. System elastic design, monitoring the load status of the data warehouse, and dynamically adjusting the resource configuration of the cluster according to the preset policy to expand or shrink nodes as needed to cope with the business peak and valley demands.

[0055] In view of the characteristics of the periodic peak and valley changes in the banking business load, the present invention introduces elastic scaling design in the system architecture to improve resource utilization and ensure service performance during peak periods. In the Greenplum MPP cluster deployment, it supports dynamically adjusting the cluster scale according to the data volume and load pressure. When the monitoring system detects that the query concurrency, CPU utilization rate, or data processing volume of the data warehouse continues to rise and approaches the preset threshold, the elastic expansion process is started: adding one or more Segment nodes to the Greenplum cluster. After the new nodes are deployed, the data is re-balanced through the shard expansion tool of Greenplum, that is, the existing data is re-partitioned to all the nodes including the new nodes according to the hash distribution rule to ensure that each node undertakes an equal data storage and processing load. After the expansion is completed, the overall computing power and storage capacity of the system are linearly increased, and it can calmly handle the query traffic and batch processing requirements during the business peak.

[0056] Conversely, during the business trough period or when the data volume decreases, a capacity reduction strategy can be implemented. For example, in a containerized or cloud environment, some Segment nodes can be taken offline or switched to a standby state, and the data can be redistributed back to the remaining nodes, thereby releasing computing resources to save costs. During the entire elastic adjustment process, the master node coordinates data redistribution and task scheduling to keep the service uninterrupted or minimize the interruption time. In addition, in cooperation with the high-availability mechanism, such as configuring mirror nodes for critical nodes and automatically switching in case of a single-point failure, the reliability of the system is ensured. The elastic design of this step enables the data warehouse to scale elastically according to the actual business volume, ensuring the performance requirements during peak periods and avoiding resource waste during idle times, and improving the overall efficiency and stability of the system.

[0057] In summary, by implementing the above steps S1 to S5, the bank data warehouse system constructed in the embodiments of the present invention can be based on the Greenplum MPP architecture to achieve efficient batch processing and query analysis support for massive bank data. Among them, the data collection and modeling steps ensure the comprehensiveness, cleanliness, and orderly organization of the data foundation; the task scheduling and query optimization steps ensure the high performance and high-concurrency processing capabilities of the system during operation; the elastic design provides a strategy for the system to expand on demand and endows the system with self-adaptive capabilities under different load conditions. In an exemplary application, after a large bank upgraded its data warehouse using the solution of the present invention, the total daily batch processing time was reduced by several dozen percentage points, and at the same time, the average online query response time during the day was shortened to a fraction of the original, fully demonstrating the superiority of the solution of the present invention in actual scenarios.

[0058] According to a second aspect of the present invention, there is provided an efficient query bank data warehouse design system based on the MPP architecture, including: a data collection module, a data modeling and storage module, a task scheduling module, a query optimization module, and an elastic management module, wherein:

[0059] The data collection module is configured to collect raw data from the banking business source system and load the cleaned data into the Greenplum MPP data warehouse;

[0060] The data modeling and storage module is configured to perform data layering and library table modeling in the Greenplum data warehouse, create a wide table containing various business metrics, and implement a partition and data distribution storage strategy for the data table;

[0061] The task scheduling module is configured to manage the dependency relationships and execution plans of batch processing tasks, and use the MPP parallel processing ability to trigger parallel jobs to complete the batch data processing process;

[0062] The query optimization module is configured to optimize the data warehouse for query requirements, including index establishment, materialized view pre-computation, and accelerating query processing using the Greenplum parallel query engine;

[0063] The elastic management module is configured to monitor the running load of the data warehouse and dynamically adjust the node resources of the Greenplum cluster according to the load change to ensure the optimal state of system performance and resource utilization.

[0064] The system further includes a monitoring module and an expansion control module. The monitoring module is used to collect real-time performance metrics of the cluster; the expansion control module is used to automatically increase node resources when the detected load exceeds the threshold and reduce node resources when the load is low.

[0065] It can be understood that an efficient query bank data warehouse design system based on the MPP architecture provided by the present invention corresponds to an efficient query bank data warehouse design method provided by the foregoing embodiments. The relevant technical features of an efficient query bank data warehouse design system based on the MPP architecture can refer to the relevant technical features of an efficient query bank data warehouse design method, which will not be elaborated here.

[0066] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on this understanding, the essence of the above technical solution or the part that contributes to the prior art can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0067] Although the preferred embodiments of the present invention have been described, those skilled in the art can make additional changes and modifications to these embodiments once they know the basic creative concept. Therefore, the appended claims are intended to be construed to include the preferred embodiments and all changes and modifications falling within the scope of the present invention.

[0068] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalent technologies, the present invention is also intended to include these changes and modifications.

[0069] Finally, it should be noted that the above are only preferred embodiments of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or perform equivalent replacements for some of the technical features. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. An efficient query bank data warehouse design method based on the MPP architecture, characterized in that, It includes the following steps: Step S1. Batch collect raw data from multiple banking business source systems, clean and format-convert the raw data, and then load it into a distributed data warehouse; Step S2. Perform hierarchical modeling and storage design on the data in the distributed database, construct a customer center wide table, and use partition and hash distribution strategies to evenly store the data on each node according to a predetermined key value; Step S3. Establish a scheduling mechanism for batch processing jobs and utilize parallel execution to improve batch processing efficiency; Step S4. Configure indexes and materialized views for query optimization, and utilize the parallel computing of the MPP architecture to accelerate query response; Step S5. Monitor the load status of the data warehouse, dynamically adjust the resource configuration of the cluster according to a preset policy, and expand or shrink nodes as needed to cope with business peak and valley demands.

2. The design method of an efficient query bank data warehouse based on the MPP architecture according to claim 1, characterized in that, The performing hierarchical modeling and storage design on the data in the distributed database and constructing a customer center wide table includes: Partition and store historical data according to time periods, additionally establish a replicated wide table for query dimensions, and partition or distribute the replicated table according to dimension keys.

3. The design method of an efficient query bank data warehouse based on the MPP architecture according to claim 2, characterized in that, The using partition and hash distribution strategies to evenly store the data on each node according to a predetermined key value includes: Utilize the hash distribution mechanism to evenly disperse the data to each node, select the primary key with frequent access as the distribution key, and use the full replication distribution strategy for small dimension tables to ensure that the storage of data on the MPP cluster not only meets the subject division and query requirements but also makes full use of the parallel architecture to achieve load balancing and efficient I / O.

4. The design method of an efficient query bank data warehouse based on the MPP architecture according to claim 1, characterized in that, The establishing a scheduling mechanism for batch processing jobs and utilizing parallel execution to improve batch processing efficiency includes: When entering the batch processing window, the scheduling module triggers the data extraction job. When the source data loading is completed, immediately start multiple subsequent processing tasks in parallel, arrange the execution order and concurrency of data processing tasks according to task dependencies and MPP parallel processing capabilities, and complete daily batch data processing.

5. An efficient query bank data warehouse design method based on the MPP architecture according to claim 4, characterized in that The establishing a scheduling mechanism for batch processing jobs and utilizing parallel execution to improve batch processing efficiency also includes: Consider the load threshold of the database when scheduling batch tasks, control the number of large jobs executed simultaneously, and allocate batch processing jobs to resource queues to limit CPU memory occupancy.

6. The design method of an efficient query bank data warehouse based on the MPP architecture according to claim 1, wherein The configuring indexes and materialized views for query optimization and utilizing the parallel computing of the MPP architecture to accelerate query response includes: Pre-calculate the summary results of frequently used queries to form materialized views. When the corresponding queries occur, directly read the pre-calculated results, and use distributed caching to store the results of high-frequency queries to reduce the access overhead to the underlying database during repeated queries.

7. An efficient query bank data warehouse design method based on the MPP architecture according to claim 1, characterized in that The dynamically adjusting the resource configuration of the cluster according to a preset policy includes: When it is detected that the system load exceeds the predetermined threshold, add one or more nodes to the distributed data cluster and re-perform hash distribution balancing on the global data; when it is detected that the system load is lower than the threshold and there is excessive idle resources, remove some nodes and migrate the data to the remaining nodes to release resources.

8. An efficient query bank data warehouse design method based on the MPP architecture according to claim 1, characterized in that, The dynamically adjusting the resource configuration of the cluster according to a preset policy also includes: In the case of a business downturn or a decrease in data volume, execute a capacity reduction strategy. In a containerized or cloud environment, take some nodes offline or put them into a standby state, redistribute the data back to the remaining nodes, and configure mirror nodes for critical nodes to enable automatic switching in case of a single point of failure.

9. An efficient query bank data warehouse design system based on the MPP architecture, characterized in that, Including: A data collection module, a data modeling and storage module, a task scheduling module, a query optimization module, and an elastic management module. Among them: The data collection module is used to collect raw data and load the cleaned data into a distributed data warehouse; The data modeling and storage module is used to perform data layering and library table modeling in the data warehouse, create wide tables containing various business metrics, and implement partition and data distribution storage strategies for data tables; The task scheduling module is used to manage the dependency relationships and execution plans of batch processing tasks, and trigger parallel jobs using the MPP parallel processing ability to complete the batch data processing process; The query optimization module is used to optimize the data warehouse for query requirements, including index establishment, materialized view precomputation, and accelerating query processing using a parallel query engine; The elastic management module is used to monitor the running load of the data warehouse and dynamically adjust the node resources of the cluster according to the load changes to ensure the optimal state of system performance and resource utilization.

10. An efficient query bank data warehouse design system based on the MPP architecture according to claim 9, characterized in that, It also includes a monitoring module and an expansion control module. The monitoring module is used to collect real-time performance metrics of the cluster; the expansion control module is used to automatically increase node resources when the detected load exceeds the threshold and reduce node resources when the load is low.

Images