\u5570\u54e9\u516b\u55e6<\/strong><\/div>\n
\u5176\u5b9e\u8be5\u4e0d\u8be5\u7528\uff0c\u6211\u4e5f\u4e0d\u80fd\u7ed9\u4e88\u5f88\u597d\u7684\u5efa\u8bae\u3002\u8fd8\u662f\u90a3\u53e5\u8bdd\uff0c\u89c9\u5f97\u9002\u5408\u81ea\u5df1\u624d\u662f\u6700\u597d\u7684\u3002\u89c9\u5f97\u81ea\u5df1\u53ef\u4ee5\u641e\u5b9a\u5206\u533a\u8868\u90a3\u5c31\u7528\u3002<\/p>\n
\u6211\u591a\u8651\u4e86<\/strong><\/div>\n
\n
\u597d\u5f88\u597d\u7684\u4f7f\u7528\u5206\u533a\u8868\u5c31\u9700\u8981\u505a\u597d\u5bf9\u5f00\u53d1\u4eba\u5458\u57f9\u8bad\u7684\u51c6\u5907\uff0c\u8ba9\u4ed6\u4eec\u77e5\u9053\u8981\u600e\u4e48\u6837\u624d\u80fd\u5f88\u597d\u7684\u4f7f\u7528\u5206\u533a\u8868\u3002<\/li>\n
\u6700\u597d\u9700\u8981\u6709\u4e00\u4e2a\u81ea\u52a8\u5316\u7684\u8ba1\u5212\uff0c\u5b9a\u65f6\u7684\u81ea\u52a8\u5904\u7406\u5206\u533a\u7684\u95ee\u9898\u3002<\/li>\n
DELETE \u5220\u9664\u6570\u636e\u4e0d\u4f1a\u91ca\u653e\u78c1\u76d8\u7a7a\u95f4\uff0cDROP PARITION \u4f1a\u91ca\u653e\u78c1\u76d8\u7a7a\u95f4\uff0c\u8fd9\u6837\u8282\u7701\u4e86\u7a7a\u95f4\u7684\u540c\u65f6\uff0c\u4e5f\u4e0d\u4f1a\u56e0\u4e3adelete\u6807\u8bb0\u6570\u636e\u8fc7\u591a\u5e26\u6765\u6027\u80fd\u95ee\u9898\u3002<\/li>\n<\/ol>\n
\u5f00\u5e72\u4e86<\/strong><\/div>\n
\u521b\u5efa MySQL \u5206\u533a\u6570\u636e<\/p>\n
DROP TABLE ord_order;\r\n\r\n-- \u521b\u5efa\u8ba2\u5355\u5206\u533a\u8868\r\nCREATE TABLE ord_order(\r\norder_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '\u8ba2\u5355ID',\r\nuser_id INT NOT NULL COMMENT '\u7528\u6237ID',\r\ngoods_id INT NOT NULL COMMENT '\u5546\u54c1ID',\r\norder_price INT NOT NULL DEFAULT 0 COMMENT '\u8ba2\u5355\u5b9e\u9645\u4ef7\u683c(\u5206)',\r\ncreate_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '\u521b\u5efa\u65f6\u95f4',\r\nPRIMARY KEY(order_id, create_time)\r\n)\r\nPARTITION BY LIST (YEAR(create_time)*100 + MONTH(create_time))\r\n(\r\nPARTITION p201601 VALUES IN (201601),\r\nPARTITION p201602 VALUES IN (201602),\r\nPARTITION p201603 VALUES IN (201603),\r\nPARTITION p201604 VALUES IN (201604),\r\nPARTITION p201605 VALUES IN (201605),\r\nPARTITION p201606 VALUES IN (201606),\r\nPARTITION p201607 VALUES IN (201607),\r\nPARTITION p201608 VALUES IN (201608),\r\nPARTITION p201609 VALUES IN (201609),\r\nPARTITION p201610 VALUES IN (201610),\r\nPARTITION p201611 VALUES IN (201611),\r\nPARTITION p201612 VALUES IN (201612)\r\n);\r\n\r\n-- \u63d2\u5165\u76f8\u5173\u6570\u636e\r\nINSERT INTO ord_order VALUES\r\n(NULL, 10000001, 11111111, 1000, '2016-01-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-01-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-01-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-01-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-01-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-02-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-02-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-02-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-02-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-02-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-03-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-03-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-03-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-03-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-03-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-04-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-04-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-04-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-04-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-04-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-05-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-05-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-05-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-05-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-05-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-06-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-06-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-06-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-06-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-06-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-07-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-07-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-07-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-07-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-07-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-08-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-08-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-08-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-08-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-08-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-09-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-09-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-09-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-09-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-09-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-10-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-10-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-10-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-10-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-10-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-11-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-11-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-11-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-11-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-11-13 05:00:50'),\r\n(NULL, 10000001, 11111111, 1000, '2016-12-13 01:00:10'),\r\n(NULL, 10000001, 11111112, 2000, '2016-12-13 02:00:20'),\r\n(NULL, 10000001, 11111113, 3000, '2016-12-13 03:00:30'),\r\n(NULL, 10000001, 11111114, 4000, '2016-12-13 04:00:40'),\r\n(NULL, 10000001, 11111115, 5000, '2016-12-13 05:00:50');\r\n\r\n-- \u67e5\u770b\u5206\u533ap201601\u6570\u636e\r\nSELECT * FROM ord_order PARTITION(p201601);\r\n\r\n-- \u7ec4\u5408\u6210\u7684 row key\r\nSELECT CONCAT(user_id, 10000000000-UNIX_TIMESTAMP(create_time), goods_id)\r\nFROM ord_order PARTITION(p201601);<\/pre>\n
\u7ed3\u5408HBase\u54af<\/strong><\/div>\n
\u521b\u5efaHBase\u8868 ord_order<\/p>\n
\u7531\u4e8e\u7248\u672c\u517c\u5bb9\u7684\u95ee\u9898\uff0c\u8fd9\u8fb9\u6211\u9700\u8981\u5148\u521b\u5efa\u597dHBase\u5bf9\u5e94\u7684\u8868\u3002\u4e0d\u7136\u4f1a\u62a5\u4e0d\u80fd\u81ea\u52a8\u521b\u5efa column family \u7684\u9519\u8bef\u3002<\/p>\n
\u4f7f\u7528 hbase shell \u521b\u5efa ord_order \u8868<\/p>\n
hbase(main):033:0> create 'ord_order', {NAME => 'cf1'}<\/pre>\n
\u4f7f\u7528 Sqoop \u5c06MySQL\u7684ord_order \u8868\u7684 p201601 \u5206\u533a\u7684\u6570\u636e\u5bfc\u5165HBase\u8868\u3002<\/p>\n
\/usr\/local\/sqoop\/bin\/sqoop import \\\r\n--connect jdbc:mysql:\/\/192.168.137.11:3306\/test\u2002\\\r\n--username HH \\\r\n--password oracle \\\r\n--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' \\\r\n--hbase-table ord_order \\\r\n--hbase-create-table \\\r\n--hbase-row-key order_id \\\r\n--split-by order_id \\\r\n--column-family cf1 \\\r\n-m 1<\/pre>\n
\u5bfc\u5165\u6210\u529f\u540e\u5c31\u53ef\u4ee5\u5728MySQL\u4e0a\u9762\u5c06\u76f8\u5173\u5206\u533a\u5220\u9664\uff0c\u5e76\u4e14\u521b\u5efa\u4e4b\u540e\u9700\u8981\u7684\u5206\u533a<\/p>\n
ALTER TABLE ord_order\r\nADD PARTITION (PARTITION p201701 VALUES IN (201701));\r\n\r\nALTER TABLE ord_order DROP PARTITION p201601;<\/pre>\n
\u67e5\u770bHbase\u4e2d\u5bfc\u5165\u7684\u6570\u636e<\/p>\n
hbase(main):001:0> scan 'ord_order'\r\nROW COLUMN+CELL\r\n10000001854736755011111115 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 05:00:50.0\r\n10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000\r\n10000001854737116011111114 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 04:00:40.0\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000\r\n10000001854737477011111113 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 03:00:30.0\r\n10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000\r\n10000001854737838011111112 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 02:00:20.0\r\n10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000\r\n10000001854738199011111111 column=cf1:create_time, timestamp=1479224942888, value=2016-01-13 01:00:10.0\r\n10000001854738199011111111 column=cf1:order_price, timestamp=1479224942888, value=1000\r\n5 row(s) in 0.5390 seconds<\/pre>\n
ROW KEY \u8bbe\u8ba1\u8be6\u89e3<\/strong><\/div>\n
HBase\u4e2d\u7684row key\u4e3a user_id, 10000000000-UNIX_TIMESTAMP(create_time), goods_id 3\u4e2a\u5b57\u6bb5\u7ec4\u6210\u3002<\/p>\n
\u8fd9\u8fb9\u503c\u5f97\u6ce8\u610f\u7684\u662f 10000000000-UNIX_TIMESTAMP(create_time), \u8fd9\u6837\u8bbe\u8ba1\u7684\u539f\u56e0\u662f\u4e3a\u4e86\u8ba9\u8ba2\u5355\u80fd\u6309\u65f6\u95f4\u7684\u5012\u5e8f\u6392\u5217, \u8fd9\u6837\u5c31\u7b26\u5408 \u8d8a\u65b0\u7684\u6570\u636e\u8d8a\u5148\u663e\u793a<\/p>\n
\u5982: \u73b0\u5728\u9700\u8981\u5bf9\u7528\u6237 10000001 \u7684\u8ba2\u5355\u8fdb\u884c\u5206\u9875, \u6bcf\u9875\u4e24\u6761\u6570\u636e, \u5e76\u4e14\u6309\u65f6\u95f4\u7684\u5012\u5e8f\u6392\u5e8f(\u6700\u65b0\u8ba2\u5355\u6700\u5148\u663e\u793a)<\/p>\n
hbase(main):003:0> scan 'ord_order', {COLUMNS=>['cf1:order_price'], ROWPREFIXFILTER=>'10000001', LIMIT=>2}\r\nROW COLUMN+CELL\r\n10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000<\/pre>\n
\u70b9\u51fb\u4e0b\u4e00\u9875\u7684\u6570\u636e:<\/b><\/strong><\/p>\n
hbase(main):004:0> scan 'ord_order', {COLUMNS=>['cf1:order_price'], LIMIT=>3, STARTROW=>'10000001854737116011111114'}\r\nROW COLUMN+CELL\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000\r\n10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000\r\n10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000\r\n3 row(s) in 0.0260 seconds\r\n\u4e0a\u9762\u83b7\u5f97\u4e86\u4e09\u884c\u6570\u636e\uff0c\u5728\u5b9e\u9645\u5c55\u73b0\u7684\u65f6\u5019\u53bb\u9664\u7b2c\u4e00\u884c\u5c31\u597d\u4e86\uff0c\u5b9e\u9645\u5c55\u793a\u5982\u4e0b:\r\n10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000\r\n10000001854737838011111112 column=cf1:order_price, timestamp=1479224942888, value=2000<\/pre>\n
\u70b9\u51fb\u4e0a\u4e00\u9875<\/b><\/strong><\/p>\n
hbase(main):008:0> scan 'ord_order', {COLUMNS=>['cf1:order_price'], LIMIT=>3, STARTROW=>'10000001854737477011111113', REVERSED=>true}\r\nROW COLUMN+CELL\r\n10000001854737477011111113 column=cf1:order_price, timestamp=1479224942888, value=3000\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000\r\n10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000\r\n3 row(s) in 0.0640 seconds\r\n\u4e0a\u9762\u540c\u6837\u83b7\u5f97\u4e86\u4e09\u6761\u6570\u636e\uff0c\u6211\u4eec\u9700\u8981\u53bb\u9664\u7b2c\u4e00\u884c\uff0c\u8ba9\u540e\u6309\u6570\u636e\u96c6\u5408\u5012\u5e8f\u663e\u793a\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000\r\n10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000\r\n\u2193\u2193\u2193\u2193\u2193 \u4e0a\u9762\u4e24\u884c\u662f\u96c6\u5408\u6570\u636e \u4e0b\u9762\u4e24\u884c\u6570\u5012\u5e8f\u904d\u5386\u96c6\u5408\u7684\u6570\u636e(\u4e5f\u662f\u6700\u7ec8\u663e\u793a\u7684\u6570\u636e)\r\n10000001854736755011111115 column=cf1:order_price, timestamp=1479224942888, value=5000\r\n10000001854737116011111114 column=cf1:order_price, timestamp=1479224942888, value=4000<\/pre>\n
\u603b\u7ed3<\/strong><\/div>\n
\u5bf9 HBase \u7684\u8bbe\u8ba1\u548c\u4f7f\u7528\u662f\u95e8\u5b66\u95ee\uff0c\u5728\u5b9e\u9645\u4f7f\u7528\u4e2d\uff0c\u4e00\u822c\u9700\u8981\u548c\u5f00\u53d1\u4ea7\u54c1\u4e00\u8d77\u8ba8\u8bba\u5982\u4f55\u8bbe\u8ba1 row key \u6bd4\u8f83\u597d\u3002\u5f53\u7136\uff0c\u4e00\u822c\u591a\u591a\u9605\u8bfb\u8fc7\u6765\u4eba\u7684\u7ecf\u9a8c\u5f80\u5f80\u4e5f\u80fd\u591f\u89e3\u51b3\u5f88\u591a\u95ee\u9898\u3002\u56e0\u4e3a\u4f60\u9047\u5230\u7684\u95ee\u9898\uff0c\u522b\u4eba\u53ef\u80fd\u4e5f\u4f1a\u9047\u5230\u3002<\/p>\n
\n
\u539f\u6587\u6765\u81ea\uff1ahttp:\/\/www.ttlsa.com\/database\/mysql-partition-and-hbase-marry\/<\/a><\/p>\n
\u672c\u6587\u5730\u5740\uff1a http:\/\/lrxjmw.cn\/mysql-partition-hbase-combine.html<\/a>\u7f16\u8f91\uff1a\u6768\u9e4f\u98de\uff0c\u5ba1\u6838\u5458\uff1a\u5cb3\u56fd\u5e05<\/span><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
\u5bfc\u8bfb \u65f6\u5e38\u90fd\u4f1a\u6709\u4eba\u95eeMySQL\u5206\u533a\u8868\u8981\u5982\u4f55\u4f7f\u7528\uff0cMySQL\u5206\u533a\u8868\u7684\u548cOracle\u7684\u5dee\u8fdc\u4e86\uff0c\u8be5\u4e0d\u8be5\u7528MySQL\u5206 […]<\/p>\n","protected":false},"author":63,"featured_media":31294,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-45576","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/45576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/63"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=45576"}],"version-history":[{"count":5,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/45576\/revisions"}],"predecessor-version":[{"id":269339,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/45576\/revisions\/269339"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/31294"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=45576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=45576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=45576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}