Re: Squirrel SQL Client doesnt work with phoenix-4.9.0-HBase-1.2

[Josh Elser]

I'm confused as to how those two points are related.

Are you saying that without setting the default serialization, you got an
error about being unable to load a commons-http SSL class?

On Mar 5, 2017 17:18, "Cheyenne Forbes" 
wrote:

turns out that the only way to connect to phoenix from the latest version
of Squirrel is with "jdbc:phoenix:thin:url=http://172.17.0.2:8765
;serialization=PROTOBUF" instead of just "jdbc:phoenix:thin:url=http://
172.17.0.2:8765;" If I'm using the default phoenix 4.9.0 settings

Re: Retiring empty regions

[rafa]

Hi Nick,

We are facing the same issue. Increasingly number of empty regions derived
from TTL and Timestamp in row key.

Did you finally published that scripts? Are they available for public usage?

Thank you very much in advance for your work and help,
Best Regards,
rafa



On Thu, Apr 21, 2016 at 1:48 AM, Andrew Purtell  wrote:

> >  the shell and find the empty ones, another to merge a given region
> into a neighbor. We've run them without incident, looks like it all works
> fine. One thing we did notice is that the AM leaves the old "retired"
> regions around in its counts -- the master status page shows a large number
> of "Other Regions". This was alarming at first,
>
> Good to know. I had seen this recently and had a mental note to circle
> around and confirm it's just a temporary artifact.
>
> On Wed, Apr 20, 2016 at 3:16 PM, Nick Dimiduk  wrote:
>
>> Circling back here and adding user@phoenix.
>>
>> I put together one script to dump region info from the shell and find the
>> empty ones, another to merge a given region into a neighbor. We've run them
>> without incident, looks like it all works fine. One thing we did notice is
>> that the AM leaves the old "retired" regions around in its counts -- the
>> master status page shows a large number of "Other Regions". This was
>> alarming at first, but we verified it's just an artifact in the AM and in
>> fact these regions are not on HDFS or in meta. Bouncing master resolved it.
>>
>> No one has volunteered any alternative schema designs, so as best we
>> know, this will happen to anyone who has timestamp in their rowkey (ie,
>> anyone using Phoenix's "Row timestamp" feature [0]) and is also using the
>> TTL feature. Are folks interested in adding these scripts to our
>> distribution and our book?
>>
>> -n
>>
>> [0]: https://phoenix.apache.org/rowtimestamp.html
>>
>> On Mon, Apr 4, 2016 at 8:34 AM, Nick Dimiduk  wrote:
>>
>>> > Crazy idea, but you might be able to take stripped down version of
>>> region
>>> > normalizer code and make a Tool to run? Requesting split or merge is
>>> done
>>> > through the client API, and the only weighing information you need is
>>> > whether region empty or not, that you could find out too?
>>>
>>> Yeah, that's the direction I'm headed.
>>>
>>> > A bit off topic, but I think unfortunately region normalizer now
>>> ignores
>>> > empty regions to avoid undoing pre-split on the table.
>>>
>>> Unfortunate indeed. Maybe we should be keeping around the initial splits
>>> list as a metadata attribute on the table?
>>>
>>> > With a right row-key design you will never have empty regions due to
>>> TTL.
>>>
>>> I'd love to hear your thoughts on this design, Vlad. Maybe you'd like to
>>> write up a post for the blog? Meanwhile, I'm sure of a couple of us on here
>>> on the list would appreciate your Cliff's Notes version. I can take this
>>> into account for my v2 schema design.
>>>
>>> > So Nick, merge on 1.1 is not recommended??? Was working very well on
>>> > previous versions. Is ProcV2 really impact it that bad??
>>>
>>> How to answer here carefully... I have no reason to believe merge is not
>>> working on 1.1. I've been on the wrong end of enough "regions stuck in
>>> transition" support tickets that I'm not keen to put undue stress on my
>>> master. ProcV2 insures against many scenarios that cause master trauma,
>>> hence my interest in the implementation details and my preference for
>>> cluster administration tasks that use it as their source of authority.
>>>
>>> Thanks for the thoughts folks.
>>> -n
>>>
>>> On Fri, Apr 1, 2016 at 10:52 AM, Jean-Marc Spaggiari <
>>> jean-m...@spaggiari.org> wrote:
>>>
 ;) That was not the question ;)

 So Nick, merge on 1.1 is not recommended??? Was working very well on
 previous versions. Is ProcV2 really impact it that bad??

 JMS

 2016-04-01 13:49 GMT-04:00 Vladimir Rodionov :

 > >> This is something
 > >> which makes it far less useful for time-series databases with
 short TTL
 > on
 > >> the tables.
 >
 > With a right row-key design you will never have empty regions due to
 TTL.
 >
 > -Vlad
 >
 > On Thu, Mar 31, 2016 at 10:31 PM, Mikhail Antonov <
 olorinb...@gmail.com>
 > wrote:
 >
 > > Crazy idea, but you might be able to take stripped down version of
 region
 > > normalizer code and make a Tool to run? Requesting split or merge
 is done
 > > through the client API, and the only weighing information you need
 is
 > > whether region empty or not, that you could find out too?
 > >
 > >
 > > "Short of upgrading to 1.2 for the region normalizer,"
 > >
 > > A bit off topic, but I think unfortunately region normalizer now
 ignores
 > > empty regions to avoid undoing pre-split on the table. This is
 something
 > > which makes it far less 

Re: Phoenix ignoring index and index hint with some queries over mapped hbase tables.

[Marcin Januszkiewicz]

On Fri, Mar 3, 2017 at 7:52 PM, James Taylor  wrote:
> Hi Marcin,
> There's a few things going on here:
> 1) An index created on a view over HBase data won't be maintained by
> Phoenix. You'd need to maintain it yourself through some other external
> means. If you create a table that maps to your HBase data, then it will be
> maintained.

UPSERTING data into the view will also work, right?

> 2) An index would only be used if you match against a constant on the
> right-hand side (while you're matching against the "number" column). For
> example, the following query would use the index and limit the scan to only
> rows in which "number" starts with 'Queen':
>
> 0: jdbc:phoenix:> explain select * from "traces" where
> regexp_substr("number", 'Q.*') = 'Queen';
> +--+
> |PLAN
> |
> +--+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER traces
> [1,'Queen'] |
> | SERVER FILTER BY FIRST KEY ONLY AND REGEXP_SUBSTR("number", 'Q.*', 1)
> =  |
> +--+
>
> Note that with local indexes, interpreting when the index is used is a bit
> subtle, but you'll see a [1, ...] after the RANGE SCAN as an indication. The
> index may be used if it's a full table scan (since the data contained in the
> index table may be smaller than that in the data table), but that won't
> buying you very much.
> 3) The index would only be used if your REGEXP_SUBSTR has a constant string
> before any wildcard matches in the second argument. You could also
> potentially use a function index [1], but it'd only use the index if the
> REGEXP_SUBSTR makes the same exact call (i.e. same pattern argument) as was
> used when the functional index was created.
>
> HTH.  Thanks,
>
> James
>
> [1] https://phoenix.apache.org/secondary_indexing.html#Functional_Indexes


So, if I understand correctly, my best bet would be to use some
heuristics to hopefully extract a coarse prefix range from the regex?
That way I can do a preliminary range scan on the index and refine the
filtering on the returned data. I assume I could implement this as a
UDF with an appropriate newKeyPart method?



>
> On Fri, Mar 3, 2017 at 12:59 AM, Marcin Januszkiewicz
>  wrote:
>>
>> Hi,
>>
>> I have a table in hbase and created a view of it in phoenix, along
>> with a local index:
>>
>> create view "traces" (rowkey VARCHAR PRIMARY KEY, "cf"."time" VARCHAR,
>> "cf"."number" VARCHAR, "cf"."class" VARCHAR);
>> create local index idxf on "dmstab_vehicle_traces" ("cf"."number",
>> "cf"."class", rowkey);
>>
>> I need to filter rows based on a regex condition on the "number"
>> column, so I use queries like these:
>>
>> explain select * from "traces" where regexp_substr("number", 'Q.*') =
>> "number";
>>
>> CLIENT 2-CHUNK PARALLEL 2-WAY ROUND ROBIN RANGE SCAN OVER traces [2]
>> SERVER FILTER BY FIRST KEY ONLY AND REGEXP_SUBSTR("number", 'Q.*',
>> 1) = "number"
>>
>>
>> It's a little ugly and less efficient than using LIKE, but the
>> performance is still relatively acceptable thanks to the index.
>>
>> However, if I want to range of rowkeys to include, Phoenix stops using
>> the index, which slows down the query significantly:
>>
>> explain select * from "traces" where regexp_substr("number", 'Q.*') =
>> "number" and rowkey < 'BY';
>>
>> CLIENT 4-CHUNK 687413 ROWS 629145681 BYTES PARALLEL 1-WAY ROUND ROBIN
>> RANGE SCAN OVER traces [*] - ['BY']
>> SERVER FILTER BY REGEXP_SUBSTR(cf."number", 'Q.*', 1) = cf."number"
>>
>> Using an index hint doesn't change anything. Is there a way to make
>> this work, and is this a bug?
>
>

Re: Squirrel SQL Client doesnt work with phoenix-4.9.0-HBase-1.2

[mferlay]

Maybe it will not solve your issue but you can use the last image of
smizy/docker-apache-phoenix with docker. it's an phoenix4.9 - hbase1.2 with
an integrated sql provider



--
View this message in context: 
http://apache-phoenix-user-list.1124778.n5.nabble.com/Squirrel-SQL-Client-doesnt-work-with-phoenix-4-9-0-HBase-1-2-tp3225p3241.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.