Re: Extracting column values from Phoenix composite primary key

[Michael McAllister]

Anil

If you split the rowkey on the zero byte character, you should end up with the 
individual columns that made up your primary key. Details are here:-

https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallis...@homeaway.com | C: 512.423.7447 | 
skype: michael.mcallister.ha | webex: 
https://h.a/mikewebex
[cid:image001.png@01D1FE20.E42A21B0]
This electronic communication (including any attachment) is confidential.  If 
you are not an intended recipient of this communication, please be advised that 
any disclosure, dissemination, distribution, copying or other use of this 
communication or any attachment is strictly prohibited.  If you have received 
this communication in error, please notify the sender immediately by reply 
e-mail and promptly destroy all electronic and printed copies of this 
communication and any attachment.

From: Anil 
Reply-To: "user@phoenix.apache.org" 
Date: Wednesday, August 24, 2016 at 4:10 AM
To: "user@phoenix.apache.org" 
Subject: Re: Extracting column values from Phoenix composite primary key

Any inputs ? Thanks.

On 24 August 2016 at 11:13, Anil 
> wrote:
Hi,

I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please help.

Thanks,
Anil

Re: Phoenix and HBase data type serialization issue

[Ryan Templeton]

Gabriel, the PDataType subclass you mention, can I read more about this in a 
Javadoc somewhere?

Hbase provides the convenient static Bytes functions for performing the 
encoding and decoding. I’m guessing the PDataType is some kind of equivalent 
and that this is part of the Phoenix JDBC (fat) driver?

Thanks,
Ryan




On 8/24/16, 3:01 AM, "Gabriel Reid"  wrote:

>Hi Ankit,
>
>All data stored in HBase is stored in the form of byte arrays. The
>conversion from richer types (e.g. date) to byte arrays is one of the
>(many) functionalities included in Phoenix.
>
>When you add a date value in the form of a string to HBase directly
>(bypassing Phoenix), you're simply saving the byte representation of
>that string to HBase. Phoenix uses an encoded long value to store
>dates in HBase, so when you try to read your date value from HBase via
>Phoenix, it's simply interpreting the bytes as a long, which leads to
>the unexpected date value that you're getting.
>
>There are two options to do what you're doing: either (1) use Phoenix
>for both reading and writing data, or (2) use the PDataType subclasses
>(e.g. PDate, PLong, etc) to encode data before storing it to HBase.
>
>- Gabriel
>
>On Wed, Aug 24, 2016 at 9:40 AM, ankit beohar  wrote:
>> HI All,
>>
>> I have table in HBase and putting data into it then create phoenix view with
>> date, bigint etc data types but when I query from phoenix its giving me
>> wrong values I tried unassigned data types also but not working below are
>> stack:-
>>
>> ==Hbase===
>> hbase(main):057:0> create 'CEHCK_DT','0'
>> 0 row(s) in 2.2650 seconds
>>
>> => Hbase::Table - CEHCK_DT
>> hbase(main):058:0> put 'CEHCK_DT','row1','0:dates','2016-08-11'
>> 0 row(s) in 0.0080 seconds
>>
>> hbase(main):059:0> scan 'CEHCK_DT'
>> ROW  COLUMN+CELL
>>  row1column=0:dates,
>> timestamp=1471930977145, value=2016-08-11
>> 1 row(s) in 0.0100 seconds
>>
>>
>> =Phoenix=
>> 0: jdbc:phoenix:localhost:2181> create table "CEHCK_DT"(pk varchar primary
>> key,"0"."dates" date,"0"."SALARY" bigint);
>> No rows affected (0.347 seconds)
>> 0: jdbc:phoenix:localhost:2181> select "0"."dates" from "CEHCK_DT";
>> +---+
>> | dates |
>> +---+
>> | 177670840-04-13 05:44:22.317  |
>> | 177670840-04-13 05:44:22.317  |
>> | 177670840-04-13 05:44:22.317  |
>> +---+
>>
>>
>>
>> Best Regards,
>> ANKIT BEOHAR
>>
>

high client cpu usage

[Aaron Molitor]

Seeing higher than expected CPU/MEM usage for the java process started by the 
sqlline.py client. 

From top:
top - 14:37:32 up 7 days, 22:15,  2 users,  load average: 25.52, 9.74, 7.89
Tasks: 509 total,   1 running, 508 sleeping,   0 stopped,   0 zombie
Cpu(s): 61.2%us,  6.3%sy,  0.0%ni, 31.0%id,  0.5%wa,  0.0%hi,  1.0%si,  0.0%st
Mem:  65920564k total, 31913580k used, 34006984k free,   647004k buffers
Swap: 33030140k total,0k used, 33030140k free, 10464056k cached

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND





 8729 splice20   0 29.9g  14g 889m S 1616.8 22.4  26:19.21 java

Why is a the JDBC client using so much memory/cpu?  The expected result is only 
a single row.

QUERY:
SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY
FROM
  TPCH.LINEITEM,
  TPCH.PART
WHERE
  P_PARTKEY = L_PARTKEY
  AND P_BRAND = 'BRAND#23'
  AND P_CONTAINER = 'MED BOX'
  AND L_QUANTITY < (
SELECT 0.2 * AVG(L_QUANTITY)
FROM
  TPCH.LINEITEM
WHERE
  L_PARTKEY = P_PARTKEY
  );

ROW COUNTS: 
TPCH.REGION 5
TPCH.NATION25
TPCH.SUPPLIER 100
TPCH.PART2000
TPCH.PARTSUPP8000
TPCH.ORDERS 15000
TPCH.CUSTOMER1500
TPCH.LINEITEM   600037902 

Thanks, 
Aaron

Monitoring Running Queries

[John Leach]

Is there a UI, command line, or logging level to see the internal execution of 
the query?  I am trying to understand how much time is spent in each phase 
(Join, Aggregation, Etc.) of a multi-phase execution tree?

Regards,
John Leach

Re: Phoenix and HBase data type serialization issue

[Gabriel Reid]

Hi Ankit,

I'm not sure what the options are for encoding data via Talend -- if
you can convert data to the correct byte representation expected by
Phoenix via another way in Talend then it might also work. There is
information on the binary representation of various types on
http://phoenix.apache.org/language/datatypes.html.

Another option would be to go via JDBC if Talend has support for
writing data via JDBC.

A last (bad) option might be to store everything as strings, but that
will probably result in a bloated HBase and poor performance.

- Gabriel

On Wed, Aug 24, 2016 at 10:27 AM, ankit beohar  wrote:
> HI Gabriel,
>
> Thanks for your quick reply, but in my case I am using Talend ETL tool to
> ingest the data into HBase, so I can not use PDataType subclasses and in
> Talend Phoenix connector is not avaialble.
> Is there any other way?
>
> Best Regards,
> ANKIT BEOHAR
>
>
> On Wed, Aug 24, 2016 at 1:31 PM, Gabriel Reid 
> wrote:
>>
>> Hi Ankit,
>>
>> All data stored in HBase is stored in the form of byte arrays. The
>> conversion from richer types (e.g. date) to byte arrays is one of the
>> (many) functionalities included in Phoenix.
>>
>> When you add a date value in the form of a string to HBase directly
>> (bypassing Phoenix), you're simply saving the byte representation of
>> that string to HBase. Phoenix uses an encoded long value to store
>> dates in HBase, so when you try to read your date value from HBase via
>> Phoenix, it's simply interpreting the bytes as a long, which leads to
>> the unexpected date value that you're getting.
>>
>> There are two options to do what you're doing: either (1) use Phoenix
>> for both reading and writing data, or (2) use the PDataType subclasses
>> (e.g. PDate, PLong, etc) to encode data before storing it to HBase.
>>
>> - Gabriel
>>
>> On Wed, Aug 24, 2016 at 9:40 AM, ankit beohar 
>> wrote:
>> > HI All,
>> >
>> > I have table in HBase and putting data into it then create phoenix view
>> > with
>> > date, bigint etc data types but when I query from phoenix its giving me
>> > wrong values I tried unassigned data types also but not working below
>> > are
>> > stack:-
>> >
>> > ==Hbase===
>> > hbase(main):057:0> create 'CEHCK_DT','0'
>> > 0 row(s) in 2.2650 seconds
>> >
>> > => Hbase::Table - CEHCK_DT
>> > hbase(main):058:0> put 'CEHCK_DT','row1','0:dates','2016-08-11'
>> > 0 row(s) in 0.0080 seconds
>> >
>> > hbase(main):059:0> scan 'CEHCK_DT'
>> > ROW  COLUMN+CELL
>> >  row1
>> > column=0:dates,
>> > timestamp=1471930977145, value=2016-08-11
>> > 1 row(s) in 0.0100 seconds
>> >
>> >
>> > =Phoenix=
>> > 0: jdbc:phoenix:localhost:2181> create table "CEHCK_DT"(pk varchar
>> > primary
>> > key,"0"."dates" date,"0"."SALARY" bigint);
>> > No rows affected (0.347 seconds)
>> > 0: jdbc:phoenix:localhost:2181> select "0"."dates" from "CEHCK_DT";
>> > +---+
>> > | dates |
>> > +---+
>> > | 177670840-04-13 05:44:22.317  |
>> > | 177670840-04-13 05:44:22.317  |
>> > | 177670840-04-13 05:44:22.317  |
>> > +---+
>> >
>> >
>> >
>> > Best Regards,
>> > ANKIT BEOHAR
>> >
>
>

Re: Phoenix and HBase data type serialization issue

[Gabriel Reid]

Hi Ankit,

All data stored in HBase is stored in the form of byte arrays. The
conversion from richer types (e.g. date) to byte arrays is one of the
(many) functionalities included in Phoenix.

When you add a date value in the form of a string to HBase directly
(bypassing Phoenix), you're simply saving the byte representation of
that string to HBase. Phoenix uses an encoded long value to store
dates in HBase, so when you try to read your date value from HBase via
Phoenix, it's simply interpreting the bytes as a long, which leads to
the unexpected date value that you're getting.

There are two options to do what you're doing: either (1) use Phoenix
for both reading and writing data, or (2) use the PDataType subclasses
(e.g. PDate, PLong, etc) to encode data before storing it to HBase.

- Gabriel

On Wed, Aug 24, 2016 at 9:40 AM, ankit beohar  wrote:
> HI All,
>
> I have table in HBase and putting data into it then create phoenix view with
> date, bigint etc data types but when I query from phoenix its giving me
> wrong values I tried unassigned data types also but not working below are
> stack:-
>
> ==Hbase===
> hbase(main):057:0> create 'CEHCK_DT','0'
> 0 row(s) in 2.2650 seconds
>
> => Hbase::Table - CEHCK_DT
> hbase(main):058:0> put 'CEHCK_DT','row1','0:dates','2016-08-11'
> 0 row(s) in 0.0080 seconds
>
> hbase(main):059:0> scan 'CEHCK_DT'
> ROW  COLUMN+CELL
>  row1column=0:dates,
> timestamp=1471930977145, value=2016-08-11
> 1 row(s) in 0.0100 seconds
>
>
> =Phoenix=
> 0: jdbc:phoenix:localhost:2181> create table "CEHCK_DT"(pk varchar primary
> key,"0"."dates" date,"0"."SALARY" bigint);
> No rows affected (0.347 seconds)
> 0: jdbc:phoenix:localhost:2181> select "0"."dates" from "CEHCK_DT";
> +---+
> | dates |
> +---+
> | 177670840-04-13 05:44:22.317  |
> | 177670840-04-13 05:44:22.317  |
> | 177670840-04-13 05:44:22.317  |
> +---+
>
>
>
> Best Regards,
> ANKIT BEOHAR
>

Phoenix and HBase data type serialization issue

[ankit beohar]

HI All,

I have table in HBase and putting data into it then create phoenix view
with date, bigint etc data types but when I query from phoenix its giving
me wrong values I tried unassigned data types also but not working below
are stack:-

==Hbase===
hbase(main):057:0> create 'CEHCK_DT','0'
0 row(s) in 2.2650 seconds

=> Hbase::Table - CEHCK_DT
hbase(main):058:0> put 'CEHCK_DT','row1','0:dates','2016-08-11'
0 row(s) in 0.0080 seconds

hbase(main):059:0> scan 'CEHCK_DT'
ROW  COLUMN+CELL
 row1
 column=0:dates, timestamp=1471930977145, value=2016-08-11
1 row(s) in 0.0100 seconds


=Phoenix=
0: jdbc:phoenix:localhost:2181> create table "CEHCK_DT"(pk varchar primary
key,"0"."dates" date,"0"."SALARY" bigint);
No rows affected (0.347 seconds)
0: jdbc:phoenix:localhost:2181> select "0"."dates" from "CEHCK_DT";
+---+
| dates |
+---+
| 177670840-04-13 05:44:22.317  |
| 177670840-04-13 05:44:22.317  |
| 177670840-04-13 05:44:22.317  |
+---+



Best Regards,
ANKIT BEOHAR