phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ganesh R <rganes...@yahoo.co.in>
Subject Re: Incorrect data retrieval: Phoenix table on HBase
Date Sat, 28 Feb 2015 21:33:34 GMT
Thank you James. I was assuming that Phoenix provides it out of the box for basic types using
java serialization... :)If there is a way to provide a phoenix 'hint' on serialization type
(while using 'view' on HBase), that will be another good feature too. 
Thanks!
 

     On Saturday, 28 February 2015 11:22 AM, James Taylor <jamestaylor@apache.org> wrote:
   

 When you create your view from an HBase table, you need to tell
Phoenix your types. Like you said, your values are an arbitrary array
of bytes. How would Phoenix know how they were serialized?

In general, storing everything as strings won't get you very far in
terms of querying (in Phoenix or HBase). Best to store in a way that
allows range queries. You could use Phoenix to do this conversion for
you with a few lines of SQL:

CREATE VIEW P_VIEW_TEST(
        DT_VAL          VARCHAR,
        FT_VAL          VARCHAR,
        IT_VAL          VARCHAR
) default_column_family='DATA';

CREATE TABLE P_TABLE_TEST(
        DT_VAL          DATE,
        FT_VAL          FLOAT,
        IT_VAL          INTEGER
) default_column_family='DATA';

UPSERT INTO P_TABLE_TEST
SELECT to_date(DT_VAL),
    cast(to_number(FT_VAL) AS FLOAT),
    cast(to_number(IT_VAL) AS INTEGER)
FROM P_VIEW_TEST;

On Sat, Feb 28, 2015 at 10:32 AM, Ganesh R <rganesh84@yahoo.co.in> wrote:
> Well, like i said the issue is not with Phoenix view on Phoenix table.
>
> Issue: Phoenix view on HBase table.
>
> Anything which goes inside HBase goes as an array of bytes. Its
> responsibility of client (Phoenix view in this case) to convert it into
> appropriate data types.
>
> Steps to reproduce:
> - Create a HBase table (using HBase shell)
> - Put values in column identifiers. Values can be date, int, float (as per
> my initial post).
> - Create Phoenix view with appropriate data types (date, int, float and not
> varchar for all cols).
> - Run a select query. You won't see the same values are you'll see in HBase
> shell get command.
>
> Refer the link if above steps aren't clear -
> https://github.com/forcedotcom/phoenix/issues/316 (I am trying to do
> something similar, not the same)
>
> Question: Does phoenix view on pre-existing HBase table support only
> varchars?? or is it an issue/enhancement?
>
>
>
> On Saturday, 28 February 2015 10:12 AM, Dan Di Spaltro
> <dan.dispaltro@gmail.com> wrote:
>
>
> It looks like you have everything stored as strings in the original table.
> So if you created the view over an existing table, they would need to all be
> VARCHARs.
>
>
> -Dan
>
> On Sat, Feb 28, 2015 at 10:06 AM, Ganesh R <rganesh84@yahoo.co.in> wrote:
>
> Naga,
>
> The issue is not with Phoenix view on Phoenix table. it's with Phoenix view
> on HBase table.
> Refer my initial post to reproduce it.
>
> Thanks!
>
>
> On Saturday, 28 February 2015 6:42 AM, Naga Vijayapuram
> <Naga_Vijayapuram@gap.com> wrote:
>
>
> Please check your view creation statement.  It works for me …
>
>> create table NV.T1 (name varchar(30), age integer, grade float, dob date
>> not null, constraint nv_t1_pk primary key (name, dob));
>
>> create view NV.V1 as select * from NV.T1;
>
>> upsert into NV.T1 values ('John Doe', 23, 3.5, to_date('1981-07-01
>> 04:02:01'));
>
>> select * from NV.T1;
> +------------+------------+------------+---------------------+
> |    NAME    |    AGE    |  GRADE    |        DOB        |
> +------------+------------+------------+---------------------+
> | John Doe  | 23        | 3.5        | 1981-07-01          |
> +------------+------------+------------+---------------------+
>
>> select * from NV.V1;
> +------------+------------+------------+---------------------+
> |    NAME    |    AGE    |  GRADE    |        DOB        |
> +------------+------------+------------+---------------------+
> | John Doe  | 23        | 3.5        | 1981-07-01          |
> +------------+------------+------------+---------------------+
>
> Naga
>
>
> On Feb 27, 2015, at 12:23 PM, Ganesh R <rganesh84@yahoo.co.in> wrote:
>
> Hello,
> I am trying to create phoenix table with appropriate data types on existing
> HBase table.
>
> HBase table:
>
> hbase(main):017:0> get 'P_VIEW_TEST', '1'
> COLUMN                                        CELL
>  DATA:DT_VAL                                  timestamp=1425066171071,
> value=2015-02-27 00:00:00.000
>  DATA:FT_VAL                                  timestamp=1425066195810,
> value=3.29
>  DATA:IT_VAL                                  timestamp=1425067277161,
> value=4345
>
> Phoenix table:
>
> CREATE VIEW P_VIEW_TEST(
>        DT_VAL          DATE,
>        FT_VAL          FLOAT,
>        IT_VAL          INTEGER
> ) default_column_family='DATA';
>
> -- View gets created -- No issues.
> However when i query the view.
>
> select * from P_VIEW_TEST;
>
> +---------------------+------------+------------+
> |      DT_VAL        |  FT_VAL          |  IT_VAL  |
> +---------------------+------------+------------+
> | 840-02-22          | -1.09997624E8 |    -1271712715 |
> +---------------------+------------+------------+
>
> So to retrieve right data in Phoenix view (on Hbase table), the view should
> always have all column types as "varchar" ??
>
> Thanks,
> Ganesh R
>
>
>
>
>
>
>

   
Mime
View raw message