question Re: hive on hbase - not showing data in Archives of Support Questions (Read Only)

hive on hbase - not showing data

vtpcnk — Tue, 18 Dec 2018 02:07:25 GMT

I created a table in hbase. Created a table in hive mapping it to the table in hbase.

Inserted a row in the hbase table.

But hive table doesn't show any rows.

Any idea what I am doing wrong?

Appreciate the feedback.

hbase(main):019:0> create 'mbev:hb_test' , 'me_data'

0 row(s) in 1.2670 seconds

hbase(main):006:0> put "mbev:hb_test",'1',"me_data:id",'1'

0 row(s) in 0.1700 seconds

hbase(main):007:0> scan "mbev:hb_test"

ROW COLUMN+CELL 1 column=me_data:id, timestamp=1545064141017, value=1 1 row(s) in 0.0590 seconds

hive>create external table hv_test (id string, idate string)

STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,me_data:idate")

TBLPROPERTIES("hbase.table.name" = "mbev:hb_test");

hive> select * from hv_test ;

OK Time taken: 0.23 seconds

Re: hive on hbase - not showing data

npallapotu — Tue, 18 Dec 2018 04:55:38 GMT

Hi @n c,The column name in hbase table id while mapping the hbase column in hive table you have used as idate, please correct the column name you will able to see the data.

Cheers,

Naveen

Re: hive on hbase - not showing data

vtpcnk — Tue, 18 Dec 2018 09:18:25 GMT

Naveen, thanks so much.

Re: hive on hbase - not showing data

vtpcnk — Wed, 19 Dec 2018 00:51:46 GMT

In my hbase table actually data for 10 columns lands from flume.

In the flume configuration I have defined : host1.sinks.sink1.serializer.columns = col1, col2 ...

In HBASE the table is only defined as above : create 'mbev:hb_test' , 'me_data'

So how do I define the hive table for the respective data for the 10 columns?

Re: hive on hbase - not showing data

vtpcnk — Wed, 19 Dec 2018 04:49:58 GMT

btw :

the create table shows two columns : create external table hv_test (id string, idate string)

so how can this statement show only 1 column : WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,me_data:id")

don't we need to likewise map idate?

Re: hive on hbase - not showing data

npallapotu — Wed, 19 Dec 2018 18:08:15 GMT

Hi @n c,

As you know the column names from flume, map the same in the below line while creating the hive table if column names in hbase col1,col2 etc

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,me_data:col1,me_data:col2...")

Re: hive on hbase - not showing data

npallapotu — Wed, 19 Dec 2018 18:11:20 GMT

In hbase you have a rowkey & one column so you mapped rowkey as key & id as id. In your case the value is same for both rowkey & id so you are confused assuming there is only 1 column. Try inserting one more row in hbase with different values & see the difference.

Re: hive on hbase - not showing data

vtpcnk — Thu, 20 Dec 2018 02:28:24 GMT

@Naveen

my hbase table is created only with a single column family.

create 'mbev:hb_test' , 'me_data'

Then data is fed into the hbase table through flume. This data originally was sent to a hive table with 10 columns - but for small files issue, i am redirecting the same data to the above hbase table.

my flume config has the following lines :

r_hbase.sinks.sink1.table = mbev:hb_test

r_hbase.sinks.sink1.columnFamily = me_data

r_hbase.sinks.sink1.serializer.columns =col1,col2,col3,col4,col5,col6,col7,col8,col9,col10

So I can see data coming into the hbase table.

Data is showing like :

hbase(main):008:0> scan "mbev:hb_test"

ROW COLUMN+CELL

default0998c6b9-2fa9 column=me_data:pCol, timestamp=1545242255268, value=.z3knt -122e-1536-0o42ef7fb dErc90GqYg5a3n-zTQ\a04NQA32018-12-19-12.57.30.000123\x09FY a3e YY\q1V10006002079317\x01M\x00\x09KEF\x00\x00

So how do I create the hive table to see the data in the hbase table.

I tried as we did above initially with only "":key,me_data:id", but don't see the data in the hive table.

I also tried :

create external table tmp_test4 (col1 string,col2 string,col3 string,col4 string,col5 string,col6 string,col7 string,col8 string,col9 string,col10 string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,me_data:col1,me_data:col2,me_data:col3,me_data:col4,me_data:col5,me_data:col6,me_data:col7,me_data:col8,me_data:col9,me_data:col10") TBLPROPERTIES("hbase.table.name" = "mbev:hb_test");

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.hbase.HBaseSerDe: columns has 10 elements while hbase.columns. mapping has 11 elements (counting the key if implicit))

Appreciate your help.

Re: hive on hbase - not showing data

vtpcnk — Thu, 20 Dec 2018 20:54:22 GMT

hi @Naveen any response on this please? Thanks.