Method and system for realizing FlinkSql statement configuration by customizing ETL (Extract-Transform-Load) process

Abstract

The invention relates to the technical field of data real-time stream processing, in particular to a method and system for achieving FlinkSql statement configuration through a user-defined ETL process, and the method comprises the following steps: operation of a job development page, graphical configuration of the ETL process, column configuration and FlinkSql basic configuration items, functions, where conditions, join and output configuration, and generation, preview and storage of FlinkSql statements. The method and system for realizing FlinkSql statement configuration through the user-defined ETL process have the beneficial effects that the use threshold is reduced through visual operation through the user-defined ETL process nodes; different tables of a plurality of data sources can be associated by using join nodes, so that the data availability is expanded; the data can be cleaned by using the function configuration node, the data standard is standardized, and dirty data is removed.

Description

Technical Field

[0001] The present invention relates to the technical field of real-time data stream processing, and in particular to a method and system for implementing FlinkSql statement configuration using a custom ETL process. Background Art

[0002] The streaming data ETL (Extract Transform Load) function provided by the data transmission service DTS is an efficient real-time data processing tool. ETL supports task configuration through visual drag-and-drop operations and Flink SQL statements, and combined with DTS's efficient streaming data replication capabilities, it can realize streaming data extraction, data conversion and processing, and data loading. While lowering the development threshold and the impact on business systems, it improves efficiency, enriches the enterprise's real-time data processing and computing scenarios, and enables the digital transformation of enterprises.

[0003] However, the existing ETL process does not meet the requirements of configuring multiple related tables and generating corresponding FlinkSql. Directly writing FlinkSql is too difficult for non-professionals and requires special technical training, which takes a long time and is expensive. Summary of the invention

[0004] The purpose of the present invention is to provide a method and system for implementing FlinkSql statement configuration in a custom ETL process to solve the problems raised in the above background technology.

[0005] To achieve the above object, the present invention provides the following technical solution: a method for configuring FlinkSql statements using a custom ETL process, the method comprising the following steps:

[0006] Step 1: Operation of the job development page: Users open the job development page through the graphical interface, create or edit directories and jobs, where jobs include job name, job type, description, and select directory;

[0007] Step 2: Graphically configure the ETL process: In the graphical drawing interface of the job development page, the user drags and connects the required custom nodes, including selecting input, configuring function, configuring where condition, configuring join, and configuring output. Click the node to open the configuration page for property configuration.

[0008] Step 3: Column configuration and FlinkSql basic configuration items: The user configures the data source type, data source name, table name, and column configuration for each node. The column configuration includes the FlinkSQL type corresponding to each column, whether it is a partition field, and the alias, and also configures the FlinkSql basic configuration items.

[0009] Step 4: Function, where condition, join and output configuration: According to actual needs, users select and configure the function in the Configure Function node, enter the where condition in the Configure Where Condition node, select the associated table in the Configure Join node and configure the join type and condition, and select the output table in the Configure Output node and configure the output field;

[0010] Step 5: Generate, preview, and save FlinkSql statements: After the user configuration is completed, you can view the generated FlinkSql statements, format, verify, and save them to ensure the correctness and availability of the FlinkSql statements.

[0011] Preferably, the column configuration in step three further includes:

[0012] Users automatically match or manually adjust the FlinkSQL type based on the data source field type;

[0013] User-configured column aliases and other related information, such as field length and field type.

[0014] Preferably, the function configuration in step 4 further includes:

[0015] The user selects the corresponding function for the selected column and configures the alias;

[0016] The user enters the function details configuration page to perform further function parameter configuration, such as function input parameters and output parameters.

[0017] Preferably, the operation operation and maintenance steps are also included, including:

[0018] Provides a job list page that displays the job name, operation mode, cluster queue name, job ID, status, modification time, and operation column;

[0019] Users can stop, restart, and view Flink Web UI operations on jobs through the Operation column.

[0020] Provides a historical instance page to display the historical execution records of the job;

[0021] Supports users to add new jobs and configure relevant job parameters, such as execution mode, cluster queue, and executable Jar package.

[0022] Preferably, the method further includes a resource management step, including:

[0023] Provides a Jar management list page to display the Jar package name, type, file path, startup class, execution parameters, description, whether it is enabled, and update time information;

[0024] Users manage Jar packages through the Create, Edit and Delete buttons;

[0025] Supports users to query and filter Jar packages based on name, type, and whether they are enabled.

[0026] A custom ETL process to implement FlinkSql statement configuration system, applied to a custom ETL process to implement a FlinkSql statement configuration method, the system includes a job development module, the job development module is used to implement the splicing of the ETL process into FlinkSQL statements, wherein the job development module includes:

[0027] The data collection unit is used to collect the address of the data source, the fields of the table, the length and type of the fields required by the ETL process;

[0028] The process configuration unit is used to configure the corresponding ETL process, including five custom nodes: select input, configure function, configure where condition, configure join, and configure output. Users can build the ETL process by dragging and connecting these nodes;

[0029] The property configuration unit is used to configure the properties of each node, including data source type, data source name, table name, column configuration, and FlinkSql basic configuration items;

[0030] The SQL generation unit is used to generate the corresponding FlinkSql statements according to the ETL process configured by the user, and provides SQL preview, formatting and verification functions.

[0031] Preferably, the process configuration unit also includes a graphic drawing interface, in which the user can intuitively see the structure of the ETL process and open, rename, and delete the job by right-clicking or left-clicking.

[0032] Preferably, the column configuration page in the property configuration unit allows the user to configure the FlinkSQL type corresponding to each column, whether it is a partition field, and provides a default field type automatic matching function. The user configures the corresponding function for the selected column and sets an alias, and enters the function details configuration page for further function parameter configuration.

[0033] Preferably, a resource management module is also included, which is used to package the FlinkSql generated in the job development to generate a jar package, and provide management and upload operations of the jar package; the resource management module includes a Jar management list page, which displays the name, type, file path, startup class, execution parameters, description, whether it is enabled, and update time information of the jar package, and supports query and filtering based on name, type, and whether it is enabled.

[0034] Preferably, it also includes a job operation and maintenance module, which is used to execute the uploaded jar package file to realize data flow and synchronization; the job operation and maintenance module includes a job list page, which displays the job name, operation mode, cluster queue name, job ID, status, modification time and operation column, and supports clicking the job name to enter the instance list page or historical instance page of the job; the job operation and maintenance module also supports opening the Flink management page through the Flink Web UI, and provides a new page for configuring the job name, execution mode, cluster queue, executable Jar package and other configuration items.

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

[0036] The custom ETL process proposed in the present invention implements the FlinkSql statement configuration method and system. By customizing the ETL process nodes, the visual operation reduces the usage threshold; the join node can be used to associate different tables of multiple data sources, thereby expanding data availability; the function configuration node can be used to clean the data, standardize the data standard, and remove dirty data; the where function is used to configure the query conditions and specify the data usage scope; the jar package is used to upload SQL to improve the safety of use; the FlinkSql task can be executed and stopped at any time to improve the operation efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 Develop a page schematic for the present invention operation;

[0038] Figure 2 A schematic diagram of a new directory is added to the present invention;

[0039] Figure 3 A schematic diagram of a newly constructed operation of the present invention;

[0040] Figure 4 It is a schematic diagram of the operation details of the present invention;

[0041] Figure 5 This is a schematic diagram of the ETL process configuration page of the present invention;

[0042] Figure 6 A schematic diagram of selecting input nodes for the present invention;

[0043] Figure 7 Select an input node-column configuration diagram for the present invention;

[0044] Figure 8 Select input node for the present invention -with configuration diagram;

[0045] Fig. 9 A schematic diagram of configuring a function node for the present invention;

[0046] Fig.10A schematic diagram of configuring function nodes-function configuration details of the present invention;

[0047] Fig.11 A schematic diagram of configuring a where condition node for the present invention;

[0048] Fig.12 A schematic diagram of configuring a JOIN node for the present invention;

[0049] Fig.13 Configuring JOIN nodes-output fields schematic diagram for the present invention;

[0050] Fig.14 A schematic diagram of selecting output nodes for the present invention;

[0051] Fig.15 This is a schematic diagram of the SQL preview page of the present invention;

[0052] Fig.16 This is a schematic diagram of the Jar package management page of the present invention;

[0053] Fig.17 This is a schematic diagram of the operation and maintenance page of the present invention;

[0054] Fig.18 The figure is a flow chart of the method of the present invention. DETAILED DESCRIPTION

[0055] 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.

[0056] For example, see Figures 1 to 18 The present invention provides a technical solution: a method for configuring FlinkSql statements using a custom ETL process, the method comprising the following steps:

[0057] 1. Job Development

[0058] The job development module realizes the splicing of ETL processes into FlinkSQL. The premise for executing job development is to have a collection task, which is used to collect the necessary data such as the address of the data source, the fields of the table, the length and type of the field, etc. required by the ETL process. After the data source and table content collection is completed, the corresponding ETL process is configured. There are a total of 5 custom nodes, namely, select input, configure function, configure where condition, configure join, and configure output. The following are the detailed steps of the entire task process:

[0059] (1) Open the job development page, and expand the first-level directory and the jobs under it by default. When creating a new directory, a pop-up window will open and you can create a new directory. Directory name (limited to 64 characters, you can enter Chinese characters, letters, numbers, and common symbols _-()(), etc.). The same applies to renaming. A directory cannot be deleted if it is not empty. (For example Figure 1 )

[0060] (2) When creating a new job, a pop-up window will open. Enter the job name (limited to 64 characters, and can contain Chinese characters, letters, numbers, and common symbols such as _-()()), job type (drop-down box: FlinkSQL, FlinkJar, sql concatenation process), description (limited to 256 characters), and select a directory before creating a new job. The job name cannot be repeated. (For example, Figure 2 )

[0061] (3) Right-click on the job name to open, rename, delete, and other operations. Left-clicking is equivalent to opening. Implement "sql splicing" type jobs. After clicking the job, the graphic drawing interface opens on the right. The job details button is displayed in the upper left corner of the interface, and the save and close buttons are displayed in the upper right corner. Drag and connect the required custom nodes. After clicking the node, the configuration page opens on the right to configure the properties. Click the close button to close the content of the right interface. Click the job details to display the job name, job ID, job type, directory, creation time, creator, update time, updater, and description. (such as Figure 3 ,like Figure 4 )

[0062] (4) After the job is created, enter the ETL process configuration page (such as Figure 5 ). Start with the start node, click +Create to select the input node (such as Figure 6 ). Step 1. Click the Select Data Source Type drop-down box to configure the data source type (mysql, hive, oracle, etc.). Step 2. Select the corresponding data source name in the Data Source drop-down box. Step 3. Select the table in the previous data source. Step 4. Click the Configure Details button in the Column Configuration row to enter the Column Configuration page (such as Figure 7 ). Configure the flinkSQL type corresponding to each column and whether it is a partition field. The corresponding FlinkSql type has a corresponding dictionary table, which is automatically matched according to the field type of different data sources by default. Step 5 Click the Configuration Details button in the WHIT configuration line to enter the configuration page (such as Figure 8 ). Configure the basic configuration items of FlinkSql. Each data source displays different configuration items.

[0063] (5) After configuring the input node, click the + sign below to display four nodes (Configure function, Configure where condition, Configure join, Select output). If you select Configure function node (such as Fig. 9 ), the upper left configuration name defaults to the table name selected in the previous node, and the first row of the table below defaults to the table column name (first column) configured in the previous node. You can select the corresponding function for the column and configure an alias. Click the Configure Column Details button to configure the detailed information of the function (such as Fig.10 ). The + button below the table can be used to add new columns. It supports configuring multiple functions for multiple columns.

[0064] (6) If you choose to configure the where condition node (such as Fig.11 ), the configuration name on the upper left defaults to the configuration name of the previous node or the table name selected by the previous node. In the Where condition input box, you can select the corresponding field and the restriction parameter of the corresponding field according to the drop-down list on the right. The corresponding field comes from the column alias configured in the previous node.

[0065] (7) If you choose to configure a join node (such as Fig.12 ), the upper left configuration name defaults to the table name selected by the previous node. First, click the Select Details button of the Select Association Table row to enter the association table configuration page. The configuration of this page is similar to the input node. After the configuration is completed, the association table alias item defaults to the configured association table name. Whether to configure the association table function is similar to selecting the configuration function node, and whether to configure the association table where condition is similar to configuring the where condition node. You can customize the configuration and the default is no. Select the join type row to configure the join type (left, right, inner). In the Join condition below, you can select the association field between the association table and the previous node. If there are multiple fields associated, you can click the Add Condition button below to add a new association field. Click the Select Output Field tab at the top to switch to the output field configuration page (such as Fig.13 ), where you can configure the alias of the source table column name of the output field, including the associated table and the original table.

[0066] (8) If you select an output node (such as Fig.14 ), configure the table into which the data in the input table should be imported. Select the data source type, select the data source, and select the table. There is currently no corresponding output table. Select the table row drop-down box and select None. After selecting None, a table name row will appear below, where you can customize the name of the table to be created. The fields in the column configuration default to the field names created in the previous node. If there is a corresponding output table in the target database, select the table corresponding to the number in the drop-down box in the table row. The column names in the column configuration are the column names in the selected table. Finally, click the Configuration Details button in the WHIT configuration row to enter the configuration page and configure the basic configuration items of FlinkSql.

[0067] (9) After configuring the output node, you can click the View SQL button in the upper left corner of the configuration page to enter the SQL preview page to view the generated FlinkSql (such as Fig.15 ). You can click the formatting standard sql in the upper left corner or click the verification button to verify the accuracy of the sql. After confirmation, return to the configuration page and click the save button in the upper left corner to save the sql to the database.

[0068] 2. Resource Management

[0069] By packaging the flinkSql generated in the job development, a jar package is generated for management and upload operations.

[0070] (1) Jar management list page: name (limited to 200 characters), type (system application, user application), file path, startup class, execution parameters, description, whether to enable (enable, disable), update time (default is creation time), operation column (edit, delete). The top left of the list displays the Create New and Batch Delete buttons. You can query based on name, type, and whether to enable. (e.g. Fig.16 )

[0071] 3. Job operation and maintenance

[0072] The developer will use the uploaded jar package file to realize data flow and synchronization. Fig.17 )

[0073] (1) Job list page: Displays the job name, operation mode, cluster queue name, job ID, status, modification time, and operation column (stop, restart, Flink Web UI), sorted in reverse order by primary key ID. Click the job name to enter the instance list page of the job.

[0074] ①Job name: the same as the name of the job development;

[0075] ② Operation mode: (FlinkJar, splicing SQL optional: Yarn Application; FlinkSQL optional: Local, Standalone, Yarn Application);

[0076] ③Cluster queue name: (limited to 64 bits, name = cluster instance name_YARN queue name, for example, iemr01_default);

[0077] ④Job ID: the ID corresponding to the task running on Flink;

[0078] ⑤ Status: RUNNING, FAILED, CANCELED, INITIALIZING, RESTARTING, CREATED, FAILING, SUSPENDED, CANCELLING, UNKNOWN. Different colored dots are displayed in front of different statuses.

[0079] ⑥Modification time (when created, the modification time is equal to the creation time).

[0080] (2) Compared with the job instance page, the historical instance page removes the Add, Edit, and Operation columns. On the job instance page, when you click a job name to enter the historical instance page, the job name is filtered by default.

[0081] (3) Click FlinkWeb and create a new tab to open the Flink management page.

[0082] (4) New pages: Job Name (required, drop-down list), Execution Mode (required, drop-down list), Cluster Queue (when the mode is Yarn Application, Cluster Queue is required), Executable Jar (when the mode is Yarn Application, select the Jar package in Resource Management), Checkpoint Step (minimum 0, help: checkpoint step of the task, 0 means disabled), Task Parallelism (minimum 1), SavePoint Strategy (help: specify SavePoint strategy, disabled by default), Other Configuration (help: other configuration items that will be applied to the execution environment, such as pipeline.name).

[0083] Embodiment 2, based on Embodiment 1, proposes a custom ETL process to implement FlinkSql statement configuration system, which is applied to the custom ETL process to implement the FlinkSql statement configuration method. The system includes a job development module, which is used to realize the splicing of the ETL process into FlinkSQL statements, wherein the job development module includes:

[0084] The data collection unit is used to collect the address of the data source, the fields of the table, the length and type of the fields required by the ETL process;

[0085] The process configuration unit is used to configure the corresponding ETL process, including five custom nodes: selecting input, configuring function, configuring where condition, configuring join, and configuring output. Users can build the ETL process by dragging and connecting these nodes; the process configuration unit also includes a graphic drawing interface, in which users can intuitively see the structure of the ETL process and open, rename, and delete the job by right-clicking or left-clicking.

[0086] The property configuration unit is used to configure the properties of each node, including data source type, data source name, table name, column configuration, and FlinkSql basic configuration items; the column configuration page in the property configuration unit allows users to configure the FlinkSQL type corresponding to each column, whether it is a partition field, and provides a default field type automatic matching function. Users configure the corresponding function for the selected column and set an alias, and enter the function details configuration page for further function parameter configuration.

[0087] The SQL generation unit is used to generate the corresponding FlinkSql statements according to the ETL process configured by the user, and provides SQL preview, formatting and verification functions.

[0088] It also includes a resource management module, which is used to package the FlinkSql generated during job development to generate a jar package, and provides management and upload operations for the jar package; the resource management module includes a Jar management list page, which displays the name, type, file path, startup class, execution parameters, description, whether it is enabled, and update time information of the jar package, and supports querying and filtering based on name, type, and whether it is enabled.

[0089] It also includes a job operation and maintenance module, which is used to execute uploaded jar package files to realize data flow and synchronization; the job operation and maintenance module includes a job list page, which displays the job name, operation mode, cluster queue name, job ID, status, modification time and operation column, and supports clicking the job name to enter the instance list page or historical instance page of the job; the job operation and maintenance module also supports opening the Flink management page through the Flink Web UI, and provides a new page for configuring the job name, execution mode, cluster queue, executable Jar package and other configuration items.

[0090] 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 method for configuring FlinkSql statements using a custom ETL process, characterized in that: The method comprises the following steps: Step 1: Operation of the job development page: Users open the job development page through the graphical interface, create or edit directories and jobs, where jobs include job name, job type, description, and select directory; Step 2: Graphically configure the ETL process: In the graphical drawing interface of the job development page, the user drags and connects the required custom nodes, including selecting input, configuring function, configuring where condition, configuring join, and configuring output. Click the node to open the configuration page for property configuration. Step 3: Column configuration and FlinkSql basic configuration items: The user configures the data source type, data source name, table name, and column configuration for each node. The column configuration includes the FlinkSQL type corresponding to each column, whether it is a partition field, and the alias, and also configures the FlinkSql basic configuration items. Step 4: Function, where condition, join and output configuration: According to actual needs, users select and configure the function in the Configure Function node, enter the where condition in the Configure Where Condition node, select the associated table in the Configure Join node and configure the join type and condition, and select the output table in the Configure Output node and configure the output field; Step 5: Generate, preview, and save FlinkSql statements: After the user configuration is completed, you can view the generated FlinkSql statements, format, verify, and save them to ensure the correctness and availability of the FlinkSql statements.

2. According to claim 1, a custom ETL process implements FlinkSql statement configuration method, characterized in that: The column configuration in step 3 also includes: Users automatically match or manually adjust the FlinkSQL type based on the data source field type; User-configured column aliases and other related information, such as field length and field type.

3. According to claim 1, a custom ETL process implements FlinkSql statement configuration method, characterized in that: The function configuration in step 4 also includes: The user selects the corresponding function for the selected column and configures the alias; The user enters the function details configuration page to perform further function parameter configuration, such as function input parameters and output parameters.

4. According to claim 1, a custom ETL process realizes FlinkSql statement configuration method, characterized in that: It also includes job operation and maintenance steps, including: Provides a job list page that displays the job name, operation mode, cluster queue name, job ID, status, modification time, and operation column; Users can stop, restart, and view Flink Web UI operations on jobs through the Operation column. Provides a historical instance page to display the historical execution records of the job; Supports users to add new jobs and configure relevant job parameters, such as execution mode, cluster queue, and executable Jar package.

5. According to claim 1, a custom ETL process realizes FlinkSql statement configuration method, characterized in that: It also includes resource management steps, including: Provides a Jar management list page to display the Jar package name, type, file path, startup class, execution parameters, description, whether it is enabled, and update time information; Users manage Jar packages through the Create, Edit and Delete buttons; Supports users to query and filter Jar packages based on name, type, and whether they are enabled.

6. A custom ETL process to implement FlinkSql statement configuration system, applied to the custom ETL process to implement FlinkSql statement configuration method according to any one of claims 1 to 5, characterized in that: The system includes a job development module, which is used to realize the splicing of ETL processes into FlinkSQL statements, wherein the job development module includes: The data collection unit is used to collect the address of the data source, the fields of the table, the length and type of the fields required by the ETL process; The process configuration unit is used to configure the corresponding ETL process, including five custom nodes: select input, configure function, configure where condition, configure join, and configure output. Users can build the ETL process by dragging and connecting these nodes; The property configuration unit is used to configure the properties of each node, including data source type, data source name, table name, column configuration, and FlinkSql basic configuration items; The SQL generation unit is used to generate the corresponding FlinkSql statements according to the ETL process configured by the user, and provides SQL preview, formatting and verification functions.

7. A custom ETL process implementing FlinkSql statement configuration system according to claim 6, characterized in that: The process configuration unit also includes a graphic drawing interface, in which the user can intuitively see the structure of the ETL process and open, rename, and delete the job by right-clicking or left-clicking.

8. A custom ETL process implementing FlinkSql statement configuration system according to claim 6, characterized in that: The column configuration page in the property configuration unit allows users to configure the FlinkSQL type corresponding to each column, whether it is a partition field, and provides a default field type automatic matching function. Users configure the corresponding function for the selected column and set an alias, and enter the function details configuration page for further function parameter configuration.

9. A custom ETL process implementing FlinkSql statement configuration system according to claim 6, characterized in that: It also includes a resource management module, which is used to package the FlinkSql generated during job development into a jar package, and provides jar package management and upload operations; The resource management module includes the Jar management list page, which displays the name, type, file path, startup class, execution parameters, description, whether it is enabled, and update time information of the jar package, and supports querying and filtering based on name, type, and whether it is enabled.

10. A custom ETL process implementing FlinkSql statement configuration system according to claim 6, characterized in that: It also includes a job operation and maintenance module, which is used to execute uploaded jar package files to realize data flow and synchronization; the job operation and maintenance module includes a job list page, which displays the job name, operation mode, cluster queue name, job ID, status, modification time and operation column, and supports clicking the job name to enter the instance list page or historical instance page of the job; the job operation and maintenance module also supports opening the Flink management page through the Flink Web UI, and provides a new page for configuring the job name, execution mode, cluster queue, executable Jar package and other configuration items.

Images