Method, system, and apparatus for generating SQL instance-based scheduled tasks

Abstract

This invention discloses a method, system, and apparatus for generating SQL instance-based scheduling tasks, comprising: acquiring SQL query logs for each period of the report to be queried; extracting all date parameters and other parameters besides the date parameters from the SQL query logs; determining the anchor date based on the date parameters of the parameterized SQL; matching with a preset scheduling rule set, and performing functional processing on the remaining date parameters except the anchor date to obtain a parameter function model of all date parameters and the anchor date when a preset condition is met; performing verification and obtaining an aggregation combination; if the verification is successful, generating a scheduling task and matching scheduling parameters based on the aggregation combination. This invention achieves extreme performance in fixed query modes such as reports through a very lightweight solution, ensuring a fast experience every time the report is opened in this scenario, improving upon the problem of slow initial opening caused by the post-event mechanism of the original caching solution, and also greatly reducing data usage costs.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method, system, and apparatus for generating SQL instance scheduling tasks. Background Technology

[0002] In data reporting and analysis scenarios, taking products like SmartBi and Tableau as examples, a common user scenario is to perform secondary processing on a specified dataset and then publish it as a data dashboard using visualization components. Such scenarios typically require an exceptional user experience, especially for VIP reports. Considering factors such as report rendering and network data transmission, the time allotted for data queries is extremely limited, requiring sub-second response times. In this situation, two main technical solutions are commonly used: 1. Feeding the dataset back to a high-performance MPP database for faster response times; 2. Implementing a caching layer. This is a lightweight and universal technical solution that caches all queried SQL queries, ensuring an optimal user experience the next time the data is accessed.

[0003] However, neither of these two solutions can fully meet the extreme performance requirements of this scenario. MPP's OLAP database addresses the time required for data computation. It can reduce query response time from minutes to within 10 seconds (sub-seconds), but under conditions of large-scale data scanning and throughput, it usually still cannot meet the millisecond-level response requirements of reporting scenarios. The second solution has even stricter requirements; it typically can only satisfy requests that have already been queried, and the request SQL must be exactly the same to achieve millisecond-level feedback. It has no effect on initial access. Summary of the Invention

[0004] To address the shortcomings of existing technologies, this invention provides a method, system, and apparatus for generating SQL instance scheduling tasks.

[0005] To solve the above-mentioned technical problems, the present invention provides the following technical solution:

[0006] A method for generating SQL instance-based scheduled tasks includes the following steps:

[0007] Retrieve the SQL query logs for each period of the report to be queried;

[0008] Extract all date parameters and other parameters except date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL;

[0009] Anchor dates are determined based on date parameters in parameterized SQL;

[0010] By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions, and the parameter function model of all date parameters and the anchor date is obtained.

[0011] The parameter function model is validated and aggregated combinations are obtained;

[0012] If the verification is successful, a scheduling task and matching scheduling parameters will be generated based on the aggregation and combination.

[0013] As one possible implementation method, the preset conditions include:

[0014] All functions; or,

[0015] The number of iterations reaches a preset threshold; or,

[0016] If there are still unfunction-defined date parameters after the number of iterations reaches a preset threshold, then the unfunction-defined date parameters will be treated as date constants.

[0017] As one possible implementation, the step of functionalizing the remaining date parameters (excluding the anchor date) to achieve a preset condition and obtaining a parameter function model of all date parameters and the anchor date includes the following steps:

[0018] If all of them are function-based, then we get the parameter function model of all date parameters and anchor dates;

[0019] If not all dates are functionalized, the process is iterated, and the already functionalized dates are used as new anchor dates. The matching process is repeated until all dates are functionalized or the number of iterations reaches a preset threshold. Then, the parameter function model of all date parameters and anchor dates is obtained.

[0020] If there are still unfunctioned date parameters after the number of iterations reaches a preset threshold, then the unfunctioned date parameters are treated as date constants, thus obtaining the parameter function model of all date parameters and anchor dates.

[0021] As one possible implementation, the verification of the parameter function model includes the following steps:

[0022] The accuracy of detecting specific date rules that exhibit periodic changes;

[0023] If date constants still exist, then date feature matching is performed on the date constants;

[0024] If a match is successful, the date constant is converted into a function to continue grouping, resulting in an aggregate combination.

[0025] As one possible implementation method, the following steps are also included:

[0026] Determine if the proportion of aggregated combinations is greater than a preset threshold; if so, complete the date feature matching.

[0027] If not, check if a date constant exists. If it does, perform date feature matching to obtain the matching result.

[0028] As one possible implementation, determining the anchor date based on parameterized SQL date parameters includes the following steps:

[0029] Select the largest date from the date parameters and use that date as the anchor date; or,

[0030] Select the smallest date from the date parameters and use that smallest date as the anchor date; or,

[0031] Select any date from the date parameters to use that date as the anchor date.

[0032] As one possible implementation, extracting all date parameters and other parameters besides the date parameters from the SQL query log to achieve SQL parameterization includes the following steps:

[0033] Extract all date parameters and other parameters from the SQL query log;

[0034] By treating date parameters as dynamic parameters and other parameters as constant parameters, parameterization of SQL can be achieved.

[0035] A task generation system based on SQL instance scheduling includes a data acquisition module, a parameterization module, an anchor date determination module, a matching establishment module, a verification module, and a generation module.

[0036] The data acquisition module is used to acquire the SQL query logs for each period of the report to be queried;

[0037] The parameterization module extracts all date parameters and other parameters besides date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL.

[0038] The anchor date determination module determines the anchor date based on the date parameters of parameterized SQL;

[0039] The matching establishment module is used to perform matching by combining the anchor date with a preset scheduling rule set, and to perform functional processing on the remaining date parameters other than the anchor date to achieve preset conditions and obtain the parameter function model of all date parameters and the anchor date.

[0040] The verification module is used to verify the parameter function model and obtain the aggregate combination;

[0041] The generation module is configured such that if the verification is successful, a scheduling task and matching scheduling parameters are generated based on the aggregation and combination.

[0042] A computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the method described below:

[0043] Retrieve the SQL query logs for each period of the report to be queried;

[0044] Extract all date parameters and other parameters except date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL;

[0045] Anchor dates are determined based on date parameters in parameterized SQL;

[0046] By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions, and the parameter function model of all date parameters and the anchor date is obtained.

[0047] The parameter function model is validated and aggregated combinations are obtained;

[0048] If the verification is successful, a scheduling task and matching scheduling parameters will be generated based on the aggregation and combination.

[0049] An apparatus for generating SQL instance scheduling tasks includes a memory, a processor, and a computer program stored in the memory and running on the processor. When the processor executes the computer program, it implements the following method steps:

[0050] Retrieve the SQL query logs for each period of the report to be queried;

[0051] Extract all date parameters and other parameters except date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL;

[0052] Anchor dates are determined based on date parameters in parameterized SQL;

[0053] By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions, and the parameter function model of all date parameters and the anchor date is obtained.

[0054] The parameter function model is validated and aggregated combinations are obtained;

[0055] If the verification is successful, a scheduling task and matching scheduling parameters will be generated based on the aggregation and combination.

[0056] This invention, by adopting the above technical solutions, has significant technical effects:

[0057] This invention achieves ultimate performance in fixed query patterns like reports through a very lightweight solution, ensuring a lightning-fast experience every time the report is opened in this scenario. It improves upon the problem of slow initial opening caused by the post-event mechanism of the original caching solution, while also greatly reducing data usage costs. Attached Figure Description

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

[0059] Figure 1 This is a flowchart illustrating the method of the present invention;

[0060] Figure 2 This is a schematic diagram of the overall structure of the system of the present invention;

[0061] Figure 3 In one embodiment, a specific flowchart for generating a scheduling task is provided using an SQL instance.

[0062] Figure 4 It is a flowchart of the logic for handling conflicts and feature matching. Detailed Implementation

[0063] The present invention will be further described in detail below with reference to the embodiments. The following embodiments are explanations of the present invention, but the present invention is not limited to the following embodiments.

[0064] A method for generating scheduled tasks based on SQL instances, such as... Figure 1 As shown, it includes the following steps:

[0065] S100, Obtain the SQL query log for each period of the report to be queried;

[0066] S200. Extract all date parameters and other parameters except date parameters from the SQL query log to achieve SQL parameterization and obtain parameterized SQL.

[0067] S300: Determine the anchor date based on date parameters in parameterized SQL;

[0068] S400: By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions and obtain the parameter function model of all date parameters and the anchor date.

[0069] S500: Verify the parameter function model and obtain the aggregate combination;

[0070] S600 If the verification is successful, then the scheduled task and matching scheduling parameters are generated based on the aggregation and combination.

[0071] In step S100, the preset conditions include:

[0072] All functions; or,

[0073] The number of iterations reaches a preset threshold; or,

[0074] If there are still unfunction-defined date parameters after the number of iterations reaches a preset threshold, then the unfunction-defined date parameters will be treated as date constants.

[0075] In step S400, the remaining date parameters, excluding the anchor date, are functionalized to meet preset conditions, resulting in a parameter function model of all date parameters and the anchor date. This includes the following steps:

[0076] If all of them are function-based, then we get the parameter function model of all date parameters and anchor dates;

[0077] If not all dates are functionalized, the process is iterated, and the already functionalized dates are used as new anchor dates. The matching process is repeated until all dates are functionalized or the number of iterations reaches a preset threshold. Then, the parameter function model of all date parameters and anchor dates is obtained.

[0078] If there are still unfunctioned date parameters after the number of iterations reaches a preset threshold, then the unfunctioned date parameters are treated as date constants, thus obtaining the parameter function model of all date parameters and anchor dates.

[0079] In step S500, the verification of the parameter function model includes the following steps:

[0080] The accuracy of detecting specific date rules that exhibit periodic changes;

[0081] If date constants still exist, then date feature matching is performed on the date constants;

[0082] If a match is successful, the date constant is converted into a function to continue grouping, resulting in an aggregate combination.

[0083] Step S500 also includes the following steps:

[0084] Determine if the proportion of aggregated combinations is greater than a preset threshold; if so, complete the date feature matching.

[0085] If not, check if a date constant exists. If it does, perform date feature matching to obtain the matching result.

[0086] In one embodiment, determining the anchor date based on the date parameter of parameterized SQL includes the following steps:

[0087] Select the largest date from the date parameters and use that date as the anchor date; or,

[0088] Select the smallest date from the date parameters and use that smallest date as the anchor date; or,

[0089] Select any date from the date parameters to use that date as the anchor date.

[0090] More specifically, the extraction of all date parameters and other parameters besides date parameters from the SQL query log to achieve SQL parameterization includes the following steps:

[0091] Extract all date parameters and other parameters from the SQL query log;

[0092] By treating date parameters as dynamic parameters and other parameters as constant parameters, parameterization of SQL can be achieved.

[0093] The method of this invention is illustrated by the following example:

[0094] The whole process is as follows Figure 3 As shown;

[0095] Extract all date parameters and other parameters from the SQL query log to parameterize the SQL, resulting in parameterized SQL. Specifically, date-related parameters are preserved in their positions and value relationships, while non-date-related parameters are treated as constants. This parameterized SQL is then used as the primary key for subsequent grouping. Here's a practical example. Assume the original SQL is as follows:

[0096] select

[0097] client, city,

[0098] --Business semantics: Query yesterday's order volume

[0099] count(case when create_date='20220110'then order_id end)as ord_cnt,

[0100] --Business semantics: Query the number of orders this month

[0101] count(case when create_date>='20220101'and create_date<='20220131'then order_id end)as month_ord_cnt,

[0102] --Business semantics: Query the order volume compared to the same period last year.

[0103] count(case when create_date='20210110'then ord_id end)as yoy_ord_cnt,

[0104] --Business semantics: Query orders compared to the same period last year and this month.

[0105] count(case when create_date>='20210101'and create_date<='20210131'then order_id end)as month_ord_cnt

[0106] from dwd_order_df

[0107] where ds = '20220110' -- Retrieves the latest partition of the table.

[0108] group by client, city;

[0109] In the SQL above, the global date parameter can be parameterized first to obtain the position and corresponding value of the date parameter in the SQL.

[0110] <![CDATA[ Parameter position ]]> <![CDATA[ Parameter value ]]> <![CDATA[ Parameter function expression ]]> <![CDATA[ 1 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ ]]> <![CDATA[ 2 ]]> <![CDATA[ 20220101 ]]> <![CDATA[ ]]> <![CDATA[ 3 ]]> <![CDATA[ 20220131 ]]> <![CDATA[ ]]> <![CDATA[ 4 ]]> <![CDATA[ 20210110 ]]> <![CDATA[ ]]> <![CDATA[ 5 ]]> <![CDATA[ 20210101 ]]> <![CDATA[ ]]> <![CDATA[ 6 ]]> <![CDATA[ 20210131 ]]> <![CDATA[ ]]> <![CDATA[ 7 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ ]]>

[0111] As shown in the table above, based on the date parameter table, the date parameters of each parameterized SQL statement are horizontally scaled to calculate the maximum date, which is then used as the anchor date. This anchor date is used as the standard. The table can then be iterated once the anchor date is selected. See the table below.

[0112]

[0113]

[0114] Based on the experience of ETL industry experts, some commonly used scheduling rules can be pre-defined, such as yesterday's, last week's, last month's, end of month, and beginning of month rules. These unstandardized date parameters can then be matched with the anchor dates using these rules. Assuming four pre-defined rules are provided: end of month / beginning of month, weekly, monthly, and yearly yearly comparisons, then after the first iteration, the parameters will evolve into the following table.

[0115] <![CDATA[ Parameter position ]]> <![CDATA[ Parameter value ]]> <![CDATA[ Parameter function expression ]]> <![CDATA[ 1 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ yyyyMMdd ]]> <![CDATA[ 2 ]]> <![CDATA[ 20220101 ]]> <![CDATA[ yyyyMM01 ]]> <![CDATA[ 3 ]]> <![CDATA[ 20220131 ]]> <![CDATA[ yyyyMM01,1m,-1d ]]> <![CDATA[ 4 ]]> <![CDATA[ 20210110 ]]> <![CDATA[ yyyyMMdd,-1y ]]> <![CDATA[ 5 ]]> <![CDATA[ 20210101 ]]> <![CDATA[ ]]> <![CDATA[ 6 ]]> <![CDATA[ 20210131 ]]> <![CDATA[ ]]> <![CDATA[ 7 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ yyyyMMdd ]]>

[0116] In the above expressions, 1m represents adding one month to the current date, -1d represents subtracting one day from the current date, and -1y represents subtracting one year from the current date.

[0117] After completing the horizontal matching of the SQL with the anchor date rules, there are still secondary relationships in the business semantics. For example, the fifth parameter mentioned above is actually the year-on-year comparison of the second parameter. It does not have a direct and obvious logical relationship with the anchor date. Therefore, it is necessary to reselect the already function-processed dates as anchor dates. Based on the reselected anchor dates, the remaining unfunction-processed dates are subjected to a second round of rule matching iterations until there are no constant dates or the specified number of iterations expires. After two iterations, the above example can be obtained as shown in the table below.

[0118] <![CDATA[ Parameter position ]]> <![CDATA[ Parameter value ]]> <![CDATA[ Parameter function expression ]]> <![CDATA[ 1 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ yyyyMMdd ]]> <![CDATA[ 2 ]]> <![CDATA[ 20220101 ]]> <![CDATA[ yyyyMM01 ]]> <![CDATA[ 3 ]]> <![CDATA[ 20220131 ]]> <![CDATA[ yyyyMM01,1m,-1d ]]> <![CDATA[ 4 ]]> <![CDATA[ 20210110 ]]> <![CDATA[ yyyyMMdd,-1y ]]> <![CDATA[ 5 ]]> <![CDATA[ 20210101 ]]> <![CDATA[ yyyyMM01,-1y ]]> <![CDATA[ 6 ]]> <![CDATA[ 20210131 ]]> <![CDATA[ yyyyMM01,1m,-1d,-1y ]]> <![CDATA[ 7 ]]> <![CDATA[ 20220110 ]]> <![CDATA[ yyyyMMdd ]]>

[0119] The anchor date of the fifth parameter is the second parameter, matching the year-on-year comparison rule. Therefore, the function expression is the second parameter minus one year (-1y). The anchor date of the sixth parameter is the third parameter, matching the year-on-year comparison rule. Therefore, the function expression is the third parameter minus one year (-1y).

[0120] After the above steps, the date parameterization of the SQL has been completed. However, step S160 is still necessary because month-end and month-beginning rules are directional. When the anchor date itself is month-end or month-beginning, misjudgment is likely to occur, causing the entire subsequent derivation logic to fail completely. Therefore, a verification mechanism is introduced. All historical data of the parameterized SQL is parameterized, and N sets of scheduling parameters (each corresponding to the table above) are extracted according to the above steps. Then, based on clustering, if the proportion of data in the TOP1 cluster exceeds a set threshold, the inference is considered correct, and the verification ends. This eliminates the randomness caused by rule conflicts.

[0121] The final generated task SQL, along with the corresponding scheduling parameters, is as follows:

[0122] select

[0123] client, city,

[0124] --Business semantics: Query yesterday's order volume

[0125] count(case when create_date='${yyyyMMdd}'then order_id end)as ord_cnt,

[0126] --Business semantics: Query the number of orders this month

[0127] count(case when create_date>='${yyyyMM01}'and create_date<='${yyyyMM01,1m,-1d}'then order_id end)as month_ord_cnt,

[0128] --Business semantics: Query the order volume compared to the same period last year.

[0129] count(case when create_date='${yyyyMMdd,-1y}'then ord_id end)as yoy_ord_cnt,

[0130] --Business semantics: Query orders compared to the same period last year and this month.

[0131] count(case when create_date>='${yyyyMM01,-1y}'and create_date<='${yyyyMM01,-1y,1m,-1d}'then order_id end)as month_ord_cnt

[0132] from dwd_order_df

[0133] where ds = '${yyyyMMdd}' -- scheduling date

[0134] group by client, city;

[0135] In addition, the accuracy of special date rules that detect periodic changes and the date feature matching of date constants are also important, namely the completeness of the rule set and the verification conditions for the rule set matching. Regarding the completeness of the rule set, it can be collected and regularized using commonly used business semantics in specific scenarios to improve convergence speed. Another crucial factor is the verification conditions for rule matching. In the above case, assuming the anchor date yyyyMMdd is January 2, 2022, a conflict will occur when matching the rule with parameter 2. The returned result is likely to be affected by the matching order of the rule set. In this patent, special processing is applied to the matching logic of special rules (rules affected by periods). For example, for the rules yesterday and the beginning of the month, when matching the beginning of the month rule, it is checked whether yesterday of the anchor date is also the beginning of the month. When there is a conflict, the rule with higher priority is taken first, such as... Figure 4 As shown.

[0136] Besides handling conflicts, it also serves another purpose: inferring functions for dates outside the established rules based on historical sequences. For example, in a query for dates, the value of a certain date parameter might look like this:

[0137]

[0138]

[0139] In the example above, the relationship between the parameter value and the anchor date is uncertain. After iteration, if the accuracy of the special date rule for detecting periodic changes and the date feature matching of the date constant are improved, then the parameter value can only be treated as a constant. However, after the task is generated, the pre-caching effect cannot be achieved. Furthermore, clustering can extract features from the historical enumeration of parameter values. Here, it's also a date rule, but not a matching rule, but a date feature rule. In the example above, the feature of every Thursday can be matched, so the date function can be inferred. By injecting last Thursday's function, the dynamic function generation can be completed, i.e., the parameter function model for Thursday can be obtained.

[0140] Example 2:

[0141] A task generation system based on SQL instance scheduling, such as Figure 2 As shown, it includes a data acquisition module 100, a parameterization module 200, an anchor date determination module 300, a matching establishment module 400, a verification module 500, and a generation module 600;

[0142] The data acquisition module 100 is used to acquire the SQL query logs for each period of the report to be queried;

[0143] The parameterization module 200 extracts all date parameters and other parameters besides date parameters from the SQL query log to achieve SQL parameterization and obtain parameterized SQL.

[0144] The anchor date determination module 300 determines the anchor date based on the date parameters of parameterized SQL;

[0145] The matching establishment module 400 is used to perform matching by combining the anchor date with a preset scheduling rule set, and to perform functional processing on the remaining date parameters other than the anchor date to achieve preset conditions, thereby obtaining the parameter function model of all date parameters and the anchor date.

[0146] The verification module 500 is used to verify the parameter function model and obtain the aggregate combination;

[0147] The generation module 600 is configured such that if the verification is successful, a scheduling task and matching scheduling parameters are generated based on the aggregation and combination.

[0148] Example 3:

[0149] A computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the method described below:

[0150] S100, Obtain the SQL query log for each period of the report to be queried;

[0151] S200. Extract all date parameters and other parameters except date parameters from the SQL query log to achieve SQL parameterization and obtain parameterized SQL.

[0152] S300: Determine the anchor date based on date parameters in parameterized SQL;

[0153] S400: By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions and obtain the parameter function model of all date parameters and the anchor date.

[0154] S500: Verify the parameter function model and obtain the aggregate combination;

[0155] S600 If the verification is successful, then the scheduled task and matching scheduling parameters are generated based on the aggregation and combination.

[0156] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on the differences from other embodiments. The same or similar parts between the various embodiments can be referred to each other.

[0157] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0158] This invention is described with reference to flowchart illustrations and / or block diagrams of the method, terminal device (system), and computer program product according to the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing terminal device to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing terminal device, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0159] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing terminal device to operate in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0160] These computer program instructions can also be loaded onto a computer or other programmable data processing terminal equipment, causing a series of operational steps to be performed on the computer or other programmable terminal equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable terminal equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0161] It should be noted that:

[0162] The phrase "an embodiment" or "an embodiment" used in this specification means that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one embodiment of the invention. Therefore, the phrase "an embodiment" or "an embodiment" appearing in various places throughout the specification does not necessarily refer to the same embodiment.

[0163] Furthermore, it should be noted that the shapes and names of the parts and components described in the specific embodiments described in this specification may differ. All equivalent or simple variations made to the structure, features, and principles described in this patent concept are included within the protection scope of this patent. Those skilled in the art to which this invention pertains may make various modifications or additions to the described specific embodiments or use similar methods to substitute them, as long as they do not depart from the structure of this invention or exceed the scope defined in these claims, all of which should fall within the protection scope of this invention.

Claims

1. A method for generating tasks based on SQL instance scheduling, characterized in that, Includes the following steps: Retrieve the SQL query logs for each period of the report to be queried; Extract all date parameters and other parameters except date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL; Anchor dates are determined based on date parameters in parameterized SQL; By matching the anchor date with a preset scheduling rule set, the remaining date parameters other than the anchor date are processed into functions to meet the preset conditions, and the parameter function model of all date parameters and the anchor date is obtained. The parameter function model is validated and aggregated combinations are obtained; If the verification is successful, a scheduling task and matching scheduling parameters will be generated based on the aggregation and combination. The step of functionalizing the remaining date parameters (excluding the anchor date) to obtain a parameter function model of all date parameters and the anchor date under preset conditions includes the following steps: If all of them are function-based, then we get the parameter function model of all date parameters and anchor dates; If not all dates are functionalized, the process is iterated, and the already functionalized dates are used as new anchor dates. The matching process is repeated until all dates are functionalized or the number of iterations reaches a preset threshold. Then, the parameter function model of all date parameters and anchor dates is obtained. If there are still unfunctioned date parameters after the number of iterations reaches a preset threshold, then the unfunctioned date parameters are treated as date constants, thus obtaining the parameter function model of all date parameters and anchor dates.

2. The method for generating tasks based on SQL instance scheduling according to claim 1, characterized in that, The preset conditions include: All functions; or, The number of iterations reaches a preset threshold; or, If there are still unfunction-defined date parameters after the number of iterations reaches a preset threshold, then the unfunction-defined date parameters will be treated as date constants.

3. The method for generating tasks based on SQL instance scheduling according to claim 1, characterized in that, The verification of the parameter function model includes the following steps: The accuracy of detecting specific date rules that exhibit periodic changes; If date constants still exist, then date feature matching is performed on the date constants; If a match is successful, the date constant is converted into a function to continue grouping, resulting in an aggregate combination.

4. The method for generating tasks based on SQL instance scheduling according to claim 3, characterized in that, It also includes the following steps: Determine if the proportion of aggregated combinations is greater than a preset threshold; if so, complete the date feature matching. If not, check if a date constant exists. If it does, perform date feature matching to obtain the matching result.

5. The method for generating tasks based on SQL instance scheduling according to claim 1, characterized in that, The method of determining the anchor date based on parameterized SQL date parameters includes the following steps: Select the largest date from the date parameters and use that date as the anchor date; or, Select the smallest date from the date parameters and use that smallest date as the anchor date; or, Select any date from the date parameters to use that date as the anchor date.

6. The method for generating tasks based on SQL instance scheduling according to claim 1, characterized in that, The process of extracting all date parameters and other parameters besides date parameters from the SQL query log to achieve SQL parameterization includes the following steps: Extract all date parameters and other parameters from the SQL query log; By treating date parameters as dynamic parameters and other parameters as constant parameters, parameterization of SQL can be achieved.

7. A task generation system based on SQL instance scheduling, characterized in that, It includes a data acquisition module, a parameterization module, an anchor date determination module, a matching establishment module, a verification module, and a generation module; The data acquisition module is used to acquire the SQL query logs for each period of the report to be queried; The parameterization module extracts all date parameters and other parameters besides date parameters from the SQL query log to parameterize the SQL and obtain parameterized SQL. The anchor date determination module determines the anchor date based on the date parameters of parameterized SQL; The matching establishment module is used to perform matching by combining the anchor date with a preset scheduling rule set, and to perform functional processing on the remaining date parameters other than the anchor date to achieve preset conditions and obtain the parameter function model of all date parameters and the anchor date. The verification module is used to verify the parameter function model and obtain the aggregate combination; The generation module is configured to: if the verification is successful, generate a scheduling task and matching scheduling parameters based on the aggregation and combination. The step of functionalizing the remaining date parameters (excluding the anchor date) to obtain a parameter function model of all date parameters and the anchor date under preset conditions includes the following steps: If all of them are function-based, then we get the parameter function model of all date parameters and anchor dates; If not all dates are functionalized, the process is iterated, and the already functionalized dates are used as new anchor dates. The matching process is repeated until all dates are functionalized or the number of iterations reaches a preset threshold. Then, the parameter function model of all date parameters and anchor dates is obtained. If there are still unfunctioned date parameters after the number of iterations reaches a preset threshold, then the unfunctioned date parameters are treated as date constants, thus obtaining the parameter function model of all date parameters and anchor dates.

8. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method as described in any one of claims 1 to 6.

9. An apparatus for generating SQL instance-based scheduled tasks, comprising a memory, a processor, and a computer program stored in the memory and running on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method as described in any one of claims 1 to 6.

Images