date:20180419

Re: 答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

2018-04-19 Thread Sergey Soldatov

Definitely, someone who is maintaining CDH branch should take a look. I
don't observer that behavior on the master branch:

0: jdbc:phoenix:thin:url=http://localhost:876> create table if not exists
testarray(id bigint not null, events bigint array constraint pk primary key
(id));
No rows affected (2.4 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876> upsert into testarray values
(1, array[1,2]);
1 row affected (0.056 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876> select * from testarray;
+-+-+
| ID  | EVENTS  |
+-+-+
| 1   | [1, 2]  |
+-+-+
1 row selected (0.068 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876>


Thanks,
Sergey

On Thu, Apr 19, 2018 at 12:57 PM, Lu Wei  wrote:

> by the way, all the queries are shot in sqlline-thin.py
>
>
>
> --
> *发件人:* Lu Wei
> *发送时间:* 2018年4月19日 6:51:15
> *收件人:* user@phoenix.apache.org
> *主题:* 答复: phoenix query server java.lang.ClassCastException for BIGINT
> ARRAY column
>
>
> ## Version:
> phoenix: 4.13.2-cdh5.11.2
> hive: 1.1.0-cdh5.11.2
>
> to reproduce:
>
> -- create table
>
> create table if not exists testarray(id bigint not null, events bigint
> array constraint pk primary key (id))
>
>
> -- upsert data:
>
> upsert into testarray values (1, array[1,2]);
>
>
> -- query:
>
> select id from testarray;   -- fine
>
> select * from testarray;-- error
> --
> *发件人:* sergey.solda...@gmail.com  代表 Sergey
> Soldatov 
> *发送时间:* 2018年4月19日 6:37:06
> *收件人:* user@phoenix.apache.org
> *主题:* Re: phoenix query server java.lang.ClassCastException for BIGINT
> ARRAY column
>
> Could you please be more specific? Which version of phoenix are you using?
> Do you have a small script to reproduce? At first glance it looks like a
> PQS bug.
>
> Thanks,
> Sergey
>
> On Thu, Apr 19, 2018 at 8:17 AM, Lu Wei  wrote:
>
> Hi there,
>
> I have a phoenix table containing an BIGINT ARRAY column. But when
> querying query server (through sqlline-thin.py), there is an exception:
>
> java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Long
>
> BTW, when query through sqlline.py, everything works fine. And data in
> HBase table are of Long type, so why does the Integer to Long cast happen?
>
>
> ## Table schema:
>
> create table if not exists gis_tracking3(tracking_object_id bigint not
> null, lat double, lon double, speed double, bearing double, time timestamp
> not null, events bigint array constraint pk primary key
> (tracking_object_id, time))
>
>
> ## when query events[1], it works fine:
>
> 0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events[1]+1 from
> gis_tracking3;
> +--+
> | (ARRAY_ELEM(EVENTS, 1) + 1)  |
> +--+
> | 11   |
> | 2223 |
> | null |
> | null |
> | 10001|
> +--+
>
>
> ## when querying events, it throws exception:
>
> 0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events from
> gis_tracking3;
> java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Long
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.Ab
> stractCursor$LongAccessor.getLong(AbstractCursor.java:550)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.Ab
> stractCursor$ArrayAccessor.convertValue(AbstractCursor.java:1310)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.Ab
> stractCursor$ArrayAccessor.getObject(AbstractCursor.java:1289)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.Ab
> stractCursor$ArrayAccessor.getArray(AbstractCursor.java:1342)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.Ab
> stractCursor$ArrayAccessor.getString(AbstractCursor.java:1354)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.Avatica
> ResultSet.getString(AvaticaResultSet.java:257)
>   at sqlline.Rows$Row.(Rows.java:183)
>   at sqlline.BufferedRows.(BufferedRows.java:38)
>   at sqlline.SqlLine.print(SqlLine.java:1660)
>   at sqlline.Commands.execute(Commands.java:833)
>   at sqlline.Commands.sql(Commands.java:732)
>   at sqlline.SqlLine.dispatch(SqlLine.java:813)
>   at sqlline.SqlLine.begin(SqlLine.java:686)
>   at sqlline.SqlLine.start(SqlLine.java:398)
>   at sqlline.SqlLine.main(SqlLine.java:291)
>   at org.apache.phoenix.queryserver.client.SqllineWrapper.main(Sq
> llineWrapper.java:93)
>
>
> I guess there is some issue in query sever, but can't figure out why.
>
> Any suggestions?
>
>
>
> Thanks,
>
> Wei
>
>
>

Re: hbase cell storage different bewteen bulk load and direct api

2018-04-19 Thread James Taylor

I believe we still rely on that empty key value, even for compact storage
formats (though theoretically it could likely be made so we don't - JIRA,
please?) A quick test would confirm:
- upsert a row with no last_name or first_name
- select * from T where last_name IS NULL
If the row isn't returned, then we need that empty key value.

Thanks,
James

On Thu, Apr 19, 2018 at 1:58 PM, Sergey Soldatov 
wrote:

> Heh. That looks like a bug actually. This is a 'dummy' KV (
> https://phoenix.apache.org/faq.html#Why_empty_key_value), but I have some
> doubts that we need it for compacted rows.
>
> Thanks,
> Sergey
>
> On Thu, Apr 19, 2018 at 11:30 PM, Lew Jackman  wrote:
>
>> I have not tried the master yet branch yet, however on Phoenix 4.13 this
>> storage discrepancy in hbase is still present with the extra
>> column=M:\x00\x00\x00\x00 cells in hbase when using psql or sqlline.
>>
>> Does anyone have an understanding of the meaning of the column qualifier
>> \x00\x00\x00\x00 ?
>>
>>
>> -- Original Message --
>> From: "Lew Jackman" 
>> To: user@phoenix.apache.org
>> Cc: user@phoenix.apache.org
>> Subject: Re: hbase cell storage different bewteen bulk load and direct api
>> Date: Thu, 19 Apr 2018 13:59:16 GMT
>>
>> The upsert statement appears the same as the psql results - i.e. extra
>> cells. I will try the master branch next. Thanks for the tip.
>>
>> -- Original Message --
>> From: Sergey Soldatov 
>> To: user@phoenix.apache.org
>> Subject: Re: hbase cell storage different bewteen bulk load and direct api
>> Date: Thu, 19 Apr 2018 12:26:25 +0600
>>
>> Hi Lew,
>> no. 1st one looks line incorrect. You may file a bug on that ( I believe
>> that the second case is correct, but you may also check with uploading data
>> using regular upserts). Also, you may check whether the master branch has
>> this issue.
>>
>> Thanks,
>> Sergey
>>
>> On Thu, Apr 19, 2018 at 10:19 AM, Lew Jackman 
>> wrote:
>>
>>> Under Phoenix 4.11 we are seeing some storage discrepancies in hbase
>>> between a load via psql and a bulk load.
>>>
>>> To illustrate in a simple case we have modified the example table from
>>> the load reference https://phoenix.apache.org/bulk_dataload.html
>>>
>>> CREATE TABLE example (
>>> Â Â Â my_pk bigint not null,
>>> Â Â Â m.first_name varchar(50),
>>> Â Â Â m.last_name varchar(50)
>>> Â Â Â CONSTRAINT pk PRIMARY KEY (my_pk))
>>> Â Â Â IMMUTABLE_ROWS=true,
>>> Â Â Â IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS,
>>> Â Â Â COLUMN_ENCODED_BYTES = 1;
>>>
>>> Hbase Rows when Loading via PSQL
>>>
>>> x80x00x00x00x00x0009
>>> Â Â Â Â column=M:x00x00x00x00,
>>> timestamp=1524109827690, value=x Â Â Â Â Â Â Â Â Â Â Â Â Â
>>> x80x00x00x00x00x0009
>>> Â Â Â Â column=M:1, timestamp=1524109827690, value=xJohnDoex00\
>>> \\\x00x00x01x00x05x0
>>> 0x00x00x08x00x00x00x03x02
>>> Â Â Â Â Â Â Â Â Â Â Â Â Â
>>> x80x00x00x00x00x01x092
>>> Â column=M:x00x00x00x00,
>>> timestamp=1524109827690, value=x Â Â Â Â Â Â Â Â Â Â Â Â Â
>>> x80x00x00x00x00x01x092
>>> Â column=M:1, timestamp=1524109827690, value=xMaryPoppinsx00\
>>> \\\x00x00x01x00x05\\
>>> \\x00x00x00x0Cx00x00
>>> x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>>>
>>> Hbase Rows when Loading via MapReduce using CsvBulkLoadTool
>>>
>>> x80x00x00x00x00x0009
>>> Â Â Â Â column=M:1, timestamp=1524110486638, value=xJohnDoex00\
>>> \\\x00x00x01x00x05x0
>>> 0x00x00x08x00x00x00x03x02
>>> Â Â Â Â Â Â Â Â Â Â Â Â Â
>>> x80x00x00x00x00x01x092
>>> Â column=M:1, timestamp=1524110486638, value=xMaryPoppinsx00\
>>> \\\x00x00x01x00x05\\
>>> \\x00x00x00x0Cx00x00
>>> x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>>>
>>>
>>> So, the bulk loaded tables have 4 cells for the two rows loaded via psql
>>> whereas a bulk load is missing two cells since it lacks the cells with col
>>> qualifierÂ :x00x00x00x00
>>> Â
>>> Is this behavior correct?
>>> Â
>>> Thanks much for any insight.
>>> Â
>>>
>>>
>>> 
>>> *How To "Remove" Dark Spots*
>>> Gundry MD
>>>
>>>

Re: hint to use a global index is not working - need to find out why

2018-04-19 Thread Sergey Soldatov

That looks strange. Could you please provide full DDLs for table and
indexes? I just tried a similar scenario and obviously index is used:

0: jdbc:phoenix:> create table VARIANTJOIN_RTSALTED24 (id integer primary
key, chrom_int integer, genomic_range integer);
No rows affected (6.339 seconds)
0: jdbc:phoenix:>create index jv2_chrom_int on VARIANTJOIN_RTSALTED24
(chrom_int);
No rows affected (10.016 seconds)
0: jdbc:phoenix:> explain SELECT/*+ INDEX(VJ jv2_chrom_int) */
VJ.chrom_int, genomic_range  FROM VARIANTJOIN_RTSALTED24 as VJ WHERE
(chrom_int =18 ) limit 5;
+---+
| PLAN
|
+---+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER
VARIANTJOIN_RTSALTED24   |
| CLIENT 5 ROW LIMIT
|
| SKIP-SCAN-JOIN TABLE 0
|
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER
JV2_CHROM_INT [18]  |
| SERVER FILTER BY FIRST KEY ONLY
 |
| DYNAMIC SERVER FILTER BY "VJ.ID" IN ($2.$4)
 |
| JOIN-SCANNER 5 ROW LIMIT
|
+---+
7 rows selected (0.936 seconds)


Thanks,
Sergey

On Thu, Apr 19, 2018 at 7:31 PM, Taylor, Ronald (Ronald) <
ronald.tay...@cchmc.org> wrote:

> Hello Phoenix users,
>
> I am a novice Phoenix user and this is my first post to this user list. I
> did some searching in the list archives, but could not find an answer to
> what I hope is a simple question: my global index is being ignored, even
> after I add a Hint, and I want to know why.
>
> We are using Phoenix 4.7 in the Hortonworks distribution. Looks like
> Hortonworks has been backporting at least some phoenix updates into their
> version of phoenix 4.7, so I guess it is a custom distribution. See
>
>
>
>  https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.4/
> bk_release-notes/content/patch_phoenix.html
>
>
>
> I have created a simple table of about 8 million rows, and about 15
> columns, with several fields having global indexes. I created the main
> table (variantjoin_rtsalted24) and its indexes, and then used a bulk loader
> to populate them from a tab-delimited file. That appeared to work fine.
>
> chrom_int is one field on which there is a global index, named
> vj2_chrom_int. And you can see the index being automatically being used
> below, where it is the only field being returned. Time required is 0.124
> sec.
>
> 0: jdbc:phoenix:> SELECT VJ.chrom_int  FROM VARIANTJOIN_RTSALTED24 as VJ
> WHERE (chrom_int =18 ) limit 5;
>
> ++
>
> | CHROM_INT  |
>
> ++
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> ++
>
> 5 rows selected (0.124 seconds)
>
> 0: jdbc:phoenix:>
>
> You can see that the vj2_chrom_int index is automatically being used, as I
> understand things  by the "RANGE SCAN" wording and "[0,1" in the explain
> plan:
>
> 0: jdbc:phoenix:> explain SELECT VJ.chrom_int  FROM VARIANTJOIN_RTSALTED24
> as VJ WHERE (chrom_int =18 ) limit 5;
>
> +---
> ---+
>
> |   PLAN
> |
>
> +---
> ---+
>
> | CLIENT 24-CHUNK SERIAL 24-WAY ROUND ROBIN RANGE SCAN OVER VJ2_CHROM_INT
> [0,1 |
>
> | SERVER FILTER BY FIRST KEY ONLY
> |
>
> | SERVER 5 ROW LIMIT
> |
>
> | CLIENT 5 ROW LIMIT
>   |
>
> +---
> ---+
>
> 4 rows selected (0.043 seconds)
>
> 0: jdbc:phoenix:>
>
>
> I can use a Hint to tell Phoenix to NOT use this index, as seen below. And
> that increases the time needed to 1.97 sec, over an order of magnitude more
> time than the 0.124 sec required with index use.
>
> 0: jdbc:phoenix:> SELECT /*+ NO_INDEX */ VJ.chrom_int  FROM
> VARIANTJOIN_RTSALTED24 as VJ WHERE (chrom_int =18 ) limit 5;
>
> ++
>
> | CHROM_INT  |
>
> ++
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> | 18 |
>
> ++
>
> 5 rows selected (1.977 seconds)
>
> 0: jdbc:phoenix:>
>
> And here is the explain plan for that:
>
>
> 0: jdbc:phoenix:> explain SELECT /*+ NO_INDEX */ VJ.chrom_int  FROM
> VARIANTJOIN_RTSALTED24 as VJ WHERE (chrom_int =18 ) limit 5;
>
> +---
> ---+
>
> |
> PLAN  |
>
> +---
> ---+
>
> | CLIENT 72-CHUNK 14325202 ROWS 15099524157 BYTES PARALLEL 24-WAY ROUND
> ROBIN  |
>
> | SERVER FILTER BY CHROM_INT = 18
> |
>
> | SERVER 5 ROW LIMIT
> |
>
> | CLIENT 5

Re: hbase cell storage different bewteen bulk load and direct api

2018-04-19 Thread Sergey Soldatov

Heh. That looks like a bug actually. This is a 'dummy' KV (
https://phoenix.apache.org/faq.html#Why_empty_key_value), but I have some
doubts that we need it for compacted rows.

Thanks,
Sergey

On Thu, Apr 19, 2018 at 11:30 PM, Lew Jackman  wrote:

> I have not tried the master yet branch yet, however on Phoenix 4.13 this
> storage discrepancy in hbase is still present with the extra
> column=M:\x00\x00\x00\x00 cells in hbase when using psql or sqlline.
>
> Does anyone have an understanding of the meaning of the column qualifier
> \x00\x00\x00\x00 ?
>
>
> -- Original Message --
> From: "Lew Jackman" 
> To: user@phoenix.apache.org
> Cc: user@phoenix.apache.org
> Subject: Re: hbase cell storage different bewteen bulk load and direct api
> Date: Thu, 19 Apr 2018 13:59:16 GMT
>
> The upsert statement appears the same as the psql results - i.e. extra
> cells. I will try the master branch next. Thanks for the tip.
>
> -- Original Message --
> From: Sergey Soldatov 
> To: user@phoenix.apache.org
> Subject: Re: hbase cell storage different bewteen bulk load and direct api
> Date: Thu, 19 Apr 2018 12:26:25 +0600
>
> Hi Lew,
> no. 1st one looks line incorrect. You may file a bug on that ( I believe
> that the second case is correct, but you may also check with uploading data
> using regular upserts). Also, you may check whether the master branch has
> this issue.
>
> Thanks,
> Sergey
>
> On Thu, Apr 19, 2018 at 10:19 AM, Lew Jackman  wrote:
>
>> Under Phoenix 4.11 we are seeing some storage discrepancies in hbase
>> between a load via psql and a bulk load.
>>
>> To illustrate in a simple case we have modified the example table from
>> the load reference https://phoenix.apache.org/bulk_dataload.html
>>
>> CREATE TABLE example (
>> Â Â Â my_pk bigint not null,
>> Â Â Â m.first_name varchar(50),
>> Â Â Â m.last_name varchar(50)
>> Â Â Â CONSTRAINT pk PRIMARY KEY (my_pk))
>> Â Â Â IMMUTABLE_ROWS=true,
>> Â Â Â IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS,
>> Â Â Â COLUMN_ENCODED_BYTES = 1;
>>
>> Hbase Rows when Loading via PSQL
>>
>> x80x00x00x00x00x0009
>> Â Â Â Â column=M:x00x00x00x00,
>> timestamp=1524109827690, value=x Â Â Â Â Â Â Â Â Â Â Â Â Â
>> x80x00x00x00x00x0009
>> Â Â Â Â column=M:1, timestamp=1524109827690, value=xJohnDoex00\
>> \\\x00x00x01x00x05
>> x00x00x00x08x00x00\\
>> \\x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>> x80x00x00x00x00x01x092
>> Â column=M:x00x00x00x00,
>> timestamp=1524109827690, value=x Â Â Â Â Â Â Â Â Â Â Â Â Â
>> x80x00x00x00x00x01x092
>> Â column=M:1, timestamp=1524109827690, value=xMaryPoppinsx00\
>> \\\x00x00x01x00x05\
>> \\\x00x00x00x0Cx00
>> x00x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>>
>> Hbase Rows when Loading via MapReduce using CsvBulkLoadTool
>>
>> x80x00x00x00x00x0009
>> Â Â Â Â column=M:1, timestamp=1524110486638, value=xJohnDoex00\
>> \\\x00x00x01x00x05
>> x00x00x00x08x00x00\\
>> \\x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>> x80x00x00x00x00x01x092
>> Â column=M:1, timestamp=1524110486638, value=xMaryPoppinsx00\
>> \\\x00x00x01x00x05\
>> \\\x00x00x00x0Cx00
>> x00x00x03x02 Â Â Â Â Â Â Â Â Â Â Â Â Â
>>
>>
>> So, the bulk loaded tables have 4 cells for the two rows loaded via psql
>> whereas a bulk load is missing two cells since it lacks the cells with col
>> qualifierÂ :x00x00x00x00
>> Â
>> Is this behavior correct?
>> Â
>> Thanks much for any insight.
>> Â
>>
>>
>> 
>> *How To "Remove" Dark Spots*
>> Gundry MD
>> 
>> http://thirdpartyoffers.netzero.net/TGL3232/5ad818ce6211c18ce6b13st04vuc
>> [image: SponsoredBy Content.Ad]
>
>

Re: pheonix client

2018-04-19 Thread Josh Elser


This question is better asked on the Phoenix users list.

The phoenix-client.jar is the one you need and is unique from the 
phoenix-core jar. Logging frameworks are likely not easily 
relocated/shaded to avoid issues which is why you're running into this.


Can you provide the error you're seeing with the play framework? 
Specifics here will help..


On 4/19/18 1:56 PM, Lian Jiang wrote:

I am using HDP 2.6 hbase and pheonix. I created a play rest service using
hbase as the backend. However, I have trouble to get a working pheonix
client.

I tried the pheonix-client.jar given by HDP but its logging dependency
conflicts with play's. Then I tried:

libraryDependencies += "org.apache.phoenix" % "phoenix-core" %
"4.13.1-HBase-1.1"

libraryDependencies += "org.apache.phoenix" % "phoenix-server-client" %
"4.7.0-HBase-1.1"

libraryDependencies += "org.apache.phoenix" % "phoenix-queryserver-client"
% "4.13.1-HBase-1.1"

None of them worked: "No suitable driver found".

Any idea will be highly appreciated!

Re: hbase cell storage different bewteen bulk load and direct api

2018-04-19 Thread Lew Jackman

I have not tried the master yet branch yet, however on Phoenix 4.13 this 
storage discrepancy in hbase is still present with the extra 
column=M:\x00\x00\x00\x00 cells in hbase when using psql or sqlline. Does 
anyone have an understanding of the meaning of the column qualifier 
\x00\x00\x00\x00 ?

-- Original Message --
From: "Lew Jackman" 
To: user@phoenix.apache.org
Cc: user@phoenix.apache.org
Subject: Re: hbase cell storage different bewteen bulk load and direct api
Date: Thu, 19 Apr 2018 13:59:16 GMT


The upsert statement appears the same as the psql results - i.e. extra cells. I 
will try the master branch next. Thanks for the tip.

-- Original Message --
From: Sergey Soldatov 
To: user@phoenix.apache.org
Subject: Re: hbase cell storage different bewteen bulk load and direct api
Date: Thu, 19 Apr 2018 12:26:25 +0600

Hi Lew,no. 1st one looks line incorrect. You may file a bug on that ( I believe 
that the second case is correct, but you may also check with uploading data 
using regular upserts). Also, you may check whether the master branch has this 
issue. Thanks,Sergey
On Thu, Apr 19, 2018 at 10:19 AM, Lew Jackman  wrote:
Under Phoenix 4.11 we are seeing some storage discrepancies in hbase between a 
load via psql and a bulk load.

To illustrate in a simple case we have modified the example table from the load 
reference https://phoenix.apache.org/bulk_dataload.html

CREATE TABLE example (
 Â Â Â my_pk bigint not null,
 Â Â Â m.first_name varchar(50),
 Â Â Â m.last_name varchar(50) 
 Â Â Â CONSTRAINT pk PRIMARY KEY (my_pk))
 Â Â Â IMMUTABLE_ROWS=true,
 Â Â Â IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS,
 Â Â Â COLUMN_ENCODED_BYTES = 1;

Hbase Rows when Loading via PSQL

 x80x00x00x00x00x0009 Â Â Â Â 
column=M:x00x00x00x00, timestamp=1524109827690, 
value=x Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x0009 Â Â Â Â 
column=M:1, timestamp=1524109827690, 
value=xJohnDoex00x00x00x01x00x05x00x00x00x08x00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 
Â column=M:x00x00x00x00, 
timestamp=1524109827690, value=x Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 
Â column=M:1, timestamp=1524109827690, 
value=xMaryPoppinsx00x00x00x01x00x05x00x00x00x0Cx00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 

Hbase Rows when Loading via MapReduce using CsvBulkLoadTool 

 x80x00x00x00x00x0009 Â Â Â Â 
column=M:1, timestamp=1524110486638, 
value=xJohnDoex00x00x00x01x00x05x00x00x00x08x00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 
Â column=M:1, timestamp=1524110486638, 
value=xMaryPoppinsx00x00x00x01x00x05x00x00x00x0Cx00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 


So, the bulk loaded tables have 4 cells for the two rows loaded via psql 
whereas a bulk load is missing two cells since it lacks the cells with col 
qualifierÂ :x00x00x00x00Â Is this behavior 
correct?Â Thanks much for any insight.Â 


How To "Remove" Dark Spots
Gundry MD
http://thirdpartyoffers.netzero.net/TGL3232/5ad818ce6211c18ce6b13st04vuc

Re: hbase cell storage different bewteen bulk load and direct api

2018-04-19 Thread Lew Jackman

The upsert statement appears the same as the psql results - i.e. extra cells. I 
will try the master branch next. Thanks for the tip.

-- Original Message --
From: Sergey Soldatov 
To: user@phoenix.apache.org
Subject: Re: hbase cell storage different bewteen bulk load and direct api
Date: Thu, 19 Apr 2018 12:26:25 +0600


Hi Lew,no. 1st one looks line incorrect. You may file a bug on that ( I believe 
that the second case is correct, but you may also check with uploading data 
using regular upserts). Also, you may check whether the master branch has this 
issue. Thanks,Sergey
On Thu, Apr 19, 2018 at 10:19 AM, Lew Jackman  wrote:
Under Phoenix 4.11 we are seeing some storage discrepancies in hbase between a 
load via psql and a bulk load.

To illustrate in a simple case we have modified the example table from the load 
reference https://phoenix.apache.org/bulk_dataload.html

CREATE TABLE example (
 Â Â Â my_pk bigint not null,
 Â Â Â m.first_name varchar(50),
 Â Â Â m.last_name varchar(50) 
 Â Â Â CONSTRAINT pk PRIMARY KEY (my_pk))
 Â Â Â IMMUTABLE_ROWS=true,
 Â Â Â IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS,
 Â Â Â COLUMN_ENCODED_BYTES = 1;

Hbase Rows when Loading via PSQL

 x80x00x00x00x00x0009 Â Â Â Â 
column=M:x00x00x00x00, timestamp=1524109827690, value=x Â Â Â Â 
Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x0009 Â Â Â Â column=M:1, 
timestamp=1524109827690, 
value=xJohnDoex00x00x00x01x00x05x00x00x00x08x00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 Â 
column=M:x00x00x00x00, timestamp=1524109827690, value=x Â Â Â Â 
Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 Â column=M:1, 
timestamp=1524109827690, 
value=xMaryPoppinsx00x00x00x01x00x05x00x00x00x0Cx00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 

Hbase Rows when Loading via MapReduce using CsvBulkLoadTool 

 x80x00x00x00x00x0009 Â Â Â Â column=M:1, 
timestamp=1524110486638, 
value=xJohnDoex00x00x00x01x00x05x00x00x00x08x00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 
 x80x00x00x00x00x01x092 Â column=M:1, 
timestamp=1524110486638, 
value=xMaryPoppinsx00x00x00x01x00x05x00x00x00x0Cx00x00x00x03x02
 Â Â Â Â Â Â Â Â Â Â Â Â Â 


So, the bulk loaded tables have 4 cells for the two rows loaded via psql 
whereas a bulk load is missing two cells since it lacks the cells with col 
qualifierÂ :x00x00x00x00Â Is this behavior correct?Â Thanks 
much for any insight.Â 


How To "Remove" Dark Spots
Gundry MD
http://thirdpartyoffers.netzero.net/TGL3232/5ad818ce6211c18ce6b13st04vuc

hint to use a global index is not working - need to find out why

2018-04-19 Thread Taylor, Ronald (Ronald)

Hello Phoenix users,

I am a novice Phoenix user and this is my first post to this user list. I did 
some searching in the list archives, but could not find an answer to what I 
hope is a simple question: my global index is being ignored, even after I add a 
Hint, and I want to know why.

We are using Phoenix 4.7 in the Hortonworks distribution. Looks like 
Hortonworks has been backporting at least some phoenix updates into their 
version of phoenix 4.7, so I guess it is a custom distribution. See

 
https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.4/bk_release-notes/content/patch_phoenix.html

I have created a simple table of about 8 million rows, and about 15 columns, 
with several fields having global indexes. I created the main table 
(variantjoin_rtsalted24) and its indexes, and then used a bulk loader to 
populate them from a tab-delimited file. That appeared to work fine.

chrom_int is one field on which there is a global index, named vj2_chrom_int. 
And you can see the index being automatically being used below, where it is the 
only field being returned. Time required is 0.124 sec.

0: jdbc:phoenix:> SELECT VJ.chrom_int  FROM VARIANTJOIN_RTSALTED24 as VJ WHERE 
(chrom_int =18 ) limit 5;

++

| CHROM_INT  |

++

| 18 |

| 18 |

| 18 |

| 18 |

| 18 |

++

5 rows selected (0.124 seconds)

0: jdbc:phoenix:>

You can see that the vj2_chrom_int index is automatically being used, as I 
understand things  by the "RANGE SCAN" wording and "[0,1" in the explain plan:

0: jdbc:phoenix:> explain SELECT VJ.chrom_int  FROM VARIANTJOIN_RTSALTED24 as 
VJ WHERE (chrom_int =18 ) limit 5;

+--+

|   PLAN   |

+--+

| CLIENT 24-CHUNK SERIAL 24-WAY ROUND ROBIN RANGE SCAN OVER VJ2_CHROM_INT [0,1 |

| SERVER FILTER BY FIRST KEY ONLY  |

| SERVER 5 ROW LIMIT   |

| CLIENT 5 ROW LIMIT   |

+--+

4 rows selected (0.043 seconds)

0: jdbc:phoenix:>


I can use a Hint to tell Phoenix to NOT use this index, as seen below. And that 
increases the time needed to 1.97 sec, over an order of magnitude more time 
than the 0.124 sec required with index use.

0: jdbc:phoenix:> SELECT /*+ NO_INDEX */ VJ.chrom_int  FROM 
VARIANTJOIN_RTSALTED24 as VJ WHERE (chrom_int =18 ) limit 5;

++

| CHROM_INT  |

++

| 18 |

| 18 |

| 18 |

| 18 |

| 18 |

++

5 rows selected (1.977 seconds)

0: jdbc:phoenix:>

And here is the explain plan for that:


0: jdbc:phoenix:> explain SELECT /*+ NO_INDEX */ VJ.chrom_int  FROM 
VARIANTJOIN_RTSALTED24 as VJ WHERE (chrom_int =18 ) limit 5;

+--+

|PLAN  |

+--+

| CLIENT 72-CHUNK 14325202 ROWS 15099524157 BYTES PARALLEL 24-WAY ROUND ROBIN  |

| SERVER FILTER BY CHROM_INT = 18  |

| SERVER 5 ROW LIMIT   |

| CLIENT 5 ROW LIMIT   |

+--+

4 rows selected (0.009 seconds)

Now,  I want to add other fields for retrieval. For example, "genomic_range". 
The Phoenix documentation says in such a case I must add a Hint to force 
Phoenix to make use of the index (since it is a simple global index, not a 
covered index wherein genomic_range has been added.) So I tried that. See 
below. Alas, the response time is about the same as what I get with NO_INDEX.  
It appears that, even with the Hint, the index is not being used.

0: jdbc:phoenix:> SELECT/*+ INDEX(VJ jv2_chrom_int) */ VJ.chrom_int, 
genomic_range  FROM VARIANTJOIN_RTSALTED24 as VJ WHERE (chrom_int =18 ) limit 5;
++--+
| CHROM_INT  |GENOMIC_RANGE |
++--+
| 18 | [49546,49547)|
| 18 | [20003625,20003626)  |
| 18 | [19618749,19618752)  |
| 18 | [47561,47583)|
| 18 | [20024261,20024272)  |
++--+
5 rows selected (1.799 seconds)
0: jdbc:phoenix:>


And below is the explain plan for the query with the index failure. No 
indication of index use, that I can tell.

0: jdbc:phoenix:> explain SELECT/*+ INDEX(VJ jv2_chrom_int) */ VJ.chrom_int, 
genomic_range  FROM

答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

2018-04-19 Thread Lu Wei

## Version:
phoenix: 4.13.2-cdh5.11.2
hive: 1.1.0-cdh5.11.2

to reproduce:

-- create table

create table if not exists testarray(id bigint not null, events bigint array 
constraint pk primary key (id))


-- upsert data:

upsert into testarray values (1, array[1,2]);


-- query:

select id from testarray;   -- fine

select * from testarray;-- error


发件人: sergey.solda...@gmail.com  代表 Sergey Soldatov 

发送时间: 2018年4月19日 6:37:06
收件人: user@phoenix.apache.org
主题: Re: phoenix query server java.lang.ClassCastException for BIGINT ARRAY 
column

Could you please be more specific? Which version of phoenix are you using? Do 
you have a small script to reproduce? At first glance it looks like a PQS bug.

Thanks,
Sergey

On Thu, Apr 19, 2018 at 8:17 AM, Lu Wei 
> wrote:

Hi there,

I have a phoenix table containing an BIGINT ARRAY column. But when querying 
query server (through sqlline-thin.py), there is an exception:

java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long

BTW, when query through sqlline.py, everything works fine. And data in HBase 
table are of Long type, so why does the Integer to Long cast happen?



## Table schema:

create table if not exists gis_tracking3(tracking_object_id bigint not null, 
lat double, lon double, speed double, bearing double, time timestamp not null, 
events bigint array constraint pk primary key (tracking_object_id, time))


## when query events[1], it works fine:

0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events[1]+1 from 
gis_tracking3;
+--+
| (ARRAY_ELEM(EVENTS, 1) + 1)  |
+--+
| 11   |
| 2223 |
| null |
| null |
| 10001|
+--+



## when querying events, it throws exception:

0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events from 
gis_tracking3;
java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$LongAccessor.getLong(AbstractCursor.java:550)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.convertValue(AbstractCursor.java:1310)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getObject(AbstractCursor.java:1289)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getArray(AbstractCursor.java:1342)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getString(AbstractCursor.java:1354)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
  at sqlline.Rows$Row.(Rows.java:183)
  at sqlline.BufferedRows.(BufferedRows.java:38)
  at sqlline.SqlLine.print(SqlLine.java:1660)
  at sqlline.Commands.execute(Commands.java:833)
  at sqlline.Commands.sql(Commands.java:732)
  at sqlline.SqlLine.dispatch(SqlLine.java:813)
  at sqlline.SqlLine.begin(SqlLine.java:686)
  at sqlline.SqlLine.start(SqlLine.java:398)
  at sqlline.SqlLine.main(SqlLine.java:291)
  at 
org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)



I guess there is some issue in query sever, but can't figure out why.

Any suggestions?



Thanks,

Wei

答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

2018-04-19 Thread Lu Wei

by the way, all the queries are shot in sqlline-thin.py




发件人: Lu Wei
发送时间: 2018年4月19日 6:51:15
收件人: user@phoenix.apache.org
主题: 答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY 
column


## Version:
phoenix: 4.13.2-cdh5.11.2
hive: 1.1.0-cdh5.11.2

to reproduce:

-- create table

create table if not exists testarray(id bigint not null, events bigint array 
constraint pk primary key (id))


-- upsert data:

upsert into testarray values (1, array[1,2]);


-- query:

select id from testarray;   -- fine

select * from testarray;-- error


发件人: sergey.solda...@gmail.com  代表 Sergey Soldatov 

发送时间: 2018年4月19日 6:37:06
收件人: user@phoenix.apache.org
主题: Re: phoenix query server java.lang.ClassCastException for BIGINT ARRAY 
column

Could you please be more specific? Which version of phoenix are you using? Do 
you have a small script to reproduce? At first glance it looks like a PQS bug.

Thanks,
Sergey

On Thu, Apr 19, 2018 at 8:17 AM, Lu Wei 
> wrote:

Hi there,

I have a phoenix table containing an BIGINT ARRAY column. But when querying 
query server (through sqlline-thin.py), there is an exception:

java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long

BTW, when query through sqlline.py, everything works fine. And data in HBase 
table are of Long type, so why does the Integer to Long cast happen?



## Table schema:

create table if not exists gis_tracking3(tracking_object_id bigint not null, 
lat double, lon double, speed double, bearing double, time timestamp not null, 
events bigint array constraint pk primary key (tracking_object_id, time))


## when query events[1], it works fine:

0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events[1]+1 from 
gis_tracking3;
+--+
| (ARRAY_ELEM(EVENTS, 1) + 1)  |
+--+
| 11   |
| 2223 |
| null |
| null |
| 10001|
+--+



## when querying events, it throws exception:

0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events from 
gis_tracking3;
java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$LongAccessor.getLong(AbstractCursor.java:550)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.convertValue(AbstractCursor.java:1310)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getObject(AbstractCursor.java:1289)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getArray(AbstractCursor.java:1342)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getString(AbstractCursor.java:1354)
  at 
org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
  at sqlline.Rows$Row.(Rows.java:183)
  at sqlline.BufferedRows.(BufferedRows.java:38)
  at sqlline.SqlLine.print(SqlLine.java:1660)
  at sqlline.Commands.execute(Commands.java:833)
  at sqlline.Commands.sql(Commands.java:732)
  at sqlline.SqlLine.dispatch(SqlLine.java:813)
  at sqlline.SqlLine.begin(SqlLine.java:686)
  at sqlline.SqlLine.start(SqlLine.java:398)
  at sqlline.SqlLine.main(SqlLine.java:291)
  at 
org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)



I guess there is some issue in query sever, but can't figure out why.

Any suggestions?



Thanks,

Wei

Re: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

2018-04-19 Thread Sergey Soldatov

Could you please be more specific? Which version of phoenix are you using?
Do you have a small script to reproduce? At first glance it looks like a
PQS bug.

Thanks,
Sergey

On Thu, Apr 19, 2018 at 8:17 AM, Lu Wei  wrote:

> Hi there,
>
> I have a phoenix table containing an BIGINT ARRAY column. But when
> querying query server (through sqlline-thin.py), there is an exception:
>
> java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Long
>
> BTW, when query through sqlline.py, everything works fine. And data in
> HBase table are of Long type, so why does the Integer to Long cast happen?
>
>
> ## Table schema:
>
> create table if not exists gis_tracking3(tracking_object_id bigint not
> null, lat double, lon double, speed double, bearing double, time timestamp
> not null, events bigint array constraint pk primary key
> (tracking_object_id, time))
>
>
> ## when query events[1], it works fine:
>
> 0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events[1]+1 from
> gis_tracking3;
> +--+
> | (ARRAY_ELEM(EVENTS, 1) + 1)  |
> +--+
> | 11   |
> | 2223 |
> | null |
> | null |
> | 10001|
> +--+
>
>
> ## when querying events, it throws exception:
>
> 0: jdbc:phoenix:thin:url=http://10.10.13.87:8> select  events from
> gis_tracking3;
> java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Long
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.
> AbstractCursor$LongAccessor.getLong(AbstractCursor.java:550)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.
> AbstractCursor$ArrayAccessor.convertValue(AbstractCursor.java:1310)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.
> AbstractCursor$ArrayAccessor.getObject(AbstractCursor.java:1289)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.
> AbstractCursor$ArrayAccessor.getArray(AbstractCursor.java:1342)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.util.
> AbstractCursor$ArrayAccessor.getString(AbstractCursor.java:1354)
>   at org.apache.phoenix.shaded.org.apache.calcite.avatica.
> AvaticaResultSet.getString(AvaticaResultSet.java:257)
>   at sqlline.Rows$Row.(Rows.java:183)
>   at sqlline.BufferedRows.(BufferedRows.java:38)
>   at sqlline.SqlLine.print(SqlLine.java:1660)
>   at sqlline.Commands.execute(Commands.java:833)
>   at sqlline.Commands.sql(Commands.java:732)
>   at sqlline.SqlLine.dispatch(SqlLine.java:813)
>   at sqlline.SqlLine.begin(SqlLine.java:686)
>   at sqlline.SqlLine.start(SqlLine.java:398)
>   at sqlline.SqlLine.main(SqlLine.java:291)
>   at org.apache.phoenix.queryserver.client.SqllineWrapper.main(
> SqllineWrapper.java:93)
>
>
> I guess there is some issue in query sever, but can't figure out why.
>
> Any suggestions?
>
>
>
> Thanks,
>
> Wei
>

Re: hbase cell storage different bewteen bulk load and direct api

2018-04-19 Thread Sergey Soldatov

Hi Lew,
no. 1st one looks line incorrect. You may file a bug on that ( I believe
that the second case is correct, but you may also check with uploading data
using regular upserts). Also, you may check whether the master branch has
this issue.

Thanks,
Sergey

On Thu, Apr 19, 2018 at 10:19 AM, Lew Jackman  wrote:

> Under Phoenix 4.11 we are seeing some storage discrepancies in hbase
> between a load via psql and a bulk load.
>
> To illustrate in a simple case we have modified the example table from the
> load reference https://phoenix.apache.org/bulk_dataload.html
>
> CREATE TABLE example (
>my_pk bigint not null,
>m.first_name varchar(50),
>m.last_name varchar(50)
>CONSTRAINT pk PRIMARY KEY (my_pk))
>IMMUTABLE_ROWS=true,
>IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS,
>COLUMN_ENCODED_BYTES = 1;
>
> Hbase Rows when Loading via PSQL
>
> \\x80\\x00\\x00\\x00\\x00\\x0009 column=M:\\x00\\x00\\x00\\x00,
> timestamp=1524109827690, value=x
> \\x80\\x00\\x00\\x00\\x00\\x0009 column=M:1, timestamp=1524109827690,
> value=xJohnDoe\\x00\\x00\\x00\\x01\\x00\\x05\\x00\\x00\\x00\\x08\\x00\\x00\\x00\\x03\\x02
>
> \\x80\\x00\\x00\\x00\\x00\\x01\\x092  column=M:\\x00\\x00\\x00\\x00,
> timestamp=1524109827690, value=x
> \\x80\\x00\\x00\\x00\\x00\\x01\\x092  column=M:1,
> timestamp=1524109827690, value=xMaryPoppins\\x00\\x00\\
> x00\\x01\\x00\\x05\\x00\\x00\\x00\\x0C\\x00\\x00\\x00\\x03\\x02
>
>
> Hbase Rows when Loading via MapReduce using CsvBulkLoadTool
>
> \\x80\\x00\\x00\\x00\\x00\\x0009 column=M:1, timestamp=1524110486638,
> value=xJohnDoe\\x00\\x00\\x00\\x01\\x00\\x05\\x00\\x00\\x00\\x08\\x00\\x00\\x00\\x03\\x02
>
> \\x80\\x00\\x00\\x00\\x00\\x01\\x092  column=M:1,
> timestamp=1524110486638, value=xMaryPoppins\\x00\\x00\\
> x00\\x01\\x00\\x05\\x00\\x00\\x00\\x0C\\x00\\x00\\x00\\x03\\x02
>
>
>
> So, the bulk loaded tables have 4 cells for the two rows loaded via psql
> whereas a bulk load is missing two cells since it lacks the cells with col
> qualifier :\\x00\\x00\\x00\\x00
>
> Is this behavior correct?
>
> Thanks much for any insight.
>
>
>
> 
> *How To "Remove" Dark Spots*
> Gundry MD
> 
> http://thirdpartyoffers.netzero.net/TGL3232/5ad818ce6211c18ce6b13st04vuc
> [image: SponsoredBy Content.Ad]

Re: 答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

Re: hbase cell storage different bewteen bulk load and direct api

Re: hint to use a global index is not working - need to find out why

Re: hbase cell storage different bewteen bulk load and direct api

Re: pheonix client

Re: hbase cell storage different bewteen bulk load and direct api

Re: hbase cell storage different bewteen bulk load and direct api

hint to use a global index is not working - need to find out why

答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

答复: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

Re: phoenix query server java.lang.ClassCastException for BIGINT ARRAY column

Re: hbase cell storage different bewteen bulk load and direct api

12 matches

Site Navigation

Mail list logo

Footer information