Unified query routing service implementation method and device, equipment and storage medium

Abstract

The invention discloses a unified query routing service implementation method and device, equipment and a storage medium, and relates to the technical field of databases, the method comprises the steps of obtaining an SQL task, judging whether a from table of the SQL can be routed to ClickHouse or not, whether the SQL does not contain an operator which cannot be routed to the ClickHouse or not, and whether the query storage period of the SQL is overdue or not: if yes, executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step of executing the step; if yes, carrying out replacement operation on the custom partition field in the SQL, and routing the SQL to the ClickHouse; and if not, routing the SQL to the Hive. According to the method and the device, efficient data access and processing between the Hive database and the ClickHouse database can be realized.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, equipment and storage medium for implementing a unified query routing service. Background Art

[0002] When customers use Hive (a data warehouse tool based on the distributed system infrastructure Hadoop) and ClickHouse (a column-based database) databases in production environments, there are performance issues, especially when processing large amounts of data and complex queries. When using Trino (an open source distributed query engine) to connect to Hive and execute queries through standard SQL (Structured Query Language), due to the large time span of data partitions and the huge amount of data, queries are often stuck or respond slowly, and in some cases, they are prone to query timeouts. This situation becomes more prominent in a big data environment, especially when the amount of data entering the table reaches tens of billions in a day, the query performance of Hive obviously cannot meet customer needs; at the same time, when using Trino to point to ClickHouse to execute tasks, due to the difference between ClickHouse's SQL syntax and standard SQL, parsing fails and errors are reported, which requires a lot of time and effort to manually modify SQL statements, further affecting work efficiency.

[0003] Furthermore, ClickHouse performs poorly in handling join operations (JOIN) and deduplication (DISTINCT), especially when joining large tables with small tables, where performance is extremely low. Although customers try to optimize performance by turning off virtual memory, adjusting the join_use_nulls configuration (used to control how NULL values ​​are handled when performing table join operations), or changing the connection order, these measures do not significantly improve query capabilities compared to Hive. Therefore, how to achieve efficient data access and processing between Hive and ClickHouse databases has become an urgent problem to be solved. Summary of the invention

[0004] The present application provides a unified query routing service implementation method, apparatus, device and storage medium, which can realize efficient data access and processing between Hive and ClickHouse databases.

[0005] In a first aspect, an embodiment of the present application provides a unified query routing service implementation method, the unified query routing service implementation method comprising:

[0006] Get the SQL task, determine whether the SQL from table can be routed to ClickHouse, whether the SQL does not contain operators that cannot be routed to ClickHouse, and whether the SQL query storage period has expired:

[0007] If both are true, replace the custom partition field in SQL and route SQL to ClickHouse;

[0008] If both are not true, the SQL will be routed to Hive.

[0009] In combination with the first aspect, in one implementation, the judgment of whether the from table of SQL can be routed to ClickHouse, whether the SQL does not contain an operator, and whether the query storage period of SQL has expired specifically includes:

[0010] Get the SQL task submitted in the trino client and determine whether the from table in the SQL can be routed to ClickHouse:

[0011] If not, route the SQL to Hive;

[0012] If yes, check whether the SQL contains no operators that cannot be routed to ClickHouse:

[0013] -If it contains operators that cannot be routed to ClickHouse, SQL is routed to Hive;

[0014] -If it does not contain operators that cannot be routed to ClickHouse, determine whether the query storage period of SQL has expired. If so, replace the custom partition field in SQL and route SQL to ClickHouse. If not, route SQL to Hive.

[0015] In combination with the first aspect, in one implementation, the determination of whether the from table of SQL can be routed to ClickHouse specifically includes:

[0016] According to the configured routable ClickHouse table, determine whether the from table in SQL is routable to ClickHouse:

[0017] If it can be routed to ClickHouse, then check whether the SQL contains no operators that cannot be routed to ClickHouse;

[0018] If routing to ClickHouse is not possible, SQL is routed to Hive.

[0019] In conjunction with the first aspect, in one implementation, determining whether the SQL query storage period has expired specifically includes:

[0020] Determine whether the session is configured to ignore storage cycle routing ClickHouse:

[0021] If yes, replace the custom partition field in SQL and route SQL to ClickHouse;

[0022] If not, continue to determine whether the SQL query storage period has expired. If not, route the SQL to Hive. If so, replace the custom partition field in the SQL and route the SQL to ClickHouse.

[0023] In combination with the first aspect, in one implementation, the continuing to determine whether the SQL query storage period has expired specifically includes:

[0024] Based on the custom partition fields and time conditions in SQL, determine whether the query storage period of SQL exceeds the storage period of the structured library configured for the routable ClickHouse table to which SQL belongs:

[0025] If yes, replace the custom partition field in SQL and route SQL to ClickHouse;

[0026] If not, the SQL is routed to Hive.

[0027] In combination with the first aspect, in one implementation, the replacing operation is performed on the custom partition field in the SQL, wherein the replacing operation specifically includes:

[0028] Replace the custom partition field in condition with the ClickHouse partition field and rewrite the value;

[0029] Replace the custom partition field in projection with the ClickHouse partition field and add the udf function conversion type;

[0030] Replace the custom partition field in groupby with the ClickHouse partition field and add the udf function conversion type;

[0031] Replace the custom partition field in orderby with the ClickHouse partition field and add the udf function conversion type.

[0032] In combination with the first aspect, in one implementation,

[0033] When performing a replacement operation, if all replacements are successful, the SQL is routed to ClickHouse, otherwise, the SQL is routed to Hive.

[0034] In a second aspect, an embodiment of the present application provides a unified query routing service implementation device, the unified query routing service implementation device comprising:

[0035] A judgment module is used to obtain SQL tasks, judge whether the from table of SQL can be routed to ClickHouse, whether SQL does not contain operators that cannot be routed to ClickHouse, and whether the query storage period of SQL has expired. If all of them are yes, the first execution module is driven to work; if all of them are not yes, the second execution module is driven to work;

[0036] The first execution module is used to replace the custom partition field in SQL and route SQL to ClickHouse;

[0037] The second execution module is used to route SQL to Hive.

[0038] In a third aspect, an embodiment of the present application provides a unified query routing service implementation device, the unified query routing service implementation device comprising a processor, a memory, and a unified query routing service implementation program stored on the memory and executable by the processor, wherein when the unified query routing service implementation program is executed by the processor, the steps of the unified query routing service implementation method described above are implemented.

[0039] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, on which a unified query routing service implementation program is stored, wherein when the unified query routing service implementation program is executed by a processor, the steps of the unified query routing service implementation method described above are implemented.

[0040] The beneficial effects brought by the technical solution provided by the embodiments of the present application include:

[0041] When the daily data volume of a form is very large, the table name to be queried can be added through the Trino service background configuration item rules. After adding, the query of the table can be directly routed to Clickhouse, thus avoiding the problem of slow Hive query. At the same time, the query cycle can be set. When the query cycle expires, it is routed to Clickhouse, which effectively avoids the problem of slow query through the directory pointing to Hive when the partition data volume is too large. In view of the fact that Clickhouse is not good at aggregate function calculation, it can be directly routed to Hive through configuration to ensure query performance and efficiency. Replace the custom partition field with the Clickhouse partition field, and add udf custom function to transform the SQL adapted to Clickhouse to avoid Clickhouse parsing failure and ensure stability and availability. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 A flowchart of a unified query routing service implementation method for this application;

[0043] Figure 2 A schematic diagram of the functional modules of the device for implementing the unified query routing service of this application;

[0044] Figure 3 Schematic diagram of the hardware structure of the device implementing the unified query routing service for this application. DETAILED DESCRIPTION

[0045] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0046] In order to make the objectives, technical solutions and advantages of the present application clearer, the implementation methods of the present application will be further described in detail below in conjunction with the accompanying drawings.

[0047] In a first aspect, an embodiment of the present application provides a method for implementing a unified query routing service.

[0048] In one embodiment, referring to Figure 1 , Figure 1 This is a flowchart of the unified query routing service implementation method for this application. Figure 1 As shown, the unified query routing service implementation method includes:

[0049] S1: Get SQL tasks, determine whether the SQL from table (used to specify the data source of the query) can be routed to ClickHouse, whether the SQL does not contain operators that cannot be routed to ClickHouse, and whether the SQL query storage period has expired. If all are yes, go to S2, if not, go to S3;

[0050] S2: Replace the custom partition field in SQL and route SQL to ClickHouse;

[0051] S3: Routes SQL to Hive.

[0052] The custom partition field is a custom function that can be annotated as time, such as dayhour. Because the original library table is created using the Hive engine, it contains some custom time fields, such as dayhour (2024-12-16). If you want to route the data to ClickHouse, SQL will fail when parsing the dayhour field. At this time, you need to replace it with a TIMESTAMP type field supported by ClickHouse, so that when the above conditions are triggered, it can be executed normally.

[0053] Further, in one embodiment, the judgment of whether the from table of SQL can be routed to ClickHouse, whether the SQL does not contain an operator, and whether the query storage period of SQL has expired specifically includes:

[0054] Get the SQL task submitted in the trino client and determine whether the from table in the SQL can be routed to ClickHouse:

[0055] If not, route the SQL to Hive;

[0056] If yes, check whether the SQL contains no operators that cannot be routed to ClickHouse:

[0057] -If it contains operators that cannot be routed to ClickHouse, SQL is routed to Hive;

[0058] -If it does not contain operators that cannot be routed to ClickHouse, determine whether the query storage period of SQL has expired. If so, replace the custom partition field in SQL and route SQL to ClickHouse. If not, route SQL to Hive.

[0059] Among them, the judgment of whether the SQL contains no operators that cannot be routed to ClickHouse is to judge whether there are operators such as aggregation and join in the SQL, because ClickHouse does not support operator configuration.

[0060] Further, in one embodiment, the determination of whether the from table of SQL can be routed to ClickHouse specifically includes:

[0061] According to the configured routable ClickHouse table, determine whether the from table in SQL is routable to ClickHouse:

[0062] If it can be routed to ClickHouse, then check whether the SQL contains no operators that cannot be routed to ClickHouse;

[0063] If routing to ClickHouse is not possible, SQL is routed to Hive.

[0064] Specifically, the user submits a SQL task in the Trino client, and then determines whether the from table in the SQL can be routed to ClickHouse. If any table cannot be routed to ClickHouse, SQL can be routed directly to Hive.

[0065] Further, in one embodiment, the determination of whether the SQL query storage period has expired specifically includes:

[0066] Determine whether the session (window) is configured to ignore storage cycle routing ClickHouse:

[0067] If yes, replace the custom partition field in SQL and route SQL to ClickHouse;

[0068] If not, continue to determine whether the SQL query storage period has expired. If not, route the SQL to Hive. If so, replace the custom partition field in the SQL and route the SQL to ClickHouse.

[0069] Further, in one embodiment, continuing to determine whether the SQL query storage period has expired specifically includes:

[0070] Based on the custom partition fields and time conditions in SQL, determine whether the query storage period of SQL exceeds the storage period of the structured library configured for the routable ClickHouse table to which SQL belongs:

[0071] If yes, replace the custom partition field in SQL and route SQL to ClickHouse;

[0072] If not, the SQL is routed to Hive.

[0073] Specifically, determine whether the session is configured to ignore the storage period routing ClickHouse (i.e. header: route_ck_ignore_period = true). If not configured, determine whether the query storage period of SQL exceeds the storage period of the structured library configured for the routable ClickHouse table to which SQL belongs (such as 30 days) based on the custom partition field and time conditions in SQL. If so, replace the custom partition field in SQL and route SQL to ClickHouse.

[0074] It should be noted that the judgment of whether the from table of SQL in this application can be routed to ClickHouse, whether the SQL does not contain operators, and whether the query storage period of SQL has expired is specifically as follows: first, determine whether the from table in SQL can be routed to ClickHouse. If it cannot be routed to ClickHouse, route SQL to Hive. If it can be routed to ClickHouse, continue to determine whether SQL does not contain operators that cannot be routed to ClickHouse. If it contains operators that cannot be routed to ClickHouse, route SQL to Hive. If it does not contain operators that cannot be routed to ClickHouse, continue to determine whether the session is configured to ignore the storage period routing ClickHouse. If configured, replace the custom partition field in SQL and route SQL to ClickHouse. If not configured, continue to determine whether the query storage period of SQL has expired. If not, route SQL to Hive. If expired, replace the custom partition field in SQL and route SQL to ClickHouse.

[0075] Furthermore, in one embodiment, a replacement operation is performed on the custom partition field in SQL, wherein the replacement operation specifically includes:

[0076] Replace the custom partition field in condition (a mechanism used to limit query results in query statements) with the ClickHouse partition field and rewrite the value;

[0077] Replace the custom partition field in projection with the ClickHouse partition field and add the udf (User-Defined Function) function conversion type;

[0078] Replace the custom partition field in groupby (used to group data according to the specified column and perform aggregation operations on each group) with the ClickHouse partition field and add the udf function conversion type;

[0079] Replace the custom partition field in orderby (used to sort the result set) with the ClickHouse partition field and add the udf function conversion type.

[0080] Specifically, according to the source table, if it is routed to ClickHouse, the dayhour field in the SQL is removed, because dayhour is only available in Hive and there is no such partition field in ClickHouse. Find the corresponding replacement name (table storage period configuration item: clickhouseTablePartitionKeyList.keyName). Here, SQL needs to be modified. When routing ClickHouse, the dayhour in the where condition was rewritten before. Since the user used dayhour in the select / groupby / orderby statement block, this part of dayhour needs to be replaced with the ClickHouse partition field before routing ClickHouse; since dayhour is of int type and the ClickHouse time partition field c_stream_time is of timestamp type, direct rewriting will cause the dayhour column type received by the user to change, resulting in parsing exceptions, so c_stream_time needs to be converted to an integer and returned, that is, a udf function conversion type is added. trino-proxy rewrites dayhour to to_yyyymmddhh(c_stream_time), and the trino engine pushes the to_yyyymmddhh function down to the ClickHouse system function toYYYYMMDDhh query. In this way, the function is pushed down and the return value: to_yyyymmddhh(timestamp)→int becomes an integer and no parsing error is reported.

[0081] This application performs routing transformation on the Trino client. For example, when the amount of data or partitions in a table is too large and there is a query demand, Trino can be routed to ClickHouse to take advantage of its high-performance OLAP query and greatly improve query efficiency. For some complex aggregate functions, you can choose to route them back to Hive to avoid the performance bottleneck of ClickHouse when parsing aggregate functions. In addition, in order to ensure smooth execution when sending standard SQL to ClickHouse, the customer plans to add udf functions in the background to replace related fields when converting field types, thereby reducing the risk of SQL task execution failure due to parsing errors, thereby maximizing the advantages of different databases and improving overall query performance.

[0082] By identifying the query cycle and aggregation characteristics of SQL, trino-proxy (a proxy server based on Trino) decides whether to route to a structured data warehouse or a high-density data warehouse. By modifying the header configuration (X-Trino-Catalog) in the client SQL request, when X-Trino-Catalog = Hive, SQL is routed to a structured data warehouse, and when X-Trino-Catalog = Clickhouse, SQL is routed to a high-density data warehouse. The main function implemented is to be able to identify the table level, that is, by setting the query table, storage cycle, aggregation function and other configurations in the configuration items. Specifically, when the query time period exceeds the set value, it is routed to Clickhouse to query the table, and when it is less than the cycle value, it is routed to Hive to query the table; in addition, when the parsed SQL statement contains join, inner join, distinct and other functions, it is directly routed to Hive to query the table; at the same time, the table routed to Clickhouse needs to replace the custom partition field of the original standard SQL with a field of Clickhouse for parsing and querying.

[0083] In this application, trino-proxy modifies the header configuration (X-Trino-Catalog) in the client SQL request. When X-Trino-Catalog = Hive, SQL is routed to the structured data warehouse. When X-Trino-Catalog = Clickhouse, SQL is routed to the high-density data warehouse, thereby achieving flexible data source management and efficient query processing; further, by modifying Trino's configmap configuration in the background, the controllable cycle size, aggregation characteristics and replacement of the original custom fields of the query (for example, routing to Clickhouse) can be dynamically adjusted according to needs to achieve the optimal solution for Trino queries and ensure the maximization of system performance and query efficiency. These adjustments not only improve the response speed of queries, but also enhance the flexibility and adaptability of data processing.

[0084] The unified query routing service implementation method of the embodiment of the present application enables, when a form has a large daily data volume, to add the table name to be queried through the Trino service background configuration item rule, and after adding, to directly route to Clickhouse when querying the table, thus avoiding the problem of slow Hive query; at the same time, the query cycle can be set, and when the query cycle expires, it is routed to Clickhouse, effectively avoiding the problem of slow query through directory pointing to Hive when the partition data volume is too large; in view of the fact that Clickhouse is not good at aggregate function calculation, it can be directly routed to Hive through configuration to ensure query performance and efficiency; the Clickhouse partition field is replaced with the custom partition field, and the UDF custom function is added to transform the SQL adapted to Clickhouse, avoiding Clickhouse parsing failure and ensuring stability and availability.

[0085] In a second aspect, an embodiment of the present application also provides a unified query routing service implementation device.

[0086] In one embodiment, referring to Figure 2 , Figure 2 This is a functional module diagram of the unified query routing service implementation device of this application. Figure 2 As shown, the unified query routing service implementation device includes: a judgment module, a first execution module, and a second execution module.

[0087] The judgment module is used to obtain SQL tasks, judge whether the from table of SQL can be routed to ClickHouse, whether the SQL does not contain operators that cannot be routed to ClickHouse, and whether the query storage period of SQL has expired. If all of them are yes, the first execution module is driven to work; if all of them are not yes, the second execution module is driven to work; the first execution module is used to replace the custom partition field in SQL and route SQL to ClickHouse; the second execution module is used to route SQL to Hive.

[0088] The functional implementation of each module in the above-mentioned unified query routing service implementation device corresponds to each step in the above-mentioned unified query routing service implementation method embodiment, and its functions and implementation processes are not described one by one here.

[0089] In a third aspect, an embodiment of the present application provides a unified query routing service implementation device, which may be a personal computer (PC), a laptop computer, a server, or other device with data processing capabilities.

[0090] Reference Figure 3 , Figure 3 The hardware structure diagram of the unified query routing service implementation device involved in the embodiment of the present application is shown in FIG. In the embodiment of the present application, the unified query routing service implementation device may include a processor, a memory, a communication interface, and a communication bus.

[0091] The communication bus may be of any type and is used to interconnect the processor, the memory, and the communication interface.

[0092] The communication interface includes input / output (I / O) interface, physical interface and logical interface, etc., which are used to interconnect the devices inside the unified query routing service implementation device, and the interface used to interconnect the unified query routing service implementation device with other devices (such as other computing devices or user devices). The physical interface can be an Ethernet interface, a fiber optic interface, an ATM interface, etc.; the user device can be a display, a keyboard, etc.

[0093] The memory can be various types of storage media, such as random access memory (RAM), read-only memory (ROM), non-volatile RAM (NVRAM), flash memory, optical storage, hard disk, programmable ROM (PROM), erasable PROM (EPROM), electrically erasable PROM (EEPROM), etc.

[0094] The processor may be a general-purpose processor, and the general-purpose processor may call the unified query routing service implementation program stored in the memory and execute the unified query routing service implementation method provided by the embodiment of the present application. For example, the general-purpose processor may be a central processing unit (CPU). The method executed when the unified query routing service implementation program is called may refer to the various embodiments of the unified query routing service implementation method of the present application, and will not be repeated here.

[0095] Those skilled in the art will understand that Figure 3 The hardware structure shown in the figure does not constitute a limitation on the present application, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0096] In a fourth aspect, an embodiment of the present application also provides a computer-readable storage medium.

[0097] The computer-readable storage medium of the present application stores a unified query routing service implementation program, wherein when the unified query routing service implementation program is executed by a processor, the steps of the unified query routing service implementation method as described above are implemented.

[0098] Among them, the method implemented when the unified query routing service implementation program is executed can refer to the various embodiments of the unified query routing service implementation method of the present application, and will not be repeated here.

[0099] The terms "including" and "having" and any variations thereof in the specification and claims of this application and the above-mentioned drawings are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not limited to the listed steps or units, but optionally includes steps or units that are not listed, or optionally includes other steps or units inherent to these processes, methods, products or devices. The terms "first", "second" and "third" are used to distinguish different objects, etc., and do not represent a sequence, nor do they limit "first", "second" and "third" to different types.

[0100] In the description of the embodiments of the present application, "exemplary", "for example" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary", "for example" or "for example" in the embodiments of the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary", "for example" or "for example" is intended to present related concepts in a specific way.

[0101] In the description of the embodiments of the present application, unless otherwise specified, “ / ” means or, for example, A / B can mean A or B; the “and / or” in the text is merely a description of the association relationship of associated objects, indicating that three relationships may exist, for example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone. In addition, in the description of the embodiments of the present application, “multiple” refers to two or more than two.

[0102] In some processes described in the embodiments of the present application, multiple operations or steps that appear in a specific order are included, but it should be understood that these operations or steps may not be executed in the order in which they appear in the embodiments of the present application or in parallel, and the sequence number of the operation is only used to distinguish the different operations, and the sequence number itself does not represent any execution order. In addition, these processes may include more or fewer operations, and these operations or steps may be executed in sequence or in parallel, and these operations or steps may be combined.

[0103] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, disk, CD) as described above, and includes a number of instructions for a terminal device to execute the methods described in each embodiment of the present application.

[0104] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A method for implementing a unified query routing service, characterized in that: The unified query routing service implementation method comprises: Get the SQL task, determine whether the SQL from table can be routed to ClickHouse, whether the SQL does not contain operators that cannot be routed to ClickHouse, and whether the SQL query storage period has expired: If both are true, replace the custom partition field in SQL and route SQL to ClickHouse; If both are not true, the SQL will be routed to Hive.

2. A unified query routing service implementation method as claimed in claim 1, characterized in that: The judgment of whether the SQL from table can be routed to ClickHouse, whether the SQL does not contain operators, and whether the SQL query storage period has expired includes: Get the SQL task submitted in the trino client and determine whether the from table in the SQL can be routed to ClickHouse: If not, route the SQL to Hive; If yes, check whether the SQL contains no operators that cannot be routed to ClickHouse: -If it contains operators that cannot be routed to ClickHouse, SQL is routed to Hive; -If it does not contain operators that cannot be routed to ClickHouse, determine whether the query storage period of SQL has expired. If so, replace the custom partition field in SQL and route SQL to ClickHouse. If not, route SQL to Hive.

3. A unified query routing service implementation method as claimed in claim 2, characterized in that: The judgment of whether the SQL from table can be routed to ClickHouse includes: According to the configured routable ClickHouse table, determine whether the from table in SQL is routable to ClickHouse: If it can be routed to ClickHouse, then check whether the SQL contains no operators that cannot be routed to ClickHouse; If routing to ClickHouse is not possible, SQL is routed to Hive.

4. A unified query routing service implementation method as claimed in claim 2, characterized in that: The judgment of whether the SQL query storage period has expired specifically includes: Determine whether the session is configured to ignore storage cycle routing ClickHouse: If yes, replace the custom partition field in SQL and route SQL to ClickHouse; If not, continue to determine whether the SQL query storage period has expired. If not, route the SQL to Hive. If so, replace the custom partition field in the SQL and route the SQL to ClickHouse.

5. A unified query routing service implementation method as claimed in claim 4, characterized in that: The step of continuing to determine whether the SQL query storage period has expired specifically includes: Based on the custom partition fields and time conditions in SQL, determine whether the query storage period of SQL exceeds the storage period of the structured library configured for the routable ClickHouse table to which SQL belongs: If yes, replace the custom partition field in SQL and route SQL to ClickHouse; If not, the SQL is routed to Hive.

6. A method for implementing a unified query routing service according to any one of claims 1 to 5, characterized in that: The replacing operation is performed on the custom partition field in the SQL, wherein the replacing operation specifically includes: Replace the custom partition field in condition with the ClickHouse partition field and rewrite the value; Replace the custom partition field in projection with the ClickHouse partition field and add the udf function conversion type; Replace the custom partition field in groupby with the ClickHouse partition field and add the udf function conversion type; Replace the custom partition field in orderby with the ClickHouse partition field and add the udf function conversion type.

7. A unified query routing service implementation method as claimed in claim 6, characterized in that: When performing a replacement operation, if all replacements are successful, the SQL is routed to ClickHouse, otherwise, the SQL is routed to Hive.

8. A unified query routing service implementation device, characterized in that: The unified query routing service implementation device comprises: A judgment module is used to obtain SQL tasks, judge whether the from table of SQL can be routed to ClickHouse, whether SQL does not contain operators that cannot be routed to ClickHouse, and whether the query storage period of SQL has expired. If all of them are yes, the first execution module is driven to work; if all of them are not yes, the second execution module is driven to work; The first execution module is used to replace the custom partition field in SQL and route SQL to ClickHouse; The second execution module is used to route SQL to Hive.

9. A unified query routing service implementation device, characterized in that: The unified query routing service implementation device includes a processor, a memory, and a unified query routing service implementation program stored in the memory and executable by the processor, wherein when the unified query routing service implementation program is executed by the processor, the steps of the unified query routing service implementation method according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a unified query routing service implementation program, wherein when the unified query routing service implementation program is executed by a processor, the steps of the unified query routing service implementation method according to any one of claims 1 to 7 are implemented.

Images