Database proxy system and method, electronic equipment and program product

Abstract

According to the database agent system and method, the electronic equipment and the program product, a service system only needs to interact with the database agent system, a hot library and a cold storage do not need to be directly connected, the existence of the bottom-layer cold and hot library is shielded, the configuration complexity of the service system is simplified, and the service system does not need to maintain connection information with multiple databases. The hot storage and the cold storage are in unified butt joint through the database agent system, a business system does not need to pay attention to specific management logic of multiple data sources, and data query can be achieved through the unified agent system no matter which programming language is used. The hot library and the cold storage with the mapping relation belong to the same project library, SQL rewriting is carried out on the original SQL of the service system according to the query mark of the project library, the back-end execution layer automatically queries the corresponding cold storage or hot library according to the rewritten SQL, manual intervention is not needed in the whole process, automatic routing of cold and hot data query is achieved, and the efficiency is improved. And the query efficiency and accuracy are improved.

Description

Technical Field

[0001] This application relates to the field of database technology, and more specifically, to a database agent system, method, electronic device, and program product. Background Technology

[0002] In the information age, all kinds of data need to be stored in database systems, such as electronic orders and office automation (OA) approval process information. However, as time goes by, the amount of data accumulates and becomes enormous, overburdening database systems, slowing down query speeds, and causing frequent glitches. However, most long-dormant historical data is actually queried and used very infrequently. Taking an e-commerce scenario as an example, big data statistics show that 99.2% of order modifications occur within the last six months, while the remaining historical data, dormant for more than six months, is queried and modified extremely infrequently. This cold data consumes database system resources and affects system performance. Therefore, a separate database can be created to store and archive cold data, while recently generated hot data can be stored in a hot database. When the system queries, it usually queries the hot database first, only querying the cold database in special scenarios.

[0003] Extracting cold data from storage and storing it separately from recent hot data can significantly reduce storage costs, improve query performance for hot data, and reduce the pressure on storage media. This technological transformation is known as cold and hot data separation.

[0004] Currently, the industry's solution for separating cold and hot data requires repeatedly writing cold data query code and implementing multi-data source management to simultaneously connect to both cold and hot storage facilities. Furthermore, because different projects use different programming languages ​​and corresponding programming frameworks, multi-data source management needs to be implemented within each project's respective programming language framework. Summary of the Invention

[0005] The purpose of this application is to provide a database proxy system, method, electronic device, and program product to solve the problems of existing cold and hot data separation transformation schemes, which require repeated writing of cold data query code, the need to achieve multi-data source management to simultaneously connect to cold and hot storage, and the need to implement multi-data source management in their respective programming language systems.

[0006] This application provides a database proxy system, comprising: a front-end access layer, a service layer, and a back-end execution layer; The front-end access layer connects to the business system, and the front-end access layer, service layer, and back-end execution layer are connected in sequence. The back-end execution layer connects to the hot storage and the cold storage. The front-end access layer is used to: receive SQL requests from the business system and decode them to obtain the original SQL statements; detect the query tags of the project library from the original SQL statements, wherein the project library includes cold storage and hot storage; and send the original SQL statements and query tags to the service layer. The service layer is used to: rewrite the SQL statement based on the original SQL statement and query tags to obtain the rewritten SQL; and send the rewritten SQL to the backend execution layer. The backend execution layer is used to: query the corresponding cold storage and hot storage according to the rewritten SQL, and obtain the query results.

[0007] The business system determines whether a cold storage query is needed based on the business scenario. If so, a custom comment is added to the SQL query to identify the cold query before it is sent to the database agent for execution. If not, the SQL query is sent directly to the database agent.

[0008] In the above technical solution, the business system only needs to add a custom comment as a marker in the SQL when querying the cold storage, eliminating the need to develop additional dedicated code for cold storage queries, reducing code duplication and redundancy, and lowering development workload. The business system only needs to interact with the database proxy system, without directly connecting to the hot and cold storage, shielding it from the underlying hot and cold storage, simplifying the configuration complexity of the business system, and eliminating the need to maintain connection information with multiple databases. By uniformly connecting to the hot and cold storage through the database proxy system, the business system does not need to concern itself with the specific management logic of multiple data sources. Regardless of the programming language used, data queries can be implemented through a unified proxy system, reducing the cost of repeatedly developing multiple data source management modules under different technology stacks. Hot and cold storage with mapping relationships belong to the same project library. Based on the query markers of the project library, the original SQL in the business system is rewritten. The backend execution layer automatically queries the corresponding cold or hot storage based on the rewritten SQL. The entire process requires no manual intervention, achieving automated routing of hot and cold data queries, improving query efficiency and accuracy.

[0009] In some optional implementations, the step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding first project library, wherein the first project library has no query mark; For the first project library, rewrite the relevant SQL statements to route to the hot library of the first project library.

[0010] In the above technical solution, most queries in the business system are hot data queries (such as order modifications, recent operations, etc.). The business system only adds the corresponding query flag when it needs to query the cold storage, and the data broker system queries the hot storage by default. In actual business, most queries (such as order modifications, recent operations, etc.) are directed to hot data (real-time or recent data), while cold data queries (such as historical archived data) are a minority of scenarios. By "defaulting to the hot storage", extra processing for high-frequency hot queries is avoided.

[0011] In some alternative implementations, the query markers include cold query markers; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding second project library, wherein the second project library has a cold query tag; For the second project library, rewrite the relevant SQL statements to route to the cold storage of the second project library.

[0012] In the above technical solution, if the business system does not obtain the desired data after querying the hot database, it will perform a cold database query. To implement a cold database query, a corresponding cold query flag needs to be added. The data proxy system then queries the corresponding cold database based on the cold query flag. The cold query flag is dynamically added by the business system based on the actual query results (whether the hot database is hit), rather than being statically configured, perfectly matching the actual needs of the business scenario. For example, for "querying user orders from the last 3 months," the hot database directly returns the results without triggering a cold database query; for "querying user orders from 3 years ago," if the hot database has no results, the business system triggers a cold database query through the flag, achieving "scenario-based on-demand routing" and avoiding a one-size-fits-all query strategy.

[0013] In some optional implementations, the query tags include hot and cold query tags; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding third project library, wherein the third project library has hot and cold query tags; For the third project library, the relevant SQL statements are first rewritten to be routed to the hot library of the third project library to obtain the first rewritten SQL, and the first rewritten SQL is sent to the backend execution layer to obtain the first query result; If the query result for the hot library of the third project library in the first query result is empty, then for the third project library, the relevant SQL statement is rewritten to route to the cold library of the third project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0014] In the above technical solution, when the business system makes its first query, it may be unclear whether to search the hot or cold storage. In this case, the data proxy system can automatically perform the following process: first, query recent data in the hot storage; if the desired data is not found in the hot storage, then query the corresponding data in the cold storage. The business system does not need to pre-determine whether the data is in the hot or cold storage (e.g., a user queries "a certain order" but is unsure whether the order is recent or historical). It only needs to add a "hot / cold query flag," and the rest of the process (querying the hot storage first, then the cold storage) is automatically completed by the proxy system. This avoids the business system writing complex "data location judgment logic" (such as inferring storage location based on time range and data status), reduces redundancy in business code, and lowers development difficulty and subsequent maintenance costs. The hot storage stores recently accessed high-frequency data and typically uses high-performance storage media (such as SSDs and in-memory databases), resulting in fast query response speeds. The proxy system first queries the hot database, quickly returning results for most "recent data," ensuring high efficiency in common scenarios. Only when the hot database yields no results does it automatically query the cold database (which stores historical low-frequency data), avoiding blind access to the cold database (which typically has lower performance). This ensures no data is missed while minimizing resource waste. Through automatic chained queries from "hot database → cold database," the proxy system ensures data can be retrieved regardless of its location, fundamentally preventing "incomplete data" issues caused by business judgment errors and improving business reliability.

[0015] In some alternative implementations, the query tags include hot and cold query tags; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, the corresponding fourth project library is determined, wherein the fourth project library has cold and hot query tags. For the fourth project library, the relevant SQL statements are first rewritten to route to the cold library of the fourth project library, resulting in the first rewritten SQL. The first rewritten SQL is then sent to the backend execution layer to obtain the first query result. If the query result for the cold storage of the fourth project library in the first query result is empty, then for the fourth project library, the relevant SQL statement is rewritten to route to the hot storage of the fourth project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0016] In the above technical solution, when the business system makes its first query, it may be unclear whether to search the hot or cold storage. In this case, it can first query earlier data in the cold storage. If the desired data is not found in the cold storage, it can then query the corresponding recent data in the hot storage. In some business scenarios, the query requirements tend to focus on historical data (such as audit traceability, compliance checks, and access to historical archives). In these scenarios, the query frequency of historical data (cold storage) is higher than that of recent data (hot storage). For example, when a financial institution queries a transaction record from many years ago, or a medical institution accesses a patient's early medical records, querying the cold storage first can directly yield the result, avoiding the ineffective step of "querying the hot storage first and then the cold storage," thus improving the query efficiency in specific scenarios. For scenarios where "historical data is more likely to exist" but cannot be 100% certain (such as querying a type of low-frequency business record), the business system does not need to write complex judgment logic, reducing development and maintenance costs.

[0017] In some optional implementations, the backend execution layer is further configured to: remove null values ​​from the first query result and the second query result to obtain the query result.

[0018] In some optional implementations, it also includes: a management backend; The service layer is connected to the management backend, and the management backend is used to configure the data source information, business logic library information and hot / cold rule information in the service layer. The data source information includes: address information and verification information for each hot storage facility, and address information and verification information for each cold storage facility. The business logic library information includes: the project library corresponding to each business logic library; The hot and cold rule information includes the correspondence between hot and cold storage facilities within each project warehouse.

[0019] The data source information is the fundamental connection information describing database instances such as hot storage and cold storage facilities. It is used by the data proxy system to locate and access specific databases. Specifically, it includes: Address information: such as the database's IP address and port number (e.g., "192.168.1.100:3306"), used at the network level to locate the server where the database resides. Authentication information: such as the username and password for logging into the database, ensuring that only authorized systems can access the database and guaranteeing data security. The data source information allows the data proxy system to accurately locate and connect to hot and cold storage facilities, and is a prerequisite for all data query operations.

[0020] Business logic database information is a mapping between the "logical database" at the business system level and the "project database" (physical database) where the actual data is stored. For example, the logical concept of "user order database" in the business system may correspond to the actual stored "order_db_2024" (project database). The business system only needs to focus on "which business logic database to query" (such as "user order database"), without needing to know which physical project database the actual data resides in, thus reducing the coupling between the business system and the underlying storage.

[0021] Hot and cold rules define the relationship between hot and cold data repositories for each project database (physical database). For example, the hot repository for the project database "order_db_2024" is "order_hot_2024" (storing data from the last 3 months), and the cold repository is "order_cold_2024" (storing historical data from 3 months ago). Hot and cold rules specify the correspondence between hot repositories (storing recently accessed high-frequency data) and cold repositories (storing historical low-frequency access data), allowing the data broker system to know "where the hot and cold data for a given project database are stored." When the data broker system receives a request with a query tag (such as a hot / cold query tag), it can find the corresponding hot or cold repository based on the hot and cold rules, enabling automatic query processes such as "hot first, then cold" and "cold first, then hot."

[0022] These three types of information are uniformly configured through the management backend, forming a complete mapping chain of "business logic → physical project library → hot / cold storage": the business system initiates a request based on the logical library; the data proxy system finds the corresponding project library based on the business logic library information; then determines the hot / cold storage of the project library based on the hot / cold rule information; and finally connects to and accesses the target database through the data source information.

[0023] In some optional implementations, the data source information may further include: connection configuration information, database configuration information, and request timeout information; the verification information may include username and password.

[0024] Connection configuration information refers to the basic parameters used to establish and maintain database connections, mainly ensuring connection validity and reasonable resource allocation. Examples include: connection pool configuration: such as maximum number of connections (the maximum number of database connections allowed to be established simultaneously), minimum number of idle connections (the minimum number of idle connections maintained to avoid frequent connection creation), and connection timeout (the timeout threshold for acquiring a connection). Network connection parameters include: connection protocol (TCP / IP, etc.), port number (the port the database service listens on, such as 3306 by default for MySQL), and connection keep-alive time (to prevent long-term idle connections from being disconnected).

[0025] The purpose of connection configuration information is to optimize the efficiency of connection resource utilization, avoid excessive database load due to too many connections, or query blocking due to insufficient connections.

[0026] Database configuration information refers to configuration parameters related to the database instance itself, used to accurately locate and adapt to the target database. Examples include: Database type: such as MySQL, PostgreSQL, Oracle, etc. (Different databases have different syntax and interaction logic, which must be specified in advance). Database name: The specific name of the target database (a database instance may contain multiple databases; the specific database to be operated on must be specified). Character encoding: such as UTF-8, GBK, etc. (To ensure consistent encoding during data reading and writing and avoid garbled characters). Other feature configurations: such as whether to enable SSL encrypted connections, whether to enable read / write splitting, etc.

[0027] The purpose of database configuration information is to ensure that the data broker system can correctly identify and adapt to the target database, and to guarantee the compatibility of SQL statement execution and the accuracy of data transmission.

[0028] Request timeout information refers to the time threshold controlling a single database query request. For example: Execution timeout: the maximum allowed time from sending the SQL statement to the database returning the result (if exceeded, the request is terminated to avoid long-term blocking). Response timeout: the maximum time to wait for a database response (if the database does not respond, a timeout retry or error reporting mechanism is triggered).

[0029] The purpose of request timeout information is to prevent queries from being blocked indefinitely due to database performance issues, network latency, etc., thus ensuring system stability and response efficiency.

[0030] In this embodiment, the configuration information is managed uniformly through the management backend, which can flexibly adapt to different database environments, business scenarios and performance requirements, and provide reliable underlying support for the routing logic of hot / cold storage.

[0031] In some optional implementations, the backend execution layer is further configured to: encode the query results according to a set format to obtain encapsulated query results; and send the encapsulated query results to the service layer. The server layer is also used to: forward the encapsulated query results to the front-end access layer; The front-end access layer is also used to: return the encapsulated query results to the business system.

[0032] In the above technical solution, for the backend execution layer, regardless of whether it accesses relational databases such as MySQL and PostgreSQL (commonly used in hot databases) or databases suitable for storing massive amounts of historical data such as Hive and ClickHouse (commonly used in cold databases), the native return format of different databases can be converted into the system's agreed format through unified encoding. For the frontend business system, by parsing the results in a unified format, there is no need to adapt to the interaction protocols of different databases, which improves the system's compatibility with multiple types of storage and multiple business scenarios.

[0033] This application provides a database proxy method, including: Receive SQL requests from the business system and decode them to obtain the original SQL statements; detect the query tags of the project library from the original SQL statements, wherein the project library includes cold storage and hot storage; Based on the original SQL statement and query tags, the SQL is rewritten to obtain the rewritten SQL; Based on the rewritten SQL, query the corresponding cold storage and hot storage to obtain the query results.

[0034] In some optional implementations, the step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding first project library, wherein the first project library has no query mark; For the first project library, rewrite the relevant SQL statements to route to the hot library of the first project library.

[0035] In some optional implementations, the step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding second project library, wherein the second project library has a cold query tag; For the second project library, rewrite the relevant SQL statements to route to the cold storage of the second project library.

[0036] In some optional implementations, the step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding third project library, wherein the third project library has hot and cold query tags; For the third project library, the relevant SQL statements are first rewritten to be routed to the hot library of the third project library to obtain the first rewritten SQL, and the first rewritten SQL is sent to the backend execution layer to obtain the first query result; If the query result for the hot library of the third project library in the first query result is empty, then for the third project library, the relevant SQL statement is rewritten to route to the cold library of the third project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0037] In some optional implementations, the step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, the corresponding fourth project library is determined, wherein the fourth project library has cold and hot query tags. For the fourth project library, the relevant SQL statements are first rewritten to route to the cold library of the fourth project library, resulting in the first rewritten SQL. The first rewritten SQL is then sent to the backend execution layer to obtain the first query result. If the query result for the cold storage of the fourth project library in the first query result is empty, then for the fourth project library, the relevant SQL statement is rewritten to route to the hot storage of the fourth project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0038] In some optional implementations, the method further includes: removing null values ​​from the first query result and the second query result to obtain the query result.

[0039] In some optional implementations, the system may also include: configuring data source information, business logic library information, and hot / cold rule information in the service layer.

[0040] An electronic device provided in this application includes a processor and a memory. The memory stores machine-readable instructions executable by the processor. When the machine-readable instructions are executed by the processor, they perform the method described above.

[0041] This application provides a computer program product, including a computer program / instruction, which, when executed by a processor, implements the steps of the method described above. Attached Figure Description

[0042] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments of this application will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0043] Figure 1 A functional module diagram of a database proxy system provided in this application embodiment; Figure 2 A flowchart of a database proxy method provided in this application embodiment; Figure 3 This is a schematic diagram of a possible structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0044] The technical solutions in the embodiments of this application will now be described with reference to the accompanying drawings.

[0045] Please refer to Figure 1 , Figure 1 A functional block diagram of a database proxy system provided in this application embodiment includes: a front-end access layer, a service layer, and a back-end execution layer; the front-end access layer connects to the business system, the front-end access layer, the service layer, and the back-end execution layer are connected in sequence, and the back-end execution layer connects to the hot storage and the cold storage.

[0046] The front-end access layer is responsible for: receiving SQL requests from the business system and decoding them to obtain the original SQL statements; detecting the query flags of the project library from the original SQL statements, where the project library includes cold storage and hot storage; and sending the original SQL statements and query flags to the service layer. Specifically, the query flags are used to indicate whether to query the hot storage or the cold storage, or to query the hot storage first and then the cold storage, or vice versa.

[0047] The service layer is used to: rewrite the SQL statement based on the original SQL statement and query tags to obtain the rewritten SQL; and send the rewritten SQL to the backend execution layer. Specifically, SQL rewriting includes: replacing the hot database table name with the corresponding cold database table name according to the hot / cold mapping rules; and changing the SQL route to the corresponding cold or hot database.

[0048] The backend execution layer is used to query the corresponding cold storage and hot storage based on the rewritten SQL and obtain the query results.

[0049] SQL (Structured Query Language) is a standard computer language used to manage and manipulate relational databases. It allows users to execute data queries (SELECT) to retrieve specific data from the database (such as querying order information as mentioned in the document).

[0050] The decoding and encoding in the database proxy system adopt the MySQL protocol format.

[0051] The business system determines whether a cold storage query is needed based on the business scenario. If so, a custom comment is added to the SQL query to identify the cold query before it is sent to the database agent for execution. If not, the SQL query is sent directly to the database agent.

[0052] In this embodiment, the business system only needs to add a custom comment as a marker in the SQL when querying cold storage, eliminating the need to develop additional dedicated code for cold storage queries. This reduces code duplication and redundancy, lowering development workload. The business system only needs to interact with the database proxy system, without directly connecting to hot and cold storage, shielding the underlying hot and cold storage and simplifying the configuration complexity of the business system, eliminating the need to maintain connection information with multiple databases. By uniformly connecting to hot and cold storage through the database proxy system, the business system does not need to concern itself with the specific management logic of multiple data sources. Regardless of the programming language used, data queries can be implemented through a unified proxy system, reducing the cost of repeatedly developing multiple data source management modules under different technology stacks. Hot and cold storage with mapping relationships belong to the same project library. Based on the query markers of the project library, the original SQL of the business system is rewritten. The backend execution layer automatically queries the corresponding cold or hot storage based on the rewritten SQL. The entire process requires no manual intervention, achieving automated routing of cold and hot data queries, improving query efficiency and accuracy.

[0053] In some optional implementations, the SQL is rewritten based on the original SQL statement and query tags to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding first project library, where the first project library has no query mark; For the first project library, rewrite the relevant SQL statements to route to the hot library in the first project library.

[0054] In this embodiment, most queries in the business system are hot data queries (such as order modifications, recent operations, etc.). The business system only adds the corresponding query flag when it needs to query the cold storage, and the data proxy system queries the hot storage by default. In actual business, most queries (such as order modifications, recent operations, etc.) are directed to hot data (real-time or recent data), while cold data queries (such as historical archived data) are a minority of scenarios. By "defaulting to the hot storage", extra processing for high-frequency hot queries is avoided.

[0055] In some alternative implementations, query tags include cold query tags; Based on the original SQL statement and query tags, the SQL is rewritten to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding second project library, where the second project library has a cold query mark; For the second project library, rewrite the relevant SQL statements to route to the cold storage of the second project library.

[0056] In this embodiment, if the business system does not obtain the desired data after querying the hot database, it will perform a cold database query. To perform a cold database query, a corresponding cold query flag needs to be added. The data proxy system then queries the corresponding cold database based on the cold query flag. The cold query flag is dynamically added by the business system based on the actual query results (whether the hot database is hit), rather than being statically configured, perfectly matching the actual needs of the business scenario. For example, for "querying user orders from the last 3 months," the hot database directly returns the results without triggering a cold database query; for "querying user orders from 3 years ago," after the hot database returns no results, the business system triggers a cold database query through the flag, achieving "scenario-based on-demand routing" and avoiding a one-size-fits-all query strategy.

[0057] In some alternative implementations, the query tags include hot and cold query tags; Based on the original SQL statement and query tags, the SQL is rewritten to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding third project library, where the third project library has hot and cold query tags; For the third project library, the relevant SQL statements are first rewritten to be routed to the hot library of the third project library to obtain the first rewritten SQL, and then the first rewritten SQL is sent to the backend execution layer to obtain the first query result; If the query result for the hot library in the third project library is empty in the first query result, then for the third project library, the relevant SQL statement is rewritten to route to the cold library in the third project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0058] In this embodiment, when the business system makes its first query, it may be unclear whether to query the hot or cold storage. In this case, the data proxy system can automatically perform the following process: first, query recent data in the hot storage; if the desired data is not found in the hot storage, then query the corresponding data in the cold storage. The business system does not need to pre-determine whether the data is in the hot or cold storage (e.g., a user queries "a certain order" but is unsure whether the order is recent or historical). It only needs to add a "hot / cold query flag," and the rest of the process (querying the hot storage first, then the cold storage) is automatically completed by the proxy system. This avoids the business system writing complex "data location judgment logic" (such as inferring storage location based on time range and data status), reduces redundancy in business code, and lowers development difficulty and subsequent maintenance costs. The hot storage stores recently accessed high-frequency data and typically uses high-performance storage media (such as SSDs or in-memory databases), resulting in fast query response speeds. The proxy system first queries the hot database, quickly returning results for most "recent data," ensuring high efficiency in common scenarios. Only when the hot database yields no results does it automatically query the cold database (which stores historical low-frequency data), avoiding blind access to the cold database (which typically has lower performance). This ensures no data is missed while minimizing resource waste. Through automatic chained queries from "hot database → cold database," the proxy system ensures data can be retrieved regardless of its location, fundamentally preventing "incomplete data" issues caused by business judgment errors and improving business reliability.

[0059] In some alternative implementations, the query tags include hot and cold query tags; Based on the original SQL statement and query tags, the SQL is rewritten to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding fourth project library, where the fourth project library has cold and hot query tags. For the fourth project library, the relevant SQL statements are first rewritten to route to the cold library of the fourth project library, resulting in the first rewritten SQL. The first rewritten SQL is then sent to the backend execution layer to obtain the first query result. If the query result for the cold storage of the fourth project library is empty in the first query result, then for the fourth project library, the relevant SQL statement is rewritten to route to the hot storage of the fourth project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0060] In this embodiment, when the business system makes its first query, it may be unclear whether to search the hot or cold storage. In this case, it can first query earlier data in the cold storage. If the desired data is not found in the cold storage, it can then query the corresponding recent data in the hot storage. In some business scenarios, the query needs tend to focus on historical data (such as audit traceability, compliance checks, and access to historical archives). In these scenarios, the query frequency of historical data (cold storage) is higher than that of recent data (hot storage). For example, when a financial institution queries a transaction record from many years ago, or a medical institution accesses a patient's early medical records, querying the cold storage first can directly yield the result, avoiding the ineffective step of "querying the hot storage first and then the cold storage," thus improving the query efficiency in specific scenarios. For scenarios where "historical data is more likely to exist" but cannot be 100% certain (such as querying a type of low-frequency business record), the business system does not need to write complex judgment logic, reducing development and maintenance costs.

[0061] In some optional implementations, the backend execution layer is also used to: remove null values ​​from the first query result and the second query result to obtain the query result.

[0062] In some optional implementations, it also includes: a management backend; The service layer connects to the management backend, which is used to configure the data source information, business logic library information, and hot / cold rule information in the service layer. The data source information includes: the address and verification information of each hot storage facility, and the address and verification information of each cold storage facility. Business logic library information, including: the project library corresponding to each business logic library; Hot and cold storage rules information, including: the correspondence between hot and cold storage within each project warehouse.

[0063] The data source information is the fundamental connection information describing database instances such as hot storage and cold storage facilities. It is used by the data proxy system to locate and access specific databases. Specifically, it includes: Address information: such as the database's IP address and port number (e.g., "192.168.1.100:3306"), used at the network level to locate the server where the database resides. Authentication information: such as the username and password for logging into the database, ensuring that only authorized systems can access the database and guaranteeing data security. The data source information allows the data proxy system to accurately locate and connect to hot and cold storage facilities, and is a prerequisite for all data query operations.

[0064] Business logic database information is a mapping between the "logical database" at the business system level and the "project database" (physical database) where the actual data is stored. For example, the logical concept of "user order database" in the business system may correspond to the actual stored "order_db_2024" (project database). The business system only needs to focus on "which business logic database to query" (such as "user order database"), without needing to know which physical project database the actual data resides in, thus reducing the coupling between the business system and the underlying storage.

[0065] Hot and cold rules define the relationship between hot and cold data repositories for each project database (physical database). For example, the hot repository for the project database "order_db_2024" is "order_hot_2024" (storing data from the last 3 months), and the cold repository is "order_cold_2024" (storing historical data from 3 months ago). Hot and cold rules specify the correspondence between hot repositories (storing recently accessed high-frequency data) and cold repositories (storing historical low-frequency access data), allowing the data broker system to know "where the hot and cold data for a given project database are stored." When the data broker system receives a request with a query tag (such as a hot / cold query tag), it can find the corresponding hot or cold repository based on the hot and cold rules, enabling automatic query processes such as "hot first, then cold" and "cold first, then hot."

[0066] These three types of information are uniformly configured through the management backend, forming a complete mapping chain of "business logic → physical project library → hot / cold storage": the business system initiates a request based on the logical library; the data proxy system finds the corresponding project library based on the business logic library information; then determines the hot / cold storage of the project library based on the hot / cold rule information; and finally connects to and accesses the target database through the data source information.

[0067] In some optional implementations, the data source information may also include: connection configuration information, database configuration information, and request timeout information; the authentication information may include username and password.

[0068] Connection configuration information refers to the basic parameters used to establish and maintain database connections, mainly ensuring connection validity and reasonable resource allocation. Examples include: connection pool configuration: such as maximum number of connections (the maximum number of database connections allowed to be established simultaneously), minimum number of idle connections (the minimum number of idle connections maintained to avoid frequent connection creation), and connection timeout (the timeout threshold for acquiring a connection). Network connection parameters include: connection protocol (TCP / IP, etc.), port number (the port the database service listens on, such as 3306 by default for MySQL), and connection keep-alive time (to prevent long-term idle connections from being disconnected).

[0069] The purpose of connection configuration information is to optimize the efficiency of connection resource utilization, avoid excessive database load due to too many connections, or query blocking due to insufficient connections.

[0070] Database configuration information refers to configuration parameters related to the database instance itself, used to accurately locate and adapt to the target database. Examples include: Database type: such as MySQL, PostgreSQL, Oracle, etc. (Different databases have different syntax and interaction logic, which must be specified in advance). Database name: The specific name of the target database (a database instance may contain multiple databases; the specific database to be operated on must be specified). Character encoding: such as UTF-8, GBK, etc. (To ensure consistent encoding during data reading and writing and avoid garbled characters). Other feature configurations: such as whether to enable SSL encrypted connections, whether to enable read / write splitting, etc.

[0071] The purpose of database configuration information is to ensure that the data broker system can correctly identify and adapt to the target database, and to guarantee the compatibility of SQL statement execution and the accuracy of data transmission.

[0072] Request timeout information refers to the time threshold controlling a single database query request. For example: Execution timeout: the maximum allowed time from sending the SQL statement to the database returning the result (if exceeded, the request is terminated to avoid long-term blocking). Response timeout: the maximum time to wait for a database response (if the database does not respond, a timeout retry or error reporting mechanism is triggered).

[0073] The purpose of request timeout information is to prevent queries from being blocked indefinitely due to database performance issues, network latency, etc., thus ensuring system stability and response efficiency.

[0074] In this embodiment, the configuration information is managed uniformly through the management backend, which can flexibly adapt to different database environments, business scenarios and performance requirements, and provide reliable underlying support for the routing logic of hot / cold storage.

[0075] In some optional implementations, the backend execution layer is also used to: encode the query results according to a set format to obtain encapsulated query results; and send the encapsulated query results to the service layer. The server layer is also used to forward encapsulated query results to the front-end access layer; The front-end access layer is also used to return encapsulated query results to the business system.

[0076] In this embodiment, for the backend execution layer, regardless of whether it accesses relational databases such as MySQL and PostgreSQL (commonly used in hot databases) or databases suitable for storing massive amounts of historical data such as Hive and ClickHouse (commonly used in cold databases), the native return format of different databases can be converted into the system's agreed format through unified encoding. For the frontend business system, by parsing the results in a unified format, there is no need to adapt to the interaction protocols of different databases, which improves the system's compatibility with multiple types of storage and multiple business scenarios.

[0077] Please refer to Figure 2 , Figure 2 A flowchart of a database proxy method provided in this application includes: Step 100: Receive the SQL request from the business system and decode it to obtain the original SQL statement; detect the query mark of the project library from the original SQL statement, where the project library includes cold storage and hot storage; Step 200: Rewrite the SQL based on the original SQL statement and query tags to obtain the rewritten SQL; Step 300: Based on the rewritten SQL, query the corresponding cold storage and hot storage to obtain the query results.

[0078] In some optional implementations, the SQL is rewritten based on the original SQL statement and query tags to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding first project library, where the first project library has no query mark; For the first project library, rewrite the relevant SQL statements to route to the hot library in the first project library.

[0079] In some optional implementations, the SQL is rewritten based on the original SQL statement and query tags to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding second project library, where the second project library has a cold query mark; For the second project library, rewrite the relevant SQL statements to route to the cold storage of the second project library.

[0080] In some optional implementations, the SQL is rewritten based on the original SQL statement and query tags to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding third project library, where the third project library has hot and cold query tags; For the third project library, the relevant SQL statements are first rewritten to be routed to the hot library of the third project library to obtain the first rewritten SQL, and then the first rewritten SQL is sent to the backend execution layer to obtain the first query result; If the query result for the hot library in the third project library is empty in the first query result, then for the third project library, the relevant SQL statement is rewritten to route to the cold library in the third project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0081] In some optional implementations, the SQL is rewritten based on the original SQL statement and query tags to obtain the rewritten SQL, including: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding fourth project library, where the fourth project library has cold and hot query tags. For the fourth project library, the relevant SQL statements are first rewritten to route to the cold library of the fourth project library, resulting in the first rewritten SQL. The first rewritten SQL is then sent to the backend execution layer to obtain the first query result. If the query result for the cold storage of the fourth project library is empty in the first query result, then for the fourth project library, the relevant SQL statement is rewritten to route to the hot storage of the fourth project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

[0082] In some optional implementations, the method further includes: removing null values ​​from the first query result and the second query result to obtain the query result.

[0083] In some optional implementations, it also includes: data source information, business logic library information, and hot / cold rule information in the configuration service layer.

[0084] Figure 3 This illustration shows a possible structure of an electronic device provided in an embodiment of this application. (Refer to...) Figure 3 The electronic device includes a processor, memory, and a communication interface, which are interconnected and communicate with each other via a communication bus and / or other forms of connection mechanism (not shown).

[0085] The memory includes one or more (only one is shown in the figure), which can be, but is not limited to, Random Access Memory (RAM), Read Only Memory (ROM), Programmable Read-Only Memory (PROM), Erasable Programmable Read-Only Memory (EPROM), Electrically Erasable Programmable Read-Only Memory (EEPROM), etc. The processor and other possible components can access the memory, reading and / or writing data to it.

[0086] The processor comprises one or more (only one is shown in the figure), which can be an integrated circuit chip with signal processing capabilities. The aforementioned processor can be a general-purpose processor, including a Central Processing Unit (CPU), a Microcontroller Unit (MCU), a Network Processor (NP), or other conventional processors; it can also be a special-purpose processor, including a Neural-network Processing Unit (NPU), a Graphics Processing Unit (GPU), a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field-Programmable Gate Array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. Furthermore, when there are multiple processors, some can be general-purpose processors, and others can be special-purpose processors.

[0087] The communication interface includes one or more (only one is shown in the figure), which can be used to communicate directly or indirectly with other devices to exchange data. The communication interface may include interfaces for wired and / or wireless communication.

[0088] One or more computer program instructions may be stored in the memory, and the processor may read and execute these computer program instructions to implement the methods provided in the embodiments of this application.

[0089] Understandable. Figure 3 The structure shown is for illustrative purposes only; the electronic device may also include structures that are more advanced than those shown. Figure 3 The more or fewer components shown, or having the same Figure 3 The different structures shown. Figure 3 The components shown can be implemented using hardware, software, or a combination thereof. Electronic devices may be physical devices, such as PCs, laptops, tablets, mobile phones, servers, embedded devices, etc., or they may be virtual devices, such as virtual machines, virtualized containers, etc. Furthermore, electronic devices are not limited to a single device; they can also be a combination of multiple devices or a cluster of a large number of devices.

[0090] This application provides a computer program product, including a computer program / instruction, which, when executed by a processor, implements the steps of any of the methods described above.

[0091] In the embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. The apparatus embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. Furthermore, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Additionally, the displayed or discussed mutual couplings, direct couplings, or communication connections may be through some communication interfaces; indirect couplings or communication connections between devices or units may be electrical, mechanical, or other forms.

[0092] Furthermore, the units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0093] Furthermore, the functional modules in the various embodiments of this application can be integrated together to form an independent part, or each module can exist independently, or two or more modules can be integrated to form an independent part.

[0094] In this document, relational terms such as first and second are used only to distinguish one entity or operation from another entity or operation, without necessarily requiring or implying any such actual relationship or order between these entities or operations.

[0095] The above description is merely an embodiment of this application and is not intended to limit the scope of protection of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A database proxy system, characterized in that, include: Front-end access layer, service layer, and back-end execution layer; The front-end access layer connects to the business system, and the front-end access layer, service layer, and back-end execution layer are connected in sequence. The back-end execution layer connects to the hot storage and the cold storage. The front-end access layer is used to: receive SQL requests from the business system and decode them to obtain the original SQL statement; detect the query flags of the project library from the original SQL statement, wherein the project library includes cold storage and hot storage; and send the original SQL statement and query flags to the service layer. The service layer is used to: rewrite the SQL based on the original SQL statement and query tags to obtain the rewritten SQL; and send the rewritten SQL to the backend execution layer. The backend execution layer is used to: query the corresponding cold storage and hot storage according to the rewritten SQL, and obtain the query results.

2. The system as described in claim 1, characterized in that, The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding first project library, wherein the first project library has no query mark; For the first project library, rewrite the relevant SQL statements to route to the hot library of the first project library.

3. The system as described in claim 1, characterized in that, The query markers include cold query markers; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding second project library, wherein the second project library has a cold query tag; For the second project library, rewrite the relevant SQL statements to route to the cold storage of the second project library.

4. The system as described in claim 1, characterized in that, The query tags include hot and cold query tags; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, determine the corresponding third project library, wherein the third project library has hot and cold query tags; For the third project library, the relevant SQL statements are first rewritten to be routed to the hot library of the third project library to obtain the first rewritten SQL, and the first rewritten SQL is sent to the backend execution layer to obtain the first query result; If the query result for the hot library of the third project library in the first query result is empty, then for the third project library, the relevant SQL statement is rewritten to route to the cold library of the third project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

5. The system as described in claim 1, characterized in that, Query tags include hot and cold query tags; The step of rewriting the SQL based on the original SQL statement and query tags to obtain the rewritten SQL includes: Based on the target business logic library corresponding to the original SQL statement, the corresponding fourth project library is determined, wherein the fourth project library has cold and hot query tags. For the fourth project library, the relevant SQL statements are first rewritten to route to the cold library of the fourth project library, resulting in the first rewritten SQL. The first rewritten SQL is then sent to the backend execution layer to obtain the first query result. If the query result for the cold storage of the fourth project library in the first query result is empty, then for the fourth project library, the relevant SQL statement is rewritten to route to the hot storage of the fourth project library to obtain the second rewritten SQL, and the second rewritten SQL is sent to the backend execution layer to obtain the second query result.

6. The system as described in claim 4 or 5, characterized in that, The backend execution layer is further configured to: remove null values ​​from the first query result and the second query result to obtain the query result.

7. The system as described in claim 1, characterized in that, Also includes: Management backend; The service layer is connected to the management backend, and the management backend is used to configure the data source information, business logic library information and hot / cold rule information in the service layer. The data source information includes: address information and verification information for each hot storage facility, and address information and verification information for each cold storage facility. The business logic library information includes: the project library corresponding to each business logic library; The hot and cold rule information includes the correspondence between hot and cold storage facilities within each project warehouse.

8. The system as described in claim 7, characterized in that, The data source information also includes: connection configuration information, database configuration information, and request timeout information; the verification information includes username and password.

9. The system as described in claim 1, characterized in that, The backend execution layer is further configured to: encode the query results according to a set format to obtain encapsulated query results; and send the encapsulated query results to the service layer. The server layer is also used to: forward the encapsulated query results to the front-end access layer; The front-end access layer is also used to: return the encapsulated query results to the business system.

10. A database proxy method, characterized in that, include: Receive SQL requests from the business system and decode them to obtain the original SQL statements; detect the query tags of the project library from the original SQL statements, wherein the project library includes cold storage and hot storage; Based on the original SQL statement and query tags, the SQL is rewritten to obtain the rewritten SQL. Based on the rewritten SQL, query the corresponding cold storage and hot storage to obtain the query results.

11. An electronic device, characterized in that, include: A processor and a memory, the memory storing machine-readable instructions executable by the processor, which, when executed by the processor, perform the method as described in claim 10.

12. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method of claim 10.

Images