CN110781205A - JDBC-based database direct-checking method, device and system - Google Patents
JDBC-based database direct-checking method, device and system Download PDFInfo
- Publication number
- CN110781205A CN110781205A CN201910864709.9A CN201910864709A CN110781205A CN 110781205 A CN110781205 A CN 110781205A CN 201910864709 A CN201910864709 A CN 201910864709A CN 110781205 A CN110781205 A CN 110781205A
- Authority
- CN
- China
- Prior art keywords
- database
- jdbc
- data
- analysis platform
- data analysis
- 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
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a JDBC-based database direct-checking method, a JDBC-based database direct-checking device and a JDBC-based database direct-checking system. The system can dynamically generate sql sentences adaptive to each relational database according to a data source and a processing node configured by a user, then the system submits the sql sentences to the databases in a jdbc form, the sql sentences are analyzed by the databases, data are processed and returned, memory consumption generated in an intermediate process is far lower than that of a current memory calculation type query engine, and efficiency is often multiple times that of the memory calculation type query engine. The problem that Presto and Impala can efficiently and quickly search big data products such as Hive and HBase, but the efficiency of the traditional relational database is low is solved.
Description
Technical Field
The invention relates to the field of databases, in particular to a database direct-checking method, device and system based on JDBC.
Background
A database is a repository where data is stored. The storage space is large, and millions, millions and hundreds of millions of data can be stored. However, the database does not store data randomly, and has certain rules, otherwise, the query efficiency is low. The world today is an internet world that is full of data, which is flooded with large amounts of data. I.e. the internet world is the data world. The sources of data are many, such as travel records, consumption records, web pages viewed, messages sent, and so forth. In addition to text type data, images, music, and sounds are data.
In the development history of databases, databases are developed in various stages such as hierarchical databases, mesh databases and relational databases, and database technologies are rapidly developed in various aspects. Particularly, the relational database has become the most important member of the database products at present, and since the 80 s, almost all database products newly produced by database manufacturers support the relational database, and even some non-relational database products almost have interfaces supporting the relational database. The problem of managing and storing the relational data can be well solved by the traditional relational database.
Existing systems are as described in application number: CN201710009824.9 entitled an ad hoc query method and system for statistical data, which adopts Presto, Impala, etc. (memory computing type) to perform query processing on a data analysis system, Presto, Impala can efficiently and quickly retrieve large data products such as Hive, HBase, etc., but has low efficiency on the traditional relational database.
Disclosure of Invention
The invention aims to: the JDBC-based database direct-checking method, device and system solve the problems that the conventional system adopts Presto, Impala and the like to perform query processing on a data analysis system, the Presto and Impala can efficiently and quickly search large data products such as Hive and HBase, but the efficiency of the conventional relational database is low.
The technical scheme adopted by the invention is as follows:
a JDBC-based database direct-checking method comprises a database and a data analysis platform, and further comprises the following steps:
s1, operating the data source by the user through the data analysis platform;
s2, generating a corresponding SQL statement by the data analysis platform according to the operation of the user;
s3, submitting the SQL statement generated in the step S2 to a database by the data analysis platform;
and S4, the database analyzes the SQL statements submitted by the data analysis platform, and returns the processed data to the data analysis platform.
The method is different from the existing method for querying data by using a Presto engine, saves Presto clusters and operating the Presto engine, reduces server load, has a JDBC-based data analysis platform data direct-checking function, generates different database SQL in the process of operating a data source by using a data analysis platform, submits the SQL to a client database by the platform in a JDBC mode, and realizes the capacity of reducing memory occupation and quickly retrieving data of an application layer by using the processing capacity of the database to good SQL.
Further, in step S1, the user operates the data source in the data set through the data processing node through the data analysis platform.
Further, in step S2, the data analysis platform generates a corresponding SQL dialect statement according to the operation of the user.
Further, the generating of the SQL dialect statement comprises the following steps:
s5, pre-recording a database type and an SQL dialect matched with the database type on the data analysis platform;
s6, when the user operates the data source through the data analysis platform in the step S1, the data analysis platform can judge the type of the database to be inquired according to the data source;
and S7, the data analysis platform calls the SQL dialect matched with the database type according to the database type to be queried to generate a corresponding SQL dialect statement.
By adopting the method, because the types of the databases are more, the corresponding SQL dialects also comprise dialects such as mysql, hivesql, sparksql, oracle and the like, and because the scheme omits Presto cluster and directly sends the corresponding databases to query by the data analysis platform, the data analysis platform needs to generate the SQL dialects matched with the databases, the scheme adopts the scheme to generate the SQL dialects matched with the databases, firstly, all database types specified by customers or existing on the data analysis platform are input, and because the SQL sentences are structured query languages and comprise 6 query languages of a data query language (L), a data operation language (DML), a Transaction Control Language (TCL), a Data Control Language (DCL), a Data Definition Language (DDL) and a pointer control language (CCL), 6 query languages matched with the databases are also input on the data analysis platform, in step S1, when the user operates the data source through the data analysis platform, the data analysis platform can determine the type of the database that needs to be queried according to the data source, and then the data analysis platform invokes 6 types of query languages matching the type of the database according to the type of the database that needs to be queried and the operation of the user to generate a corresponding SQL dialect statement.
Further, in step S3, the data analysis platform submits the SQL statement generated in step S2 to the database in JDBC form.
A JDBC-based database direct-checking device comprises:
a memory for storing executable instructions;
and the multi-core processor is used for executing the executable instructions stored in the memory and is loaded with a data analysis platform to realize the JDBC-based database direct-checking method.
Furthermore, the system also comprises a display device for displaying the data returned by the database.
A JDBC-based database direct-checking system comprises the database direct-checking device and a device loaded with the database.
In summary, due to the adoption of the technical scheme, the invention has the beneficial effects that:
1. according to the JDBC-based database direct-checking method, device and system, the number of the query engine servers can be reduced, in the past, in order to improve the query efficiency and increase the query memory, a plurality of servers are used for presto clustering, the consumption of the application memory is reduced, the query efficiency is improved, and the user perception is improved;
2. the invention discloses a JDBC-based database direct-checking method, a JDBC-based database direct-checking device and a JDBC-based database direct-checking system, which solve the problems that the conventional system adopts Presto, Impala and the like to perform query processing on a data analysis system, the Presto and the Impala can efficiently and quickly search large data products such as Hive, HBase and the like, but the efficiency of the conventional relational database is low.
3. Compared with the existing scheme, the JDBC-based database direct-checking method, the JDBC-based database direct-checking device and the JDBC-based database direct-checking system save system resources, simplify steps and accelerate query speed.
Drawings
The invention will now be described, by way of example, with reference to the accompanying drawings, in which:
FIG. 1 is a schematic of the computational flow of the present invention;
FIG. 2 is a schematic diagram of a prior art database query computation flow;
fig. 3 is a comparison graph of query time and system resource occupation compared with the prior art.
Detailed Description
All of the features disclosed in this specification, or all of the steps in any method or process so disclosed, may be combined in any combination, except combinations of features and/or steps that are mutually exclusive.
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention will be further described in detail with reference to fig. 1 to 3, the described embodiments should not be construed as limiting the present invention, and all other embodiments obtained by a person of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. The terminology used herein is for the purpose of describing embodiments of the invention only and is not intended to be limiting of the invention.
Before further detailed description of the embodiments of the present invention, terms and expressions mentioned in the embodiments of the present invention are explained, and the terms and expressions mentioned in the embodiments of the present invention are applied to the following explanations.
JDBC: the Java API is used for executing SQL statements, can provide uniform access for a plurality of relational databases, and consists of a group of classes and interfaces written by Java language. JDBC provides a benchmark by which more advanced tools and interfaces can be built to enable database developers to write database applications.
SQL: structured Query Language (SQL), a special purpose programming Language, is a database Query and programming Language for accessing data and querying, updating, and managing relational database systems.
A data source: the file and the database are the general names of data sources.
The data processing node: the data processing method is a subdivision node of a business intelligent analysis platform for data processing functions, wherein the data processing functions comprise functions of table association, field coming, data filtering, field calculation, grouping statistics, data types and the like, and the data processing method mainly comprises a series of processing functions of cleaning, filtering, splitting and the like of data on a data source
Data set (dataset): by the general term data processing node configuration for a data source, a data set may comprise one or more data sources and one or more data processing nodes, and the resulting data set may be considered a virtual data source (virtual table)
Analysis (card): the module is used for carrying out configuration query and data association graph assembly on the dimension and measurement generated in the process after the data set is processed, and dimension indexes generated by one data set can be bound to a plurality of different types of display assemblies in the module.
Database (DB): the database is a data set which is stored together in a certain mode, can be shared by a plurality of users, has the redundancy as small as possible and is independent from the application program, and the users can add, inquire, update, delete and the like to the data in the file.
Example 1
The embodiment of the invention provides a JDBC-based database direct-checking method, which comprises a database and a data analysis platform, and is characterized in that: further comprising the steps of:
s1, operating the data source by the user through the data analysis platform;
s2, generating a corresponding SQL statement by the data analysis platform according to the operation of the user;
s3, submitting the SQL statement generated in the step S2 to a database by the data analysis platform;
and S4, the database analyzes the SQL statements submitted by the data analysis platform, and returns the processed data to the data analysis platform.
Example 2
Further, in step S1, the user operates the data source in the data set through the data processing node through the data analysis platform.
Further, in step S2, the data analysis platform generates a corresponding SQL dialect statement according to the operation of the user.
Further, in step S3, the data analysis platform submits the SQL statement generated in step S2 to the database in JDBC form.
Example 3
The difference between this embodiment and embodiment 2 is that the generation of the SQL dialect statement includes the following steps:
s5, pre-recording a database type and an SQL dialect matched with the database type on the data analysis platform;
s6, when the user operates the data source through the data analysis platform in the step S1, the data analysis platform can judge the type of the database to be inquired according to the data source;
and S7, the data analysis platform calls the SQL dialect matched with the database type according to the database type to be queried to generate a corresponding SQL dialect statement.
A user selects a data source in the data set, the type of the database to be inquired can be known according to the data source data analysis platform, and dialect sentences of different databases are generated according to the type analysis platform.
Specifically, with reference to mysql and oracle, if the customer has a requirement to query the a table for b1, b2, b3, b4 and limit 10000 terms, the dialect sql is generated as follows:
Mysql：Select b1,b2,b3,b4 from A limit 10000
Oracle：Select b1,b2,b3,b4 from A rownum10000
similarly, for dialects, the statements generated by nodes of different functions in the system are also differentiated. The specific sentence is determined by the operation of the user.
Example 4
A JDBC-based database direct-checking device comprises:
a memory for storing executable instructions;
and the multi-core processor is used for executing the executable instructions stored in the memory and is loaded with a data analysis platform to realize the JDBC-based database direct-checking method.
Furthermore, the system also comprises a display device for displaying the data returned by the database.
Example 5
A JDBC-based database direct-checking system comprises the database direct-checking device and a device loaded with the database.
Example 6
The embodiment is an existing database query method: the data query engine is used as Presto which is an open-source distributed SQL query engine and is provided with API interfaces such as data query. And connecting with Presto through JDBC, submitting Presto SQL generated by the business intelligent analysis platform to Presto, and executing the Presto SQL by the Presto. The data query engine may also be Hive, Impala, Shark, or Stinger, etc.
Example 7
As shown in fig. 3, this embodiment is a comparison between the present database query method and the existing database query method, and a user performs the same query operation on the same database, which shows that, compared with the existing database query method, the memory usage of the present solution is about 1/15, and the time consumption is about 1/10 of the existing method.
In the embodiments provided in the present application, it should be understood that the disclosed apparatus and method can be implemented in other ways. The apparatus embodiments described above are merely illustrative, and for example, the flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of apparatus, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
In addition, the functional modules in the embodiments of the present invention may be integrated together to form an independent part, or each module may exist separately, or two or more modules may be integrated to form an independent part.
The functions, if implemented in the form of software functional modules and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, 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 an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.
The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention, and various modifications and changes may be made by those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention. It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, it need not be further defined and explained in subsequent figures.
The above description is only for the specific embodiments of the present invention, but the scope of the present invention is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present invention, and all the changes or substitutions should be covered within the scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.
Claims (8)
1. A JDBC-based database direct-checking method comprises a database and a data analysis platform, and is characterized in that: further comprising the steps of:
s1, operating the data source by the user through the data analysis platform;
s2, generating a corresponding SQL statement by the data analysis platform according to the operation of the user;
s3, submitting the SQL statement generated in the step S2 to a database by the data analysis platform;
and S4, the database analyzes the SQL statements submitted by the data analysis platform, and returns the processed data to the data analysis platform.
2. The JDBC-based database direct-checking method according to claim 1, wherein: in step S1, the user operates the data source in the data set through the data processing node via the data analysis platform.
3. The JDBC-based database direct-checking method according to claim 1, wherein: in step S2, the data analysis platform generates a corresponding SQL dialect statement according to the operation of the user.
4. The JDBC-based database direct-checking method according to claim 3, wherein: the generation of the SQL dialect statement comprises the following steps:
s5, pre-recording a database type and an SQL dialect matched with the database type on the data analysis platform;
s6, when the user operates the data source through the data analysis platform in the step S1, the data analysis platform can judge the type of the database to be inquired according to the data source;
and S7, the data analysis platform calls the SQL dialect matched with the database type according to the database type to be queried to generate a corresponding SQL dialect statement.
5. The JDBC-based database direct-checking method according to claim 1, wherein: in step S3, the data analysis platform submits the SQL statement generated in step S2 to the database in JDBC form.
6. A database direct-checking device based on JDBC is characterized in that: the method comprises the following steps:
a memory for storing executable instructions;
a multi-core processor for executing the executable instructions stored in the memory, loaded with a data analysis platform for implementing a JDBC-based database direct lookup method according to claim 1.
7. The JDBC based database direct-checking device of claim 6, wherein: and the display device is used for displaying the data returned by the database.
8. A database direct-checking system based on JDBC is characterized in that: comprising the database direct-checking device according to claim 6, and further comprising a device loaded with the database according to claim 1.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910864709.9A CN110781205A (en) | 2019-09-12 | 2019-09-12 | JDBC-based database direct-checking method, device and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910864709.9A CN110781205A (en) | 2019-09-12 | 2019-09-12 | JDBC-based database direct-checking method, device and system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110781205A true CN110781205A (en) | 2020-02-11 |
Family
ID=69383446
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910864709.9A Pending CN110781205A (en) | 2019-09-12 | 2019-09-12 | JDBC-based database direct-checking method, device and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110781205A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113742311A (en) * | 2020-05-29 | 2021-12-03 | 北京滴普科技有限公司 | Index model management method, storage medium and device based on data warehouse |
| CN115563191A (en) * | 2022-11-21 | 2023-01-03 | 广东盈峰科技有限公司 | Method and system for multi-type database table mixed association query in water environment project |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105989150A (en) * | 2015-03-02 | 2016-10-05 | 中国移动通信集团四川有限公司 | Data query method and device based on big data environment |
| CN109446218A (en) * | 2018-09-25 | 2019-03-08 | 中国平安人寿保险股份有限公司 | SQL statement generation method, device and computer readable storage medium |
| CN109766352A (en) * | 2018-11-19 | 2019-05-17 | 成都四方伟业软件股份有限公司 | The method and system that a kind of pair of heterogeneous data source is uniformly processed |
-
2019
- 2019-09-12 CN CN201910864709.9A patent/CN110781205A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105989150A (en) * | 2015-03-02 | 2016-10-05 | 中国移动通信集团四川有限公司 | Data query method and device based on big data environment |
| CN109446218A (en) * | 2018-09-25 | 2019-03-08 | 中国平安人寿保险股份有限公司 | SQL statement generation method, device and computer readable storage medium |
| CN109766352A (en) * | 2018-11-19 | 2019-05-17 | 成都四方伟业软件股份有限公司 | The method and system that a kind of pair of heterogeneous data source is uniformly processed |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113742311A (en) * | 2020-05-29 | 2021-12-03 | 北京滴普科技有限公司 | Index model management method, storage medium and device based on data warehouse |
| CN115563191A (en) * | 2022-11-21 | 2023-01-03 | 广东盈峰科技有限公司 | Method and system for multi-type database table mixed association query in water environment project |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11829391B2 (en) | Systems, methods, and apparatuses for executing a graph query against a graph representing a plurality of data stores | |
| US7734615B2 (en) | Performance data for query optimization of database partitions | |
| US7251653B2 (en) | Method and system for mapping between logical data and physical data | |
| US10853361B2 (en) | Scenario based insights into structure data | |
| US11698918B2 (en) | System and method for content-based data visualization using a universal knowledge graph | |
| JP2017157229A (en) | Scalable analysis platform for semi-structured data | |
| Aranda-Andújar et al. | AMADA: web data repositories in the amazon cloud | |
| CN109033113B (en) | Data warehouse and data mart management method and device | |
| US10216782B2 (en) | Processing of updates in a database system using different scenarios | |
| US9547646B2 (en) | User-created members positioning for OLAP databases | |
| US11442934B2 (en) | Database calculation engine with dynamic top operator | |
| US11074267B2 (en) | Staged approach to automatic data discovery and performance | |
| Abdel Azez et al. | Optimizing join in HIVE star schema using key/facts indexing | |
| Huang et al. | Nosql database: A scalable, availability, high performance storage for big data | |
| Shakhovska et al. | Big Data Model" Entity and Features" | |
| CN110781205A (en) | JDBC-based database direct-checking method, device and system | |
| Roy-Hubara et al. | A method for database model selection | |
| US20230131872A1 (en) | Augmenting search results based on relevancy and utility | |
| Rajesh | Big Data Analytics: Applications and Benefits. | |
| Azez et al. | JOUM: an indexing methodology for improving join in hive star schema | |
| Reniers et al. | Schema design support for semi-structured data: Finding the sweet spot between NF and De-NF | |
| US20180285536A1 (en) | Determining a database signature | |
| Serrano et al. | From relations to multi-dimensional maps: A SQL-to-hbase transformation methodology | |
| Yuan et al. | VDB-MR: MapReduce-based distributed data integration using virtual database | |
| US20160019204A1 (en) | Matching large sets of words |
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 | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20200211 |
|
| RJ01 | Rejection of invention patent application after publication |