THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
Hive HBase Integration
| Table of Contents |
|---|
| Info | ||
|---|---|---|
| ||
As of Hive 0.9.0 the HBase integration requires at least HBase 0.92, earlier versions of Hive were working with HBase 0.89/0.90 |
...
Here's an example using CLI from a source build environment, targeting a single-node HBase server. (Note that the jar locations and names have changed in Hive 0.9.0, so for earlier releases, some changes are needed.)
| No Format |
|---|
$HIVE_SRC/build/dist/bin/hive --auxpath $HIVE_SRC/build/dist/lib/hive-hbase-handler-0.9.0.jar,$HIVE_SRC/build/dist/lib/hbase-0.92.0.jar,$HIVE_SRC/build/dist/lib/zookeeper-3.3.4.jar,$HIVE_SRC/build/dist/lib/guava-r09.jar --hiveconf hbase.master=hbase.yoyodyne.com:60000 |
Here's an example which instead targets a distributed HBase cluster where a quorum of 3 zookeepers is used to elect the HBase master:
| No Format |
|---|
$HIVE_SRC/build/dist/bin/hive --auxpath $HIVE_SRC/build/dist/lib/hive-hbase-handler-0.9.0.jar,$HIVE_SRC/build/dist/lib/hbase-0.92.0.jar,$HIVE_SRC/build/dist/lib/zookeeper-3.3.4.jar,$HIVE_SRC/build/dist/lib/guava-r09.jar --hiveconf hbase.zookeeper.quorum=zk1.yoyodyne.com,zk2.yoyodyne.com,zk3.yoyodyne.com |
...
In order to create a new HBase table which is to be managed by Hive, use the STORED BY clause on CREATE TABLE:
| No Format |
|---|
CREATE TABLE hbase_table_1(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val")
TBLPROPERTIES ("hbase.table.name" = "xyz");
|
...
After executing the command above, you should be able to see the new (empty) table in the HBase shell:
| No Format |
|---|
$ hbase shell
HBase Shell; enter 'help<RETURN>' for list of supported commands.
Version: 0.20.3, r902334, Mon Jan 25 13:13:08 PST 2010
hbase(main):001:0> list
xyz
1 row(s) in 0.0530 seconds
hbase(main):002:0> describe "xyz"
DESCRIPTION ENABLED
{NAME => 'xyz', FAMILIES => [{NAME => 'cf1', COMPRESSION => 'NONE', VE true
RSIONS => '3', TTL => '2147483647', BLOCKSIZE => '65536', IN_MEMORY =>
'false', BLOCKCACHE => 'true'}]}
1 row(s) in 0.0220 seconds
hbase(main):003:0> scan "xyz"
ROW COLUMN+CELL
0 row(s) in 0.0060 seconds
|
...
Here's how to move data from Hive into the HBase table (see GettingStarted for how to create the example table pokes in Hive first):
| No Format |
|---|
INSERT OVERWRITE TABLE hbase_table_1 SELECT * FROM pokes WHERE foo=98;
|
Use HBase shell to verify that the data actually got loaded:
| No Format |
|---|
hbase(main):009:0> scan "xyz"
ROW COLUMN+CELL
98 column=cf1:val, timestamp=1267737987733, value=val_98
1 row(s) in 0.0110 seconds
|
And then query it back via Hive:
| No Format |
|---|
hive> select * from hbase_table_1;
Total MapReduce jobs = 1
Launching Job 1 out of 1
...
OK
98 val_98
Time taken: 4.582 seconds
|
Inserting large amounts of data may be slow due to WAL overhead; if you would like to disable this, make sure you have HIVE-1383 (as of Hive 0.6), and then issue this command before the INSERT:
| No Format |
|---|
set hive.hbase.wal.enabled=false;
|
...
If you want to give Hive access to an existing HBase table, use CREATE EXTERNAL TABLE:
| No Format |
|---|
CREATE EXTERNAL TABLE hbase_table_2(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf1:val")
TBLPROPERTIES("hbase.table.name" = "some_existing_table");
|
...
Here's an example with three Hive columns and two HBase column families, with two of the Hive columns (value1 and value2) corresponding to one of the column families (a, with HBase column names b and c), and the other Hive column corresponding to a single column (e) in its own column family (d).
| No Format |
|---|
CREATE TABLE hbase_table_1(key int, value1 string, value2 int, value3 int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,a:b,a:c,d:e"
);
INSERT OVERWRITE TABLE hbase_table_1 SELECT foo, bar, foo+1, foo+2
FROM pokes WHERE foo=98 OR foo=100;
|
Here's how this looks in HBase:
| No Format |
|---|
hbase(main):014:0> describe "hbase_table_1"
DESCRIPTION ENABLED
{NAME => 'hbase_table_1', FAMILIES => [{NAME => 'a', COMPRESSION => 'N true
ONE', VERSIONS => '3', TTL => '2147483647', BLOCKSIZE => '65536', IN_M
EMORY => 'false', BLOCKCACHE => 'true'}, {NAME => 'd', COMPRESSION =>
'NONE', VERSIONS => '3', TTL => '2147483647', BLOCKSIZE => '65536', IN
_MEMORY => 'false', BLOCKCACHE => 'true'}]}
1 row(s) in 0.0170 seconds
hbase(main):015:0> scan "hbase_table_1"
ROW COLUMN+CELL
100 column=a:b, timestamp=1267740457648, value=val_100
100 column=a:c, timestamp=1267740457648, value=101
100 column=d:e, timestamp=1267740457648, value=102
98 column=a:b, timestamp=1267740457648, value=val_98
98 column=a:c, timestamp=1267740457648, value=99
98 column=d:e, timestamp=1267740457648, value=100
2 row(s) in 0.0240 seconds
|
And when queried back into Hive:
| No Format |
|---|
hive> select * from hbase_table_1;
Total MapReduce jobs = 1
Launching Job 1 out of 1
...
OK
100 val_100 101 102
98 val_98 99 100
Time taken: 4.054 seconds
|
...
Here's how a Hive MAP datatype can be used to access an entire column family. Each row can have a different set of columns, where the column names correspond to the map keys and the column values correspond to the map values.
| No Format |
|---|
CREATE TABLE hbase_table_1(value map<string,int>, row_key int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "cf:,:key"
);
INSERT OVERWRITE TABLE hbase_table_1 SELECT map(bar, foo), foo FROM pokes
WHERE foo=98 OR foo=100;
|
...
Here's how this looks in HBase (with different column names in different rows):
| No Format |
|---|
hbase(main):012:0> scan "hbase_table_1"
ROW COLUMN+CELL
100 column=cf:val_100, timestamp=1267739509194, value=100
98 column=cf:val_98, timestamp=1267739509194, value=98
2 row(s) in 0.0080 seconds
|
And when queried back into Hive:
| No Format |
|---|
hive> select * from hbase_table_1;
Total MapReduce jobs = 1
Launching Job 1 out of 1
...
OK
{"val_100":100} 100
{"val_98":98} 98
Time taken: 3.808 seconds
|
Note that the key of the MAP must have datatype string, since it is used for naming the HBase column, so the following table definition will fail:
| No Format |
|---|
CREATE TABLE hbase_table_1(key int, value map<int,int>)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,cf:"
);
FAILED: Error in metadata: java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.hbase.HBaseSerDe: hbase column family 'cf:' should be mapped to map<string,?> but is mapped to map<int,int>)
|
...
Table definitions such as the following are illegal because a
Hive column mapped to an entire column family must have MAP type:
| No Format |
|---|
CREATE TABLE hbase_table_1(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,cf:"
);
FAILED: Error in metadata: java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.hbase.HBaseSerDe: hbase column family 'cf:' should be mapped to map<string,?> but is mapped to string)
|
...
Relying on the default value of hbase.table.default.storage.type:
| No Format |
|---|
CREATE TABLE hbase_table_1 (key int, value string, foobar double)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key#b,cf:val,cf:foo#b"
);
|
Specifying hbase.table.default.storage.type:
| No Format |
|---|
CREATE TABLE hbase_table_1 (key int, value string, foobar double)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,cf:val#s,cf:foo",
"hbase.table.default.storage.type" = "binary"
);
|
...
For example, the pokes table contains rows with duplicate keys. If it is copied into another Hive table, the duplicates are preserved:
| No Format |
|---|
CREATE TABLE pokes2(foo INT, bar STRING);
INSERT OVERWRITE TABLE pokes2 SELECT * FROM pokes;
-- this will return 3
SELECT COUNT(1) FROM POKES WHERE foo=498;
-- this will also return 3
SELECT COUNT(1) FROM pokes2 WHERE foo=498;
|
But in HBase, the duplicates are silently eliminated:
| No Format |
|---|
CREATE TABLE pokes3(foo INT, bar STRING)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,cf:bar"
);
INSERT OVERWRITE TABLE pokes3 SELECT * FROM pokes;
-- this will return 1 instead of 3
SELECT COUNT(1) FROM pokes3 WHERE foo=498;
|
...
The QL tests can be executed via ant like this:
| No Format |
|---|
ant test -Dtestcase=TestHBaseCliDriver -Dqfile=hbase_queries.q
|
...
- Primary credit for this feature goes to Samuel Guo, who did most of the development work in the early drafts of the patch
Open Issues (JIRA)
| Jira Issues | ||
|---|---|---|
|