A logical verification method and device applied to Flink SQL

Abstract

The present invention discloses a logical verification method and device applied to Flink SQL, which relates to the field of computer technology. A specific implementation manner of the method includes: obtaining the SQL text of the logic to be verified, splitting the SQL statements in the SQL text to obtain multiple SQL statements; analyzing the insert SQL statements to obtain the created objects, and searching for the table creation DDL statements used to create the objects; replacing the external components in the table creation DDL statements with logical verification components corresponding to the types of the objects, and then performing logical verification on the SQL text based on the logical verification components. This implementation manner transforms the existing DataGen and Print components in the Flink SQL framework to obtain two Connectors, namely ApusDataGen and ApusPrint, to better adapt to the product to display data, facilitate users to quickly verify the accuracy of the SQL logic, facilitate early problem discovery, and shorten the development cycle.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular, to a logical verification method and device applied to Flink SQL. Background Art

[0002] With the development of big data technology, excellent big data computing engine frameworks such as Storm, Spark, and Flink have emerged. Among them, Flink is mainly used. For the API layer facing users, in order to lower the threshold for users to use real-time computing, a development language Flink SQL that conforms to standard SQL semantics is designed.

[0003] Currently, there is no open-source product that can be combined with the Flink SQL platform. Developers need to combine underlying technologies with products for users to use. The Flink open-source framework provides two connectors. One is the component DataGen for generating debug data for data source tables, and the other is the component Print for displaying the results of target source tables. In the process of implementing the present invention, the inventors found the following problems in the prior art:

[0004] 1. DataGen currently does not support generating debug data for dimension tables, which limits the business usage scenarios; it is impossible to display the generated debug data to users for viewing, and it is a black box. Although it supports customization for each field, it is not flexible enough to uniformly configure a certain type of field.

[0005] 2. When Print displays, it is impossible to distinguish the logical verification results in which target source table, because a task may have multiple target sources. Summary of the Invention

[0006] In view of this, embodiments of the present invention provide a logical verification method and device applied to Flink SQL, which can at least solve the problems existing in the DataGen component and the Print component in the prior art.

[0007] To achieve the above object, according to one aspect of the embodiments of the present invention, a logical verification method applied to Flink SQL is provided, including:

[0008] Obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0009] Analyze the insert SQL statement to obtain the created object, and find the table creation DDL statement for creating the object; where the object includes a data source table and / or a dimension table, and a target source table;

[0010] Replace the external components in the table creation DDL statement with logical verification components corresponding to the type of the object, and then perform logical verification on the SQL text based on the logical verification components.

[0011] Optionally, the analyzing the insert SQL statement to obtain the created object and finding the table creation DDL statement for creating the object includes:

[0012] Analyze the insert SQL statement through the first string to obtain the target source table, and find the table creation DDL statement for creating the target source table;

[0013] Analyze the insert SQL statement through the second string to obtain the data source table, and find the table creation DDL statement for creating the data source table; and / or

[0014] Analyze the insert SQL statement through the third string to obtain the dimension table, and find the table creation DDL statement for creating the dimension table.

[0015] Optionally, the analyzing the insert SQL statement to obtain the created object and finding the table creation DDL statement for creating the object includes:

[0016] Traverse the insert SQL statement through the first string to obtain the target source table and store it in the target source table set;

[0017] Traverse each SQL statement again by regular matching to obtain the table creation DDL statement, and obtain the table name in the table creation DDL statement;

[0018] Determine whether the table name exists in the target source table set. If it exists, determine it as the table creation DDL statement of the target source table, otherwise it is the table creation DDL statement of the data table source or the dimension table.

[0019] Optionally, the replacing the external components in the table creation DDL statement with logical verification components corresponding to the type of the object includes:

[0020] For the target source table, replace the external component in the table creation DDL statement for displaying the verification result data of the target source table with the first logical verification component;

[0021] For the data source table and / or the dimension table, replace the external component in the table creation DDL statement for generating debug data with the second logical verification component.

[0022] Optionally, it further includes: for the first logical verification component, introduce a debug identifier parameter to add the debug identifier parameter and the table name of the corresponding target source table in the prefix of the verification result data;

[0023] When presenting data, filter the verification result data using the debug identifier parameter, and based on the table name of the target source table in the prefix, present the verification result data in a tabular form in the log.

[0024] Optionally, it further includes: for the second logical verification component, introduce a preset length parameter of the string type to generate corresponding debug data with a preset length according to the preset length parameter of the string type.

[0025] Optionally, it further includes: introduce a debug identifier parameter to add the debug identifier parameter, the table name of the corresponding data source table or dimension table in the prefix of the debug data.

[0026] When presenting data, filter the debug data using the debug identifier parameter, and based on the table name of the data source table or dimension table in the prefix, present the debug data in a tabular form in the log.

[0027] Optionally, it further includes: traverse the SQL statement to obtain the create catalog statement, and replace the external catalog in the create catalog statement with the in-memory catalog default in Flink SQL.

[0028] Optionally, the splitting of the SQL statements in the SQL text includes:

[0029] Determine the delimiter from the delimiter declaration in the SQL text to split the SQL statements in the SQL text using the delimiter.

[0030] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a logical verification device applied to Flink SQL, including:

[0031] A splitting module, configured to obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0032] A searching module, configured to analyze the insert SQL statement to obtain the created object, and search for the create table DDL statement for creating the object; where the object includes a data source table and / or a dimension table, and a target source table;

[0033] A replacement module, configured to replace the external components in the create table DDL statement with the logical verification components corresponding to the type of the object, and then perform logical verification on the SQL text based on the logical verification components.

[0034] Optionally, the searching module is configured to:

[0035] Analyze and insert SQL statements through the first string to obtain the target source table, and search for the DDL statement for creating the target source table;

[0036] Analyze and insert SQL statements through the second string to obtain the data source table, and search for the DDL statement for creating the data source table; and / or

[0037] Analyze and insert SQL statements through the third string to obtain the dimension table, and search for the DDL statement for creating the dimension table.

[0038] Optionally, the searching module is used for:

[0039] Traverse and insert SQL statements through the first string to obtain the target source table and store it in the target source table set;

[0040] Traverse each SQL statement again through regular matching to obtain the DDL statement for creating the table, and obtain the table name in the DDL statement for creating the table;

[0041] Judge whether the table name exists in the target source table set. If it exists, determine it as the DDL statement for creating the target source table; otherwise, it is the DDL statement for creating the data source table or the dimension table.

[0042] Optionally, the replacing module is used for:

[0043] For the target source table, replace the external component used to display the verification result data of the target source table in the DDL statement for creating the table with the first logical verification component;

[0044] For the data source table and / or the dimension table, replace the external component used to generate the debug data in the DDL statement for creating the table with the second logical verification component.

[0045] Optionally, it further includes a debug identifier parameter module, which is used for:

[0046] For the first logical verification component, introduce the debug identifier parameter to add the debug identifier parameter and the table name of the corresponding target source table in the prefix of the verification result data;

[0047] When displaying the data, filter the verification result data by using the debug identifier parameter, and display the verification result data in sub-tables in the form of logs based on the table name of the target source table in the prefix.

[0048] Optionally, it further includes a string type preset length parameter module, which is used for:

[0049] For the second logical verification component, introduce the string type preset length parameter to generate the corresponding preset length of debug data according to the string type preset length parameter.

[0050] Optionally, it further includes a debug identifier parameter for:

[0051] Introduce a debug identifier parameter to add the debug identifier parameter and the table names of the corresponding data source table or dimension table to the prefix of the debug data;

[0052] When displaying data, filter the debug data using the debug identifier parameter, and display the debug data in separate tables in the form of a log based on the table names of the data source table or dimension table in the prefix.

[0053] Optionally, it further includes a catalog module for:

[0054] Traverse the SQL statements to obtain the create catalog statements, and replace the external catalog in the create catalog statements with the in-memory catalog default in Flink SQL.

[0055] Optionally, the splitting module is used to: determine the delimiter from the delimiter declaration in the SQL text, and use the delimiter to split the SQL statements in the SQL text.

[0056] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a logic verification electronic device applied to Flink SQL.

[0057] The electronic device in the embodiments of the present invention includes: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement any of the above-mentioned logic verification methods applied to Flink SQL.

[0058] To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a computer-readable medium having a computer program stored thereon, and when the program is executed by a processor, it implements any of the above-mentioned logic verification methods applied to Flink SQL.

[0059] According to the solution provided by the present invention, one embodiment of the above invention has the following advantages or beneficial effects: Applied to the Flink SQL platform, it mainly solves the problem of how to reduce the development cost of obtaining the final correct SQL statement by continuously interacting with the real upstream and downstream production environments to adjust the SQL statement logic. Through this solution, the accuracy of the SQL statement logic can be quickly verified, problems can be detected in a timely manner before going online, the business development cycle can be shortened, and the development and personnel costs can be reduced.

[0060] The further effects of the above non-conventional optional methods will be described in conjunction with the specific embodiments below. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] The drawings are used to better understand the present invention and do not constitute an improper limitation to the present invention. Among them:

[0062] Figure 1 is a schematic diagram of the main process of a logical verification method applied to Flink SQL according to an embodiment of the present invention;

[0063] Figure 2 is a schematic diagram of the process of an optional logical verification method applied to Flink SQL according to an embodiment of the present invention;

[0064] Figure 3 is a schematic diagram of the process of another optional logical verification method applied to Flink SQL according to an embodiment of the present invention;

[0065] Figure 4 is a schematic diagram of the process of a specific logical verification method applied to Flink SQL according to an embodiment of the present invention;

[0066] Figure 5 is a schematic diagram of the main modules of a logical verification device applied to Flink SQL according to an embodiment of the present invention;

[0067] Figure 6 is an exemplary system architecture diagram to which the embodiments of the present invention can be applied;

[0068] Figure 7 is a schematic diagram of the structure of a computer system of a mobile device or a server suitable for implementing the embodiments of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0069] The following describes exemplary embodiments of the present invention with reference to the drawings, including various details of the embodiments of the present invention to facilitate understanding. It should be considered that they are merely exemplary. Therefore, those of ordinary skill in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for the sake of clarity and conciseness, the description below omits the description of well-known functions and structures.

[0070] For the terms involved in this solution, the explanations are as follows:

[0071] DDL (Data Definition Language): used to create or delete tables, views, etc.

[0072] Flink: An open-source computing engine under Apache that can handle streaming tasks, batch tasks, and support SQL for consuming data processing and persisting it to external storage systems, etc.

[0073] AVRO, JSON, CSV: Common data format types.

[0074] Catalog: An abstract concept in a database. A database can have multiple catalogs. Each catalog has multiple databases, and each database has multiple tables.

[0075] Connector: External components such as Mysql, Kafka, etc. These components are integrated into the Flink engine, and by using these components, access to these external components can be achieved.

[0076] In the traditional field of streaming computing, such as Storm and Spark Streaming, some Functions or Datastream APIs are provided. Users write business logic in Java or Scala. Although this method is flexible, there are some deficiencies. For example, it has a certain threshold and is difficult to optimize. As the version is continuously updated, there are also many incompatible places in the API.

[0077] Considering the generality and ease of use of SQL, the existing approach combines traditional SQL development with big data tools and applies traditional SQL to the big data field, reducing the threshold for using big data. Similarly, the Flink engine also provides the Flink SQL syntax. Flink SQL is a development language that conforms to standard SQL semantics and is designed to simplify the computing model and lower the threshold for users to use real-time computing in Flink real-time computing.

[0078] Currently, both Spark and Flink are actively moving towards the SQL platform. Through the process of validating business logic on the Flink SQL platform provided by this solution, the accuracy of SQL statement logic can be quickly verified. Problems can be discovered in a timely manner before the product is launched, shortening the business development cycle and reducing development and personnel costs.

[0079] See Figure 1 , which shows the main flowchart of a logic verification method applied to Flink SQL provided by an embodiment of the present invention, including the following steps:

[0080] S101: Obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0081] S102: Analyze the inserted SQL statement to obtain the created object, and find the DDL statement for creating the table used to create the object; where the object includes a data source table and / or a dimension table, and a target source table.

[0082] S103: Replace the external components in the DDL statement for creating the table with logical verification components corresponding to the type of the object, and then perform logical verification on the SQL text based on the logical verification components.

[0083] In the above implementation, for step S101, in actual operation, the open-source product is provided to users through the Flink SQL platform. Therefore, the debugging function is designed into the product and the content of the underlying application. Currently, the FlinkSQL platform provides multiple Connectors, among which the common ones are Mysql, Kafka, etc.

[0084] Taking the platform editing task as an example, each SQL statement is separated by a semicolon, and multiple SQL statements constitute a task:

[0085] / / This is the table creation statement, which will create a connection with the kafka message queue and then read data. It is a data source table

[0086]

[0087]

[0088] / / This is the table creation statement, which will create a connection with the kafka message queue and then write the data processed by the SQL into it. It is a target source table

[0089]

[0090] / / This is the inserted SQL statement, which will read the data from the data source table and write it into the target source table

[0091] INSERT INTO kafkaTableSink SELECT*FROM kafkaTableSource

[0092] The above shows a simple SQL task, which reads the data from the kafka data source table and writes it into the kafka target source table. In actual operation, a task may also have multiple data sources or target sources. For example, based on the above code, add more SQL statements to set multiple target sources. Specifically:

[0093] / / Create another mysql target source

[0094]

[0095] / / Similarly, write the data from the above Kafka data source to this MySQL target source as well.

[0096] INSERT INTO mysqlink SELECT*FROM kafkaTableSource

[0097] After adding the above SQL statement, the entire task now includes two target sources, Kafka and MySQL, and one data source, Kafka.

[0098] Split the entire task (SQL text) according to a delimiter to obtain various types of SQL statements and store them in an SQL set, such as create table (create), query (select), insert into (insert into) statements, etc. The delimiter here can be customized. For example, declare the delimiter in the first line of the SQL editor on the Flink SQL platform. The default is a semicolon, and the declaration usage is delimiter symbol.

[0099] For steps S102 - S103, traverse each SQL statement in the SQL set. Through regular expression matching, find the objects to be searched for, namely the data source table and / or dimension table, and the target source table. Among them, a dimension table (also known as a dimension table) is a quantity used for data analysis.

[0100] The debugging function needs to split the entire SQL text according to a semicolon or a customized delimiter to obtain each complete SQL statement. Then, if a certain SQL is a table creation statement, special processing will be carried out. Therefore, the purpose of splitting the SQL statement is to find the table creation statement in the SQL text. Because during the process of SQL logical accuracy verification, it is not possible to actually create a real external table and pull real online data. Instead, the SQL written by the business side is parsed to obtain the DDL statements for creating tables related to the data source, target source, and dimension table, and the real associated Connector is replaced with ApusPrint (i.e., the first logical verification component) and ApusDataGen (i.e., the second logical verification component) for the SQL logical verification function.

[0101] Example 1, the object is the target source

[0102] By regular matching (including keyword processing for insert into / overwrite), find the SQL statements that contain the target source table name. For example, if the SQL statement "INSERT INTO mysqlink SELECT*FROM kafkaTableSource" is found as above, it is analyzed that "mysqlink is a target source", and INSERT INTO is the first string. For example:

[0103]

[0104] Analyze according to the insert statement insert into to obtain the mysqlink target source table. Find the create table DDL statement that includes mysqlink for component replacement, and replace the parameters in the original external component WITH with the parameters of ApusPrint as follows:

[0105]

[0106] Example 2, the object is the data source table

[0107] The operation method of the data source table is the same as that of the target source table. The insert statement is found through the insert statement "insert into". For example, in the above "INSERT INTO kafkaTableSink SELECT*FROM kafkaTableSource", kafka is analyzed as the data source table through "SELECT*FROM" (i.e., the second string).

[0108] Find the create table DDL statement that includes kafka, and also perform component replacement. Replace the parameters in the original external component WITH used to generate debug data with the parameters of the first logical verification component ApusDataGen. The ApusDataGen component supports the generation of debug data for ordinary data sources, such as:

[0109]

[0110] Example 3, the object is the dimension table

[0111] The acquisition method of the dimension table is different from that of the data source table and the target source table. The keyword syntax it uses is FOR SYSTEM_TIME AS OF (i.e., the third string). For example, the hive dimension table:

[0112] insert into mysqlink

[0113] SELECT…FROM kafkaTableSource AS o

[0114] JOIN shipu3_test_0922 FOR SYSTEM_TIME AS OF o.pro AS dim

[0115] ON o.name = dim.customer;

[0116] The table - creation statement of the dimension table is the same as that of the data source and the target source. Therefore, for the dimension table, the parameters within the original external component WITH used to generate debug data will also be replaced with the parameters of the custom ApusDataGen component, and the ApusDataGen component can support the generation of debug data for the dimension table.

[0117] It should be noted that the insert SQL statement is usually located after the table - creation DDL statement. Therefore, after the first traversal of the SQL statement, only the data source table, the dimension table, and the target source table can be obtained. Therefore, the search for the table - creation DDL requires a second traversal of the SQL statement.

[0118] Example 4, different from the above Examples 1 - 3, can be regarded as an independent example

[0119] Traverse each SQL statement in the SQL set. First, find the SQL insert statement through the insert statement "insert into", analyze the SQL insert statement to obtain the target source table and store it in the target source table set. Similarly, a data source table set and a dimension table set can also be established. However, since in the SQL text, it is either the target source table or the data source table or the dimension table, it is preferred to only create the target source table set.

[0120]

[0121] Traverse each SQL statement in the SQL set again. A large number of regular matching expressions are set for matching. If a table - creation DDL statement in the create table style is matched, obtain the table name after "createtable" from the table - creation DDL statement, and determine whether the table name exists in the target source table set:

[0122] 1) If it exists, it means it is the target source table. Process the table - creation DDL statement again, and replace the parameters within the original external component WITH with the parameters of the first logical verification component ApusPrint.

[0123] 2) If it does not exist, it means it is the data source table or the dimension table. Process the table - creation DDL statement again, and replace the parameters within the original external component WITH with the parameters of the second logical verification component ApusDataGen.

[0124] After the above-described First Embodiment, Second Embodiment, Third Embodiment, and Fourth Embodiment are executed, the adapted SQL statements will be executed by the relevant interfaces of Flink SQL to logically verify the table creation DDL statements using the ApusDataGen component and the ApusPrint component, and obtain the verification result data.

[0125] The method provided in the above embodiments reconstructs the DataGen component to form the ApusDataGen component with more abundant functions, which can support the generation of debugging data for data sources and dimension tables, enriching more business scenarios available for SQL logical verification. In addition, the result data generated by the logical verification is cached and printed out in the form of logs after the task is executed.

[0126] See Figure 2 , which shows a schematic flowchart of an optional logical verification method applied to Flink SQL according to an embodiment of the present invention, including the following steps:

[0127] S201: For the first logical verification component, introduce a debug identifier parameter to add the debug identifier parameter and the table name of the corresponding target source table to the prefix of the verification result data;

[0128] S202: When displaying data, filter the verification result data using the debug identifier parameter, and display the verification result data in a sub-table form in the form of logs based on the table name of the target source table in the prefix;

[0129] S203: For the second logical verification component, introduce a preset length parameter of the string type to generate corresponding debugging data of the preset length according to the preset length parameter of the string type;

[0130] S204: Introduce a debug identifier parameter to add the debug identifier parameter, the table name of the corresponding data source table or dimension table to the prefix of the debugging data;

[0131] S205: When displaying data, filter the debugging data using the debug identifier parameter, and display the debugging data in a sub-table form in the form of logs based on the table name of the data source table or dimension table in the prefix.

[0132] In the above implementation manner, for steps S201 to S202 and S204 to S205, the data related to the data source, dimension table, and target source table will be output to the logs. Since there are many other logs in the Flink SQL task, in order to facilitate extracting specific data from the logs, a debug identifier "debug-identifier" is introduced.

[0133] For example, for the target source table:

[0134]

[0135] For the data source table or dimension table:

[0136]

[0137] Whether it is a data source, a dimension table or a target source, each piece of output data contains, in addition to the keyword configured by the debug-identifier parameter, the output of the table name, which is used to distinguish table data and facilitate subsequent interception of data from the log collection service and display of data in separate tables. The format is as follows (this is just an example):

[0138] INFO xxxxx

[0139] INFO xxxxx

[0140] INFO Debug identifier Data source table 1 (specific debug data)

[0141] INFO xxxxx

[0142] INFO Debug identifier Dimension table 2 (specific debug data)

[0143] INFO Debug identifier Target source table 3 (specific verification result data)

[0144] INFO xxxxx

[0145] To display the generated debug data to the user for viewing, the native DataGen component was also functionally extended to display the generated debug data in the form of logs. When performing data display later, first filter the debug data using the "debug-identifier debug identifier", and then display the debug data in separate tables in the form of logs based on the data source table name or dimension table name in the prefix.

[0146] Similarly, the original Print component was transformed and upgraded to the ApusPrint component, which changed from the original standard output to the method of printing the target source verification result data in the form of logs. At the same time, the table name keyword was added to distinguish the table to which the data belongs, and it is consistent with the data output format of the data source table, which is convenient for the product side to collect data and display data in separate tables in a unified manner. Specifically, first filter the verification result data using the "debug-identifier debug identifier", and then display the verification result data in separate tables in the form of logs based on the target source table name in the prefix.

[0147] The log service retrieves keywords in the task log according to the value of the configured debug-identifier parameter to obtain each complete data log, and then splits it according to the table name to cache the data of each table separately.

[0148] For step S203, although the existing system supports customization for each field, it cannot be uniformly configured for a certain type of field, which is not flexible enough. Suppose there are 100 string-type fields in the data source table, and currently, each of these 100 string-type fields needs to be set separately, which is rather cumbersome.

[0149] To address this issue, a stringtype-default-length preset length parameter for string types is introduced to control the preset length of data generated for table fields that are of string type.

[0150] For example, as mentioned above

[0151] name string,

[0152] age int,

[0153] sex string,

[0154] address string,

[0155] name, sex, and address are all of string type. Suppose the preset length for this type is 10, then a string with a length of 10 will be automatically generated, such as abcedfea12. age is of numeric type, and a number with a length of 10 will be generated, and the insufficient positions can be filled with 0.

[0156] In this way, there is no need to configure each field separately, and the same type of strings can be configured uniformly, with only one overall configuration; this length can be preset, and if not preset, the default length will be automatically used, which is flexible and changeable.

[0157] According to the above description, the data source, dimension table, and target source table implement the storage of the generated debug data or verification result data in the form of log files on disk; in addition, the ApusDataGen component used by the data source and dimension table is optimized, and a debug-identifier parameter is introduced to customize the configuration keywords, facilitating the log service to retrieve the log data related to the table data.

[0158] It also introduces a table name keyword identifier in the data output of ApusDataGen and ApusPrint components. The logging service can parse and obtain the data of each table from the chaotic task log files, facilitating the product side to display the results by table; the stringtype-default-length parameter is introduced. In the case of a large number of string types, the default length of the generated data for string type fields can be uniformly set, which is more flexible and convenient.

[0159] For the method provided in the above embodiments, in view of the problem that debug data cannot be displayed, a debug-identifier is set; for the problem that data cannot be distinguished and displayed, the table name is set in its prefix for distinction; the stringtype-default-length is introduced to handle the problem that fields cannot be uniformly configured, realizing configuration flexibility and variability.

[0160] See Figure 3 , which shows another optional logical verification method flow diagram applied to Flink SQL according to an embodiment of the present invention, including the following steps:

[0161] S301: Obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0162] S302: Traverse the SQL statements to obtain the create catalog statement, and replace the external catalog in the create catalog statement with the in-memory catalog default in Flink SQL;

[0163] S303: Analyze the insert SQL statement to obtain the created objects, and find the DDL statement for creating the table used to create the objects; where the objects include data source tables and / or dimension tables, and target source tables;

[0164] S304: Replace the external components in the DDL statement for creating the table with the logical verification components corresponding to the types of the objects, and then perform logical verification on the SQL text based on the logical verification components.

[0165] In the above embodiments, for steps S301, S303, and S304, reference can be made to Figure 1 the descriptions of steps S101 to S103 shown, which will not be elaborated here.

[0166] In the above embodiments, for step S302, the catalogs currently supported by Flink SQL include hivecatalog, xxbc catgalog, and generic_in_memory catalog (default). In the case of introducing an external catalog, such as hive catalog, if the target source or data source table is actually created through the hive metastore during the logical verification process, unnecessary dirty tables will be introduced in hive.

[0167] To reduce the introduction of dirty tables, for the Flink SQL scenario using an external catalog, the type of the external catalog created can be mapped to the default catalog type of Flink. After that, the tables created for this catalog will all be created based on memory. However, for other statements such as the creation of UDFs and Insert into statements, they can be directly executed without further processing. For example,

[0168]

[0169] Replace with

[0170]

[0171] For the Flink SQL scenario using an external catalog, the method provided in the above embodiments can map the external catalog to the default in-memory catalog of Flink. After that, the tables created for this catalog will all be created based on memory to reduce the occurrence of dirty table phenomena.

[0172] See Figure 4 , which shows a schematic diagram of the framework of a specific logical verification method applied to Flink SQL according to an embodiment of the present invention, including:

[0173] Flink SQL business development: The business side develops SQL services and writes SQL statements that conform to the Flink SQL syntax;

[0174] SQL parsing service: Parse the business SQL written by the business side and adapt each SQL in the parsing service. For example, replace the DDL statement for creating the target source table with the ApusPrint component, replace the DDL statement for creating the data source table and / or dimension table with the ApusDataGen component, and map the Catalog statement to a memory-based Catalog statement;

[0175] FlinSQL Engine Execution: After adaptation, submit the SQL statement to the relevant interfaces of the Flink SQL engine. The ApusDataGen and ApusPrint components will output the relevant data of the data source table, dimension table, and target source table in the form of logs.

[0176] Error Correction: If there are errors in the written SQL, the Flink SQL engine will display the reasons for the errors through logs during execution, facilitating the business side to modify the SQL statement according to the specific reasons and verify it again.

[0177] Log Collection: Match the records of the output result data according to keywords, collect table data, and each table summarizes its own data.

[0178] Product Display: Display the result data by table, facilitating the viewing of the results and verifying the accuracy of the SQL result logic.

[0179] For the method provided by the embodiments of the present invention, the Flink SQL platform supports the SQL logic verification function, which facilitates the quick verification of the SQL logic accuracy, discovers problems in advance, and shortens the development cycle. The specific beneficial effects are as follows:

[0180] 1. The existing method does not support the logical verification of dimension tables. This solution transforms the underlying code of the native DataGen component, enabling it to support both the generation of debugging data for data source tables and the generation of debugging data for dimension tables. The new component ApusDataGen can mask the formats of business real data (JSON, AVRO, CSV, etc.) and has better compatibility. It can also generate corresponding debugging data according to the Flink SQL internal types configured for each field in the table creation DDL statement.

[0181] 2. To display the generated debugging data, the native DataGen component is also functionally extended. The debugging data is displayed in the form of logs, and at the same time, the prefix of the output debugging data is customized to facilitate subsequent interception of the generated debugging data from the logs.

[0182] 3. The Print component for the result display function of the target source table is also transformed to create the ApusPrint component. The underlying implementation displays the verification result data in the form of logs, and at the same time, the output of the table name keyword is added. In the case of multiple target source tables, it is convenient for the FlinkSQL platform to display the result data of the SQL logic verification by table to the business side for result verification.

[0183] See Figure 5 , which shows the main module schematic diagram of a logical verification device 500 applied to Flink SQL provided by the embodiments of the present invention, including:

[0184] The splitting module 501 is configured to obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0185] The searching module 502 is configured to analyze the insert SQL statement to obtain the created object, and search for the table creation DDL statement for creating the object; wherein, the object includes a data source table and / or a dimension table, and a target source table;

[0186] The replacing module 503 is configured to replace the external components in the table creation DDL statement with logic verification components corresponding to the type of the object, and then perform logic verification on the SQL text based on the logic verification components.

[0187] In the implementation device of the present invention, the searching module 502 is configured to:

[0188] Analyze the insert SQL statement through the first string to obtain the target source table, and search for the table creation DDL statement for creating the target source table;

[0189] Analyze the insert SQL statement through the second string to obtain the data source table, and search for the table creation DDL statement for creating the data source table; and / or

[0190] Analyze the insert SQL statement through the third string to obtain the dimension table, and search for the table creation DDL statement for creating the dimension table.

[0191] In the implementation device of the present invention, the searching module 502 is configured to:

[0192] Traverse the insert SQL statement through the first string to obtain the target source table and store it in the target source table set;

[0193] Traverse each SQL statement again through regular matching to obtain the table creation DDL statement, and obtain the table name in the table creation DDL statement;

[0194] Determine whether the table name exists in the target source table set. If it exists, it is determined as the table creation DDL statement of the target source table, otherwise it is the table creation DDL statement of the data table source or the dimension table.

[0195] In the implementation device of the present invention, the replacing module 503 is configured to:

[0196] For the target source table, replace the external component in the table creation DDL statement for displaying the verification result data of the target source table with the first logic verification component;

[0197] For the data source table and / or the dimension table, replace the external component in the table creation DDL statement for generating debug data with the second logic verification component.

[0198] The implementation device of the present invention further includes a debugging identifier parameter module for:

[0199] For the first logical verification component, introduce a debugging identifier parameter to add the debugging identifier parameter and the table name of the corresponding target source table to the prefix of the verification result data.

[0200] When presenting data, filter the verification result data using the debugging identifier parameter, and based on the table name of the target source table in the prefix, present the verification result data in a tabular form in the form of a log.

[0201] The implementation device of the present invention further includes a string type preset length parameter module for:

[0202] For the second logical verification component, introduce a string type preset length parameter to generate corresponding preset length debugging data according to the string type preset length parameter.

[0203] The implementation device of the present invention further includes a debugging identifier parameter for:

[0204] Introduce a debugging identifier parameter to add the debugging identifier parameter, the table name of the corresponding data source table or dimension table to the prefix of the debugging data.

[0205] When presenting data, filter the debugging data using the debugging identifier parameter, and based on the table name of the data source table or dimension table in the prefix, present the debugging data in a tabular form in the form of a log.

[0206] The implementation device of the present invention further includes a catalog module for:

[0207] Traverse the SQL statement to obtain the create catalog statement, and replace the external catalog in the create catalog statement with the Flink SQL default in-memory catalog.

[0208] In the implementation device of the present invention, the splitting module 501 is used for:

[0209] Determine the delimiter from the delimiter declaration in the SQL text to split the SQL statement in the SQL text using the delimiter.

[0210] In addition, the specific implementation content of the device in the embodiments of the present invention has been described in detail in the above method, so the repeated content will not be described here.

[0211] Figure 6 An exemplary system architecture 600 to which the embodiments of the present invention can be applied is shown, including terminal devices 601, 602, 603, a network 604, and a server 605 (merely examples).

[0212] The terminal devices 601, 602, and 603 can be various electronic devices with a display screen and supporting web browsing, installed with various communication client applications. Users can use the terminal devices 601, 602, and 603 to interact with the server 605 through the network 604 to receive or send messages, etc.

[0213] The network 604 is a medium for providing a communication link between the terminal devices 601, 602, 603 and the server 605. The network 604 can include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.

[0214] The server 605 can be a server that provides various services, used to execute writing SQL text, disassembling SQL statements, analyzing target source tables, data source tables, and dimension tables, replacing components and performing logical verification operations.

[0215] It should be noted that the method provided by the embodiments of the present invention is generally executed by the server 605. Correspondingly, the device is generally arranged in the server 605.

[0216] It should be understood that Figure 6 the numbers of terminal devices, networks, and servers in

[0217] are merely illustrative. According to actual needs, there can be any number of terminal devices, networks, and servers. Figure 7 , which shows a schematic structural diagram of a computer system 700 of a terminal device suitable for implementing the embodiments of the present invention. Figure 7 The shown terminal device is merely an example and should not impose any limitations on the functions and usage scope of the embodiments of the present invention.

[0218] As Figure 7 shown, the computer system 700 includes a central processing unit (CPU) 701, which can perform various appropriate actions and processes according to the program stored in the read-only memory (ROM) 702 or the program loaded from the storage section 708 into the random access memory (RAM) 703. In the RAM 703, various programs and data required for the operation of the system 700 are also stored. The CPU 701, ROM 702, and RAM 703 are connected to each other through a bus 704. The input / output (I / O) interface 705 is also connected to the bus 704.

[0219] The following components are connected to the I / O interface 705: an input section 706 including a keyboard, a mouse, etc.; an output section 707 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc. and a speaker, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN card, a modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as required. A removable medium 711 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc. is mounted on the drive 710 as required so that a computer program read therefrom is installed into the storage section 708 as required.

[0220] Specifically, according to the embodiments disclosed by the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, the embodiments disclosed by the present invention include a computer program product which includes a computer program carried on a computer-readable medium, and the computer program includes program codes for performing the methods shown in the flowcharts. In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 709, and / or installed from the removable medium 711. When the computer program is executed by a central processing unit (CPU) 701, the above functions defined in the system of the present invention are executed.

[0221] It should be noted that the computer-readable medium shown in the present invention can be a computer-readable signal medium, a computer-readable storage medium, or any combination of the two. A computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples of a computer-readable storage medium can include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium can be any tangible medium that contains or stores a program, and this program can be used by or in conjunction with an instruction execution system, apparatus, or device. In the present invention, a computer-readable signal medium can include a data signal propagated in a baseband or as part of a carrier wave, which carries computer-readable program code. Such a propagated data signal can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. A computer-readable signal medium can also be any computer-readable medium other than a computer-readable storage medium, and this computer-readable medium can send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device. The program code contained on a computer-readable medium can be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0222] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram can represent a module, a program segment, or a part of code, and the above module, program segment, or part of code contains one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks may occur in a different order than marked in the accompanying drawings. For example, two consecutive blocks shown may actually be executed substantially in parallel, and they may sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and the combination of blocks in a block diagram or flowchart, can be implemented by a dedicated hardware-based system for performing the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0223] The modules involved in the embodiments of the present invention can be implemented in software or in hardware. The described modules can also be provided in a processor. For example, it can be described as: a processor includes a splitting module, a searching module, and a replacing module. Among them, the names of these modules do not constitute a limitation to the module itself in some cases. For example, the replacing module can also be described as "replacing component and logical verification module".

[0224] As another aspect, the present invention also provides a computer-readable medium. The computer-readable medium can be included in the device described in the above embodiments; or it can exist alone without being assembled into the device. The above computer-readable medium carries one or more programs. When the above one or more programs are executed by the device, the device includes:

[0225] Obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements;

[0226] Analyze the insert SQL statement to obtain the created object, and search for the table creation DDL statement used to create the object; where the object includes a data source table and / or a dimension table, and a target source table;

[0227] Replace the external components in the table creation DDL statement with logical verification components corresponding to the type of the object, and then perform logical verification on the SQL text based on the logical verification components.

[0228] According to the technical solution of the embodiments of the present invention, the DataGen component is reconstructed to form an ApusDataGen component that can support richer functions, which can support the generation of debugging data for data sources and dimension tables, enriching more business scenarios available for SQL logical verification; for the problem that debugging data cannot be displayed, a debug-identifier is set, and for the problem that data cannot be distinguished and displayed, the table name is set in its prefix for distinction; stringtype-default-length is introduced to handle the problem that fields cannot be uniformly configured, realizing configuration flexibility and variability.

[0229] The above specific embodiments do not constitute a limitation to the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A logical verification method applied to Flink SQL, characterized in that, Including: Obtain the SQL text of the logic to be verified, split the SQL statements in the SQL text to obtain multiple SQL statements; Analyze the insert SQL statement to obtain the created object, and find the table creation DDL statement for creating the object; wherein, the object includes a data source table and / or a dimension table, and a target source table; Replace the external components in the table creation DDL statement with logic verification components corresponding to the type of the object, and then perform logic verification on the SQL text based on the logic verification components; wherein, replacing the external components in the table creation DDL statement with logic verification components corresponding to the type of the object includes: for the target source table, replace the external component in the table creation DDL statement for displaying the verification result data of the target source table with a first logic verification component; for the data source table and / or the dimension table, replace the external component in the table creation DDL statement for generating debug data with a second logic verification component; Introduce a debug identifier parameter. For each piece of data output for the data source, dimension table, and target source, in addition to including the debug identifier parameter, the output also adds the output of the table name.

2. The method according to claim 1, wherein The analyzing the insert SQL statement to obtain the created object and finding the table creation DDL statement for creating the object includes: Analyze the insert SQL statement through a first string to obtain the target source table, and find the table creation DDL statement for creating the target source table; Analyze the insert SQL statement through a second string to obtain the data source table, and find the table creation DDL statement for creating the data source table; and / or Analyze the insert SQL statement through a third string to obtain the dimension table, and find the table creation DDL statement for creating the dimension table.

3. The method according to claim 1, wherein The analyzing the insert SQL statement to obtain the created object and finding the table creation DDL statement for creating the object includes: Traverse the insert SQL statement through a first string to obtain the target source table and store it in the target source table set; Traverse each SQL statement again through regular matching to obtain the table creation DDL statement, and obtain the table name in the table creation DDL statement; Judge whether the table name exists in the target source table set. If it exists, it is determined as the table creation DDL statement of the target source table, otherwise it is the table creation DDL statement of the data table source or the dimension table.

4. The method according to claim 1, characterized in that It also includes: For the first logic verification component, introduce a debug identifier parameter to add the debug identifier parameter and the table name of the corresponding target source table to the prefix of the verification result data; When displaying data, use the debug identifier parameter to filter to obtain the verification result data, and based on the table name of the target source table in the prefix, display the verification result data in a log form by table.

5. The method according to claim 1, wherein It also includes: For the second logic verification component, introduce a preset length parameter of string type to generate debug data of a corresponding preset length according to the preset length parameter of string type.

6. The method according to claim 5, wherein It also includes: Introduce a debug identifier parameter to add the debug identifier parameter, the table name of the corresponding data source table or dimension table to the prefix of the debug data; When presenting data, filter the debug data using the debug identifier parameter, and based on the table name of the data source table or dimension table in the prefix, present the debug data in a tabular form in the form of a log.

7. The method according to claim 1, characterized in that It also includes: Traverse the SQL statement to obtain the create catalog statement, and replace the external catalog in the create catalog statement with the in-memory catalog default in Flink SQL.

8. The method according to claim 1, characterized in that, The splitting of the SQL statements in the SQL text includes: Determine the delimiter from the delimiter declaration in the SQL text to split the SQL statements in the SQL text using the delimiter.

9. A logical verification device applied to Flink SQL, characterized in that, It includes: A splitting module for obtaining the SQL text of the logic to be verified, splitting the SQL statements in the SQL text to obtain multiple SQL statements; A lookup module for analyzing the insert SQL statement to obtain the created object and looking up the DDL statement for creating the object; where the object includes a data source table and / or a dimension table, and a target source table; A replacement module for replacing the external components in the DDL statement for creating the table with the logical verification components corresponding to the type of the object, and then performing logical verification on the SQL text based on the logical verification components; where the replacement of the external components in the DDL statement for creating the table with the logical verification components corresponding to the type of the object includes: for the target source table, replacing the external component in the DDL statement for creating the table that is used to display the verification result data of the target source table with the first logical verification component; for the data source table and / or the dimension table, replacing the external component in the DDL statement for creating the table that is used to generate the debug data with the second logical verification component; Introduce a debug identifier parameter. For each piece of data output for the data source, dimension table, and target source, in addition to including the debug identifier parameter, the output also includes the table name.

10. An electronic device, characterized in that, It includes: One or more processors; A storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1-8.

11. A computer-readable medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method according to any one of claims 1-8.

Images