CN110795418A - Json-based data extraction method from mongoDB to mysql - Google Patents
Json-based data extraction method from mongoDB to mysql Download PDFInfo
- Publication number
- CN110795418A CN110795418A CN201910900410.4A CN201910900410A CN110795418A CN 110795418 A CN110795418 A CN 110795418A CN 201910900410 A CN201910900410 A CN 201910900410A CN 110795418 A CN110795418 A CN 110795418A
- Authority
- CN
- China
- Prior art keywords
- mongodb
- data
- mysql
- database
- json
- 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/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- 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/25—Integrating or interfacing systems involving database management systems
- G06F16/258—Data format conversion from or to a database
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a json-based data extraction method from mongoDB to mysql, and relates to the technical field of data extraction. The method comprises loading and storing configuration information of the mongoDB database through a UGetMOongoPlus processor, and reading data from the mongoDB database by executing an sql query statement; outputting the json format of the data to a queue; loading and storing json conversion configuration information through a MongoMappingprocessor processor; mapping json format data to mysql data table fields; and loading mysql connection configuration information, data information and data table information of the relational database through the ULoadprocessor, and storing json format data to the mysql database. The visualized page configures the information transfer from mongoDB to mysql, so that the method is simple, intuitive, convenient and quick; data flows from source mongoDB to destination mysql database in json format; and an operator does not need to write a command line, an sql script or a shell script, so that the efficiency is improved.
Description
Technical Field
The invention belongs to the technical field of data extraction, and particularly relates to a json-based data extraction method from mongoDB to mysql.
Background
With the increasing of the mongoDB non-relational database in software application, the use advantages brought by document storage are more and more recognized by programmers and companies, and the mongoDB is widely applied; and the dominant mongoDB that has many scenarios using relational databases to present business data has not been able to replace. There is therefore a transfer of data from the mongoDB database to the relational database mysql; in addition, there are many original business systems built on top of the mysql database, and there is also a need to transfer from mongoDB database to mysql database.
As shown in fig. 11, a common technical solution for transferring data from the mongoDB database to the mysql database is to export the data in the mongoDB to the csv file or txt file, and then import the data in the csv file or txt file to the mysql database. However, this approach has the following disadvantages: a. the data reading from mongoDB requires command line operation and has high requirement on professional knowledge of operators; b. writing data into mysql requires writing sql scripts or shell scripts, and operators need to master necessary professional skills; c. the user experience is poor due to the fact that the visual operation interface is not available.
In view of the above, the invention provides a json-based data extraction method from mongoDB to mysql, which realizes data transfer from a mongoDB database to a mysql database in a visual configuration mode; the json technology is utilized to realize the data corresponding relation in the process of converting the data from mongoDB to mysql, so that the transformation of the data from a source to a target can be conveniently and visually checked; and realizing the corresponding relation between the data in the mongoDB and the data in the mysql.
Disclosure of Invention
The invention aims to provide a json-based data extraction method from mongoDB to mysql, which realizes data transfer from a mongoDB database to a mysql database; the json technology is utilized to realize the data corresponding relation in the process of converting the data from mongoDB to mysql, so that the transformation of the data from a source to a target can be conveniently and visually checked; and realizing the corresponding relation between the data in the mongoDB and the data in the mysql.
In order to solve the technical problems, the invention is realized by the following technical scheme:
the invention relates to a json-based data extraction method from mongoDB to mysql, which comprises the following steps:
s00: configuring mongoDB database information to be connected to a mongoDB database;
s01: loading and storing configuration information of the mongoDB database through a UGetMOngoPlus processor, and reading data from the mongoDB database by executing an sql query statement; outputting the json format of the data to a queue;
the sql query statement is generated after being configured in configuration information;
a02: loading and storing json conversion configuration information through a MongoMappingprocessor processor; mapping json format data to mysql data table fields;
s03: and loading mysql connection configuration information, data information and data table information of the relational database through the ULoadprocessor, and storing json format data to the mysql database.
Preferably, the mongoDB database information, the json conversion configuration information, and the mysql connection configuration information are configured in advance on the corresponding configuration files.
Preferably, the mongoDB database information and the mysql connection configuration information are established on the basis that the mongoDB database and the mysql database are managed.
Preferably, in S01, key information in the mongoDB set is obtained by traversing documents in the mongoDB database set or executing sql query statements.
The invention has the following beneficial effects:
1. the invention realizes the data transfer from the mongoDB database to the mysql database; the json technology is utilized to realize the data corresponding relation in the process of converting the data from mongoDB to mysql, so that the transformation of the data from a source to a target can be conveniently and visually checked; realizing the corresponding relation between the data in the mongoDB and the data in the mysql;
2. the method configures mongoDB database information, is connected to the mongoDB database, acquires key information in the mongoDB set by traversing the mongoDB database set or executing sql query statements, and realizes the association of source and target data by configuring the corresponding relation between key in the mongoDB database set and table fields in the mysql database, thereby being convenient and fast and convenient to operate;
3. the method configures the information transfer from mongoDB to mysql through the visual page, and is simple, intuitive, convenient and quick; data flows from source mongoDB to destination mysql database in json format; and an operator does not need to write a command line, an sql script or a shell script, so that the efficiency is improved.
Of course, it is not necessary for any product in which the invention is practiced to achieve all of the above-described advantages at the same time.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without creative efforts.
FIG. 1 is a flow chart of a json-based data extraction method from mongoDB to mysql;
FIG. 2 is a schematic diagram of a source and destination data corresponding relationship page according to the present invention
FIG. 3 is a schematic diagram of a data integration home page according to the present invention;
FIG. 4 is a schematic diagram of a newly added data integration configuration page according to the present invention;
FIG. 5 is a schematic diagram of filling configuration information in a newly added data integration configuration page according to the present invention;
FIG. 6 is a schematic view of a process configuration page in the present invention;
FIG. 7 is a schematic diagram of an input flow configuration page in accordance with the present invention;
FIG. 8 is a schematic view of a cleaning configuration page in accordance with the present invention;
FIG. 9 is a diagram of a conversion configuration page in accordance with the present invention;
FIG. 10 is a schematic diagram of an output configuration page in accordance with the present invention;
FIG. 11 is a diagram of the transfer of data from the mongoDB database to the mysql database by CSV in the background art.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Referring to fig. 1, the present invention is a json-based data extraction method from mongoDB to mysql, comprising the following steps:
s00: configuring mongoDB database information to be connected to a mongoDB database;
s01: loading and storing configuration information of the mongoDB database through a UGetMOngoPlus processor, and reading data from the mongoDB database by executing an sql query statement; outputting the json format of the data to a queue;
wherein, the sql query statement is generated after being configured in the configuration information;
a02: loading and storing json conversion configuration information through a MongoMappingprocessor processor; mapping json format data to mysql data table fields;
s03: and loading mysql connection configuration information, data information and data table information of the relational database through the ULoadprocessor, and storing json format data to the mysql database.
The mongoDB database information, the json conversion configuration information and the mysql connection configuration information are configured on the corresponding configuration files in advance.
The mongoDB database information and the mysql connection configuration information are established on the basis of management of the mongoDB database and the mysql database.
In S01, the key information in the mongoDB set is obtained by traversing documents in the mongoDB database set or executing sql query statements, and the association of the source data and the target data is realized by configuring the corresponding relation between the key in the mongoDB database set and the table field in the mysql database.
The format of the source data is as follows:
{
"_id":{"$oid":"5b88a4e1157de2b482886636"},
"__initiator":"00119",
"__initiatorName":"XXX(00119)",
"__startTime":{"$date":"2018-08-14T11:28:28.654Z"},
"place of business": somewhere ",
"business trip start time": 2018-08-15",
"business trip finish time": 2018-08-16",
the actual departure time is null,
the "actual return time" is the null,
"Applicant department": 000004",
'direct main pipe': 00100"
Please refer to fig. 2, which is a page of the corresponding relationship between the source data and the destination data.
The first embodiment is as follows:
as shown in fig. 3, firstly, click the new data integration on the data integration home page; as shown in fig. 4, entering a new data integration configuration page; filling in an integration name, an integration type and an integration description on a newly added data integration configuration page as shown in fig. 5; as shown in fig. 6, enter the flow configuration page to select the corresponding flow configuration; as shown in fig. 7, the input flow configures the data source, the database, the data table, and the extraction field in the page; as shown in fig. 8, the cleaning rule and the cleaning field are configured on the cleaning configuration page; as shown in fig. 9, the conversion rule and the conversion field are configured in the conversion configuration page; as shown in fig. 10, in the output configuration page, the data source, the database, the data table, the storage mode, and the database field are configured; and after the configuration is completed, the data in the mongoDB database is extracted to the mysql database.
It should be noted that, in the above system embodiment, each included unit is only divided according to functional logic, but is not limited to the above division as long as the corresponding function can be implemented; in addition, specific names of the functional units are only for convenience of distinguishing from each other, and are not used for limiting the protection scope of the present invention.
In addition, it is understood by those skilled in the art that all or part of the steps in the method for implementing the embodiments described above may be implemented by a program instructing associated hardware, and the corresponding program may be stored in a computer-readable storage medium.
The preferred embodiments of the invention disclosed above are intended to be illustrative only. The preferred embodiments are not intended to be exhaustive or to limit the invention to the precise embodiments disclosed. Obviously, many modifications and variations are possible in light of the above teaching. The embodiments were chosen and described in order to best explain the principles of the invention and the practical application, to thereby enable others skilled in the art to best utilize the invention. The invention is limited only by the claims and their full scope and equivalents.
Claims (4)
1. A json-based data extraction method from mongoDB to mysql, which is characterized by comprising the following steps:
s00: configuring mongoDB database information to be connected to a mongoDB database;
s01: loading and storing configuration information of the mongoDB database through a UGetMOngoPlus processor, and reading data from the mongoDB database by executing an sql query statement; outputting the json format of the data to a queue;
the sql query statement is generated after being configured in configuration information;
a02: loading and storing json conversion configuration information through a MongoMappingprocessor processor; mapping json format data to mysql data table fields;
s03: and loading mysql connection configuration information, data information and data table information of the relational database through the ULoadprocessor, and storing json format data to the mysql database.
2. The json-based mongoDB to mysql data extraction method as claimed in claim 1, wherein the mongoDB database information, the json conversion configuration information and the mysql connection configuration information are configured in advance on the corresponding configuration files.
3. The json-based mongoDB to mysql data extraction method as claimed in claim 2, wherein the mongoDB database information and mysql connection configuration information are based on the management of the mongoDB database and the mysql database.
4. The json-based mongoDB to mysql data extraction method as claimed in claim 1, wherein the key information in the mongoDB collection is obtained by traversing the documents in the mongoDB collection or executing the sql query statement in S01.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910900410.4A CN110795418A (en) | 2019-09-23 | 2019-09-23 | Json-based data extraction method from mongoDB to mysql |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910900410.4A CN110795418A (en) | 2019-09-23 | 2019-09-23 | Json-based data extraction method from mongoDB to mysql |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110795418A true CN110795418A (en) | 2020-02-14 |
Family
ID=69439663
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910900410.4A Pending CN110795418A (en) | 2019-09-23 | 2019-09-23 | Json-based data extraction method from mongoDB to mysql |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110795418A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111694812A (en) * | 2020-05-06 | 2020-09-22 | 五八有限公司 | Data migration method and data migration device |
| CN111797154A (en) * | 2020-07-03 | 2020-10-20 | 紫光云技术有限公司 | Json-based real-time extraction method |
| CN112860792A (en) * | 2021-01-29 | 2021-05-28 | 银盛支付服务股份有限公司 | Method and system for synchronizing mongodb cluster data and hive cluster data |
| CN114741393A (en) * | 2022-04-19 | 2022-07-12 | 四川大学 | Material genetic engineering data conversion and retrieval method |
| CN116775680A (en) * | 2023-05-31 | 2023-09-19 | 北京龙软科技股份有限公司 | SQL-based method for operating MongoDB database |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108009282A (en) * | 2017-12-22 | 2018-05-08 | 武汉楚鼎信息技术有限公司 | A kind of json data are synchronized to the method and system device of relevant database |
-
2019
- 2019-09-23 CN CN201910900410.4A patent/CN110795418A/en active Pending
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108009282A (en) * | 2017-12-22 | 2018-05-08 | 武汉楚鼎信息技术有限公司 | A kind of json data are synchronized to the method and system device of relevant database |
Non-Patent Citations (1)
| Title |
|---|
| JACKDONGTING: "关于mongodb转存MySQL", 《HTTPS://BLOG.51CTO.COM/U_10725691/2160620》 * |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111694812A (en) * | 2020-05-06 | 2020-09-22 | 五八有限公司 | Data migration method and data migration device |
| CN111797154A (en) * | 2020-07-03 | 2020-10-20 | 紫光云技术有限公司 | Json-based real-time extraction method |
| CN112860792A (en) * | 2021-01-29 | 2021-05-28 | 银盛支付服务股份有限公司 | Method and system for synchronizing mongodb cluster data and hive cluster data |
| CN114741393A (en) * | 2022-04-19 | 2022-07-12 | 四川大学 | Material genetic engineering data conversion and retrieval method |
| CN114741393B (en) * | 2022-04-19 | 2023-04-28 | 四川大学 | Material genetic engineering data conversion and retrieval method |
| CN116775680A (en) * | 2023-05-31 | 2023-09-19 | 北京龙软科技股份有限公司 | SQL-based method for operating MongoDB database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110795418A (en) | Json-based data extraction method from mongoDB to mysql | |
| CN108536761B (en) | Report data query method and server | |
| CN108052681B (en) | Method and system for synchronizing structured data between relational databases | |
| CN105354314B (en) | Data migration method and device | |
| CN109491989B (en) | Data processing method and device, electronic equipment and storage medium | |
| CN109919691B (en) | Data processing system, method and device | |
| CN108319661A (en) | A kind of structured storage method and device of spare part information | |
| US20200097487A1 (en) | Novel olap pre-calculation model and modeling method | |
| CN104765731A (en) | Database query optimization method and equipment | |
| CN110688828A (en) | File processing method and device, file processing system and computer equipment | |
| CN109684270B (en) | Database archiving method, device, system, equipment and readable storage medium | |
| CN110191156B (en) | Data processing method and device based on block chain | |
| CN109104305A (en) | A kind of node increases method and relevant apparatus | |
| JPWO2017090475A1 (en) | Information processing system, function creation method, and function creation program | |
| CN111090803A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN115617773A (en) | Data migration method, device and system | |
| CN109783779B (en) | Tool for importing, exporting and converting data EXCEL | |
| WO2024179139A1 (en) | Data synchronization method, database, data synchronization apparatus, and product | |
| CN103729455B (en) | Master data storage method based on primary copy storage pattern | |
| CN109101229A (en) | A kind of code automatic generation method of object-oriented, device and equipment | |
| CN108460078B (en) | Auxiliary function execution method and device, storage medium and terminal | |
| CN105608103A (en) | Transforming method and device of data storing structure | |
| CN108664503A (en) | A kind of data archiving method and device | |
| CN110633271A (en) | Data extraction method from Hbase to mysql based on json | |
| CN114254030A (en) | Data synchronization method, system, terminal 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 | ||
| WD01 | Invention patent application deemed withdrawn after publication | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20200214 |