CN112596723B - Database script generation method, device, equipment and medium - Google Patents
Database script generation method, device, equipment and medium Download PDFInfo
- Publication number
- CN112596723B CN112596723B CN202011499835.8A CN202011499835A CN112596723B CN 112596723 B CN112596723 B CN 112596723B CN 202011499835 A CN202011499835 A CN 202011499835A CN 112596723 B CN112596723 B CN 112596723B
- Authority
- CN
- China
- Prior art keywords
- data
- database
- module
- component module
- processed
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 158
- 238000013515 script Methods 0.000 title claims abstract description 118
- 238000012545 processing Methods 0.000 claims abstract description 86
- 230000000007 visual effect Effects 0.000 claims abstract description 27
- 238000012795 verification Methods 0.000 claims abstract description 22
- 238000004458 analytical method Methods 0.000 claims abstract description 13
- 238000012800 visualization Methods 0.000 claims abstract description 11
- 230000015572 biosynthetic process Effects 0.000 claims abstract description 7
- 238000003786 synthesis reaction Methods 0.000 claims abstract description 7
- 238000006243 chemical reaction Methods 0.000 claims description 76
- 230000002776 aggregation Effects 0.000 claims description 52
- 238000004220 aggregation Methods 0.000 claims description 52
- 238000001914 filtration Methods 0.000 claims description 39
- 238000000586 desensitisation Methods 0.000 claims description 23
- 238000004590 computer program Methods 0.000 claims description 14
- 238000012163 sequencing technique Methods 0.000 claims description 13
- 238000004422 calculation algorithm Methods 0.000 claims description 9
- 230000002194 synthesizing effect Effects 0.000 claims description 3
- 230000006870 function Effects 0.000 description 8
- 238000007726 management method Methods 0.000 description 5
- 238000013468 resource allocation Methods 0.000 description 5
- 238000004364 calculation method Methods 0.000 description 3
- 238000005516 engineering process Methods 0.000 description 3
- 238000010276 construction Methods 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000002441 reversible effect Effects 0.000 description 2
- 238000012216 screening Methods 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 238000013507 mapping Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000003062 neural network model Methods 0.000 description 1
- 238000003672 processing method Methods 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000001960 triggered effect Effects 0.000 description 1
- 230000003442 weekly effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/34—Graphical or visual programming
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
- G06F16/2462—Approximate or statistical queries
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/35—Creation or generation of source code model driven
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/44—Arrangements for executing specific programs
- G06F9/445—Program loading or initiating
- G06F9/44521—Dynamic linking or loading; Link editing at or after load time, e.g. Java class loading
- G06F9/44526—Plug-ins; Add-ons
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Probability & Statistics with Applications (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Fuzzy Systems (AREA)
- Stored Programmes (AREA)
Abstract
The invention relates to the technical field of databases, and discloses a database script generation method, a device, equipment and a medium, wherein the method comprises the following steps: generating a request by receiving a script containing configuration data and a data processing flow model from a graphical visualization interface; establishing connection of databases in a Java database connection mode to obtain database tables; identifying a start component module, a process component module, and an end component module; calling a starting component module code, and performing column clipping on the database table to obtain a column to be processed; calling a process component module code, and processing the column to be processed according to the sequence of the directed data stream to obtain data to be processed; and calling an ending component module code, performing SQL analysis and synthesis on the data to be processed, and verifying the visual workflow to generate an execution script. The method and the device realize automatic verification and generation of the script codes of the database through the graphic visual interface, and improve the correctness and efficiency of database script generation.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a medium for generating a database script.
Background
With the advent of the big data age, the magnitude of data has increased, and database management systems have become particularly important for managing data. In the prior art, when data is stored and processed in a database management system, a specialized database language (SQL language) is generally required to operate on the data, and most database management systems, such as Oracle, sybase, SQL Server, etc., need to complete corresponding data processing by executing a database script file specially written by a developer. Because the database script stores codes written by SQL language, the database management system has very high requirements on the personnel used, the non-professional personnel can not write the database script easily, the operations such as data query and data update can not be performed, and the problems of low accuracy, low efficiency and the like exist in the manual script writing mode.
Disclosure of Invention
The invention provides a database script generation method, a database script generation device, computer equipment and a storage medium.
A database script generation method, comprising:
receiving a script generation request from a graphic visualization interface, and acquiring configuration data and a data processing flow model in the script generation request;
Establishing a connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to a table name in the configuration data from the database;
identifying a start component module, a process component module and an end component module in all component modules; the data processing flow model comprises at least three component modules;
calling initial component module codes corresponding to initial configuration parameters in the initial component module from an application server, and performing column clipping on the database table to obtain a column to be processed;
calling process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directional data flow sequence of the process component modules in the data processing flow model to obtain data to be processed;
and calling an end component module code corresponding to the end component module from the application server, analyzing and synthesizing SQL (structured query language) to the data to be processed according to the end configuration parameters in the end component module, performing visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
A database script generating apparatus comprising:
the receiving module is used for receiving a script generation request from the graphic visualization interface and acquiring configuration data and a data processing flow model in the script generation request;
the connection module is used for establishing connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to the table name in the configuration data from the database;
the identification module is used for identifying a starting component module, a process component module and an ending component module in all the component modules; the data processing flow model comprises at least three component modules;
the clipping module is used for calling initial component module codes corresponding to initial configuration parameters in the initial component module from the application server, and performing column clipping on the database table to obtain a column to be processed;
the calling module is used for calling the process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directed data flow sequence of the process component modules in the data processing flow model to obtain data to be processed;
And the generation module is used for calling an ending component module code corresponding to the ending component module from the application server, carrying out SQL analysis and synthesis on the data to be processed according to ending configuration parameters in the ending component module, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, the processor implementing the steps of the database script generation method described above when the computer program is executed.
A computer readable storage medium storing a computer program which, when executed by a processor, implements the steps of the database script generating method described above.
The invention provides a database script generation method, a device, computer equipment and a storage medium, which are used for receiving a script generation request containing configuration data and a data processing flow model from a graphic visualization interface; establishing a connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to a table name in the configuration data from the database; identifying a start component module, a process component module, and an end component module in all of the component modules; calling initial component module codes corresponding to initial configuration parameters in the initial component module from an application server, and performing column clipping on the database table to obtain a column to be processed; calling process component module codes corresponding to the process component modules from the application server, and processing the columns to be processed according to the directed data flow sequence to obtain data to be processed; and calling end component module codes corresponding to the end component modules from the application server, carrying out SQL analysis and synthesis on the data to be processed according to end configuration parameters in the end component modules, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification is passed, thereby realizing the componentization configuration component modules provided by a graphic visual interface, realizing the construction of flexible and changeable data processing flow models, automatically verifying and generating executable script codes of a database, greatly reducing code development amount, reducing technical thresholds of developers, and improving the correctness and efficiency of database script generation.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are needed in the description of the embodiments of the present invention will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic view of an application environment of a database script generation method according to an embodiment of the present invention;
FIG. 2 is a flow chart of a database script generation method in accordance with an embodiment of the present invention;
FIG. 3 is a flowchart of step S50 of a database script generation method in accordance with an embodiment of the present invention;
FIG. 4 is a flowchart of step S501 of a database script generation method in accordance with an embodiment of the present invention;
FIG. 5 is a flowchart of step S501 of a database script generation method in another embodiment of the present invention;
FIG. 6 is a functional block diagram of a conversion module of the database script generating apparatus in one embodiment of the present invention;
FIG. 7 is a schematic diagram of a computer device in accordance with an embodiment of the invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
The database script generation method provided by the invention can be applied to an application environment as shown in fig. 1, wherein a client (computer equipment) communicates with a server through a network. Among them, clients (computer devices) include, but are not limited to, personal computers, notebook computers, smartphones, tablet computers, and portable wearable devices. The server may be implemented as a stand-alone server or as a server cluster composed of a plurality of servers.
In one embodiment, as shown in fig. 2, a database script generating method is provided, and the technical scheme mainly includes the following steps S10-S60:
s10, receiving a script generation request from a graphic visualization interface, and acquiring configuration data and a data processing flow model in the script generation request.
The graphic visual interface (Graphical User Interface, abbreviated GUI, also called graphic user interface) is information such as a data processing flow model template, component modules and configuration parameters of the component modules for various graphic data processing, the graphic visual interface is a display interface of an application program, the application program is a program installed on a client, on the graphic visual interface, a user can drag the component modules and configure the configuration parameters thereof on a page, and connect the component modules in series through directional arrows, that is, the output of the last component module is used as the input of the next component module until the last component module, and the final data processing result is output, thereby creating the required data processing flow model, realizing the visual processing and/or storage of the data flow of the component modules, setting the detailed operation of the data in each component module in the graphic visual interface, and presetting the general component module in the graphic visual interface for the user to directly call or combine.
The script generation request is a request triggered after a user confirms that the data processing flow model is completed on the graphic visualization interface, the data processing flow model is a model formed by a directed acyclic graph formed by a plurality of component modules, and the configuration data is data related to establishing connection with a database of a target.
S20, establishing connection of a database corresponding to the database source configuration in the configuration data through a Java database connection mode, and acquiring a database table corresponding to the table name in the configuration data from the database.
It is understood that the configuration data includes a database source configuration, a user name, a password and a table name, a database matched with the database source configuration is established through a connection mode of Java database connection (JavaDatabase Connectivity, JDBC for short), the database logs in and accesses the database with the user name and the password, the authority of the user name and the password is set by the database before logging in, the database source configuration is information of related configuration of logging in the database source, and the database source configuration includes a database path, a database type (mysql, oracle, hive and other common databases) and a database name.
The table names can be obtained after a table selection frame in the graphic visual interface is selected and determined, and the table selection frame provides the functions of selecting tables in a database in modes of fuzzy query, regular matching, forward selection, reverse selection and the like.
S30, identifying a starting component module, a process component module and an ending component module in all component modules; the data processing flow model includes at least three of the component modules.
It is understood that the component modules include a data stream input and a data stream output, and by determining whether the data stream input and the data stream output of each of the component modules are connected, it is determined whether the component module is the start component module, the end component module or the process component module, the component module to which only the data stream output is connected is determined as the start component module, the component module to which only the data stream input is connected is determined as the end component module, and the component module to which both the data stream input and the data stream output are connected is determined as the process component module.
S40, calling a starting component module code corresponding to the starting configuration parameter in the starting component module from the application server, and performing column clipping on the database table to obtain a column to be processed.
The configuration parameters in the initial component module include column type and column parameters, wherein the initial configuration parameters are related parameters of columns required to be selected from the database table, the column parameters can be obtained after a column selection box in the graphic visualization interface is selected and determined, and the column selection box provides functions of fuzzy query, regular matching, forward selection, reverse selection and the like to achieve columns in the selection table.
The application server is a server for providing component module codes for clients provided with the application program, the application server stores all component module codes corresponding to the configuration parameters of the component modules one by one, the component module codes are code templates for realizing relevant configuration parameters in the component modules, the configuration parameters in the initial component module are determined to be the initial configuration parameters, and the component module codes corresponding to the initial configuration parameters are determined to be the initial component module codes.
In an embodiment, after the step S40, that is, the step of calling, from the application server, a start component module code corresponding to a start configuration parameter in the start component module, performing column cropping on the database table to obtain a column to be processed, further includes:
S401, performing row-column conversion on the column to be processed through the database conversion module according to row-column conversion parameters in the database conversion module to obtain conversion rows; the process component modules in the data processing flow model include a database conversion module.
The row-column conversion is to perform row-column conversion or column-column conversion operation on a specified column, and the program generates a row-column conversion program according to the configuration of the user, performs specified row-column conversion on the inputted data specified column, and generates final data.
S402, carrying out dimension association on the conversion rows through the ending component module according to association parameters in the database conversion module to obtain association data.
It may be understood that the data warehouse is divided into a number table and a dimension table, where the data warehouse is a storage unit that performs storage conversion on data stored in the database, the number table is stored with dimension numbers, the dimension numbers are numbers converted according to the association parameters, or a number generated according to a combination of the association parameters and a current time by applying a universal unique identification code (UUID) technology, the dimension table includes a dimension field and unit data corresponding to the dimension field, the dimension field is a first field of each row in the conversion row, the unit data is data corresponding to a cell of the dimension field, the conversion row is used as a dimension table to be created in the data warehouse, the dimension association refers to associating a dimension number in the number table with the conversion row, and a specific dimension value corresponding to each data in the conversion row is calculated according to the dimension number and a position of each data in the conversion row, where the calculation process is that the dimension number and the position of each data in the conversion row are sequentially combined, for example, the table is formed continuously: converting the dimension number into a numerical value, adding the number of rows (3 rows, 4 columns, 3×4) of the converted row, namely adding 34, starting counting from 1 after adding the numerical value again until 12, wherein each sequence value corresponds to one data of the converted row, the dimension field or the unit data under the dimension table corresponding to the dimension value can be quickly searched through the dimension value, and the subsequent data processing for the dimension table is carried out.
S403, calling end component module codes corresponding to the end component modules from the application server, and generating data warehouse instructions for the associated data according to the end configuration parameters in the end component modules to generate execution scripts.
It is appreciated that the data warehouse instructions are generated to add the end configuration parameters to the end component module code and convert the end configuration parameters in conjunction with the associated data into database executable instructions, the end configuration parameters including the location of a numbering table in the data warehouse (i.e., the numbering table that determines which dimension it belongs to), the operation of inserting the associated data in the numbering table being converted into database executable instructions, effecting row-column conversion and dimension-associated database operations.
According to the line-column conversion parameters in the database conversion module, line-column conversion is carried out on the columns to be processed through the database conversion module, so that converted lines are obtained; according to the association parameters in the database conversion module, carrying out dimension association on the conversion row through the ending component module to obtain association data; and calling an ending component module code corresponding to the ending component module from the application server, generating a data warehouse instruction for the associated data according to ending configuration parameters in the ending component module, and generating an execution script, so that the processing process of storing and converting the data of the database is satisfied through row-column conversion and dimension association, and the management of the database on the data is facilitated.
S50, calling the process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directed data flow sequence of the process component modules in the data processing flow model to obtain data to be processed.
It is understood that all the process component modules in the data processing flow model are connected in series through directional arrows, so as to form a directional data flow sequence, the serial sequence numbers are determined as the directional data flow sequence numbers, and the sequence according to the directional data flow sequence numbers is the directional data flow sequence. The conversion processing comprises data conversion processing and statistical operator processing, the data conversion processing comprises data processing processes of data sequencing, data filtering and data aggregation, the statistical operator processing comprises data processing processes of operators such as maximum value, minimum value, median, average number, field maximum length, field shortest length, mode, null value and the like, one or more data processing processes in the conversion processing corresponding to the configuration parameters are executed according to the process configuration parameters in the process component module, the desensitization processing is carried out on the converted columns to be processed, the desensitization processing is a process of identifying sensitive data of the converted columns to be processed, and the identified sensitive data is encrypted by using a data desensitization technology.
The configuration parameters in the process component modules are determined to be the process configuration parameters, the component module codes corresponding to the process configuration parameters are determined to be the process component module codes, the data desensitization comprises an elliptic encryption algorithm, and the elliptic encryption algorithm is high in safety, good in flexibility and high in encryption performance.
In one embodiment, as shown in fig. 3, in the step S50, the converting the column to be processed according to the directional data flow sequence of each process component module in the data processing flow model includes:
s501, performing data conversion processing on the column to be processed according to process configuration parameters in the data conversion module through the data conversion module to obtain data to be counted; the process component modules in the data processing flow model comprise a data conversion module and a data statistics module; the data conversion module is connected with the data statistics module in series.
The process configuration parameters may be set according to requirements, the process configuration parameters may include a sorting mode, a filtering column name, and an aggregation mode, the process of the data conversion processing may be that the columns to be processed are sorted according to the sorting mode, the columns to be processed after sorting are filtered by the filtering component module according to the filtering column name, a filtering column is obtained, the filtering columns are subjected to data aggregation by the aggregation component module according to the aggregation mode, the process configuration parameters further include a sorting mode, a filtering column name, an aggregation mode, and a desensitization identifier, the process of the data conversion processing may also be that the columns to be processed are sorted according to the sorting mode, the columns to be processed after sorting are filtered by the filtering component module according to the filtering column name, a filtering column is obtained, the data aggregation is performed by the aggregation component module according to the aggregation column to obtain aggregated data, the desensitization data is obtained according to the aggregation module, and the encryption algorithm is applied to the data to obtain the data to be subjected to be encrypted.
The process component modules in the data processing flow model comprise a data conversion module and a data statistics module, wherein the data conversion module is connected with the data statistics module in series, the data conversion module realizes the conversion of the input data in a multiple way, and the data statistics module is a module for carrying out statistics summarization on the input data.
In an embodiment, as shown in fig. 4, in step S501, performing data conversion processing on the column to be processed according to the process configuration parameters in the data conversion module includes:
s5011, sequencing the columns to be processed according to the sequencing mode through a sequencing component module; the data conversion module comprises the sequencing component module, a filtering component module and an aggregation component module; the process configuration parameters include a sorting mode, a filtering column name and an aggregation mode.
Understandably, the sorting component module, the filtering component module and the aggregation component module are all sub-modules of the process conversion module, the sorting component module sorts the selected columns according to the sorting mode in the process configuration parameters, the filtering component module realizes a function of screening the selected columns, and the aggregation component module realizes a function of data aggregation of the columns to be processed.
S5012, filtering the ordered columns to be processed through the filtering component module according to the filtering column names to obtain filtering columns.
Understandably, the filter column name is a field name of a column remaining after the filtering, and the filter column is data under the filter column name in the database table.
S5013, according to the aggregation mode, data aggregation is carried out on the filtering columns through the aggregation component module, and the data to be counted are generated.
The data aggregation means grouping according to one or more columns, counting, summing, maximum value, minimum value, average value and the like of the grouped columns, and determining the result of the aggregation calculation and the column to be processed as the data to be counted.
The invention realizes the sorting of the columns to be processed according to the sorting mode by the sorting component module; according to the filter column names, the ordered columns to be processed are filtered through the filter component module to obtain filter columns; according to the aggregation mode, the aggregation component module is used for carrying out data aggregation on the filtering columns to generate the data to be counted, so that the processing procedures of sequencing, filtering and data aggregation are realized, the multiple operation of the database table is satisfied, different data processing procedures are provided, and the user operation is facilitated.
In an embodiment, as shown in fig. 5, in step S501, the data conversion process is performed on the column to be processed according to the process configuration parameters in the data conversion module, and the method further includes:
s5014, sorting the columns to be processed according to a sorting mode through a sorting component module; the data conversion module comprises the sequencing component module, a filtering component module, an aggregation component module and a desensitization component module; the process configuration parameters include the ordering mode, the filter column name, the aggregation mode and the desensitization identification.
Understandably, the sorting component module, the filtering component module, the aggregation component module and the desensitizing component module are all sub-modules of the process conversion module, the sorting component module sorts the selected columns according to the sorting mode in the process configuration parameters, the filtering component module realizes a function of screening the selected columns, the aggregation component module realizes a function of data aggregation of columns to be processed, and the desensitizing component module realizes a function of desensitizing aggregated data after data aggregation.
S5015, filtering the ordered columns to be processed through the filtering component module according to the filtering column names to obtain filtering columns.
Understandably, the filter column name is a field name of a column remaining after the filtering, and the filter column is data under the filter column name in the database table.
S5016, according to the aggregation mode, data aggregation is carried out on the filtering columns through the aggregation component module, and aggregation data is generated.
The data aggregation means grouping according to one or more columns, counting, summing, maximum value, minimum value, average value and the like of the grouped columns, and determining the result of the aggregation calculation as the aggregated data.
S5017, according to the desensitization mark, performing data desensitization on the column to be processed through a desensitization component module by using an elliptic encryption algorithm, and determining the aggregate data and the desensitized column to be processed as the data to be counted.
The data desensitization is to apply an elliptic encryption algorithm to desensitize the sensitive fields in the column to be processed, and identify which fields are the sensitive fields through the desensitization identifier, wherein the elliptic encryption algorithm is an algorithm based on public key encryption of elliptic curve math, and the mode of identifying the type of the sensitive fields can extract the sensitive characteristics of the sensitive fields for a shallow neural network model, so that the type of the sensitive fields is identified according to the extracted sensitive characteristics.
The invention realizes that the columns to be processed are ordered according to the ordering mode through the ordering component module; according to the filter column names, the ordered columns to be processed are filtered through the filter component module to obtain filter columns; according to the aggregation mode, data aggregation is carried out on the filtering columns through the aggregation component module, so that aggregation data are generated; according to the desensitization mark, an elliptic encryption algorithm is used for carrying out data desensitization on the column to be processed through a desensitization component module, and the aggregated data and the desensitized column to be processed are determined to be the statistical data, so that the processing procedures of sequencing, filtering, data aggregation and data desensitization are realized, the diversified operation of a database table is satisfied, different data processing procedures are provided, the user operation is facilitated, and the data desensitization is carried out on sensitive fields, so that the security of the database table can be improved.
S502, inputting the data to be counted into the data counting module, and carrying out counting operator processing on the data to be counted according to the process configuration parameters in the data counting module to obtain data to be processed.
Understandably, the statistical operator processing includes data processing procedures of operators such as maximum value, minimum value, median, average number, field maximum length, field shortest length, mode, null value number, and the like, and one or more data processing procedures of the conversion processing corresponding to the configuration parameters are executed according to the process configuration parameters in the process component module.
The invention realizes the data conversion processing of the column to be processed according to the process configuration parameters in the data conversion module by the data conversion module to obtain the data to be counted; the data to be counted is input into the data counting module, and the data to be counted is subjected to counting operator processing according to the process configuration parameters in the data counting module to obtain the data to be processed, so that the data conversion and counting operator processing method is provided through the data conversion module and the data counting module, the data processing is modularized, and the database table operation is facilitated.
S60, calling an end component module code corresponding to the end component module from the application server, carrying out SQL analysis and synthesis on the data to be processed according to the end configuration parameters in the end component module, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
Understandably, find the component code matching with the operation column in the ending configuration parameter from the ending component module code, and analyze the component code and the data to be processed according to the found component code (StructuredQuery Language ), the SQL analyzes into SQL sentences which can be executed by a database, namely, the component code and the data to be processed are combined, and the SQL (StructuredQuery Language ) sentences which are in line with both the component code and the data to be processed are analyzed, and the SQL sentences with highest executable probability are predicted, the SQL sentences are execution sentences for performing database operations, all SQL sentences are synthesized, the synthesized SQL sentences are combined with the ending component module code, visual job flow verification is performed on the data to be processed, if verification is passed, the execution scripts are a set of SQL sentences which can be executed by the database, the visual job flow verification is performed on the synthesized data to be processed, and the SQL sentences are visualized, if the execution sequences of the SQL sentences are in line with the SQL sentences, the visual job flow is failed, the visual image is stopped, and if the module is failed to be executed, the visual image is passed through the visual interface, and the results are failed to be sequentially generated after the module is successfully executed, and the module is successfully executed.
And determining a configuration parameter in the ending component module as the ending configuration parameter, and determining the component module code corresponding to the ending configuration parameter as the ending component module code.
The method and the device realize that configuration data and a data processing flow model in a script generation request are obtained by receiving the script generation request from a graphic visual interface; establishing a connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to a table name in the configuration data from the database; identifying a start component module, a process component module, and an end component module in all of the component modules; the data processing flow model comprises at least three component modules; calling initial component module codes corresponding to initial configuration parameters in the initial component module from an application server, and performing column clipping on the database table to obtain a column to be processed; calling process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directional data flow sequence of the process component modules in the data processing flow model to obtain data to be processed; and calling an end component module code corresponding to the end component module from the application server, analyzing and synthesizing SQL (structured query language) to the data to be processed according to the end configuration parameters in the end component module, performing visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
In this way, a script generation request containing configuration data and a data processing flow model is received from the graphical visualization interface; establishing a connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to a table name in the configuration data from the database; identifying a start component module, a process component module, and an end component module in all of the component modules; calling initial component module codes corresponding to initial configuration parameters in the initial component module from an application server, and performing column clipping on the database table to obtain a column to be processed; calling process component module codes corresponding to the process component modules from the application server, and processing the columns to be processed according to the directed data flow sequence to obtain data to be processed; and calling end component module codes corresponding to the end component modules from the application server, carrying out SQL analysis and synthesis on the data to be processed according to end configuration parameters in the end component modules, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification is passed, thereby realizing the componentization configuration component modules provided by a graphic visual interface, realizing the construction of flexible and changeable data processing flow models, automatically verifying and generating executable script codes of a database, greatly reducing code development amount, reducing technical thresholds of developers, and improving the correctness and efficiency of database script generation.
In an embodiment, after the step S60, that is, after the generating the execution script, the method includes:
and S70, the execution script is sent to the application server, and the execution script is executed through the application server.
Understandably, the application server automatically executes the execution script upon receiving the execution script.
And S80, analyzing the timing configuration to generate a timing scheduling process when the application server detects the timing configuration in the execution script.
The timing configuration is understandably a setting that needs to be executed at a timing in the execution script, and the timing scheduling process is an executable process that can run in the background of the application server.
And S90, running the timing scheduling process through the application server, and triggering and executing the execution script according to the timing of the timing scheduling process.
Understandably, the background of the application server will execute the execution script to write into the database specified in the execution script. And under the condition that the timing configuration exists, the description is not a single-frequency execution, the user can execute the execution script at regular time through the timing configuration, such as daily, weekly and monthly timing scheduling, a timing scheduling rule and a unique timing scheduling process are generated according to the timing configuration, the background of the application server can run the timing scheduling process, the execution script is executed at regular time according to the user requirement, and the processing of the data in the database is completed.
The invention realizes that after the execution script is generated, the execution script is sent to the application server, and the execution script is executed by the application server; when the timing configuration in the execution script is detected by the application server, analyzing the timing configuration to generate a timing scheduling process; and running the timing scheduling process through the application server, and triggering and executing the execution script at fixed time according to the timing scheduling process, so that the database script is executed at fixed time according to the user demand, and diversity is provided for executing the database script.
In an embodiment, after the step S60, that is, after the generating the execution script, the method further includes:
and S100, the execution script is sent to a system service center based on a distributed open source scheduling framework, and the execution script is executed through a distributed task machine in the system service center.
Understandably, the system service center based on the distributed open source scheduling framework can regularly scan the number of current servers, re-perform task allocation, monitor and manage scheduled tasks and strategies in a web mode, and update and adjust in real time.
S110, when the system service center detects the timing configuration in the execution script, the timing configuration is analyzed to generate a timing task and a timing process.
The timing configuration is set to be executed at fixed time in the execution script, the process of parsing is to parse the timing configuration, parse a fixed time interval and a storage path of the execution script, create the timing task according to the fixed time interval, the timing task is a task executed every time the time interval is reached, store the execution script to the storage path according to the storage path of the execution script, and convert the execution script to the timing process, wherein the timing process is a process executable by a distributed task machine.
S120, the system service center scans scheduling information of each distributed task machine at regular time according to the timing tasks, performs resource scheduling analysis according to all scheduling information to obtain an optimal distributed task machine, and distributes the timing process to the optimal distributed task machine to execute the timing process.
The scheduling information of each distributed task machine is scanned at fixed time, the scheduling information is an information list of performance and resource allocation of the distributed task machine, the resource scheduling analysis is performed according to all scanned scheduling information, the optimal distributed task machine with highest total score of the performance and the resource allocation is analyzed, the total score is a process of mapping the information list of each distributed task machine to a sum of scores corresponding to the optimal distributed task machine, namely, the total score is obtained by carrying out weighted addition on all scores corresponding to the performance and all scores corresponding to the resource allocation, the resource scheduling analysis can be set according to requirements, preferably, the resource scheduling analysis is performed according to adjustable weights of the performance and the resource allocation, the weighted addition is a process of multiplying all scores corresponding to the performance by the adjustable weights of the resource allocation, and the two multiplied results are added, and the adjustable weights can be adjusted dynamically by learning the different running weights of the distributed task machines under the service center for one day.
The invention realizes that the execution script is transmitted to a system service center based on a distributed open source scheduling framework and is executed by a distributed task machine in the system service center; when the timing configuration in the execution script is detected by the system service center, analyzing the timing configuration to generate a timing task and a timing process; the system service center scans the scheduling information of each distributed task machine at regular time according to the timing tasks, performs resource scheduling analysis according to all scheduling information to obtain an optimal distributed task machine, and distributes the timing process to the optimal distributed task machine to execute the timing process, so that the system service center based on a distributed open source scheduling framework can perform resource scheduling on the distributed task machine, accurately and regularly execute the execution script, ensure the accuracy and the precision of timing execution, and realize the high efficiency of timing script execution.
In an embodiment, a database script generating apparatus is provided, which corresponds to the database script generating method in the above embodiment one by one. As shown in fig. 6, the database script generating apparatus includes a receiving module 11, a connecting module 12, an identifying module 13, a clipping module 14, a calling module 15, and a generating module 16. The functional modules are described in detail as follows:
The receiving module 11 is used for receiving a script generation request from the graphic visualization interface and acquiring configuration data and a data processing flow model in the script generation request;
the connection module 12 is configured to establish a connection with a database corresponding to a database source configuration in the configuration data by using a Java database connection manner, and obtain a database table corresponding to a table name in the configuration data from the database;
an identification module 13 for identifying a start component module, a process component module, and an end component module among all the component modules; the data processing flow model comprises at least three component modules;
the clipping module 14 is configured to call a starting component module code corresponding to a starting configuration parameter in the starting component module from the application server, and perform column clipping on the database table to obtain a column to be processed;
a calling module 15, configured to call, from the application server, a process component module code corresponding to each process component module, and process the column to be processed according to a directional data flow sequence of each process component module in the data processing flow model, so as to obtain data to be processed;
And the generating module 16 is used for calling the end component module code corresponding to the end component module from the application server, carrying out SQL analysis and synthesis on the data to be processed according to the end configuration parameters in the end component module, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
For specific limitations of the database scenario generation apparatus, reference may be made to the above limitation of the database scenario generation method, and no further description is given here. The respective modules in the database script generating apparatus described above may be implemented in whole or in part by software, hardware, or a combination thereof. The above modules may be embedded in hardware or may be independent of a processor in the computer device, or may be stored in software in a memory in the computer device, so that the processor may call and execute operations corresponding to the above modules.
In one embodiment, a computer device is provided, which may be a server, the internal structure of which may be as shown in fig. 7. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement a database script generation method.
In one embodiment, a computer device is provided that includes a memory, a processor, and a computer program stored on the memory and executable on the processor, the processor implementing the database script generation method of the above embodiments when the computer program is executed by the processor.
In one embodiment, a computer readable storage medium is provided, on which a computer program is stored, which when executed by a processor implements the database script generation method of the above embodiments.
Those skilled in the art will appreciate that implementing all or part of the above described methods may be accomplished by way of a computer program stored on a non-transitory computer readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in embodiments provided herein may include non-volatile and/or volatile memory. The nonvolatile memory can include Read Only Memory (ROM), programmable ROM (PROM), electrically Programmable ROM (EPROM), electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double Data Rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous Link DRAM (SLDRAM), memory bus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), among others.
It will be apparent to those skilled in the art that, for convenience and brevity of description, only the above-described division of the functional units and modules is illustrated, and in practical application, the above-described functional distribution may be performed by different functional units and modules according to needs, i.e. the internal structure of the apparatus is divided into different functional units or modules to perform all or part of the above-described functions.
The above embodiments are only for illustrating the technical solution of the present invention, and not for limiting the same; although the invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit and scope of the technical solutions of the embodiments of the present invention, and are intended to be included in the scope of the present invention.
Claims (10)
1. A database script generation method, comprising:
receiving a script generation request from a graphic visualization interface, and acquiring configuration data and a data processing flow model in the script generation request;
Establishing a connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to a table name in the configuration data from the database;
identifying a start component module, a process component module and an end component module in all component modules; the data processing flow model comprises at least three component modules;
calling initial component module codes corresponding to initial configuration parameters in the initial component module from an application server, and performing column clipping on the database table to obtain a column to be processed;
calling process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directional data flow sequence of the process component modules in the data processing flow model to obtain data to be processed;
and calling an end component module code corresponding to the end component module from the application server, analyzing and synthesizing SQL (structured query language) to the data to be processed according to the end configuration parameters in the end component module, performing visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
2. The database script generation method of claim 1, wherein after generating the execution script, comprising:
the execution script is sent to the application server, and the execution script is executed through the application server;
when the timing configuration in the execution script is detected by the application server, analyzing the timing configuration to generate a timing scheduling process;
and running the timing scheduling process through the application server, and triggering and executing the execution script according to the timing of the timing scheduling process.
3. The database script generation method of claim 1, wherein after generating the execution script, further comprising:
the execution script is sent to a system service center based on a distributed open source scheduling framework, and the execution script is executed through a distributed task machine in the system service center;
when the timing configuration in the execution script is detected by the system service center, analyzing the timing configuration to generate a timing task and a timing process;
and the system service center scans scheduling information of each distributed task machine at regular time according to the timing tasks, performs resource scheduling analysis according to all scheduling information to obtain an optimal distributed task machine, and distributes the timing process to the optimal distributed task machine to execute the timing process.
4. The database script generation method of claim 1 wherein said processing said column to be processed in a directed data flow order of each of said process component modules in said data processing flow model comprises:
the data conversion module is used for carrying out data conversion processing on the column to be processed according to the process configuration parameters in the data conversion module to obtain data to be counted; the process component modules in the data processing flow model comprise a data conversion module and a data statistics module; the data conversion module is connected with the data statistics module in series;
inputting the data to be counted into the data counting module, and carrying out counting operator processing on the data to be counted according to the process configuration parameters in the data counting module to obtain data to be processed.
5. The database script generation method of claim 4, wherein the performing data conversion processing on the column to be processed according to a process configuration parameter in the data conversion module comprises:
sequencing the columns to be processed according to the process configuration parameters through a sequencing component module; the data conversion module comprises a sequencing component module, a filtering component module and an aggregation component module; the process configuration parameters comprise a sequencing mode, a filtering column name and an aggregation mode;
According to the filter column names, the ordered columns to be processed are filtered through the filter component module to obtain filter columns;
and according to the aggregation mode, carrying out data aggregation on the filtering columns through the aggregation component module to generate the data to be counted.
6. The database script generation method of claim 4 wherein the performing data conversion processing on the column to be processed according to a process configuration parameter in the data conversion module further comprises:
sorting the columns to be processed by a sorting component module according to a sorting mode; the data conversion module comprises the sequencing component module, a filtering component module, an aggregation component module and a desensitization component module; the process configuration parameters comprise the sorting mode, the filtering column name, the aggregation mode and the desensitization mark;
according to the filter column names, the ordered columns to be processed are filtered through the filter component module to obtain filter columns;
according to the aggregation mode, data aggregation is carried out on the filtering columns through the aggregation component module, so that aggregation data are generated;
and according to the desensitization mark, performing data desensitization on the column to be processed through a desensitization component module by using an elliptic encryption algorithm, and determining the aggregated data and the desensitized column to be processed as the data to be counted.
7. The method for generating a database script according to claim 1, wherein the calling a start component module code corresponding to a start configuration parameter in the start component module from the application server, and performing column clipping on the database table to obtain a column to be processed, further comprises:
according to the row-column conversion parameters in the database conversion module, carrying out row-column conversion on the columns to be processed through the database conversion module to obtain conversion rows; the process component modules in the data processing flow model include a database conversion module;
according to the association parameters in the database conversion module, carrying out dimension association on the conversion row through the ending component module to obtain association data;
and calling end component module codes corresponding to the end component modules from the application server, and generating data warehouse instructions for the associated data according to the end configuration parameters in the end component modules to generate execution scripts.
8. A database scenario generation apparatus, comprising:
the receiving module is used for receiving a script generation request from the graphic visualization interface and acquiring configuration data and a data processing flow model in the script generation request;
The connection module is used for establishing connection of a database corresponding to the database source configuration in the configuration data in a Java database connection mode, and acquiring a database table corresponding to the table name in the configuration data from the database;
the identification module is used for identifying a starting component module, a process component module and an ending component module in all the component modules; the data processing flow model comprises at least three component modules;
the clipping module is used for calling initial component module codes corresponding to initial configuration parameters in the initial component module from the application server, and performing column clipping on the database table to obtain a column to be processed;
the calling module is used for calling the process component module codes corresponding to the process component modules from the application server, and processing the column to be processed according to the directed data flow sequence of the process component modules in the data processing flow model to obtain data to be processed;
and the generation module is used for calling an ending component module code corresponding to the ending component module from the application server, carrying out SQL analysis and synthesis on the data to be processed according to ending configuration parameters in the ending component module, carrying out visual workflow verification on the synthesized data to be processed, and generating an execution script after the verification passes.
9. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that the processor implements the database script generation method of any of claims 1 to 7 when executing the computer program.
10. A computer readable storage medium storing a computer program, wherein the computer program when executed by a processor implements the database script generation method of any of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011499835.8A CN112596723B (en) | 2020-12-17 | 2020-12-17 | Database script generation method, device, equipment and medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011499835.8A CN112596723B (en) | 2020-12-17 | 2020-12-17 | Database script generation method, device, equipment and medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112596723A CN112596723A (en) | 2021-04-02 |
| CN112596723B true CN112596723B (en) | 2024-02-13 |
Family
ID=75199303
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011499835.8A Active CN112596723B (en) | 2020-12-17 | 2020-12-17 | Database script generation method, device, equipment and medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112596723B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113434220A (en) * | 2021-07-15 | 2021-09-24 | 上海商汤科技开发有限公司 | Method, device and equipment for constructing workflow and computer storage medium |
| CN113448657B (en) * | 2021-09-01 | 2021-11-30 | 深圳市信润富联数字科技有限公司 | Method for generating and executing dynamic spark task |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110109897A (en) * | 2019-04-15 | 2019-08-09 | 深圳壹账通智能科技有限公司 | Database script generation method, device, computer equipment and storage medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9372671B2 (en) * | 2011-08-30 | 2016-06-21 | Sybase, Inc. | Modeling and code generation for SQL-based data transformations |
-
2020
- 2020-12-17 CN CN202011499835.8A patent/CN112596723B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110109897A (en) * | 2019-04-15 | 2019-08-09 | 深圳壹账通智能科技有限公司 | Database script generation method, device, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112596723A (en) | 2021-04-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110428127B (en) | Automatic analysis method, user equipment, storage medium and device | |
| CN111444175A (en) | Database table changing method and device, computer equipment and storage medium | |
| CN112596723B (en) | Database script generation method, device, equipment and medium | |
| US11809406B2 (en) | Event records in a log file | |
| CN110750414B (en) | Mobile data monitoring and analyzing method and device, computer equipment and storage medium | |
| CN109272215B (en) | Project development quality monitoring method and device, computer equipment and storage medium | |
| CN112286806A (en) | Automatic testing method and device, storage medium and electronic equipment | |
| CN113626241B (en) | Abnormality processing method, device, equipment and storage medium for application program | |
| CN112631686A (en) | Data processing method, data processing device, computer equipment and storage medium | |
| CN105205144A (en) | Method and system used for data diagnosis and optimization | |
| CN111400367A (en) | Business report generation method and device, computer equipment and storage medium | |
| CN112000903A (en) | Data query method and device, computer equipment and storage medium | |
| CN110502513B (en) | Data acquisition method, device, equipment and computer readable storage medium | |
| CN111367754A (en) | Data monitoring method and device, computer equipment and storage medium | |
| US11823086B2 (en) | Membership analyzing method, apparatus, computer device and storage medium | |
| CA3144122A1 (en) | Data verifying method, device and system | |
| CN118014506A (en) | Batch processing method and device for financial business, computer equipment and storage medium | |
| US9519727B2 (en) | Improving the efficiency of real-time website optimization processes using saliency analysis | |
| CN113885896B (en) | Application software package installation method and device, computer equipment and storage medium | |
| CN109857998A (en) | A kind of intelligence generates the method and device of EXCEL table | |
| CN110874612B (en) | Time interval prediction method and device, computer equipment and storage medium | |
| CN111984742B (en) | Audit line management method and device and computer equipment | |
| CN114003784A (en) | Request recording method, device, equipment and storage medium | |
| CN113468076A (en) | Application program exception testing method, device, equipment and storage medium | |
| CN114116268A (en) | Method and device for checking Flink SQL statement, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |