Is there a way to query using Hive on existing table records in HBase?

Hi Vishal,

Not through a released version of Hive. But, if you are willing to build
hive from svn trunk, you will be able to do query on existing hbase tables.

Or, you can wait for 0.9 to be released.

Hope it helps,
Ashutosh

On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]> wrote:

> Is there a way to query using Hive on existing table records in HBase?
>
>

Oh ok, thanks Ashutosh

Is there a new API introduced for this or works for External Table?

On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:

> Hi Vishal,
>
> Not through a released version of Hive. But, if you are willing to build
> hive from svn trunk, you will be able to do query on existing hbase tables.
>
> Or, you can wait for 0.9 to be released.
>
> Hope it helps,
> Ashutosh
>
> On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>
>> Is there a way to query using Hive on existing table records in HBase?
>>
>>
>
>
--
Person is only known by it's work done, and not by what it's capable of
doing

Hi Vishal and Ashutosh

        Using hive hbase storage handler it is possible to query an existing table in hbase using hive, and you can load data into hbase as well. And the feature is available in hive since hive 0.6 .
JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .

wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html

Regards
Bejoy KS

________________________________
 From: Vishal M. Katkar <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Sunday, April 8, 2012 12:52 AM
Subject: Re: Hive query on existing HBase table
 

Oh ok, thanks Ashutosh
Is there a new API introduced for this or works for External Table?
On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]> wrote:

Hi Vishal,
>
>Not through a released version of Hive. But, if you are willing to build hive from svn trunk, you will be able to do query on existing hbase tables.  
>Or, you can wait for 0.9 to be released.
>
>Hope it helps,
>Ashutosh
>
>
>On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]> wrote:
>
>Is there a way to query using Hive on existing table records in HBase?
>> 
>
--
Person is only known by it's work done, and not by what it's capable of doing

I'm only able to access table records which are loaded through Hive, but
not the one loaded through HBase

This table is defined *External*, and using Hive - HBase *Storage handler*
*
*
Regards,
Vishal

On Sun, Apr 8, 2012 at 3:26 PM, Bejoy Ks <[EMAIL PROTECTED]> wrote:

> Hi Vishal and Ashutosh
>
>         Using hive hbase storage handler it is possible to query an
> existing table in hbase using hive, and you can load data into hbase as
> well. And the feature is available in hive since hive 0.6 .
>
> JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .
>
> wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html
>
>
> Regards
> Bejoy KS
>
>   ------------------------------
> *From:* Vishal M. Katkar <[EMAIL PROTECTED]>
> *To:* [EMAIL PROTECTED]
> *Sent:* Sunday, April 8, 2012 12:52 AM
> *Subject:* Re: Hive query on existing HBase table
>
> Oh ok, thanks Ashutosh
>
> Is there a new API introduced for this or works for External Table?
>
> On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:
>
> Hi Vishal,
>
> Not through a released version of Hive. But, if you are willing to build
> hive from svn trunk, you will be able to do query on existing hbase tables.
>
> Or, you can wait for 0.9 to be released.
>
> Hope it helps,
> Ashutosh
>
> On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>
> Is there a way to query using Hive on existing table records in HBase?
>
>
>
>
>
>
> --
> Person is only known by it's work done, and not by what it's capable of
> doing
>
>
>
--
Person is only known by it's work done, and not by what it's capable of
doing

Bejoy,
As Vishal said in current releases integration works only if you have
written data in hbase from hive, if you have pre-existing data in hbase,
you can't query it from hive. You need
https://issues.apache.org/jira/browse/HIVE-1634 for it.

Vishal,
No there is no new api. Nothing changes from existing ddl statements As
previously, you need to define table as external and have stored by clause
for HBaseStorageHandler. Only, extra thing you need is you need to add
following key-value in table-properties
"hbase.table.default.storage.type"="binary"
Thanks,
Ashutosh
On Sun, Apr 8, 2012 at 03:30, Vishal M. Katkar <[EMAIL PROTECTED]> wrote:

> I'm only able to access table records which are loaded through Hive, but
> not the one loaded through HBase
>
> This table is defined *External*, and using Hive - HBase *Storage handler*
> *
> *
> Regards,
> Vishal
>
> On Sun, Apr 8, 2012 at 3:26 PM, Bejoy Ks <[EMAIL PROTECTED]> wrote:
>
>> Hi Vishal and Ashutosh
>>
>>         Using hive hbase storage handler it is possible to query an
>> existing table in hbase using hive, and you can load data into hbase as
>> well. And the feature is available in hive since hive 0.6 .
>>
>> JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .
>>
>> wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html
>>
>>
>> Regards
>> Bejoy KS
>>
>>   ------------------------------
>> *From:* Vishal M. Katkar <[EMAIL PROTECTED]>
>> *To:* [EMAIL PROTECTED]
>> *Sent:* Sunday, April 8, 2012 12:52 AM
>> *Subject:* Re: Hive query on existing HBase table
>>
>> Oh ok, thanks Ashutosh
>>
>> Is there a new API introduced for this or works for External Table?
>>
>> On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:
>>
>> Hi Vishal,
>>
>> Not through a released version of Hive. But, if you are willing to build
>> hive from svn trunk, you will be able to do query on existing hbase tables.
>>
>> Or, you can wait for 0.9 to be released.
>>
>> Hope it helps,
>> Ashutosh
>>
>> On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>>
>> Is there a way to query using Hive on existing table records in HBase?
>>
>>
>>
>>
>>
>>
>> --
>> Person is only known by it's work done, and not by what it's capable of
>> doing
>>
>>
>>
>
>
> --
> Person is only known by it's work done, and not by what it's capable of
> doing
>

Checked-out the following branch
svn checkout http://svn.apache.org/repos/asf/hive/trunk trunk

Still facing the same issue.

*Created Hive table with following command:*
CREATE EXTERNAL TABLE myTable(key STRING, fileName STRING) STORED BY
'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" ":key,default:fileName","hbase.columns.storage.types"="-,b")
TBLPROPERTIES ("hbase.table.name" "myTable","hbase.table.default.storage.type"="string")

*Hive table description looks like this:*
Detailed Table Information Table(tableName:myTable, dbName:default,
owner:vishal, createTime:1334043228, lastAccessTime:0, retention:0,
sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string,
comment:null), FieldSchema(name:filename, type:string, comment:null)],
location:hdfs://localhost:9000/user/hive/warehouse/myTable,
inputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat,
outputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableOutputFormat,
compressed:false, numBuckets:-1,
serdeInfo:SerDeInfo(name:null,
serializationLib:org.apache.hadoop.hive.hbase.HBaseSerDe,
parameters:{serialization.format=1,
hbase.columns.mapping=:key,default:fileName,
hbase.columns.storage.types=-,b}), bucketCols:[], sortCols:[],
parameters:{}), partitionKeys:[],
parameters:{hbase.table.default.storage.type=string, EXTERNAL=TRUE,
hbase.table.name=myTable, transient_lastDdlTime=1334043228,
storage_handler=org.apache.hadoop.hive.hbase.HBaseStorageHandler},
viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE)

I'm referring to the correct branch right?

Thanks,
Vishal

On Sun, Apr 8, 2012 at 9:26 PM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:

> Bejoy,
> As Vishal said in current releases integration works only if you have
> written data in hbase from hive, if you have pre-existing data in hbase,
> you can't query it from hive. You need
> https://issues.apache.org/jira/browse/HIVE-1634 for it.
>
> Vishal,
> No there is no new api. Nothing changes from existing ddl statements As
> previously, you need to define table as external and have stored by clause
> for HBaseStorageHandler. Only, extra thing you need is you need to add
> following key-value in table-properties
> "hbase.table.default.storage.type"="binary"
>
>
> Thanks,
> Ashutosh
>
> On Sun, Apr 8, 2012 at 03:30, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>
>> I'm only able to access table records which are loaded through Hive, but
>> not the one loaded through HBase
>>
>>  This table is defined *External*, and using Hive - HBase *Storage
>> handler*
>> *
>> *
>> Regards,
>> Vishal
>>
>> On Sun, Apr 8, 2012 at 3:26 PM, Bejoy Ks <[EMAIL PROTECTED]> wrote:
>>
>>> Hi Vishal and Ashutosh
>>>
>>>         Using hive hbase storage handler it is possible to query an
>>> existing table in hbase using hive, and you can load data into hbase as
>>> well. And the feature is available in hive since hive 0.6 .
>>>
>>> JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .
>>>
>>> wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html
>>>
>>>
>>> Regards
>>> Bejoy KS
>>>
>>>   ------------------------------
>>> *From:* Vishal M. Katkar <[EMAIL PROTECTED]>
>>> *To:* [EMAIL PROTECTED]
>>> *Sent:* Sunday, April 8, 2012 12:52 AM
>>> *Subject:* Re: Hive query on existing HBase table
>>>
>>> Oh ok, thanks Ashutosh
>>>
>>> Is there a new API introduced for this or works for External Table?
>>>
>>> On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:
>>>
>>> Hi Vishal,
>>>
>>> Not through a released version of Hive. But, if you are willing to build
>>> hive from svn trunk, you will be able to do query on existing hbase tables.
>>>
>>> Or, you can wait for 0.9 to be released.
>>>
>>> Hope it helps,
>>> Ashutosh
>>>
>>> On Sat, Apr 7, 2012 at 11:43, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>>>
>>> Is there a way to query using Hive on existing table records in HBase?
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Person is only known by it's work done, and not by what it's capable of
Person is only known by it's work done, and not by what it's capable of
doing

I did wrong deployment, let me check it once again

Sorry for premature reply

Regards,
Vishal

On Tue, Apr 10, 2012 at 1:20 PM, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:

> Checked-out the following branch
> svn checkout http://svn.apache.org/repos/asf/hive/trunk trunk
>
> Still facing the same issue.
>
> *Created Hive table with following command:*
> CREATE EXTERNAL TABLE myTable(key STRING, fileName STRING) STORED BY
> 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
> WITH SERDEPROPERTIES ("hbase.columns.mapping" > ":key,default:fileName","hbase.columns.storage.types"="-,b")
> TBLPROPERTIES ("hbase.table.name" > "myTable","hbase.table.default.storage.type"="string")
>
> *Hive table description looks like this:*
> Detailed Table Information Table(tableName:myTable, dbName:default,
> owner:vishal, createTime:1334043228, lastAccessTime:0, retention:0,
> sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string,
> comment:null), FieldSchema(name:filename, type:string, comment:null)],
> location:hdfs://localhost:9000/user/hive/warehouse/myTable,
> inputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat,
> outputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableOutputFormat,
> compressed:false, numBuckets:-1,
> serdeInfo:SerDeInfo(name:null,
> serializationLib:org.apache.hadoop.hive.hbase.HBaseSerDe,
> parameters:{serialization.format=1,
> hbase.columns.mapping=:key,default:fileName,
> hbase.columns.storage.types=-,b}), bucketCols:[], sortCols:[],
> parameters:{}), partitionKeys:[],
> parameters:{hbase.table.default.storage.type=string, EXTERNAL=TRUE,
> hbase.table.name=myTable, transient_lastDdlTime=1334043228,
> storage_handler=org.apache.hadoop.hive.hbase.HBaseStorageHandler},
> viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE)
>
> I'm referring to the correct branch right?
>
> Thanks,
> Vishal
>
> On Sun, Apr 8, 2012 at 9:26 PM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:
>
>> Bejoy,
>> As Vishal said in current releases integration works only if you have
>> written data in hbase from hive, if you have pre-existing data in hbase,
>> you can't query it from hive. You need
>> https://issues.apache.org/jira/browse/HIVE-1634 for it.
>>
>> Vishal,
>> No there is no new api. Nothing changes from existing ddl statements As
>> previously, you need to define table as external and have stored by clause
>> for HBaseStorageHandler. Only, extra thing you need is you need to add
>> following key-value in table-properties
>> "hbase.table.default.storage.type"="binary"
>>
>>
>> Thanks,
>> Ashutosh
>>
>> On Sun, Apr 8, 2012 at 03:30, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>>
>>> I'm only able to access table records which are loaded through Hive, but
>>> not the one loaded through HBase
>>>
>>>  This table is defined *External*, and using Hive - HBase *Storage
>>> handler*
>>> *
>>> *
>>> Regards,
>>> Vishal
>>>
>>> On Sun, Apr 8, 2012 at 3:26 PM, Bejoy Ks <[EMAIL PROTECTED]> wrote:
>>>
>>>> Hi Vishal and Ashutosh
>>>>
>>>>         Using hive hbase storage handler it is possible to query an
>>>> existing table in hbase using hive, and you can load data into hbase as
>>>> well. And the feature is available in hive since hive 0.6 .
>>>>
>>>> JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .
>>>>
>>>> wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html
>>>>
>>>>
>>>> Regards
>>>> Bejoy KS
>>>>
>>>>   ------------------------------
>>>> *From:* Vishal M. Katkar <[EMAIL PROTECTED]>
>>>> *To:* [EMAIL PROTECTED]
>>>> *Sent:* Sunday, April 8, 2012 12:52 AM
>>>> *Subject:* Re: Hive query on existing HBase table
>>>>
>>>> Oh ok, thanks Ashutosh
>>>>
>>>> Is there a new API introduced for this or works for External Table?
>>>>
>>>> On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <[EMAIL PROTECTED]
>>>> > wrote:
>>>>
>>>> Hi Vishal,
>>>>
>>>> Not through a released version of Hive. But, if you are willing to
>>>> build hive from svn trunk, you will be able to do query on existing hbase

Person is only known by it's work done, and not by what it's capable of
doing

It's working great

Thanks a lot

-Regards
Vishal

On Tue, Apr 10, 2012 at 1:34 PM, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:

> I did wrong deployment, let me check it once again
>
> Sorry for premature reply
>
> Regards,
> Vishal
>
>
> On Tue, Apr 10, 2012 at 1:20 PM, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>
>> Checked-out the following branch
>> svn checkout http://svn.apache.org/repos/asf/hive/trunk trunk
>>
>> Still facing the same issue.
>>
>> *Created Hive table with following command:*
>> CREATE EXTERNAL TABLE myTable(key STRING, fileName STRING) STORED BY
>> 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
>> WITH SERDEPROPERTIES ("hbase.columns.mapping" >> ":key,default:fileName","hbase.columns.storage.types"="-,b")
>> TBLPROPERTIES ("hbase.table.name" >> "myTable","hbase.table.default.storage.type"="string")
>>
>> *Hive table description looks like this:*
>> Detailed Table Information Table(tableName:myTable, dbName:default,
>> owner:vishal, createTime:1334043228, lastAccessTime:0, retention:0,
>> sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string,
>> comment:null), FieldSchema(name:filename, type:string, comment:null)],
>> location:hdfs://localhost:9000/user/hive/warehouse/myTable,
>> inputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat,
>> outputFormat:org.apache.hadoop.hive.hbase.HiveHBaseTableOutputFormat,
>> compressed:false, numBuckets:-1,
>> serdeInfo:SerDeInfo(name:null,
>> serializationLib:org.apache.hadoop.hive.hbase.HBaseSerDe,
>> parameters:{serialization.format=1,
>> hbase.columns.mapping=:key,default:fileName,
>> hbase.columns.storage.types=-,b}), bucketCols:[], sortCols:[],
>> parameters:{}), partitionKeys:[],
>> parameters:{hbase.table.default.storage.type=string, EXTERNAL=TRUE,
>> hbase.table.name=myTable, transient_lastDdlTime=1334043228,
>> storage_handler=org.apache.hadoop.hive.hbase.HBaseStorageHandler},
>> viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE)
>>
>> I'm referring to the correct branch right?
>>
>> Thanks,
>> Vishal
>>
>> On Sun, Apr 8, 2012 at 9:26 PM, Ashutosh Chauhan <[EMAIL PROTECTED]>wrote:
>>
>>> Bejoy,
>>> As Vishal said in current releases integration works only if you have
>>> written data in hbase from hive, if you have pre-existing data in hbase,
>>> you can't query it from hive. You need
>>> https://issues.apache.org/jira/browse/HIVE-1634 for it.
>>>
>>> Vishal,
>>> No there is no new api. Nothing changes from existing ddl statements As
>>> previously, you need to define table as external and have stored by clause
>>> for HBaseStorageHandler. Only, extra thing you need is you need to add
>>> following key-value in table-properties
>>> "hbase.table.default.storage.type"="binary"
>>>
>>>
>>> Thanks,
>>> Ashutosh
>>>
>>> On Sun, Apr 8, 2012 at 03:30, Vishal M. Katkar <[EMAIL PROTECTED]>wrote:
>>>
>>>> I'm only able to access table records which are loaded through Hive,
>>>> but not the one loaded through HBase
>>>>
>>>>  This table is defined *External*, and using Hive - HBase *Storage
>>>> handler*
>>>> *
>>>> *
>>>> Regards,
>>>> Vishal
>>>>
>>>> On Sun, Apr 8, 2012 at 3:26 PM, Bejoy Ks <[EMAIL PROTECTED]> wrote:
>>>>
>>>>> Hi Vishal and Ashutosh
>>>>>
>>>>>         Using hive hbase storage handler it is possible to query an
>>>>> existing table in hbase using hive, and you can load data into hbase as
>>>>> well. And the feature is available in hive since hive 0.6 .
>>>>>
>>>>> JIRA:  https://issues.apache.org/jira/browse/HIVE-705 .
>>>>>
>>>>> wiki:   https://cwiki.apache.org/Hive/hbaseintegration.html
>>>>>
>>>>>
>>>>> Regards
>>>>> Bejoy KS
>>>>>
>>>>>   ------------------------------
>>>>> *From:* Vishal M. Katkar <[EMAIL PROTECTED]>
>>>>> *To:* [EMAIL PROTECTED]
>>>>> *Sent:* Sunday, April 8, 2012 12:52 AM
>>>>> *Subject:* Re: Hive query on existing HBase table
>>>>>
>>>>> Oh ok, thanks Ashutosh
>>>>>
>>>>> Is there a new API introduced for this or works for External Table?
>>>>>
>>>>> On Sun, Apr 8, 2012 at 12:36 AM, Ashutosh Chauhan <

Person is only known by it's work done, and not by what it's capable of
doing