Shulou(Shulou.com)05/31 Report--

What is the relationship between MySQL partition table and HBase? in view of this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Create MySQL partition data

DROP TABLE ord_order

-- create an order partition table

CREATE TABLE ord_order (

Order_id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'order ID'

User_id INT NOT NULL COMMENT 'user ID'

Goods_id INT NOT NULL COMMENT 'merchandise ID'

Order_price INT NOT NULL DEFAULT 0 COMMENT 'order actual Price (min)'

Create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time'

PRIMARY KEY (order_id, create_time)

)

PARTITION BY LIST (YEAR (create_time) * 100 + MONTH (create_time))

(

PARTITION p201601 VALUES IN (201601)

PARTITION p201602 VALUES IN (201602)

PARTITION p201603 VALUES IN (201603)

PARTITION p201604 VALUES IN (201604)

PARTITION p201605 VALUES IN (201605)

PARTITION p201606 VALUES IN (201606)

PARTITION p201607 VALUES IN (201607)

PARTITION p201608 VALUES IN (201608)

PARTITION p201609 VALUES IN (201609)

PARTITION p201610 VALUES IN (201610)

PARTITION p201611 VALUES IN (201611)

PARTITION p201612 VALUES IN (201612)

)

-- insert relevant data

INSERT INTO ord_order VALUES

(NULL, 10000001, 11111111, 1000, '2016-01-13 01-13 01)

(NULL, 10000001, 11111112, 2000, '2016-01-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-01-13 03rig 030')

(NULL, 10000001, 11111114, 4000, '2016-01-13 04purl 0040')

(NULL, 10000001, 11111115, 5000, '2016-01-13 05VONPLOR 50')

(NULL, 10000001, 11111111, 1000, '2016-02-13 01)

(NULL, 10000001, 11111112, 2000, '2016-02-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-02-13 03)

(NULL, 10000001, 11111114, 4000, '2016-02-13 04)

(NULL, 10000001, 11111115, 5000, '2016-02-13 05VOLV 50')

(NULL, 10000001, 11111111, 1000, '2016-03-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-03-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-03-13 03-13 03)

(NULL, 10000001, 11111114, 4000, '2016-03-13 04)

(NULL, 10000001, 11111115, 5000, '2016-03-13 03-13 05purl 0050')

(NULL, 10000001, 11111111, 1000, '2016-04-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-04-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-04-13 03VOULARO 30')

(NULL, 10000001, 11111114, 4000, '2016-04-13 04)

(NULL, 10000001, 11111115, 5000, '2016-04-13 04-13 05PUR 00RAPL 50')

(NULL, 10000001, 11111111, 1000, '2016-05-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-05-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-05-13 03VOULARO 30')

(NULL, 10000001, 11111114, 4000, '2016-05-13 04 rig 0040')

(NULL, 10000001, 11111115, 5000, '2016-05-13 05-13 05-13 05-0015-050')

(NULL, 10000001, 11111111, 1000, '2016-06-13 01)

(NULL, 10000001, 11111112, 2000, '2016-06-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-06-13 03)

(NULL, 10000001, 11111114, 4000, '2016-06-13 04 rig 0040')

(NULL, 10000001, 11111115, 5000, '2016-06-13 05rig 0015')

(NULL, 10000001, 11111111, 1000, '2016-07-13 01)

(NULL, 10000001, 11111112, 2000, '2016-07-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-07-13 03rig 0030')

(NULL, 10000001, 11111114, 4000, '2016-07-13 04)

(NULL, 10000001, 11111115, 5000, '2016-07-13 05VONPLOR 50')

(NULL, 10000001, 11111111, 1000, '2016-08-13 01)

(NULL, 10000001, 11111112, 2000, '2016-08-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-08-13 03)

(NULL, 10000001, 11111114, 4000, '2016-08-13 04)

(NULL, 10000001, 11111115, 5000, '2016-08-13 05rig 050')

(NULL, 10000001, 11111111, 1000, '2016-09-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-09-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-09-13 03)

(NULL, 10000001, 11111114, 4000, '2016-09-13 04)

(NULL, 10000001, 11111115, 5000, '2016-09-13 05VONPLOR 50')

(NULL, 10000001, 11111111, 1000, '2016-10-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-10-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-10-13 03)

(NULL, 10000001, 11111114, 4000, '2016-10-13 04)

(NULL, 10000001, 11111115, 5000, '2016-10-13 05VOV 050')

(NULL, 10000001, 11111111, 1000, '2016-11-13 01)

(NULL, 10000001, 11111112, 2000, '2016-11-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-11-13 03)

(NULL, 10000001, 11111114, 4000, '2016-11-13 04)

(NULL, 10000001, 11111115, 5000, '2016-11-13 05VOLV 50')

(NULL, 10000001, 11111111, 1000, '2016-12-13 01VO1VOLING 10')

(NULL, 10000001, 11111112, 2000, '2016-12-13 02purl 0020')

(NULL, 10000001, 11111113, 3000, '2016-12-13 03)

(NULL, 10000001, 11111114, 4000, '2016-12-13 04)

(NULL, 10000001, 11111115, 5000, '2016-12-13 05VONPLOR 50')

-- View partition p201601 data

SELECT * FROM ord_order PARTITION (p201601)

-- combined row key

SELECT CONCAT (user_id, 10000000000-UNIX_TIMESTAMP (create_time), goods_id)

FROM ord_order PARTITION (p201601)

Combine with HBase.

Create HBase table ord_order

Due to the compatibility of versions, I need to create a table corresponding to HBase first. Otherwise, an error will be reported that the column family cannot be created automatically.

Create an ord_order table using hbase shell

Hbase (main): 033 ord_order', 0 > create 'ord_order', {NAME = >' cf1'}

Use Sqoop to import data from the p201601 partition of MySQL's ord_order table into the HBase table.

/ usr/local/sqoop/bin/sqoop import\

-- connect jdbc:mysql://192.168.137.11:3306/test  \

-- username HH\

-- password oracle\

-- query 'SELECT CONCAT (user_id, 10000000000-UNIX_TIMESTAMP (create_time), goods_id) AS order_id, order_price, create_time FROM ord_order PARTITION (p201601) WHERE $CONDITIONS'\

-- hbase-table ord_order\

-- hbase-create-table\

-- hbase-row-key order_id\

-- split-by order_id\

-- column-family cf1\

-m 1

After the import is successful, you can delete the relevant partitions on the MySQL and create the required partitions:

ALTER TABLE ord_order

ADD PARTITION (PARTITION p201701 VALUES IN (201701))

ALTER TABLE ord_order DROP PARTITION p201601

View imported data in Hbase

Hbase (main): 001scan 0 > ord_order'

ROW COLUMN+CELL

10000001854736755011111115 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 05VOV 001V 50.0

10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000

10000001854737116011111114 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 04VOUD 00VOV 40.0

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

10000001854737477011111113 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 03lv 001V 30.0

10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000

100000018547838011111112 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 02PUBG

10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000

1000000185473819901111111111 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 01-13 01VOLPLO 10.0

10000001854738199011111111 column=cf1:order_price, timestamp=1479224942888, value=1000

5 row (s) in 0.5390 seconds

Detailed explanation of ROW KEY Design

The row key in HBase consists of three fields: user_id, 10000000000-UNIX_TIMESTAMP (create_time) and goods_id.

What is noteworthy here is 10000000000-UNIX_TIMESTAMP (create_time), which is designed so that orders can be arranged in reverse order in time, so that the newer the data, the first to display.

For example, 10000001 of users' orders need to be paginated, with two pieces of data per page, and sorted in reverse order of time (the latest order is shown first)

Hbase (main): 003ord_order', 0 > scan 'ord_order', {COLUMNS= > [' cf1:order_price'], ROWPREFIXFILTER= > '10000001mm, LIMIT= > 2}

ROW COLUMN+CELL

10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

Click the data on the next page:

Hbase (main): 004ord_order', 0 > scan 'ord_order', {COLUMNS= > [' cf1:order_price'], LIMIT= > 3, STARTROW= > '10000001854737116011111114'}

ROW COLUMN+CELL

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000

10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000

3 row (s) in 0.0260 seconds

Three rows of data have been obtained above. Just remove the first row in the actual display. The actual display is as follows:

10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000

10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000

Click on the previous page

Hbase (main): 008ord_order', 0 > scan 'ord_order', {COLUMNS= > [' cf1:order_price'], LIMIT= > 3, STARTROW= > '100000018547377011111113, REVERSED= > true}

ROW COLUMN+CELL

10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000

3 row (s) in 0.0640 seconds

We also get three pieces of data above. We need to remove the first row and display it in reverse order according to the data set.

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000

The top two rows of ↓ are the data of the collection traversing the collection in reverse order below the collection data (and the final displayed data)

10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000

10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000

The answer to the question about the relationship between MySQL partition table and HBase is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.