Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - HBase-Hive integration - fetching long values in HBase as bigint in Hive


Copy link to this message
-
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
>>
>
>