CN116204189A - Data processing method based on sql, server and electronic equipment - Google Patents
Data processing method based on sql, server and electronic equipment Download PDFInfo
- Publication number
- CN116204189A CN116204189A CN202211711889.5A CN202211711889A CN116204189A CN 116204189 A CN116204189 A CN 116204189A CN 202211711889 A CN202211711889 A CN 202211711889A CN 116204189 A CN116204189 A CN 116204189A
- Authority
- CN
- China
- Prior art keywords
- target
- sql
- server
- data
- query
- 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.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/44—Encoding
- G06F8/447—Target code generation
-
- 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/2457—Query processing with adaptation to user needs
-
- 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/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
-
- 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/31—Programming languages or programming paradigms
- G06F8/315—Object-oriented languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/70—Software maintenance or management
- G06F8/71—Version control; Configuration management
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Computing Systems (AREA)
- Computer Security & Cryptography (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides an sql-based data processing method, a server and electronic equipment. The method comprises the following steps: the server acquires an sql template; the server receives the inquiry requirement sent by the terminal; the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on query data of the target sql statement, wherein the target database is also used for sending the queried target data to the server; and the server generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
Description
Technical Field
The present application relates to the field of distributed data processing technologies, and in particular, to an sql-based data processing method, a server, a computer readable storage medium, and an electronic device.
Background
With the development of information security, large enterprises often split the business into business departments under each subdivision business, each business department has independent application systems and database groups, and the opposite side databases cannot be directly accessed between the business department systems. In order to solve the number supply requirements among different systems, a unified number drawing and number supply capability component can be provided, and the data of the data table are subjected to number drawing processing and sending through a certain configuration.
At present, it is common in the industry to deploy a down-shift script for executing a data table on each database, read a library table sentence needing to be down-shifted, execute the library table sentence, output the data table data to a file in a down-shift mode, and send the data table data to a downstream system. The executing down-level script is deployed on each database, so that most of the down-level script can only process simple sql screening sentences, and personalized processing can not be performed on the sql sentences.
Disclosure of Invention
The main object of the present application is to provide an sql-based data processing method, a server, a computer readable storage medium and an electronic device, so as to at least solve the problem that in the prior art, sql sentences cannot be personalized.
To achieve the above object, according to one aspect of the present application, there is provided an sql-based data processing method, including: the method comprises the steps that a server obtains an sql template, wherein the sql template refers to a template of a predefined structured query sql language; the server receives a query requirement sent by the terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data; the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database so that the target database obtains target data based on query data of the target sql statement, wherein the target database is also used for sending the queried target data to the server, and the server is communicated with the target database; and the server generates a target file according to the target data and sends the target file to the terminal.
Optionally, the query requirement at least includes a configuration parameter of sql, where the configuration parameter is a parameter of an operation command for configuring an sql statement, and the server generates a target sql statement at least according to the sql template and the query requirement, and includes: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates the operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
Optionally, the server determines a first mapping relationship between the configuration parameter and the field in the target database, and determines a second mapping relationship between the configuration parameter and the sql statement, including: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement; updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the updating comprises the following steps: and updating a template field of the sql statement in the copied sql template into the field in the target database corresponding to the query field in the configuration parameter by the server according to the first mapping relation, and updating a template condition of the sql statement in the copied sql template into the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation to obtain the target sql statement by the server.
Optionally, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement of preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value and averaging, wherein before the server generates the target file according to the target data, the method further comprises the following steps: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copied sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Optionally, the query requirement further includes a plurality of target table identifiers, and sending the target sql statement to a target database includes: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployments corresponding to the target table identifiers, including: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql statements to the target database where the target data table is located according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the target file is multiple, and sending the target file to the terminal includes: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; the server controls the data transfer equipment to combine the temporarily stored target files under the condition that the target files are uploaded to the data transfer equipment, and receives a reply file sent by the data transfer equipment, wherein the reply file is obtained by combining the target files; and the server sends the reply file to the terminal.
According to another aspect of the present application, there is provided a server including: the system comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring an sql template, wherein the sql template refers to a template of a predefined structured query sql language; the receiving unit is used for receiving the query requirement sent by the terminal, wherein the query requirement refers to the requirement of requesting to query and acquire data; the first processing unit is used for generating a target sql statement at least according to the sql template and the query requirement, and sending the target sql statement to a target database so that the target database obtains target data based on query data of the target sql statement, wherein the target database is also used for sending the queried target data to the server, and the server is communicated with the target database; and the second processing unit is used for generating a target file according to the target data and sending the target file to the terminal.
According to still another aspect of the present application, there is provided a computer-readable storage medium including a stored program, wherein the program performs any one of the methods.
According to still another aspect of the present application, there is provided an electronic apparatus including: one or more processors, memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising instructions for performing any of the methods.
By applying the technical scheme, firstly, a server acquires an sql template, then the server receives the query requirement sent by the terminal, then the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on query data of the target sql statement, and finally the server generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
Drawings
The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application and do not constitute an undue limitation to the application. In the drawings:
fig. 1 shows a block diagram of a hardware structure of a mobile terminal performing an sql-based data processing method according to an embodiment of the present application;
FIG. 2 shows a flow diagram of an sql-based data processing method provided in accordance with an embodiment of the present application;
FIG. 3 shows a deployment architecture diagram of a plurality of applications of the present solution;
FIG. 4 shows a timing diagram of sql-based data processing of the present approach;
fig. 5 shows a block diagram of a server provided according to an embodiment of the present application.
Detailed Description
It should be noted that, in the case of no conflict, the embodiments and features in the embodiments may be combined with each other. The present application will be described in detail below with reference to the accompanying drawings in conjunction with embodiments.
In order to make the present application solution better understood by those skilled in the art, the following description will be made in detail and with reference to the accompanying drawings in the embodiments of the present application, it is apparent that the described embodiments are only some embodiments of the present application, not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art based on the embodiments herein without making any inventive effort, shall fall within the scope of the present application.
It should be noted that the terms "first," "second," and the like in the description and claims of the present application and the above figures are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate in order to describe the embodiments of the present application described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
For convenience of description, the following will describe some terms or terms related to the embodiments of the present application:
library and table separation: refers to a process of splitting a single data table of a single database into multiple data tables of multiple databases according to different rules, and scattering data into the multiple data tables of the multiple databases (splitting the single database into multiple databases, splitting the single data table of the single database into multiple data tables of multiple databases). According to different rules, the data may fall into different data tables.
Drawing: and in the process of extracting the database table data by the data warehouse, the data in the database table is extracted through a certain sql screening statement and the like according to the requirement of a downstream system.
Number of supplies: and the data warehouse processes the number of the downstream system, generates a file from the data extracted from the database table according to the requirement of the downstream system, and supplies the file to the downstream system.
With the development of information security, large enterprises often split the business into business departments under each subdivision business, each business department has independent application systems and database groups, and the opposite side databases cannot be directly accessed between the business department systems. They have data cross and dependencies between each other, such as that the a-department needs basic data of the B-department, which may also need some basic data of the a-department.
For a large number of supply demands, if each supply demand is required to develop a program operation, not only human resources are consumed, but also operation and maintenance difficulties are increased, and the use of server resources is not easy to control, so that congestion of supply tasks of a certain time node is unavoidable, and the occurrence of system operation is affected. Meanwhile, data to the associated system is generally provided in files, and the data needs to be subjected to special conversion treatment.
The lower-grade script executed by the deployment on each database can only process simple sql screening sentences, and personalized processing of the data cannot be performed, for example, mapping processing is performed on the data according to different data dictionaries, and personalized processing such as reprocessing is performed on special sensitive data is performed.
As described in the background art, in the prior art, a executed down script is deployed on each database, most of the down script can only process simple sql screening sentences, and cannot perform personalized processing on the sql sentences.
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.
The method embodiments provided in the embodiments of the present application may be performed in a mobile terminal, a computer terminal or similar computing device. Taking the mobile terminal as an example, fig. 1 is a block diagram of a hardware structure of a mobile terminal according to an sql-based data processing method according to an embodiment of the present invention. As shown in fig. 1, a mobile terminal may include one or more (only one is shown in fig. 1) processors 102 (the processor 102 may include, but is not limited to, a microprocessor MCU or a processing device such as a programmable logic device FPGA) and a memory 104 for storing data, wherein the mobile terminal may also include a transmission device 106 for communication functions and an input-output device 108. It will be appreciated by those skilled in the art that the structure shown in fig. 1 is merely illustrative and not limiting of the structure of the mobile terminal described above. For example, the mobile terminal may also include more or fewer components than shown in fig. 1, or have a different configuration than shown in fig. 1.
The memory 104 may be used to store a computer program, for example, a software program of application software and a module, such as a computer program corresponding to a display method of device information in an embodiment of the present invention, and the processor 102 executes the computer program stored in the memory 104 to perform various functional applications and data processing, that is, to implement the above-described method. Memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include memory remotely located relative to the processor 102, which may be connected to the mobile terminal via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof. The transmission device 106 is used to receive or transmit data via a network. Specific examples of the network described above may include a wireless network provided by a communication provider of the mobile terminal. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, simply referred to as NIC) that can connect to other network devices through a base station to communicate with the internet. In one example, the transmission device 106 may be a Radio Frequency (RF) module, which is configured to communicate with the internet wirelessly.
In the present embodiment, there is provided an sql-based data processing method that operates on a mobile terminal, a computer terminal, or a similar computing device, it being noted that the steps shown in the flowcharts of the drawings may be performed in a computer system such as a set of computer-executable instructions, and that although a logical order is shown in the flowcharts, in some cases the steps shown or described may be performed in an order different from that herein.
Fig. 2 is a flow chart of an sql-based data processing method according to an embodiment of the present application. As shown in fig. 2, the method comprises the steps of:
step S201, a server acquires an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
Step S202, the server receives a query requirement sent by a terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
Step S203, the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on the query data of the target sql statement, wherein the target database is further used for sending the queried target data to the server, and the server communicates with the target database;
specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
In step S204, the server generates a target file according to the target data, and transmits the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, a preprocessing interface can be added before the data downshifting and file writing processing process, so that the processing and writing preprocessing can be realized, the processing is performed on the sql statement of the downshifting before the data downshifting, the flexible dynamic downshifting capability of the sql is further realized, and the processing can be performed on the data file before the data file is written (before the target file is generated), so that the individuation processing can be flexibly performed on the downshifting data file.
Through the embodiment, firstly, a server acquires an sql template, then the server receives a query requirement sent by a terminal, then the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on query data of the target sql statement, and finally the server generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
In some schemes, the number of the downshifts is made based on the data export capability of the database, the data export script is relatively simple, the personalized processing capability of the data is not provided, meanwhile, the personalized slicing downshifting capability is not supported when the downshifts are performed according to the information downshifts of the specific database and the data table, so that the subsequent data downshifting efficiency is lower under the condition that the data quantity is distributed in a scattered manner, the subsequent data downshifting efficiency is improved by performing personalized processing on the sql statement based on the query requirement sent by the terminal, in the specific implementation process, the query requirement at least comprises the configuration parameter of the sql, the configuration parameter is the parameter of the operation command for assigning the sql statement, and the server generates the target sql statement at least according to the sql template and the query requirement, and the method can be realized by the following steps: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates an operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
In the scheme, the query requirement sent by the terminal can comprise the configuration parameters of the sql, but the configuration parameters of the sql sent by the terminal are not complete sql sentences and are not corresponding to the fields in the target database, so that the first mapping relation between the configuration parameters of the sql and the fields in the target database and the second mapping relation between the configuration parameters of the sql and the operation commands in the sql sentences can be determined first, dynamic adjustment is performed on the copied sql template, personalized processing of the sql sentences is realized, and the problem that personalized processing of the sql sentences cannot be performed further is solved.
In order to further accurately determine the first mapping relationship and the second mapping relationship, so as to ensure that a target sql statement can be generated more efficiently and accurately based on the accurate first mapping relationship and the second mapping relationship, in a specific implementation process, the server determines the first mapping relationship between the configuration parameters and the fields in the target database, and determines the second mapping relationship between the configuration parameters and the sql statement, which can be achieved by the following steps: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement;
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of the boy is to be queried, and then the corresponding conditional instruction in the sql statement is a sphere instruction.
In the scheme, a first mapping relation between the query field and the field in the target database can be pre-stored in the field mapping table, so that the query field in the configuration parameters can be directly extracted, and the field in the target database corresponding to the query field can be accurately determined by querying in the field mapping table; the configuration parameters actually have query conditions, and the query conditions correspond to the conditional instructions in the sql statement, so that the query conditions are directly converted into the conditional instructions in the corresponding sql, and the conditional instructions of the sql statement corresponding to the query conditions can be accurately determined.
Because the first mapping relation and the second mapping relation determined before are more accurate, the target sql statement can be more efficiently and accurately generated based on the accurate first mapping relation and the second mapping relation, and in the specific implementation process, the operation command of the sql statement in the replication sql template is updated according to the first mapping relation and the second mapping relation to obtain the target sql statement, and the method can be realized by the following steps: the server updates a template field of the sql statement in the replication sql template to the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and updates a template condition of the sql statement in the replication sql template to the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, thereby obtaining the target sql statement.
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of a male is to be queried, and then the corresponding conditional instruction in the sql statement is a where instruction, and then according to the determined field and the conditional instruction, the original copied sql template is "select from table name where subject=xx", and the generated target sql statement is "select from score table where subject=language and gender=0".
In the scheme, under the condition that the first mapping relation between the configuration parameters and the fields in the target database is determined and the second mapping relation between the configuration parameters and the sql statement is determined, the fields in the target database corresponding to the query fields in the configuration parameters can be directly determined, the conditional instruction of the sql statement corresponding to the query conditions in the configuration parameters can be directly determined, and further, the copied sql template is updated, so that an accurate target sql statement can be obtained, and further, personalized processing of the sql statement is further realized.
Specifically, a read-before-process interface can be set before data is acquired, so that personalized processing can be performed on sql sentences before data is read, dynamic sql sentence screening is further realized, personalized processing can be performed before a target file is generated, namely before data writing, a write-before-process interface is set, data processing before writing can be supported, and further self-defined data processing capacity can be realized.
In practical applications, the terminal may not only query data alone, but also request service processing according to the queried data, and in order to implement post-read processing on the data individually, in one embodiment of the present application, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement for preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and averaging, wherein before the server generates the target file according to the target data, the method further comprises: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes the service sql statement based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of a male is to be queried, and the corresponding conditional instruction in the sql statement is a where instruction, then according to the determined field and the conditional instruction, the original copied sql template is "select from table name where subject=xx", and the generated target sql statement is "select from table where subject=language and gender=0"; on the basis of the terminal requests statistics of data of all men, then on the basis of the original replication sql template, the generated business sql statement may be "select sun (score) from score table where subject=language and gender=0".
In the scheme, under the condition that the terminal also requests to process the queried data, the queried data can be preprocessed based on the service processing requirement of the terminal, or a service sql statement can be generated, the generated sql statement is updated in a sql template, so that the service sql statement is obtained, and personalized processing of the sql statement in the data read post-processing is further realized.
Because the current data size is larger, when the data of the same logic data table is counted down, the data size of the file executed by the single database is insufficient, and meanwhile, for the logic table with a large data size, the processing of the output data file of the next file is slower, so as to improve the counting efficiency of the data table, flexibly configure the number of fragments, the inquiry requirement also comprises a plurality of target table identifiers, and the target sql statement is sent to the target database, and the method can be realized by the following steps: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
In the scheme, under the application of the distributed storage databases and the distributed storage data tables, the deployed positions of the different data tables can be different, so that corresponding target sql statements can be sequentially sent to the corresponding target databases according to the deployed positions of the data tables, a plurality of target databases can execute the corresponding target sql statements, the efficiency of data query can be improved through the distributed mode, the slower data query and processing speed caused by larger data storage in one database can be avoided, the efficiency of data slicing can be improved, and the efficiency of the following data tables can be improved.
Specifically, the data can be processed in a concurrent manner according to the slicing mode, slicing downshifting can be performed by default according to the database, multi-slicing downshifting can be performed by default of one database, specific slicing rules can be set individually, thus the downshifting requirements under different scenes can be met, meanwhile, the slicing configuration can be dynamically adjusted according to the data size, and further the data downshifting and counting task can be efficiently completed.
In order to improve the capability of the subsequent data downshifting and number supplying, tasks may be generated based on the database and table division to query data from multiple databases respectively, so that sql statements may be dynamically adjusted according to multiple databases, in some embodiments, the server may send the target sql statements to the corresponding target databases according to the positions where the target data tables corresponding to the multiple target table identifiers are deployed, and the method may specifically be implemented by the following steps: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data tables corresponding to the target table identifiers are deployed.
Specifically, the deployment position (i.e. the rule of database and table division) of the corresponding target data table can be determined according to the target table identification, if a plurality of data tables are database and table division, a plurality of tasks can be generated and distributed to execute the tasks, so that the task of the next file can be executed concurrently according to the positions of different data tables, and the task can be executed in the executed database and the specific data segment according to the different positions set by the data tables.
For executing a plurality of tasks, the tasks can be executed concurrently, the tasks can be executed by a worker in a server, specific service execution tasks are adopted, the tasks are randomly distributed to different workers, when the tasks are executed, corresponding target sql sentences are acquired according to the tasks, the designated target sql sentences are executed, and then a simple data file can be exported according to the obtained data.
In the scheme, the method can be realized based on a data distribution scene of a database sub-table, the concurrent processing of the sub-tables can be performed according to the granularity of the database, the tasks can be generated concurrently according to the dimension of the database for one logic table (data table) to process the data of the logic table and all sub-tables under the database, meanwhile, the method can support multi-sub-tables of a single database and formulate a sub-table processing file, can specify the number of the data of a specific sub-table under the database, simultaneously, can support the concurrent number of the sub-tables according to the plurality of sub-tables under the database, and can flexibly configure the number of the sub-tables according to the downstream requirement and the data amount by applying, so that the efficiency of the number of the sub-tables of the data is further improved.
In a practical application scenario, a plurality of target files acquired by a server may be sent by different databases, if one target file is obtained and sent to a terminal once, the target file may not exist in the database, and then the target file may be repeatedly acquired later, so in order to avoid the occurrence of repeated data acquisition, in some embodiments, the plurality of target files are provided, and the sending of the target file to the terminal may be specifically implemented by the following steps: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; when the plurality of target files are uploaded to the data transfer device, the server controls the data transfer device to combine the plurality of temporarily stored target files, and receives a reply file sent by the data transfer device, wherein the reply file is obtained by combining the plurality of target files; the server transmits the reply file to the terminal.
Specifically, the merging target file may be merged according to the size of the target file, if the target file is too large, the file may be compressed first, for example, the merged file is greater than 1024MB and may exceed the maximum file limit that the terminal may receive, and then the reply file may be compressed first, where the compressed file may be 512MB, until the compressed reply file meets the maximum file limit that the terminal may receive, and then the reply file is sent to the terminal.
In the scheme, after the target files are acquired, the plurality of target files can be uploaded to the data transfer equipment first, under the condition that the data requested by the terminal is acquired, the plurality of target files are uploaded to the data transfer equipment, the plurality of target files temporarily stored in the data transfer equipment can be combined into one, the standard reply files are output after the combination, and the reply files are sent to the terminal, so that the files of the requested data can be sent to the terminal at one time in practice without multiple sending, the task of down-grade supply can be efficiently completed, the condition that the terminal side does not need to receive the files for multiple times is ensured, and the experience effect of the terminal side is better.
In order to enable those skilled in the art to more clearly understand the technical solutions of the present application, the implementation procedure of the sql-based data processing method of the present application will be described in detail below with reference to specific embodiments.
The deployment structure of the terminal, the server and the database in the scheme is shown in fig. 3, the terminal sends the query requirement to the master control server, the master control server generates a plurality of slicing tasks according to the query requirement, the plurality of slicing tasks (slicing information comprises a database suffix, a data table suffix and data slicing values, such as custNo: 111) are issued to the application server (the master control server and the application server belong to the same server cluster, or two servers are not required to be set, one server is set to realize all functions of the master control server and the application server), the application server (the application server can also communicate with the terminal and mutually transmit sliced information) has the following application services, the following application services can be used for calling a shell script in the application server, the following application services are used for generating target sql sentences based on an sql template, the function of the shell scripts is to send the target sql sentences to the corresponding database, the database can have a plurality of corresponding data tables, each database has the corresponding data table, the application server is set to store the plurality of data tables, the data tables are correspondingly stored in the corresponding database, the target sql sentences are sent to the master control server and the target file is returned to the master control server, the target file is generated, the target file is returned to the target file is generated, and the target file is returned to the target file is generated.
In the data processing process based on sql in this scheme, as shown in fig. 4, a terminal sends a query requirement to a master control server, then the master control server generates a slicing task and issues the slicing task to an application server, a next application service of the application server obtains the slicing task, the next application service can dynamically generate target sql statements, the personalized generation of target sql statements can be performed by dynamically generating target sql statements before reading (before obtaining target data), by performing post-reading (after obtaining target data, generating target sql statements according to the requirement of the terminal, for example, preprocessing the obtained target data), by performing pre-writing (preprocessing the target data before generating a target file, processing according to the requirement of the terminal, dynamically generating target sql statements), by performing post-writing (processing on the target file after generating the target file, for example, performing personalized processing on the data in the target file), the next application service can be performed by sending target sql statements to a shell (call script), by sending the target sql statements to a service script (call script, sending the target sql statements to a plurality of service devices to a plurality of corresponding service devices, and sending the target data to a corresponding service devices to a plurality of service devices to a service database (which can be associated with each other terminal) to send the target data to a response data to a service device, and can be combined to a plurality of target files (which can be sent to a service device).
The embodiment of the application also provides a server, and it should be noted that the server of the embodiment of the application can be used for executing the data processing method based on sql provided by the embodiment of the application. The device is used for realizing the above embodiments and preferred embodiments, and is not described in detail. As used below, the term "module" may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.
The server provided in the embodiment of the present application is described below.
Fig. 5 is a block diagram of a server according to an embodiment of the present application. As shown in fig. 5, the server includes:
an obtaining unit 10, configured to obtain an sql template, where the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
A receiving unit 20, configured to receive a query requirement sent by a terminal, where the query requirement refers to a requirement for requesting to query and obtain data;
Specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
A first processing unit 30, configured to generate a target sql statement at least according to the sql template and the query requirement, and send the target sql statement to a target database, so that the target database obtains target data based on query data of the target sql statement, where the target database is further configured to send the queried target data to the server, and the server communicates with the target database;
specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
And a second processing unit 40 for generating a target file according to the target data and transmitting the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, a preprocessing interface can be added before the data downshifting and file writing processing process, so that the processing and writing preprocessing can be realized, the processing is performed on the sql statement of the downshifting before the data downshifting, the flexible dynamic downshifting capability of the sql is further realized, and the processing can be performed on the data file before the data file is written (before the target file is generated), so that the individuation processing can be flexibly performed on the downshifting data file.
According to the method, the device and the system, the acquiring unit acquires the sql template, the receiving unit receives the query requirement sent by the terminal, the first processing unit generates a target sql statement at least according to the sql template and the query requirement, the target sql statement is sent to the target database, the target database obtains target data based on query data of the target sql statement, and the second processing unit generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
In some schemes, the number of the downshifts is based on the data export capability of the database, the data export script is relatively simple, the personalized processing capability of the data is not provided, meanwhile, the personalized slicing downshifting capability is not supported when the downshifts are downshifted according to the information of the specific database and the data table, so that the subsequent data downshifting efficiency is lower under the condition that the data quantity is distributed in a scattered manner, the subsequent data downshifting efficiency is improved by performing personalized processing on the sql statement based on the query requirement sent by the terminal, in the specific implementation process, the query requirement at least comprises the configuration parameter of the sql, the configuration parameter is the parameter of the operation command for assigning the sql statement, and the first processing unit comprises an acquisition module, a determination module and a first processing module, wherein the acquisition module is used for acquiring the fields in the target database; the determining module is used for determining a first mapping relation between the configuration parameters and the fields in the target database and determining a second mapping relation between the configuration parameters and the sql statement; the first processing module is used for copying one part of the sql template to obtain a copied sql template, and updating the operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
In the scheme, the query requirement sent by the terminal can comprise the configuration parameters of the sql, but the configuration parameters of the sql sent by the terminal are not complete sql sentences and are not corresponding to the fields in the target database, so that the first mapping relation between the configuration parameters of the sql and the fields in the target database and the second mapping relation between the configuration parameters of the sql and the operation commands in the sql sentences can be determined first, dynamic adjustment is performed on the copied sql template, personalized processing of the sql sentences is realized, and the problem that personalized processing of the sql sentences cannot be performed further is solved.
In order to further accurately determine the first mapping relation and the second mapping relation, so as to ensure that a target sql statement can be generated more efficiently and accurately based on the accurate first mapping relation and the second mapping relation, in a specific implementation process, the determining module comprises a first determining submodule and a second determining submodule, the first determining submodule is used for extracting a query field in the configuration parameter, and determining the first mapping relation between the query field and the field in the target database according to a field mapping table, wherein the query field refers to the range of the screened field, and the field mapping table is a table of mapping relation between the predefined query field and the field in the target database; the second determining submodule is used for extracting the query condition in the configuration parameter and determining the second mapping relation between the query condition and the conditional instruction in the sql statement, wherein the query condition is the condition of the screened data, and the first mapping relation and the second mapping relation form the relation between the query requirement and the target sql statement;
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of the boy is to be queried, and then the corresponding conditional instruction in the sql statement is a sphere instruction.
In the scheme, a first mapping relation between the query field and the field in the target database can be pre-stored in the field mapping table, so that the query field in the configuration parameters can be directly extracted, and the field in the target database corresponding to the query field can be accurately determined by querying in the field mapping table; the configuration parameters actually have query conditions, and the query conditions correspond to the conditional instructions in the sql statement, so that the query conditions are directly converted into the conditional instructions in the corresponding sql, and the conditional instructions of the sql statement corresponding to the query conditions can be accurately determined.
Because the first mapping relation and the second mapping relation determined before are more accurate, the target sql statement can be generated more efficiently and accurately based on the accurate first mapping relation and the second mapping relation, in a specific implementation process, the first processing module includes a first processing sub-module, and the first processing sub-module is used for updating a template field of the sql statement in the duplicate sql template into the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and the server updates a template condition of the sql statement in the duplicate sql template into the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, so as to obtain the target sql statement.
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of a male is to be queried, and then the corresponding conditional instruction in the sql statement is a where instruction, and then according to the determined field and the conditional instruction, the original copied sql template is "select from table name where subject=xx", and the generated target sql statement is "select from score table where subject=language and gender=0".
In the scheme, under the condition that the first mapping relation between the configuration parameters and the fields in the target database is determined and the second mapping relation between the configuration parameters and the sql statement is determined, the fields in the target database corresponding to the query fields in the configuration parameters can be directly determined, the conditional instruction of the sql statement corresponding to the query conditions in the configuration parameters can be directly determined, and further, the copied sql template is updated, so that an accurate target sql statement can be obtained, and further, personalized processing of the sql statement is further realized.
Specifically, a read-before-process interface can be set before data is acquired, so that personalized processing can be performed on sql sentences before data is read, dynamic sql sentence screening is further realized, personalized processing can be performed before a target file is generated, namely before data writing, a write-before-process interface is set, data processing before writing can be supported, and further self-defined data processing capacity can be realized.
In practical applications, the terminal may not only query data alone, but also request service processing according to the queried data, and in order to implement post-read processing on the data individually, in one embodiment of the present application, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement for preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and calculating an average value, wherein the server further comprises a determining unit, an updating unit and a third processing unit, the determining unit is used for determining an operation command of the sql statement corresponding to a preprocessing requirement in the business processing requirement according to the business processing requirement before generating a target file according to the target data, so as to obtain a business sql instruction; the finer unit is used for updating the operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the third processing unit is configured to send the service sql statement to the target database, so that the target database processes the target data and the service sql statement to obtain service data, where the target database is further configured to send the service data to the server.
Specifically, the query field in the configuration parameters is a male with gender, and the field of the male in the corresponding target database is 0, and the field of the female is 1, then the corresponding field of the male is directly determined to be 0; in the query condition, the language score of a male is to be queried, and the corresponding conditional instruction in the sql statement is a where instruction, then according to the determined field and the conditional instruction, the original copied sql template is "select from table name where subject=xx", and the generated target sql statement is "select from table where subject=language and gender=0"; on the basis of the terminal requests statistics of data of all men, then on the basis of the original replication sql template, the generated business sql statement may be "select sun (score) from score table where subject=language and gender=0".
In the scheme, under the condition that the terminal also requests to process the queried data, the queried data can be preprocessed based on the service processing requirement of the terminal, or a service sql statement can be generated, the generated sql statement is updated in a sql template, so that the service sql statement is obtained, and personalized processing of the sql statement in the data read post-processing is further realized.
Because the current data size is larger, when the data of the same logic data table is counted down, the data size of the file executed by the single database is insufficient, and meanwhile, when the logic table with a large data size is processed down, the processing of the output data file of the next file is slower, in order to improve the counting efficiency of the data table, the number of fragments is flexibly configured, the query requirement further comprises a plurality of target table identifiers, the first processing unit comprises an extraction module, a second processing module and a first sending module, and the extraction module is used for extracting a plurality of the target table identifiers in the query requirement; the second processing module is used for establishing connection with a plurality of databases, and determining the deployment position of the target data table corresponding to the target table identifier according to the target table identifier in sequence, wherein the position refers to the target database where the target data table is located; the first sending module is used for sending the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
In the scheme, under the application of the distributed storage databases and the distributed storage data tables, the deployed positions of the different data tables can be different, so that corresponding target sql statements can be sequentially sent to the corresponding target databases according to the deployed positions of the data tables, a plurality of target databases can execute the corresponding target sql statements, the efficiency of data query can be improved through the distributed mode, the slower data query and processing speed caused by larger data storage in one database can be avoided, the efficiency of data slicing can be improved, and the efficiency of the following data tables can be improved.
Specifically, the data can be processed in a concurrent manner according to the slicing mode, slicing downshifting can be performed by default according to the database, multi-slicing downshifting can be performed by default of one database, specific slicing rules can be set individually, thus the downshifting requirements under different scenes can be met, meanwhile, the slicing configuration can be dynamically adjusted according to the data size, and further the data downshifting and counting task can be efficiently completed.
In order to improve the subsequent data downshifting capacity, tasks can be generated based on a database-dividing table to respectively query data from a plurality of databases, so that sql sentences can be dynamically adjusted according to the plurality of databases, and in some embodiments, the first sending module comprises a generating sub-module and a second processing sub-module, the generating sub-module is used for generating a plurality of tasks according to a plurality of target table identifiers, and the number of the tasks is the same as the number of the target table identifiers; the second processing sub-module is used for executing a plurality of tasks in parallel, and sending a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data table is deployed, which correspond to the target table identifiers.
Specifically, the deployment position (i.e. the rule of database and table division) of the corresponding target data table can be determined according to the target table identification, if a plurality of data tables are database and table division, a plurality of tasks can be generated and distributed to execute the tasks, so that the task of the next file can be executed concurrently according to the positions of different data tables, and the task can be executed in the executed database and the specific data segment according to the different positions set by the data tables.
For executing a plurality of tasks, the tasks can be executed concurrently, the tasks can be executed by a worker in a server, specific service execution tasks are adopted, the tasks are randomly distributed to different workers, when the tasks are executed, corresponding target sql sentences are acquired according to the tasks, the designated target sql sentences are executed, and then a simple data file can be exported according to the obtained data.
In the scheme, the method can be realized based on a data distribution scene of a database sub-table, the concurrent processing of the sub-tables can be performed according to the granularity of the database, the tasks can be generated concurrently according to the dimension of the database for one logic table (data table) to process the data of the logic table and all sub-tables under the database, meanwhile, the method can support multi-sub-tables of a single database and formulate a sub-table processing file, can specify the number of the data of a specific sub-table under the database, simultaneously, can support the concurrent number of the sub-tables according to the plurality of sub-tables under the database, and can flexibly configure the number of the sub-tables according to the downstream requirement and the data amount by applying, so that the efficiency of the number of the sub-tables of the data is further improved.
In a practical application scenario, a plurality of target files acquired by a server may be sent by different databases, if one target file is obtained and sent to a terminal once, the target file may not exist in the database, and then the target file may be repeatedly acquired later, so in order to avoid repeatedly acquiring data, in some embodiments, the plurality of target files are provided, the second processing unit includes an uploading module, a third processing module and a second sending module, the uploading module is configured to sequentially upload the plurality of target files to a data transfer device, and the data transfer device is configured to temporarily store the target files; the third processing module is used for controlling the data transfer equipment to combine the temporarily stored target files and receiving a reply file sent by the data transfer equipment under the condition that the target files are uploaded to the data transfer equipment, wherein the reply file is obtained by combining the target files; the second sending module is used for sending the reply file to the terminal.
Specifically, the merging target file may be merged according to the size of the target file, if the target file is too large, the file may be compressed first, for example, the merged file is greater than 1024MB and may exceed the maximum file limit that the terminal may receive, and then the reply file may be compressed first, where the compressed file may be 512MB, until the compressed reply file meets the maximum file limit that the terminal may receive, and then the reply file is sent to the terminal.
In the scheme, after the target files are acquired, the plurality of target files can be uploaded to the data transfer equipment first, under the condition that the data requested by the terminal is acquired, the plurality of target files are uploaded to the data transfer equipment, the plurality of target files temporarily stored in the data transfer equipment can be combined into one, the standard reply files are output after the combination, and the reply files are sent to the terminal, so that the files of the requested data can be sent to the terminal at one time in practice without multiple sending, the task of down-grade supply can be efficiently completed, the condition that the terminal side does not need to receive the files for multiple times is ensured, and the experience effect of the terminal side is better.
The server includes a processor and a memory, the acquisition unit, the receiving unit, the first processing unit, the second processing unit, and the like are stored in the memory as program units, and the processor executes the program units stored in the memory to realize corresponding functions. The modules are all located in the same processor; alternatively, the above modules may be located in different processors in any combination.
The processor includes a kernel, and the kernel fetches the corresponding program unit from the memory. The kernel can be provided with one or more than one, and the sql statement is personalized by adjusting the kernel parameters.
The memory may include volatile memory, random Access Memory (RAM), and/or nonvolatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM), among other forms in computer readable media, the memory including at least one memory chip.
The embodiment of the invention provides a computer readable storage medium, which comprises a stored program, wherein the device where the computer readable storage medium is located is controlled to execute the sql-based data processing method when the program runs.
Specifically, the sql-based data processing method includes:
step S201, a server acquires an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
Step S202, the server receives a query requirement sent by a terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
Step S203, the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on the query data of the target sql statement, wherein the target database is further used for sending the queried target data to the server, and the server communicates with the target database;
specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
In step S204, the server generates a target file according to the target data, and transmits the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, the query requirement at least includes a configuration parameter of sql, where the configuration parameter is a parameter of an operation command for configuring an sql statement, and the server generates a target sql statement at least according to the sql template and the query requirement, and includes: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates an operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
Optionally, the determining, by the server, a first mapping relationship between the configuration parameter and the field in the target database, and determining, by the server, a second mapping relationship between the configuration parameter and the sql statement includes: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement; updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the method comprises the following steps: the server updates a template field of the sql statement in the replication sql template to the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and updates a template condition of the sql statement in the replication sql template to the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, thereby obtaining the target sql statement.
Optionally, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement of preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and averaging, wherein before the server generates the target file according to the target data, the method further comprises: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes the service sql statement based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Optionally, the query requirement further includes a plurality of target table identifiers, and sending the target sql statement to a target database includes: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployments corresponding to the target table identifiers, including: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data tables corresponding to the target table identifiers are deployed.
Optionally, there are a plurality of the target files, and sending the target files to the terminal includes: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; when the plurality of target files are uploaded to the data transfer device, the server controls the data transfer device to combine the plurality of temporarily stored target files, and receives a reply file sent by the data transfer device, wherein the reply file is obtained by combining the plurality of target files; the server transmits the reply file to the terminal.
The embodiment of the invention provides a processor, which is used for running a program, wherein the data processing method based on sql is executed when the program runs.
Specifically, the sql-based data processing method includes:
step S201, a server acquires an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
Step S202, the server receives a query requirement sent by a terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
Step S203, the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on the query data of the target sql statement, wherein the target database is further used for sending the queried target data to the server, and the server communicates with the target database;
Specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
In step S204, the server generates a target file according to the target data, and transmits the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, the query requirement at least includes a configuration parameter of sql, where the configuration parameter is a parameter of an operation command for configuring an sql statement, and the server generates a target sql statement at least according to the sql template and the query requirement, and includes: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates an operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
Optionally, the determining, by the server, a first mapping relationship between the configuration parameter and the field in the target database, and determining, by the server, a second mapping relationship between the configuration parameter and the sql statement includes: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement; updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the method comprises the following steps: the server updates a template field of the sql statement in the replication sql template to the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and updates a template condition of the sql statement in the replication sql template to the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, thereby obtaining the target sql statement.
Optionally, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement of preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and averaging, wherein before the server generates the target file according to the target data, the method further comprises: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes the service sql statement based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Optionally, the query requirement further includes a plurality of target table identifiers, and sending the target sql statement to a target database includes: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployments corresponding to the target table identifiers, including: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data tables corresponding to the target table identifiers are deployed.
Optionally, there are a plurality of the target files, and sending the target files to the terminal includes: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; when the plurality of target files are uploaded to the data transfer device, the server controls the data transfer device to combine the plurality of temporarily stored target files, and receives a reply file sent by the data transfer device, wherein the reply file is obtained by combining the plurality of target files; the server transmits the reply file to the terminal.
The invention also provides an electronic device comprising one or more processors, a memory and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising instructions for performing any of the methods described above.
The embodiment of the invention provides equipment, which comprises a processor, a memory and a program stored in the memory and capable of running on the processor, wherein the processor realizes at least the following steps when executing the program:
step S201, a server acquires an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
Step S202, the server receives a query requirement sent by a terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
Step S203, the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on the query data of the target sql statement, wherein the target database is further used for sending the queried target data to the server, and the server communicates with the target database;
specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
In step S204, the server generates a target file according to the target data, and transmits the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, the query requirement at least includes a configuration parameter of sql, where the configuration parameter is a parameter of an operation command for configuring an sql statement, and the server generates a target sql statement at least according to the sql template and the query requirement, and includes: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates an operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
Optionally, the determining, by the server, a first mapping relationship between the configuration parameter and the field in the target database, and determining, by the server, a second mapping relationship between the configuration parameter and the sql statement includes: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement; updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the method comprises the following steps: the server updates a template field of the sql statement in the replication sql template to the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and updates a template condition of the sql statement in the replication sql template to the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, thereby obtaining the target sql statement.
Optionally, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement of preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and averaging, wherein before the server generates the target file according to the target data, the method further comprises: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes the service sql statement based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Optionally, the query requirement further includes a plurality of target table identifiers, and sending the target sql statement to a target database includes: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployments corresponding to the target table identifiers, including: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data tables corresponding to the target table identifiers are deployed.
Optionally, there are a plurality of the target files, and sending the target files to the terminal includes: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; when the plurality of target files are uploaded to the data transfer device, the server controls the data transfer device to combine the plurality of temporarily stored target files, and receives a reply file sent by the data transfer device, wherein the reply file is obtained by combining the plurality of target files; the server transmits the reply file to the terminal.
The device herein may be a server, PC, PAD, cell phone, etc.
The present application also provides a computer program product adapted to perform a program initialized with at least the following method steps when executed on a data processing device:
step S201, a server acquires an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
specifically, a set of sql templates may be predefined, so that after the sql templates are obtained, the sql statements may be dynamically adjusted based on the sql templates.
Step S202, the server receives a query requirement sent by a terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
specifically, the terminal communicates with the server, and the terminal may be a downstream system of the server, and when the terminal needs to query data, the terminal may send a query requirement to the server, and the server may receive the query requirement sent by the terminal, and then may dynamically adjust the sql statement based on the query requirement.
Step S203, the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on the query data of the target sql statement, wherein the target database is further used for sending the queried target data to the server, and the server communicates with the target database;
Specifically, when the query requirement sent by the terminal is obtained, dynamic adjustment can be performed based on the sql template, so that a corresponding target sql statement can be generated according to the query requirement, personalized processing of the sql statement is realized, and then the required data can be queried by adopting the target sql statement.
In step S204, the server generates a target file according to the target data, and transmits the target file to the terminal.
Specifically, after the target data required by the terminal has been obtained, a corresponding target file may be generated according to the target data, and then the target file is directly sent to the terminal, and the terminal may receive the required data.
Optionally, the query requirement at least includes a configuration parameter of sql, where the configuration parameter is a parameter of an operation command for configuring an sql statement, and the server generates a target sql statement at least according to the sql template and the query requirement, and includes: the server acquires the fields in the target database; the server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement; and the server copies one part of the sql template to obtain a copied sql template, and updates an operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
Optionally, the determining, by the server, a first mapping relationship between the configuration parameter and the field in the target database, and determining, by the server, a second mapping relationship between the configuration parameter and the sql statement includes: the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined; the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement; updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the method comprises the following steps: the server updates a template field of the sql statement in the replication sql template to the field in the target database corresponding to the query field in the configuration parameter according to the first mapping relation, and updates a template condition of the sql statement in the replication sql template to the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation, thereby obtaining the target sql statement.
Optionally, the query requirement further includes a service processing requirement, where the service processing requirement refers to a requirement of preprocessing the target data, and the preprocessing includes at least one of the following: summarizing, screening a maximum value, screening a minimum value, and averaging, wherein before the server generates the target file according to the target data, the method further comprises: the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction; the server updates an operation command of the sql statement of the copy sql template based on the service sql instruction to obtain a service sql statement; the server sends the service sql statement to the target database, so that the target database processes the service sql statement based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
Optionally, the query requirement further includes a plurality of target table identifiers, and sending the target sql statement to a target database includes: the server extracts a plurality of target table identifiers in the query requirement; the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located; and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
Optionally, the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployments corresponding to the target table identifiers, including: the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers; and the server executes a plurality of tasks in parallel, and sends a plurality of target sql sentences to the target database where the target data table is located according to the positions where the target data tables corresponding to the target table identifiers are deployed.
Optionally, there are a plurality of the target files, and sending the target files to the terminal includes: the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files; when the plurality of target files are uploaded to the data transfer device, the server controls the data transfer device to combine the plurality of temporarily stored target files, and receives a reply file sent by the data transfer device, wherein the reply file is obtained by combining the plurality of target files; the server transmits the reply file to the terminal.
It will be appreciated by those skilled in the art that the modules or steps of the invention described above may be implemented in a general purpose computing device, they may be concentrated on a single computing device, or distributed across a network of computing devices, they may be implemented in program code executable by computing devices, so that they may be stored in a storage device for execution by computing devices, and in some cases, the steps shown or described may be performed in a different order than that shown or described herein, or they may be separately fabricated into individual integrated circuit modules, or multiple modules or steps of them may be fabricated into a single integrated circuit module. Thus, the present invention is not limited to any specific combination of hardware and software.
It will be appreciated by those skilled in the art that embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
In one typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include volatile memory in a computer-readable medium, random Access Memory (RAM) and/or nonvolatile memory, etc., such as Read Only Memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.
Computer readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of storage media for a computer include, but are not limited to, phase change memory (PRAM), static Random Access Memory (SRAM), dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), read Only Memory (ROM), electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium, which can be used to store information that can be accessed by a computing device. Computer-readable media, as defined herein, does not include transitory computer-readable media (transmission media), such as modulated data signals and carrier waves.
It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article or apparatus that comprises an element.
From the above description, it can be seen that the above embodiments of the present application achieve the following technical effects:
1) According to the sql-based data processing method, firstly, a server acquires an sql template, then the server receives a query requirement sent by a terminal, then the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database, so that the target database obtains target data based on query data of the target sql statement, and finally the server generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
2) According to the server, the acquiring unit acquires the sql template, the receiving unit receives the query requirement sent by the terminal, the first processing unit generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to the target database, so that the target database obtains target data based on query data of the target sql statement, and the second processing unit generates a target file according to the target data and sends the target file to the terminal. In the scheme, a set of sql templates are predefined, when the query requirement sent by the terminal is received, the sql templates can be subjected to personalized processing on the sql statement based on the query requirement, so that the sql statement can be dynamically adjusted, and the problem that the sql statement cannot be subjected to personalized processing is solved.
The foregoing description is only of the preferred embodiments of the present application and is not intended to limit the same, but rather, various modifications and variations may be made by those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principles of the present application should be included in the protection scope of the present application.
Claims (10)
1. A method for sql-based data processing, comprising:
The method comprises the steps that a server obtains an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
the server receives a query requirement sent by the terminal, wherein the query requirement refers to a requirement for requesting to query and acquire data;
the server generates a target sql statement at least according to the sql template and the query requirement, and sends the target sql statement to a target database so that the target database obtains target data based on query data of the target sql statement, wherein the target database is also used for sending the queried target data to the server, and the server is communicated with the target database;
and the server generates a target file according to the target data and sends the target file to the terminal.
2. The method according to claim 1, wherein the query requirement includes at least a configuration parameter of sql, the configuration parameter being a parameter of an operation command for assigning an sql statement, and the server generating a target sql statement at least according to the sql template and the query requirement includes:
the server acquires the fields in the target database;
The server determines a first mapping relation between the configuration parameters and the fields in the target database, and determines a second mapping relation between the configuration parameters and the sql statement;
and the server copies one part of the sql template to obtain a copied sql template, and updates the operation command of the sql statement in the copied sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement.
3. The method of claim 2, wherein the step of determining the position of the substrate comprises,
the server determining a first mapping relation between the configuration parameters and the fields in the target database, and determining a second mapping relation between the configuration parameters and the sql statement, including:
the server extracts a query field in the configuration parameters, and determines the first mapping relation between the query field and the fields in the target database according to a field mapping table, wherein the query field refers to the range of the screened fields, and the field mapping table is a table of mapping relation between the query field and the fields in the target database, which is predefined;
the server extracts a query condition in the configuration parameters and determines the second mapping relation between the query condition and a conditional instruction in the sql statement, wherein the query condition is a condition of screened data, and the first mapping relation and the second mapping relation form a relation between the query requirement and the target sql statement;
Updating the operation command of the sql statement in the replication sql template according to the first mapping relation and the second mapping relation to obtain the target sql statement, wherein the updating comprises the following steps:
and updating a template field of the sql statement in the copied sql template into the field in the target database corresponding to the query field in the configuration parameter by the server according to the first mapping relation, and updating a template condition of the sql statement in the copied sql template into the conditional instruction in the sql statement corresponding to the query condition in the configuration parameter according to the second mapping relation to obtain the target sql statement by the server.
4. The method of claim 2, wherein the query requirement further includes a business process requirement, the business process requirement being a requirement for preprocessing the target data, the preprocessing including at least one of: summarizing, screening a maximum value, screening a minimum value and averaging, wherein before the server generates the target file according to the target data, the method further comprises the following steps:
the server determines an operation command of the sql statement corresponding to the preprocessing requirement in the service processing requirement according to the service processing requirement to obtain a service sql instruction;
The server updates an operation command of the sql statement of the copied sql template based on the service sql instruction to obtain a service sql statement;
the server sends the service sql statement to the target database, so that the target database processes based on the target data and the service sql statement to obtain service data, wherein the target database is also used for sending the service data to the server.
5. The method of claim 1, wherein the query requirement further comprises a plurality of target table identifications, and wherein sending the target sql statement into a target database comprises:
the server extracts a plurality of target table identifiers in the query requirement;
the server establishes connection with a plurality of databases, and sequentially determines the deployment position of a target data table corresponding to the target table identifier according to the target table identifier, wherein the position refers to the target database where the target data table is located;
and the server sends the target sql statement to the corresponding target database according to the positions of the target data table deployment corresponding to the target table identifiers.
6. The method of claim 5, wherein the server sending the target sql statement into the corresponding target database based on the locations of the target data table deployments corresponding to a plurality of the target table identifications, comprising:
the server generates a plurality of tasks according to a plurality of target table identifiers, wherein the number of the tasks is the same as that of the target table identifiers;
and the server executes a plurality of tasks in parallel, and sends a plurality of target sql statements to the target database where the target data table is located according to the positions of the target data table deployment corresponding to the target table identifiers.
7. The method of claim 1, wherein there are a plurality of the target files, and transmitting the target files to the terminal comprises:
the server sequentially uploads a plurality of target files to data transfer equipment, wherein the data transfer equipment is used for temporarily storing the target files;
the server controls the data transfer equipment to combine the temporarily stored target files under the condition that the target files are uploaded to the data transfer equipment, and receives a reply file sent by the data transfer equipment, wherein the reply file is obtained by combining the target files;
And the server sends the reply file to the terminal.
8. A server, comprising:
the system comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring an sql template, wherein the sql template refers to a template of a predefined structured query sql language;
the receiving unit is used for receiving the query requirement sent by the terminal, wherein the query requirement refers to the requirement of requesting to query and acquire data;
the first processing unit is used for generating a target sql statement at least according to the sql template and the query requirement, and sending the target sql statement to a target database so that the target database obtains target data based on query data of the target sql statement, wherein the target database is also used for sending the queried target data to the server, and the server is communicated with the target database;
and the second processing unit is used for generating a target file according to the target data and sending the target file to the terminal.
9. A computer-readable storage medium, characterized in that the computer-readable storage medium comprises a stored program, wherein the program performs the method of any one of claims 1 to 7.
10. An electronic device, comprising: one or more processors, memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising instructions for performing the method of any of claims 1-7.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202211711889.5A CN116204189A (en) | 2022-12-29 | 2022-12-29 | Data processing method based on sql, server and electronic equipment |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202211711889.5A CN116204189A (en) | 2022-12-29 | 2022-12-29 | Data processing method based on sql, server and electronic equipment |
Publications (1)
Publication Number | Publication Date |
---|---|
CN116204189A true CN116204189A (en) | 2023-06-02 |
Family
ID=86518249
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202211711889.5A Pending CN116204189A (en) | 2022-12-29 | 2022-12-29 | Data processing method based on sql, server and electronic equipment |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN116204189A (en) |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN117056343A (en) * | 2023-10-11 | 2023-11-14 | 湖北华中电力科技开发有限责任公司 | Multi-source data management method and system in power grid field and electronic equipment |
-
2022
- 2022-12-29 CN CN202211711889.5A patent/CN116204189A/en active Pending
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN117056343A (en) * | 2023-10-11 | 2023-11-14 | 湖北华中电力科技开发有限责任公司 | Multi-source data management method and system in power grid field and electronic equipment |
CN117056343B (en) * | 2023-10-11 | 2024-01-23 | 湖北华中电力科技开发有限责任公司 | Multi-source data management method and system in power grid field and electronic equipment |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CN107766132A (en) | Multi-task scheduling method, application server and computer-readable recording medium | |
US11948014B2 (en) | Multi-tenant control plane management on computing platform | |
CN112162915B (en) | Test data generation method, device, equipment and storage medium | |
CN111290828A (en) | Dynamic routing using container orchestration services | |
CN106487553A (en) | The processing method of VNF example, device and VNFM | |
CN114244717B (en) | Configuration method and device of virtual network card resources, computer equipment and medium | |
CN112612452B (en) | API platform realization method, device, equipment and storage medium | |
CN107294750B (en) | Cloud cluster energy self-identification distributed configuration management method and device | |
CN109002560A (en) | Document deriving method, device, computer equipment and computer readable storage medium | |
CN110990482A (en) | Data synchronization method and device between asynchronous databases | |
CN113204368B (en) | Application processing method, server and storage medium | |
CN111966943A (en) | Streaming data distribution method and system | |
CN116204189A (en) | Data processing method based on sql, server and electronic equipment | |
CN110297944A (en) | Distributed XML data processing method and system | |
CN109360106B (en) | Sketch construction method, system, medium and computer system | |
CN105700877A (en) | Application deployment method and apparatus | |
CN116450355A (en) | Multi-cluster model training method, device, equipment and medium | |
CN106850724A (en) | Data push method and device | |
US9965536B2 (en) | Replication description model for data distribution | |
CN106961453A (en) | Service calling method and device based on TOSCA | |
CN110413427B (en) | Subscription data pulling method, device, equipment and storage medium | |
CN112491943A (en) | Data request method, device, storage medium and electronic equipment | |
CN110297945A (en) | Data information processing method and system based on XBRL | |
CN105208095A (en) | Filtering service deployment recommendation device and method | |
CN112637288A (en) | Streaming data distribution method and system |
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 |