Visual SQL generation method and system for FLINK real-time data processing

Abstract

The invention relates to the technical field of big data processing and analysis, in particular to a visual SQL generation method and system for FLINK real-time data processing, and the method comprises the following steps: data source management, which is used for managing a data source classification directory; the data type management is used for converting various database management data types; the visual SQL configuration is used for combining and adjusting a data source, a database and a data table in the data source management system and data type information in the data type management system in a visual mode, and finally generating an SQL capable of being executed in an Flink environment; the visual SQL generation method and system for FLINK real-time data processing have the advantages that the technical threshold is lowered, non-technical users can easily define data processing logic, complex Flink codes do not need to be written, and the threshold for using Flink is lowered; the development efficiency is improved, the data processing flow is simplified through a visual operation interface, the development period is shortened, and the development efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing and analysis, and in particular to a visual SQL generation method and system for FLINK real-time data processing. Background Art

[0002] With the rapid development of information technology, big data processing and analysis have become key requirements for many industries. Flink is designed to run on all common cluster environments and can process data in streams as well as batches, which makes it very suitable for real-time data processing and analysis.

[0003] Although Flink provides powerful data processing capabilities, it is still a challenge for non-technical users to write and maintain Flink job code. This has prompted the development of visual data processing tools that allow users to define data processing logic through a graphical interface instead of programming.

[0004] Therefore, it is necessary to provide a visual SQL generation method for Flink real-time data processing. Through this method, users can use a graphical interface to generate SQL for real-time data processing tasks. Summary of the invention

[0005] The object of the present invention is to provide a visual SQL generation method and system for FLINK real-time data processing to solve the problems raised in the above background technology.

[0006] To achieve the above object, the present invention provides the following technical solution: a visual SQL generation method for FLINK real-time data processing, the method comprising the following steps:

[0007] Data source management, used to manage data source classification catalog;

[0008] Data type management, used to convert data types managed by various databases;

[0009] Visual SQL configuration is used to combine and adjust the data source, database, data table in the data source management system and the data type information in the data type management system in a visual way, and finally generate SQL that can be executed in the Flink environment.

[0010] Preferably, data source management includes:

[0011] Data domain management is used to manage the data source classification directory; including: data domain directory information, including data domain directory name, data domain directory code, and data domain directory description;

[0012] Data source management is used to manage data source information, including creation, import, editing, activation, deactivation, and query operations; including: data source name, data source type, data source IP, directory, connection status, and editing time information; supports registration, configuration, testing, monitoring, maintenance, and management of data source permissions and versions.

[0013] Preferably, data type management includes:

[0014] Database type management, responsible for the management of database types, including creation, editing, deletion, and query operations;

[0015] Data type management: matching and managing different database types with Flink-related types.

[0016] Preferably, the visual SQL configuration includes:

[0017] Job catalog management, used to manage the SQL to be configured, including creation and editing functions;

[0018] Process configuration design, including source-side operators, processing operators, and target-side operators;

[0019] Data source query: query related data source information based on the database type selected by the user;

[0020] Data type configuration, the general interface queries the corresponding fields of the data type, and displays and adjusts them separately on the page.

[0021] Preferably, a data source column, an operator column, and a target end column are set in the graphical interface of the process configuration design, and the user can quickly build the basic structure of the Flink SQL statement by dragging and dropping;

[0022] Detailed configuration options are provided for each operator, and users can set them according to their needs, including but not limited to data source connection information, data format, processing logic, and output target;

[0023] According to the user's selection and configuration information, the corresponding Flink SQL statements are automatically generated. The generated SQL statements are directly executed in the Flink environment to achieve real-time data processing.

[0024] A visual SQL generation system for FLINK real-time data processing, the system comprising:

[0025] Data source management system, used to manage data source classification catalog;

[0026] Data type management system, used to convert data types managed by various databases;

[0027] The visual SQL configuration system is used to combine and adjust the data source, database, data table in the data source management system and the data type information in the data type management system in a visual way, and finally generate SQL that can be executed in the Flink environment.

[0028] Preferably, the data source management system includes:

[0029] The data domain management module is used to manage the data source classification directory; including: data domain directory information, including data domain directory name, data domain directory code, and data domain directory description;

[0030] The data source management module is used to manage data source information, including creation, import, editing, activation, deactivation, and query operations, including: data source name, data source type, data source IP, directory, connection status, and editing time information; supports registration, configuration, testing, monitoring, maintenance, and management of data source permissions and versions.

[0031] Preferably, the data type management system includes:

[0032] The database type management module is responsible for the management of database types, including creation, editing, deletion, and query operations;

[0033] The data type management module matches and manages the relevant types of Flink according to the different types of databases.

[0034] Preferably, the visual SQL configuration system includes:

[0035] The job directory management module is used to manage the SQL to be configured, including creation and editing functions;

[0036] Process configuration design module, including source-side operators, processing operators, and target-side operators;

[0037] The data source query module queries the relevant data source information according to the database type selected by the user;

[0038] Data type configuration module, the general interface queries the corresponding fields of the data type, and displays and adjusts them separately on the page.

[0039] Preferably, a data source column, an operator column, and a target end column are set in the graphical interface of the process configuration design module, and the user quickly builds the basic structure of the Flink SQL statement by dragging and dropping;

[0040] The system provides detailed configuration options for each operator, and users can set them according to their needs, including but not limited to data source connection information, data format, processing logic, and output target;

[0041] The system automatically generates corresponding Flink SQL statements based on the user's selection and configuration information. The generated SQL statements can be executed directly in the Flink environment to achieve real-time data processing.

[0042] Compared with the prior art, the present invention has the following beneficial effects:

[0043] The visual SQL generation method and system for FLINK real-time data processing proposed in the present invention reduce the technical threshold, and non-technical users can also easily define data processing logic without writing complex Flink code, which reduces the threshold for using Flink; improve development efficiency, the visual operation interface simplifies the data processing process, shortens the development cycle, and improves development efficiency; easy to understand, the SQL language is easy to understand, and users can more easily understand the data processing logic, reducing communication costs. Flexible expansion, support for multiple data sources and targets, convenient for users to access different data systems, and adapt to diverse business needs. Easy to maintain, the code generation method is convenient for subsequent maintenance and modification, reducing maintenance costs. Real-time data processing, based on the Flink framework, supports real-time data processing to meet the needs of real-time data analysis and application. Data cleaning and deduplication, support data cleaning and deduplication and other functions, improve data quality. Data visualization, the visual interface intuitively displays the data processing process, which is convenient for users to understand the data flow and logic. Permission management, support data source permission management, ensure data security. Easy to deploy, the system is easy to deploy and use, reducing the cost of use. Code reuse, the generated SQL code can be reused, improving development efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Figure 1 is a flow chart of the method of the present invention;

[0045] Figure 2 This is a system architecture diagram of the present invention. DETAILED DESCRIPTION

[0046] In order to make the purpose and technical solution of the present invention clearly and completely described, and the advantages more clearly understood, the embodiments of the present invention are further described in detail with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are part of the embodiments of the present invention, rather than all of the embodiments, and are only used to explain the embodiments of the present invention, and are not used to limit the embodiments of the present invention. All other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0047] For example, see Figure 1 The present invention provides a technical solution: a visual SQL generation method for FLINK real-time data processing, the method comprising the following steps:

[0048] Data source management, used to manage data source classification catalogs; including:

[0049] Data domain management is used to manage the data source classification directory; including: data domain directory information, including data domain directory name, data domain directory code, and data domain directory description;

[0050] Data source management is used to manage data source information, including creation, import, editing, activation, deactivation, and query operations; including: data source name, data source type, data source IP, directory, connection status, and editing time information; supports registration, configuration, testing, monitoring, maintenance, and management of data source permissions and versions.

[0051] Data type management, used to convert various database management data types; including:

[0052] Database type management, responsible for the management of database types, including creation, editing, deletion, and query operations;

[0053] Data type management: matching and managing different database types with Flink-related types.

[0054] Visual SQL configuration is used to combine and adjust the data source, database, data table in the data source management system and the data type information in the data type management system in a visual way, and finally generate SQL that can be executed in the Flink environment; including:

[0055] Job catalog management, used to manage the SQL to be configured, including creation and editing functions;

[0056] The process configuration design includes source-side operators, processing operators, and target-side operators. The data source column, operator column, and target-side column are set in the graphical interface of the process configuration design. Users can quickly build the basic structure of Flink SQL statements by dragging and dropping. Detailed configuration options are provided for each operator. Users can set them according to their needs, including but not limited to data source connection information, data format, processing logic, and output target. According to the user's selection and configuration information, the corresponding Flink SQL statement is automatically generated and executed directly in the Flink environment to achieve real-time data processing.

[0057] Data source query: query related data source information based on the database type selected by the user;

[0058] Data type configuration, the general interface queries the corresponding fields of the data type, and displays and adjusts them separately on the page.

[0059] Embodiment 2, refer to the attached Figure 2As shown, based on the first embodiment, a visual SQL generation system for FLINK real-time data processing is proposed, and the system includes:

[0060] The data source management system is responsible for managing the data source classification directory. The data type management system is responsible for converting data types managed by various databases. The visual SQL configuration system is responsible for combining and adjusting the data source, database, data table in the data source management system and the data type in the data type management system in a visual way, and finally generates SQL that can be executed in the Flink environment.

[0061] 1. Data source management system

[0062] The data source management system includes two modules, the data domain management module and the data source management module. It is mainly responsible for classifying and managing data sources. Data domain management includes data domain directory information, data domain directory name, data domain directory code, and data domain directory description. Data source management includes creating data sources, importing data sources, data source names, data source types, data source IPs, directories, connection status, editing time, etc. It is specifically responsible for managing, maintaining, and monitoring various data sources. This module allows users to register, configure, test, monitor, maintain, and manage the permissions and versions of data sources to ensure the stability of data sources and the accuracy of data.

[0063] Data domain management module: used to manage the data source classification directory. Data domains can classify and manage data assets based on business attributes, organizational structure, data characteristics and other dimensions. By centralizing data assets with common attributes into the same data domain, administrators can efficiently manage data assets and sensitive data in the assets. There are many data assets belonging to different departments, and assets need to be divided by department for efficient management by data administrators. Data assets need to be divided among different data administrators. Data administrators can only manage assets within their authority, thereby achieving management authority control.

[0064] Data source management module: used to manage data source information. You can create, import, edit, enable, disable, query, and other operations on data sources. First, fill in the data source name, select the data source directory, and the data source type displays the default data source LOGO. Then fill in the installed database environment information, including IP address, port, instance name, user name, password, connection pool size, version and other configuration items, description, etc. After filling in, you can test whether it is connected. The filled data source can also be edited, enabled, disabled, queried, etc. Set the connection parameters and collection strategies of the data source to meet different business needs. Test the connection status and query capabilities of the data source to ensure that the data source can be used normally. Monitor the operating status and performance indicators of the data source in real time, and issue timely alarms when abnormal situations occur. Maintain data sources: solve problems that occur during the use of data sources, such as connection timeouts and data loss. You can also use import to manage data sources. The configured data is used in the subsequent visual SQL configuration. Provide a unified interface or platform for centralized management of various types of data sources, such as relational databases, non-relational databases, file storage, etc. Through data source testing and data source monitoring functions, the stability of data sources and data quality can be ensured, and data loss and errors can be reduced. Permission management can be implemented to ensure that different users have different access rights to different data sources, thereby improving data security.

[0065] 2. Data type management system

[0066] The data type management system includes two modules, namely the database type management module and the data type management module.

[0067] Database type management module: responsible for the management of database types, including operations such as creating, editing, deleting, and querying database types. It can manage various types including sqlserver, postgresql, oscar, oracle, mysql, kingbase, greenplum, dameng, hive, etc.

[0068] Data type management module: Matches and manages related types of Flink according to different types of databases. Including varchar type corresponding to string type in Flink, money type corresponding to decimal type in Flink, datetime type corresponding to timestamp type in Flink, etc. It can also provide query API for SQL configuration system to use. In the data type configuration module, fields are key elements that describe data type attributes. Including: data type name is used to identify the unique name of the data type. Data type length for character data types indicates the number of characters that can be stored in this type. Data type precision for numeric data types indicates the number of decimal places that can be stored in this type. Constraints: such as non-null constraints and unique constraints, which are used to limit the value range of data. The data type configuration module ensures that data is represented consistently in the database by defining unified data types, which is easy to manage and maintain.

[0069] 3. Visual SQL configuration system

[0070] The visual SQL configuration system has built-in job directory management module, process configuration design module, data source query module, and data type configuration module.

[0071] Job Directory Management Module: manages the SQL to be configured, including creation and editing functions. When creating a job, enter the name, description, and job category. After completion, use the process configuration module to configure the operator.

[0072] Process configuration design module:

[0073] a) Operator classification:

[0074] According to the characteristics of Flink SQL, operators are divided into source-side operators, processing operators, and target-side operators. Source-side operators are responsible for reading data from the data source, processing operators are responsible for converting and filtering data, and target-side operators are responsible for outputting processed data to the target location.

[0075] Source-side operator: responsible for reading data from data sources, such as MySQL, HIVE, ClickHouse, etc.

[0076] Processing operators: perform various processing operations on data, such as filtering, aggregation, and connection.

[0077] Target operator: responsible for outputting the processed data to the specified storage medium, such as Mysql, Oracle, KingBase, etc.

[0078] b) Visual interface design:

[0079] In the graphical interface, set the data source column, operator column, and target column. Users can drag the source operator from the data source column to the canvas, drag the processing operator from the operator column to the canvas, and drag the target operator from the target column to the canvas. Through dragging and dropping operations, users can quickly build the basic structure of Flink SQL statements.

[0080] Data source bar: displays available data sources, from which users can select and drag them to the canvas as source operators.

[0081] Operator bar: displays all processing operators. Users can select and drag them to the drawing board according to business needs and set the processing order of the operators.

[0082] Target sidebar: Displays available output targets. Users can select and drag them to the canvas as target operators.

[0083] c) Operator configuration:

[0084] For each operator, the system provides detailed configuration options, and users can set them according to their needs.

[0085] Configuration information includes but is not limited to data source connection information, data format, processing logic, output target, etc.

[0086] Source operator configuration:

[0087] The user needs to configure the information of the source operator, such as data source type, data source address, table name, etc., so that the system can correctly read data from the data source.

[0088] Processing operator configuration:

[0089] Users can select multiple processing operators to the drawing board according to business needs, and configure each processing operator. The configuration content includes but is not limited to input fields, output fields, filter conditions, conversion functions, etc. By configuring the processing operator, users can perform operations such as data conversion and filtering. These include: configuring functions to configure CAST, COUNT, LIKE and other functions by column, setting the configuration name, and displaying the configuration details. To configure the WHERE condition, you can select a cross-combination configuration of multiple fields and multiple operators. To configure JOIN, you can configure the name, select the associated table, configure the associated table function and WHERE condition, select the JOIN type, and select the JOIN condition field from multiple data sources.

[0090] Target operator configuration:

[0091] The user needs to configure the information of the target operator, such as output location, output format, etc., so that the system can output the processed data to the target location.

[0092] d) SQL generation:

[0093] After the user completes the dragging and configuration of the operator, the system will automatically generate the corresponding Flink SQL statement based on the user's selection and configuration information.

[0094] The generated SQL statements can be executed directly in the Flink environment to achieve real-time data processing.

[0095] Data source query module: Based on the database type selected by the user, relevant data source information is queried, and then the database information and data table information under the data source are queried, and the relevant table fields and types are displayed. It usually supports multiple query methods, including simple query, complex query, aggregate query, etc. Queries can be constructed and executed through a graphical interface to return a result set that meets the conditions.

[0096] Data type configuration module: The general interface queries the corresponding fields of the data type, and can be displayed and adjusted separately on the page. It can also add or delete fields and change types.

[0097] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A visual SQL generation method for FLINK real-time data processing, characterized by: The method comprises the following steps: Data source management, used to manage data source classification catalog; Data type management, used to convert data types managed by various databases; Visual SQL configuration is used to combine and adjust the data source, database, data table in the data source management system and the data type information in the data type management system in a visual way, and finally generate SQL that can be executed in the Flink environment.

2. A visual SQL generation method for FLINK real-time data processing according to claim 1, characterized in that: Data source management includes: Data domain management is used to manage the data source classification directory; including: data domain directory information, including data domain directory name, data domain directory code, and data domain directory description; Data source management is used to manage data source information, including creation, import, editing, activation, deactivation, and query operations; including: data source name, data source type, data source IP, directory, connection status, and editing time information; supports registration, configuration, testing, monitoring, maintenance, and management of data source permissions and versions.

3. A visual SQL generation method for FLINK real-time data processing according to claim 1, characterized in that: Data type management includes: Database type management, responsible for the management of database types, including creation, editing, deletion, and query operations; Data type management: matching and managing different database types with Flink-related types.

4. A visual SQL generation method for FLINK real-time data processing according to claim 1, characterized in that: Visual SQL configuration includes: Job catalog management, used to manage the SQL to be configured, including creation and editing functions; Process configuration design, including source-side operators, processing operators, and target-side operators; Data source query: query related data source information based on the database type selected by the user; Data type configuration, the general interface queries the corresponding fields of the data type, and displays and adjusts them separately on the page.

5. A visual SQL generation method for FLINK real-time data processing according to claim 4, characterized in that: In the graphical interface of process configuration design, the data source column, operator column, and target column are set. Users can quickly build the basic structure of Flink SQL statements by dragging and dropping. Detailed configuration options are provided for each operator, and users can set them according to their needs, including but not limited to data source connection information, data format, processing logic, and output target; According to the user's selection and configuration information, the corresponding Flink SQL statements are automatically generated. The generated SQL statements are directly executed in the Flink environment to achieve real-time data processing.

6. A visual SQL generation system for FLINK real-time data processing according to the visual SQL generation method for FLINK real-time data processing according to any one of claims 1 to 5, characterized in that: The system comprises: Data source management system, used to manage data source classification catalog; Data type management system, used to convert data types managed by various databases; The visual SQL configuration system is used to combine and adjust the data source, database, data table in the data source management system and the data type information in the data type management system in a visual way, and finally generate SQL that can be executed in the Flink environment.

7. A visual SQL generation system for FLINK real-time data processing according to claim 6, characterized in that: The data source management system includes: The data domain management module is used to manage the data source classification directory; including: data domain directory information, including data domain directory name, data domain directory code, and data domain directory description; The data source management module is used to manage data source information, including creation, import, editing, activation, deactivation, and query operations, including: data source name, data source type, data source IP, directory, connection status, and editing time information; supports registration, configuration, testing, monitoring, maintenance, and management of data source permissions and versions.

8. A visual SQL generation system for FLINK real-time data processing according to claim 6, characterized in that: The data type management system includes: The database type management module is responsible for the management of database types, including creation, editing, deletion, and query operations; The data type management module matches and manages the relevant types of Flink according to the different types of databases.

9. A visual SQL generation system for FLINK real-time data processing according to claim 6, characterized in that: The visual SQL configuration system includes: The job directory management module is used to manage the SQL to be configured, including creation and editing functions; Process configuration design module, including source-side operators, processing operators, and target-side operators; The data source query module queries the relevant data source information according to the database type selected by the user; Data type configuration module, the general interface queries the corresponding fields of the data type, and displays and adjusts them separately on the page.

10. A visual SQL generation system for FLINK real-time data processing according to claim 9, characterized in that: In the graphical interface of the process configuration design module, the data source column, operator column, and target column are set. Users can quickly build the basic structure of Flink SQL statements by dragging and dropping. The system provides detailed configuration options for each operator, and users can set them according to their needs, including but not limited to data source connection information, data format, processing logic, and output target; The system automatically generates corresponding Flink SQL statements based on the user's selection and configuration information. The generated SQL statements can be directly executed in the Flink environment to achieve real-time data processing.

Images