--- title: 'Season II: 6. Use Pig and Hive with HBase' author: admin type: post date: 2013-10-21T20:41:35+00:00 url: /hadoop-tutorial-use-pig-and-hive-with-hbase/ tumblr_gethue_permalink: - http://gethue.tumblr.com/post/64707633719/hadoop-tutorial-use-pig-and-hive-with-hbase tumblr_gethue_id: - 64707633719 sf_thumbnail_type: - none sf_thumbnail_link_type: - link_to_post sf_detail_type: - none sf_page_title: - 1 sf_page_title_style: - standard sf_no_breadcrumbs: - 1 sf_page_title_bg: - none sf_page_title_text_style: - light sf_background_image_size: - cover sf_social_sharing: - 1 sf_sidebar_config: - left-sidebar sf_left_sidebar: - Sidebar-2 sf_right_sidebar: - Sidebar-1 sf_caption_position: - caption-right categories: - Browsing - Querying - Tutorial ---
The HBase app is an elegant way to visualize and search a lot of data. Apache HBase tables can be tricky to update as they require lower level API. Some good alternative for simplifying the data management or access is to use Apache Pig or Hive.
In this post we are going to show how to load our yelp data from the [Oozie Bundles][1] episode into HBase with Hive. Then we will use the [HBase Browser][2] to visualize it and Pig to compute some statistics. {{< youtube Qpll_XbUEf0 >}} # Access HBase with Hive First, let’s use Beeswax to create a Hive table that is persisted as a HBase table. The script works as intended when using HiveServer2 as the Hive backend. Some HBase jar need to be registered, as shown in the video. In our use case of Yelp data, [map][3] is the correct data type for our HBase that will created as [EXTERNAL][3]. Here is the [create table statement][4] for creating a table that is going to store the top N coolest restaurants for everyday:set hbase.zookeeper.quorum my-hbase.com
CREATE TABLE top_cool_hbase (key string, value map<string, int>)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,review:")
TBLPROPERTIES ("hbase.table.name" = "top_cool");
In order to allow Hive to use HBase some jars need to be registered (one by session). Upload them on HDFS and add them as resources in the first create table query:
/usr/lib/hive/lib/zookeeper.jar; /usr/lib/hive/lib/hbase.jar; /usr/lib/hive/lib/hive-hbase-handler-0.XX.0-cdhX.X.X.jar /usr/lib/hive/lib/guava-11.0.2.jar;Then lets add data to our new table. We copy it from our top_cool table of the [previous episode][1].
INSERT OVERWRITE TABLE top_cool_hbase SELECT name, map(`date`, cast(coolness as int)) FROM top_coolIf you don’t have the table from the past episode, you can still use the one from [episode one][5] as a workaround:
INSERT OVERWRITE TABLE top_cool_hbase SELECT name, map(`date`, cast(r.stars as int)) FROM review r JOIN business b ON r.business_id = b.business_id;Access HBase with HBase Browser As seen in the video, the HBase app provides a slick new Web interface to HBase. # Access HBase with Pig Pig comes with some built-in [HBaseStorage][6] and HBaseLoader. After registering two jars, you will be able to use them. Here is the [script][7] for dumping all the counts of a particular day:
REGISTER /usr/lib/zookeeper/zookeeper-3.4.5-cdhX.X.X.jar
REGISTER /usr/lib/hbase/hbase-0.94.6-cdhX.X.X-security.jar
set hbase.zookeeper.quorum 'localhost'
data = LOAD 'hbase://top_cool'
USING org.apache.pig.backend.hadoop.hbase.HBaseStorage('review:*', '-loadKey true')
as (name:CHARARRAY, dates:MAP[]);
counts =
FOREACH data
GENERATE name, dates#'2012-12-02';
DUMP counts;
# Sum-up
Hive and Pig are excellent tools for manipulating HBase data. All combinations are possible, the sky is the limit! For example you could load from HBase and save into Hive table with Pig or use Hive SQL to query HBase tables. You can even pull HDFS or Hive data from Pig with [Hcatalog][8], save it into HBase (or vice versa) and browse it with HBase Browser!
Next time, let’s see how to create a search engine from the Yelp data!
As usual, if you have questions or feedback, feel free to contact the Hue community on [hue-user][9] or [@gethue.com][10]!
[1]: http://gethue.tumblr.com/post/63988110361/hadoop-tutorial-bundle-oozie-coordinators-with-hue
[2]: http://gethue.tumblr.com/post/59071544309/the-web-ui-for-hbase-hbase-browser
[3]: https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration
[4]: https://github.com/romainr/hadoop-tutorials-examples/blob/master/hbase-hive-pig/create_hbase_table.sql
[5]: http://gethue.tumblr.com/post/60376973455/hadoop-tutorials-ii-1-prepare-the-data-for-analysis
[6]: http://pig.apache.org/docs/r0.11.1/func.html#HBaseStorage
[7]: https://github.com/romainr/hadoop-tutorials-examples/blob/master/hbase-hive-pig/load_hbase.pig
[8]: http://gethue.tumblr.com/post/56804308712/hadoop-tutorial-how-to-access-hive-in-pig-with
[9]: http://groups.google.com/a/cloudera.org/group/hue-user
[10]: http://twitter.com/gethue