Re: Incorrect number of rows affected from DELETE query

[James Taylor]

Phoenix returns the number of delete markers that were placed if it’s a
point delete, not the actual number of rows deleted. Otherwise you’d need
to do a read before the delete (which would be costly). It’s possible that
this could be made configurable - please file a JIRA. You could work around
this by adding a nullable column that’s never populated to your primary key
or by adding another ANDed expression to your WHERE clause for a non pk
column which you know is always true.

On Thu, Feb 22, 2018 at 4:05 PM Sergey Soldatov 
wrote:

> Hi Jins,
> If you provide steps to reproduce it would be much easier to understand
> where the problem is. If nothing was deleted the report should be 'No
> rows affected'.
>
> Thanks,
> Sergey
>
> On Mon, Feb 19, 2018 at 4:30 PM, Jins George 
> wrote:
>
>> Hi,
>>
>> I am facing an issue in which the number of rows affected by a DELETE
>> query returns an incorrect value.   The record I am trying to delete does
>> not exists in the table, as evident from the first query but on deletion,
>> it reports 1 row is affected.  Is this a known issue?
>>
>> I have tried this in Phoenix 4.7 & Phoenix 4.13 and both behaves the same
>> way.
>>
>>
>> 0: jdbc:phoenix:localhost> select accountId, subid  from test.mytable
>> where accountid = '1' and subid = '1';
>> +++
>> | ACCOUNTID  | SUBID  |
>> +++
>> +++
>> *No rows selected (0.017 seconds)*
>> 0: jdbc:phoenix:localhost> delete from test.mytable where accountid = '1'
>> and subid = '1';
>> *1 row affected (0.005 seconds)*
>> 0: jdbc:phoenix:localhost>
>>
>>
>> Thanks,
>> Jins George
>>
>
>

Phoenix admin?

[Reid Chan]

Hi team,

I created a table through HBase api, and then created a view for it on Phoenix.
And for some reasons, i dropped the view, but coprocessors are still attached 
on this table.

>From hbase webui:
'recommend:vulgar_feed', {TABLE_ATTRIBUTES => {coprocessor$1 => 
'|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|', coprocessor$2 
=> 
'|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|', 
coprocessor$3 => 
'|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|', 
coprocessor$4 => 
'|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|'}, {NAME 
=> 'b'}

>From regionserver log:
2018-02-16 17:42:50,022 WARN 
org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver: Unable to 
collect stats for recommend:vulgar_feed
java.io.IOException: Unable to initialize the guide post depth
at 
org.apache.phoenix.schema.stats.DefaultStatisticsCollector.init(DefaultStatisticsCollector.java:369)
at 
org.apache.phoenix.schema.stats.DefaultStatisticsCollector.createCompactionScanner(DefaultStatisticsCollector.java:359)
at 
org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver$2.run(UngroupedAggregateRegionObserver.java:923)
at 
org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver$2.run(UngroupedAggregateRegionObserver.java:912)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at 
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1628)
at 
org.apache.hadoop.security.SecurityUtil.doAsUser(SecurityUtil.java:445)
at 
org.apache.hadoop.security.SecurityUtil.doAsLoginUser(SecurityUtil.java:426)
at sun.reflect.GeneratedMethodAccessor69.invoke(Unknown Source)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hbase.util.Methods.call(Methods.java:39)
at org.apache.hadoop.hbase.security.User.runAsLoginUser(User.java:210)
at 
org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver.preCompact(UngroupedAggregateRegionObserver.java:912)
at 
org.apache.hadoop.hbase.coprocessor.BaseRegionObserver.preCompact(BaseRegionObserver.java:195)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$9.call(RegionCoprocessorHost.java:595)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$RegionOperation.call(RegionCoprocessorHost.java:1673)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperation(RegionCoprocessorHost.java:1749)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperationWithResult(RegionCoprocessorHost.java:1722)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.preCompact(RegionCoprocessorHost.java:590)
at 
org.apache.hadoop.hbase.regionserver.compactions.Compactor.postCreateCoprocScanner(Compactor.java:253)
at 
org.apache.hadoop.hbase.regionserver.compactions.DefaultCompactor.compact(DefaultCompactor.java:94)
at 
org.apache.hadoop.hbase.regionserver.DefaultStoreEngine$DefaultCompactionContext.compact(DefaultStoreEngine.java:119)
at org.apache.hadoop.hbase.regionserver.HStore.compact(HStore.java:1223)
at 
org.apache.hadoop.hbase.regionserver.HRegion.compact(HRegion.java:1856)
at 
org.apache.hadoop.hbase.regionserver.CompactSplitThread$CompactionRunner.doCompaction(CompactSplitThread.java:526)
at 
org.apache.hadoop.hbase.regionserver.CompactSplitThread$CompactionRunner.run(CompactSplitThread.java:562)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 
(42M03): Table undefined. tableName=recommend.vulgar_feed
at 
org.apache.phoenix.schema.PMetaDataImpl.getTableRef(PMetaDataImpl.java:71)
at 
org.apache.phoenix.jdbc.PhoenixConnection.getTable(PhoenixConnection.java:575)
at 
org.apache.phoenix.util.PhoenixRuntime.getTable(PhoenixRuntime.java:444)
at 
org.apache.phoenix.schema.stats.DefaultStatisticsCollector.initGuidepostDepth(DefaultStatisticsCollector.java:160)
at 
org.apache.phoenix.schema.stats.DefaultStatisticsCollector.init(DefaultStatisticsCollector.java:367)


My question is, is it possible to drop those coprocessors through like phoenix 
admin, or some tools i missed? Although no harm done, i just think this drop 
not clean enough...

Re: Incorrect number of rows affected from DELETE query

[Sergey Soldatov]

Hi Jins,
If you provide steps to reproduce it would be much easier to understand
where the problem is. If nothing was deleted the report should be 'No
rows affected'.

Thanks,
Sergey

On Mon, Feb 19, 2018 at 4:30 PM, Jins George  wrote:

> Hi,
>
> I am facing an issue in which the number of rows affected by a DELETE
> query returns an incorrect value.   The record I am trying to delete does
> not exists in the table, as evident from the first query but on deletion,
> it reports 1 row is affected.  Is this a known issue?
>
> I have tried this in Phoenix 4.7 & Phoenix 4.13 and both behaves the same
> way.
>
>
> 0: jdbc:phoenix:localhost> select accountId, subid  from test.mytable
> where accountid = '1' and subid = '1';
> +++
> | ACCOUNTID  | SUBID  |
> +++
> +++
> *No rows selected (0.017 seconds)*
> 0: jdbc:phoenix:localhost> delete from test.mytable where accountid = '1'
> and subid = '1';
> *1 row affected (0.005 seconds)*
> 0: jdbc:phoenix:localhost>
>
>
> Thanks,
> Jins George
>

Error Occurs while selecting a specific set of columns

[akshay sivan]

Hi,

https://issues.apache.org/jira/browse/PHOENIX-4609?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Is there any work around/fix for this issue?

Thanks and regards,
Akshay 

Re: Runtime DDL supported?

[James Taylor]

Another option would be to use dynamic columns[1] when querying across
views. You’d have to disable column encoding [2] in this case.

[1] http://phoenix.apache.org/dynamic_columns.html
[2] http://phoenix.apache.org/columnencoding.html

On Wed, Feb 21, 2018 at 11:57 PM Miles Spielberg  wrote:

> I believe each query in a UNION needs to have the same result tuple
> format, which would work in this toy example, but in the general case each
> view would have a different schema. We could make the result tuples conform
> with each other by selecting NULL literals for every column except those in
> a view. It would get quite verbose though. Assuming f1,f2,f3 all have
> incompatible types, were you suggesting something like this?
>
> Select f1, null, null from v1 where PK=?
> Union all
> Select null, f2, null from v2 where PK=?
> Union all
> Select null, null, f3 from v3 where PK=?
>
> We might just run separate parallel queries against each view and merge
> the results client side. I would guess this should perform well since the
> block cache can be leveraged for queries after the first.
>
> We could also use the HBase API to run a point row get. We'd have to
> reimplement decoding for Phoenix's column values, which is not ideal but
> quite doable.
>
> Sent from my iPhone
>
> On Feb 21, 2018, at 9:09 PM, James Taylor  wrote:
>
> Have you tried a UNION ALL query on (f1, f2, f3) instead? It seems you’re
> on a good track with multiple views over a single (or handful) of physical
> table(s).
>
> On Wed, Feb 21, 2018 at 6:45 PM Miles Spielberg  wrote:
>
>> I've done some experimentation with views, with a schema resembling this:
>>
>> create table t1(
>>>
>>> pk bigint not null primary key
>>>
>>> );
>>>
>>>
 create view v1(
>>>
>>> f1 varchar
>>>
>>> ) AS SELECT * FROM t1;
>>>
>>> create INDEX v1_f1 ON v1(f1);
>>>
>>>
 create view v2(
>>>
>>> f2 varchar
>>>
>>> ) AS SELECT * FROM t1;
>>>
>>> create INDEX v2_f2 ON v2(f2);
>>>
>>>
 create view v3(
>>>
>>> f3 varchar
>>>
>>> ) AS SELECT * FROM t1;
>>>
>>> create INDEX v3_f3 ON v3(f3);
>>>
>>>
>> Most of the time we'll be accessing data via the indexed views, but we'd
>> also like to be able to query all columns (f1, f2, f3) for a given pk. At
>> the HBase level, this should be doable as a point get on t1. The SQL-y
>> way to express this would probably be with JOINs, but the EXPLAIN plan is
>> not encouraging.
>>
>> > explain SELECT * from t1 left join v1 on v1.pk=t1.pk left join v2 on
>>> v2.pk=t1.pk left  join v3 on v3.pk=t1.pk where t1.pk=12345;
>>> | CLIENT 1-CHUNK 1 ROWS 281 BYTES PARALLEL 1-WAY ROUND ROBIN POINT
>>> LOOKUP ON 1 KEY OVER T1
>>> | PARALLEL LEFT-JOIN TABLE 0
>>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T1
>>> | PARALLEL LEFT-JOIN TABLE 1
>>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T1
>>> | PARALLEL LEFT-JOIN TABLE 2
>>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T1
>>>
>>
>> This is pushing me back towards a design of having a single table, except
>> for the issue of proliferating physical HBase tables for the indexes. Would
>> you advise having a single table + a single view on it containing all
>> columns, to coerce Phoenix to consolidate the indexes into a single
>> physical table? Are there other alternatives we should be considering?
>>
>> Miles Spielberg
>> Staff Software Engineer
>>
>>
>> O. 650.485.1102
>> 900 Jefferson Ave
>> 
>> Redwood City
>> ,
>> CA 94063
>> 
>>
>> On Fri, Feb 16, 2018 at 3:27 PM, James Taylor 
>> wrote:
>>
>>> All indexes on views are stored in a single physical table, so you'll be
>>> ok in that regard.
>>>
>>> If you could file bugs for any local index issues, we'd really
>>> appreciate it. We've been steadily improving local indexes
>>> (see PHOENIX-3941 for some recent perf improvements - applicable for
>>> multi-tenant tables in particular - these will appear in our 4.14 release).
>>> Handling non covered columns is pretty isolated, so we should be able to
>>> fix bugs you find. Plus, there's a workaround - you can cover your indexes
>>> until any issues are fixed.
>>>
>>> Global, mutable indexes have had many improvements over the last several
>>> releases too, but there's more operational overhead if/when a data table
>>> gets out of sync with it's index table (plus some amount of configurable
>>> eventual consistency or index disablement). With local indexes (and HBase
>>> 1.3), this isn't possible.
>>>
>>> Thanks,
>>> James
>>>
>>> On Fri, Feb 16, 2018 at 3:10 PM, Miles Spielberg  wrote:
>>>
 Hi James,

 Thanks for the tips around reducing 

Spark Phoenix index usage

[Davide Gazzè]

Good mooning at all,
thank you for your wonderful work. Apache Phoenix really saves me in the
past. I have one question, If I use Spark for connection to Phoenix, I have
2 ways:

   1. JDBC
   2. Apache Spark Plugin 

I would like to use spark for reading Phoenix Table where I add different
indexes. Which way is better? the second?
Thank you,

   Davide