A method for generating Flink SQL based on data sources

Abstract

The present invention relates to the technical field of data governance, and provides a method for generating Flink SQL based on a data source. The method of the present invention includes: constructing a mapping table of data source field types, and writing the mapping relationship between the data source fields and Flink fields into the constructed mapping table of data source field types; using a data source connector to read the field information of the data source from the constructed mapping table of data source field types, and converting the field information of the data source into Flink field information according to the mapping relationship between the data source fields and Flink fields; supplementing performance parameters for the data source according to the Flink connector type, and setting default values for the supplemented performance parameters; generating Flink SQL code corresponding to the data source according to the Flink SQL syntax, the Flink connector type, the Flink field information, and the supplemented performance parameters. The present invention can reduce the difficulty for data governance personnel to write Flink SQL, improve the accuracy of the data source and the accuracy of the Flink SQL script, reduce the time for data governance personnel to debug the script, and improve the data governance efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of data governance, and in particular to a method for generating Flink SQL based on a data source. Background Art

[0002] As the most popular computing framework for real-time computing, Apache Flink has currently attracted the attention of more and more governance personnel and has been applied more and more. In the actual development process of Flink SQL scripts, governance personnel usually refer to the Flink official website and complete the development of the scripts according to the Flink SQL syntax provided by the Flink official website combined with the provided connector attributes. However, the Flink official website currently only gives the mapping relationship between the field types of Mysql and PostgreSQL and Flink types, and there is no type conversion mapping for other data sources provided, resulting in limited support for data source types and the following deficiencies in data governance: 1. When facing a new data source, data governance personnel need to explore the conversion relationship between the data source type and the Flink type, consuming a large amount of work resources and having a very high trial-and-error cost; 2. When defining data using the data sources provided by the Flink official website, due to the large differences in data sources, the accuracy cannot be guaranteed, resulting in uncontrollable deviations in the calculation results; 3. Due to the lack of targeted optimization of the data source, data blocking is likely to occur in the big data traffic scenario.

[0003] Therefore, how to provide a more efficient, accurate and smooth Flink SQL development method has become an urgent technical problem to be solved. Summary of the Invention

[0004] In view of this, in order to overcome the deficiencies of the prior art, the present invention aims to provide a method for generating Flink SQL based on a data source.

[0005] The present invention provides a method for generating Flink SQL based on a data source, including:

[0006] Step S1: Construct a data source field type mapping table, and write the mapping relationship between the data source field and the Flink field into the constructed data source field type mapping table;

[0007] Step S2: Use a data source connector to read the field information of the data source from the constructed data source field type mapping table, and convert the field information of the data source into Flink field information according to the mapping relationship between the data source field and the Flink field;

[0008] Step S3: Supplement performance parameters for the data source according to the Flink connector type, and set default values for the supplemented performance parameters;

[0009] Step S4: Generate FlinkSQL code corresponding to the data source according to the FlinkSQL syntax, data source connector type, Flink field information, and supplemented performance parameters.

[0010] As a preference of the present invention, for the method of generating FlinkSQL based on the data source, in step S1, constructing the data source field type mapping table includes: taking the field name as the first column of the data source resource type mapping table, taking the field type corresponding to the field name as the second column of the data source field type mapping table, and taking the description corresponding to the field name as the third column of the data source field type mapping table.

[0011] As a preference of the present invention, for the method of generating FlinkSQL based on the data source, in step S1, the field names in the data source field type mapping table include primary key ID, data source type DATABASE_TYPE, data source field type DATA_TYPE, data source field length DATA_PRECISION, data source field precision DATA_SCALE, Flink field type FLINK_DATA_TYPE, Flink field length FLINK_DATA_PRECISION, and Flink field precision FLINK_DATA_SCALE.

[0012] As a preference of the present invention, for the method of generating FlinkSQL based on the data source, in step S2, use the data source connector to read the field information of the data source from the constructed data source field type mapping table, including: generating a data source connector for the data source according to the source database. When the source database of the data source is Mysql or Oracle, generate a JDBC connector as the data source connector; when the source database of the data source is ElasticSearch, generate a Rest connector as the data source connector; when the source database of the data source is Kafka or Hbase, generate a command-line connector as the data source connector.

[0013] As a preference of the present invention, for the method of generating FlinkSQL based on the data source, in step S2, the field information of the data source includes data source field type DATA_TYPE, data source field length DATA_PRECISION, and data source field precision DATA_SCALE.

[0014] As a preference of the present invention, the method for generating FlinkSQL based on a data source of the present invention, step S3 includes: when the Flink connector is a JDBC connector, supplementing performance parameters for the corresponding data source and setting default values for the performance parameters, where the performance parameters include the maximum interval time for data writing, the number of buffered data entries for data writing, the maximum number of retries after writing records to the database fails, the maximum retry time for querying the database fails, the maximum survival time of each row record in the lookup cache, the maximum number of rows in the lookup cache, and the maximum retry timeout.

[0015] As a preference of the present invention, the method for generating FlinkSQL based on a data source of the present invention, step S3 further includes: when the Flink connector is an ElasticSearch connector, supplementing performance parameters for the corresponding data source and setting default values for the performance parameters, where the performance parameters include the maximum data volume in the data writing buffer, the maximum interval time for data writing, and the maximum retry timeout.

[0016] The present invention provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed, the above method for generating FlinkSQL based on a data source is executed.

[0017] Finally, the present invention also provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, the above method for generating FlinkSQL based on a data source is implemented.

[0018] The method for generating FlinkSQL based on a data source of the present invention has the following beneficial effects:

[0019] 1. By writing the mapping relationship between the data source fields and Flink fields into the constructed data source field type mapping table, data governance personnel can generate the corresponding data source connector after selecting the data source, reducing the difficulty for data governance personnel to write FlinkSQL and enabling those who are not familiar with the data source to use it quickly.

[0020] 2. Through the built-in data source type mapping, it is automatically mapped to the Flink data type and precision when reading the data source, ensuring precision consistency and avoiding data precision loss.

[0021] 3. By supplementing performance parameters for the data source according to the Flink connector type and setting default values for the supplemented performance parameters, performance tuning of the data source is achieved, ensuring that data calculation is not delayed under high-throughput data volumes.

[0022] 4. By means of a mode that combines system generation and manual correction, according to the FlinkSQL syntax, data source connector type, Flink field information, and supplementary performance parameters, generate FlinkSQL code corresponding to the data source, improve the accuracy of FlinkSQL scripts, reduce the time for data governance personnel to debug scripts, and enhance data governance efficiency. Description of the Drawings

[0023] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the embodiments will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other accompanying drawings can be obtained based on these drawings.

[0024] Figure 1 It is a flowchart of a method for generating FlinkSQL based on a data source according to an exemplary first embodiment of the present invention.

[0025] Figure 2 It is a schematic structural diagram of the device provided by the present invention. Detailed Embodiments

[0026] The embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0027] It should be noted that, without conflict, the following embodiments and the features in the embodiments can be combined with each other; and, based on the embodiments in the present disclosure, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present disclosure.

[0028] It should be noted that the following describes various aspects of the embodiments within the scope of the appended claims. It should be apparent that the aspects described herein can be embodied in a wide variety of forms, and any specific structure and / or function described herein is illustrative only. Based on the present disclosure, those skilled in the art should understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects described herein can be used to implement a device and / or practice a method. In addition, this device and / or this method can be implemented using other structures and / or functions in addition to one or more of the aspects described herein.

[0029] The following are the noun explanations in each of the following embodiments:

[0030] Flink: Apache Flink is an open-source stream processing framework developed by the Apache Software Foundation. Its core is a distributed stream dataflow engine written in Java and Scala. Flink executes any stream data program in a data-parallel and pipelined manner. Flink's pipelined runtime system can execute batch processing and stream processing programs. In addition, Flink's runtime itself also supports the execution of iterative algorithms.

[0031] FlinkSQL: A high-level API based on SQL statements provided by Apache Flink for reading data from data sources, performing transformations and queries, and then writing the results to target data sources. It is a serverless, scalable, fault-tolerant, distributed data processing engine that supports real-time analysis from structured data and stream data to machine learning and graph computing.

[0032] Mysql: A relational database management system developed by MySQL AB in Sweden and now owned by Oracle. MySQL is one of the most popular relational database management systems. In the context of web applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

[0033] Oracle: Short for Oracle Database, it is a relational database management system of Oracle Corporation. The system has good portability, is easy to use, and has strong functions, suitable for various large, medium, small, and microcomputer environments. It is a database solution with high efficiency, good reliability, and suitable for high throughput.

[0034] JDBC Connector: Java Database Connectivity Connector, a program that enables different databases to be accessed by a Java application server running on Sun Microsystems' Java 2 Platform, Enterprise Edition (J2EE). The JDBC Connector links an application server and a JDBC driver. The connector allows the driver vendor to package the driver so that it is plug-and-play for J2EE applications, and at the same time enables the application server vendor to use third-party JDBC drivers in their products.

[0035] ElasticSearch: A distributed search and analysis engine at the core of the Elastic Stack.

[0036] Rest Connector: A client programming client that provides efficient, up-to-date, and feature-rich support for the HTTP protocol, offering a simple encapsulation for HTTP requests and return values. It is used to construct HTTP requests and quickly obtain request results.

[0037] Kafka: An open-source stream processing platform developed by the Apache Software Foundation, written in Scala and Java. Kafka is a high-throughput distributed publish-subscribe messaging system that can handle all action stream data of consumers in a website.

[0038] Hbase: A highly reliable, high-performance, column-oriented, scalable distributed storage system. Using HBase technology, a large-scale structured storage cluster can be built on inexpensive PC Servers.

[0039] Command-line Connector: A shell-based tool client that can execute shell commands, capture, and return command execution results.

[0040] Flink Connector: It is a connector for Flink tasks to link various types of databases, extract, calculate, and store data for various types of components. It is a bridge for Flink to communicate with external systems.

[0041] ElasticSearch Connector: A type of Flink Connector that provides the ability to write to ElasticSearch components, supporting both stream writing and batch writing capabilities.

[0042] Figure 1 It is a flowchart of a method for generating FlinkSQL based on a data source according to an exemplary first embodiment of the present invention. As Figure 1 shown, the method of this embodiment is implemented as follows:

[0043] Step S1: Construct a data source field type mapping table and write the mapping relationship between the data source fields and Flink fields into the constructed data source field type mapping table;

[0044] Step S2: Use the data source connector to read the field information of the data source from the constructed data source field type mapping table, and convert the field information of the data source into Flink field information according to the mapping relationship between the data source fields and Flink fields;

[0045] Step S3: Supplement performance parameters for the data source according to the Flink connector type and set default values for the supplemented performance parameters;

[0046] Step S4: Generate FlinkSQL code corresponding to the data source according to the FlinkSQL syntax, data source connector type, Flink field information, and supplemented performance parameters.

[0047] In the method of this embodiment, by writing the mapping relationship between the data source fields and Flink fields into the constructed data source field type mapping table, data governance personnel can generate the corresponding data source connector after selecting the data source, which reduces the difficulty of data governance personnel in writing FlinkSQL and enables those who are not familiar with the data source to use it quickly. Through the built-in data source type mapping, it is automatically mapped to the Flink data type and precision when reading the data source, ensuring precision consistency and avoiding data precision loss.

[0048] An exemplary second embodiment of the present invention provides a method for generating FlinkSQL based on a data source. This embodiment is Figure 1 a preferred embodiment of the method shown. In step S1 of the method of this embodiment, constructing the data source field type mapping table includes: using the field name as the first column of the data source resource type mapping table, using the field type corresponding to the field name as the second column of the data source field type mapping table, and using the description corresponding to the field name as the third column of the data source field type mapping table. Specifically, in practical applications, an example of the data source field type mapping table constructed by the method of this embodiment is shown in Table 1. The field names include primary key ID, data source type DATABASE_TYPE, data source field type DATA_TYPE, data source field length DATA_PRECISION, data source field precision DATA_SCALE, Flink field type FLINK_DATA_TYPE, Flink field length FLINK_DATA_PRECISION, and Flink field precision FLINK_DATA_SCALE.

[0049] Table 1

[0050] Field Name Field Type Description ID char(32) Primary Key DATABASE_TYPE varchar(255) Data Source Type DATA_TYPE varchar(255) Data Source Field Type DATA_PRECISION varchar(255) Data Source Field Length DATA_SCALE varchar(255) Data Source Field Precision FLINK_DATA_TYPE varchar(255) Flink Field Type FLINK_DATA_PRECISION varchar(255) Flink Field Length FLINK_DATA_SCALE varchar(255) Flink Field Precision

[0051] An exemplary third embodiment of the present invention provides a method for generating FlinkSQL based on a data source. This embodiment is Figure 1 a preferred embodiment of the method shown.

[0052] In step S2 of the method of this embodiment, reading the field information of the data source from the constructed data source field type mapping table by using the data source connector is implemented as follows:

[0053] Generate a data source connector for the data source according to the source database. When the source database of the data source is Mysql or Oracle, generate a JDBC connector as the data source connector; when the source database of the data source is ElasticSearch, generate a Rest connector as the data source connector; when the source database of the data source is Kafka or Hbase, generate a command-line connector as the data source connector. The field information of the data source in the method of this embodiment includes the data source field type DATA_TYPE, the data source field length DATA_PRECISION, and the data source field precision DATA_SCALE.

[0054] An exemplary fourth embodiment of the present invention provides a method for generating FlinkSQL based on a data source. This embodiment is Figure 1 a preferred embodiment of the method shown. Step S3 of the method of this embodiment is implemented as follows:

[0055] When the Flink connector is a JDBC connector, supplement performance parameters for the corresponding data source and set default values for the performance parameters. The performance parameters include the maximum interval time for data writing, the number of data writing buffers, the maximum number of retries after writing records to the database fails, the maximum retry time for querying the database fails, the maximum survival time of each row record in the lookup cache, the maximum number of rows in the lookup cache, and the maximum retry timeout time.

[0056] When the Flink connector is an ElasticSearch connector, supplement performance parameters for the corresponding data source and set default values for the performance parameters. The performance parameters include the maximum data volume in the data writing buffer, the maximum interval time for data writing, and the maximum retry timeout time.

[0057] Specifically, in practical applications, the performance parameters supplemented and the default values set in step S3 of the method of this embodiment are shown in Table 2.

[0058] Table 2

[0059]

[0060] The method of this embodiment supplements performance parameters for the data source according to the Flink connector type and sets default values for the supplemented performance parameters, realizes the performance tuning of the data source, and ensures that data calculation is not delayed under a high throughput data volume. Through a combination of system generation and manual correction, according to the FlinkSQL syntax, data source connector type, Flink field information, and supplemented performance parameters, generate FlinkSQL code corresponding to the data source, improve the accuracy of the FlinkSQL script, reduce the time for data governance personnel to debug the script, and improve the data governance efficiency.

[0061] The present invention provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed, it implements the method for generating FlinkSQL based on data sources according to the present invention.

[0062] As Figure 2 shown, the present invention also provides a device, including a processor 110, a communication interface 120, a memory 130 for storing computer programs executable by the processor, and a communication bus 140. Among them, the processor 110, the communication interface 120, and the memory 130 complete mutual communication through the communication bus 140. The processor 110 realizes the above-mentioned method for generating FlinkSQL based on data sources by running the executable computer program.

[0063] Among them, the computer program in the memory 130 can be implemented in the form of a software functional unit and sold or used as an independent product. When it can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods in various embodiments of the present application. And the aforementioned storage medium includes: USB flash drives, mobile hard disks, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), magnetic disks, or optical discs, etc., which can store program codes.

[0064] The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place, or may be distributed to multiple network units. Some or all of the modules can be selected based on actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art can understand and implement it without creative labor.

[0065] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on such an understanding, the essence of the above technical solution, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of each embodiment or some parts of the embodiments.

[0066] As described above, the above are only specific embodiments of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by those skilled in the art within the technical scope disclosed by the present invention should be covered by 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 generating FlinkSQL based on data sources, characterized in that, The method includes: Step S1: Construct a data source field type mapping table, and write the mapping relationship between the data source field and the Flink field into the constructed data source field type mapping table; Step S2: Use the data source connector to read the field information of the data source from the constructed data source field type mapping table, and convert the field information of the data source into Flink field information according to the mapping relationship between the data source field and the Flink field; Step S3: Supplement performance parameters for the data source according to the Flink connector type, and set default values for the supplemented performance parameters; Step S4: Generate FlinkSQL code corresponding to the data source according to the FlinkSQL syntax, the data source connector type, the Flink field information, and the supplemented performance parameters.

2. The method for generating FlinkSQL based on a data source according to claim 1, wherein In step S1, constructing the data source field type mapping table includes: taking the field name as the first column of the data source resource type mapping table, taking the field type corresponding to the field name as the second column of the data source field type mapping table, and taking the description corresponding to the field name as the third column of the data source field type mapping table.

3. The method for generating FlinkSQL based on a data source according to claim 2, wherein The field names in the data source field type mapping table in step S1 include primary key ID, data source type DATABASE_TYPE, data source field type DATA_TYPE, data source field length DATA_PRECISION, data source field precision DATA_SCALE, Flink field type FLINK_DATA_TYPE, Flink field length FLINK_DATA_PRECISION, and Flink field precision FLINK_DATA_SCALE.

4. The method for generating FlinkSQL based on a data source according to claim 1, wherein In step S2, using the data source connector to read the field information of the data source from the constructed data source field type mapping table includes: generating a data source connector for the data source according to the source database. When the source database of the data source is Mysql or Oracle, generate a JDBC connector as the data source connector; when the source database of the data source is ElasticSearch, generate a Rest connector as the data source connector; when the source database of the data source is Kafka or Hbase, generate a command-line connector as the data source connector.

5. The method for generating FlinkSQL based on a data source according to claim 1, wherein In step S2, the field information of the data source includes data source field type DATA_TYPE, data source field length DATA_PRECISION, and data source field precision DATA_SCALE.

6. The method for generating FlinkSQL based on a data source according to claim 1, wherein Step S3 includes: when the Flink connector is a JDBC connector, supplement performance parameters for the corresponding data source, and set default values for the performance parameters. The performance parameters include the maximum interval time for data writing, the number of data writing buffers, the maximum number of retries after writing records to the database fails, the maximum retry time for querying the database fails, the maximum survival time of each row record in the lookup cache, the maximum number of rows in the lookup cache, and the maximum retry timeout.

7. The method for generating Flink SQL based on a data source according to claim 1, wherein Step S3 further includes: when the Flink connector is an ElasticSearch connector, supplementing performance parameters for the corresponding data source and setting default values for the performance parameters, where the performance parameters include the maximum amount of data in the data write buffer, the maximum data write interval time, and the maximum retry timeout time.

8. A computer-readable storage medium, characterized in that, A computer program is stored on the computer-readable storage medium, and when the computer program is executed, the method according to any one of claims 1-7 is executed.

9. A computer device, characterized in that, The computer device includes a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, the steps of the method according to any one of claims 1-7 are implemented.

Images