This can be easily achieved via a lookup UDF in hive. See https://issues.apache.org/jira/browse/HIVE-758 on how hive and hbase can interact without having to write a serde.
On 8/23/09 7:43 PM, "Matt Pestritto" <[email protected]> wrote: > Hi All. I see a lot of good work being done on HBase/Hive integration > especially around how to express hbase metadata in hive and how to load data > from/to hbase/hive. > > Has any thought be been put into how to use HBase data as lookup data in a > query and not load all of the data as a normal hive query ? > > My use case is as follows: I have a table < users > with 50m users. I have > a 5gb daily clickstream file that only touchs 150k of those users on a daily > basis. It would be much more efficient if I didn't have to load all of the > data in HBase to a hive table and write a traditional hive query but just do > 150k lookups in the map ( or reduce ) phase of the MR job. If the hbase > lookups were done in realtime it would be much faster than sourcing the > original user table with 50m rows. > > Thoughts ? > > Thanks > -Matt > > > On Sun, Aug 23, 2009 at 8:20 AM, Samuel Guo (JIRA) <[email protected]> wrote: > >> >> [ >> https://issues.apache.org/jira/browse/HIVE-705?page=com.atlassian.jira.plugin >> .system.issuetabpanels:comment-tabpanel&focusedCommentId=12746592#action_1274 >> 6592] >> >> Samuel Guo commented on HIVE-705: >> --------------------------------- >> >> Attach a new patch. >> >> 1) move the related hbase code to the contrib package, as hbase just an >> optional storage for hive, not neccessary. >> I have tried to avoid modifying the hive original code and just add a hbase >> serde to connect hive with hbase. But the hbase storage model is quite >> different with file storage model. For example, a loadwork is used to >> rename/copy files from temp dir to the target table's dir if a query's >> target is a hive table. But in a hbased hive table, we can't rename a table >> now. So it's hard to let a hbased hive table to follow the logic of a normal >> file-based hive table. So I add some code(HiveFormatUtils) to distinguish a >> file-based table from a not-file-based table. >> >> 2) fix some bugs in the draft patch, such as "select *" return nothing. >> >> >> ----------------------------------------------------------------------------- >> ----------------- >> >> How to use the hbase as hive's storage? >> >> 1) remember to add the contrib jar and the hbase jar in the hive's auxPath, >> so m/r can populate the neccessary hbase-related jars to the whole hadoop >> m/r cluster. >> >>> $HIVE_HOME/bin/hive -auxPath ${contrib_jar},${hbase_jar} >> >> 2) modify the configuration to add the following configuration parameters. >> >> "hbase.master" : pointer to the hbase's master. >> "hive.othermetadata.handlers" : >> "org.apache.hadoop.hive.contrib.hbase.HiveHBaseTableInputFormat:org.apache.ha >> doop.hive.contrib.hbase.HBaseMetadataHandler" >> >> "hive.othermetadata.handlers" collects the metadata handlers to handle the >> other metadata operations in the not-file-based hive tables. Take hbase as >> an example. HBaseMetadataHandler will create the neccessary hbase table and >> its family columns when we create a hbased hive table from hive's client. It >> also drop the hbase table when we drop the hive table. >> >> The metastore read the registered handlers map from the configuration file >> during initialization. The registered handlers map is formated as >> "table_format_classname:table_metadata_handler_classname,table_format_classna >> me:table_metadata_handler_classname,...". >> >> 3) enjoy "hive over hbase"! >> >> ------------------------------------------------------------------------ >> >> Other problems. >> >> 1) Altering a hased-hive table is not supported now. :( >> renaming a table in hbase is not supported now, so I just do not support >> rename operation. ( maybe if we rename a hive table, we do not need to >> rename the base hbase table.) >> >> adding/replacing cloumns. >> Now we need to specify the schema mapping in the SerDe properties >> explicitly. If we want to adding columns, we need to call 'alter' twice to >> adding columns: change the serde properties and the hive columns. Either >> change the serde properties first or change the hive columns first will fail >> now, because we validate the schema mapping during SerDe initialization. One >> of the hbase serde validation is to check the counts of hive columns and >> hbase mapping columns. If we first change the hive columns, the number of >> hive columns will be more than hbase mapping columns, the HBase Serde >> initialization will fail this alter operation. (maybe we need to remove the >> validation code from HBaseSerDe initialization and do it in other place?) >> >> 2) more flexible schema mapping? >> As Schubert metioned before, more flexible schema mapping will be useful >> for user. This feature will be added later. >> >> >> welcome for comments~ >> >> >> >> >>> Let Hive can analyse hbase's tables >>> ----------------------------------- >>> >>> Key: HIVE-705 >>> URL: https://issues.apache.org/jira/browse/HIVE-705 >>> Project: Hadoop Hive >>> Issue Type: New Feature >>> Reporter: Samuel Guo >>> Attachments: hbase-0.19.3-test.jar, hbase-0.19.3.jar, >> HIVE-705_draft.patch, HIVE-705_revision806905.patch >>> >>> >>> Add a serde over the hbase's tables, so that hive can analyse the data >> stored in hbase easily. >> >> -- >> This message is automatically generated by JIRA. >> - >> You can reply to this email to add a comment to the issue online. >> >>
