MySQL分區(qū)表和HBase的關(guān)系是什么，針對(duì)這個(gè)問題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡(jiǎn)單易行的方法。

成都網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)公司、微信開發(fā)、小程序制作、集團(tuán)成都定制網(wǎng)頁(yè)設(shè)計(jì)等服務(wù)項(xiàng)目。核心團(tuán)隊(duì)均擁有互聯(lián)網(wǎng)行業(yè)多年經(jīng)驗(yàn)，服務(wù)眾多知名企業(yè)客戶；涵蓋的客戶類型包括：成都汽車玻璃修復(fù)等眾多領(lǐng)域，積累了大量豐富的經(jīng)驗(yàn)，同時(shí)也獲得了客戶的一致贊賞！

創(chuàng)建 MySQL 分區(qū)數(shù)據(jù)

DROP TABLE ord_order;

-- 創(chuàng)建訂單分區(qū)表

CREATE TABLE ord_order(

order_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '訂單ID',

user_id INT NOT NULL COMMENT '用戶ID',

goods_id INT NOT NULL COMMENT '商品ID',

order_price INT NOT NULL DEFAULT 0 COMMENT '訂單實(shí)際價(jià)格(分)',

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',

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)

);

-- 插入相關(guān)數(shù)據(jù)

INSERT INTO ord_order VALUES

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

(NULL, 10000001, 11111112, 2000, '2016-01-13 02:00:20'),

(NULL, 10000001, 11111113, 3000, '2016-01-13 03:00:30'),

(NULL, 10000001, 11111114, 4000, '2016-01-13 04:00:40'),

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

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

(NULL, 10000001, 11111112, 2000, '2016-02-13 02:00:20'),

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

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

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

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

(NULL, 10000001, 11111112, 2000, '2016-03-13 02:00:20'),

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

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

(NULL, 10000001, 11111115, 5000, '2016-03-13 05:00:50'),

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

(NULL, 10000001, 11111112, 2000, '2016-04-13 02:00:20'),

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

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

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

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

(NULL, 10000001, 11111112, 2000, '2016-05-13 02:00:20'),

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

(NULL, 10000001, 11111114, 4000, '2016-05-13 04:00:40'),

(NULL, 10000001, 11111115, 5000, '2016-05-13 05:00:50'),

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

(NULL, 10000001, 11111112, 2000, '2016-06-13 02:00:20'),

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

(NULL, 10000001, 11111114, 4000, '2016-06-13 04:00:40'),

(NULL, 10000001, 11111115, 5000, '2016-06-13 05:00:50'),

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

(NULL, 10000001, 11111112, 2000, '2016-07-13 02:00:20'),

(NULL, 10000001, 11111113, 3000, '2016-07-13 03:00:30'),

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

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

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

(NULL, 10000001, 11111112, 2000, '2016-08-13 02:00:20'),

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

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

(NULL, 10000001, 11111115, 5000, '2016-08-13 05:00:50'),

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

(NULL, 10000001, 11111112, 2000, '2016-09-13 02:00:20'),

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

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

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

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

(NULL, 10000001, 11111112, 2000, '2016-10-13 02:00:20'),

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

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

(NULL, 10000001, 11111115, 5000, '2016-10-13 05:00:50'),

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

(NULL, 10000001, 11111112, 2000, '2016-11-13 02:00:20'),

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

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

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

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

(NULL, 10000001, 11111112, 2000, '2016-12-13 02:00:20'),

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

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

(NULL, 10000001, 11111115, 5000, '2016-12-13 05:00:50');

-- 查看分區(qū)p201601數(shù)據(jù)

SELECT * FROM ord_order PARTITION(p201601);

-- 組合成的 row key

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

FROM ord_order PARTITION(p201601);

結(jié)合HBase咯

創(chuàng)建HBase表 ord_order

由于版本兼容的問題，這邊我需要先創(chuàng)建好HBase對(duì)應(yīng)的表。不然會(huì)報(bào)不能自動(dòng)創(chuàng)建 column family 的錯(cuò)誤。

使用 hbase shell 創(chuàng)建 ord_order 表

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

使用 Sqoop 將MySQL的ord_order 表的 p201601 分區(qū)的數(shù)據(jù)導(dǎo)入HBase表。

/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

導(dǎo)入成功后就可以在MySQL上面將相關(guān)分區(qū)刪除，并且創(chuàng)建之后需要的分區(qū):

ALTER TABLE ord_order

ADD PARTITION (PARTITION p201701 VALUES IN (201701));

ALTER TABLE ord_order DROP PARTITION p201601;

查看Hbase中導(dǎo)入的數(shù)據(jù)

hbase(main):001:0> scan 'ord_order'

ROW COLUMN+CELL

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

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

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

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

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

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

10000001854737838011111112 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 02:00:20.0

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

10000001854738199011111111 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 01:00:10.0

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

5 row(s) in 0.5390 seconds

ROW KEY 設(shè)計(jì)詳解

HBase中的row key為 user_id, 10000000000-UNIX_TIMESTAMP(create_time), goods_id 3個(gè)字段組成。

這邊值得注意的是 10000000000-UNIX_TIMESTAMP(create_time), 這樣設(shè)計(jì)的原因是為了讓訂單能按時(shí)間的倒序排列, 這樣就符合 越新的數(shù)據(jù)越先顯示

如: 現(xiàn)在需要對(duì)用戶 10000001 的訂單進(jìn)行分頁(yè), 每頁(yè)兩條數(shù)據(jù), 并且按時(shí)間的倒序排序(最新訂單最先顯示)

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

ROW COLUMN+CELL

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

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

點(diǎn)擊下一頁(yè)的數(shù)據(jù):

hbase(main):004: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

上面獲得了三行數(shù)據(jù)，在實(shí)際展現(xiàn)的時(shí)候去除第一行就好了，實(shí)際展示如下:

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

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

點(diǎn)擊上一頁(yè)

hbase(main):008:0> scan 'ord_order', {COLUMNS=>['cf1:order_price'], LIMIT=>3, STARTROW=>'10000001854737477011111113', 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

上面同樣獲得了三條數(shù)據(jù)，我們需要去除第一行，讓后按數(shù)據(jù)集合倒序顯示

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

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

↓↓↓↓↓ 上面兩行是集合數(shù)據(jù) 下面兩行數(shù)倒序遍歷集合的數(shù)據(jù)(也是最終顯示的數(shù)據(jù))

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

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

關(guān)于MySQL分區(qū)表和HBase的關(guān)系是什么問題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

分享文章：MySQL分區(qū)表和HBase的關(guān)系是什么
文章出自：http://jinyejixie.com/article22/psppjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、網(wǎng)站導(dǎo)航、定制網(wǎng)站、網(wǎng)站維護(hù)、移動(dòng)網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)