FlinkSQL Dimension Table Join Implementation Method, Device, Medium

Abstract

The present invention relates to a method, device, and medium for implementing a dimension table join in FlinkSQL, including the following steps: listening for whether there is new data in the middleware, and if so, obtaining the SQL source table from the middleware; generating enterprise service bus (Esb) interface information according to the SQL source table; querying and obtaining dimension table data according to the Esb interface information of the enterprise service bus; and performing a preset operation process on the dimension table data, obtaining a processing result and storing it at a preset position to implement the join of the dimension table. Compared with the prior art, the present invention improves the timeliness of data query, the accuracy of the result, the stability of real-time calculation, and reduces unnecessary computing and storage resources.

Description

Technical Field

[0001] The present invention relates to the field of big data real-time computing, and particularly to a method, device, and medium for implementing FlinkSQL dimension table join. Background Art

[0002] A dimension table is a concept in a data warehouse. The dimension attributes in the dimension table are the perspectives for observing data. When building an offline data warehouse, the dimension table is usually associated with the fact table to construct a star model.

[0003] The existing FlinkSQL dimension table Join implementation solution based on offline query of Hbase is the commonly used method at present. The implementation of this solution is mainly divided into two steps:

[0004] Step 1: Offline data batch writing to Hbase. Regular scheduling is used to detect whether the t-1 dimension table data used by the service is ready. If it is detected that the dt on which the dimension table data depends has arrived, Spark batch processing is used to update and write the data into the Hbase table; if it is detected that the dt on which the dimension table data depends has not arrived, continue to detect whether the batch processing conditions are met until the batch processing calculation task scheduling is completed.

[0005] Step 2: Asynchronous association Hbase table query. In real-time computing, each piece of data requires a real-time association dimension table Join operation. A completely asynchronous, non-blocking, thread-safe, and high-performance Hbase client is created to query the Hbase table data in real time.

[0006] Chinese Patent Application No. CN202010477617.8 discloses a method, device, and electronic device for updating dimension table data. This method is applied to a stream computing system and includes: determining a target synchronization log for updating a dimension table to be updated based on the synchronization log of the target original dimension table; for each log entry in the target synchronization log, determining the position change amount of the storage positions of the dimension table data corresponding to this log entry before and after the update; for each log entry in the target synchronization log, reading the dimension table data from the storage area indicated by the position change amount corresponding to this log entry; for each log entry in the target synchronization log, updating the dimension table data entry corresponding to this log entry in the dimension table to be updated according to the dimension table data to be utilized corresponding to this log entry. However, this method does not solve the problem that the dimension table data cannot be ready immediately, resulting in poor accuracy.

[0007] In summary, the FlinkSQL dimension table Join implementation solution based on offline query of Hbase has the following deficiencies:

[0008] (1) The data cannot be prepared in time. In real-time computing, the t-1 dimension table data cannot be ready immediately at zero o'clock, and the output time of the source table dt involved in the dimension table data dependency is not fixed.

[0009] (2) The accuracy of the calculation results is not high. The timeliness of the data output of the business dimension table is low, which affects the results after the streaming task is joined by association. The reason may be that historical data that has not been updated is queried, or the data has not been written into Hbase yet.

[0010] (3) The stability of real-time calculation is poor. Faults in the Hbase data table or cluster faults, etc., will affect the output of real-time data and the use by users on the business side.

[0011] (4) The utilization rate of computing resources is low. The business dimension table data is stored by multiple business systems, occupying a large amount of storage space and the bandwidth resources occupied by IO network transmission of data. Summary of the Invention

[0012] The purpose of the present invention is to overcome the defects existing in the above-mentioned prior art and provide a method, device, and medium for realizing FlinkSQL dimension table join, which can improve the timeliness of dimension table data query.

[0013] The purpose of the present invention can be achieved through the following technical solutions:

[0014] According to one aspect of the present invention, a method for realizing FlinkSQL dimension table join is provided, including the following steps:

[0015] Monitor whether there is new data in the middleware. If so, obtain the SQL source table from the middleware;

[0016] Generate enterprise service bus Esb interface information according to the SQL source table;

[0017] Query and obtain dimension table data according to the enterprise service bus Esb interface information;

[0018] For the dimension table data, through preset arithmetic processing, obtain the processing result and store it in a preset location to realize the join of the dimension table.

[0019] As a preferred technical solution, the middleware is KafkaTopic.

[0020] As a preferred technical solution, the generation of the enterprise service bus Esb interface information includes the following steps:

[0021] By identifying the information description in the SQL source table, extract the key field values of the new data;

[0022] Fill a preset enterprise service bus Esb interface template according to the key field values to obtain the enterprise service bus Esb interface information.

[0023] As a preferred technical solution, the querying and obtaining of the dimension table data includes the following steps:

[0024] Send the enterprise service bus Esb interface information to an external system, and obtain a message from the external system;

[0025] Obtain the dimension table data according to the message.

[0026] As a preferred technical solution, the external system is a third-party business system.

[0027] As a preferred technical solution, the message is an XML message.

[0028] As a preferred technical solution, the preset position is a preset external storage.

[0029] As a preferred technical solution, the enterprise service bus Esb interface is an asynchronous I / O interface with a dynamic rate control function.

[0030] According to another aspect of the present invention, there is provided an electronic device, including: one or more processors and a memory, wherein the memory stores one or more programs, and the one or more programs include instructions for executing the above-mentioned FlinkSQL dimension table join implementation method.

[0031] According to another aspect of the present invention, there is provided a computer-readable storage medium, including one or more programs for execution by one or more processors of an electronic device, and the one or more programs include instructions for executing the above-mentioned FlinkSQL dimension table join implementation method.

[0032] Compared with the prior art, the present invention has the following advantages:

[0033] (1) Improve the timeliness of dimension table data query. During real-time calculation, the dimension table data can be directly queried from an external system through the enterprise service bus Esb interface, and the timeliness depends on the self-update time of the business dimension table, with an error at the millisecond level.

[0034] (2) Improve the result accuracy. During real-time calculation, if the updated data in the business dimension table is immediately queried by a streaming task without the delay waiting error at the hour or day level, the accuracy of the calculation result will be greatly improved.

[0035] (3) Improve the stability of real-time calculation. The stability of the query interface can be maintained by each business system party itself, which can reduce the risk of data loss caused by centralized maintenance and will not affect the use of real-time data on the business side.

[0036] (4) Reduce unnecessary computing and storage resources. The dimension table data is maintained separately in each business system, so it does not need to be centrally stored, reducing the waste of storage resources and transmission bandwidth resources.

[0037] (5) Asynchronous IO requests & computing speed regulation can ensure the overall performance of real-time computing, and further reduce the query time of dimension table data, thereby improving the timeliness of dimension table data query.

[0038] (6) Improve adaptability. Since the dimension table data is scattered in each business system, the API specifications provided by them vary greatly. Introduce the standard Enterprise Service Bus Esb specification, and develop a dimension SQL table information that can be abstractly defined from several dimensions such as request input parameters, response output parameters, and interface protocol parameters to adapt to each API scenario and be used for FlinkSQL dimension table association calculation. Description of the Drawings

[0039] Figure 1 It is a schematic flow diagram of the FlinkSQL dimension table join implementation method in Embodiment 1. Detailed Implementation Manner

[0040] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0041] Embodiment 1

[0042] As Figure 1 described, this embodiment provides a FlinkSQL dimension table join implementation method, which mainly includes the following steps:

[0043] Step S1: Initialize the real-time computing task status;

[0044] Steps S2 and S3: The SQL source table fetching Task loops to listen to whether there is new data in the message middleware KafkaTopic. If there is new data, it is loaded into the streaming task memory and abstracted into a SQL source table; if not, continue to loop and listen for new data to arrive;

[0045] Step S4: The SQL dimension table query Task can extract the key field values in the new data of the data source KafkaTopic by identifying the FlinkSQL dimension table Join equivalent condition information description, and use them to fill the Enterprise Service Bus Esb interface template. Then call this interface template to query and return the dimension table data provided by the third-party business system in real time for subsequent logical calculations;

[0046] Steps S5 and S6: After each new piece of data is enriched through dimension table query, complex logical operation processing is performed by the SQL logic processing Task thread.

[0047] Step S7: The SQL result table output Task writes the real-time result calculated and processed in the previous step into the specified external storage.

[0048] Since the dimension table data is scattered in various business systems, the API specifications provided by each of them vary greatly. To reduce the inconsistency, this embodiment introduces the standard Enterprise Service Bus Esb specification. A dimension SQL table information can be abstractly defined from several dimension directions such as request input parameters, response output parameters, and interface protocol parameters to adapt to each interface API scenario and be used for FlinkSQL dimension table association calculation.

[0049] In real-time calculation, each piece of data needs to perform a real-time association dimension table Join operation. To avoid putting pressure on the business dimension table data storage system, this embodiment adopts an asynchronous I / O request method for real-time query and dynamic multi-level speed limit control to ensure the overall performance of real-time calculation.

[0050] Embodiment 2

[0051] This embodiment provides an electronic device, including: one or more processors and a memory. The memory stores one or more programs, and one or more programs include instructions for executing the FlinkSQL dimension table join implementation method as described in Embodiment 1.

[0052] Embodiment 3

[0053] This embodiment provides a computer-readable storage medium, including one or more programs for execution by one or more processors of an electronic device. One or more programs include instructions for executing the FlinkSQL dimension table join implementation method as described in Embodiment 1.

[0054] The above is only the specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art within the technical scope disclosed by the present invention can easily think of various equivalent modifications or substitutions, and these modifications or substitutions should all be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention should be subject to the protection scope of the claims.

Claims

1. A method for implementing a FlinkSQL dimension table join, characterized in that, It includes the following steps: Monitor whether there is new data in the middleware. If so, obtain the SQL source table from the middleware; Generate Enterprise Service Bus (Esb) interface information according to the SQL source table; Query and obtain the dimension table data according to the Enterprise Service Bus (Esb) interface information; For the dimension table data, through preset arithmetic processing, obtain the processing result and store it in a preset location to achieve the join of the dimension table.

2. The method for implementing FlinkSQL dimension table join according to claim 1, wherein The middleware is KafkaTopic.

3. The method for implementing FlinkSQL dimension table join according to claim 1, wherein The generation of the Enterprise Service Bus (Esb) interface information includes the following steps: By identifying the information description in the SQL source table, extract the key field values of the new data; Fill a preset Enterprise Service Bus (Esb) interface template according to the key field values to obtain the Enterprise Service Bus (Esb) interface information.

4. The method for implementing FlinkSQL dimension table join according to claim 1, wherein The querying and obtaining of the dimension table data includes the following steps: Send the Enterprise Service Bus (Esb) interface information to an external system and obtain a message from the external system; Obtain the dimension table data according to the message.

5. The FlinkSQL virtual table join implementation method according to claim 4, wherein The external system is a third-party business system.

6. The method for implementing FlinkSQL dimension table join according to claim 4, wherein The message is an XML message.

7. The method for implementing FlinkSQL dimension table join according to claim 1, wherein The preset location is a preset external memory.

8. The method for implementing FlinkSQL dimension table join according to claim 1, wherein The Enterprise Service Bus (Esb) interface is an asynchronous I / O interface with a dynamic rate control function.

9. An electronic device, characterized in that, It includes: One or more processors and a memory. The memory stores one or more programs, and the one or more programs include instructions for executing the FlinkSQL dimension table join implementation method as described in any one of claims 1-8.

10. A computer-readable storage medium, characterized in that, It includes one or more programs for execution by one or more processors of an electronic device, and the one or more programs include instructions for executing the FlinkSQL dimension table join implementation method as described in any one of claims 1-8.

Images