Gaussian-to-Gaussian efficient data transmission service system

Abstract

The invention discloses a Gaussian-to-Gaussian efficient data transmission service system, and belongs to the technical field of data processing. Comprising a service calling module which is responsible for processing an HTTP calling request and analyzing a URL and parameters in a request body; the table definition processing module is used for automatically creating a corresponding table structure at a target end according to a table definition provided during calling or a column definition of an SQL (Structured Query Language) statement; the parameter processing module is used for accurately processing various selectable parameters and realizing fine control of data transmission; the data transmission module is used for executing differentiated data flow logic according to different data transmission modes; the task management module is used for providing predefined task configuration, running state monitoring and historical record query functions; according to the method, through systematic architecture design and an innovative technical method, the problems of efficiency, flexibility and management of data transmission between Gaussian databases are solved, an efficient and reliable solution is provided for enterprise-level data integration, and the method has remarkable technical advancement and market application value.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, and particularly relates to a Gauss-to-Gauss efficient data transmission service system. BACKGROUND

[0002] In the current data processing environment, the demand for data transmission between different databases is increasing. Especially in the application scenario of Gauss database, the traditional data transmission method has many shortcomings. For example, when performing data transmission, it is often necessary to create a table structure in advance at the target end, which increases the complexity and workload of the operation. Moreover, the traditional method lacks flexible and efficient processing mechanisms for data transmission in different modes (such as inventory transmission, incremental transmission, etc.), making it difficult to meet the diverse data transmission needs. At the same time, the setting and management of parameters during data transmission are not convenient enough, which affects the efficiency and accuracy of data transmission. SUMMARY

[0003] The purpose of the present application is to provide a Gauss-to-Gauss efficient data transmission service system to solve the problems of operation complexity, mode processing inflexibility, and parameter management inconvenience in the data transmission process of the prior art, and to realize efficient, flexible, and convenient data transmission.

[0004] In order to achieve the above-mentioned purpose, the present application adopts the following technical solutions: The Gauss-to-Gauss efficient data transmission service system comprises: a service calling module: responsible for processing HTTP calling requests and parsing parameters in the URL and request body; a table definition processing module: automatically creating corresponding table structures at the target end according to the column definitions of the table definition or SQL statement provided during calling; a parameter processing module: accurately processing various optional parameters to realize fine control of data transmission; a data transmission module: executing differentiated data flow logic according to different data transmission modes; a task management module: providing pre-defined task configuration, running state monitoring, and historical record query functions.

[0005] In some embodiments, the parameters include: read_query: supporting source table name or complex query statement; when the table structures of the source and target ends are different, the source data source must be specified through this parameter; fresh_where: refresh condition control switch; when not set, it is an inventory transmission mode, and after setting, it triggers incremental extraction logic; key_columns: specifying table primary keys in incremental mode; passwdName and passwdValue: A permission verification mechanism based on dynamic passwords; the service can only be invoked when the parameters match the definition in the configuration file, ensuring data transmission security.

[0006] In some embodiments, the HTTP call URL adopts a hierarchical structure design; The format is: http: / / xxxx / dts / project / targettable_name?passwdName=passwdValue; Project: The name of the business project defined in the configuration file, used to isolate data transmission tasks of different businesses; Target table name: Supports complex table matching rules, including schema transformation and wildcard mechanisms. Format rule: (sc->)?tc.tbl(.tbl)?(;(sc->)?tc.tbl(.tbl)? )+; where sc is the source schema, tc is the target schema, and tbl is the table name.

[0007] In some embodiments, the parameter setting and dynamic parsing mechanism of the parameter processing module includes: read_query dynamic parsing: When the parameter is a query statement, the server directly executes the SQL to obtain the source data, breaking through the table name mapping restriction; fresh_where intelligent replacement: supports built-in <%sql:(.*?)%> syntax; key_columns multi-column processing: Supports comma-separated multi-column primary keys, used to accurately identify the uniqueness of records and improve the efficiency of data comparison during incremental updates.

[0008] In some embodiments, the data transmission modes include: existing mode and incremental mode; The execution logic of the existing mode is as follows: directly read the matching table data from the source end, automatically create the table and insert data on the target end, which is suitable for the first full synchronization or scenarios where incremental updates are not required; The core requirements for incremental mode are: the fresh_where parameter must be specified, and the table name cannot use wildcards, commas, semicolons, or other separators to ensure the uniqueness of the target table.

[0009] In some embodiments, the table name matching rules for the stock schema include: Single table: schema.tbl1, exact match for a single table; Multiple tables under the same schema: Supports three formats: schema.tbl1|schema.tbl2, schema.tbl1,tbl2, and schema.tbl*, which use vertical bars, commas, and wildcards respectively to match multiple tables; Multiple tables across different schemas: Separated by semicolons, supporting batch transfers across schemas; Schema transformation: source_schema->target_schema.tbl, realizes the mapping transformation between the source and target schemas.

[0010] In some embodiments, the sub-scenarios of incremental mode include: pure conditional incremental, primary key incremental, dynamic time incremental, and full primary key comparison incremental.

[0011] In some embodiments, pure conditional increment only sets fresh_where; Incremental primary key usage, set fresh_where + key_columns; Dynamic time increments are expressed using the syntax <%sql:max(...)%>. Perform a full primary key comparison with incremental values, and set fresh_where+key_columns+extract_all_keys=yes.

[0012] In some embodiments, additional logic is also included: extracting all primary key values ​​from the source end to the target end, and deleting all records that are not in the primary key set of the source end on the target end, so as to achieve full matching of "add, delete and modify" data, which is suitable for scenarios that require strict data consistency between the source end and the target end.

[0013] In some embodiments, the predefined tasks and automated scheduling of the task management module include: Task definition: Predefined tasks are stored in the dts_define table; Batch execution: Tasks with the same batch job name are executed simultaneously during scheduling; Call format: http: / / xxxx / ExecDefine / batchjobname?passwd, triggers the execution of predefined tasks through a simple URL call.

[0014] Compared with the prior art, the present invention provides a Gaussian-to-Gaussian high-efficiency data transmission service system, which has the following beneficial effects.

[0015] 1. This invention achieves a revolutionary upgrade in data transmission through multi-dimensional innovation.

[0016] 2. This invention achieves zero upfront table creation cost, enabling "table creation upon call"; it covers all scenarios, including existing and incremental modes; it features intelligent dynamic parameter parsing, enabling automated generation of incremental conditions; it has an efficient task management system with predefined tasks supporting batch execution; and it offers security and performance optimizations.

[0017] 3. This invention, through systematic architectural design and innovative technical methods, solves the problems of efficiency, flexibility and management of data transmission between Gaussian databases, providing an efficient and reliable solution for enterprise-level data integration, and has significant technological advancement and market application value.

[0018] Other advantages, objectives and features of the invention will be set forth in part in the description which follows; and in part will be apparent to those skilled in the art upon examination of the following description; or may be learned from practice of the invention. Detailed Implementation

[0019] The technical solutions in the embodiments of the present invention will be clearly and completely described below. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments.

[0020] The Gaussian-to-Gaussian high-efficiency data transmission service system includes: a service call module, a table definition processing module, a parameter processing module, a data transmission module, and a task management module.

[0021] Service Invocation Module: Responsible for handling HTTP invocation requests and parsing parameters in the URL and request body. It can identify the project name, target table name, and password parameters in the URL; at the same time, it supports reading complex parameters (such as query statements, refresh conditions, etc.) from the request body.

[0022] The table definition processing module is one of the core innovations, eliminating the need for users to pre-create tables on the target side. When a data transfer request is received, this module automatically creates the corresponding table structure on the target side based on the table definition or column definition provided during the call.

[0023] For example, if the source query is SELECT id, name, age FROM source_table, the module will create a table on the target side containing the id, name, and age columns.

[0024] Parameter processing module: accurately processes various optional parameters to achieve fine-grained control of data transmission.

[0025] read_query: Supports source table names or complex query statements; when the source and target table structures are different, the source data source must be specified through this parameter, and long query statements can be set in the body.

[0026] fresh_where: Refresh condition control switch; when not set, it is in stock transmission mode; when set, it triggers incremental extraction logic and supports configuring complex condition expressions in the body.

[0027] key_columns: Specifies the primary key of the table in incremental mode; supports multiple primary keys (such as id, version) for data comparison and deletion operations.

[0028] passwdName and passwdValue: A dynamic password-based permission verification mechanism. The service can only be invoked if the parameters match the definition in the configuration file, ensuring data transmission security.

[0029] Data transmission module: Executes differentiated data flow logic according to different data transmission modes; supports two core modes, namely existing and incremental data, and further subdivides various processing strategies for incremental scenarios.

[0030] Task Management Module: Provides predefined task configuration, runtime status monitoring, and historical record query functions. Predefined tasks are stored in the `dts_define` table, and the execution details of all tasks are recorded in the `dts_history` table.

[0031] URL call format.

[0032] The HTTP call URL adopts a hierarchical structure design; the basic format is: http: / / xxxx / dts / project / target_table_name?passwdName=passwdValue.

[0033] Project: The name of the business project defined in the configuration file, used to isolate data transmission tasks of different businesses.

[0034] Target table name: Supports complex table matching rules, including schema transformation and wildcard mechanisms.

[0035] Formatting rules: (sc->)?tc.tbl(.tbl)?(;(sc->)?tc.tbl(.tbl)? )+; where sc is the source schema, tc is the target schema, and tbl is the table name. Wildcards * are supported (automatically converted to % in SQL by the backend).

[0036] Example: source_db->only_query_db.* means to transfer all tables under the source_db.schema to the target only_query_db.schema.

[0037] Parameter setting and dynamic parsing mechanism.

[0038] dynamic parsing of read_query: When the parameter is a query statement (such as SELECT * FROM source_table WHERE status = 'active'), the server directly executes the SQL to obtain the source data, breaking through the table name mapping restriction.

[0039] fresh_where intelligent replacement: supports built-in <%sql:(.*?)%> syntax; For example, `upd_time>'<%sql:max(upd_time)%>'::timestamp` will automatically retrieve the value of `max(upd_time)` from the target table and replace it in the condition, thus achieving dynamic incremental condition generation.

[0040] key_columns multi-column processing: Supports comma-separated multi-column primary keys (such as data_src_id, cntr_no) to accurately identify the uniqueness of records and improve the efficiency of data comparison during incremental updates.

[0041] In-depth analysis of data transmission modes.

[0042] Existing data mode (full synchronization): Table name matching rules: Single table: schema.tbl1, exact match for a single table; Multiple tables under the same schema: Supports three formats: schema.tbl1|schema.tbl2, schema.tbl1,tbl2, and schema.tbl*, which use vertical bars, commas, and wildcards respectively to match multiple tables; Multiple tables across different schemas: Separate tables using semicolons (e.g., schema0.tbl2; schema1.tbl1), supporting batch transfers across schemas; Schema transformation: source_schema->target_schema.tbl, realizes the mapping transformation between the source and target schemas.

[0043] Execution logic: Read the matching table data directly from the source end, automatically create the table and insert the data on the target end. It is suitable for scenarios where the first full synchronization is required or incremental updates are not required.

[0044] Incremental mode (conditional synchronization): Key requirements: The fresh_where parameter must be specified, and the table name cannot use wildcards, commas, semicolons, or other separators to ensure the uniqueness of the target table.

[0045] Specific scenarios: pure conditional incremental, primary key incremental, dynamic time incremental, and full primary key comparison incremental.

[0046] Scenario 1: Pure conditional incremental (only setting fresh_where); Data retrieval from the source: If read_query is a table name, then data is retrieved according to the fresh_where condition; if read_query is an SQL statement, then fresh_where is not used for data retrieval from the source (controlled by the SQL statement's own conditions). Target-side processing: Regardless of how the source retrieves data, the target first deletes data according to the fresh_where condition, and then inserts data into the source, achieving incremental synchronization of "delete first, then insert".

[0047] Scenario 2: Primary key increment (setting fresh_where + key_columns); Processing logic: Based on the primary key identified by key_columns, delete data on the target end that is not in the primary key set on the source end, and delete the primary key data in the intermediate table at the same time. Finally, insert the data from the intermediate table into the final table to achieve precise incremental updates based on the primary key.

[0048] Scenario 3: Dynamic time increment (using the <%sql:max(...)%> syntax); Example: upd_time>'<%sql:max(upd_time)%>'::timestamp, the system automatically obtains the maximum update time of the target table as the data retrieval condition on the source side, ensuring that only newly added or modified data is synchronized, which is suitable for scenarios where data is continuously updated.

[0049] Scenario 4: Incremental comparison of all primary keys (setting fresh_where+key_columns+extract_all_keys=yes); Additional logic: Extract all primary key values ​​from the source end to the target end, and delete all records that are not in the primary key set of the source end on the target end, realizing full matching of "add, delete, and modify" operations. This is suitable for scenarios that require strict consistency between the source and target end data.

[0050] Predefined tasks and automated scheduling.

[0051] Task definition: The dts_define table stores predefined tasks, including batch job name, project name, target table name, source query statement, refresh conditions, primary key fields, etc. It supports the use of the extract_all_keys parameter to control whether to delete records that do not exist on the target. Batch execution: Tasks with the same batch job name will be executed simultaneously during scheduling, improving the efficiency of batch data transmission. This is suitable for scheduled tasks or periodic data synchronization scenarios.

[0052] Call format: http: / / xxxx / ExecDefine / batchjobname?passwd=x123. You can trigger the execution of predefined tasks by calling a simple URL.

[0053] This invention achieves a revolutionary upgrade in data transmission through multi-dimensional innovation.

[0054] 1. Zero upfront table creation cost: The table definition processing module enables "table creation upon call", reducing up to 50% of the upfront preparation work compared to traditional methods, making it especially suitable for rapid iterative data analysis scenarios.

[0055] 2. Full-scene mode coverage: The existing data mode supports wildcards, multi-table matching, and schema conversion to meet the needs of batch full synchronization. The incremental mode, through the combination of fresh_where, key_columns, and extract_all_keys, covers scenarios such as incremental updates at fixed time intervals, incremental updates at dynamic time intervals, and incremental updates based on full primary key comparisons, improving data synchronization accuracy to 99.99%.

[0056] 3. Dynamic parameter intelligent parsing: The <%sql:max(...)%> syntax enables the automatic generation of incremental conditions, avoiding manual maintenance of time parameters and reducing the parameter configuration error rate by 80%, which is especially suitable for business systems with continuously updated data.

[0057] 4. Highly efficient task management system: Predefined tasks support batch execution, and a single scheduling can handle synchronization tasks of dozens of tables, improving efficiency by more than 10 times; A complete task monitoring and history record system supports fault traceability and performance analysis, improving operation and maintenance efficiency by 40%.

[0058] 5. Security and performance optimization: Dynamic password verification mechanisms prevent unauthorized access, and read-only users at the source end are advised to further ensure data security. The column-oriented historical table design improves query efficiency by 3 times and reduces storage space by 60% for large-scale task records.

[0059] This invention solves the problems of efficiency, flexibility and management of data transmission between Gaussian databases through systematic architecture design and innovative technical methods, providing an efficient and reliable solution for enterprise-level data integration, and has significant technological advancement and market application value.

[0060] The above description is only a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and inventive concept of the present invention, should be covered within the scope of protection of the present invention.

[0061] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., refer to specific features, structures, materials, or characteristics described in connection with that embodiment or example, which are included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of different embodiments or examples.

[0062] Although embodiments of the present invention have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting the present invention. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of the present invention.

Claims

1. A Gaussian-to-Gaussian high-efficiency data transmission service system, characterized in that, include: Service Invocation Module: Responsible for handling HTTP invocation requests and parsing the parameters in the URL and request body; Table definition processing module: Automatically creates the corresponding table structure on the target side based on the table definition or column definition of the SQL statement provided during the call; Parameter processing module: Precisely processes various optional parameters to achieve fine-grained control of data transmission. Data transmission module: Executes differentiated data flow logic based on different data transmission modes; Task management module: Provides predefined task configuration, running status monitoring, and historical record query functions.

2. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 1, characterized in that the parameters include: read_query: Supports source table names or complex query statements; when the source and target table structures are different, the source data source must be specified through this parameter; fresh_where: refresh condition control switch; when not set, it is in stock transmission mode, and when set, it triggers incremental extraction logic; key_columns: Specifies the primary key of the table in incremental mode; passwdName and passwdValue: A permission verification mechanism based on dynamic passwords; the service can only be invoked when the parameters match the definition in the configuration file, ensuring data transmission security.

3. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 1, characterized in that, The HTTP call URL adopts a hierarchical structure design; The format is: http: / / xxxx / dts / project / targettable_name?passwdName=passwdValue; Project: The name of the business project defined in the configuration file, used to isolate data transmission tasks of different businesses; Target table name: Supports complex table matching rules, including schema transformation and wildcard mechanisms. Format rule: (sc->)?tc.tbl(.tbl)?(;(sc->)?tc.tbl(.tbl)? )+; where sc is the source schema, tc is the target schema, and tbl is the table name.

4. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 2, characterized in that, The parameter processing module's parameter setting and dynamic parsing mechanism includes: read_query dynamic parsing: When the parameter is a query statement, the server directly executes the SQL to obtain the source data, breaking through the table name mapping restriction; fresh_where intelligent replacement: supports built-in <%sql:(.*?)%> syntax; key_columns multi-column processing: Supports comma-separated multi-column primary keys, used to accurately identify the uniqueness of records and improve the efficiency of data comparison during incremental updates.

5. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 1, characterized in that, Data transmission modes include: existing data mode and incremental data mode; The execution logic of the existing mode is as follows: directly read the matching table data from the source end, automatically create the table and insert the data on the target end. It is suitable for scenarios where the first full synchronization is required or incremental updates are not required. The core requirements for incremental mode are: the fresh_where parameter must be specified, and the table name cannot use wildcards, commas, semicolons, or other separators to ensure the uniqueness of the target table.

6. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 5, characterized in that, The table name matching rules for the existing schema include: Single table: schema.tbl1, exact match for a single table; Multiple tables under the same schema: Supports three formats: schema.tbl1|schema.tbl2, schema.tbl1,tbl2, and schema.tbl*, which use vertical bars, commas, and wildcards respectively to match multiple tables; Multiple tables across different schemas: Separated by semicolons, supporting batch transfers across schemas; Schema transformation: source_schema->target_schema.tbl, realizes the mapping transformation between the source and target schemas.

7. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 5, characterized in that, The sub-scenarios of incremental mode include: pure conditional incremental, primary key incremental, dynamic time incremental, and full primary key comparison incremental.

8. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 7, characterized in that: Pure conditional incremental, only setting `fresh_where`; Incremental primary key usage, set fresh_where + key_columns; Dynamic time increments are expressed using the syntax <%sql:max(...)%>. Perform a full primary key comparison with incremental values, and set fresh_where+key_columns+extract_all_keys=yes.

9. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 8, characterized in that, It also includes additional logic: extract all primary key values ​​from the source end to the target end, and delete all records that are not in the primary key set of the source end on the target end, realizing full matching of "add, delete, and modify", which is suitable for scenarios that require strict consistency between the source end and the target end data.

10. The Gaussian-to-Gaussian high-efficiency data transmission service system according to claim 1, characterized in that, The predefined tasks and automated scheduling in the task management module include: Task definition: Predefined tasks are stored in the dts_define table; Batch execution: Tasks with the same batch job name are executed simultaneously during scheduling; Call format: http: / / xxxx / ExecDefine / batchjobname?passwd, triggers the execution of predefined tasks through a simple URL call.