A kind of multiple HBase table correlating methods based on snowflake model
Technical field
The present invention relates to big data technical field, specifically a kind of multiple HBase tables association based on snowflake model
Method.
Background technology
For industry big data business demand, the Computational frame and system of data-oriented intensive applications continuously emerge, such as
MapReduce programming models, NoSQL high concurrent real-time data bases, internal memory computing engines and Stream Processing framework etc..It is at these
In system, the high concurrent real-time data base towards mass data is due to can solve the problem that traditional Web application extensions performance bottleneck, using most
To be extensive.
HBase is exactly a high reliability, high-performance, towards row, telescopic distributed memory system, utilizes HBase
Technology can erect large-scale structure storage cluster on cheap PC Server.HBase is opening for Google Bigtable
Realize that HBase is according to rowkey in source(Row major key), column key (columnFamily+qualifier), timestamp
The three-dimensional order storage of (timestamp) three part composition, the three-dimensional structure constitutes HBase KeyValue data plus value values
Structure, support changes to the additions and deletions for rowkey looks into and scan operation.
HBase improves the recall precision towards big data due to establishing towards rowkey global indexs, but by
It is to exchange high-performance for by simplifying data model in HBase, therefore does not support SQL, the tradition such as secondary index, multirow affairs is closed
It is the indispensable function of database.In addition, because the table in HBase is independent, it is impossible to support the association of two tables and multilist
Operation.However, current many business scenarios are designed according to ER conceptual models and BCNF normal forms, exist in data model a large amount of
Incidence relation, therefore multilist is operation associated most important for many business scenarios.Under big data scene, in order that
HBase fully can meet business demand, it is necessary to set up the multilist correlating method based on HBase basic operations.
The content of the invention
The technical assignment of the present invention is to provide a kind of major key by reasonably designing multiple HBase tables, and reduction table is swept
Retouch number of times and the circulating level of execution logic, realize high concurrent towards mass data, low latency HBase multilist it is operation associated
A kind of multiple HBase table correlating methods based on snowflake model.
The technical assignment of the present invention is realized in the following manner:
A kind of multiple HBase table correlating methods based on snowflake model, HBase table includes inquiring about into oral thermometer, master fact
The true table of table, supplement and dimension table;Inquire about includes input condition into oral thermometer(input)With object in master fact table(object)One
To many map informations;Master fact table includes each entry of each object(item)Managing detailed catalogue, including the external key of dimension table
(fk);The true table of supplement is included to each entry in master fact table(item)Side information, including the external key of dimension table
(fk);Dimension table includes the external key related information to master fact table, the true table of supplement;
Because HBase major key is the scanning based on scope by lexcographical order tissue(scan)Expense is significantly larger than directly
Inquiry based on keyword(get)Expense, and each scan meeting causes one layer of circulation, it should scan operation time is reduced as far as possible
Number, therefore design based on snowflake model the major key of each table;
Center table and the starting point associated using master fact table as snowflake model, include inquiry in the major key of master fact table
Entrance surface condition is used as part, i.e. pk=input | object | item;
Inquiring about into oral thermometer by input condition and the design combination of object corresponding relation major key, i.e. pk=input | object is designed;
For the true table of supplement of master fact table, design combines major key pk=input with master fact table identical | object
|item;
For each dimension table major key, based on master fact table, the true off-balancesheet key design of supplement, i.e. pk=fk;
The implementation procedure of multilist association is according to input scannings to all eligible objects and entry in master fact table
object|item;According to input in oral thermometer is inquired about | object query-related informations;The basis in the true table of supplement
Input | object | item inquires about the side information of master fact table entry;According to the fk in master fact table entry, the root in dimension table
According to relevant information in pk inquiry dimension tables.
The detailed inquiry business of bank is related to five HBase tables, including inquires about into oral thermometer, master fact table, the true table of supplement
With 2 dimension tables;It is account table to inquire about into oral thermometer(b)Include identification card number(cust_no)With the corresponding relation of account (acct_no)
And log-on message;Master fact table(a)Include account(acct_no)And the main managing detailed catalogue of each operation(acct_seq), it is main
Managing detailed catalogue includes operator's code(opr_no), amount of money involved, the time;The true table of supplement is detail list of transferring accounts(c)Comprising every
The recipient mechanism of one transfer operation(op_bank_no)Details, are the supplements to master fact table;There are two in addition
Dimension table:Operator's table and institution table, operator's table(d)Include each operator(opr_no)Details, institution table(f)Bag
Include each branch(bank_cod)Details.
The detailed inquiry business of bank is related to five HBase tables, and b represents to inquire about into oral thermometer, and a represents master fact table, and c is represented
D represents operator's table, f outgoing mechanism tables in the true table of supplement, 2 dimension tables;
The operation associated of above-mentioned five HBase tables is realized, sql correlation logics are expressed as with sql:
SELECT *
FROM b, d, f,a
LEFT OUTER JOIN c ON a.acct_no = c.acct_no AND a.acct_seq = c.acct_
seq
WHERE a.acct_no = b.acct_no
AND b.cust_no LIKE ' $ cust_no%'-- customer IDs are used as parameter
AND a.opr_no = d.opr_no
AND c.op_bank_no = f.bank_cod
MIT 50;
The function of above-mentioned sql sentences is exactly that the managing detailed catalogue of its account is inquired about according to identification card number, including main managing detailed catalogue,
Operator message, managing detailed catalogue of transferring accounts, return to preceding 50 data;
The major key of each HBase table, master fact table are designed according to snowflake model(a)Major key be cust_no | acct_no |
acct_seq;
Association process following steps:
(1), in master fact table(a)It is middle that all qualified detailed cust_no are arrived according to cust_no scannings | acct_no
|acct_seq;
(2), for every detail, in account table(b)It is middle according to cust_no | acct_no inquires about all log-on messages;
(3), according to master fact table(a)In operator's code(opr_no)Inquiry operation person's table(d), obtain operator detailed
Thin information;
(4), in detail list of transferring accounts(c)Certain detail of middle inquiry account(acct_no|acct_seq)Whether it is bar of transferring accounts
Mesh;
(5), if transfer entry, then pass through institution table(f)Inquire about the recipient mechanism that transfers accounts(op_bank_no)In detail
Information.
A kind of multiple HBase table correlating methods based on snowflake model of the present invention have advantages below:Reduce HBase
The scanning times of table and the circulating level of execution logic, improve execution efficiency, can ensure the independence of each HBase table
Under the premise of, support that the HBase multilists towards the high concurrent of large-scale data, low latency are operation associated.
Embodiment
Following detailed are made to a kind of multiple HBase table correlating methods based on snowflake model of the present invention with reference to specific embodiment
Carefully illustrate.
Embodiment 1:
The present invention a kind of multiple HBase table correlating methods based on snowflake model, HBase table include inquire about into oral thermometer,
The true table of master fact table, supplement and dimension table;Inquire about includes input condition into oral thermometer(input)With object in master fact table
(object)One-to-many map information;Master fact table includes each entry of each object(item)Managing detailed catalogue, wherein wrapping
Include the external key of dimension table(fk);The true table of supplement is included to each entry in master fact table(item)Side information, including
The external key of dimension table(fk);Dimension table includes the external key related information to master fact table, the true table of supplement;
Because HBase major key is the scanning based on scope by lexcographical order tissue(scan)Expense is significantly larger than directly
Inquiry based on keyword(get)Expense, and each scan meeting causes one layer of circulation, it should scan operation time is reduced as far as possible
Number, therefore design based on snowflake model the major key of each table;
Center table and the starting point associated using master fact table as snowflake model, include inquiry in the major key of master fact table
Entrance surface condition is used as part, i.e. pk=input | object | item;
Inquiring about into oral thermometer by input condition and the design combination of object corresponding relation major key, i.e. pk=input | object is designed;
For the true table of supplement of master fact table, design combines major key pk=input with master fact table identical | object
|item;
For each dimension table major key, based on master fact table, the true off-balancesheet key design of supplement, i.e. pk=fk;
The implementation procedure of multilist association is according to input scannings to all eligible objects and entry in master fact table
object|item;According to input in oral thermometer is inquired about | object query-related informations;The basis in the true table of supplement
Input | object | item inquires about the side information of master fact table entry;According to the fk in master fact table entry, the root in dimension table
According to relevant information in pk inquiry dimension tables.
Embodiment 2:
A kind of multiple HBase table correlating methods based on snowflake model of the present invention,
The detailed inquiry business of bank is related to five HBase tables, including inquires about into oral thermometer, master fact table, the true table of supplement
With 2 dimension tables;It is account table to inquire about into oral thermometer(b)Include identification card number(cust_no)With the corresponding relation of account (acct_no)
And log-on message;Master fact table(a)Include account(acct_no)And the main managing detailed catalogue of each operation(acct_seq), it is main
Managing detailed catalogue includes operator's code(opr_no), amount of money involved, the time;The true table of supplement is detail list of transferring accounts(c)Comprising every
The recipient mechanism of one transfer operation(op_bank_no)Details, are the supplements to master fact table;There are two in addition
Dimension table:Operator's table and institution table, operator's table(d)Include each operator(opr_no)Details, institution table(f)Bag
Include each branch(bank_cod)Details.
The detailed inquiry business of bank is related to five HBase tables, and b represents to inquire about into oral thermometer, and a represents master fact table, and c is represented
D represents operator's table, f outgoing mechanism tables in the true table of supplement, 2 dimension tables;
The operation associated of above-mentioned five HBase tables is realized, sql correlation logics are expressed as with sql:
SELECT *
FROM b, d, f,a
LEFT OUTER JOIN c ON a.acct_no = c.acct_no AND a.acct_seq = c.acct_
seq
WHERE a.acct_no = b.acct_no
AND b.cust_no LIKE ' $ cust_no%'-- customer IDs are used as parameter
AND a.opr_no = d.opr_no
AND c.op_bank_no = f.bank_cod
MIT 50;
The function of above-mentioned sql sentences is exactly that the managing detailed catalogue of its account is inquired about according to identification card number, including main managing detailed catalogue,
Operator message, managing detailed catalogue of transferring accounts, return to preceding 50 data;
The major key of each HBase table, master fact table are designed according to snowflake model(a)Major key be cust_no | acct_no |
acct_seq;
Association process following steps:
(1), in master fact table(a)It is middle that all qualified detailed cust_no are arrived according to cust_no scannings | acct_no
|acct_seq;
(2), for every detail, in account table(b)It is middle according to cust_no | acct_no inquires about all log-on messages;
(3), according to master fact table(a)In operator's code(opr_no)Inquiry operation person's table(d), obtain operator detailed
Thin information;
(4), in detail list of transferring accounts(c)Certain detail of middle inquiry account(acct_no|acct_seq)Whether it is bar of transferring accounts
Mesh;
(5), if transfer entry, then pass through institution table(f)Inquire about the recipient mechanism that transfers accounts(op_bank_no)In detail
Information.
If designing the major key of each HBase table according to tree shaped model, the major key of HBase table is as follows:Account table(b)Major key
For cust_no | acct_no, master fact table(a)Major key be acct_no | acct_seq, detail list of transferring accounts(c)Major key be
Acct_no | acct_seq, operator's table(d)Major key be opr_no, institution table(f)Major key be bank_cod;
Association process following steps:
(1), in account table(b)It is middle according to identification card number(cust_no)Scan all accounts(acct_no);
(2), for each account, in master fact table(a)It is middle according to account(acct_no)Scan all eligible
Main managing detailed catalogue;
(3), for each main managing detailed catalogue, according to master fact table(a)In operator's code(opr_no)Inquiry behaviour
Work person's table(d), obtain operator's details;
(4), in detail list of transferring accounts(c)Whether the main managing detailed catalogue of each operation of middle inquiry account is transfer entry;
(5), if transfer entry, then pass through recipient mechanism(op_bank_no)Details, to institution table(f)In
Inquire about recipient mechanism details of transferring accounts;
Said process can realize five table correlation logics, it is necessary to twice sweep(scan)With three inquiries(get);HBase
The major key of table is the scanning based on scope by lexcographical order tissue(scan)Expense is significantly larger than the inquiry for being directly based upon keyword
(get)Expense, and each scan meeting causes one layer of circulation, it is especially excessive in the account of some identification card number, and it is each
In the case that the data of account are again smaller, above-mentioned logic can be absorbed in multiple circulation.
It can fast and effectively realize that HBase multilists are associated based on snowflake model, and ensure the independence of each table.It is based on
Snowflake model is with being based on tree shaped model, although major key designs redundancy, but reduces cycle-index, improves efficiency.
Under 20 node HBase clusters, the OLTP test environments of single node WebLogic and LoadRunner server,
The five tables association scene TPS of billions of datas can be less than 0.2 second more than 100 times/s, average response time.
By embodiment above, the those skilled in the art can readily realize the present invention.But should
Work as understanding, the present invention is not limited to 2 kinds of above-mentioned embodiments.On the basis of disclosed embodiment, the technology
The technical staff in field can be combined different technical characteristics, so as to realize different technical schemes.