Hive, mail # user - Re: HBase-Hive integration - fetching long values in HBase as bigint in Hive

HBase-Hive integration - fetching long values in HBase as bigint in Hive

Ashish Nigam 2013-02-14, 14:23

Hi,
I am storing counters (long value) in HBase table.
I have created Hive Table like this -

CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,bigint>, row_key
int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
TBLPROPERTIES("hbase.table.name" = "counters");

The problem is that all counter values are coming as NULL if I perform
select on this table.

If I change the DDL to get string, I do see counter values.Modified DDL is
like this -

CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,string>, row_key
int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH
SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
TBLPROPERTIES("hbase.table.name" = "counters");

Now I do see the values. For example, here's

hive> select * from hbase_hive_counters;
OK
{"20130209-C":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130209-C-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000
ï¿½"} 2000
{"20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012"}
2018
I even tried this query when the counter type is string in the HIVE table -

select cast(value["20130210"] as bigint) from hbase_hive_counters where
row_key=2000;

The answer is again, NULL.

How do I get counter values in HIVE as bigint?

Thanks
Ashish

Re: HBase-Hive integration - fetching long values in HBase as bigint in Hive

kulkarni.swarnim@...) 2013-02-14, 14:51

Hi Ashish,

How many columns do you have under the column family "stats-daily"? The way
you have created your DDL, it would mean that the values for all columns
under your column family "stats-daily" are of bigint. If that is not the
case, you are bound to get a NULL. I had logged a JIRA[1] for it sometime
back.

If you know the name of your column, I would say you be more specific and
query that specific column. For instance,

CREATE EXTERNAL TABLE hbase_hive_counters(value bigint, row_key int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:*MY_COLUMN_NAME
*,:key")
TBLPROPERTIES("hbase.table.name" = "counters");

Hope this helps.

[1] https://issues.apache.org/jira/browse/HIVE-3312
On Thu, Feb 14, 2013 at 8:23 AM, Ashish Nigam <[EMAIL PROTECTED]>wrote:

> Hi,
> I am storing counters (long value) in HBase table.
> I have created Hive Table like this -
>
> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,bigint>,
> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
> TBLPROPERTIES("hbase.table.name" = "counters");
>
> The problem is that all counter values are coming as NULL if I perform
> select on this table.
>
> If I change the DDL to get string, I do see counter values.Modified DDL is
> like this -
>
> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,string>,
> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
> TBLPROPERTIES("hbase.table.name" = "counters");
>
> Now I do see the values. For example, here's
>
> hive> select * from hbase_hive_counters;
> OK
>
> {"20130209-C":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130209-C-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000
> ï¿½"} 2000
> {"20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012"}
> 2018
>
>
> I even tried this query when the counter type is string in the HIVE table
> -
>
> select cast(value["20130210"] as bigint) from hbase_hive_counters where
> row_key=2000;
>
> The answer is again, NULL.
>
>
>
> How do I get counter values in HIVE as bigint?
>
> Thanks
> Ashish
>
>
>
--
Swarnim

Re: HBase-Hive integration - fetching long values in HBase as bigint in Hive

Ashish Nigam 2013-02-14, 17:02

Columns in "stats-daily" CF are dynamic in nature but all the values are
long counters.
I also tried with a table that has only one column mapped. That also is
returning NULL.
So the issue does not seem to be different value types in a column family.

On Thu, Feb 14, 2013 at 6:51 AM, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:

> Hi Ashish,
>
> How many columns do you have under the column family "stats-daily"? The
> way you have created your DDL, it would mean that the values for all
> columns under your column family "stats-daily" are of bigint. If that is
> not the case, you are bound to get a NULL. I had logged a JIRA[1] for it
> sometime back.
>
> If you know the name of your column, I would say you be more specific and
> query that specific column. For instance,
>
> CREATE EXTERNAL TABLE hbase_hive_counters(value bigint, row_key int)
> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:*
> MY_COLUMN_NAME*,:key")
> TBLPROPERTIES("hbase.table.name" = "counters");
>
> Hope this helps.
>
> [1] https://issues.apache.org/jira/browse/HIVE-3312
>
>
> On Thu, Feb 14, 2013 at 8:23 AM, Ashish Nigam <[EMAIL PROTECTED]>wrote:
>
>> Hi,
>> I am storing counters (long value) in HBase table.
>> I have created Hive Table like this -
>>
>> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,bigint>,
>> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
>> TBLPROPERTIES("hbase.table.name" = "counters");
>>
>> The problem is that all counter values are coming as NULL if I perform
>> select on this table.
>>
>> If I change the DDL to get string, I do see counter values.Modified DDL
>> is like this -
>>
>> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,string>,
>> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
>> TBLPROPERTIES("hbase.table.name" = "counters");
>>
>> Now I do see the values. For example, here's
>>
>> hive> select * from hbase_hive_counters;
>> OK
>>
>> {"20130209-C":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130209-C-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000
>> ï¿½"} 2000
>> {"20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012"}
>> 2018
>>
>>
>> I even tried this query when the counter type is string in the HIVE table
>> -
>>
>> select cast(value["20130210"] as bigint) from hbase_hive_counters where
>> row_key=2000;
>>
>> The answer is again, NULL.
>>
>>
>>
>> How do I get counter values in HIVE as bigint?
>>
>> Thanks
>> Ashish
>>
>>
>>
>
>
> --
> Swarnim
>

Re: HBase-Hive integration - fetching long values in HBase as bigint in Hive

Ashish Nigam 2013-02-14, 17:16

It worked... I was missing this in SERDE properties -

"hbase.table.default.storage.type" = "binary"
On Thu, Feb 14, 2013 at 9:02 AM, Ashish Nigam <[EMAIL PROTECTED]>wrote:

> Columns in "stats-daily" CF are dynamic in nature but all the values are
> long counters.
> I also tried with a table that has only one column mapped. That also is
> returning NULL.
> So the issue does not seem to be different value types in a column family.
>
>
>
> On Thu, Feb 14, 2013 at 6:51 AM, [EMAIL PROTECTED] <
> [EMAIL PROTECTED]> wrote:
>
>> Hi Ashish,
>>
>> How many columns do you have under the column family "stats-daily"? The
>> way you have created your DDL, it would mean that the values for all
>> columns under your column family "stats-daily" are of bigint. If that is
>> not the case, you are bound to get a NULL. I had logged a JIRA[1] for it
>> sometime back.
>>
>> If you know the name of your column, I would say you be more specific and
>> query that specific column. For instance,
>>
>> CREATE EXTERNAL TABLE hbase_hive_counters(value bigint, row_key int)
>> STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:*
>> MY_COLUMN_NAME*,:key")
>> TBLPROPERTIES("hbase.table.name" = "counters");
>>
>> Hope this helps.
>>
>> [1] https://issues.apache.org/jira/browse/HIVE-3312
>>
>>
>> On Thu, Feb 14, 2013 at 8:23 AM, Ashish Nigam <[EMAIL PROTECTED]>wrote:
>>
>>> Hi,
>>> I am storing counters (long value) in HBase table.
>>> I have created Hive Table like this -
>>>
>>> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,bigint>,
>>> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>>> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
>>> TBLPROPERTIES("hbase.table.name" = "counters");
>>>
>>> The problem is that all counter values are coming as NULL if I perform
>>> select on this table.
>>>
>>> If I change the DDL to get string, I do see counter values.Modified DDL
>>> is like this -
>>>
>>> CREATE EXTERNAL TABLE hbase_hive_counters(value map<string,string>,
>>> row_key int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>>> WITH SERDEPROPERTIES ("hbase.columns.mapping" = "stats-daily:,:key")
>>> TBLPROPERTIES("hbase.table.name" = "counters");
>>>
>>> Now I do see the values. For example, here's
>>>
>>> hive> select * from hbase_hive_counters;
>>> OK
>>>
>>> {"20130209-C":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130209-C-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000ï¿½","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000
>>> ï¿½"} 2000
>>> {"20130210":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012","20130210-U-8077639":"\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0012"}
>>> 2018
>>>
>>>
>>> I even tried this query when the counter type is string in the HIVE
>>> table -
>>>
>>> select cast(value["20130210"] as bigint) from hbase_hive_counters where
>>> row_key=2000;
>>>
>>> The answer is again, NULL.
>>>
>>>
>>>
>>> How do I get counter values in HIVE as bigint?
>>>
>>> Thanks
>>> Ashish
>>>
>>>
>>>
>>
>>
>> --
>> Swarnim
>>
>
>