Import multiple column families from hbase to hive

0
votes

I am trying to move hbase table having two column family into hive table. I am able to move one column family but how can i move another one in same hive table.

Edit:

I moved one column family ushing below code.

CREATE TABLE hbase_hive(key string, firstname string, age string)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES (“hbase.columns.mapping” = “id:firstname,id:age")
TBLPROPERTIES(“hbase.table.name” = “hl”);

but i am having one more column family with name hb and having three columns. How to achive this.

Update:

I also tried adding column name of different column family below is my code.

CREATE TABLE hbase_hive(key string, firstname string, age string, testname string)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES (“hbase.columns.mapping” = “id:firstname,id:age,pd:name")
TBLPROPERTIES(“hbase.table.name” = “hl”);

but i am getting below result:

 819215975  19391121    625678921720    NULL
 819617215  19570622    625116365890    NULL
 820333876  19640303    623221670810    NULL
 824794938  19531211    625278010070    NULL
 828093442  19420803    625284904860    NULL
 828905771  19320209    625078004220    NULL
 829832017  19630722    625178010070    NULL

Instead of values i am getting null.

Update:

I tried creating hbase table using below command in hbase shell

create ‘hl’,’id’

then i created one more column family using below command

alter ‘hl’,’pd’
2
hadoophivehbasebigdata
it's easier if you show some sample code or how you achieved moving the first cf – Marsellus Wallace
@Gevorg i have updated my question. please check. – animal

2 Answers

0
votes

In your HiveQL, you select two columns in column family "id" from hbase table "hl" into hive table. If you want to add more columns (even from other column families), you just need to add them to table schema and hbase.columns.mapping. For example:

CREATE TABLE hbase_hive(key string, firstname string, age string, a string)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES (“hbase.columns.mapping” = “id:firstname,id:age,hb:a")
TBLPROPERTIES(“hbase.table.name” = “hl”);

see https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration#HBaseIntegration-MultipleColumnsandFamilies

0
votes

I see a couple of issues (more or less serious) with what you wrote:

  • First of all, I would create an EXTERNAL TABLE instead
  • You are creating a Hive table with only 3 columns but expecting 4 in the end
  • You are not explicitly mapping the :key
  • Your data for 'firstname' and 'age' looks like wild random numbers! :|

I could not test it but the following should be a better starting point:

CREATE EXTERNAL TABLE hbase_hive_hl(key string, firstname string, age string, name string)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES (“hbase.columns.mapping” = “:key,id:firstname,id:age,pd:name")
TBLPROPERTIES(“hbase.table.name” = “hl”);