An efficient multi-source data output method based on FlinkSql

Abstract

The present invention relates to the technical field of multi-source data output, and in particular to an efficient multi-source data output method based on FlinkSql. The method, based on different connector tables, compiles multiple INSERT INTO statements for inserting processed data into corresponding target tables; dynamically configures or statically configures parallelism for the multiple INSERT INTO statements based on data dependency tendency values ​​of the multiple INSERT INTO statements or the complexity of the job topology structures of the multiple INSERT INTO statements; when dynamically configuring parallelism for the multiple INSERT INTO statements, determines the initial parallelism based on the data hot and cold distribution types and / or data association complexity of the multiple INSERT INTO statements, and dynamically increases or decreases the initial parallelism based on the data freshness tolerance and / or real-time data delay of the multiple INSERT INTO statements. The present invention improves output efficiency by improving the accuracy of INSERT INTO statement analysis and thus accurately configuring parallelism.

Description

Technical Field

[0001] The present invention relates to the technical field of multi-source data output, and in particular to an efficient multi-source data output method based on FlinkSql. Background Art

[0002] FlinkSql is a SQL-based data processing method provided by Flink. It allows users to operate on data using familiar SQL syntax, greatly lowering the barrier to entry for data processing. However, in practical applications, multi-source data output based on FlinkSql still faces some challenges. On the one hand, different data sources have different data processing and performance requirements. How to efficiently configure and manage the parallelism of data processing tasks to meet the processing requirements of different data sources is an urgent problem to be solved. Traditional parallelism configuration methods are often based on fixed parameters or experience and cannot be dynamically adjusted according to the real-time characteristics of the data and the operating status of the system, resulting in inefficient data processing and wasted resources.

[0003] For example, Chinese patent application publication number: CN119862198A discloses a Flink-based online data processing method, which includes: selecting icons for data source, data processing target, and data processing method in a visualization page for connection; generating Flink SQL code based on preset codes corresponding to different icons; submitting the Flink SQL code to a Flink cluster, and parsing the Flink SQL code through the Flink cluster to obtain a data source table, a data target table, and corresponding data processing methods; based on the Flink cluster, processing the to-be-processed data of each data source in the data source table through the data target table and the data processing method to obtain data processing results. This solution provides an efficient and flexible online data processing method, provides graphical configuration for process control, improves development efficiency and data processing efficiency, and only requires minimal configuration to achieve high throughput and low latency.

[0004] However, the existing technology has the problem that the analysis of data processing requirements of different data sources is not accurate enough, resulting in inaccurate parallel configuration when executing tasks and causing low data processing efficiency. Summary of the Invention

[0005] To this end, the present invention provides an efficient multi-source data output method based on FlinkSql to overcome the problem in the prior art of inaccurate analysis of data processing requirements for different data sources, resulting in inaccurate parallel configuration when executing tasks and low data processing efficiency.

[0006] To achieve the above objectives, the present invention provides an efficient multi-source data output method based on FlinkSql, comprising:

[0007] Use the CREATE TABLE statement to create multiple different connector tables, each corresponding to a data source or target, and determine the data format, storage location, and connection information;

[0008] Use SELECT statements to write business logic and perform query, transformation, and aggregation operations on input data;

[0009] Based on different connector tables, write several INSERT INTO statements to insert the data processed by the SELECT statement into the corresponding target table;

[0010] Determining dynamically configuring parallelism for the plurality of INSERT INTO statements based on data dependency tendency values ​​of the plurality of INSERT INTO statements or complexity of job topology structures of the plurality of INSERT INTO statements;

[0011] When dynamically configuring parallelism for the plurality of INSERT INTO statements, determining an initial parallelism based on the data hot / cold distribution type and / or data association complexity of the plurality of INSERT INTO statements, and dynamically increasing or decreasing the initial parallelism based on the data freshness tolerance and / or real-time data latency of the plurality of INSERT INTO statements;

[0012] Monitor the progress of each INSERT INTO statement in real time, and adjust the parameters for dynamically configuring parallelism based on the deviation between the execution of the task and the preset deviation, or whether the resource elasticity value fluctuates.

[0013] Furthermore, determining to dynamically configure parallelism for the plurality of INSERT INTO statements includes:

[0014] If the data dependency tendency values ​​of some of the INSERT INTO statements are greater than a preset data dependency tendency value, or the complexity of the job topology structures of some of the INSERT INTO statements is greater than a preset complexity, it is determined to dynamically configure parallelism for some of the INSERT INTO statements.

[0015] Furthermore, the data dependency tendency values ​​of several of the INSERT INTO statements are determined based on the ratio of the number of INSERT INTO statements with strong dependencies among the several INSERT INTO statements to the total number of the several INSERT INTO statements, and the complexity of the job topology structure of several of the INSERT INTO statements is determined based on the number of parallel branches and the proportion of indirect connections in the job topology structure.

[0016] Furthermore, determining the initial parallelism includes:

[0017] If the data hot / cold distribution type of several INSERT INTO statements is hot distribution or the data association complexity is greater than the preset data association complexity, the initial parallelism is determined to be the first parallelism;

[0018] If the data hot / cold distribution type of several INSERT INTO statements is cold distribution and the data association complexity is less than or equal to the preset data association complexity, the initial parallelism is determined to be the second parallelism.

[0019] Furthermore, the hot and cold data distribution types of several INSERT INTO statements are determined, including:

[0020] Record the timestamp of the data processed by each INSERT INTO statement;

[0021] For data that has been accessed, collect access frequency information;

[0022] Calculate the access frequency of each data item and compare it with the set access frequency threshold to determine the number of hot and cold data items. Count the number of hot and cold data items in the data processed by each INSERT INTO statement.

[0023] Determine the hot / cold distribution type of a single INSERT INTO statement based on the ratio of hot / cold data in the data processed by the single INSERT INTO statement.

[0024] Count the hot and cold distribution types of all INSERT INTO statements and then determine the hot and cold distribution types of data in several INSERT INTO statements.

[0025] Furthermore, determining whether to dynamically increase the initial parallelism or dynamically decrease the initial parallelism includes:

[0026] If the data freshness tolerance of several INSERT INTO statements is less than the preset data freshness tolerance or the real-time data delay is greater than the preset real-time data delay, the initial parallelism is dynamically increased.

[0027] If the data freshness tolerance of several INSERT INTO statements is greater than or equal to the preset data freshness tolerance and the real-time data delay is less than or equal to the preset real-time data delay, it is determined that the initial parallelism is dynamically reduced.

[0028] Furthermore, the preset data freshness tolerance is determined according to an average value of data freshness tolerance of several groups of data of the same type under the same conditions.

[0029] Furthermore, the real-time data delay is the difference between the data insertion completion timestamp and the data generation timestamp, and the preset real-time data delay is determined based on the historical average real-time data delay of several groups of identical data processing tasks.

[0030] Furthermore, determining to adjust the judgment parameter for dynamically configuring the parallelism includes:

[0031] If the degree of deviation in the execution of the processing task is greater than the preset degree of deviation or the resource elasticity value fluctuates, it is determined to adjust the preset data dependency tendency value and the preset complexity.

[0032] Furthermore, the adjustment amount of the preset data dependence tendency value is negatively correlated with the degree of deviation in the execution of the processing task, and the adjustment amount of the preset complexity is negatively correlated with the degree of deviation in the execution of the processing task.

[0033] Compared with the existing technology, the present invention has the advantage of allowing developers to output results to multiple different output terminals within a single Flink task. Developers do not need to perform any other processing and only need to focus on their own business logic. They can write tasks according to the normal syntax and logic of FlinkSql. When outputting, they only need to write multiple Insert statements to achieve output from multiple different output sources.

[0034] Furthermore, the present invention can avoid over-allocation or under-allocation of resources by matching the parallelism with the available resources of the cluster (such as the number of CPU cores and the number of available task slots). Reasonable matching of parallelism and resources can maximize the use of cluster resources, improve resource utilization, and reduce operating costs. The static configuration of parallelism can provide a relatively stable execution environment for task execution when the data dependency tendency value and the complexity of the job topology are low, because the dependencies between tasks are simple and the topology is not complex. The fixed parallelism setting can ensure that the tasks are executed in the expected manner, reduce the uncertainty and potential risks caused by the dynamic adjustment of parallelism, thereby improving the stability and reliability of task execution and ensuring the accuracy of data processing results. The above method improves the accuracy of data processing demand analysis for different data sources and thus improves the accuracy of parallelism configuration when executing tasks to improve data processing efficiency.

[0035] Furthermore, the present invention determines the initial parallelism based on the hot and cold distribution type of data and the complexity of data association, which can enable more accurate allocation of system resources. When the data is hot distributed or has high association complexity, a higher first parallelism is set to fully utilize system resources (such as the number of CPU cores, memory size and network bandwidth) to accelerate data processing. For cold distributed and simply associated data, a lower second parallelism is set to avoid excessive competition for resources. Different data characteristics have different requirements for resources. Determining the initial parallelism in this way can dynamically balance resources between different data processing tasks, improve resource utilization of the entire system, and reduce operating costs. The above method improves the accuracy of data processing demand analysis for different data sources and thus improves the accuracy of parallelism configuration when executing tasks to improve data processing efficiency.

[0036] Furthermore, the present invention, when the data freshness tolerance of several INSERT INTO statements is greater than or equal to the preset data freshness tolerance, and the real-time data delay is less than or equal to the preset real-time data delay, means that the current data processing is in good condition and does not require too much resource investment. At this time, the initial parallelism is dynamically reduced by the second adjustment coefficient, which can avoid the system from allocating too many resources to these tasks, thereby using the released resources for other more urgent or more resource-demanding tasks, thereby improving the overall resource utilization. Dynamically adjusting the parallelism according to the data freshness tolerance and the real-time data delay can enable system resources to be accurately allocated according to actual data processing needs. When the data freshness tolerance is less than the preset data freshness tolerance, or the real-time data delay is too late, the system resources can be allocated accurately according to the actual data processing needs. When the real-time data delay is greater than the preset real-time data delay, it indicates that the current data processing speed cannot meet the business requirements for data freshness. At this time, dynamically increasing the initial parallelism with the first adjustment coefficient can speed up data processing, reduce real-time data delay, and ensure that data can be inserted within a time acceptable to the business, meeting the business requirements for data real-time. In scenarios where the data update frequency is unstable, the solution can quickly adjust the parallelism according to changes in real-time data delay and data freshness tolerance. The above method improves the accuracy of data processing demand analysis for different data sources, thereby improving the accuracy of parallelism configuration when executing tasks and improving data processing efficiency.

[0037] Furthermore, in actual data processing scenarios, the present invention dynamically changes the execution status of tasks and the status of system resources. By monitoring the degree of deviation in task execution and the fluctuation of resource elasticity values, abnormal situations and unstable resource states during task execution can be discovered in a timely manner. When deviations or fluctuations occur, the preset data dependency tendency value and preset complexity are adjusted, allowing the system to quickly adapt to these changes and ensure the smooth progress of data processing tasks. Different business scenarios have different data processing requirements and resource usage. This solution can flexibly adjust preset parameters based on the deviation in task execution and resource fluctuations in specific business scenarios, allowing the system to maintain good performance in various business scenarios. Fluctuations in resource elasticity values ​​reflect the usage of system resources. When resource elasticity values ​​fluctuate, it indicates that the allocation of system resources may be unreasonable. By adjusting the preset data dependency tendency value and preset complexity, the parallelism and resource requirements of tasks can be re-evaluated to avoid waste caused by excessive allocation of resources or slow task execution caused by insufficient resources. The above method improves the accuracy of data processing demand analysis for different data sources and thus improves the accuracy of parallelism configuration when executing tasks, thereby improving data processing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 This is a workflow diagram of an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention;

[0039] Figure 2 This is a workflow diagram for determining the data hot and cold distribution types of multiple INSERT INTO statements in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention;

[0040] Figure 3 This is a workflow diagram of a method for determining parallelism configuration in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention;

[0041] Figure 4 This is a workflow diagram for determining whether to dynamically increase or decrease the initial parallelism in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention. DETAILED DESCRIPTION

[0042] In order to make the objects and advantages of the present invention more clearly understood, the present invention is further described below in conjunction with embodiments; it should be understood that the specific embodiments described herein are merely used to explain the present invention and are not intended to limit the present invention.

[0043] The preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are only used to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention.

[0044] See also Figure 1-Figure 4 As shown, Figure 1 This is a workflow diagram of an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention; Figure 2 This is a workflow diagram for determining the hot and cold data distribution types of multiple INSERT INTO statements in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention; Figure 3 This is a workflow diagram of a method for determining parallelism configuration in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention. Figure 4 This is a workflow diagram for determining whether to dynamically increase or decrease the initial parallelism in an efficient multi-source data output method based on FlinkSql in an embodiment of the present invention.

[0045] The embodiment of the present invention provides an efficient multi-source data output method based on FlinkSql, including:

[0046] Step S1: Use the CREATE TABLE statement to create multiple different connector tables, each corresponding to a data source or target, and determine the data format, storage location, and connection information;

[0047] Step S2: Use SELECT statements to write business logic to query, transform, and aggregate input data;

[0048] Step S3: Based on different connector tables, write several INSERT INTO statements to insert the data processed by the SELECT statement into the corresponding target table;

[0049] Step S4, determining dynamically configuring parallelism for the plurality of INSERT INTO statements based on the data dependency tendency values ​​of the plurality of INSERT INTO statements or the complexity of the job topology structures of the plurality of INSERT INTO statements;

[0050] Step S5, when dynamically configuring parallelism for the plurality of INSERT INTO statements, determining an initial parallelism based on the data hot / cold distribution type and / or data association complexity of the plurality of INSERT INTO statements, and determining to dynamically increase or decrease the initial parallelism based on the data freshness tolerance and / or real-time data latency of the plurality of INSERT INTO statements;

[0051] Step S6: monitor the task progress corresponding to each INSERT INTO statement in real time, and adjust the judgment parameters for dynamically configuring parallelism based on the judgment result of the task execution deviation degree and the preset deviation degree, or whether the resource elasticity value fluctuates.

[0052] This invention allows developers to output results to multiple different output terminals within a single Flink task. Developers do not need to perform any other processing and only need to focus on their own business logic. They can write tasks according to the normal syntax and logic of FlinkSql. When outputting, they only need to write multiple Insert statements to achieve output from multiple different output sources.

[0053] In this embodiment of the present invention, a CREATE TABLE statement is used to create a connector table named sales_data_source. This table corresponds to the database of the supermarket cash register system and is used to obtain daily product sales records. The data format is specified as CSV, because sales data records information such as product ID, sales quantity, unit price, and sales time in a simple table format, which is easy to read and process. The storage location is a designated folder on the cash register system server. The connection information includes the server IP address, access port, folder path, and necessary user authentication information to ensure that Flink can successfully read the sales data. The use of SELECT statements to write business logic and perform query, conversion, and aggregation operations on input data includes writing SELECT statements to query product sales data for different time periods (such as daily, weekly, and monthly) and different stores from the sales_data_source table, aggregating the sales quantity to calculate the total sales volume of each product, converting the sales amount, classifying and summarizing it by product category, and analyzing the sales share of each product. At the same time, information on hot-selling and slow-selling products is queried to provide a basis for adjusting sales strategies.

[0054] Specifically, in step S4, when determining whether to dynamically configure parallelism or statically configure parallelism for the plurality of INSERT INTO statements, determining whether to dynamically configure parallelism or statically configure parallelism for the plurality of INSERT INTO statements is based on the data dependency tendency values ​​of the plurality of INSERT INTO statements and / or the complexity of the job topology structures of the plurality of INSERT INTO statements;

[0055] When the data dependency tendency values ​​of the plurality of INSERT INTO statements are greater than a preset data dependency tendency value or the complexity of the job topology structures of the plurality of INSERT INTO statements is greater than a preset complexity, determining to dynamically configure parallelism for the plurality of INSERT INTO statements;

[0056] When the data dependency tendency values ​​of the INSERT INTO statements are less than or equal to a preset data dependency tendency value and the complexity of the job topology structures of the INSERT INTO statements is less than or equal to a preset complexity, it is determined to statically configure parallelism for the INSERT INTO statements.

[0057] Specifically, in step S4, the data dependency tendency values ​​of the several INSERT INTO statements are the ratio of the number of INSERT INTO statements with strong dependencies among the several INSERT INTO statements to the total number of INSERT INTO statements. The complexity of the job topology structure of the several INSERT INTO statements is determined by the sum of the proportion of the number of parallel branches and the proportion of the number of indirect connections in the job topology structure. The preset data dependency tendency value is the average value of the data dependency tendency values ​​of several groups of the same type of data (including but not limited to the same data source, similar data features and the same data processing purpose) within a preset period. The preset complexity is the average value of the complexity of the job topology structures of several groups of the same type of data within a preset period. The strong dependency relationship includes but is not limited to "close time dependence, data integrity dependence and direct dependence". The value range of the preset period is set to 1 day-10 days, and the value of the preset period is preferably 8 days, but the above value is not limited to this. Those skilled in the art can also adjust the value according to actual needs.

[0058] In this embodiment of the present invention, it is assumed that a preset period of 8 days is selected. During these 8 days, a series of INSERT INTO statements are executed every day to complete the data insertion task. The following is an example of the specific situation on a certain day: a total of 10 INSERT INTO statements are executed on that day, respectively recorded as INSERT1-INSERT10. The job topology of these statements can be represented by a directed graph, where nodes represent INSERT INTO statements and edges represent dependencies between statements. After analysis, it is found that INSERT2 depends on the execution result of INSERT1 (a close time dependency, INSERT2 must be executed after INSERT1 is completed), INSERT4 depends on the integrity of the data inserted by INSERT3 (data integrity dependency), and INSERT6 is inserted directly based on the result of INSERT5 (direct dependency). A total of three pairs have strong dependencies. The data dependency propensity value is calculated: According to the definition, the data dependency propensity value is the ratio of the number of INSERT INTO statements with strong dependencies to the total number. Therefore, the data dependency tendency value for that day is 3 / 10=0.3. In the directed graph of the job topology structure, 3 parallel branches (i.e., a set of INSERT INTO statements that can be executed simultaneously) are found, with a total of 10 nodes (i.e., 10 INSERT INTO statements). Therefore, the proportion of parallel branches is 3 / 10=0.3. After statistics, the number of indirect connections in the directed graph (i.e., connections established through other nodes) is 2, so the proportion of indirect connections is 2 / 10=0.2. Calculate the complexity of the job topology structure: The complexity of the job topology structure is the sum of the proportion of parallel branches and the proportion of indirect connections, i.e., 0.3+0.2=0.5. Within the preset period of 8 days, the data dependency tendency value and the complexity of the job topology structure are calculated every day according to the above method, and then the respective averages are taken to obtain the preset data dependency tendency value and the preset complexity. However, the above values ​​are not limited to this, and those skilled in the art can also adjust the values ​​according to actual needs.

[0059] In an embodiment of the present invention, when statically configuring the degree of parallelism for several INSERT INTO statements, the degree of parallelism must be matched to the available resources of the cluster. For example, it can be determined based on the number of CPU cores (95% of the number of CPU cores) or the number of available task slots (95% of the number of task slots). For example, assuming that the enterprise's data processing cluster is a distributed cluster with 8 computing nodes, each equipped with 4 CPU cores, then the total number of CPU cores in the entire cluster is 8*4=32. When statically configuring the degree of parallelism, resources can be allocated based on the number of CPU cores. Generally speaking, to fully utilize CPU resources while avoiding resource contention and performance degradation caused by excessive parallelism, the degree of parallelism can be set to a value slightly smaller than the total number of CPU cores. For example, we set the degree of parallelism to 30 (rounded up). For these 10 INSERT INTO statements, the system will allocate them to these 30 parallel tasks for execution. The specific allocation method can be to allocate them in the order of statement execution, or to dynamically adjust them based on the complexity of the statements and the amount of data. For example, for INSERT INTO statements with large data volumes and high processing complexity, INTO statements can allocate more parallel tasks; while for simple statements, fewer tasks can be allocated, but the above values ​​are not limited to this, and those skilled in the art can also adjust the values ​​according to actual needs.

[0060] The present invention can avoid over-allocation or under-allocation of resources by matching the parallelism with the available resources of the cluster (such as the number of CPU cores and the number of available task slots). By reasonably matching the parallelism with resources, the utilization of cluster resources can be maximized, resource utilization can be improved, and operating costs can be reduced. The static configuration of parallelism can provide a relatively stable execution environment for task execution when the data dependency tendency value and the complexity of the job topology are low, because the dependencies between tasks are simple and the topology is not complex. The fixed parallelism setting can ensure that the tasks are executed in the expected manner, reduce the uncertainty and potential risks caused by the dynamic adjustment of parallelism, thereby improving the stability and reliability of task execution and ensuring the accuracy of data processing results. The above method improves the accuracy of data processing demand analysis for different data sources and thus improves the accuracy of parallelism configuration when executing tasks to improve data processing efficiency.

[0061] Specifically, in step S4, under the condition that the parallelism is dynamically configured for the plurality of INSERT INTO statements, when determining the initial parallelism, the initial parallelism is determined according to the hot and cold data distribution type and / or the complexity of the data association of the plurality of INSERT INTO statements;

[0062] When the data hot and cold distribution type of several INSERT INTO statements is hot distribution or the data association complexity is greater than the preset data association complexity, the initial parallelism is determined to be the first parallelism;

[0063] When the data hot-cold distribution type of the plurality of INSERT INTO statements is cold distribution and the data association complexity is less than or equal to the preset data association complexity, the initial parallelism is determined to be the second parallelism.

[0064] In the embodiment of the present invention, the data association complexity is the number of associated tables. In the data processing process involved in the INSERT INTO statement, the more the number of associated tables, the higher the data association complexity. For example, if an INSERT INTO statement only involves inserting data into a single table, its association complexity is low; if it involves data association operations on three or more tables, the complexity increases accordingly. The number of associated tables can be used as a basic indicator, such as 1 when associating one table, 2 when associating two tables, and so on. The preset data association complexity can be obtained by collecting the data of each INSERT INTO statement in similar data processing tasks in the past. The number of associated tables involved in the INTO statement is calculated, and the average, median, or other statistical metric of these numbers is used as the preset data association complexity. For example, if the average number of associated tables in the past 100 data processing tasks is 2.5, then the preset data association complexity can be set to 3. The first degree of parallelism can be determined based on the number of CPU cores and memory capacity. If the system has N CPU cores, the first degree of parallelism can be set to N / 2 or N / 3. (In actual applications, these ratios are usually verified through experiments to ensure that the system still has sufficient resources to process other tasks. For example, by running tasks on systems with different configurations and observing system performance under different degrees of parallelism, the appropriate ratio can be selected.) 16. The first degree of parallelism should be the smaller of the two results calculated by the above two methods to avoid resource overload. For example, if the system has 16 CPU cores, each task requires 2 GB of memory, and the system has 32 GB of available memory, then the first degree of parallelism is min(16 / 2, 32 / 2) = 8. The second degree of parallelism can also be determined by the number of CPU cores and memory capacity. The second degree of parallelism can be set to N / 4 or N / 8 to ensure that system resources are not over-occupied. For example, if the system has 16 CPU cores, the second degree of parallelism can be set to 4 or 2. If each task requires X GB of memory and the system has M GB of available memory, the second degree of parallelism can be set to M / (2X). For example, if each task requires 2 GB of memory and the system has 32 GB of available memory, the second degree of parallelism can be set to 32 / (2*2) = 8. The second degree of parallelism should be the smaller of the two results calculated by the above two methods to avoid resource waste. For example, if the system has 16 CPU cores, each task requires 2 GB of memory, and the system has 32 GB of available memory, then the second degree of parallelism is min(16 / 4, 32 / (2*2)) = 4.

[0065] Specifically, in step S5, the step of determining the data hot and cold distribution types of several INSERT INTO statements includes:

[0066] Step S5501, record the timestamp of the data processed by each INSERT INTO statement;

[0067] Step S5502: For data with access operations, collect access frequency information;

[0068] Step S5503: Calculate the access frequency of each piece of data and compare it with the set access frequency threshold to determine the number of hot data and cold data, and count the number of hot data and cold data in the data processed by each INSERT INTO statement;

[0069] Step S5504: determining the hot-cold distribution type of the single INSERT INTO statement based on the ratio of hot and cold data in the data processed by the single INSERT INTO statement;

[0070] Step S5505: Count the hot and cold distribution types of all INSERT INTO statements and then determine the hot and cold distribution types of data of several INSERT INTO statements.

[0071] The access frequency threshold described in the embodiment of the present invention can be set as an appropriate threshold by analyzing historical access data, combining business objectives and user behavior characteristics, etc. For example, if it is found that most users' attention to news is concentrated on those contents with a large number of visits within a week, and from a business perspective, data accessed more than 5 times within a week has high value and popularity, the threshold can be set to 5 times. The cold and hot distribution types are determined to include that if the proportion of cold data is greater than the proportion of hot data, it is determined to be a cold data distribution type, otherwise it is a hot data type. However, the above values ​​are not limited to this, and those skilled in the art can also adjust the values ​​according to actual needs.

[0072] In this embodiment of the present invention, it is assumed that the website has three INSERT INTO statements, each used to insert news data from a different section. Assume that the website has three INSERT INTO statements, each used to insert news data from a different section: INSERT INTO statement 1: inserts international news data, with a table structure of (news ID, title, content, release time, generation timestamp); INSERT INTO statement 2: inserts sports news data, with a table structure of (news ID, title, content, release time, generation timestamp); INSERT INTO statement 3: inserts science and technology news data, with a table structure of (news ID, title, content, release time, generation timestamp). When data is inserted, the generation timestamp of each news item is automatically recorded. The website records user access operations to news through a log system, and sets the access frequency threshold to be greater than 5 times per week for hot data and less than or equal to 5 times per week for cold data. The statistical period is one week. INSERT INTO statement 1 (international news): a total of 100 news items are processed. Statistics show that there are 30 news items with an access frequency greater than 5 times, and 70 news items with an access frequency less than or equal to 5 times. INSERT INTO statement 2 (sports news): processes a total of 80 news items. Of these, 60 are accessed more than 5 times, and 20 are accessed less than or equal to 5 times. INSERT INTO statement 3 (technology news): processes a total of 120 news items. Of these, 20 are accessed more than 5 times, and 100 are accessed less than or equal to 5 times. INSERT INTO statement 1: Hot data ratio = 30 / 100 = 0.3, Cold data ratio = 1 - 0.3 = 0.7. Since the cold data ratio is greater than 0.5, the data distribution type for this statement is cold. INSERT INTO statement 2: Hot data ratio = 60 / 80 = 0.75, Cold data ratio = 1 - 0.75 = 0.25.Because the proportion of hot data is greater than 0.5, the data hot-cold distribution type of this statement is hot distribution. INSERT INTO statement 3: Hot data proportion = 20 / 120 ≈ 0.17, cold data proportion = 1 - 0.17 = 0.83. Because the cold data proportion is greater than 0.5, the data hot-cold distribution type of this statement is cold distribution. Calculating the total number of hot data and cold data for all INSERT INTO statements, the total number of hot data = 30 + 60 + 20 = 110, the total number of cold data = 70 + 20 + 100 = 190, the hot data proportion = 110 / (110 + 190) = 0.37, and the cold data proportion = 1 - 0.37 = 0.63. Because the cold data proportion is greater than 0.5, the data hot-cold distribution type of these three INSERT INTO statements is cold distribution.

[0073] The present invention determines the initial parallelism based on the hot and cold distribution type of data and the complexity of data association, which can enable more accurate allocation of system resources. When the data is hotly distributed or has a high degree of association complexity, a higher first parallelism is set to fully utilize system resources (such as the number of CPU cores, memory size and network bandwidth) to accelerate data processing. For cold-distributed data with simple associations, a lower second parallelism is set to avoid excessive competition for resources. Different data characteristics have different requirements for resources. Determining the initial parallelism in this way can dynamically balance resources between different data processing tasks, improve resource utilization of the entire system, and reduce operating costs. The above method improves the accuracy of data processing demand analysis for different data sources and thus improves the accuracy of parallelism configuration when executing tasks to improve data processing efficiency.

[0074] Specifically, in step S5, when determining to dynamically increase the initial parallelism or dynamically decrease the initial parallelism, determining to dynamically increase the initial parallelism or dynamically decrease the initial parallelism is based on the data freshness tolerance and / or real-time data latency of the plurality of INSERT INTO statements;

[0075] When the data freshness tolerance of several INSERT INTO statements is less than the preset data freshness tolerance or the real-time data delay is greater than the preset real-time data delay, determining to dynamically increase the initial parallelism by a first adjustment coefficient;

[0076] When the data freshness tolerance of several INSERT INTO statements is greater than or equal to the preset data freshness tolerance and the real-time data delay is less than or equal to the preset real-time data delay, it is determined to dynamically reduce the initial parallelism by a second adjustment coefficient.

[0077] In the embodiment of the present invention, the values ​​of the first adjustment coefficient and the second adjustment coefficient can be determined by analyzing historical data processing tasks, statistically analyzing the impact of adjusting the parallelism on data processing efficiency and system performance under different data freshness tolerances and real-time data delays, and summarizing the appropriate adjustment coefficient range. For example, after analyzing the past 100 groups of data processing tasks on an e-commerce platform, in the 30 groups of tasks whose data freshness tolerance is less than the preset data freshness tolerance, when the initial parallelism is increased by the first adjustment coefficient, the impact on data processing efficiency and system performance is as follows: when the first adjustment coefficient is between 1.2 and 1.5, the data processing efficiency is improved by an average of 30%. -40%, the system's CPU utilization remains within a reasonable range (70%-80%), and the memory and network bandwidth can also meet the needs well. There is no situation where excessive resource competition causes system performance to decline. When the first adjustment coefficient is greater than 1.5, although the data processing efficiency is further improved (about 40%-50%), the system's CPU utilization exceeds 85%, and the memory and network bandwidth are temporarily strained, occasionally causing delays in the execution of some tasks. The overall system performance is not stable enough. When the first adjustment coefficient is less than 1.2, the data processing efficiency is not significantly improved (only 10%-20%), which still cannot meet the requirements of data freshness. Taking all factors into consideration, in the data processing scenario of this e-commerce platform, for the case where the data freshness tolerance is less than the preset value, the more appropriate range of the first adjustment coefficient is 1.2-1.5. For the 45 groups of tasks whose data freshness tolerance is greater than or equal to the preset data freshness tolerance and whose real-time data delay is less than or equal to the preset real-time data delay, the case of reducing the initial parallelism with the second adjustment coefficient is analyzed. When the second adjustment coefficient is between 0.7 and 0.9, the system resource utilization rate decreases by an average of 10%-20%. The released resources can be reasonably allocated to other tasks, and the execution of the current data processing task is not significantly affected. , data processing efficiency remains stable. When the second adjustment coefficient is less than 0.7, although the resource utilization rate decreases more significantly (about 20%-30%), the execution time of some data processing tasks increases, which may affect other subsequent operations that depend on these tasks. When the second adjustment coefficient is greater than 0.9, the resource utilization rate does not decrease significantly (5%-10%), and the purpose of effectively releasing resources cannot be achieved. It can be seen that in this case, the appropriate value range of the second adjustment coefficient is 0.7-0.9, but the above value is not limited to this. Those skilled in the art can also adjust the value according to actual needs.

[0078] The data freshness tolerance described in the embodiment of the present invention is determined according to the data update frequency. For example, the data update frequency must first be accurately counted and analyzed, and the time interval for data updates must be counted with the help of tools such as log records and monitoring systems; data update frequencies are divided into different levels according to their frequency, with data updated once per second or minute being a high-frequency update; data updated once per hour or day being a medium-frequency update; data updated once per week, month or even year being a low-frequency update, and a corresponding data freshness tolerance is set for each level. According to different update frequency levels, combined with business needs and actual conditions, a suitable data freshness tolerance is set for it. For example, high-frequency update data: Since this type of data has extremely high real-time requirements, the data freshness tolerance is usually set at the second or minute level. For example, the data freshness tolerance for stock trading market data may be set to 1 second or 1 minute to ensure that investors Able to obtain the latest market information, medium-frequency update data: its data freshness tolerance can be set at the hourly or daily level. Taking the website daily visit statistics as an example, the data freshness tolerance can be set to 1 hour or 1 day. As long as the data can be updated within one day, it can meet the needs of business analysis. Low-frequency update data: data freshness tolerance can be set at the weekly, monthly or even grade level. For example, for the company's quarterly financial statement data, the data freshness tolerance can be set to 1 week. The preset data freshness tolerance is the average value of the data freshness tolerance of several groups of the same type of data under the same conditions. The real-time data delay is the difference between the data insertion completion timestamp and the data generation timestamp. The preset real-time data delay is the historical average real-time data delay of several groups of the same data processing tasks, but the above values ​​are not limited to this. Those skilled in the art can also adjust the value according to actual needs.

[0079] In the present invention, when the data freshness tolerance of several INSERT INTO statements is greater than or equal to the preset data freshness tolerance, and the real-time data delay is less than or equal to the preset real-time data delay, it means that the current data processing is in good condition and does not require too much resource investment. At this time, the initial parallelism is dynamically reduced by the second adjustment coefficient to avoid the system from allocating too many resources to these tasks, thereby using the released resources for other more urgent or resource-demanding tasks, thereby improving the overall resource utilization. The parallelism is dynamically adjusted according to the data freshness tolerance and the real-time data delay, so that the system resources can be accurately allocated according to the actual data processing needs. When the data freshness tolerance is less than the preset data freshness tolerance, or the real-time data delay is too late, the system resources can be allocated according to the actual data processing needs. When the real-time data delay is greater than the preset real-time data delay, it indicates that the current data processing speed cannot meet the business requirements for data freshness. At this time, dynamically increasing the initial parallelism with the first adjustment coefficient can speed up data processing, reduce real-time data delay, and ensure that data can be inserted within a time acceptable to the business, meeting the business requirements for data real-time. In scenarios where the data update frequency is unstable, the solution can quickly adjust the parallelism according to changes in real-time data delay and data freshness tolerance. The above method improves the accuracy of data processing demand analysis for different data sources, thereby improving the accuracy of parallelism configuration when executing tasks and improving data processing efficiency.

[0080] Specifically, in step S6, when it is determined that the judgment parameter for dynamically configuring the parallelism is to be adjusted, the judgment parameter for dynamically configuring the parallelism is adjusted based on the result of determining whether the degree of deviation between the execution of the processing task and the preset degree of deviation and / or the resource elasticity value fluctuates;

[0081] When the degree of deviation in the execution of the processing task is greater than the preset degree of deviation or the resource elasticity value fluctuates, it is determined to adjust the preset data dependency tendency value and the preset complexity;

[0082] When the degree of deviation in the execution of the processing task is less than or equal to the preset degree of deviation and the resource elasticity value does not fluctuate, it is determined that there is no need to adjust the preset data dependence tendency value and the preset complexity.

[0083] Specifically, in step S6, when determining to adjust the preset data dependence tendency value and the preset complexity, it is determined to adjust the preset data dependence tendency value with a third adjustment coefficient and to adjust the preset complexity with a fourth adjustment coefficient.

[0084] In the embodiment of the present invention, the judgment parameters for dynamically configuring the parallelism include a preset data dependency tendency value and the preset complexity. The preset deviation degree can be determined by the execution of similar data processing tasks, and the actual values ​​of the processing time deviation, data processing amount deviation and processing result deviation are statistically calculated. The average value, median, maximum value and minimum value of these deviation values ​​are calculated. According to the tolerance of the business to the deviation, the appropriate statistics are selected as a reference for the preset deviation degree. For example, through the analysis of the past 100 similar tasks, it is found that the average value of the processing time deviation is 30 seconds, and the processing time deviation of 95% of the tasks is within 60 seconds. In order to ensure that most tasks can meet the requirements, the preset value of the processing time deviation can be set to 60 seconds. The degree of deviation of the execution of the processing task is determined based on the processing time deviation, data processing amount deviation and processing result deviation. Calculated based on the weighted average of the processing volume deviation and the processing result deviation, determining whether the resource elasticity value has fluctuated includes monitoring the changes in system resource utilization (such as CPU utilization and memory utilization) during task execution, calculating the difference in resource utilization in adjacent time intervals, and if the difference is greater than the preset difference (analyzing the fluctuations in system resource utilization under stable execution of similar tasks in the past, calculating statistics such as the average fluctuation value and the standard deviation of the fluctuation, and adding a certain multiple of the standard deviation to the average fluctuation value as the preset difference to ensure that the resource elasticity value is not mistakenly judged to have fluctuated within the normal fluctuation range. For example, if the average fluctuation value of CPU utilization in historical data is 5% and the standard deviation is 2%, the preset difference can be set to 5% + 2 × 2% = 9%), then the resource elasticity value is considered to have fluctuated.

[0085] The third adjustment coefficient and the fourth adjustment coefficient described in the embodiment of the present invention can be obtained by collecting the relationship data between the degree of deviation of the processing task execution, the resource elasticity value and the preset data dependence tendency value, and the preset complexity adjustment in a large number of historical data processing tasks, and analyzing the execution effect of the data processing task after adjusting the preset data dependence tendency value and the preset complexity under different deviation degrees and resource elasticity value fluctuations, such as the optimization of task completion time and resource utilization, etc. For example, when the degree of deviation of the processing task execution is large and the resource elasticity value fluctuates, the reduction in task completion time under different values ​​of the third adjustment coefficient and the fourth adjustment coefficient is statistically analyzed. Or the improvement degree of resource utilization, find the coefficient value range that optimizes the task execution effect, the value range of the third adjustment coefficient is set to 0.82-0.96, the value of the third adjustment coefficient is preferably 0.88, the value range of the fourth adjustment coefficient is set to 0.83-0.97, the value of the fourth adjustment coefficient is preferably 0.89, the adjustment amount of the preset data dependence tendency value is negatively correlated with the degree of deviation in processing task execution, and the adjustment amount of the preset complexity is negatively correlated with the degree of deviation in processing task execution, but the above values ​​are not limited to this, and those skilled in the art can also adjust the value according to actual needs.

[0086] In actual data processing scenarios, the present invention dynamically changes the execution status of tasks and the status of system resources. By monitoring the degree of deviation in task execution and fluctuations in resource elasticity, abnormalities and unstable resource states during task execution can be promptly detected. When deviations or fluctuations occur, the preset data dependency tendency value and preset complexity are adjusted, allowing the system to quickly adapt to these changes and ensure the smooth progress of data processing tasks. Different business scenarios have different data processing requirements and resource usage. This solution can flexibly adjust preset parameters based on the deviation in task execution and resource fluctuations in specific business scenarios, allowing the system to maintain good performance in various business scenarios. Fluctuations in resource elasticity reflect the usage of system resources. When resource elasticity fluctuates, it indicates that the allocation of system resources may be unreasonable. By adjusting the preset data dependency tendency value and preset complexity, the parallelism and resource requirements of tasks can be re-evaluated to avoid waste caused by excessive allocation of resources or slow task execution caused by insufficient resources. The above method improves the accuracy of data processing requirement analysis for different data sources, thereby improving the accuracy of parallelism configuration when executing tasks and improving data processing efficiency.

[0087] Thus far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it will be readily understood by those skilled in the art that the scope of protection of the present invention is obviously not limited to these specific embodiments. Without departing from the principles of the present invention, those skilled in the art may make equivalent changes or substitutions to the relevant technical features, and the technical solutions after such changes or substitutions will fall within the scope of protection of the present invention.

Claims

1. An efficient multi-source data output method based on FlinkSql, characterized in that: include: Use the CREATE TABLE statement to create multiple different connector tables, each corresponding to a data source or target, and determine the data format, storage location, and connection information; Use SELECT statements to write business logic and perform query, transformation, and aggregation operations on input data; Based on different connector tables, write several INSERT INTO statements to insert the data processed by the SELECT statement into the corresponding target table; Determining dynamically configuring parallelism for the plurality of INSERT INTO statements based on data dependency tendency values ​​of the plurality of INSERT INTO statements or complexity of job topology structures of the plurality of INSERT INTO statements; When dynamically configuring parallelism for the plurality of INSERT INTO statements, determining an initial parallelism based on the data hot / cold distribution type and / or data association complexity of the plurality of INSERT INTO statements, and dynamically increasing or decreasing the initial parallelism based on the data freshness tolerance and / or real-time data latency of the plurality of INSERT INTO statements; Monitor the progress of each INSERT INTO statement in real time and adjust the dynamic parallelism parameters based on the deviation between task execution and the preset deviation or fluctuation in resource elasticity. The data dependency tendency values ​​of the plurality of INSERT INTO statements are determined according to a ratio of the number of INSERT INTO statements having a strong dependency relationship among the plurality of INSERT INTO statements to the total number of the plurality of INSERT INTO statements; The data freshness tolerance is determined according to the data update frequency.

2. The efficient multi-source data output method based on FlinkSql according to claim 1 is characterized in that: Determining the dynamically configured degree of parallelism for the plurality of INSERT INTO statements includes: The data dependency tendency values ​​of some of the INSERT INTO statements are greater than a preset data dependency tendency value, or the complexity of the job topology structures of some of the INSERT INTO statements is greater than a preset complexity, and it is determined to dynamically configure parallelism for some of the INSERT INTO statements.

3. The efficient multi-source data output method based on FlinkSql according to claim 2 is characterized in that: The complexity of the job topology structure of the INSERT INTO statements is determined according to the number of parallel branches and the proportion of indirect connections in the job topology structure.

4. The efficient multi-source data output method based on FlinkSql according to claim 3 is characterized in that: Determining the initial degree of parallelism involves: If the data hot / cold distribution type of several INSERT INTO statements is hot distribution or the data association complexity is greater than the preset data association complexity, the initial parallelism is determined to be the first parallelism; If the data hot / cold distribution type of several INSERT INTO statements is cold distribution and the data association complexity is less than or equal to the preset data association complexity, the initial parallelism is determined to be the second parallelism.

5. The efficient multi-source data output method based on FlinkSql according to claim 4 is characterized in that: Determine the hot and cold data distribution types of several INSERT INTO statements, including: Record the timestamp of the data processed by each INSERT INTO statement; For data that has been accessed, collect access frequency information; Calculate the access frequency of each data item and compare it with the set access frequency threshold to determine the number of hot and cold data items. Count the number of hot and cold data items in the data processed by each INSERT INTO statement. Determine the hot / cold distribution type of a single INSERT INTO statement based on the ratio of hot / cold data in the data processed by the single INSERT INTO statement. Count the hot and cold distribution types of all INSERT INTO statements and then determine the hot and cold distribution types of data in several INSERT INTO statements.

6. The efficient multi-source data output method based on FlinkSql according to claim 5, characterized in that: Determining whether to dynamically increase or decrease the initial parallelism includes: If the data freshness tolerance of several INSERT INTO statements is less than the preset data freshness tolerance or the real-time data delay is greater than the preset real-time data delay, the initial parallelism is dynamically increased. If the data freshness tolerance of several INSERT INTO statements is greater than or equal to the preset data freshness tolerance and the real-time data delay is less than or equal to the preset real-time data delay, it is determined that the initial parallelism is dynamically reduced.

7. The efficient multi-source data output method based on FlinkSql according to claim 6, characterized in that: The preset data freshness tolerance is determined according to an average value of data freshness tolerance of a plurality of groups of data of the same type under the same conditions.

8. The efficient multi-source data output method based on FlinkSql according to claim 7, characterized in that: The real-time data delay is the difference between the data insertion completion timestamp and the data generation timestamp. The preset real-time data delay is determined based on the historical average real-time data delay of several groups of identical data processing tasks.

9. The efficient multi-source data output method based on FlinkSql according to claim 8, characterized in that: Determining the parameters for dynamically configuring parallelism to adjust includes: If the degree of deviation in the execution of the processing task is greater than the preset degree of deviation or the resource elasticity value fluctuates, it is determined to adjust the preset data dependency tendency value and the preset complexity.

10. The efficient multi-source data output method based on FlinkSql according to claim 9, characterized in that: The adjustment amount of the preset data dependence tendency value is negatively correlated with the degree of deviation in the execution of the processing task, and the adjustment amount of the preset complexity is negatively correlated with the degree of deviation in the execution of the processing task.

Images