Some thoughts and designs on migrating data from mysql to hbase

1. Reasons for migration

Due to the development of business, using mysql to create indexes and search has caused the bottleneck of data flow to be stuck in the database io. For example, every time a full table is dumped, it will cause The pressure is too great, which takes a long time, and the current data volume has basically reached 100 million levels. If you want mysql to provide better services, you must consider sub-databases and tables in the next step; based on this In this case, consider using hbase for data storage, because the amount of data that hbase can bear is much larger than mysql, and the expansion of columns is also very convenient

2. Some differences between relational databases and Nosql

(1) Differences in storage methods

In relational databases such as mysql, sqlserver, oracle, data is stored according to rows, as shown in the following figure:

But in hbase, all data is stored based on columns, as shown below:

The logical model of hbase is as follows:

Among them: com.cnn.ww corresponds to rowkey, which is equivalent to the concept of mysql's primary key

contents, anchor: These two correspond to the concept of column family. In terms of physical storage, data of the same column family is stored in the same file

cnnsi.com, mylook.ca: corresponds to Columns under the column family can be dynamically added in hbase

The corresponding grid data represents unit data, that is, corresponding to rowkey, cf: the specific value under column

Among them, tn: represents the timestamp, different versions of unit data

One of the storage structures is as follows:

##(2) Some differences between CRUD

CRUD is the most basic and commonly used operation of the database. There are also corresponding commands in hbase. For example, the table creation statement for mysql will not be detailed here. For The hbase shell is as follows

create 'table','columnfamily'

You can create a table named table, the column family is columnfamily, and some other blocksize and version data are default

When reading data, use hbase statements such as: get 'table', 'row', 'cf:column' to get the corresponding data

When updating data, use hbase There is no concept of corresponding updates, but there will be a new version, which can be reflected from the timestamp. The statements used are

put 'table', 'row', 'cf:name', 'value '

can assign the value of value to the corresponding cf column family. The column of name

is the difference between deleting data. Deleting data in mysql can only be to delete a row directly or to change a certain column. Set it to empty, and you can directly delete a column in hbase

(3) Differences in indexes

In mysql, you can create indexes or filter queries, but in hbase, only rowkey is supported The fastest query speed

(4) Thoughts on the development from mysql to nosql

The history of relational databases has been long, but when the amount of data expands, for example, for the mysql database, when the amount of data reaches hundreds of millions or more Sometimes, if you query according to the index, the effect may not be particularly obvious. In the end, you can only query according to the primary key, or gradually develop into a sub-database and sub-table model. However, sub-database and sub-table bring a lot of trouble to operation, maintenance and use. Big trouble; so at this time, the development of primary key of nosql database, nosql abbreviated as not only sql, gradually developed and expanded as the amount of data increased dramatically. Taking hbase in nosql as an example, it supports TB and PB data, and columns The expansion is particularly flexible

(5) Why can hbase store massive amounts of data?

In fact, hbase can be regarded as the result of mysql sub-database and table sub-database, but the difference is that mysql sub-database is divided into The table supports indexes, etc., but hbase only supports rowkey as the primary key index. From the book, we can know that hbase data is stored according to columns, and when the data is too large, it will be split according to rows, as shown below :

## Put different regions on different machines, and finally there is a master for management, which is equivalent to The rows and columns are divided to store a large amount of data

3. Some problems encountered in data migration

(1) Problems with joint index

There will be problems in mysql In some joint index situations, for example, there is a table of correspondence between products and categories. We need to get all the categories of a certain product, and we also hope to get all the products of a certain category. In mysql, we can directly follow the joint index to meet the requirements, but in What should I do when hbase can only query according to rowkey?

After reading the relevant data, there are two solutions as follows

1. Build a wide table

In hbase , allowing the columns between rows to be different, as long as there is a common column family, then for the above situation, you can build a wide table classified as rowkey, as shown below

Classification id , as rowkey

product_id, as column name

value is stored as whether to delete

The above rowkey can be the classification id , you can get all product_id directly from row, and then filter whether to delete it yourself

2. Build a tall table

What is building a tall table, that is to say, you don’t need so many columns, just To store multiple rows, because hbase is sorted in dictionary order, the following design can be done

Classification id_product id, as rowkey

As long as you scan the rows starting with 1, you can get all the data

Essentially, the above two methods build a secondary index to store the data

The above are some thoughts and designs on migrating data from mysql to hbase. For more related content, please pay attention to the PHP Chinese website (www.php. cn)!