FAQ
I am using the Impala 0.6 virtual machine with no other modifications other
than populating HBase tables with my data. I want to be able to query my
data in HBase via Impala and am not able to retrieve non-string (e.g. any
numeric types) values in HBase.

I have an HBase Table called '911db9a5-d31f-4aac-a700-0911016150c5'.
There are three columns in the main0 column family:
main0:\x00\x00\x00\x01, main0:\x00\x00\x00\x02, ,
and main0:\x00\x00\x00\x03. main0:\x00\x00\x00\x01
and main0:\x00\x00\x00\x03 store STRINGs, while main0:\x00\x00\x00\x02
stores DOUBLEs. Scanning the table would return the following:

ROW COLUMN+CELL


\x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x01,
timestamp=1, value=SUPERMARKETS

\x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x02,
timestamp=2, value=@\xFA\xDF\x90\x00\x00\x00\x00

\x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x03,
timestamp=2, value=CHIEVE'S SUPERMARKET

I create a mapping between my HBase table and Hive as follows:

CREATE EXTERNAL TABLE finaltable (

key string,

customer string,

custnbr double

)


STORED BY

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


WITH SERDEPROPERTIES

("hbase.columns.mapping" =
":key,main0:\000\000\000\001#s,main0:\000\000\000\002#b")


TBLPROPERTIES

("hbase.table.name" =
'911db9a5-d31f-4aac-a700-0911016150c5',"hbase.table.default.storage.type" =
"binary");


This returns an OK. I then REFRESH in Impala Shell and run the following
query


select * from finaltable


I get correct values returned for KEY and CUSTOMER. But no matter what
type of mapping I try, I can not get any numeric columns (of any numeric
type) to return anything except NULL. So at this point I can use Impala
for strings in HBase but I have not figured out how to extract numerics.
Any guidance or workarounds appreciated. Thank you.

Search Discussions

  • Ricky Saltzer at Mar 27, 2013 at 9:02 pm
    H*i *Nenshad -

    Unfortunately, Impala only supports ASCII data within HBase at the moment.
    As a workaround, the values would need to be stored as a string
    representation. This is a known limitation and something we are working on,
    but currently we do not have a timeframe as to when it will make it in.

    Let me know if I can help with anything else.

    Ricky

    On Wed, Mar 27, 2013 at 4:41 PM, Nenshad Bardoliwalla wrote:

    I am using the Impala 0.6 virtual machine with no other modifications
    other than populating HBase tables with my data. I want to be able to
    query my data in HBase via Impala and am not able to retrieve non-string
    (e.g. any numeric types) values in HBase.

    I have an HBase Table called '911db9a5-d31f-4aac-a700-0911016150c5'.
    There are three columns in the main0 column family:
    main0:\x00\x00\x00\x01, main0:\x00\x00\x00\x02, ,
    and main0:\x00\x00\x00\x03. main0:\x00\x00\x00\x01
    and main0:\x00\x00\x00\x03 store STRINGs, while main0:\x00\x00\x00\x02
    stores DOUBLEs. Scanning the table would return the following:

    ROW COLUMN+CELL


    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x01,
    timestamp=1, value=SUPERMARKETS

    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x02,
    timestamp=2, value=@\xFA\xDF\x90\x00\x00\x00\x00

    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x03,
    timestamp=2, value=CHIEVE'S SUPERMARKET

    I create a mapping between my HBase table and Hive as follows:

    CREATE EXTERNAL TABLE finaltable (

    key string,

    customer string,

    custnbr double

    )


    STORED BY

    'org.apache.hadoop.hive.hbase.**HBaseStorageHandler'


    WITH SERDEPROPERTIES

    ("hbase.columns.mapping" = ":key,main0:\000\000\000\001#s,**
    main0:\000\000\000\002#b")


    TBLPROPERTIES

    ("hbase.table.name" = '911db9a5-d31f-4aac-a700-0911016150c5',"hbase.table.
    **default.storage.type" = "binary");


    This returns an OK. I then REFRESH in Impala Shell and run the following
    query


    select * from finaltable


    I get correct values returned for KEY and CUSTOMER. But no matter what
    type of mapping I try, I can not get any numeric columns (of any numeric
    type) to return anything except NULL. So at this point I can use Impala
    for strings in HBase but I have not figured out how to extract numerics.
    Any guidance or workarounds appreciated. Thank you.

    --
    Ricky Saltzer
    Customer Operations Engineer
    http://www.cloudera.com
  • Nenshad Bardoliwalla at Mar 27, 2013 at 9:20 pm
    Ricky, I really appreciate the lightning quick response, so thank you for
    that. Is there any way to do an "on-the-fly" double to string conversion
    in IMPALA? I believe this is possible in HIVE but don't think setting this
    up in the mapping will help.
    On Wednesday, March 27, 2013 2:02:34 PM UTC-7, Ricky Saltzer wrote:

    H*i *Nenshad -

    Unfortunately, Impala only supports ASCII data within HBase at the moment.
    As a workaround, the values would need to be stored as a string
    representation. This is a known limitation and something we are working on,
    but currently we do not have a timeframe as to when it will make it in.

    Let me know if I can help with anything else.

    Ricky


    On Wed, Mar 27, 2013 at 4:41 PM, Nenshad Bardoliwalla <nen...@gmail.com<javascript:>
    wrote:
    I am using the Impala 0.6 virtual machine with no other modifications
    other than populating HBase tables with my data. I want to be able to
    query my data in HBase via Impala and am not able to retrieve non-string
    (e.g. any numeric types) values in HBase.

    I have an HBase Table called '911db9a5-d31f-4aac-a700-0911016150c5'.
    There are three columns in the main0 column family:
    main0:\x00\x00\x00\x01, main0:\x00\x00\x00\x02, ,
    and main0:\x00\x00\x00\x03. main0:\x00\x00\x00\x01
    and main0:\x00\x00\x00\x03 store STRINGs, while main0:\x00\x00\x00\x02
    stores DOUBLEs. Scanning the table would return the following:

    ROW COLUMN+CELL


    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x01,
    timestamp=1, value=SUPERMARKETS

    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x02,
    timestamp=2, value=@\xFA\xDF\x90\x00\x00\x00\x00

    \x00\x00\x00\x00\x00\x00\x00\x01 column=main0:\x00\x00\x00\x03,
    timestamp=2, value=CHIEVE'S SUPERMARKET

    I create a mapping between my HBase table and Hive as follows:

    CREATE EXTERNAL TABLE finaltable (

    key string,

    customer string,

    custnbr double

    )


    STORED BY

    'org.apache.hadoop.hive.hbase.**HBaseStorageHandler'


    WITH SERDEPROPERTIES

    ("hbase.columns.mapping" = ":key,main0:\000\000\000\001#s,**
    main0:\000\000\000\002#b")


    TBLPROPERTIES

    ("hbase.table.name" =
    '911db9a5-d31f-4aac-a700-0911016150c5',"hbase.table.**default.storage.type"
    = "binary");


    This returns an OK. I then REFRESH in Impala Shell and run the following
    query


    select * from finaltable


    I get correct values returned for KEY and CUSTOMER. But no matter what
    type of mapping I try, I can not get any numeric columns (of any numeric
    type) to return anything except NULL. So at this point I can use Impala
    for strings in HBase but I have not figured out how to extract numerics.
    Any guidance or workarounds appreciated. Thank you.

    --
    Ricky Saltzer
    Customer Operations Engineer
    http://www.cloudera.com

  • Ricky Saltzer at Mar 27, 2013 at 9:48 pm
    Hey Nenshad -

    Since the data itself is stored in a format Impala currently does not
    understand how to read, I don't believe using a CAST would help. If you're
    not particularly concerned with querying live data, you could try loading
    the data from that table into another table (backed by text files, or
    rcfile, etc) using a INSERT OVERWRITE in Hive, then query that table using
    Impala.

    hope this helps

    Ricky

    On Wed, Mar 27, 2013 at 5:20 PM, Nenshad Bardoliwalla wrote:

    Ricky, I really appreciate the lightning quick response, so thank you for
    that. Is there any way to do an "on-the-fly" double to string conversion
    in IMPALA? I believe this is possible in HIVE but don't think setting this
    up in the mapping will help.

    On Wednesday, March 27, 2013 2:02:34 PM UTC-7, Ricky Saltzer wrote:

    H*i *Nenshad -

    Unfortunately, Impala only supports ASCII data within HBase at the
    moment. As a workaround, the values would need to be stored as a string
    representation. This is a known limitation and something we are working on,
    but currently we do not have a timeframe as to when it will make it in.

    Let me know if I can help with anything else.

    Ricky

    On Wed, Mar 27, 2013 at 4:41 PM, Nenshad Bardoliwalla wrote:

    I am using the Impala 0.6 virtual machine with no other modifications
    other than populating HBase tables with my data. I want to be able to
    query my data in HBase via Impala and am not able to retrieve non-string
    (e.g. any numeric types) values in HBase.

    I have an HBase Table called '911db9a5-d31f-4aac-a700-**0911016150c5'.
    There are three columns in the main0 column family:
    main0:\x00\x00\x00\x01, **main0:\x00\x00\x00\x02, ,
    and main0:\x00\x00\x00\x03. main0:\x00\x00\x00\x01
    and main0:\x00\x00\x00\x03 store STRINGs, while main0:\x00\x00\x00\x02
    stores DOUBLEs. Scanning the table would return the following:

    ROW COLUMN+CELL


    \x00\x00\x00\x00\x00\x00\x00\**x01 column=main0:\x00\x00\x00\**x01,
    timestamp=1, value=SUPERMARKETS

    \x00\x00\x00\x00\x00\x00\x00\**x01 column=main0:\x00\x00\x00\**x02,
    timestamp=2, value=@\xFA\xDF\x90\x00\x00\**x00\x00

    \x00\x00\x00\x00\x00\x00\x00\**x01 column=main0:\x00\x00\x00\**x03,
    timestamp=2, value=CHIEVE'S SUPERMARKET

    I create a mapping between my HBase table and Hive as follows:

    CREATE EXTERNAL TABLE finaltable (

    key string,

    customer string,

    custnbr double

    )


    STORED BY

    'org.apache.hadoop.hive.hbase.****HBaseStorageHandler'


    WITH SERDEPROPERTIES

    ("hbase.columns.mapping" = ":key,main0:\000\000\000\001#**s,**
    main0:\000\000\000\002#b")


    TBLPROPERTIES

    ("hbase.table.name" = '911db9a5-d31f-4aac-a700-**
    0911016150c5',"hbase.table.**def**ault.storage.type" = "binary");


    This returns an OK. I then REFRESH in Impala Shell and run the
    following query


    select * from finaltable


    I get correct values returned for KEY and CUSTOMER. But no matter what
    type of mapping I try, I can not get any numeric columns (of any numeric
    type) to return anything except NULL. So at this point I can use Impala
    for strings in HBase but I have not figured out how to extract numerics.
    Any guidance or workarounds appreciated. Thank you.

    --
    Ricky Saltzer
    Customer Operations Engineer
    http://www.cloudera.com


    --
    Ricky Saltzer
    Customer Operations Engineer
    http://www.cloudera.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupimpala-user @
categorieshadoop
postedMar 27, '13 at 8:41p
activeMar 27, '13 at 9:48p
posts4
users2
websitecloudera.com
irc#hadoop

People

Translate

site design / logo © 2022 Grokbase