CN111104396A - Cross-database data migration method and data access method - Google Patents
Cross-database data migration method and data access method Download PDFInfo
- Publication number
- CN111104396A CN111104396A CN201911404075.5A CN201911404075A CN111104396A CN 111104396 A CN111104396 A CN 111104396A CN 201911404075 A CN201911404075 A CN 201911404075A CN 111104396 A CN111104396 A CN 111104396A
- Authority
- CN
- China
- Prior art keywords
- data
- database
- hbase
- query
- rowkey
- 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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Abstract
The invention provides a data migration method and a data access method across databases, wherein the data migration method comprises the following steps: when the oracle/mysql database stores data, the stored data is exported when the system is idle; generating a service data file with an Hbase specified format according to the exported data; establishing an Hbase data table, and designing a rowKey format according to service requirements; and writing the service data file into the Hbase database through an importsv command. The data access method realizes data migration based on the data migration method, then converts a query expression in a data access request into a rowKey query through a phonix query engine and a thritf query engine, and queries required data from an Hbase database and returns the data to a client. The invention can carry out data migration on the premise of not influencing the stability of the online system and reduce the access pressure of the online system.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a cross-database data migration method and a data access method.
Background
As the system is constructed and operated, the system data is more and more. More and more third-party service data are connected to smart cities. The efficiency of the traditional relational databases such as mysql and oracle in the write-in processing of a large amount of data, the application when the fields are not fixed, the processing aspect that the result needs to be returned quickly in simple query is unsatisfactory, and the bottleneck of the databases in concurrent operation of a large amount of data needs to be solved.
Traditional performance tuning starts from sql optimization and database performance, but the bottleneck of the line database mysql/oracle is that after the data volume exceeds ten million levels, the line database exposes performance weakness. Through business comparison, using a line database is 5 times less efficient than a columnar database query. When the access amount is large and reaches a certain order of magnitude, the line database may have access bottleneck problem.
At present, a solution for improving query efficiency by replacing an oracle database with HBASE data exists, and a columnar database Hbase essentially has only one operation, namely insertion, wherein an updating operation is to insert a row with a new timestamp, and a deletion operation is to insert a row with an insertion mark. The main operation is to collect a batch of data in the memory and then write the batch into the hard disk, so the writing speed is mainly determined by the speed of hard disk transmission. The line database Oracle is different because it usually needs random reading and writing, so the hard disk head needs to continuously search the data, so the bottleneck is the hard disk seek time.
In the scheme of adopting HBASE data to replace an oracle database, in the link of importing and exporting data, data is generally migrated by a data importing and exporting function sqoop direct connection service database of HBASE. The method can well perform incremental synchronization of data, but if sqoop is used for importing and exporting the full service data, because the database is huge, the online environment database is directly accessed, the access pressure of a service system is increased, and the online service is affected.
Disclosure of Invention
The purpose of the invention is as follows: in order to overcome the defects of the prior art, the invention provides a data migration method and a data access method across databases, which can perform data migration on the premise of not influencing the stability of an online system and reduce the access pressure of the online system.
The technical scheme is as follows: in order to achieve the purpose, the invention provides the following technical scheme:
a method of data migration across databases, comprising the steps of:
(1) when the oracle/mysql database stores data, the stored data is exported when the system is idle;
(2) generating a service data file with an Hbase specified format according to the exported data;
(3) establishing an Hbase data table, and designing a rowKey format according to service requirements;
(4) and writing the service data file into the Hbase database through an importsv command.
Further, the format of the service data file is csv.
Further, the format of the rowKey is as follows: MD5 primary key + rowKey primary key + timestamp.
The invention also provides a data access method, which comprises the following steps:
(1) data stored in the oracle/mysql database is migrated to the Hbase database by the data migration method across databases;
(2) when a client sends a data access request to an oracle/mysql database, distributing the data access request to a phonix query engine and a thritf query engine according to a load balancing principle;
(3) the phonix query engine and the thrtif query engine respectively convert the query expression in the data access request into rowKey query, query the required data from the Hbase database and return the data to the client.
Has the advantages that: compared with the prior art, the invention has the following advantages:
according to the invention, by adopting a data migration asynchronous processing method, direct access to an online environment database is avoided, and stable operation of an online system is fully ensured;
for the data access service, the Hbase database, the phonix query engine and the thritf query engine are used for shunting the data access service, so that the pressure of the original database oracle/mysql in response to the data access service is reduced.
Drawings
FIG. 1 is a flow chart of a method for data migration across databases according to an embodiment of the present invention;
fig. 2 is an architecture diagram of a data access method according to an embodiment of the present invention.
Detailed Description
The invention will be further described with reference to the accompanying drawings and specific embodiments. It is to be understood that the present invention may be embodied in various forms, and that there is no intention to limit the invention to the specific embodiments illustrated, but on the contrary, the intention is to cover some exemplary and non-limiting embodiments shown in the attached drawings and described below.
Fig. 1 shows an embodiment of a data access method according to the invention, comprising the following steps:
(1) when the oracle/mysql database stores data, the stored data is exported when the system is idle;
(2) generating a service data file with an Hbase specified format according to the exported data;
(3) establishing an Hbase data table, and designing a rowKey format according to service requirements;
(4) and writing the service data file into the Hbase database through an importsv command.
In one or more implementations of a data access method of the present invention, the format of the service data file is csv.
In one or more implementations of a data access method of the present invention, the rowKey format is: MD5 primary key + rowKey primary key + timestamp, the adoption of this rowKey design mode can promote the inquiry efficiency.
Fig. 2 shows an embodiment of a data access method according to the present invention, which includes a phonix query engine and a thritf query engine. The data access method comprises the following steps:
(1) data stored in the oracle/mysql database is migrated to the Hbase database by the data migration method across databases;
(2) when a client sends a data access request to an oracle/mysql database, distributing the data access request to a phonix query engine and a thritf query engine according to a load balancing principle;
(3) the phonix query engine and the thrtif query engine respectively convert the query expression in the data access request into a rowKey query, and query the required data from the Hbase database, such as:
StrVec columnNames；
std::string table("H_05_TG_DW201510_GS")；
columnNames.push_back("cf:GGSN")；
std::cout<<"Starting scanner..."<<std::endl；
int scanner＝client.scannerOpen(table,
"dedac612529978^20111001001236AAByu6AAvAAA",columnNames,dummyAttributes)；
std::cout<<"Started scanner..."<<std::endl；
after the query is finished, the phonix query engine/thrtif query engine returns the query result to the client.
It is to be understood that the features listed above for the different embodiments may be combined with each other to form further embodiments within the scope of the invention, where technically feasible. Furthermore, the particular examples and embodiments of the invention described are non-limiting, and various modifications may be made in the structure, steps, and sequence set forth above without departing from the scope of the invention.
The above-described embodiments, particularly any "preferred" embodiments, are possible examples of implementations, and are presented merely for a clear understanding of the principles of the invention. Many variations and modifications may be made to the above-described embodiments without departing substantially from the spirit and principles of the technology described herein, and such variations and modifications are to be considered within the scope of the invention.
Claims (4)
1. A method of data migration across databases, comprising the steps of:
(1) when the oracle/mysql database stores data, the stored data is exported when the system is idle;
(2) generating a service data file with an Hbase specified format according to the exported data;
(3) establishing an Hbase data table, and designing a rowKey format according to service requirements;
(4) and writing the service data file into the Hbase database through an importsv command.
2. The method according to claim 1, wherein the business data file is in the format of csv.
3. The method of data migration across a database according to claim 1, wherein the rowKey format is: MD5 primary key + rowKey primary key + timestamp.
4. A method of accessing data, comprising the steps of:
(1) migrating data stored in an oracle/mysql database to an Hbase database by using the data migration method across databases according to any one of claims 1 to 3;
(2) when a client sends a data access request to an oracle/mysql database, distributing the data access request to a phonix query engine and a thritf query engine according to a load balancing principle;
(3) the phonix query engine and the thrtif query engine respectively convert the query expression in the data access request into rowKey query, query the required data from the Hbase database and return the data to the client.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911404075.5A CN111104396A (en) | 2019-12-31 | 2019-12-31 | Cross-database data migration method and data access method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911404075.5A CN111104396A (en) | 2019-12-31 | 2019-12-31 | Cross-database data migration method and data access method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111104396A true CN111104396A (en) | 2020-05-05 |
Family
ID=70423946
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911404075.5A Pending CN111104396A (en) | 2019-12-31 | 2019-12-31 | Cross-database data migration method and data access method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111104396A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113254421A (en) * | 2021-05-31 | 2021-08-13 | 重庆富民银行股份有限公司 | Database double-writing based cross-library account migration method |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103631907A (en) * | 2013-11-26 | 2014-03-12 | 中国科学院信息工程研究所 | Method and system for migrating relational data to HBbase |
| CN106933859A (en) * | 2015-12-30 | 2017-07-07 | 中国移动通信集团公司 | The moving method and device of a kind of medical data |
| CN108255966A (en) * | 2017-12-25 | 2018-07-06 | 太极计算机股份有限公司 | A kind of data migration method and storage medium |
-
2019
- 2019-12-31 CN CN201911404075.5A patent/CN111104396A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103631907A (en) * | 2013-11-26 | 2014-03-12 | 中国科学院信息工程研究所 | Method and system for migrating relational data to HBbase |
| CN106933859A (en) * | 2015-12-30 | 2017-07-07 | 中国移动通信集团公司 | The moving method and device of a kind of medical data |
| CN108255966A (en) * | 2017-12-25 | 2018-07-06 | 太极计算机股份有限公司 | A kind of data migration method and storage medium |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113254421A (en) * | 2021-05-31 | 2021-08-13 | 重庆富民银行股份有限公司 | Database double-writing based cross-library account migration method |
| CN113254421B (en) * | 2021-05-31 | 2023-07-14 | 重庆富民银行股份有限公司 | Database double-writing-based cross-database account migration method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107818115B (en) | Method and device for processing data table | |
| CN107423422B (en) | Spatial data distributed storage and search method and system based on grid | |
| KR102177190B1 (en) | Managing data with flexible schema | |
| CN103631907B (en) | A kind of method and system that relational data is migrated to HBase | |
| CA2777425C (en) | Method for performing transactions on data and a transactional database | |
| US9501550B2 (en) | OLAP query processing method oriented to database and HADOOP hybrid platform | |
| US9149054B2 (en) | Prefix-based leaf node storage for database system | |
| US9047330B2 (en) | Index compression in databases | |
| US11061924B2 (en) | Multi-region, multi-master replication of database tables | |
| CN105117417A (en) | Read-optimized memory database Trie tree index method | |
| CN109521959A (en) | One kind being based on SSD-SMR disk mixing key assignments memory system data method for organizing | |
| CN103440245A (en) | Line and column hybrid storage method of database system | |
| CN1652112A (en) | Implementing method of data dictionary under embedded environment | |
| CN106682148A (en) | Method and device based on Solr data search | |
| CN110309233A (en) | Method, apparatus, server and the storage medium of data storage | |
| CN103365987A (en) | Clustered database system and data processing method based on shared-disk framework | |
| KR101806394B1 (en) | A data processing method having a structure of the cache index specified to the transaction in a mobile environment dbms | |
| US10558636B2 (en) | Index page with latch-free access | |
| CN107273443B (en) | Mixed indexing method based on metadata of big data model | |
| CN111104396A (en) | Cross-database data migration method and data access method | |
| CN117235028A (en) | Data query method and device based on log file | |
| CN109800233A (en) | A kind of big data fusion searching method | |
| CN116049193A (en) | Data storage method and device | |
| CN113051271B (en) | Cold and hot data separation method, device and equipment thereof | |
| CN103353891A (en) | Database management system and data processing method thereof |
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: 20200505 |