Data weaving cross-library view efficient query method and system

Abstract

The invention discloses a data knitting cross-library view efficient query method and system, and relates to the technical field of data processing. The system comprises a unified query interface module, a metadata management module, an intelligent distributed query optimizer, a multi-level cache module, a materialized view management module, an execution engine module and a performance monitoring and feedback module. According to the efficient query method and system for the data knitting cross-library view, a dynamic cost model is constructed through an intelligent distributed query optimizer, and a push-down strategy is decided and queried in combination with source capability metadata; a multi-level cache and materialized view collaboration mechanism is adopted to reduce source data access; maintenance overhead of materialized views is reduced with incremental refresh driven by CDC.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a method and system for efficient querying of cross-database views. Background Technology

[0002] As enterprises become increasingly information-driven, data is scattered across various heterogeneous systems and databases (such as relational databases, NoSQL databases, data lakes, and cloud storage), creating numerous data silos. To break down these data silos and provide a unified data access interface, data fabric technology has emerged. One of the core concepts of data fabric is to use technologies such as data virtualization to build logical views (i.e., cross-database views or virtual views) across different data sources without moving the data itself. This allows users and applications to access the integrated data as if it were a single database.

[0003] Problems with existing technology: 1. Limitations of traditional data virtualization: Existing data virtualization or traditional cross-database query technologies (such as database chains and federated queries) While it offers cross-database access capabilities, its performance is often poor when handling complex cross-database queries (such as those involving multi-source joins, aggregations, and filtering). Its main drawback is: Insufficient query optimization capabilities: Most existing technologies lack a global optimizer for multiple heterogeneous data sources, making it difficult to effectively optimize queries. The evaluation of query capabilities, network conditions, and data distribution of different data sources makes it impossible to generate an optimal execution plan.

[0004] Limited query pushdown capabilities make it difficult to intelligently and effectively "push down" operations such as filtering, projection, joins, and aggregations within a query to the data source for execution. In many cases, it becomes necessary to pull data from the source system to the virtualization layer for processing, resulting in large amounts of data being transmitted over the network, increasing latency and resource consumption. For example, if a join between large tables in two different databases is required, traditional methods might need to pull both full tables to the virtualization layer before joining, which is extremely inefficient.

[0005] Lack of a unified caching strategy: Existing technologies often lack an integrated, intelligent caching layer to store frequently accessed data fragments or query results, resulting in repeated queries needing to repeatedly access the underlying data source.

[0006] Lack of sufficient consideration of the heterogeneity of the source system: Different data sources support huge differences in SQL dialects, functions, index types and performance characteristics. Existing technologies cannot make full use of these heterogeneities for adaptation optimization, resulting in general optimization strategies being inefficient on specific sources.

[0007] Lack of performance monitoring and feedback mechanisms: It is difficult to effectively monitor the performance bottlenecks of cross-database queries at various stages (network transmission, source system execution, virtualization layer processing), and it is even more impossible to automatically feed the monitoring results back to the optimizer or provide guidance to users.

[0008] Materialized views are not flexible in application: Although some technologies support materialized views, their management (definition, refresh, invalidation) is usually static and manual, lacking intelligent recommendation and incremental refresh capabilities, and is not closely integrated with query optimization of virtual views.

[0009] 2. Challenges in the context of data weaving: In the broader context of data weaving, the number of data sources is greater, their types are more complex, and their distribution is more extensive (cloud, edge, local). This makes the shortcomings of the aforementioned traditional technologies more prominent, seriously affecting the query performance and user experience of cross-database virtual views, and limiting the true realization of the value of "data as a service" by data weaving. Summary of the Invention

[0010] To address the shortcomings of existing technologies, this invention provides a method and system for efficient cross-database view querying using data weaving. It solves the performance bottleneck problems of existing data weaving and data virtualization technologies when processing virtual view queries across multiple heterogeneous data sources, particularly the deficiencies such as insufficient query pushdown capability, lack of intelligent caching and materialization mechanisms, and inability to effectively cope with the heterogeneity of source systems and the complexity of distributed environments.

[0011] To achieve the above objectives, the present invention provides the following technical solution: a data weaving cross-database view efficient query system, comprising: Unified Query Interface Module: Receives query requests (such as SQL queries) from users or applications for cross-database virtual views.

[0012] Metadata Management Module: Stores the definitions of all virtual views in the data weaving platform, connection information of the underlying data source, metadata of the data source (table structure, column information, index information, etc.), and capability information of the data source (supported SQL dialects, functions, operators, maximum number of connections, etc.). In addition, it also stores the definitions, storage locations, and refresh strategy information of materialized views.

[0013] Intelligent Distributed Query Optimizer: This is the core of this invention. This optimizer is responsible for receiving queries from a unified query interface and generating the optimal distributed execution plan. Its functions include: Query parsing and rewriting: Parse user queries and map them to the underlying data source of the virtual view.

[0014] Source capability awareness and matching: Based on the source capability information provided by the metadata management module, identify which parts of the query can be executed locally on a specific source system.

[0015] Cost-based optimization model: Construct a cost model that comprehensively considers multiple factors, including but not limited to: Source system execution cost: The estimated cost of performing a specific operation (filtering, joining, aggregation) on the source system (using statistics from the source system and historical performance data collected by the platform).

[0016] Network transmission cost: Estimate the cost of transmitting data between different data sources and data weaving platforms (considering data volume, network bandwidth, and latency).

[0017] Internal platform processing costs: Estimated costs of performing operations within the data weaving platform.

[0018] Source system load awareness (optional but preferred): Real-time or periodic awareness of the current load of the source system and its inclusion in the cost model.

[0019] Query pushdown decision-making: Intelligently determines which source system to push down operations such as filtering, projection, partial joins, and partial aggregations in the query to for execution, in order to minimize total cost and data transfer volume. For example, for cross-source joins, the optimizer evaluates whether to filter at the source end before transferring the join, whether to pull data from one side to the other source end for a partial join (if the source system supports heterogeneous joins), or whether to pull data from both sides to the platform for a join, and selects the optimal strategy.

[0020] Caching and materialized view utilization decisions: When generating the execution plan, prioritize checking whether the query (or a subexpression of the query) can be satisfied by cached data fragments, cached query results, or existing materialized views. If so, redirect the plan to use that cached / materialized data, thereby avoiding access to the original data source.

[0021] Parallel execution plan generation: Generates execution plans that can retrieve data from multiple data sources in parallel and process them in parallel within the platform.

[0022] Adaptive execution plan (advanced): During query execution, dynamically adjust parts of the execution plan based on actual conditions (such as a sudden slowdown in the response of a source system).

[0023] Multi-level caching module: An integrated, intelligently managed caching layer, including: Data Fragment Cache: Caches popular data fragments pulled from the source system.

[0024] Query result cache: Caches query results that are exactly the same or have similar parameter ranges.

[0025] Cache management strategy: Employ intelligent cache eviction strategies (such as LFU, LRU variants) and invalidation strategies (such as TTL-based, or integrated with the source system's Change Data Capture (CDC) to achieve more accurate cache invalidation).

[0026] Materialized View Management Module: Responsible for managing the lifecycle of materialized views.

[0027] Materialized view definition and storage: Allows users / administrators to define the physical storage of the results of specific virtual views on high-performance storage media (such as dedicated query databases or distributed storage).

[0028] Refresh Scheduling and Execution: Executes refreshes of materialized views according to user-defined strategies (timed, event-triggered). Incremental refresh is supported. Refresh (if the underlying source and platform support CDC or other change capture mechanisms) to reduce refresh overhead and time.

[0029] Optimizer Integration: Tightly integrated with the intelligent optimizer to ensure queries intelligently hit materialized views.

[0030] Execution Engine Module: Based on the execution plan generated by the optimizer, it coordinates the acquisition of data from the data source, utilizes cached / materialized views, performs necessary calculations within the platform (such as cross-source joins and aggregations), and returns the final results to the user.

[0031] Performance monitoring and feedback module: Monitors the query execution process in real time, collecting performance metrics at each stage (such as network latency, source system response time, data transfer volume, cache hit rate, materialized view refresh time, etc.). This data is fed back to the optimizer to improve the cost model, and a visualization interface is provided for users / administrators to analyze performance bottlenecks, potentially offering materialized view or index optimization suggestions.

[0032] This invention also discloses a method for efficient querying of cross-database views using data weaving, with a specific deployment and usage process as follows: 1. Administrators or data engineers use the platform connector to configure connections with various heterogeneous data sources, and the metadata management module collects metadata and capability information from the source systems.

[0033] 2. Users or data engineers define cross-database virtual views through the platform, which are based on tables and columns of the underlying data source and may include cross-source joins, filtering and other logic.

[0034] 3. Users or applications submit queries for virtual views through a unified query interface.

[0035] 4. The intelligent distributed query optimizer takes over the query, parses and rewrites it, and uses metadata information, cost models, source capability information, etc. to generate the optimal execution plan. This plan will take into account pushdown, cache utilization, and materialized view redirection.

[0036] 5. The execution engine executes the query according to the plan: If the plan includes a pushdown operation, a simplified query fragment is sent to the source system.

[0037] If the plan hits the cache, then quickly retrieve the data from the cache module.

[0038] If the plan hits a materialized view, then query the materialized view storage layer.

[0039] If data needs to be pulled from the source system, it is retrieved in parallel and processed within the platform (such as performing cross-source connections and aggregation).

[0040] During execution, the acquired data fragments are populated into a multi-level cache (if the policy allows).

[0041] 6. The performance monitoring and feedback module records execution process data for subsequent optimization decisions and user analysis.

[0042] 7. Users or administrators can manually define materialized views and their refresh strategies based on performance monitoring results and platform recommendations, or create indexes on the source system to further accelerate pushdown. The materialized view management module is responsible for refreshing and maintaining materialized views.

[0043] 8. On the next query, the intelligent optimizer will re-evaluate and may utilize newly created materialized views or optimized source system indexes.

[0044] Replacement plan description: Caching strategies: Different cache eviction algorithms (LRU, LFU, ARC, etc.) can be used, or different cache granularities (row, column, block) can be adopted according to the characteristics of the data source.

[0045] Materialized view storage: Materialized views can be stored on high-performance storage within the platform, a separate data warehouse, or a data lake, depending on the data volume, access patterns, and cost.

[0046] Query pushdown granularity: The complexity of pushdown can vary, from simple filtering and projection to more complex Join and Aggregate pushdown, depending on the SQL capabilities of the source system and the implementation level of the platform connector.

[0047] This invention provides a method and system for efficient cross-database view querying. Compared with existing technologies, it has the following advantages: 1. This data weaving method and system for efficient cross-database view query significantly improves query performance: Through intelligent query pushdown, unnecessary data transmission and internal platform processing are reduced; through multi-level caching and intelligent utilization of materialized views, repeated access to the underlying data source is avoided, greatly reducing query latency and improving response speed.

[0048] 2. This data weaving method and system provides an efficient query method and system for cross-database views, reducing network overhead: by pushing computation to the data source and transmitting only the necessary subset of data, it greatly reduces data flow across the network.

[0049] 3. This data weaving method and system provides an efficient query approach for cross-database views, reducing the load on the underlying data source and platform: the pushdown operation leverages the computing power of the source system, alleviating the processing burden on the data weaving platform itself. Intelligent caching and materialized views further reduce the pressure of repeated accesses to the source system.

[0050] 4. This data weaving method and system enables efficient cross-database view querying and better heterogeneity adaptation: the intelligent optimizer can perceive and utilize the specific capabilities of different data sources to generate pushdown plans that are more suitable for the source, thereby improving the effectiveness of optimization.

[0051] 5. This data weaving method and system for efficient cross-database view query improves data access efficiency: it provides real-time or near real-time data access capabilities, eliminates the need for traditional ETL processes, and supports a wider range of data application scenarios. 6. This data weaving system provides an efficient method and system for cross-database view querying, characterized by automation and intelligence: query optimization, cache utilization, and materialized view matching are highly automated and intelligent, reducing manual intervention and tuning workload, and lowering the technical threshold. 7. This data weaving system provides an efficient query method and system for cross-database views, with continuous optimization capabilities: performance monitoring and feedback mechanisms provide data support for the continuous improvement and optimization of the system. Attached Figure Description

[0052] Figure 1 This is a schematic diagram of the system of the present invention; The diagram shows: 1. Unified query interface module; 2. Metadata management module; 3. Intelligent distributed query optimizer; 4. Multi-level caching module; 5. Materialized view management module; 6. Execution engine module; 7. Performance monitoring and feedback module. Detailed Implementation

[0053] The technical solutions in the embodiments of the present invention have been clearly and completely described. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0054] Please see Figure 1 The present invention provides a technical solution: a data weaving cross-database view high-efficiency query system, including a unified query interface module 1, a metadata management module 2, an intelligent distributed query optimizer 3, a multi-level caching module 4, a materialized view management module 5, an execution engine module 6, and a performance monitoring and feedback module 7.

[0055] The overall system architecture is as follows: the system can adopt a microservice architecture, and the modules (query interface service, optimizer service, execution engine service, metadata service, cache service, materialized view service, and monitoring service) communicate with each other through APIs.

[0056] The unified query interface module 1 implements the following: receiving query requests from users or applications for cross-database virtual views.

[0057] Metadata management module 2 implementation: A relational database or a dedicated metadata storage system can be used to store virtual view definitions, source connection information, source metadata and capability information. Source capability information can be obtained by the connector during initialization or through manual configuration.

[0058] The Intelligent Distributed Query Optimizer 3 implementation can employ a combination of rule-based and cost-based optimization strategies.

[0059] Query parsing: The query is converted into an abstract syntax tree (AST) using a standard SQL parsing library.

[0060] Logical plan generation: Converts the AST into a logical operator tree, including filtering, projection, joining, aggregation, etc.

[0061] Source capability matching: Traverse the operations in the logical plan, compare them with the source capability information in the metadata, and identify which operations can be executed on which source systems.

[0062] Cost model implementation: Regression models, machine learning models, or empirical formulas can be used to estimate the cost of different execution paths. The training data for the cost model can come from the monitoring data of historical query executions. Different cost model parameters need to be built for different types of source systems.

[0063] Physical plan generation and selection: Based on the cost model, explore different physical execution paths (including pushdown, pull, cache utilization, materialized view utilization, and parallel execution strategies), such as using dynamic programming algorithms to select the connection order and choose the physical execution plan with the lowest total cost.

[0064] Cache / materialized view integration: During the physical plan generation phase, add a step to check if there are cached items or materialized views that can match the current logical operator subtree or the entire query, and prioritize the use of these options when calculating costs.

[0065] Multi-level caching module 4 implementation: Memory caching (such as Redis cluster, Ehcache) can be used as the first-level cache, and distributed file system or object storage as the second-level cache. The key design of the cache needs to take into account the differences in query parameters in order to identify similar queries or data fragments. Cache invalidation can be achieved by configuring TTL, or by integrating the message notification mechanism of CDC tools such as Kafka and Debezium to achieve more accurate invalidation.

[0066] Materialized view management module 5 implementation: Materialized view definitions are stored in the metadata management module. Refresh tasks can be triggered by a scheduling system (such as Quartz, Airflow), and incremental refresh can be achieved by reading the CDC logs of the source system or using the platform's built-in CDC connector. The materialized view itself can be stored in a high-performance columnar storage database (such as ClickHouse, Doris) or a distributed storage system (such as HDFS, S3).

[0067] The execution engine module 6 is responsible for translating the physical execution plan into executable tasks and distributing them to worker nodes for parallel execution. It also interacts with the source system connector to obtain data and performs necessary calculations and data transformations internally.

[0068] Performance monitoring and feedback module 7 implementation: It uses distributed tracing systems (such as Open Tracing, Jaeger) and metric collection systems (such as Prometheus) to collect execution process data. The data is stored in a time-series database and provides a visual dashboard for user monitoring. It can develop algorithms to automatically analyze slow query logs, identify bottlenecks, and provide feedback to the optimizer or push optimization suggestions to users (such as "suggest creating a materialized view based on column Y for view X").

[0069] User Interface: Provides a graphical interface for defining virtual views, defining materialized views, monitoring performance, and receiving optimization suggestions.

[0070] During implementation, it is crucial to focus on the stability and performance of connectors to various heterogeneous data sources. This ensures that connectors accurately transmit the capability information of the source systems and efficiently execute pushed-down query fragments. Simultaneously, it is necessary to continuously collect and analyze query performance data, iteratively optimizing the cost model and optimizer's decision logic to adapt to constantly changing query patterns and data environments. For scenarios with massive data volumes and relatively fixed access patterns, materialized views are a key means of improving performance, requiring investment in the research and development of intelligent recommendation and incremental refresh technologies for materialized views.

[0071] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus.

[0072] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for efficient querying of cross-database views using data weaving, characterized in that, Includes the following steps: S1. Receive query requests for cross-database virtual views through a unified query interface; S2. Call the virtual view definition, data source connection information, metadata and capability information in the metadata management module, parse and rewrite the query; S3. Generate a distributed execution plan through the intelligent distributed query optimizer, including: (a) Identify operations that can be pushed down to the source system based on data source capability information; (b) Evaluate the operational costs based on the cost model, including source system execution costs, network transmission costs, and platform internal processing costs; (c) Decision-making optimal query pushdown strategy to minimize total cost and data transfer volume; (d) Prioritize matching either cached data or materialized view data from the multi-level cache module to replace access to the source system; S4. The execution engine coordinates operations according to a plan: pushing query fragments down to the source system, accessing caches and materialized views, and performing cross-source computations; S5. Return the query results to the user and dynamically optimize the cost model based on performance monitoring data.

2. The efficient query method and system for cross-database views according to claim 1, characterized in that: The cost model for step (b); Estimate the operational cost using source system statistics and historical performance data; Optional, real-time sensing of source system load data can be incorporated.

3. The efficient query method and system for cross-database views according to claim 1, characterized in that: The cache matching in step (d) includes: Query result caching: Matches query results with the same or similar parameter ranges; Data fragment caching: Matches popular data fragments pulled from the source system; The cache invalidation strategy is implemented based on TTL and the source system's CDC.

4. The efficient query method and system for cross-database views according to claim 1, characterized in that: The pushdown decision in step (c) includes cross-source connectivity strategy evaluation; Choose the optimal solution among source-side filtering followed by transmission, source-side partial connection execution, or platform-internal connection. The evaluation criteria include whether the source system supports heterogeneous connection operations.

5. The efficient query method and system for cross-database views according to claim 1, characterized in that: The plan is dynamically adjusted during the execution process; Data retrieval nodes are switched in real time based on the source system's response delay; Get the path by redirecting data based on cache hit status.

6. The efficient query method and system for cross-database views according to claim 1, characterized in that: The use of the materialized view includes; Perform refresh according to a user-defined timed or event-triggered strategy; Supports incremental refresh via the CDC mechanism; Assign priority weights to materialized views in the cost model.

7. A data weaving cross-database view high-efficiency query system according to any one of claims 1-6, characterized in that, include: The unified query interface module is used to receive query requests in SQL format; The metadata management module is used to store virtual view definitions, data source connection information, table structure metadata, source capability information, and materialized view metadata. An intelligent distributed query optimizer is used to perform query parsing and rewriting, source capability-aware matching, cost-based optimization decisions, caching, materialized view redirection, and parallel plan generation. The multi-level caching module includes a data fragment caching submodule and a query result caching submodule; The materialized view management module is used to manage the storage, refresh scheduling, and incremental refresh execution of materialized views; The execution engine module is used to perform query pushdown, cache access, cross-source join calculations, and result return. The performance monitoring and feedback module is used to collect metrics such as network latency, source response time, and cache hit rate, and to optimize the cost model.

8. The efficient cross-database view query system for data weaving according to claim 1, characterized in that: The metadata management module stores data source capability information including: Supported SQL dialects, functions, operators, maximum number of connections, and index information.

9. The efficient cross-database view query system according to claim 1, characterized in that: The cache management strategy of the caching module adopts LFU and LRU variant cache eviction strategies, and achieves accurate cache invalidation through CDC integration.

10. The efficient cross-database view query system according to claim 1, characterized in that: The materialized view management module; Store the materialized view in either a high-performance columnar database or a distributed storage system; The refresh task is triggered by the scheduling system; Incremental refresh is achieved by parsing the source system's CDC logs.

Images