Communication method, device and equipment among multi-source heterogeneous data warehouses and medium

Abstract

The application discloses a communication method and device between multi-source heterogeneous data warehouses, equipment and medium, relates to the technical field of data processing, and comprises the following steps: a first heterogeneous data warehouse receives a data transmission request; a preset cluster routing table is queried according to a data warehouse cluster identifier to determine a metadata client, an execution engine, a distributed file system client and a resource queue; a query statement is rewritten into a structured query language statement for creating a temporary table, the structured query language statement is executed to obtain a query result; the query result is materialized into a current temporary table, and the current temporary table is written into a file under a temporary path of a distributed file system; after the writing of the current temporary table is completed, a transmission task is found according to the type of a second heterogeneous data warehouse; in the transmission stage, the data of the current temporary table is read in a streaming mode based on the temporary path, and the read data is written into the second heterogeneous data warehouse. Efficient, safe and unified data exchange between multi-source heterogeneous data warehouses is realized.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to communication methods, apparatus, equipment and media between multi-source heterogeneous data warehouses. Background Technology

[0002] In data-intensive business scenarios such as finance, data warehouses (including offline and real-time data warehouses) need to continuously distribute data to dozens or even hundreds of external business systems. These external business systems cover many areas, including applications, accounting, and credit reporting. These external business systems vary greatly in access protocols, storage media, data formats, and writing methods, and may use more than ten different media such as relational databases, NoSQL (Not Only SQL) databases, message queues, search engines, graph databases, and file systems. Therefore, data warehouses face severe heterogeneous adaptation challenges when distributing data externally. Among related technologies, there are several main implementation methods for data warehouses to distribute data to external business systems. The first type is the point-to-point direct connection solution, where business systems directly connect to the data warehouse's Hive, HDFS (Hadoop Distributed File System), and Impala to pull data via JDBC (Java Database Connectivity), Sqoop scripts, shell tasks, etc. The second category involves defining the data flow from the source to the target using tools such as Kettle, DataX, and Informatica in a graphical configuration manner. The third category is a message queue-based data distribution solution, where all data from the data warehouse is pushed into Kafka, and downstream systems subscribe to and consume it. The fourth category is a manual script plus scheduled task solution, where operations personnel maintain scripts to complete the export, transmission, and loading of data.

[0003] However, the aforementioned technologies suffer from at least the following drawbacks. First, the cluster is highly coupled with downstream systems. Each time a new downstream system is connected or a data warehouse cluster is replaced, adaptation code needs to be rewritten, resulting in poor scalability and high development costs. Second, different data warehouse clusters have significantly different execution engines. For example, Impala and Spark SQL differ in syntax, temporary table creation mechanisms, and HDFS path specifications, requiring business units to adapt separately, leading to high learning costs and a high risk of errors. Third, there is a lack of unified access control. The data warehouse is directly exposed to downstream systems, making fine-grained permission verification at the database, table, field, and system levels impossible, resulting in high security risks. Fourth, data consistency is difficult to guarantee. In scenarios such as multi-threaded batch processing, network transmission, and target system write failures, it is impossible to accurately count rows and locate failed tasks, posing a risk of data misalignment and time-consuming troubleshooting. Fifth, throughput is limited. Traditional ETL (Extraction Transformation Loading) tools use single-point processing, resulting in significant performance bottlenecks when handling terabyte (TB) level data. Sixth, the lack of unified monitoring and alarms, and the scattered direct connection make the task status invisible and anomalies cannot be notified in a timely manner, resulting in low operation and maintenance efficiency.

[0004] Therefore, how to uniformly connect multiple heterogeneous data warehouse clusters in one system, support the writing of various heterogeneous downstream target systems with a standardized interface, achieve high-throughput data delivery while ensuring data consistency and traceability, and implement unified access control and anomaly alarms for data delivery are technical problems that urgently need to be solved in this field. Summary of the Invention

[0005] In view of this, the purpose of this invention is to provide a communication method, apparatus, device, and medium between multi-source heterogeneous data warehouses, capable of connecting multiple heterogeneous data warehouse clusters through a unified two-stage standard pipeline and distributing data to various heterogeneous downstream target systems. While achieving linear scalability and masking differences in execution engines, it ensures data consistency, traceability, high throughput, and unified access control. The specific solution is as follows: In a first aspect, this application discloses a communication method between multi-source heterogeneous data warehouses, applied to a first heterogeneous data warehouse, comprising: Receive data transmission requests that include data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier; The preset cluster routing table is queried based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client, and resource queue corresponding to the data warehouse cluster identifier; Based on the metadata client, the query statement corresponding to the data source parameters is rewritten into a structured query language statement that creates a temporary table. The execution engine then executes the structured query language statement based on the computing resources limited by the resource queue to obtain the query result. The execution engine materializes the query results into a current temporary table, and the distributed file system client writes the current temporary table to a file under a temporary path on the distributed file system. After the current temporary table is written, the transfer task corresponding to the type of the second heterogeneous data warehouse is searched in the transfer task registry according to the type of the second heterogeneous data warehouse; During the transmission phase, the distributed file system client reads the data of the current temporary table in a streaming manner based on the temporary path, and writes the read data into the second heterogeneous data warehouse based on the transmission task.

[0006] Optionally, the preset cluster routing table maintains a four-tuple mapping relationship between different data warehouse cluster identifiers and metadata clients, execution engines, distributed file system clients, and resource queues; Accordingly, the step of rewriting the query statement corresponding to the data source parameters based on the metadata client into a Structured Query Language statement for creating a temporary table includes: The metadata client obtains the field types of the table specified in the data source parameters; Based on the field type, identify the string type fields in the query statement, and add escaping processing to the identified string type fields; Rewrite the query statement with added escaping as a Structured Query Language statement that creates a temporary table.

[0007] Optionally, before searching for the transmission task corresponding to the type of the second heterogeneous data warehouse from the transmission task registry based on the type of the second heterogeneous data warehouse, the method further includes: Scan all components that implement the data transmission task interface; the data transmission task interface is used to define a unified execution contract for transmission tasks. The type of the second heterogeneous data warehouse corresponding to each component is resolved based on the type identifier of the second heterogeneous data warehouse contained in the class name of each component; The parsed type of the second heterogeneous data warehouse and its corresponding components are registered in the transmission task registry with a mapping relationship.

[0008] Optionally, after searching the transmission task registry for the transmission task corresponding to the type of the second heterogeneous data warehouse based on the type of the second heterogeneous data warehouse, the method further includes: If there is no transmission task in the transmission task registry that corresponds to the type of the second heterogeneous data warehouse, a transmission failure message indicating that the type of the second heterogeneous data warehouse is not supported is returned.

[0009] Optionally, after the current temporary table write is complete, but before the transfer phase, the following may also be included: Set the current row number of the temporary table as the first row number; The number of rows in the second heterogeneous data warehouse that have been written to the data is determined as the second row number; Compare the numbers in the first row with the numbers in the second row; Randomly sample the target number of rows of data from the current temporary table, compare the actual number of fields in each row with the number of fields in the table structure of the current temporary table, and determine the field misalignment rate based on the comparison results. If the number of the first row is inconsistent with the number of the second row or the field misalignment rate is greater than the preset threshold, then the Structured Query Language self-correction is executed and the current temporary table is rematerialized; The self-correction of the structured query language includes: Add a regular expression replacement function to the first string field in the query statement to remove the separator, newline character, and carriage return character from the first field; Add an explicit type conversion function to the second field in the query statement that is not a string type to convert the second field to a string type; The query statement after adding the regular expression replacement function and the type conversion function is written as the updated query statement into the structured query language statement for creating the temporary table; A new temporary table name is generated based on the current temporary table name. The structured query language statement for creating the temporary table, which contains the updated query statement, is re-executed based on the new temporary table name to obtain a new temporary table. The new temporary table is then designated as the current temporary table.

[0010] Optionally, the name of the current temporary table may include the request identifier of the data transfer request; Accordingly, after writing the current temporary table to a file under a temporary path on the distributed file system via the distributed file system client, the process further includes: The duration of each current temporary table under the temporary path is checked according to the preset scheduling cycle. Delete the data file and metadata corresponding to the current temporary table whose duration exceeds the preset time threshold.

[0011] Optionally, before querying the preset cluster routing table based on the data warehouse cluster identifier, the method further includes: Verify whether the correspondence between the key carried in the data transmission request and the system name is valid; Verify whether the combination of the system name and the target source table belongs to the preset system-table permission whitelist; the target source table is the table included in the data source parameters that indicates the source of the data to be transmitted. If all verifications are successful, the operation of querying the preset cluster routing table based on the data warehouse cluster identifier is triggered; Otherwise, the data transmission request is rejected and written to the audit log.

[0012] Secondly, this application discloses a communication device between multi-source heterogeneous data warehouses, applied to a first heterogeneous data warehouse, comprising: The request receiving module is used to receive data transmission requests that include data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier; The data acquisition module is used to query a preset cluster routing table based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client, and resource queue corresponding to the data warehouse cluster identifier. The query result acquisition module is used to rewrite the query statement corresponding to the data source parameters into a structured query language statement for creating a temporary table based on the metadata client, and execute the structured query language statement through the execution engine based on the computing resources limited by the resource queue to obtain the query result; The current temporary table writing module is used to materialize the query result into a current temporary table through the execution engine, and to write the current temporary table into a file under a temporary path on the distributed file system through the distributed file system client. The transmission task lookup module is used to look up the transmission task corresponding to the type of the second heterogeneous data warehouse from the transmission task registry after the current temporary table is written; The data writing module is used to read the data of the current temporary table in a streaming manner based on the temporary path through the distributed file system client during the transmission phase, and write the read data into the second heterogeneous data warehouse based on the transmission task.

[0013] Thirdly, this application discloses an electronic device, including: Memory, used to store computer programs; A processor is used to execute computer programs to implement the steps of the aforementioned communication method between multi-source heterogeneous data warehouses.

[0014] Fourthly, a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the aforementioned communication method between multi-source heterogeneous data warehouses.

[0015] The first heterogeneous data warehouse of this application first receives a data transmission request containing data source parameters, the type of the second heterogeneous data warehouse, and a data warehouse cluster identifier; it queries a preset cluster routing table based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client, and resource queue corresponding to the data warehouse cluster identifier; based on the metadata client, it rewrites the query statement corresponding to the data source parameters into a structured query language statement to create a temporary table, and executes the structured query language statement through the execution engine based on the computing resources limited by the resource queue to obtain the query result; the execution engine materializes the query result into a current temporary table, and the distributed file system client writes the current temporary table to a file under a temporary path on the distributed file system; after the current temporary table is written, it searches the transmission task registry for a transmission task corresponding to the type of the second heterogeneous data warehouse based on the type of the second heterogeneous data warehouse; during the transmission phase, the distributed file system client streams the data of the current temporary table based on the temporary path, and writes the read data to the second heterogeneous data warehouse based on the transmission task. As can be seen, this application achieves automatic identification and shielding of underlying differences for heterogeneous data warehouse clusters by uniformly managing metadata clients, execution engines, distributed file system clients, and resource queues corresponding to different data warehouse clusters through a pre-set cluster routing table; by rewriting query statements into structured query language statements that create temporary tables and using the execution engine to materialize the data, and then writing the temporary tables to a unified temporary path via the distributed file system client, a standardized intermediate data layer is constructed, decoupling data extraction from data distribution; by searching for the corresponding transmission task in the transmission task registry according to the type of the second heterogeneous data warehouse and executing data writing, unified access and standardized distribution to different types of heterogeneous data warehouses are achieved, effectively reducing the adaptation complexity in multi-cluster, multi-target scenarios and improving the scalability and execution efficiency of the data transmission system. Attached Figure Description

[0016] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0017] Figure 1This is a flowchart of a communication method between multi-source heterogeneous data warehouses disclosed in this application; Figure 2 This is a schematic diagram of a communication device between multi-source heterogeneous data warehouses disclosed in this application; Figure 3 This is a structural diagram of an electronic device disclosed in this application. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. 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.

[0019] Currently, some related technologies suffer from at least the following drawbacks: First, the cluster is highly coupled with downstream systems. Each time a new downstream system is connected or a data warehouse cluster is replaced, adaptation code needs to be rewritten, resulting in poor scalability and high development costs. Second, different data warehouse clusters have significantly different execution engines. For example, Impala and Spark SQL differ in syntax, temporary table creation mechanisms, and HDFS path specifications, requiring business units to adapt separately, leading to high learning costs and a high risk of errors. Third, there is a lack of unified access control. The data warehouse is directly exposed to downstream systems, making fine-grained permission verification at the database, table, field, and system levels impossible, resulting in high security risks. Fourth, data consistency is difficult to guarantee. In scenarios such as multi-threaded batch processing, network transmission, and target system write failures, it is impossible to accurately count rows and locate failed tasks, posing a risk of data misalignment and time-consuming troubleshooting. Fifth, throughput is limited. Traditional ETL tools use single-point processing, resulting in significant performance bottlenecks when distributing terabyte-level data. Sixth, the lack of unified monitoring and alerting, coupled with scattered direct connections, results in invisible task status and untimely notification of anomalies, leading to low operational efficiency. To address these technical issues, this application discloses a communication method, apparatus, device, and medium between multi-source heterogeneous data warehouses. This method enables the connection of multiple heterogeneous data warehouse clusters to a unified two-stage standard pipeline and the distribution of data to various heterogeneous downstream target systems. While achieving linear scalability and masking differences in execution engines, it also ensures data consistency, traceability, high throughput, and unified access control.

[0020] Before detailing the specific implementation methods of each step, the overall architecture and formal abstraction upon which this invention is based will be explained to clearly understand the collaborative relationships between the various technical points. This invention proposes a three-layer architecture of "cluster routing identification, temporary table materialization relay, and transmission task registration and distribution," unifying data distribution into a two-stage standard pipeline from "Fetch" to "Push." ​​Specifically, the first heterogeneous data warehouse receives data transmission requests from an upper layer via a unified application programming interface (API) access layer, connects to different data warehouse clusters via a cluster routing identifier, and connects to different downstream heterogeneous target systems via a transmission task registry.

[0021] In this embodiment of the invention, the first heterogeneous data warehouse can be understood as the data exchange platform side that carries this method. The data warehouse clusters that can be connected to include, but are not limited to, offline data warehouse CDH (Cloudera's Distribution Including Apache Hadoop, an open-source big data platform) clusters, open-source Apache clusters, real-time data warehouse CDH clusters, and ClickHouse clusters. The second heterogeneous data warehouse that is connected to is the downstream heterogeneous target system, including but not limited to, search engines or Kafka, relational databases MySQL (My Structured Query Language, a relational database management system), and graph databases Neo4j, etc., more than ten kinds.

[0022] To clearly explain the principle behind the linear scaling effect of this invention, a single data exchange request can be modeled as a triple R=(S, T, Θ), where S is the data source, belonging to a data warehouse cluster c; T is the downstream target, belonging to a target type τ; and Θ is the parameter set, including filtering conditions, delimiters, encryption keys, etc. In the point-to-point schemes of related technologies, due to the pairwise coupling between the source-side cluster and the target-side type, the size of the adaptation code that needs to be maintained is proportional to the product of the number of clusters C and the number of target types TY, i.e., the complexity is |C|×|TY|. However, this invention reduces the adaptation complexity to |C|+|TY| by introducing two independent mappings: a "cluster routing function fC" and a "target distribution function fT". Based on this, when the number of clusters is 4 and the number of target types is 12, related technologies require maintaining 48 sets of adaptation code, while this invention only requires maintaining 16 sets, resulting in a relative reduction in adaptation cost of approximately 66.7%. This reduction in complexity from "product level" to "summation level" is the fundamental reason why this invention can achieve linear expansion in dozens of "cluster × target" combinations, and it is also the core improvement in scalability compared to related technologies.

[0023] Furthermore, this invention abstracts data exchange into a function composition form, that is, request R first passes through the cluster routing function fC(c) to obtain the intermediate materialization layer D. tmp The data is then written to the downstream Sink via the target distribution function fT(τ). The intermediate materialization layer, a standard path file on a distributed file system, acts as a "semantic glue" between the extraction and transmission phases, eliminating direct coupling between fC and fT. This achieves bidirectional decoupling: "write a cluster component once, reuse it in N downstream instances" and "write a downstream component once, reuse it in M ​​clusters." Compared to the strong binding between the source and target in related technologies, this bidirectional decoupling is the structural source of the scalability of this invention.

[0024] See Figure 1 As shown, this embodiment of the invention discloses a communication method between multi-source heterogeneous data warehouses, applied to a first heterogeneous data warehouse, including: Step S11: Receive a data transmission request containing data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier.

[0025] In this embodiment, the first heterogeneous data warehouse receives data transmission requests submitted by the caller through a unified application programming interface (API) access layer. The data transmission request carries at least three types of information: data source parameters, the type of the second heterogeneous data warehouse, and a data warehouse cluster identifier. The data source parameters indicate the location and range of the data to be transmitted on the source side, and may include, for example, the source table name, a list of fields to be queried, filter conditions, and field separators. The type of the second heterogeneous data warehouse indicates the target medium for data distribution, such as ES, HBase, Kafka, JDBC, Sqoop, ClickHouse, Neo4j, etc. The data warehouse cluster identifier indicates the data warehouse cluster to which the data to be extracted belongs.

[0026] In one specific implementation, the data transmission request can be organized into a hierarchical input parameter structure. For example, a data transmission parameter object can be used as the input parameter layer structure of the application programming interface, containing a data source parameter object, a target parameter object, a key field, and a notification address field. The data source parameter object can include a source table, field columns, filtering conditions, cluster identifier, field separator, etc. The target parameter object can be a container of "target type plus multiple dedicated parameter objects" to carry the dedicated write parameters required for different target types. The advantage of this hierarchical input parameter structure is that when adding a new downstream target type, only the corresponding dedicated parameter object needs to be expanded in the target parameter container, without changing the top-level structure of the data transmission parameters. This reflects the design principle of being open for expansion and closed for modification at the parameter level. As an alternative implementation, the data transmission request can also be carried in a flat key-value pair format or a structured data exchange format. This invention does not limit the specific form of the input parameters.

[0027] Step S12: Query the preset cluster routing table according to the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client and resource queue corresponding to the data warehouse cluster identifier.

[0028] In this embodiment, the first heterogeneous data warehouse is pre-configured with a cluster routing table. This table maintains mappings between different data warehouse cluster identifiers and metadata clients, execution engines, distributed file system clients, and resource queues. Upon receiving a data transmission request, the first heterogeneous data warehouse uses the data warehouse cluster identifier carried in the request as the key to query the cluster routing table, thereby obtaining the necessary metadata client, execution engine, distributed file system client, and resource queue for this extraction in one go. For example, when the data warehouse cluster identifier is apache, the first heterogeneous data warehouse obtains the Apache Hive metadata service client, the Spark-based execution engine, the Apache distributed file system client, and the corresponding resource queue by querying the table; when the data warehouse cluster identifier is rt, it switches to the Impala execution engine of the real-time data warehouse CDH cluster and the corresponding authentication link and resource queue by querying the table. Thus, the upper-layer calling logic does not need to be aware of the underlying execution engine, achieving masking of execution engine differences.

[0029] In one specific implementation, the aforementioned preset cluster routing table maintains a four-tuple mapping relationship between different data warehouse cluster identifiers and metadata clients, execution engines, distributed file system clients, and resource queues. That is, for each data warehouse cluster identifier, the routing table records its corresponding metadata client, execution engine, distributed file system client, and resource queue as a four-tuple. When looking up the table, the cluster identifier can be used as the key to retrieve the complete four-tuple at once, which can then be used in subsequent steps.

[0030] For example, the cluster routing table can be implemented using a cluster identifier enumeration, that is, an enumeration class maintains the mapping from cluster identifiers to four-tuples. As a further optimization, the "execution engine" object in the four-tuple can uniformly implement the same execution engine interface, so that the upper-layer calling code only programs against this unified interface when calling the execution engine, without being aware of whether the underlying layer is an Impala coordinating node, a Spark-based execution service, or a ClickHouse client. Thus, even if other types of execution engines are introduced later, such as StarRocks, Doris, or Iceberg, it is only necessary to extend the enumeration values ​​and supplement the corresponding component implementations, without intruding on the core engine call chain. Compared with the approach of hard-coding the engine selection logic in the caller code of related technologies, this implementation converges engine adaptation to "extending the enumeration and implementing the interface". Its technical advantage is that the scope of modification for adding engines is small, the risk is low, and it does not affect the stable operation of the existing chain.

[0031] As alternative implementations for cluster routing, this invention also provides several implementations, all of which achieve the equivalent effect of obtaining the corresponding component quadruple by cluster identifier. First, a dynamic mapping table in the configuration center can be used instead of an enumeration class. The mapping from cluster identifier to quadruple can be maintained in the configuration center, allowing for dynamic expansion or adjustment of the mapping relationship at runtime without redeploying the application. Second, a service provider interface mechanism can be used, where the class loader automatically discovers and loads the implementations of each cluster component at startup, enabling pluggable assembly of cluster components. Third, an annotation-based approach combined with reflection scanning can be used. For example, each cluster component implementation class can be annotated with an annotation carrying the cluster identifier, and the annotated components can be registered in the routing table at startup via reflection scanning. Each of the above alternative implementations has its own emphasis. For example, the configuration center-based approach facilitates dynamic adjustment at runtime, while the service provider interface or annotation scanning approach facilitates decoupled assembly of components. Those skilled in the art can choose to adopt one or a combination of these approaches according to actual needs.

[0032] Additionally, it should be noted that before querying the preset cluster routing table based on the data warehouse cluster identifier, a security authentication process for the data transmission request is included. This process specifically includes: verifying whether the correspondence between the key carried in the data transmission request and the system name is valid; verifying whether the combination of the system name and the target source table belongs to the preset system-table permission whitelist, where the target source table is the table included in the data source parameters that indicates the source of the data to be transmitted; if all verifications are successful, the operation of querying the preset cluster routing table based on the data warehouse cluster identifier is triggered; otherwise, the data transmission request is rejected and written to the audit log.

[0033] Specifically, before entering the extraction process, the first heterogeneous data warehouse performs two levels of verification on requests. The first level is identity verification, which verifies whether the correspondence between the key carried in the request and the system name is valid to confirm the legitimacy of the requester's identity. The second level is permission verification, which verifies whether the combination of the system name and the target source table falls within the preset system-table permission whitelist to confirm whether the system has the right to access the source table. Only when both identity verification and permission verification pass will the first heterogeneous data warehouse continue to query the cluster routing table and enter the subsequent extraction process; if either verification fails, the data transmission request is rejected, and this rejection event is written to the audit log.

[0034] This implementation constructs an authentication chain combining "key, system name, and table permissions," enabling the data warehouse to maintain the principle of least visibility while providing data distribution capabilities. Compared to related technologies, its improvement and technical effects are as follows: In related technologies, data warehouses are often directly exposed to downstream systems, making it difficult to achieve fine-grained access control at the system and table levels, and posing risks of unauthorized access and lack of security auditing. This implementation converges access control to a unified access side, where any system can only access source tables within the whitelist authorization range, thus eliminating unauthorized access caused by scattered direct connections; simultaneously, rejected requests are written to the audit log, making every unauthorized attempt traceable. Therefore, since all distribution requests must first pass unified three-factor authentication before accessing data, the external exposure of the data warehouse is significantly reduced, and security is significantly improved compared to direct connection solutions. As alternative implementation methods, identity verification can use not only keys, but also standard open authorization protocols or token mechanisms; permission verification can use not only system-table whitelists, but also centralized permission management components to achieve finer-grained control at the database, table, and field levels, or link with the enterprise's unified account directory for authentication.

[0035] Step S13: Based on the metadata client, rewrite the query statement corresponding to the data source parameters into a structured query language statement for creating a temporary table, and execute the structured query language statement through the execution engine based on the computing resources limited by the resource queue to obtain the query result.

[0036] In this embodiment, the first heterogeneous data warehouse assembles a query statement corresponding to the user's intent based on the data source parameters. Then, based on the metadata client obtained in step S12, the query statement is rewritten as a Structured Query Language (SCL) statement for creating a temporary table. This SCL statement is then executed within the computing resources defined by the resource queue obtained in step S12 through the execution engine obtained in step S12 to obtain the query result. For example, the SCL statement for creating the temporary table can use the following template: `CREATE TABLE bdp_outlet_tmp.{reqId} STOREDAS {format} ROW FORMAT DELIMITED FIELDS TERMINATED BY '{sep}' AS SELECT{columns} FROM {srcTable} WHERE {condition};` where `{reqId}` is the request identifier for this data transmission request, `{format}` is the intermediate storage format, `{sep}` is the field separator, `{columns}` is the list of fields to be queried, `{srcTable}` is the source table, and `{condition}` is the filtering condition. Each placeholder in the above template can be populated by data source parameters. By wrapping the user's query statement as a temporary table creation statement, the query results can be materialized into a temporary table located in a specified temporary database, laying the foundation for subsequent cross-engine seamless reading.

[0037] In this way, extraction tasks from different data warehouse clusters and with different priorities are constrained to be executed within their respective resource queues, thereby achieving resource isolation and preventing large-scale data extraction tasks from exhausting cluster resources and affecting other tasks.

[0038] Further, in this embodiment, when the query statement corresponding to the data source parameters is rewritten into a Structured Query Language (SCL) statement for creating a temporary table based on the metadata client, the field types of the table specified in the data source parameters are obtained through the metadata client; string type fields in the query statement are identified based on the field types, and escaping processing is added to the identified string type fields; the query statement with added escaping processing is then rewritten into a SCL statement for creating a temporary table. Specifically, the first heterogeneous data warehouse accesses the metadata service where the source table resides through the metadata client obtained in step S12, and reads the field types of each field in the source table, such as string type, integer, floating-point type, date type, etc. Since the metadata client corresponds one-to-one with the cluster, regardless of which cluster the source table is located in, its field types can be accurately obtained through the corresponding metadata client, providing a basis for subsequent type-based processing. Based on the obtained field types, the first heterogeneous data warehouse identifies string type fields from the fields involved in the query statement and adds escaping processing to these string type fields. The reason for escaping string type fields is that their values ​​may naturally contain characters that are the same as the field delimiter, or they may contain newline or carriage return characters. If these are directly materialized into delimited text without processing, field misalignment will occur during subsequent delimiter parsing. By adding escaping processing to string type fields when rewriting the query statement, delimiter conflicts can be avoided at the source of data materialization, thereby reducing the probability of field misalignment. The first heterogeneous data warehouse rewrites the escaped query statement into a Structured Query Language (SCL) statement for creating a temporary table, following the aforementioned temporary table creation statement template, and then submits it to the execution engine for execution. The resulting temporary table data has clear and unambiguous field boundaries.

[0039] This implementation identifies string-type fields based on metadata and applies escaping during the extraction phase, shifting the prevention of field misalignment to the source of data materialization, thereby reducing the occurrence of field misalignment at the source. Based on this, since most field misalignment problems originate from special characters in string fields, applying escaping to string fields at the source can significantly reduce the proportion of subsequent field misalignment checks triggering self-correction, thus reducing the resource overhead caused by repeated materialization.

[0040] In one specific implementation, the intermediate storage format in the temporary table creation statement can be adaptively selected by the first heterogeneous data warehouse based on the type of the second heterogeneous data warehouse; that is, the target type determines the intermediate storage format used by the current temporary table. When the downstream target type is a columnar storage engine such as ClickHouse, the temporary table data generated by the CTAS (CREATE TABLE AS SELECT) statement uses the Parquet columnar storage format because ClickHouse's HDFS table engine natively supports columnar storage formats. It can perform SIMD (Single Instruction Multiple Data) columnar vectorization import without parsing the row structure, with a throughput approximately 3 to 5 times that of CSV (Comma-Separated Values) format. When the downstream target type is a row-oriented downstream system such as HBase, JDBC, MySQL, or Kafka, the temporary table data generated by the CTAS statement uses the CSV format because the parsing overhead of the CSV format is negligible and it has better compatibility.

[0041] In another possible implementation, the temporary table format can be chosen to use a uniform Parquet format, sacrificing some compatibility for better storage compression ratio and read performance. The columnar compression ratio η = B_c / B_p is typically between 2.5 and 6 (where B_c is the average number of bytes per row in CSV and B_p is the average number of bytes per row in Parquet), so choosing the format alone can reduce network and disk I / O (Input / Output) overhead by 50% to 80%.

[0042] In another possible implementation, the format of the temporary table can be dynamically selected based on the data volume: CSV format is used for small data volumes to simplify the processing flow, while Parquet format is switched for large data volumes to improve transmission efficiency.

[0043] In another possible implementation, Apache Arrow can be used as the in-memory transfer format to achieve zero-copy transmission, further reducing the overhead of data serialization and deserialization in scenarios requiring extreme performance.

[0044] Step S14: The query result is materialized into a current temporary table by the execution engine, and the current temporary table is written to a file under a temporary path on the distributed file system by the distributed file system client.

[0045] In this embodiment, after the execution engine executes the Structured Query Language (SCL) statement that creates a temporary table, the query result is materialized into the current temporary table. The first heterogeneous data warehouse then writes the data of this current temporary table to a file under a temporary path on the distributed file system through a distributed file system client. For example, the temporary path can adopt the following standard path specification: / user / hive / warehouse / bdp_outlet_tmp.db / {reqId} / , that is, using the unified temporary library bdp_outlet_tmp as the parent directory and the request identifier reqId as the subdirectory, so that the temporary table data corresponding to each data transfer request is isolated and stored in an independent directory.

[0046] In one specific implementation, the name of the current temporary table includes the request identifier of the data transmission request. Accordingly, after writing the current temporary table to a file under a temporary path on the distributed file system via the distributed file system client, the process further includes: checking the duration of each current temporary table under the temporary path according to a preset scheduling period; and deleting the data files and metadata corresponding to current temporary tables whose duration exceeds a preset time threshold. Specifically, since the temporary table name includes the request identifier, the temporary tables corresponding to each data transmission request are unique in name, which facilitates locating the temporary data according to the request and naturally avoids naming conflicts between temporary tables in different requests. The first heterogeneous data warehouse, through scheduling tasks, inspects the temporary path according to a preset scheduling period, calculates the duration of each temporary table since its creation, and for temporary tables whose duration exceeds the preset time threshold, deletes their corresponding data files from the distributed file system and removes their corresponding metadata from the metadata service, thereby reclaiming the storage capacity of the distributed file system and the metadata entries of the metadata service.

[0047] Temporary tables, serving as intermediate materialized carriers between the extraction and transmission phases, will continuously consume distributed file system capacity and metadata service resources if not cleaned up. This implementation method forms a closed loop for the reclamation of temporary resources through timed cleanup based on their duration. Its cleanup strategy is equivalent to eviction of expired objects based on their creation time. Therefore, under stable operating conditions, the amount of temporary data occupied will be constrained to a limited level related to the scheduling cycle, time threshold, and the number of requests per unit time, and will not grow indefinitely with the accumulation of historical requests, thus avoiding the risk of storage resource exhaustion caused by the long-term accumulation of temporary data.

[0048] Step S15: After the current temporary table is written, search the transfer task corresponding to the type of the second heterogeneous data warehouse in the transfer task registry according to the type of the second heterogeneous data warehouse.

[0049] In this embodiment, the first heterogeneous data warehouse pre-maintains a transmission task registry. This registry maintains a mapping relationship between types and transmission tasks, using the type of the second heterogeneous data warehouse as the key and the transmission task capable of writing data to that type as the value. After the current temporary table is written, the first heterogeneous data warehouse queries the transmission task registry using the type of the second heterogeneous data warehouse carried in the data transmission request as the key, thereby obtaining the transmission task corresponding to that type. Since this lookup process is a key-based hash table lookup, its time complexity is constant, meaning that the time required to dispatch a transmission task remains essentially constant regardless of the number of registered transmission tasks. Compared to the distribution method in related technologies that uses conditional branches to determine the target type one by one, this step does not lead to a linear increase in distribution time as the number of transmission tasks increases, and no changes to the distribution logic are required when adding a new target type, resulting in better scalability.

[0050] To construct the aforementioned transmission task registry, in one specific implementation, before searching for a transmission task corresponding to the type of the second heterogeneous data warehouse in the transmission task registry based on the type of the second heterogeneous data warehouse, all components implementing the data transmission task interface are scanned. The data transmission task interface defines a unified execution contract for transmission tasks. The type of the second heterogeneous data warehouse corresponding to each component is parsed based on the type identifier of the second heterogeneous data warehouse contained in the class name of each component. The parsed type of the second heterogeneous data warehouse and its corresponding component are registered in the transmission task registry with a mapping relationship. Specifically, this invention predefines a data transmission task interface, and all transmission tasks targeting specific downstream target types implement this interface. The data transmission task interface defines a unified execution contract for transmission tasks, that is, it agrees on a unified method signature exposed by the transmission task, so that transmission tasks of different downstream targets have a consistent calling method when invoked. The first heterogeneous data warehouse scans all components implementing the data transmission task interface during the container startup phase. For example, a callback can be triggered after the container completes the initialization of each component, in which it is determined whether the current component implements the data transmission task interface; if so, it is included in the registration scope. Specifically, each component implementing the data transmission task interface is named according to a pre-agreed naming convention, and its class name contains a type identifier for the corresponding downstream target type. The first heterogeneous data warehouse, following this naming convention, parses the type of the second heterogeneous data warehouse corresponding to the component from its class name. For example, if the component class name is agreed to consist of a "type identifier" plus a fixed suffix, then the remaining part after removing the fixed suffix from the component name can be used as the type identifier, and its first letter can be converted to lowercase to obtain the target type corresponding to the component. For example, for a component named "esDataPushTask", removing the fixed suffix yields "es", meaning the target type corresponding to this component is ES. The first heterogeneous data warehouse uses the parsed target type as the key and the corresponding component as the value, writing them into the transmission task registry in a mapping relationship. At this point, the transmission task registry is completed and can be used by this invention for table lookup and dispatch according to the target type.

[0051] As alternative implementations for automatic registration of transmission tasks, this invention also provides the following several implementations. First, all components implementing the data transmission task interface can be collected and registered at once during the container initialization phase by batch acquiring components by type. Second, a service provider interface or automatic generation of registration metadata during compilation can be used to generate a registration list during the packaging or compilation phase, which is then directly loaded at runtime. Third, an annotation processor can be used to scan components with specific annotations during compilation and generate registration code. All of the above alternative implementations can achieve the equivalent effect of registering each transmission task to the registry according to its target type. The main difference lies in the timing of the registration action, such as collection at runtime, generation during packaging, or generation during compilation. Those skilled in the art can choose one based on the build process and deployment method.

[0052] Furthermore, if no transmission task corresponding to the type of the second heterogeneous data warehouse exists in the transmission task registry, a transmission failure message indicating that the type of the second heterogeneous data warehouse is not supported is returned. Specifically, when the type of the second heterogeneous data warehouse specified by the caller in the data transmission request has not been registered, i.e., no corresponding transmission task is found in the transmission task registry, the first heterogeneous data warehouse does not enter the transmission stage, but directly returns a clear failure message to the caller, indicating that the target type is currently unsupported. For example, the failure message may carry a list of target types currently supported by the system, so that the caller can modify the request accordingly.

[0053] Compared to related technologies where runtime exceptions or even partial writes occur during the transmission phase due to unsupported target types, this implementation verifies the availability of the target type before entering the transmission phase, thus achieving failure prevention. Therefore, since failures are intercepted before data is written downstream, dirty or partially written data can be avoided downstream due to unsupported target types, ensuring the integrity of downstream data. Furthermore, explicit failure prompts, compared to underlying runtime exceptions, facilitate faster problem location and correction by the caller, shortening troubleshooting time.

[0054] In one specific implementation, before entering the transmission stage after the current temporary table is written, a process for verifying the data quality of the current temporary table and, if necessary, performing a self-correction process is included to act as a data quality guardrail during the extraction stage. Specifically, this includes: determining the number of rows in the current temporary table as the first row count; determining the number of rows written to the second heterogeneous data warehouse as the second row count; comparing the first row count with the second row count; randomly sampling a target number of rows of data from the current temporary table, comparing the actual number of fields in each row with the number of fields in the table structure of the current temporary table, and determining the field misalignment rate based on the comparison results; if the first row count is inconsistent with the second row count or the field misalignment rate is greater than a preset threshold, then performing a structured query language self-correction and rematerializing the current temporary table. In this context, the self-correcting structured query language includes: adding a regular expression replacement function to the first string field in the query statement to remove delimiters, newline characters, and carriage returns from the first field; adding an explicit type conversion function to the second non-string field in the query statement to convert the second field to a string type; writing the query statement with the added regular expression replacement function and the type conversion function as the updated query statement into the structured query language statement for creating a temporary table; generating a new temporary table name based on the current temporary table name; re-executing the structured query language statement for creating a temporary table with the updated query statement written in it based on the new temporary table name to obtain a new temporary table; and determining the new temporary table as the current temporary table.

[0055] Specifically, the first heterogeneous data warehouse counts the number of rows in the current temporary table, denoted as the first row count; after the data is written to the second heterogeneous data warehouse, it counts the actual number of rows written to the second heterogeneous data warehouse, denoted as the second row count; then, the first row count and the second row count are compared. Ideally, the two should be equal. If they are inconsistent, it indicates that data loss or duplication occurred during transmission, i.e., there is data inconsistency at the row count level. For example, for relational databases or targets written via data export tools, a counting query can be performed on the target database after export, and its result can be compared with the row count of the source temporary table to achieve row count consistency verification. The first heterogeneous data warehouse randomly selects the target number of rows from the current temporary table as a sample. For each row in the sample, it counts the actual number of fields obtained after splitting by the delimiter and compares it with the number of fields defined in the current temporary table structure; if the actual number of fields in a row is not equal to the number of fields in the table structure, it is determined that the row has a field misalignment. The first heterogeneous data warehouse then calculates the proportion of rows with field misalignment in the sample to the total number of sampled rows, as the field misalignment rate. As can be seen, the field misalignment rate reflects the data quality of the current temporary table at the field boundary level. When the number of rows in the first row is inconsistent with the number of rows in the second row, or when the field misalignment rate exceeds a preset threshold, the first heterogeneous data warehouse determines that the current temporary table has a data quality problem, thereby triggering the Structured Query Language (SCL) self-correction and rematerializing the current temporary table. The preset threshold can be set according to the business requirements for data quality. For example, in a scenario with zero tolerance for field misalignment, the preset threshold can be set to zero, meaning that self-correction is triggered as long as any row of fields is misaligned during sampling. During the self-correction process, the first heterogeneous data warehouse applies a regular expression replacement function to string type fields in the query statement to replace and remove separators, newline characters, and carriage returns in the field values, thereby eliminating the interference of string values ​​on field boundaries; at the same time, it applies an explicit type conversion function to non-string type fields to uniformly convert them to string type, thereby eliminating ambiguity in values ​​caused by implicit type conversion or format differences. Subsequently, the first heterogeneous data warehouse reassembles the temporary table statement with the updated query statement, and re-executes it based on the new temporary table name derived from the original temporary table name to obtain a new temporary table. This new temporary table is then used as the current temporary table in subsequent transmission stages. For example, a version suffix can be appended to the original table name as the new temporary table name, such as appending a version identifier to the request identifier. This ensures that the temporary tables before and after self-correction are distinct and do not overwrite each other, facilitating comparison and guaranteeing the traceability of the self-correction process.

[0056] In this way, this implementation method specifically removes these special characters and forces non-string fields to be converted to strings, thus eliminating the vast majority of field misalignment alarms. Actual testing shows it can eliminate over 90% of field misalignment alarms, significantly reducing the proportion of manual intervention required due to data quality issues and improving the automation level and data accuracy of the distribution process. As an alternative implementation method, in addition to the aforementioned row count comparison and sampled field count comparison, data quality verification can also introduce a data quality rule engine for more comprehensive post-verification. Self-correction triggering can be performed either after writing based on the second row count, as described above, or before the transmission stage based solely on the sampled field misalignment rate of the temporary table itself.

[0057] Step S16: During the transmission phase, the distributed file system client reads the data of the current temporary table in a streaming manner based on the temporary path, and writes the read data into the second heterogeneous data warehouse based on the transmission task.

[0058] In this embodiment, during the transmission phase, the first heterogeneous data warehouse streams data from the current temporary table in a temporary path via a distributed file system client, and then calls the found transmission task to write the read data into the second heterogeneous data warehouse. The advantage of using streaming reading instead of loading the entire temporary table data into memory at once is that, in scenarios involving TB-level large data volumes, memory usage can be maintained at a limited level related to the batch size, rather than increasing with the total data volume. This effectively avoids the memory overflow problem caused by one-time loading in related technologies, ensuring the stability of the transmission process in large data volume scenarios.

[0059] In one specific implementation, the extraction and transmission phases can be executed asynchronously. For example, after the first heterogeneous data warehouse completes parameter and permission verification of the data transmission request, it can separate the two time-consuming operations—the extraction and transmission phases—from the main calling thread of the application programming interface (API) and delegate them to an asynchronous thread pool. This reduces the synchronous response time of the API to the level of only the verification time. Based on this, assuming the total time for a single request consists of verification time, extraction time, transmission time, and notification time, then under asynchronous execution, the synchronous response time returned by the API to the caller is approximately equal to the verification time, typically controllable within two hundred milliseconds, while the longer extraction and transmission operations proceed concurrently in the background. Thus, this implementation simultaneously achieves the dual benefits of high API availability and high task concurrency.

[0060] In one specific implementation, when the aforementioned transmission task writes data to different types of second heterogeneous data warehouses, it can adopt a differentiated optimization strategy that matches the characteristics of each target. That is, under a unified two-stage pipeline, a dedicated transmission task adapted to each downstream target is attached, thereby providing near-native tool performance for each link while maintaining architectural uniformity. Several examples are given below.

[0061] For HBase-type targets, the transfer task can use a custom thread pool for multi-threaded concurrent writing, and disable write-ahead logging in permissible scenarios to improve write throughput. Based on this, if the number of concurrent threads is n and the number of write requests per second per thread is r, then the total throughput is approximately the product of n and r, with its upper limit constrained by the number of servers in the cluster. For relational database batch write targets, the transfer task can use prepared statements combined with batch processing submission, submitting at a fixed batch size. If the total number of rows to be written is N and the batch size is B, then the number of submissions is approximately N divided by B rounded up. Transaction overhead is positively correlated with the number of submissions, therefore there exists a batch size value that optimizes the total time. For relational database targets supporting direct file injection, the transfer task can directly connect the data stream from the distributed file system to the database loading entry point, thus eliminating the intermediate disk write stage and reducing the complexity of intermediate disk input / output from being related to the data volume to constant level. For search engine-type targets, the transfer task can use batch request submission combined with index aliases for second-level switching, thereby achieving near-zero-pause gray-scale switching in full refresh scenarios. For message queue-type targets, the transmission task can employ asynchronous sending combined with batch and backlog parameter control, and perform whitelist verification on topics. Its end-to-end latency approximately depends on the greater of the backlog waiting time and the batch fill time. For targets written via data export tools, the transmission task can remotely drive the export tool and export in parallel according to the number of mapped tasks. Its parallel speedup is limited by the number of data blocks on the source side and the number of connections to the target database. For file-type targets, the transmission task can slice large files by row number and generate control files to identify transmission integrity, thus facilitating breakpoint resumption. For columnar analytical database targets, the transmission task can directly import data in columnar storage using its distributed file system table engine, significantly reducing input / output overhead through column-vectorized execution.

[0062] As can be seen, this application achieves automatic identification and shielding of underlying differences for heterogeneous data warehouse clusters by uniformly managing metadata clients, execution engines, distributed file system clients, and resource queues corresponding to different data warehouse clusters through a pre-set cluster routing table; by rewriting query statements into structured query language statements that create temporary tables and using the execution engine to materialize the data, and then writing the temporary tables to a unified temporary path via the distributed file system client, a standardized intermediate data layer is constructed, decoupling data extraction from data distribution; by searching for the corresponding transmission task in the transmission task registry according to the type of the second heterogeneous data warehouse and executing data writing, unified access and standardized distribution to different types of heterogeneous data warehouses are achieved, effectively reducing the adaptation complexity in multi-cluster, multi-target scenarios and improving the scalability and execution efficiency of the data transmission system.

[0063] In one specific implementation, the first heterogeneous data warehouse manages the entire lifecycle of data transmission requests in the form of a state machine. For example, the request state may include initialization, verified, in progress, completed, and exception states. The request flows strictly between these states sequentially, and each process node is independently logged. For instance, after a request is received, it enters the initialization state; after parameter and permission verification passes, it enters the verified state; after being asynchronously dispatched, it enters the in progress state; after successful extraction and transmission, it enters the completed state; and if any step fails, it enters the exception state.

[0064] In one specific implementation, a corresponding notification action is triggered upon the request entering the final state. For example, an abnormal state triggers an alarm card notification; both completed and abnormal states trigger callback notifications to the caller; and metrics are simultaneously reported to the monitoring system. Thus, the operations and maintenance team and the business team can promptly obtain task results without polling, achieving state-driven event notification. Compared to related technologies that lack unified state management and require active polling of task status, this implementation uses request identifiers throughout the end-to-end logs, making the entire process of each distribution traceable, and reducing the time to locate operational problems from hours to minutes. It is worth emphasizing that this invention also treats "notification" as a legitimate final state trigger action of the state machine. The final state triggers the following three callbacks: Lark card alarm (only triggered by ERROR); caller HTTP (Hypertext Transfer Protocol) callback (triggered by both DONE and ERROR); and monitoring metric reporting (Prometheus).

[0065] This state-driven event model allows operations and business teams to achieve true zero-polling.

[0066] Furthermore, in one specific implementation, the following high availability and data consistency guarantees can be provided: First, request idempotency: Using the request identifier as the deduplication key, duplicate submissions of the same request are directly rejected by the state machine, avoiding repeated execution. Second, timed cleanup of temporary tables: A scheduled task cleans up expired temporary tables according to time thresholds, reclaiming distributed file system capacity and metadata. Third, row count verification: For targets written via export tools or relational databases, a back-link is made to the target database after export to count rows and compare them with the row count on the source side. Fourth, multi-server distributed coordination: The execution server name is registered in the request status record to prevent the same request from being executed concurrently by multiple nodes. Fifth, power failure recovery: Based on request status table replay, unfinished requests can continue to be executed after a system restart. The above guarantees further improve the stability and data consistency of this invention in a production environment.

[0067] In one specific implementation, considering the inherent mismatch between upstream output rate and downstream receiving rate in big data delivery scenarios, the first heterogeneous data warehouse can embed token bucket-based backpressure control during the transmission phase. For example, the system replenishes tokens to the token bucket at a fixed rate, with an upper limit on the bucket's capacity. Before each downstream write operation, tokens corresponding to the current batch of data are retrieved from the bucket. When there are insufficient tokens in the bucket, the processor time is actively allocated to wait until tokens are replenished. Thus, the downstream write rate is smoothly constrained to a level matching the token replenishment rate, avoiding cascading failures caused by backpressure from downstream write failures. This approach is superior to related technologies that lack rate constraints and are prone to avalanche-like failures when downstream is overloaded.

[0068] This invention incorporates token bucket-based backpressure control during the Push phase: ; Where C is the maximum capacity of the token bucket (the maximum number of tokens that can be accumulated); r is the token generation rate (how many tokens are generated per second). Tokens(t) is the number of tokens remaining in the token bucket at time t; For time intervals; The number of tokens consumed for this Push operation (usually proportional to the number of data rows or bytes).

[0069] This implementation improves the stability of the downlink in scenarios where upstream and downstream rates do not match by using rate shaping.

[0070] See Figure 2 As shown, this embodiment of the invention discloses a communication device between multi-source heterogeneous data warehouses, applied to a first heterogeneous data warehouse, comprising: The request receiving module 11 is used to receive a data transmission request containing data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier; Data acquisition module 12 is used to query a preset cluster routing table based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client and resource queue corresponding to the data warehouse cluster identifier; The query result acquisition module 13 is used to rewrite the query statement corresponding to the data source parameters into a structured query language statement for creating a temporary table based on the metadata client, and execute the structured query language statement through the execution engine based on the computing resources limited by the resource queue to obtain the query result; The current temporary table writing module 14 is used to materialize the query result into a current temporary table through the execution engine, and to write the current temporary table into a file under a temporary path on the distributed file system through the distributed file system client. The transmission task lookup module 15 is used to look up the transmission task corresponding to the type of the second heterogeneous data warehouse from the transmission task registry after the current temporary table is written; The data writing module 16 is used to read the data of the current temporary table in a streaming manner based on the temporary path through the distributed file system client during the transmission phase, and write the read data into the second heterogeneous data warehouse based on the transmission task.

[0071] Since the embodiments of the device part correspond to the embodiments described above, please refer to the embodiments described in the method part for the embodiments of the device part, and will not be repeated here.

[0072] As can be seen, this application achieves automatic identification and shielding of underlying differences for heterogeneous data warehouse clusters by uniformly managing metadata clients, execution engines, distributed file system clients, and resource queues corresponding to different data warehouse clusters through a pre-set cluster routing table; by rewriting query statements into structured query language statements that create temporary tables and using the execution engine to materialize the data, and then writing the temporary tables to a unified temporary path via the distributed file system client, a standardized intermediate data layer is constructed, decoupling data extraction from data distribution; by searching for the corresponding transmission task in the transmission task registry according to the type of the second heterogeneous data warehouse and executing data writing, unified access and standardized distribution to different types of heterogeneous data warehouses are achieved, effectively reducing the adaptation complexity in multi-cluster, multi-target scenarios and improving the scalability and execution efficiency of the data transmission system.

[0073] Furthermore, embodiments of this application also disclose an electronic device, Figure 3 This is a structural diagram of an electronic device 20 according to an exemplary embodiment. The content of the diagram should not be construed as limiting the scope of this application.

[0074] Figure 3This is a schematic diagram of the structure of an electronic device 20 provided in an embodiment of this application. Specifically, the electronic device 20 may include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25, and a communication bus 26. The memory 22 stores a computer program, which is loaded and executed by the processor 21 to implement the relevant steps in the communication method between multi-source heterogeneous data warehouses disclosed in any of the foregoing embodiments. Furthermore, the electronic device 20 in this embodiment may specifically be an electronic computer.

[0075] In this embodiment, the power supply 23 is used to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and external devices, and the communication protocol it follows can be any communication protocol applicable to the technical solution of this application, and is not specifically limited here; the input / output interface 25 is used to acquire external input data or output data to the outside world, and its specific interface type can be selected according to specific application needs, and is not specifically limited here.

[0076] In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, disk or optical disk, etc. The resources stored thereon can include operating system 221, computer program 222, etc., and the storage method can be temporary storage or permanent storage.

[0077] The operating system 221 is used to manage and control the various hardware devices on the electronic device 20 and the computer program 222, which may be Windows Server, Netware, Unix, Linux, etc. In addition to including a computer program capable of performing the communication method between multi-source heterogeneous data warehouses executed by the electronic device 20 as disclosed in any of the foregoing embodiments, the computer program 222 may further include a computer program capable of performing other specific tasks.

[0078] Furthermore, this application also discloses a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by a processor, it implements the aforementioned communication method between multi-source heterogeneous data warehouses. Specific steps of this method can be found in the corresponding content disclosed in the foregoing embodiments, and will not be repeated here.

[0079] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the apparatus disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple; relevant parts can be referred to in the method section.

[0080] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

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

[0082] Finally, 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 a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0083] The technical solutions provided in this application have been described in detail above. Specific examples have been used to illustrate the principles and implementation methods of this application. The descriptions of the above embodiments are only for the purpose of helping to understand the methods and core ideas of this application. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A communication method between multi-source heterogeneous data warehouses, characterized in that, Applied to the first heterogeneous data warehouse, including: Receive data transmission requests that include data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier; The preset cluster routing table is queried based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client, and resource queue corresponding to the data warehouse cluster identifier; Based on the metadata client, the query statement corresponding to the data source parameters is rewritten into a structured query language statement that creates a temporary table. The execution engine then executes the structured query language statement based on the computing resources limited by the resource queue to obtain the query result. The execution engine materializes the query results into a current temporary table, and the distributed file system client writes the current temporary table to a file under a temporary path on the distributed file system. After the current temporary table is written, the transfer task corresponding to the type of the second heterogeneous data warehouse is searched in the transfer task registry according to the type of the second heterogeneous data warehouse; During the transmission phase, the distributed file system client reads the data of the current temporary table in a streaming manner based on the temporary path, and writes the read data into the second heterogeneous data warehouse based on the transmission task.

2. The communication method between multi-source heterogeneous data warehouses according to claim 1, characterized in that, The preset cluster routing table maintains a four-tuple mapping relationship between different data warehouse cluster identifiers and metadata clients, execution engines, distributed file system clients, and resource queues; Accordingly, the step of rewriting the query statement corresponding to the data source parameters based on the metadata client into a Structured Query Language statement for creating a temporary table includes: The metadata client obtains the field types of the table specified in the data source parameters; Based on the field type, identify the string type fields in the query statement, and add escaping processing to the identified string type fields; Rewrite the query statement with added escaping as a Structured Query Language statement that creates a temporary table.

3. The communication method between multi-source heterogeneous data warehouses according to claim 1, characterized in that, Before searching for the transmission task corresponding to the type of the second heterogeneous data warehouse from the transmission task registry according to the type of the second heterogeneous data warehouse, the method further includes: Scan all components that implement the data transmission task interface; the data transmission task interface is used to define a unified execution contract for transmission tasks. The type of the second heterogeneous data warehouse corresponding to each component is resolved based on the type identifier of the second heterogeneous data warehouse contained in the class name of each component; The parsed type of the second heterogeneous data warehouse and its corresponding components are registered in the transmission task registry with a mapping relationship.

4. The communication method between multi-source heterogeneous data warehouses according to claim 1, characterized in that, After searching the transmission task registry for the type of the second heterogeneous data warehouse according to its type, the process further includes: If there is no transmission task in the transmission task registry that corresponds to the type of the second heterogeneous data warehouse, a transmission failure message indicating that the type of the second heterogeneous data warehouse is not supported is returned.

5. The communication method between multi-source heterogeneous data warehouses according to claim 1, characterized in that, After the current temporary table is written, before the transfer phase, the following is also included: Set the current row number of the temporary table as the first row number; The number of rows in the second heterogeneous data warehouse that have been written to the data is determined as the second row number; Compare the numbers in the first row with the numbers in the second row; Randomly sample the target number of rows of data from the current temporary table, compare the actual number of fields in each row with the number of fields in the table structure of the current temporary table, and determine the field misalignment rate based on the comparison results. If the number of the first row is inconsistent with the number of the second row or the field misalignment rate is greater than the preset threshold, then the Structured Query Language self-correction is executed and the current temporary table is rematerialized; The self-correction of the structured query language includes: Add a regular expression replacement function to the first string field in the query statement to remove the separator, newline character, and carriage return character from the first field; Add an explicit type conversion function to the second field in the query statement that is not a string type to convert the second field to a string type; The query statement after adding the regular expression replacement function and the type conversion function is written as the updated query statement into the structured query language statement for creating the temporary table; A new temporary table name is generated based on the current temporary table name. The structured query language statement for creating the temporary table, which contains the updated query statement, is re-executed based on the new temporary table name to obtain a new temporary table. The new temporary table is then designated as the current temporary table.

6. The communication method between multi-source heterogeneous data warehouses according to claim 1, characterized in that, The name of the current temporary table contains the request identifier of the data transmission request; Accordingly, after writing the current temporary table to a file under a temporary path on the distributed file system via the distributed file system client, the process further includes: The duration of each current temporary table under the temporary path is checked according to the preset scheduling cycle. Delete the data file and metadata corresponding to the current temporary table whose duration exceeds the preset time threshold.

7. The communication method between multi-source heterogeneous data warehouses according to any one of claims 1 to 6, characterized in that, Before querying the preset cluster routing table based on the data warehouse cluster identifier, the method further includes: Verify whether the correspondence between the key carried in the data transmission request and the system name is valid; Verify whether the combination of the system name and the target source table belongs to the preset system-table permission whitelist; the target source table is the table included in the data source parameters that indicates the source of the data to be transmitted. If all verifications are successful, the operation of querying the preset cluster routing table based on the data warehouse cluster identifier is triggered; Otherwise, the data transmission request is rejected and written to the audit log.

8. A communication device for communication between multi-source heterogeneous data warehouses, characterized in that, Applied to the first heterogeneous data warehouse, including: The request receiving module is used to receive data transmission requests that include data source parameters, the type of the second heterogeneous data warehouse, and the data warehouse cluster identifier; The data acquisition module is used to query a preset cluster routing table based on the data warehouse cluster identifier to determine the metadata client, execution engine, distributed file system client, and resource queue corresponding to the data warehouse cluster identifier. The query result acquisition module is used to rewrite the query statement corresponding to the data source parameters into a structured query language statement for creating a temporary table based on the metadata client, and execute the structured query language statement through the execution engine based on the computing resources limited by the resource queue to obtain the query result; The current temporary table writing module is used to materialize the query result into a current temporary table through the execution engine, and to write the current temporary table into a file under a temporary path on the distributed file system through the distributed file system client. The transmission task lookup module is used to look up the transmission task corresponding to the type of the second heterogeneous data warehouse from the transmission task registry after the current temporary table is written; The data writing module is used to read the data of the current temporary table in a streaming manner based on the temporary path through the distributed file system client during the transmission phase, and write the read data into the second heterogeneous data warehouse based on the transmission task.

9. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing a computer program to implement the steps of the communication method between multi-source heterogeneous data warehouses as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, A computer program is stored on a computer-readable storage medium, which, when executed by a processor, implements the steps of the communication method between multi-source heterogeneous data warehouses as described in any one of claims 1 to 7.

Images