CN113326270A - Data storage method, device, equipment and storage medium - Google Patents
Data storage method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN113326270A CN113326270A CN202110734748.4A CN202110734748A CN113326270A CN 113326270 A CN113326270 A CN 113326270A CN 202110734748 A CN202110734748 A CN 202110734748A CN 113326270 A CN113326270 A CN 113326270A
- Authority
- CN
- China
- Prior art keywords
- sql
- execution
- target
- sentences
- data
- 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
- 238000000034 method Methods 0.000 title claims abstract description 67
- 238000013500 data storage Methods 0.000 title claims abstract description 42
- 238000004806 packaging method and process Methods 0.000 claims abstract description 20
- 230000006870 function Effects 0.000 claims description 29
- 238000012545 processing Methods 0.000 claims description 18
- 238000004590 computer program Methods 0.000 claims description 13
- 238000004458 analytical method Methods 0.000 claims description 6
- 238000003672 processing method Methods 0.000 claims description 3
- 230000004931 aggregating effect Effects 0.000 claims 1
- 230000008569 process Effects 0.000 description 24
- 238000006243 chemical reaction Methods 0.000 description 9
- 238000007726 management method Methods 0.000 description 8
- 238000012544 monitoring process Methods 0.000 description 4
- 230000006978 adaptation Effects 0.000 description 3
- 238000004422 calculation algorithm Methods 0.000 description 3
- 238000004140 cleaning Methods 0.000 description 3
- 238000011161 development Methods 0.000 description 3
- 238000012550 audit Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000012423 maintenance Methods 0.000 description 2
- 230000014759 maintenance of location Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000005192 partition Methods 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 238000013475 authorization Methods 0.000 description 1
- 230000006399 behavior Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000007405 data analysis Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 230000003862 health status Effects 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 238000005065 mining Methods 0.000 description 1
- 238000011160 research Methods 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000012954 risk control Methods 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 230000002194 synthesizing effect Effects 0.000 description 1
- 230000000007 visual effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application belongs to the technical field of data storage, and provides a data storage method, a device, equipment and a storage medium, wherein the method comprises the following steps: receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence; according to the grammar structure, packaging all SQL sentences into target sentences of java grammar structure respectively; storing the target sentences in a temporary table created in advance and generating an execution plan of each target sentence; and sending each execution plan to each working node, extracting the execution result of each working node from the temporary table, summarizing the execution result of each working node, and sending the summarized execution result to a user. According to the method and the system, each SQL statement is packaged, the execution plan of each target statement is managed by the temporary table, the purpose of unified storage operation is achieved, independent submission of the SQL statements through a big data platform is avoided, and the operation mode is more efficient and convenient.
Description
Technical Field
The present application relates to the field of data storage technologies, and in particular, to a data storage method, apparatus, device, and storage medium.
Background
With the rapid development of informatization, the resource types and functions of information systems are more and more complete, huge information system application data are generated, the application data are more and more emphasized, deep mining and analysis of the data become a large number of technical means based on mass data analysis, such as industrial deep ploughing business functions, user behavior analysis, hotspot application and the like, the important position of the business data is reflected, and meanwhile, how to effectively store the precious business data also becomes a research hotspot.
In the existing big data platform, a plurality of processing steps are needed to execute a plurality of query statements and finally return a query result when data are stored in a database.
Disclosure of Invention
The present application mainly aims to provide a data storage method, device, apparatus and storage medium, so as to improve data storage efficiency.
In order to achieve the above object, the present application provides a data storage method, which includes the steps of:
receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence;
according to the grammar structure, packaging the SQL sentences into target sentences of java grammar structure respectively;
storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes, and sending the summarized execution results to the user.
Preferably, the step of analyzing the plurality of SQL statements respectively includes:
analyzing the SQL statement, acquiring a keyword in the SQL statement, and determining a syntactic structure of the SQL statement according to the keyword;
the step of respectively encapsulating each SQL statement into a target statement of a java grammar structure comprises the following steps:
packaging each keyword into an intermediate object corresponding to the grammar structure;
and converting each intermediate object into a target statement of the java grammar structure.
Further, after the step of summarizing the execution results of the work nodes and sending the summarized execution results to the user, the method further includes:
receiving a query request of an SQL statement;
extracting SQL grammar to be queried from the query request, and processing the SQL grammar to be queried by adopting a preset rule to obtain a processing result;
determining an index matched with the processing result of the SQL grammar to be inquired from indexes of preset encrypted SQL grammars, and determining a target encrypted SQL grammar;
generating a target SQL statement based on the target encryption SQL grammar;
and determining the target SQL statement as a query result and returning.
Preferably, the step of sending each execution plan to each work node includes:
analyzing the execution plan of each working node to obtain the node name of each working node;
and sending the execution plan of each working node to each working node in a message alignment mode according to the node name, and recording the sending state of each execution plan.
Preferably, the step of analyzing the plurality of SQL statements respectively includes:
judging whether an analytic function which is not supported by a data warehouse tool exists in the plurality of SQL sentences;
if the unsupported analytic function exists, acquiring an SQL statement of the analytic function which is not supported by the data warehouse tool to obtain a target SQL statement, and converting the unsupported analytic function according to a preset processing method to obtain a target analytic function;
and analyzing the target SQL statement by using the target analysis function.
Preferably, the step of summarizing the execution results of the work nodes and then sending the summarized execution results to the user includes:
collecting the execution results of all the working nodes in a data table;
and sending the data table to the user.
Further, after the step of extracting the execution result of each working node from the temporary table, the method further includes:
and inquiring the target statement corresponding to the execution result, and deleting the target statement corresponding to the execution result from the temporary table.
The present application further provides a data storage device, comprising:
the receiving module is used for receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively and determining the syntactic structure of each SQL sentence;
the packaging module is used for respectively packaging the SQL sentences into target sentences of java grammar structures according to the grammar structures;
the generating module is used for storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
the sending module is used for sending each execution plan to each working node so that each working node obtains data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and the summarizing module is used for extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes and then sending the summarized execution results to the user.
The present application further provides a computer device comprising a memory and a processor, the memory storing a computer program, the processor implementing the steps of any of the above methods when executing the computer program.
The present application also provides a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, performs the steps of any of the methods described above.
According to the data storage method, the data storage device, the data storage equipment and the data storage medium, a plurality of SQL sentences sent by a user are received and analyzed respectively, and the syntactic structures of the SQL sentences are determined; packaging each SQL statement into a target statement of a java grammar structure according to the grammar structure so as to be conveniently executed by each working node; then storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into a temporary table; finally, the execution results of all the working nodes are extracted from the temporary table, and the execution results of all the working nodes are gathered and then sent to the user, so that the purpose of unified storage operation is realized by packaging all the SQL sentences and storing the SQL sentences into the temporary table, and the execution plan of all the target sentences is managed by the temporary table, thereby avoiding independent submission of all the SQL sentences through a large data platform, and having more efficient and convenient operation mode; meanwhile, each working node completes data storage, and the execution results of each working node are collected and then sent to a user, so that the processing pressure of the large data platform is reduced.
Drawings
FIG. 1 is a schematic flow chart illustrating a data storage method according to an embodiment of the present application;
FIG. 2 is a block diagram illustrating a data storage device according to an embodiment of the present application;
fig. 3 is a block diagram illustrating a structure of a computer device according to an embodiment of the present application.
The implementation, functional features and advantages of the objectives of the present application will be further explained with reference to the accompanying drawings.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
Referring to fig. 1, the present application provides a data storage method, which uses a big data platform as an execution subject and is used to improve data storage efficiency, wherein in an embodiment, the data storage method includes the following steps:
s11, receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence;
s12, packaging the SQL sentences into target sentences of java grammar structures respectively according to the grammar structures;
s13, storing the target sentences in a temporary table created in advance, and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
s14, sending each execution plan to each working node, so that each working node obtains data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and S15, extracting the execution results of each working node from the temporary table, summarizing the execution results of each working node, and sending the summarized execution results to the user.
As described in step S11, SQL is an abbreviation for Structured Query Language, wherein the text name is Structured Query Language. SQL statements are a database query and programming language for accessing data and querying, updating, and managing relational database systems.
When the big data platform receives a plurality of SQL sentences simultaneously, the plurality of SQL sentences are analyzed respectively to determine the syntactic structure of each SQL sentence, and when the big data platform specifically analyzes, the SQL sentences are preprocessed to realize the standardization of the SQL sentences, for example, blanks before and after the SQL sentences are removed, continuous blank characters in the SQL sentences are replaced by single blanks, the SQL sentences are changed into a lower case form, an end symbol is added after the tail of the SQL sentences, and the like, then the SQL sentences are divided into sentence blocks, and syntactic structural description information in the sentence blocks is extracted to determine the syntactic structure of the SQL sentences.
As described in the step S12, according to the syntax structure, the groovy script is called to encapsulate each SQL statement into a target statement of a java syntax structure, so as to store the target statement in the database. The database is a clickhouse database, Groovy is an agile development language based on JVM (Java virtual machine), the database combines many strong characteristics of Python, Ruby and Smalltalk, and Groovy codes can be well combined with Java codes and can also be used for expanding existing codes. Due to its nature of running on a JVM, Groovy may also use libraries written in other non-Java languages.
As described in step S13, in this step, the pre-constructed temporary table is used to manage the execution process of each SQL statement, the temporary table in the process needs to be registered in the context and becomes effective during execution, and the table name ends with the task ID and also contains the information of the retention period. The temporary table can be deleted by using a drop statement in the storage process, and can also be deleted by a temporary table cleaning process guarded by a background.
Further, the temporary table is one created in the database, in which the temporary table is created for storing each execution plan. The execution plan in the database refers to a description of an execution process of an SQL (Structured query language) statement. Each time the database server executes an SQL statement sent by the client, the database server needs to analyze the SQL statement and generate a corresponding execution plan, and complete data storage according to the execution plan.
Creating a temporary table in the database refers to creating a temporary table connected with the performance table v $ SQL and the performance table v $ SQL _ plan in the database, and storing the SQL instructions in the performance table v $ SQL and the corresponding execution plan in the performance table v $ SQL _ plan so as to prolong the storage time of the SQL instructions and the corresponding SQL execution plan.
As described in step S14, the temporary table may make an execution time for each execution plan, send each execution plan to each work node according to the execution time, so that each work node obtains data to be stored according to a target statement in the execution plan when receiving the execution plan, store the data in a corresponding database, that is, each work node corresponds to one database, and generate an execution result to store in the temporary table. The execution result is a data storage result of each working node, such as whether data is successfully stored, storage duration, storage location, and the like. Preferably, after the storage process is completed, a result table can be constructed to store the final result, and the query statistics are provided to the outside finally, and can also be used for data synchronization to hive.
As described in step S15, in this step, after the execution results of the respective work nodes are obtained, the execution results of the respective work nodes are collected and sent to the user, so that the user can view the storage state of the data.
According to the data storage method provided by the application, a plurality of SQL sentences sent by a user are received and analyzed respectively, and the syntactic structure of each SQL sentence is determined; packaging each SQL statement into a target statement of a java grammar structure according to the grammar structure so as to be conveniently executed by each working node; then storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into a temporary table; finally, the execution results of all the working nodes are extracted from the temporary table, and the execution results of all the working nodes are gathered and then sent to the user, so that the purpose of unified storage operation is realized by packaging all the SQL sentences and storing the SQL sentences into the temporary table, and the execution plan of all the target sentences is managed by the temporary table, thereby avoiding independent submission of all the SQL sentences through a large data platform, and having more efficient and convenient operation mode; meanwhile, each working node completes data storage, and the execution results of each working node are collected and then sent to a user, so that the processing pressure of the large data platform is reduced.
In an embodiment, in step S11, the step of analyzing the plurality of SQL statements respectively may specifically include:
s111, analyzing the SQL statement, acquiring a keyword in the SQL statement, and determining a syntax structure of the SQL statement according to the keyword;
at this time, in step S12, the step of encapsulating each SQL statement into a target statement of a java grammar structure may specifically include:
s121, packaging each keyword into an intermediate object corresponding to the grammar structure;
and S122, converting each intermediate object into a target statement of the java grammar structure.
In the specific implementation process, the SQL statement can be analyzed to obtain keywords in the SQL statement, the syntactic structure of the SQL statement is determined according to the keywords, each keyword is packaged into an intermediate object, and the intermediate object is converted into a target statement of a java syntactic structure.
In an embodiment, in step S11, the parsing the plurality of SQL statements respectively includes:
a111, judging whether an analytic function which is not supported by a data warehouse tool exists in the plurality of SQL sentences;
a112, if the unsupported analytic function exists, acquiring an SQL statement of the analytic function which is not supported by the data warehouse tool to obtain a target SQL statement, and converting the unsupported analytic function according to a preset processing method to obtain a target analytic function;
and A113, analyzing the target SQL statement by using the target analysis function.
In a large data platform, some parsing functions are not supported in SQL, such as a date conversion function to _ date, a data conversion function int, and a character conversion function to _ char, and need to be converted into the parsing functions supported in SQL.
In the process of analyzing the plurality of SQL sentences respectively, differences which do not conform to the SQL sentences need to be found out, and then the differences are replaced based on a preset conversion rule. Specifically, whether unsupported operation commands, namely updating operation, deleting operation and submitting operation, exist in the SQL statement is judged, then conversion is performed according to preset corresponding operation rules, whether analytical functions which are not supported by the data warehouse tool hive exist in the SQL statement is further judged, such as date conversion, data conversion, character conversion and the like, and then the functions are converted according to preset conversion rules.
In an embodiment, after the step of summarizing the execution results of the work nodes and sending the summarized execution results to the user, the method may further include:
receiving a query request of an SQL statement;
extracting SQL grammar to be queried from the query request, and processing the SQL grammar to be queried by adopting a preset rule to obtain a processing result;
determining an index matched with the processing result of the SQL grammar to be inquired from indexes of preset encrypted SQL grammars, and determining a target encrypted SQL grammar;
generating a target SQL statement based on the target encryption SQL grammar;
and determining the target SQL statement as a query result and returning.
In this embodiment, if the query request of the SQL statement includes the to-be-queried SQL statement, the to-be-queried SQL statement may include the to-be-queried SQL syntax and the to-be-queried SQL parameter, and the query result of the to-be-queried SQL statement may be obtained by synthesizing the query modes of the encrypted SQL parameter and the encrypted SQL syntax. Specifically, receiving a query request of an SQL statement; in response to determining that the query request comprises the SQL statement to be queried, extracting the SQL grammar to be queried and the SQL parameter to be queried from the SQL statement to be queried; processing the SQL grammar to be queried and the SQL parameters to be queried by adopting a preset rule; respectively determining indexes matched with the processing result of the SQL grammar to be queried and the processing result of the SQL parameter to be queried from the indexes of the encrypted SQL grammars and the indexes of the encrypted SQL parameters, and determining a target encrypted SQL grammar and a target encrypted SQL parameter; generating a target SQL statement based on the target encryption SQL grammar and the target encryption SQL parameters; and determining the target SQL statement as a query result and returning.
In an embodiment, in step S14, the step of sending each execution plan to each work node may specifically include:
s141, analyzing the execution plan of each working node to obtain the node name of each working node;
and S142, sending the execution plan of each working node to each working node in a message alignment mode according to the node name, and recording the sending state of each execution plan.
In this embodiment, the big data platform searches for the nodeid value in the working node connected to the big data platform, and sends a piece of data corresponding to the node in the execution plan to the corresponding working node in a message alignment manner. After each working node successfully sends, the status is marked as 1, which indicates that the sending is successful.
In an embodiment, in step S15, the step of summarizing the execution results of the work nodes and sending the summarized execution results to the user may specifically include:
s151, collecting the execution results of all the working nodes in a data table;
s152, the data table is sent to the user.
Specifically, the SQL statements are executed in the multi-library multi-table, a result set of multiple data is obtained, the obtained result sets are merged and compiled into a data table for output. For example, new query statements are respectively executed in the clickhouse database a and the clickhouse database b to obtain result sets of a plurality of data, the result sets are combined and are collected into one data table to be output, and a user can operate the data table as a single library table, so that the data table is convenient to use.
In an embodiment, when the query statement is analyzed, a field with a sorting condition is obtained, the field with the sorting condition is obtained, a new query statement is executed according to the sorting condition, a plurality of obtained result sets completing data sorting are sorted, and then a merging sorting algorithm is adopted for sorting to generate a list for output. In the embodiment, the result sets of the obtained multiple data are sorted, and then the list is generated and output. The operation of the user is simple and convenient.
In an embodiment, in step S14, after the step of extracting the execution result of each work node from the temporary table, the method may further include:
and inquiring the target statement corresponding to the execution result, and deleting the target statement corresponding to the execution result from the temporary table.
In this embodiment, when the storage process is finished, the data of the partition where the task is located is cleaned, or the data is left to the background temporary partition cleaning process to be deleted, that is, the execution is to be completed, and the target statement that obtains the execution result is deleted from the temporary table, so as to avoid occupying the storage space.
Specifically, after the execution result in the temporary table is sent to the user, in order to reduce the storage pressure of the database, the target statement corresponding to the execution result that has been sent to the user and is stored in the temporary table may be deleted, so that the data related to the execution plan is stored after being scanned in the next time of the timed task execution, and the storage pressure of the database may be released in time.
In one embodiment, data such as execution results, temporary tables and the like of each working node may be stored in nodes of a blockchain network, and a data storage method as described above is implemented in the blockchain network.
As described above, the blockchain is a novel application mode of computer technologies such as distributed data storage, point-to-point transmission, consensus mechanism, and encryption algorithm. A block chain (Blockchain), which is essentially a decentralized database, is a series of data blocks associated by using a cryptographic method, and each data block contains information of a batch of network transactions, so as to verify the validity (anti-counterfeiting) of the information and generate a next block. The blockchain may include a blockchain underlying platform, a platform product services layer, and an application services layer.
The block chain underlying platform can comprise processing modules such as user management, basic service, intelligent contract and operation monitoring. The user management module is responsible for identity information management of all blockchain participants, and comprises public and private key generation maintenance (account management), key management, user real identity and blockchain address corresponding relation maintenance (authority management) and the like, and under the authorization condition, the user management module supervises and audits the transaction condition of certain real identities and provides rule configuration (wind control audit) of risk control; the basic service module is deployed on all block chain node equipment and used for verifying the validity of the service request, recording the service request to storage after consensus on the valid request is completed, for a new service request, the basic service firstly performs interface adaptation analysis and authentication processing (interface adaptation), then encrypts service information (consensus management) through a consensus algorithm, transmits the service information to a shared account (network communication) completely and consistently after encryption, and performs recording and storage; the intelligent contract module is responsible for registering and issuing contracts, triggering the contracts and executing the contracts, developers can define contract logics through a certain programming language, issue the contract logics to a block chain (contract registration), call keys or other event triggering and executing according to the logics of contract clauses, complete the contract logics and simultaneously provide the function of upgrading and canceling the contracts; the operation monitoring module is mainly responsible for deployment, configuration modification, contract setting, cloud adaptation in the product release process and visual output of real-time states in product operation, such as: alarm, monitoring network conditions, monitoring node equipment health status, and the like.
Referring to fig. 2, an embodiment of the present application further provides a data storage device, including:
the receiving module 11 is configured to receive a plurality of SQL statements sent by a user, analyze the plurality of SQL statements respectively, and determine a syntax structure of each SQL statement;
the packaging module 12 is configured to package each SQL statement into a target statement of a java grammar structure according to the grammar structure;
a generating module 13, configured to store the target statements in a temporary table created in advance, and generate an execution plan of each target statement; wherein the temporary table is used for managing each execution plan;
a sending module 14, configured to send each execution plan to each working node, so that when each working node receives the execution plan, data is obtained according to a target sentence in the execution plan, the data is stored in a corresponding database, and an execution result is generated and stored in the temporary table;
and the summarizing module 15 is configured to extract the execution results of the work nodes from the temporary table, summarize the execution results of the work nodes, and send the summarized execution results to the user.
SQL is short for Structured Query Language, the name of which is Structured Query Language. SQL statements are a database query and programming language for accessing data and querying, updating, and managing relational database systems.
When the big data platform receives a plurality of SQL sentences simultaneously, the plurality of SQL sentences are analyzed respectively to determine the syntactic structure of each SQL sentence, and when the big data platform specifically analyzes, the SQL sentences are preprocessed to realize the standardization of the SQL sentences, for example, blanks before and after the SQL sentences are removed, continuous blank characters in the SQL sentences are replaced by single blanks, the SQL sentences are changed into a lower case form, an end symbol is added after the tail of the SQL sentences, and the like, then the SQL sentences are divided into sentence blocks, and syntactic structural description information in the sentence blocks is extracted to determine the syntactic structure of the SQL sentences.
And calling a groovy script to respectively package the SQL sentences into target sentences of a java grammar structure according to the grammar structure so as to store the target sentences into a database. The database is a clickhouse database, Groovy is an agile development language based on JVM (Java virtual machine), the database combines many strong characteristics of Python, Ruby and Smalltalk, and Groovy codes can be well combined with Java codes and can also be used for expanding existing codes. Due to its nature of running on a JVM, Groovy may also use libraries written in other non-Java languages.
In addition, the execution process of each SQL statement can be managed by utilizing a pre-constructed temporary table, the temporary table in the process needs to be registered in the context and takes effect in the execution process, and the table name is ended by adopting the task ID and also contains the information of the retention period. The temporary table can be deleted by using a drop statement in the storage process, and can also be deleted by a temporary table cleaning process guarded by a background.
The temporary table is a data table created in the database, and the temporary table is created in the database and used for storing each execution plan. The execution plan in the database refers to a description of an execution process of an SQL (Structured query language) statement. Each time the database server executes an SQL statement sent by the client, the database server needs to analyze the SQL statement and generate a corresponding execution plan, and complete data storage according to the execution plan.
Creating a temporary table in the database refers to creating a temporary table connected with the performance table v $ SQL and the performance table v $ SQL _ plan in the database, and storing the SQL instructions in the performance table v $ SQL and the corresponding execution plan in the performance table v $ SQL _ plan so as to prolong the storage time of the SQL instructions and the corresponding SQL execution plan.
The temporary table may make an execution time for each execution plan, and send each execution plan to each working node according to the execution time, so that each working node obtains data to be stored according to the execution plan when receiving the execution plan, stores the data in a corresponding database, that is, each working node corresponds to one database, and generates an execution result to store in the temporary table. The execution result is a data storage result of each working node, such as whether data is successfully stored, storage duration, storage location, and the like. . Preferably, after the storage process is completed, a result table can be constructed to store the final result, and the query statistics are provided to the outside finally, and can also be used for data synchronization to hive.
After the execution results of the working nodes are obtained, the execution results of the working nodes are collected and sent to a user, so that the user can check the storage state of the data.
As described above, it can be understood that each component of the data storage device provided in the present application may implement the function of any one of the data storage methods described above, and the detailed structure is not described again.
Referring to fig. 3, an embodiment of the present application further provides a computer device, and an internal structure of the computer device may be as shown in fig. 3. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the computer designed processor is used to provide computational and control capabilities. The memory of the computer device comprises a storage medium and an internal memory. The storage medium stores an operating system, a computer program, and a database. The memory provides an environment for the operation of the operating system and computer programs in the storage medium. The database of the computer device is used for storing data such as temporary tables and execution results. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement a data storage method.
The processor executes the data storage method, and the method comprises the following steps:
receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence;
according to the grammar structure, packaging the SQL sentences into target sentences of java grammar structure respectively;
storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes, and sending the summarized execution results to the user.
An embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored, and the computer program, when executed by a processor, implements a data storage method, including the steps of:
receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence;
according to the grammar structure, packaging the SQL sentences into target sentences of java grammar structure respectively;
storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes, and sending the summarized execution results to the user.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and can include the processes of the embodiments of the methods described above when the computer program is executed. Any reference to memory, storage, database, or other medium provided herein and used in the examples may include non-volatile and/or volatile memory. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), double-rate SDRAM (SSRSDRAM), Enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and bus dynamic RAM (RDRAM).
To sum up, the most beneficial effect of this application lies in:
according to the data storage method, the data storage device, the data storage equipment and the data storage medium, a plurality of SQL sentences sent by a user are received and analyzed respectively, and the syntactic structures of the SQL sentences are determined; packaging each SQL statement into a target statement of a java grammar structure according to the grammar structure so as to be conveniently executed by each working node; then storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into a temporary table; finally, the execution results of all the working nodes are extracted from the temporary table, and the execution results of all the working nodes are gathered and then sent to the user, so that the purpose of unified storage operation is realized by packaging all the SQL sentences and storing the SQL sentences into the temporary table, and the execution plan of all the target sentences is managed by the temporary table, thereby avoiding independent submission of all the SQL sentences through a large data platform, and having more efficient and convenient operation mode; meanwhile, each working node completes data storage, and the execution results of each working node are collected and then sent to a user, so that the processing pressure of the large data platform is reduced.
It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, apparatus, article, or method 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, apparatus, article, or method. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, apparatus, article, or method that includes the element.
The above description is only a preferred embodiment of the present application, and not intended to limit the scope of the present application, and all modifications of equivalent structures and equivalent processes, which are made by the contents of the specification and the drawings of the present application, or which are directly or indirectly applied to other related technical fields, are also included in the scope of the present application.
Claims (10)
1. A method of storing data, comprising the steps of:
receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively, and determining the syntactic structure of each SQL sentence;
according to the grammar structure, packaging the SQL sentences into target sentences of java grammar structure respectively;
storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
sending each execution plan to each working node, so that each working node acquires data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes, and sending the summarized execution results to the user.
2. The method according to claim 1, wherein the step of parsing the plurality of SQL statements respectively comprises:
analyzing the SQL statement, acquiring a keyword in the SQL statement, and determining a syntactic structure of the SQL statement according to the keyword;
the step of respectively encapsulating each SQL statement into a target statement of a java grammar structure comprises the following steps:
packaging each keyword into an intermediate object corresponding to the grammar structure;
and converting each intermediate object into a target statement of the java grammar structure.
3. The method according to claim 1, wherein after the step of summarizing the execution results of the work nodes and sending the summarized execution results to the user, the method further comprises:
receiving a query request of an SQL statement;
extracting SQL grammar to be queried from the query request, and processing the SQL grammar to be queried by adopting a preset rule to obtain a processing result;
determining an index matched with the processing result of the SQL grammar to be inquired from indexes of preset encrypted SQL grammars, and determining a target encrypted SQL grammar;
generating a target SQL statement based on the target encryption SQL grammar;
and determining the target SQL statement as a query result and returning.
4. The method of claim 1, wherein the step of sending each execution plan to each worker node comprises:
analyzing the execution plan of each working node to obtain the node name of each working node;
and sending the execution plan of each working node to each working node in a message alignment mode according to the node name, and recording the sending state of each execution plan.
5. The method according to claim 1, wherein the step of parsing the plurality of SQL statements respectively comprises:
judging whether an analytic function which is not supported by a data warehouse tool exists in the plurality of SQL sentences;
if the unsupported analytic function exists, acquiring an SQL statement of the analytic function which is not supported by the data warehouse tool to obtain a target SQL statement, and converting the unsupported analytic function according to a preset processing method to obtain a target analytic function;
and analyzing the target SQL statement by using the target analysis function.
6. The method according to claim 1, wherein the step of aggregating the execution results of the work nodes and sending the aggregated results to the user comprises:
collecting the execution results of all the working nodes in a data table;
and sending the data table to the user.
7. The method of claim 1, wherein after the step of extracting the execution result of each working node from the temporary table, the method further comprises:
and inquiring the target statement corresponding to the execution result, and deleting the target statement corresponding to the execution result from the temporary table.
8. A data storage device, comprising:
the receiving module is used for receiving a plurality of SQL sentences sent by a user, analyzing the plurality of SQL sentences respectively and determining the syntactic structure of each SQL sentence;
the packaging module is used for respectively packaging the SQL sentences into target sentences of java grammar structures according to the grammar structures;
the generating module is used for storing the target sentences in a pre-created temporary table and generating an execution plan of each target sentence; wherein the temporary table is used for managing each execution plan;
the sending module is used for sending each execution plan to each working node so that each working node obtains data according to the execution plan when receiving the execution plan, stores the data into a corresponding database, generates an execution result and stores the execution result into the temporary table;
and the summarizing module is used for extracting the execution results of all the working nodes from the temporary table, summarizing the execution results of all the working nodes and then sending the summarized execution results to the user.
9. A computer device, comprising:
a processor;
a memory;
the memory stores a computer program which, when executed by the processor, implements a data storage method as claimed in any one of claims 1 to 7.
10. A computer-readable storage medium, characterized in that a computer program is stored on the computer-readable storage medium, which computer program, when being executed by a processor, carries out the data storage method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110734748.4A CN113326270A (en) | 2021-06-30 | 2021-06-30 | Data storage method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110734748.4A CN113326270A (en) | 2021-06-30 | 2021-06-30 | Data storage method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113326270A true CN113326270A (en) | 2021-08-31 |
Family
ID=77423655
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110734748.4A Pending CN113326270A (en) | 2021-06-30 | 2021-06-30 | Data storage method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113326270A (en) |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US5930795A (en) * | 1997-01-21 | 1999-07-27 | International Business Machines Corporation | Supporting dynamic tables in SQL query compilers |
| CN104133891A (en) * | 2014-07-30 | 2014-11-05 | 广州科腾信息技术有限公司 | Method for storing massive structural data based on relational database |
| CN108460079A (en) * | 2018-01-08 | 2018-08-28 | 平安科技（深圳）有限公司 | database monitoring method, device, terminal device and storage medium |
| CN109408493A (en) * | 2018-10-17 | 2019-03-01 | 成都四方伟业软件股份有限公司 | A kind of moving method and system of data source |
| CN110019308A (en) * | 2017-12-28 | 2019-07-16 | 中国移动通信集团海南有限公司 | Data query method, apparatus, equipment and storage medium |
| CN111782678A (en) * | 2020-05-29 | 2020-10-16 | 北京沃东天骏信息技术有限公司 | Data storage method and device |
-
2021
- 2021-06-30 CN CN202110734748.4A patent/CN113326270A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US5930795A (en) * | 1997-01-21 | 1999-07-27 | International Business Machines Corporation | Supporting dynamic tables in SQL query compilers |
| CN104133891A (en) * | 2014-07-30 | 2014-11-05 | 广州科腾信息技术有限公司 | Method for storing massive structural data based on relational database |
| CN110019308A (en) * | 2017-12-28 | 2019-07-16 | 中国移动通信集团海南有限公司 | Data query method, apparatus, equipment and storage medium |
| CN108460079A (en) * | 2018-01-08 | 2018-08-28 | 平安科技（深圳）有限公司 | database monitoring method, device, terminal device and storage medium |
| CN109408493A (en) * | 2018-10-17 | 2019-03-01 | 成都四方伟业软件股份有限公司 | A kind of moving method and system of data source |
| CN111782678A (en) * | 2020-05-29 | 2020-10-16 | 北京沃东天骏信息技术有限公司 | Data storage method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112540811B (en) | Cache data detection method and device, computer equipment and storage medium | |
| CN112540923B (en) | Interface parameter checking and converting method, device, equipment and storage medium | |
| CN112380286B (en) | Method, device, equipment and medium for generating data object relation map of database | |
| CN113127519B (en) | File query method and device, computer equipment and storage medium | |
| CN104717085A (en) | Log parsing method and device | |
| CN112613067A (en) | User behavior data acquisition method and device, computer equipment and storage medium | |
| CN111880921A (en) | Job processing method and device based on rule engine and computer equipment | |
| CN112527421A (en) | Service calling method, device, equipment and storage medium | |
| CN112860727A (en) | Data query method, device, equipment and medium based on big data query engine | |
| CN115794839B (en) | Data collection method based on Php+Mysql system, computer equipment and storage medium | |
| CN112017007A (en) | User behavior data processing method and device, computer equipment and storage medium | |
| CN113468172A (en) | Logistics service data processing method, device, equipment and storage medium | |
| CN113791586A (en) | Novel industrial APP and identification registration analysis integration method | |
| CN114218315A (en) | Interface generation method and device, computer equipment and storage medium | |
| CN111898036A (en) | Behavior data collecting and processing system and method | |
| KR102189127B1 (en) | A unit and method for processing rule based action | |
| CN113377350B (en) | Access request processing method, device, equipment and readable storage medium | |
| CN112507010B (en) | Service data processing method and device, computer equipment and storage medium | |
| CN113326270A (en) | Data storage method, device, equipment and storage medium | |
| CN112633761A (en) | Index data query method, device, equipment and storage medium | |
| CN106201542B (en) | WOF rapid development platform | |
| CN108717438B (en) | Chained data state acquisition system and method | |
| CN112069031B (en) | Abnormality query method, device, equipment and computer readable storage medium | |
| CN112667469A (en) | Method, system and readable medium for automatically generating diversified big data statistical report | |
| CN114116268A (en) | Method and device for checking Flink SQL statement, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |