Re: out of memory - unable to create new native thread

[Ankit Singhal]

Hi Ralph,

Try increasing the ulimit for number of open files(ulimit -n) and
processes(ulimit -u) for below users:-
hbase
hdfs


Regards,
Ankit Singhal


On Tue, Jul 7, 2015 at 4:13 AM, Perko, Ralph J  wrote:

>  Hi,
>
>  I am using a pig script to regularly load data into hbase/phoenix.
> Recently, "OutOfMemory – unable to create new native thread” errors have
> cropped up in the pig generated MR job – specifically in the Mapper task.
> I have not seen this before and it only occurs on this one data load while
> other similar scripts complete successfully.  We also recently upgraded to
> Phoenix 4.4.  My understanding is this is less about memory and more about
> resource availability from the OS.
>
>  The attached PDF contains some of the relevant log entries from the MR
> job
>
>  Any thoughts on what could be causing this?
>
>  Thanks,
>  Ralph
>
>
>  Phoenix 4.4.0
>  HBase 0.98.4
>
>  Pig script:
>
>  register $phoenix_jar;
> register $project_jar;
> register $piggybank_jar;
>
>  SET job.name 'Load $data into $table_name'
>
>  Z = load '$data' USING
> org.apache.pig.piggybank.storage.CSVExcelStorage() as (
> f:chararray,
> r:int,
> ...
> );
>
>  X = FILTER Z BY f is not null AND r is not null and fst is not null;
>
>  D = foreach X generate
> fst,
> gov.pnnl.pig.Format(f),
> r,
> ...
> ;
>
>  STORE D into 'hbase://$table_name/FST,F,R,...' using
> org.apache.phoenix.pig.PhoenixHBaseStorage('$zookeeper','-batchSize 500');
>
>

Re: taking a backup of a Phoenix database

[Ankit Singhal]

Yes snapshot will work as mentioned by Yuhao , as we have done this during
data center migration.

On Mon, Aug 10, 2015 at 9:19 PM, Yuhao Bi  wrote:

> Phoenix is based on HBase, since we can take a snapshot of HBase table,
> may be we can archive by this method?
>
> Taking snapshot of  SYSTEM.CATALOG, SYSTEM.SEQUENCE.
> And taking snapshot of user table.
>
> Hope someone could share some experience here.
>
>
> Thanks.
>
> 2015-08-10 20:29 GMT+08:00 James Heather :
>
>> Is there a nice way of taking a backup of a Phoenix database?
>>
>> Either just the schema, or schema plus data?
>>
>> James
>>
>
>

Re: Backup and Recovery for disaster recovery

[Ankit Singhal]

+1 for taking snapshots and exporting them on DR cluster if there is no
requirement of DR cluster to stay up-to-date in realtime.
I am not sure if there is any Incremental snaphost feature out yet but
doing snapshot on periodic basis is also not that heavy.

On Thu, Dec 24, 2015 at 3:44 PM, Sandeep Nemuri 
wrote:

> You can take incremental Hbase Snapshots for required tables and store it
> in the DR cluster.
> Restoring doesn't take much time in this case.
>
> Thanks
> Sandeep Nemuri
>
> ᐧ
>
> On Thu, Dec 24, 2015 at 11:49 AM, Vasudevan, Ramkrishna S <
> ramkrishna.s.vasude...@intel.com> wrote:
>
>> Am not very sure if Phoenix directly has any replication support now. But
>> in your case as you are bulk loading the tables you are not able to
>> replicate but that problems is addressed in HBase
>> As part of
>> https://issues.apache.org/jira/browse/HBASE-13153
>> Where bulk loaded files can get replicated directly to the remote cluster
>> like how WAL edits gets replicated.
>>
>> Regards
>> Ram
>>
>> -Original Message-
>> From: Krishnasamy Rajan [mailto:yume.kris...@gmail.com]
>> Sent: Tuesday, December 22, 2015 8:04 AM
>> To: user@phoenix.apache.org
>> Subject: Backup and Recovery for disaster recovery
>>
>> Hi,
>>
>> We’re using HBase under phoenix. Need to setup DR site and ongoing
>> replication.
>> Phoenix tables are salted tables. In this scenario what is the best
>> method to copy data to remote cluster?
>> People give different opinions.  Replication will not work for us as
>> we’re using bulk loading.
>>
>> Can you advise what are our options to copy data to remote cluster and
>> keeping it up to date.
>> Thanks for your inputs.
>>
>> -Regards
>> Krishna
>>
>
>
>
> --
> *  Regards*
> *  Sandeep Nemuri*
>

Re: Phoenix Query exception on few tables

[Ankit Singhal]

can you check HBase master page and see if some regions are in transition
or failing in opening/closing?
If yes, then restart your master or try to assign region with * assign
‘REGION_NAME’.*

And run *hbck *to detect any consistency and fix them.
http://hbase.apache.org/book.html#hbck.in.depth

you said that scan on HBase is succeeding , are you running it with *limit*
if yes , then it's not touching all the regions and giving result from the
opened regions only but with phoenix if you are running query like count(*)
which is touching all regions ,will not complete until all region
responded. so you need to fix the region assignments first and check hbase
master page to be clean before running queries.

Regards,
Ankit Singhal

On Fri, Jan 29, 2016 at 8:28 PM,  wrote:

> Can someone please help? It’s very sporadic and not sure where to start
> looking into this issue. Many thanks.
>
>
>
> *From:* Ramanathan, Kannan: IT (NYK)
> *Sent:* Thursday, January 28, 2016 14:04
> *To:* user@phoenix.apache.org
> *Subject:* Phoenix Query exception on few tables
>
>
>
> We have started seeing PhoenixIOException exception from select query on
> few of our tables. The same query worked before.
>
>
>
> The query is simple select query and the exception is below:
>
>
>
> org.apache.phoenix.exception.PhoenixIOException:
> org.apache.phoenix.exception.PhoenixIOException: The system cannot find the
> path specified
>
>
>
> If I run the scan command from HBase shell on the same table, it works
> fine.
>
>
>
> It appears that Phoenix metadata for these tables might have been
> corrupted. What could be the problem and how do we fix it?
>
>
>
> Thanks for your help.
>
>
>
> Regards
>
> Kannan.
>
> ___
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> ___
>
> ___
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> ___
>

Re: strange behavior with DATE columns

[Ankit Singhal]

As Afshin also said, You need to adjust your timezone with
phoenix.query.dateFormatTimeZone
https://phoenix.apache.org/tuning.html


phoenix.query.dateFormatTimeZone
IST


for eg:-

*upsert like this:*-

jdbc:phoenix:localhost> UPSERT INTO DESTINATION_METRICS_TABLE VALUES
(to_date('2015-09-12 22:02:11'), 'timezone unset', 1);

*With property set(which is in IST):-*

0: jdbc:phoenix:localhost> select * from DESTINATION_METRICS_TABLE;
+-+-+--+
|CREATED_DATE |METRIC_ID|   METRIC_VALUE
|
+-+-+--+
| 2015-09-12  | timezone set| 1
|
+-+-+—+

*With property unset(date passed in upsert will be considered to be of
default timezone i.e GMT and convert it to local timezone IST+5:30 while
storing in hbase):-*

0: jdbc:phoenix:localhost> select * from DESTINATION_METRICS_TABLE;
+-+-+--+
|CREATED_DATE |METRIC_ID|   METRIC_VALUE
|
+-+-+--+
| 2015-09-13  | timezone unset  | 1
|
+-+-+--+


On Fri, Jan 8, 2016 at 8:15 PM, Afshin Moazami 
wrote:

> Do you consider adjusting timezones when using JDBC?
>
> On Jan 8, 2016, at 3:01 AM, Thomas Decaux  wrote:
>
> What about Phoenix shell? I don't see this problem with Datagrip or
> Phoenix shell.
>
> 2016-01-07 20:53 GMT+01:00 :
>
>> Hello,
>>
>>
>>
>> I am having a DATE column in Phoenix DB. Through SQuirreL SQL client or
>> JDBC, when I do an upsert date as string, the resultant date is always one
>> day less. For ex all the below statements (with any date format in TO_DATE
>> function) yield the same result (i.e., instead of 15th it’s 14th of July):
>>
>>
>>
>> COLUMN TYPE
>>
>> === 
>>
>> COL1  VARCHAR
>>
>> COL3  DATE
>>
>>
>>
>> Queries:
>>
>> ==
>>
>> UPSERT INTO TEST VALUES ('5', TO_DATE('2016-07-15', '-MM-dd'))
>>
>> UPSERT INTO TEST VALUES ('5', '07/15/2016')
>>
>>
>>
>> SELECT * FROM TEST
>>
>>
>>
>> Result:
>>
>> ==
>>
>> 5  2016-07-14
>>
>>
>>
>>
>>
>> If I use JDBC PreparedStatement.setDate(…), it works as expected.
>>
>>
>>
>> Any ideas?
>>
>>
>>
>> Thanks
>>
>> Kannan.
>>
>>
>>
>> ___
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product or
>> service nor an official confirmation of any transaction. It is directed at
>> persons who are professionals and is not intended for retail customer use.
>> Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market participants; and
>> in respect of Barclays Research, including disclosures relating to specific
>> issuers, please see http://publicresearch.barclays.com.
>>
>> ___
>>
>
>
>

Re: Custom udf

[Ankit Singhal]

You can enable remote debugging on regionserver by appending "-Xdebug
-Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=8071" to
 HBASE_REGIONSERVER_OPTS and debug through eclipse.

On Tue, Jan 12, 2016 at 7:08 PM, Gaurav Agarwal 
wrote:

> Hello
> How to debug custom udf In phoenix.I wrote my replace for not null value.
> I have to debug what's the value coming .I am unable to find any steps
> please let us know
>

Re: Error : starting spark-shell with phoenix client jar

[Ankit Singhal]

Hi Divya,

It is fixed in 4.7 , please find a jira for the same.
https://issues.apache.org/jira/browse/PHOENIX-2608

Regards,
Ankit Singhal

On Thu, Feb 18, 2016 at 2:03 PM, Divya Gehlot 
wrote:

> Hi,
> I am getting following error while starting spark shell with phoenix
> clients
> spark-shell  --jars
> /usr/hdp/current/phoenix-client/phoenix-4.4.0.2.3.4.0-3485-client.jar
> --driver-class-path
> /usr/hdp/current/phoenix-client/phoenix-4.4.0.2.3.4.0-3485-client.jar
> --master yarn-client
>
> StackTrace :
>
>>  INFO TimelineClientImpl: Timeline service address:
>> http://ip-xxx-xx-xx-xxx.ap-southeast-1.compute.internal:8188/ws/v1/timeline/
>> java.lang.NoSuchMethodError:
>> org.codehaus.jackson.map.ObjectMapper.setSerializationInclusion(Lorg/codehaus/jackson/map/annotate/JsonSerialize$Inclusion;)Lorg/codehaus/jackson/map/ObjectMapper;
>> at
>> org.apache.hadoop.yarn.webapp.YarnJacksonJaxbJsonProvider.configObjectMapper(YarnJacksonJaxbJsonProvider.java:59)
>> at
>> org.apache.hadoop.yarn.util.timeline.TimelineUtils.(TimelineUtils.java:50)
>> at
>> org.apache.hadoop.yarn.client.api.impl.YarnClientImpl.serviceInit(YarnClientImpl.java:172)
>> at
>> org.apache.hadoop.service.AbstractService.init(AbstractService.java:163)
>> at
>> org.apache.spark.deploy.yarn.Client.submitApplication(Client.scala:108)
>> at
>> org.apache.spark.scheduler.cluster.YarnClientSchedulerBackend.start(YarnClientSchedulerBackend.scala:57)
>> at
>> org.apache.spark.scheduler.TaskSchedulerImpl.start(TaskSchedulerImpl.scala:144)
>> at org.apache.spark.SparkContext.(SparkContext.scala:523)
>> at
>> org.apache.spark.repl.SparkILoop.createSparkContext(SparkILoop.scala:1017)
>> at $iwC$$iwC.(:9)
>> at $iwC.(:18)
>> at (:20)
>> at .(:24)
>> at .()
>> at .(:7)
>> at .()
>> at $print()
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>> at java.lang.reflect.Method.invoke(Method.java:606)
>> at
>> org.apache.spark.repl.SparkIMain$ReadEvalPrint.call(SparkIMain.scala:1065)
>> at
>> org.apache.spark.repl.SparkIMain$Request.loadAndRun(SparkIMain.scala:1340)
>> at
>> org.apache.spark.repl.SparkIMain.loadAndRunReq$1(SparkIMain.scala:840)
>> at
>> org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:871)
>> at
>> org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:819)
>> at
>> org.apache.spark.repl.SparkILoop.reallyInterpret$1(SparkILoop.scala:857)
>> at
>> org.apache.spark.repl.SparkILoop.interpretStartingWith(SparkILoop.scala:902)
>> at org.apache.spark.repl.SparkILoop.command(SparkILoop.scala:814)
>> at
>> org.apache.spark.repl.SparkILoopInit$$anonfun$initializeSpark$1.apply(SparkILoopInit.scala:125)
>> at
>> org.apache.spark.repl.SparkILoopInit$$anonfun$initializeSpark$1.apply(SparkILoopInit.scala:124)
>> at
>> org.apache.spark.repl.SparkIMain.beQuietDuring(SparkIMain.scala:324)
>> at
>> org.apache.spark.repl.SparkILoopInit$class.initializeSpark(SparkILoopInit.scala:124)
>> at
>> org.apache.spark.repl.SparkILoop.initializeSpark(SparkILoop.scala:64)
>> at
>> org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1$$anonfun$apply$mcZ$sp$5.apply$mcV$sp(SparkILoop.scala:974)
>> at
>> org.apache.spark.repl.SparkILoopInit$class.runThunks(SparkILoopInit.scala:159)
>> at org.apache.spark.repl.SparkILoop.runThunks(SparkILoop.scala:64)
>> at
>> org.apache.spark.repl.SparkILoopInit$class.postInitialization(SparkILoopInit.scala:108)
>> at
>> org.apache.spark.repl.SparkILoop.postInitialization(SparkILoop.scala:64)
>> at
>> org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply$mcZ$sp(SparkILoop.scala:991)
>> at
>> org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
>> at
>> org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
>> at
>> scala.tools.nsc.util.ScalaClassLoader$.savingContextLoader(ScalaClassLoader.scala:135)

Re: Error : starting spark-shell with phoenix client jar

[Ankit Singhal]

Phoenix team is in process of releasing 4.7 soon so for meanwhile you can
ask for a hotfix from your distribution provider.

On Thu, Feb 18, 2016 at 2:35 PM, Divya Gehlot 
wrote:

> Thank you very much Ankit for guiding me in the right direction.
>
> How can I upgrade to phoenix 4.7 ?
> I couldn't find it here  <http://apache.arvixe.com/phoenix/>
>
> On 18 February 2016 at 16:43, Ankit Singhal 
> wrote:
>
>> Hi Divya,
>>
>> It is fixed in 4.7 , please find a jira for the same.
>> https://issues.apache.org/jira/browse/PHOENIX-2608
>>
>> Regards,
>> Ankit Singhal
>>
>> On Thu, Feb 18, 2016 at 2:03 PM, Divya Gehlot 
>> wrote:
>>
>>> Hi,
>>> I am getting following error while starting spark shell with phoenix
>>> clients
>>> spark-shell  --jars
>>> /usr/hdp/current/phoenix-client/phoenix-4.4.0.2.3.4.0-3485-client.jar
>>> --driver-class-path
>>> /usr/hdp/current/phoenix-client/phoenix-4.4.0.2.3.4.0-3485-client.jar
>>> --master yarn-client
>>>
>>> StackTrace :
>>>
>>>>  INFO TimelineClientImpl: Timeline service address:
>>>> http://ip-xxx-xx-xx-xxx.ap-southeast-1.compute.internal:8188/ws/v1/timeline/
>>>> java.lang.NoSuchMethodError:
>>>> org.codehaus.jackson.map.ObjectMapper.setSerializationInclusion(Lorg/codehaus/jackson/map/annotate/JsonSerialize$Inclusion;)Lorg/codehaus/jackson/map/ObjectMapper;
>>>> at
>>>> org.apache.hadoop.yarn.webapp.YarnJacksonJaxbJsonProvider.configObjectMapper(YarnJacksonJaxbJsonProvider.java:59)
>>>> at
>>>> org.apache.hadoop.yarn.util.timeline.TimelineUtils.(TimelineUtils.java:50)
>>>> at
>>>> org.apache.hadoop.yarn.client.api.impl.YarnClientImpl.serviceInit(YarnClientImpl.java:172)
>>>> at
>>>> org.apache.hadoop.service.AbstractService.init(AbstractService.java:163)
>>>> at
>>>> org.apache.spark.deploy.yarn.Client.submitApplication(Client.scala:108)
>>>> at
>>>> org.apache.spark.scheduler.cluster.YarnClientSchedulerBackend.start(YarnClientSchedulerBackend.scala:57)
>>>> at
>>>> org.apache.spark.scheduler.TaskSchedulerImpl.start(TaskSchedulerImpl.scala:144)
>>>> at org.apache.spark.SparkContext.(SparkContext.scala:523)
>>>> at
>>>> org.apache.spark.repl.SparkILoop.createSparkContext(SparkILoop.scala:1017)
>>>> at $iwC$$iwC.(:9)
>>>> at $iwC.(:18)
>>>> at (:20)
>>>> at .(:24)
>>>> at .()
>>>> at .(:7)
>>>> at .()
>>>> at $print()
>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>> at
>>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>>> at
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>> at java.lang.reflect.Method.invoke(Method.java:606)
>>>> at
>>>> org.apache.spark.repl.SparkIMain$ReadEvalPrint.call(SparkIMain.scala:1065)
>>>> at
>>>> org.apache.spark.repl.SparkIMain$Request.loadAndRun(SparkIMain.scala:1340)
>>>> at
>>>> org.apache.spark.repl.SparkIMain.loadAndRunReq$1(SparkIMain.scala:840)
>>>> at
>>>> org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:871)
>>>> at
>>>> org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:819)
>>>> at
>>>> org.apache.spark.repl.SparkILoop.reallyInterpret$1(SparkILoop.scala:857)
>>>> at
>>>> org.apache.spark.repl.SparkILoop.interpretStartingWith(SparkILoop.scala:902)
>>>> at
>>>> org.apache.spark.repl.SparkILoop.command(SparkILoop.scala:814)
>>>> at
>>>> org.apache.spark.repl.SparkILoopInit$$anonfun$initializeSpark$1.apply(SparkILoopInit.scala:125)
>>>> at
>>>> org.apache.spark.repl.SparkILoopInit$$anonfun$initializeSpark$1.apply(SparkILoopInit.scala:124)
>>>> at
>>>> org.apache.spark.repl.SparkIMain.beQuietDuring(SparkIMain.scala:324)
>>>> at
>>>> org.apache.spark.repl.SparkILoopInit$class.initializeSpark(SparkILoopInit.scala:124)
>>>> at
>>>> org.apache.spark.repl.SparkILoop.initializeSpark(SparkILoop.scala:64)
>>>> at
>

Re: ORDER BY Error on Windows

[Ankit Singhal]

Hi Yiannis,

You may need to set phoenix.spool.directory to correct windows folder as by
default it is set to /tmp.
It is fixed in 4.7.
https://issues.apache.org/jira/browse/PHOENIX-2348

Regards,
Ankit Singhal

On Wed, Feb 24, 2016 at 10:05 PM, Yiannis Gkoufas 
wrote:

> Hi there,
>
> we have been using phoenix client without a problem in linux systems but
> we have encountered some problems on windows.
> We run the queries through SquirellSQL using the 4.5.2 client jar
> The query which looks like this SELECT * FROM TABLE WHERE ID='TEST' works
> without a problem. But when we issue SELECT * FROM TABLE WHERE ID='TEST'
> ORDER BY DT we get the error:
> org.apache.phoenix.exception.PhoenixIOException:
> org.apache.phoenix.exception.PhoenixIOException: The system cannot find the
> path specified
> at
> org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:108)
> at
> org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:553)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.getIterators(MergeSortResultIterator.java:48)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.minIterator(MergeSortResultIterator.java:84)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.next(MergeSortResultIterator.java:111)
> at
> org.apache.phoenix.iterate.DelegateResultIterator.next(DelegateResultIterator.java:44)
> at
> org.apache.phoenix.iterate.LimitingResultIterator.next(LimitingResultIterator.java:47)
> at
> org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:771)
> at
> net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetWrapper._nextOnResultSet(ResultSetWrapper.java:95)
> at
> net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetWrapper.next(ResultSetWrapper.java:56)
> at
> net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:182)
> at
> net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.createRow(ResultSetDataSet.java:238)
> at
> net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet._setResultSet(ResultSetDataSet.java:204)
> at
> net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setSqlExecutionTabResultSet(ResultSetDataSet.java:127)
> at
> net.sourceforge.squirrel_sql.client.session.mainpanel.SQLExecutionHandler.sqlResultSetAvailable(SQLExecutionHandler.java:423)
> at
> net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processResultSet(SQLExecuterTask.java:549)
> at
> net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:414)
> at
> net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:212)
> at
> net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
> at java.lang.Thread.run(Unknown Source)
> Caused by: java.util.concurrent.ExecutionException:
> org.apache.phoenix.exception.PhoenixIOException: The system cannot find the
> path specified
> at java.util.concurrent.FutureTask.report(Unknown Source)
> at java.util.concurrent.FutureTask.get(Unknown Source)
> at
> org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:549)
> ... 18 more
> Caused by: org.apache.phoenix.exception.PhoenixIOException: The system
> cannot find the path specified
> at
> org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:108)
> at
> org.apache.phoenix.iterate.SpoolingResultIterator.(SpoolingResultIterator.java:151)
> at
> org.apache.phoenix.iterate.SpoolingResultIterator.(SpoolingResultIterator.java:86)
> at
> org.apache.phoenix.iterate.SpoolingResultIterator.(SpoolingResultIterator.java:64)
> at
> org.apache.phoenix.iterate.SpoolingResultIterator$SpoolingResultIteratorFactory.newIterator(SpoolingResultIterator.java:81)
> at
> org.apache.phoenix.iterate.ChunkedResultIterator.(ChunkedResultIterator.java:92)
> at
> org.apache.phoenix.iterate.ChunkedResultIterator.(ChunkedResultIterator.java:47)
> at
> org.apache.phoenix.iterate.ChunkedResultIterator$ChunkedResultIteratorFactory.newIterator(ChunkedResultIterator.java:72)
> at
> org.apache.phoenix.iterate.ParallelIterators$1.call(ParallelIterators.java:109)
> at
> org.apache.phoenix.iterate.ParallelIterators$1.call(ParallelIterators.java:100)
> at java.util.concurrent.FutureTask.run(Unknown Source)
> at
> org.apache.phoenix.job.JobManager$InstrumentedJobFutureTask.run(JobManager.java:183)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
> ... 1 more
> Caused by: java.io.IOExcept

Re: Need Help Dropping Phoenix Table Without Dropping HBase Table

[Ankit Singhal]

Hi Steve,

can you check whether the properties are picked by the sql/application
client.

Regards,
Ankit Singhal

On Wed, Feb 24, 2016 at 11:09 PM, Steve Terrell 
wrote:

> HI, I hope someone can tell me what I'm doing wrong…
>
> I set *phoenix.schema.dropMetaData* to *false* in hbase-site.xml on both
> the client and server side.
>
> I restarted the HBase master service.
>
> I used Phoenix to create a table and upsert some values.
>
> I used Phoenix to drop the table.
>
> I expected the table to still exist when I went into *hbase shell* and
> did a *list*, but the table apparently was dropped from HBase as well.
>
> Did I miss something?
>
> Thank you,
> Steve
>

Re: Need Help Dropping Phoenix Table Without Dropping HBase Table

[Ankit Singhal]

Yes, data will be deleted during drop table command and currently there is
no parameter to control that. You may raise a jira for this.

A workaround you may try is by opening a connection at a timestamp a little
greater than last modified timestamp of table and then run drop table
command. but remember you may still loose some data inserted before that
timestamp

Regards,
Ankit Singhal

On Wed, Feb 24, 2016 at 11:27 PM, Steve Terrell 
wrote:

> Good idea!
>
> I was using SQuirreL Client, and I did put the hbase-site.xml in the same
> directory as the driver jar.  However, I did not know how to check to see
> that the property was being found.
>
> So, I switched to using the sqlline query command line, and this time the
> table remained in the hbase shell list after I dropped it in Phoenix.  So,
> when using sqlline I am sure that the property is being picked up.  I'll
> worry about SQuirreL another day.
>
> Now I have a new problem:  Even though the table still exists in HBase,
> the data that I had upserted into the table via Phoenix does not show up
> when I *scan* it in *hbase shell*.  Looks like the drop table in Phoenix
> also deleted my rows in HBase.
>
> Is there  a way to drop the Phoenix table without the rows in HBase being
> deleted?
>
> Thank you,
> Steve
>
>
> On Wed, Feb 24, 2016 at 11:42 AM, Ankit Singhal 
> wrote:
>
>> Hi Steve,
>>
>> can you check whether the properties are picked by the sql/application
>> client.
>>
>> Regards,
>> Ankit Singhal
>>
>> On Wed, Feb 24, 2016 at 11:09 PM, Steve Terrell 
>> wrote:
>>
>>> HI, I hope someone can tell me what I'm doing wrong…
>>>
>>> I set *phoenix.schema.dropMetaData* to *false* in hbase-site.xml on
>>> both the client and server side.
>>>
>>> I restarted the HBase master service.
>>>
>>> I used Phoenix to create a table and upsert some values.
>>>
>>> I used Phoenix to drop the table.
>>>
>>> I expected the table to still exist when I went into *hbase shell* and
>>> did a *list*, but the table apparently was dropped from HBase as well.
>>>
>>> Did I miss something?
>>>
>>> Thank you,
>>> Steve
>>>
>>
>>
>

Re: Cache of region boundaries are out of date - during index creation

[Ankit Singhal]

can you try after truncating the SYSTEM.STATS table or deleting records of
parent table only from SYSTEM.STATS like below.

DELETE * FROM SYSTEM.STATS WHERE PHYSICAL_NAME='media';

Regards,
Ankit Singhal


On Wed, Feb 24, 2016 at 8:16 PM, Jaroslav Šnajdr  wrote:

> Hello everyone,
>
> while creating an index on my Phoenix table:
>
> CREATE LOCAL INDEX idx_media_next_update_at ON media
> (next_metadata_update_at);
>
>
> I'm getting an exception every time the command is run, after it's been
> running for a while:
>
>
> *Error: ERROR 1108 (XCL08): Cache of region boundaries are out of date.
> (state=XCL08,code=1108)*
>
> org.apache.phoenix.schema.StaleRegionBoundaryCacheException: ERROR 1108
> (XCL08): Cache of region boundaries are out of date.
>
> at
> org.apache.phoenix.exception.SQLExceptionCode$13.newException(SQLExceptionCode.java:312)
>
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
>
> at
> org.apache.phoenix.util.ServerUtil.parseRemoteException(ServerUtil.java:131)
>
> at
> org.apache.phoenix.util.ServerUtil.parseServerExceptionOrNull(ServerUtil.java:115)
>
> at
> org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:104)
>
> at
> org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:611)
>
> at
> org.apache.phoenix.iterate.ConcatResultIterator.getIterators(ConcatResultIterator.java:50)
>
> at
> org.apache.phoenix.iterate.ConcatResultIterator.currentIterator(ConcatResultIterator.java:97)
>
> at
> org.apache.phoenix.iterate.ConcatResultIterator.next(ConcatResultIterator.java:117)
>
> at
> org.apache.phoenix.iterate.BaseGroupedAggregatingResultIterator.next(BaseGroupedAggregatingResultIterator.java:64)
>
> at
> org.apache.phoenix.iterate.UngroupedAggregatingResultIterator.next(UngroupedAggregatingResultIterator.java:39)
>
> at
> org.apache.phoenix.schema.MetaDataClient$2.execute(MetaDataClient.java:1034)
>
> at
> org.apache.phoenix.query.ConnectionQueryServicesImpl.updateData(ConnectionQueryServicesImpl.java:2132)
>
> at
> org.apache.phoenix.schema.MetaDataClient.buildIndex(MetaDataClient.java:1059)
>
> at
> org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1348)
>
> at
> org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:313)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>
> at sqlline.Commands.execute(Commands.java:822)
>
>
> Any suggestions why this is happening? When the command is running,
> nothing else is accessing HBase, so there should be no reason for splitting
> regions. I'm using Phoenix 4.6.0-HBase-1.1.
>
> Creating a global index (i.e., removing the LOCAL keyword) leads to the
> same result. Actually, we're getting this error on many other queries that
> use scans and run for some longer period of time (i.e., more that a few
> seconds).
>
> Can this be an indication that there is something wrong with the
> underlying HBase cluster? Some corruption or misconfiguration? hbck reports
> the tables as healthy and there is no other indication that something might
> be wrong.
>
> Jarda Snajdr
>
>
>

Re: Unexpected region splits

[Ankit Singhal]

Hi Pedro,

You may need to configure your split policy
to ConstantSizeRegionSplitPolicy if you want *hbase.hregion.max.filesize to
be adhered strictly * (as default policy is
IncreasingToUpperBoundRegionSplitPolicy which uses Min (R^2 *
“hbase.hregion.memstore.flush.size”, “hbase.hregion.max.filesize”), where R
is the number of regions of the same table hosted on the same regionserver)

You may read below article to understand splitting policies:-
http://hortonworks.com/blog/apache-hbase-region-splitting-and-merging/

Regards,
Ankit Singhal

On Mon, Feb 15, 2016 at 8:52 PM, Pedro Gandola 
wrote:

> Hi,
>
> I have a cluster using *HBase 1.1.2* where I have a table and a local
> index (using *Apache Phoenix 4.6*) in total both tables have *300 regions*
> (aprox: *18 regions per server*), my* hbase.hregion.max.filesize=30GB *and
> my region sizes are now *~4.5GB compressed (~7GB uncompressed)*. However
> each time I restart a RS sometimes a region gets split. This is unexpected
> because my key space is uniform (using MD5) and if the problem was my 
> *region.size
> > * *hbase.hregion.max.filesize *I would expect to have all the regions
> or almost all splitting but this only happens when I restart a RS and it
> happens only for 1 or 2 regions.
>
> What are the different scenarios where a region can split?
>
> What are the right steps to restart a region server in order to avoid
> these unexpected splits?
>
> Thank you,
> Cheers
> Pedro
>
>

Re: Phoenix DB Migration with Flyway

[Ankit Singhal]

Awesome!! Great work Josh.

On Tue, Mar 8, 2016 at 8:59 PM, James Heather 
wrote:

> Cool. That's big news for us.
> On 8 Mar 2016 2:15 p.m., "Josh Mahonin"  wrote:
>
>> Hi all,
>>
>> Just thought I'd let you know that Flyway 4.0 was recently released,
>> which includes support for DB migrations with Phoenix.
>>
>> https://flywaydb.org/blog/flyway-4.0
>>
>> Josh
>>
>

Re: [Query:]Table creation with column family in Phoenix

[Ankit Singhal]

You may check discussion from below mail chain.
https://www.mail-archive.com/dev@phoenix.apache.org/msg19448.html

On Fri, Mar 11, 2016 at 3:20 PM, Divya Gehlot 
wrote:

>
> Hi,
> I created a table in Phoenix with three column families  and Inserted the
> values as shown below
>
> Syntax :
>
>> CREATE TABLE TESTCF (MYKEY VARCHAR NOT NULL PRIMARY KEY, CF1.COL1
>> VARCHAR, CF2.COL2 VARCHAR, CF3.COL3 VARCHAR)
>> UPSERT INTO TESTCF (MYKEY,CF1.COL1,CF2.COL2,CF3.COL3)values
>> ('Key2','CF1','CF2','CF3')
>> UPSERT INTO TESTCF (MYKEY,CF1.COL1,CF2.COL2,CF3.COL3)values
>> ('Key2','CF12','CF22','CF32')
>
>
>  When I try to scan same table in Hbase
> hbase(main):010:0> scan "TESTCF"
>
>> ROW   COLUMN+CELL
>>  Key1 column=CF1:COL1, timestamp=1457682385805, value=CF1
>>  Key1 column=CF1:_0, timestamp=1457682385805, value=
>>  Key1 column=CF2:COL2, timestamp=1457682385805, value=CF2
>>  Key1 column=CF3:COL3, timestamp=1457682385805, value=CF3
>>  Key2 column=CF1:COL1, timestamp=1457682426396, value=CF12
>>  Key2 column=CF1:_0, timestamp=1457682426396, value=
>>  Key2 column=CF2:COL2, timestamp=1457682426396, value=CF22
>>  Key2 column=CF3:COL3, timestamp=1457682426396, value=CF32
>> 2 row(s) in 0.0260 seconds
>
>
> My query is why I am getting CF1:_0 one extra column in each row with no
> value.
>
> Can any body explain me .
> Would really appreciate the help.
>
> Thanks,
> Divya
>
>
>
>
>

Re: Problem Updating Stats

[Ankit Singhal]

Yes it seems to.
Did you get any error related to SYSTEM.STATS when the client is connected
first time ?

can you please describe your system.stats table and paste the output here.

On Wed, Mar 16, 2016 at 3:24 AM, Benjamin Kim  wrote:

> When trying to run update status on an existing table in hbase, I get
> error:
>
> Update stats:
>
> UPDATE STATISTICS "ops_csv" ALL
>
> error:
>
> ERROR 504 (42703): Undefined column. columnName=REGION_NAME
>
> Looks like the meta data information is messed up, ie. there is no column
> with name REGION_NAME in this table.
>
> I see similar errors for other tables that we currently have in hbase.
>
> We are using CDH 5.5.2, HBase 1.0.0, and Phoenix 4.5.2.
>
> Thanks,
> Ben
>

Re: Problem Updating Stats

[Ankit Singhal]

Sorry Ben, I may not be clear in first comment but I need you to describe
SYSTEM.STATS in some sql client so that I can see the columns present.
And also please scan 'SYSTEM.CATALOG' ,{RAW=>true} in hbase shell and
attach a output here.

On Wed, Mar 16, 2016 at 8:55 PM, Benjamin Kim  wrote:

> Ankit,
>
> I did not see any problems when connecting with the phoenix sqlline
> client. So, below is the what you asked for. I hope that you can give us
> insight into fixing this.
>
> hbase(main):005:0> describe 'SYSTEM.STATS'
> Table SYSTEM.STATS is ENABLED
>
>
> SYSTEM.STATS, {TABLE_ATTRIBUTES => {coprocessor$1 =>
> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
> coprocessor$2 => '|org.apache.phoenix.coprocessor.UngroupedAggr
> egateRegionObserver|805306366|', coprocessor$3 =>
> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
> coprocessor$4 => '|org.apache.phoenix.coprocessor.Serv
> erCachingEndpointImpl|805306366|', coprocessor$5 =>
> '|org.apache.hadoop.hbase.coprocessor.MultiRowMutationEndpoint|805306366|',
> coprocessor$6 => '|org.apache.hadoop.hbase.regionserv
> er.LocalIndexSplitter|805306366|', METADATA => {'SPLIT_POLICY' =>
> 'org.apache.phoenix.schema.MetaDataSplitPolicy'}}
>
> COLUMN FAMILIES DESCRIPTION
>
>
> {NAME => '0', DATA_BLOCK_ENCODING => 'FAST_DIFF', BLOOMFILTER => 'ROW',
> REPLICATION_SCOPE => '0', VERSIONS => '3', COMPRESSION => 'NONE',
> MIN_VERSIONS => '0', TTL => 'FOREVER', KEEP
> _DELETED_CELLS => 'true', BLOCKSIZE => '65536', IN_MEMORY => 'false',
> BLOCKCACHE => 'true'}
>
> 1 row(s) in 0.0280 seconds
>
> Thanks,
> Ben
>
>
> On Mar 15, 2016, at 11:59 PM, Ankit Singhal 
> wrote:
>
> Yes it seems to.
> Did you get any error related to SYSTEM.STATS when the client is connected
> first time ?
>
> can you please describe your system.stats table and paste the output here.
>
> On Wed, Mar 16, 2016 at 3:24 AM, Benjamin Kim  wrote:
>
>> When trying to run update status on an existing table in hbase, I get
>> error:
>>
>> Update stats:
>>
>> UPDATE STATISTICS "ops_csv" ALL
>>
>> error:
>>
>> ERROR 504 (42703): Undefined column. columnName=REGION_NAME
>>
>> Looks like the meta data information is messed up, ie. there is no column
>> with name REGION_NAME in this table.
>>
>> I see similar errors for other tables that we currently have in hbase.
>>
>> We are using CDH 5.5.2, HBase 1.0.0, and Phoenix 4.5.2.
>>
>> Thanks,
>> Ben
>>
>
>
>

Re: Problem Updating Stats

[Ankit Singhal]

It seems from the attached logs that you have upgraded phoenix to 4.7
version and now you are using old client to connect with it.
"Update statistics" command and guideposts will not work with old client
after upgradation to 4.7, you need to use the new client for such
operations.

On Wed, Mar 16, 2016 at 10:55 PM, Benjamin Kim  wrote:

> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME
>|   |
>
> +-+--+-++-+
> |  | SYSTEM| STATS  |
> PHYSICAL_NAME| 12  |
> |  | SYSTEM| STATS  |
> COLUMN_FAMILY| 12  |
> |  | SYSTEM| STATS  |
> GUIDE_POST_KEY  | -3   |
> |  | SYSTEM| STATS  |
> GUIDE_POSTS_WIDTH   | -5   |
> |  | SYSTEM| STATS  |
> LAST_STATS_UPDATE_TIME | 91  |
> |  | SYSTEM| STATS  |
> GUIDE_POSTS_ROW_COUNT   | -5   |
>
> I have attached the SYSTEM.CATALOG contents.
>
> Thanks,
> Ben
>
>
>
> On Mar 16, 2016, at 9:34 AM, Ankit Singhal 
> wrote:
>
> Sorry Ben, I may not be clear in first comment but I need you to describe
> SYSTEM.STATS in some sql client so that I can see the columns present.
> And also please scan 'SYSTEM.CATALOG' ,{RAW=>true} in hbase shell and
> attach a output here.
>
> On Wed, Mar 16, 2016 at 8:55 PM, Benjamin Kim  wrote:
>
>> Ankit,
>>
>> I did not see any problems when connecting with the phoenix sqlline
>> client. So, below is the what you asked for. I hope that you can give us
>> insight into fixing this.
>>
>> hbase(main):005:0> describe 'SYSTEM.STATS'
>> Table SYSTEM.STATS is ENABLED
>>
>>
>> SYSTEM.STATS, {TABLE_ATTRIBUTES => {coprocessor$1 =>
>> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
>> coprocessor$2 => '|org.apache.phoenix.coprocessor.UngroupedAggr
>> egateRegionObserver|805306366|', coprocessor$3 =>
>> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
>> coprocessor$4 => '|org.apache.phoenix.coprocessor.Serv
>> erCachingEndpointImpl|805306366|', coprocessor$5 =>
>> '|org.apache.hadoop.hbase.coprocessor.MultiRowMutationEndpoint|805306366|',
>> coprocessor$6 => '|org.apache.hadoop.hbase.regionserv
>> er.LocalIndexSplitter|805306366|', METADATA => {'SPLIT_POLICY' =>
>> 'org.apache.phoenix.schema.MetaDataSplitPolicy'}}
>>
>> COLUMN FAMILIES DESCRIPTION
>>
>>
>> {NAME => '0', DATA_BLOCK_ENCODING => 'FAST_DIFF', BLOOMFILTER => 'ROW',
>> REPLICATION_SCOPE => '0', VERSIONS => '3', COMPRESSION => 'NONE',
>> MIN_VERSIONS => '0', TTL => 'FOREVER', KEEP
>> _DELETED_CELLS => 'true', BLOCKSIZE => '65536', IN_MEMORY => 'false',
>> BLOCKCACHE => 'true'}
>>
>> 1 row(s) in 0.0280 seconds
>>
>> Thanks,
>> Ben
>>
>>
>> On Mar 15, 2016, at 11:59 PM, Ankit Singhal 
>> wrote:
>>
>> Yes it seems to.
>> Did you get any error related to SYSTEM.STATS when the client is
>> connected first time ?
>>
>> can you please describe your system.stats table and paste the output here.
>>
>> On Wed, Mar 16, 2016 at 3:24 AM, Benjamin Kim  wrote:
>>
>>> When trying to run update status on an existing table in hbase, I get
>>> error:
>>>
>>> Update stats:
>>>
>>> UPDATE STATISTICS "ops_csv" ALL
>>>
>>> error:
>>>
>>> ERROR 504 (42703): Undefined column. columnName=REGION_NAME
>>>
>>> Looks like the meta data information is messed up, ie. there is no
>>> column with name REGION_NAME in this table.
>>>
>>> I see similar errors for other tables that we currently have in hbase.
>>>
>>> We are using CDH 5.5.2, HBase 1.0.0, and Phoenix 4.5.2.
>>>
>>> Thanks,
>>> Ben
>>>
>>
>>
>>
>
>
>

Re: Problem Updating Stats

[Ankit Singhal]

ok , or you could have drop SYSTEM.STATS table from sql client at
CURRENT_SCN=7 and reconnect the client. If client doesn't see this table ,
it will create it automatically.

On Thu, Mar 17, 2016 at 3:14 AM, Benjamin Kim  wrote:

> I got it to work by uninstalling Phoenix and reinstalling it again. I had
> to wipe clean all components.
>
> Thanks,
> Ben
>
>
> On Mar 16, 2016, at 10:47 AM, Ankit Singhal 
> wrote:
>
> It seems from the attached logs that you have upgraded phoenix to 4.7
> version and now you are using old client to connect with it.
> "Update statistics" command and guideposts will not work with old client
> after upgradation to 4.7, you need to use the new client for such
> operations.
>
> On Wed, Mar 16, 2016 at 10:55 PM, Benjamin Kim  wrote:
>
>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME
>>  |   |
>>
>> +-+--+-++-+
>> |  | SYSTEM| STATS  |
>> PHYSICAL_NAME| 12  |
>> |  | SYSTEM| STATS  |
>> COLUMN_FAMILY| 12  |
>> |  | SYSTEM| STATS  |
>> GUIDE_POST_KEY  | -3   |
>> |  | SYSTEM| STATS  |
>> GUIDE_POSTS_WIDTH   | -5   |
>> |  | SYSTEM| STATS  |
>> LAST_STATS_UPDATE_TIME | 91  |
>> |  | SYSTEM| STATS  |
>> GUIDE_POSTS_ROW_COUNT   | -5   |
>>
>> I have attached the SYSTEM.CATALOG contents.
>>
>> Thanks,
>> Ben
>>
>>
>>
>> On Mar 16, 2016, at 9:34 AM, Ankit Singhal 
>> wrote:
>>
>> Sorry Ben, I may not be clear in first comment but I need you to describe
>> SYSTEM.STATS in some sql client so that I can see the columns present.
>> And also please scan 'SYSTEM.CATALOG' ,{RAW=>true} in hbase shell and
>> attach a output here.
>>
>> On Wed, Mar 16, 2016 at 8:55 PM, Benjamin Kim  wrote:
>>
>>> Ankit,
>>>
>>> I did not see any problems when connecting with the phoenix sqlline
>>> client. So, below is the what you asked for. I hope that you can give us
>>> insight into fixing this.
>>>
>>> hbase(main):005:0> describe 'SYSTEM.STATS'
>>> Table SYSTEM.STATS is ENABLED
>>>
>>>
>>> SYSTEM.STATS, {TABLE_ATTRIBUTES => {coprocessor$1 =>
>>> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
>>> coprocessor$2 => '|org.apache.phoenix.coprocessor.UngroupedAggr
>>> egateRegionObserver|805306366|', coprocessor$3 =>
>>> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
>>> coprocessor$4 => '|org.apache.phoenix.coprocessor.Serv
>>> erCachingEndpointImpl|805306366|', coprocessor$5 =>
>>> '|org.apache.hadoop.hbase.coprocessor.MultiRowMutationEndpoint|805306366|',
>>> coprocessor$6 => '|org.apache.hadoop.hbase.regionserv
>>> er.LocalIndexSplitter|805306366|', METADATA => {'SPLIT_POLICY' =>
>>> 'org.apache.phoenix.schema.MetaDataSplitPolicy'}}
>>>
>>> COLUMN FAMILIES DESCRIPTION
>>>
>>>
>>> {NAME => '0', DATA_BLOCK_ENCODING => 'FAST_DIFF', BLOOMFILTER => 'ROW',
>>> REPLICATION_SCOPE => '0', VERSIONS => '3', COMPRESSION => 'NONE',
>>> MIN_VERSIONS => '0', TTL => 'FOREVER', KEEP
>>> _DELETED_CELLS => 'true', BLOCKSIZE => '65536', IN_MEMORY => 'false',
>>> BLOCKCACHE => 'true'}
>>>
>>> 1 row(s) in 0.0280 seconds
>>>
>>> Thanks,
>>> Ben
>>>
>>>
>>> On Mar 15, 2016, at 11:59 PM, Ankit Singhal 
>>> wrote:
>>>
>>> Yes it seems to.
>>> Did you get any error related to SYSTEM.STATS when the client is
>>> connected first time ?
>>>
>>> can you please describe your system.stats table and paste the output
>>> here.
>>>
>>> On Wed, Mar 16, 2016 at 3:24 AM, Benjamin Kim 
>>> wrote:
>>>
>>>> When trying to run update status on an existing table in hbase, I get
>>>> error:
>>>>
>>>> Update stats:
>>>>
>>>> UPDATE STATISTICS "ops_csv" ALL
>>>>
>>>> error:
>>>>
>>>> ERROR 504 (42703): Undefined column. columnName=REGION_NAME
>>>>
>>>> Looks like the meta data information is messed up, ie. there is no
>>>> column with name REGION_NAME in this table.
>>>>
>>>> I see similar errors for other tables that we currently have in hbase.
>>>>
>>>> We are using CDH 5.5.2, HBase 1.0.0, and Phoenix 4.5.2.
>>>>
>>>> Thanks,
>>>> Ben
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>

Re: problems about using phoenix over hbase

[Ankit Singhal]

Hi,

I think when you are doing a put from shel then value is going as String
not as Integer. So phoenix can decode it with varchar only .
If you want to put Integer to your hbase table , use byte representation of
integer or java api instead.

Regards,
Ankit Singhal

On Wed, Apr 20, 2016 at 8:00 AM, 金砖  wrote:

> thanks for your reply rafa!
>
> It works. Now the value isn't null,  but is -1322110156.
>
> But when using this :  create view if not exists "test" (pk VARCHAR
> primary key, "family"."number" UNSIGNED_INT);
>
> value of number is 825373492.
>
> value is right only when I use this:  create view if not exists "test" (pk
> VARCHAR primary key, "family"."number" varche nar);
>
> Does this mean I could only use varchar? I'm totally confused.
>
>
>
>
> 在 2016年04月19日 20:17, rafa 写道:
>
> Hi,
>
> test with:
>
> create view if not exists "test" (pk VARCHAR primary key,
> "family"."number" INTEGER);
>
> regards,
> rafa
>
> On Tue, Apr 19, 2016 at 12:58 PM, 金砖  wrote:
>
>> I'm new to phoenix
>> using phoenix-4.7.0-HBase-1.1 on hbase-1.1.3
>>
>> my steps:
>>
>> 1. create hbase table
>> create 'test', 'family'
>>
>> 2. put row in hbase table
>> put 'test', 'row1', 'family:number', '123456'
>>
>> 3. create view in phoenix
>> create view if not exists "test" (pk VARCHAR primary key,
>> "family".number INTEGER);
>>
>> 4. select phoenix view
>> select NUMBER from "test";
>>
>> But why result  is null ? Is there anything wrong about the steps ?
>>
>
>
>

Re: phoenix.spool.directory

[Ankit Singhal]

can you please check that hbase-site.xml(where you are setting this
property) is in phoenix class path .

On Wed, Apr 20, 2016 at 3:10 AM,  wrote:

> I am having trouble setting the "phoenix.spool.directory"
> (QueryServices.SPOOL_DIRECTORY) property value.  Any ideas?
>
>
> https://github.com/apache/phoenix/blob/master/phoenix-core/src/main/java/org/apache/phoenix/query/QueryServices.java
>
>
> https://github.com/apache/phoenix/blob/master/phoenix-core/src/main/java/org/apache/phoenix/cache/aggcache/SpillManager.java
>
>

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Hi Arun,

Do you see 'IS_ROW_TIMESTAMP' column in SYSTEM.CATALOG, by doing !describe
on system.catalog.


if not,
can you share the output of below command. As it seems SYSTEM.CATALOG was
updated with timestamp greater v4.6 timestamp , and which stopping upgrade
code to add a new column.

scan 'SYSTEM.CATALOG', {RAW=>true}



Regards,
Ankit Singhal

On Wed, Apr 20, 2016 at 4:25 AM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> After further investigation, we found that Phoenix Upsert query
> SYSTEM.CATALOG has IS_ROW_TIMESTAMP column, but PTableImpl.getColumn() is
> failing with error:"Undefined column. columnName=IS_ROW_TIMESTAMP" . Does
> this mean that PTableImpl is reading from cached entity of  SYSTEM.CATALOG
>  before 4.6 upgrade?"
>
> We do see that clearCache() is being called for 4.7, and 4.7 upgrades from
> ConnectionQueryServicesImpl class, but not for 4.6
>
>
> Thanks,
> Arun
>
> On Tue, Apr 19, 2016 at 10:22 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
>> James,
>>
>> To add more information on this issue, this happens in new phoenix views
>> associated with brand new tables as well. So, this cannot be an
>> upgrade/migration issue. Not figured out a specific way to reproduce this
>> issue yet. Could you throw some ideas on what direction this problem could
>> be approached from this point?
>>
>> Thanks,
>> Arun
>>
>
>

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

TED_DATE\x00defau
>
> lt
>
> \x00\x00TEST_TABLE_2 column=0:PK_NAME, timestamp=1460354090655,
> type=DeleteColumn
>
> _0_1460354090089\x00CDS_UPDATED_DATE\x00defau
>
> lt
>
> \x00\x00TEST_TABLE_2 column=0:SORT_ORDER, timestamp=1460354090655,
> value=\x80\x00\x00\x02
>
> _0_1460354090089\x00CDS_UPDATED_DATE\x00defau
>
> lt
>
> \x00\x00TEST_TABLE_2 column=0:VIEW_CONSTANT, timestamp=1460354090655,
> type=DeleteColumn
>
> _0_1460354090089\x00CDS_UPDATED_DATE\x00defau
>
> lt
>
> \x00\x00TEST_TABLE_2 column=0:_0, timestamp=1460354090655,
> type=DeleteColumn
>
> _0_1460354090089\x00CDS_UPDATED_DATE\x00defau
>
> lt
>
>
> Thanks,
>
> Arun
>
>
> On Wed, Apr 20, 2016 at 11:31 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
>> James,
>>
>> Table SYSTEM.CATALOG is ENABLED
>> SYSTEM.CATALOG, {TABLE_ATTRIBUTES => {coprocessor$1 =>
>> '|org.apache.phoenix.coprocessor.ScanRegionObserver|1|', coprocessor$2 =>
>> '|org.apache
>>
>> .phoenix.coprocessor.UngroupedAggregateRegionObserver|1|', coprocessor$3
>> => '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|1
>>
>> |', coprocessor$4 =>
>> '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|1|',
>> coprocessor$5 => '|org.apache.phoenix.coprocessor.MetaDa
>>
>> taEndpointImpl|1|', coprocessor$6 =>
>> '|org.apache.phoenix.coprocessor.MetaDataRegionObserver|2|', METADATA =>
>> {'SPLIT_POLICY' => 'org.apache.
>>
>> phoenix.schema.MetaDataSplitPolicy', 'UpgradeTo30' => 'true'}}
>> COLUMN FAMILIES DESCRIPTION
>> {NAME => '0', DATA_BLOCK_ENCODING => 'FAST_DIFF', BLOOMFILTER => 'ROW',
>> REPLICATION_SCOPE => '0', COMPRESSION => 'NONE', VERSIONS => '1000',
>>
>> MIN_VERSIONS => '0', TTL => 'FOREVER', KEEP_DELETED_CELLS => 'true',
>> BLOCKSIZE => '65536', IN_MEMORY => 'false', BLOCKCACHE => 'true'}
>>
>> 1 row in 0.6060 seconds
>>
>> The above is for describe SYSTEM.CATALOG. The output for scan
>> 'SYSTEM.CATALOG', {RAW=>true} is too huge.
>>
>> Thanks,
>> Arun
>>
>>
>> On Wed, Apr 20, 2016 at 11:19 AM, James Taylor 
>> wrote:
>>
>>> Arun,
>>> Please run the command Ankit mentioned in an HBase shell and post the
>>> output back here.
>>> Thanks,
>>> James
>>>
>>>
>>> On Wednesday, April 20, 2016, Arun Kumaran Sabtharishi <
>>> arun1...@gmail.com> wrote:
>>>
>>>> !describe SYSTEM.CATALOG is not returning IS_ROW_TIMESTAMP column.
>>>>
>>>> But we do see this column from select statement:
>>>>
>>>> select * from SYSTEM.CATALOG where TABLE_NAME=’TEST_TABLE_1’ AND
>>>> TABLE_SCHEM IS NULL AND TENANT_ID IS NULL ;
>>>>
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Arun
>>>>
>>>> On Wed, Apr 20, 2016 at 1:37 AM, Ankit Singhal <
>>>> ankitsingha...@gmail.com> wrote:
>>>>
>>>>> Hi Arun,
>>>>>
>>>>> Do you see 'IS_ROW_TIMESTAMP' column in SYSTEM.CATALOG, by doing
>>>>> !describe on system.catalog.
>>>>>
>>>>>
>>>>> if not,
>>>>> can you share the output of below command. As it seems SYSTEM.CATALOG
>>>>> was updated with timestamp greater v4.6 timestamp , and which stopping
>>>>> upgrade code to add a new column.
>>>>>
>>>>> scan 'SYSTEM.CATALOG', {RAW=>true}
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>> Ankit Singhal
>>>>>
>>>>> On Wed, Apr 20, 2016 at 4:25 AM, Arun Kumaran Sabtharishi <
>>>>> arun1...@gmail.com> wrote:
>>>>>
>>>>>> After further investigation, we found that Phoenix Upsert query
>>>>>> SYSTEM.CATALOG has IS_ROW_TIMESTAMP column, but PTableImpl.getColumn() is
>>>>>> failing with error:"Undefined column. columnName=IS_ROW_TIMESTAMP" . Does
>>>>>> this mean that PTableImpl is reading from cached entity of  
>>>>>> SYSTEM.CATALOG
>>>>>>  before 4.6 upgrade?"
>>>>>>
>>>>>> We do see that clearCache() is being called for 4.7, and 4.7 upgrades
>>>>>> from ConnectionQueryServicesImpl class, but not for 4.6
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>> Arun
>>>>>>
>>>>>> On Tue, Apr 19, 2016 at 10:22 AM, Arun Kumaran Sabtharishi <
>>>>>> arun1...@gmail.com> wrote:
>>>>>>
>>>>>>> James,
>>>>>>>
>>>>>>> To add more information on this issue, this happens in new phoenix
>>>>>>> views associated with brand new tables as well. So, this cannot be an
>>>>>>> upgrade/migration issue. Not figured out a specific way to reproduce 
>>>>>>> this
>>>>>>> issue yet. Could you throw some ideas on what direction this problem 
>>>>>>> could
>>>>>>> be approached from this point?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Arun
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>
>

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Arun,
the observations you had is understandable.
So, that's why we again requested the output of

*echo "scan 'SYSTEM.CATALOG',{RAW=>true}"|bin/hbase shell|grep CATALOG*

So that we can help you in adding ROW_TIMESTAMP column in system.catalog
table so that your phoenix setup works properly and you can also see it in
!describe command.






On Thu, Apr 21, 2016 at 4:20 AM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> Another observation:(After upgrading from Phoenix 4.4 to 4.6.1)
>
> In a new SYSTEM.CATALOG table , when connected from phoenix 4.6.1 client,
>
> !describe SYSTEM.CATALOG does not show IS_ROW_TIMESTAMP
>
> But, select * from SYSTEM.CATALOG shows the IS_ROW_TIMESTAMP column.
>
> Is this an expected behavior?
>
> If I have to see IS_ROW_TIMESTAMP when doing describe, what should I do?
> This issue is blocking us in the production environment. Any help to
> resolve or workaround is highly appreciated.
>
> Thanks,
> Arun
>
>
> On Wed, Apr 20, 2016 at 12:01 PM, Ankit Singhal 
> wrote:
>
>> It's ok if you can just post after grep for CATALOG in a command output (scan
>> 'SYSTEM.CATALOG', {RAW=>true}).
>>
>> On Wed, Apr 20, 2016 at 10:07 PM, Arun Kumaran Sabtharishi <
>> arun1...@gmail.com> wrote:
>>
>>> One more question to add,
>>> Do we need to have 1000 versions, and KEEP_DELETED_CELL=true?
>>>
>>> I have limited the scan in HBase and here is the following data.
>>>
>>> \x00\x00TEST_TABLE_2 column=0:, timestamp=1460455162842,
>>> type=DeleteFamily
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:BASE_COLUMN_COUNT,
>>> timestamp=1460354090655, value=\x7F\xFF\xFF\xFF
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:COLUMN_COUNT, timestamp=1460354090655,
>>> value=\x80\x00\x00\x05
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:DATA_TABLE_NAME, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:DEFAULT_COLUMN_FAMILY,
>>> timestamp=1460354090655, value=default
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:DISABLE_WAL, timestamp=1460354090655,
>>> value=\x00
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:IMMUTABLE_ROWS, timestamp=1460354090655,
>>> value=\x00
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:INDEX_STATE, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:INDEX_TYPE, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:MULTI_TENANT, timestamp=1460354090655,
>>> value=\x00
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:PK_NAME, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:ROW_KEY_ORDER_OPTIMIZABLE,
>>> timestamp=1460354090655, value=\x01
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:SALT_BUCKETS, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:STORE_NULLS, timestamp=1460354090655,
>>> value=\x00
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:TABLE_SEQ_NUM, timestamp=1460354090655,
>>> value=\x80\x00\x00\x00\x00\x00\x00\x00
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:TABLE_TYPE, timestamp=1460354090655,
>>> value=u
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:VIEW_INDEX_ID, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:VIEW_STATEMENT, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:VIEW_TYPE, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \x00\x00TEST_TABLE_2 column=0:_0, timestamp=1460354090655,
>>> type=DeleteColumn
>>>
>>> _0_1460354090089
>>>
>>> \

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Arun,
As per the system.catalog ,there seems no way that upgrade code(adding new
columns and view upgrade for version < v4.5) doesn't execute.

But anyways, let's add the required columns manually by following below
step;

>  ./sqlline.py localhost;CurrentSCN=9
> ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER,
IS_ROW_TIMESTAMP BOOLEAN;
>!quit

Quit the shell and start new session without CurrentSCN.
>  ./sqlline.py localhost
> !describe system.catalog

this should resolve the issue of missing column.

Regards,
Ankit Singhal


On Fri, Apr 22, 2016 at 3:02 AM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> Ankit,James,
>
> Please find the results you have asked for in the attachment text document.
>
> Thanks,
> Arun
>
> On Thu, Apr 21, 2016 at 3:12 AM, Ankit Singhal 
> wrote:
>
>> Arun,
>> the observations you had is understandable.
>> So, that's why we again requested the output of
>>
>> *echo "scan 'SYSTEM.CATALOG',{RAW=>true}"|bin/hbase shell|grep CATALOG*
>>
>> So that we can help you in adding ROW_TIMESTAMP column in system.catalog
>> table so that your phoenix setup works properly and you can also see it in
>> !describe command.
>>
>>
>>
>>
>>
>>
>> On Thu, Apr 21, 2016 at 4:20 AM, Arun Kumaran Sabtharishi <
>> arun1...@gmail.com> wrote:
>>
>>> Another observation:(After upgrading from Phoenix 4.4 to 4.6.1)
>>>
>>> In a new SYSTEM.CATALOG table , when connected from phoenix 4.6.1 client,
>>>
>>> !describe SYSTEM.CATALOG does not show IS_ROW_TIMESTAMP
>>>
>>> But, select * from SYSTEM.CATALOG shows the IS_ROW_TIMESTAMP column.
>>>
>>> Is this an expected behavior?
>>>
>>> If I have to see IS_ROW_TIMESTAMP when doing describe, what should I do?
>>> This issue is blocking us in the production environment. Any help to
>>> resolve or workaround is highly appreciated.
>>>
>>> Thanks,
>>> Arun
>>>
>>>
>>> On Wed, Apr 20, 2016 at 12:01 PM, Ankit Singhal <
>>> ankitsingha...@gmail.com> wrote:
>>>
>>>> It's ok if you can just post after grep for CATALOG in a command output
>>>> (scan 'SYSTEM.CATALOG', {RAW=>true}).
>>>>
>>>> On Wed, Apr 20, 2016 at 10:07 PM, Arun Kumaran Sabtharishi <
>>>> arun1...@gmail.com> wrote:
>>>>
>>>>> One more question to add,
>>>>> Do we need to have 1000 versions, and KEEP_DELETED_CELL=true?
>>>>>
>>>>> I have limited the scan in HBase and here is the following data.
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:, timestamp=1460455162842,
>>>>> type=DeleteFamily
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:BASE_COLUMN_COUNT,
>>>>> timestamp=1460354090655, value=\x7F\xFF\xFF\xFF
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:COLUMN_COUNT, timestamp=1460354090655,
>>>>> value=\x80\x00\x00\x05
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:DATA_TABLE_NAME,
>>>>> timestamp=1460354090655, type=DeleteColumn
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:DEFAULT_COLUMN_FAMILY,
>>>>> timestamp=1460354090655, value=default
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:DISABLE_WAL, timestamp=1460354090655,
>>>>> value=\x00
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:IMMUTABLE_ROWS, timestamp=1460354090655,
>>>>> value=\x00
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:INDEX_STATE, timestamp=1460354090655,
>>>>> type=DeleteColumn
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:INDEX_TYPE, timestamp=1460354090655,
>>>>> type=DeleteColumn
>>>>>
>>>>> _0_1460354090089
>>>>>
>>>>> \x00\x00TEST_TABLE_2 column=0:MULTI_TENANT, timestamp=1460354090655,
>>>>> value=\x00
>>>>>
>>>>> _0_1460354090

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Yes Arun, it seems fine to me.
Can you do it for BASE_COLUMN_COUNT too so that the dependent features work
correctly.(Remember use correct INTEGER byte representation for DATA_TYPE
column).

And, can you also please share output of
> scan 'SYSTEM.SEQUENCE'

Regards,
Ankit

On Fri, Apr 22, 2016 at 9:14 PM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> Ankit,
>
> Though the ALTER in pheonix failed, a bunch of puts in HBase  did the job.
> Is it okay to do the following? These puts in HBase intends to do what the
> ALTER command in phoenix supposed to do.
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:DATA_TYPE',"\x80\x00\x00\x10",9
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:IS_VIEW_REFERENCED',"\x00",9
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:NULLABLE',"\x80\x00\x00\x01",9
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:ORDINAL_POSITION',"\x80\x00\x001",9
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:SORT_ORDER',"\x80\x00\x00\x02",9
>
>put 'SYSTEM.CATALOG',
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:_0','',9
>
>
> Thanks,
>
> Arun
>
> On Fri, Apr 22, 2016 at 10:22 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
>> Ankit,
>>
>> When tried executing the query, the following exception is thrown.
>>
>> Error: ERROR 301 (23000): Concurrent modification to table.
>> tableName=SYSTEM.CATALOG (state=23000,code=301)
>> org.apache.phoenix.schema.ConcurrentTableMutationException: ERROR 301
>> (23000): Concurrent modification to table. tableName=SYSTEM.CATALOG
>>
>> at
>> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2339)
>> at
>> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1106)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>> at sqlline.Commands.execute(Commands.java:822)
>> at sqlline.Commands.sql(Commands.java:732)
>> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>> at sqlline.SqlLine.begin(SqlLine.java:681)
>> at sqlline.SqlLine.start(SqlLine.java:398)
>>
>>
>> Thank,
>> Arun
>>
>> On Fri, Apr 22, 2016 at 8:59 AM, Ankit Singhal 
>> wrote:
>>
>>> Arun,
>>> As per the system.catalog ,there seems no way that upgrade code(adding
>>> new columns and view upgrade for version < v4.5) doesn't execute.
>>>
>>> But anyways, let's add the required columns manually by following below
>>> step;
>>>
>>> >  ./sqlline.py localhost;CurrentSCN=9
>>> > ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER,
>>> IS_ROW_TIMESTAMP BOOLEAN;
>>> >!quit
>>>
>>> Quit the shell and start new session without CurrentSCN.
>>> >  ./sqlline.py localhost
>>> > !describe system.catalog
>>>
>>> this should resolve the issue of missing column.
>>>
>>> Regards,
>>> Ankit Singhal
>>>
>>>
>>> On Fri, Apr 22, 2016 at 3:02 AM, Arun Kumaran Sabtharishi <
>>> arun1...@gmail.com> wrote:
>>>
>>>> Ankit,James,
>>>>
>>>> Please find the results you have asked for in the attachment text
>>>> document.
>>>>
>>>> Thanks,
>>>> Arun
>>>>
>>>> On Thu, Apr 21, 2016 at 3:12 AM, Ankit Singhal <
>>>> ankitsingha...@gmail.com> wrote:
>>>>
>>>>> Arun,
>>>>> the observations you had is understandable.
>>>>> So, that's why we again requested the output of
>>>>>
>>>>> 

Re: Extract report from phoenix table

[Ankit Singhal]

Sanooj,
It is not necessary that output can only be written to a table when using
MR, you can have your own custom reducer with appropriate OutputFormat set
in driver.

Similar solutions with phoenix are:-
1. phoenix MR
2. phoenix spark
3. phoenix pig


On Thu, Apr 21, 2016 at 11:06 PM, Sanooj Padmakumar 
wrote:

> Hi
>
> Is there a way using phoenix we can query a huge table to extract some
> information from them ? The "where" condition will have "value" fields and
> might not have any row key fields.
>
> I am looking for something like a phoenix mapreduce (
> https://phoenix.apache.org/phoenix_mr.html) but this always require the
> output to be written to a table.
>
> Any inputs ?
>
> --
> Thanks,
> Sanooj Padmakumar
>

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Hi Arun,

Actually , we missed the quotes as shell will consider everything after ";"
as second command.

bin/sqlline.py "localhost;currentSCN=9"

So, can you fix the timestamp for these columns?
Steps you may follow(but request you to try first on non production cluster
as these are not tested by me).
- Take snapshot of your system.catalog( for backup)
- delete such cells from system.catalog at latest timestamp
- set keep_deleted_cells to false in SYSTEM.CATALOG(remember you should not
have any query running and using schema in point in time snapshots).
- do flush and major_compaction on SYSTEM.CATALOG
- when you don't see those columns and open connection at currentSCN=9 and
alter table to add both the columns.
- you may set keep_deleted_cells back to true in SYSTEM.CATALOG

Regards,
Ankit Singhal



Regards,
Ankit Singhal

On Tue, Apr 26, 2016 at 11:26 PM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> James,
>
> Thanks for the answer. But, why was the currentSCN=9 on connection is not
> working in the first place? Do you have any pointers for us to look into?
>
> Thanks,
> Arun
>
> On Tue, Apr 26, 2016 at 11:12 AM, James Taylor 
> wrote:
>
>> Hi Arun,
>> Yes, this could cause problems, as we base the need to upgrade on the
>> time stamp of the system catalog table.
>> Thanks,
>> James
>>
>>
>> On Tuesday, April 26, 2016, Arun Kumaran Sabtharishi 
>> wrote:
>>
>>> Hi Ankit,
>>>
>>> Just following with the question that when the alter statement was
>>> issued with CurrentSCN=9, the current timestamp was not set to 9.
>>> Will this cause an issue in the future if it has to compare the
>>> timestamps?
>>>
>>> Thanks,
>>> Arun
>>>
>>> On Mon, Apr 25, 2016 at 10:32 AM, Arun Kumaran Sabtharishi <
>>> arun1...@gmail.com> wrote:
>>>
>>>> Hi Ankit,
>>>>
>>>> Even though the ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT
>>>> INTEGER, IS_ROW_TIMESTAMP BOOLEAN; statement threw an exception, it
>>>> actually added the columns to the SYSTEM.CATALOG.
>>>> But, the ./sqlline.py localhost;CurrentSCN=9 did not work. The current
>>>> timestamp was not set to 9. Will this cause an issue in the future if it
>>>> has to compare the timestamps?
>>>>
>>>> After the alter statement was executed and when queried from HBase, the
>>>> following is the output.
>>>>
>>>> hbase(main):004:0> get
>>>> 'SYSTEM.CATALOG',"\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000"
>>>> COLUMN   CELL
>>>>  0 ATA_TYPE timestamp=1461362240655,
>>>> value=\x80\x00\x00\x10
>>>>  0:IS_VIEW_REFERENCEDtimestamp=1461362240655, value=\x00
>>>>  0:NULLABLE  timestamp=1461362240655,
>>>> value=\x80\x00\x00\x01
>>>>  0 RDINAL_POSITION  timestamp=1461362240655,
>>>> value=\x80\x00\x004
>>>>  0 ORT_ORDERtimestamp=1461362240655,
>>>> value=\x80\x00\x00\x02
>>>>
>>>>
>>>> Thanks,
>>>> Arun
>>>>
>>>>
>>>> On Mon, Apr 25, 2016 at 2:19 AM, Ankit Singhal <
>>>> ankitsingha...@gmail.com> wrote:
>>>>
>>>>> Yes Arun, it seems fine to me.
>>>>> Can you do it for BASE_COLUMN_COUNT too so that the dependent features
>>>>> work correctly.(Remember use correct INTEGER byte representation for
>>>>> DATA_TYPE column).
>>>>>
>>>>> And, can you also please share output of
>>>>> > scan 'SYSTEM.SEQUENCE'
>>>>>
>>>>> Regards,
>>>>> Ankit
>>>>>
>>>>> On Fri, Apr 22, 2016 at 9:14 PM, Arun Kumaran Sabtharishi <
>>>>> arun1...@gmail.com> wrote:
>>>>>
>>>>>> Ankit,
>>>>>>
>>>>>> Though the ALTER in pheonix failed, a bunch of puts in HBase  did the
>>>>>> job.
>>>>>> Is it okay to do the following? These puts in HBase intends to do
>>>>>> what the ALTER command in phoenix supposed to do.
>>>>>>
>>>>>>put 'SYSTEM.CATALOG',
>>>>>> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x000",'0:DATA_TYPE',"\x80\x00\x00\x10",9
>>>>>>
>>>>>&

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Do a raw scan on SYSTEM.CATALOG through hbase shell and confirm that there
should not be any cell for SYSTEM.CATALOG row having timestamp greater than
9.

On Sat, Apr 30, 2016 at 3:28 AM, Arun Kumaran Sabtharishi <
arun1...@gmail.com> wrote:

> Ankit,
>
> Follwing the steps, the below exception is thrown. Can you let me know
> what could have gone wrong?
>
> ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER, IS_ROW_TIMESTAMP
> BOOLEAN;
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1106)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
>
> Thanks,
> Arun
>
> On Fri, Apr 29, 2016 at 4:47 AM, Ankit Singhal 
> wrote:
>
>> Hi Arun,
>>
>> Actually , we missed the quotes as shell will consider everything after
>> ";" as second command.
>>
>> bin/sqlline.py "localhost;currentSCN=9"
>>
>> So, can you fix the timestamp for these columns?
>> Steps you may follow(but request you to try first on non production
>> cluster as these are not tested by me).
>> - Take snapshot of your system.catalog( for backup)
>> - delete such cells from system.catalog at latest timestamp
>> - set keep_deleted_cells to false in SYSTEM.CATALOG(remember you should
>> not have any query running and using schema in point in time snapshots).
>> - do flush and major_compaction on SYSTEM.CATALOG
>> - when you don't see those columns and open connection at currentSCN=9
>> and alter table to add both the columns.
>> - you may set keep_deleted_cells back to true in SYSTEM.CATALOG
>>
>> Regards,
>> Ankit Singhal
>>
>>
>>
>> Regards,
>> Ankit Singhal
>>
>> On Tue, Apr 26, 2016 at 11:26 PM, Arun Kumaran Sabtharishi <
>> arun1...@gmail.com> wrote:
>>
>>> James,
>>>
>>> Thanks for the answer. But, why was the currentSCN=9 on connection is
>>> not working in the first place? Do you have any pointers for us to look
>>> into?
>>>
>>> Thanks,
>>> Arun
>>>
>>> On Tue, Apr 26, 2016 at 11:12 AM, James Taylor 
>>> wrote:
>>>
>>>> Hi Arun,
>>>> Yes, this could cause problems, as we base the need to upgrade on the
>>>> time stamp of the system catalog table.
>>>> Thanks,
>>>> James
>>>>
>>>>
>>>> On Tuesday, April 26, 2016, Arun Kumaran Sabtharishi <
>>>> arun1...@gmail.com> wrote:
>>>>
>>>>> Hi Ankit,
>>>>>
>>>>> Just following with the question that when the alter statement was
>>>>> issued with CurrentSCN=9, the current timestamp was not set to 9.
>>>>> Will this cause an issue in the future if it has to compare the
>>>>> timestamps?
>>>>>
>>>>> Thanks,
>>>>> Arun
>>>>>
>>>>> On Mon, Apr 25, 2016 at 10:32 AM, Arun Kumaran Sabtharishi <
>>>>> arun1...@gmail.com> wrote:
>>>>>
>>>>>> Hi Ankit,
>>>>>>
>>>>>> Even though the ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT
>>>>>> INTEGER, IS_ROW_TIMESTAMP BOOLEAN; statement threw an exception, it
>>>>>> actually added the columns to the SYSTEM.CATALOG.
>>>>>> But, the ./sqlline.py localhost;CurrentSCN=9 did not work. The
>>>>>> current timestamp was not set to 9. Will this cause an issue in the 
>>>>>> future
>>>&g

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

As you are also not seeing any cell having timestamp greater than 9 (in scan
'SYSTEM.CATALOG',{RAW => true, STARTROW => "\x00SYSTEM\x00CATALOG\x00", STOPROW
=> "\x00SYSTEM\x00CATALOG\x01"}) then I think restarting your hbase cluster
will help as it will bounce the stale cache.
Nothing is required to be deleted from phoenix side.

On Sat, Apr 30, 2016 at 9:04 PM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Hi Ankit,
>
>  We have removed all cells for these two columns and scan with raw =>
> true does not return any results:
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT_\x00"}
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP_\x00"}
>
> We still see the same error.
>
>
>
> Do we need to explicitly delete from phoenix as well?
>
>
>
> Thanks,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Saturday, April 30, 2016 3:00 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Do a raw scan on SYSTEM.CATALOG through hbase shell and confirm that there
> should not be any cell for SYSTEM.CATALOG row having timestamp greater than
> 9.
>
>
>
> On Sat, Apr 30, 2016 at 3:28 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
> Ankit,
>
>
>
> Follwing the steps, the below exception is thrown. Can you let me know
> what could have gone wrong?
>
>
>
> ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER, IS_ROW_TIMESTAMP
> BOOLEAN;
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1106)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>
> at sqlline.Commands.execute(Commands.java:822)
>
> at sqlline.Commands.sql(Commands.java:732)
>
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>
> at sqlline.SqlLine.begin(SqlLine.java:681)
>
> at sqlline.SqlLine.start(SqlLine.java:398)
>
> at sqlline.SqlLine.main(SqlLine.java:292)
>
>
>
> Thanks,
>
> Arun
>
>
>
> On Fri, Apr 29, 2016 at 4:47 AM, Ankit Singhal 
> wrote:
>
> Hi Arun,
>
>
>
> Actually , we missed the quotes as shell will consider everything after
> ";" as second command.
>
>
>
> bin/sqlline.py "localhost;currentSCN=9"
>
>
>
> So, can you fix the timestamp for these columns?
>
> Steps you may follow(but request you to try first on non production
> cluster as these are not tested by me).
>
> - Take snapshot of your system.catalog( for backup)
>
> - delete such cells from system.catalog at latest timestamp
>
> - set keep_deleted_cells to false in SYSTEM.CATALOG(remember you should
> not have any query running and using schema in point in time snapshots).
>
> - do flush and major_compaction on SYSTEM.CATALOG
>
> - when you don't see those columns and open connection at currentSCN=9 and
> alter table to add both the columns.
>
> - you may set keep_deleted_cells back to true in SYSTEM.CATALOG
>
>
>
> Regards,
>
> Ankit Singhal
>
>
>
>
>
>
>
> Regards,
>
> Ankit Singhal
>
>
>
> On Tue, Apr 26, 2016 at 11:26 PM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
> James,
>
>
>
> Thanks for the answer. But, why was the currentSCN=9 on connection is not
> working in the first place? Do you have any pointers for us to look into?
>
>
>
> Thanks,
>
> Arun
>
>
>
> On Tue, Apr 26, 2016 at 11:12 AM, James Taylor 
> wrote:
&g

Re: java.io.EOFException on phoenix table

[Ankit Singhal]

It seems your guidePosts collected got corrupted somehow. You may have
tried deleting guidePosts for that physical table from SYSTEM.STATS table.

On Sun, May 1, 2016 at 10:20 AM, Michal Medvecky  wrote:

> If anyone experiences the same problem (hello, google!), here is my
> workaround:
>
> - create table ANOTHER_TABLE
> - hbase org.apache.hadoop.hbase.mapreduce.CopyTable --new.name=ANOTHER_TABLE
> SEARCH_RESULT
>
> But I still have no idea what has happened to the original table.
>
> Michal
>

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Explicit "put" to add column is not recommended as it may affect you down
the line when you start using features which expects some column attribute
and are missed because of manual error.
It could be just a solution for immediate production recovery.


On Sun, May 1, 2016 at 11:07 AM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Ankit,
>
> We will try restarting HBase cluster. Adding explicit “put” in HBase with
> timestamp as 9 for these two columns has any side effects?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:35 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> As you are also not seeing any cell having timestamp greater than 9 (in scan
> 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"})
> then I think restarting your hbase cluster will help as it will bounce the
> stale cache.
>
> Nothing is required to be deleted from phoenix side.
>
>
>
> On Sat, Apr 30, 2016 at 9:04 PM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Hi Ankit,
>
>  We have removed all cells for these two columns and scan with raw =>
> true does not return any results:
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT_\x00"}
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP_\x00"}
>
> We still see the same error.
>
>
>
> Do we need to explicitly delete from phoenix as well?
>
>
>
> Thanks,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Saturday, April 30, 2016 3:00 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Do a raw scan on SYSTEM.CATALOG through hbase shell and confirm that there
> should not be any cell for SYSTEM.CATALOG row having timestamp greater than
> 9.
>
>
>
> On Sat, Apr 30, 2016 at 3:28 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
> Ankit,
>
>
>
> Follwing the steps, the below exception is thrown. Can you let me know
> what could have gone wrong?
>
>
>
> ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER, IS_ROW_TIMESTAMP
> BOOLEAN;
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1106)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>
> at sqlline.Commands.execute(Commands.java:822)
>
> at sqlline.Commands.sql(Commands.java:732)
>
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>
> at sqlline.SqlLine.begin(SqlLine.java:681)
>
> at sqlline.SqlLine.start(SqlLine.java:398)
>
> at sqlline.SqlLine.main(SqlLine.java:292)
>
>
>
> Thanks,
>
> Arun
>
>
>
> On Fri, Apr 29, 2016 at 4:47 AM, Ankit Singhal 
> wrote:
>
> Hi Arun,
>
>
>
> Actually , we missed the quotes as shell will consider everything after
> ";" as second command.
>
>
>
> bin/sqlline.py "localhost;currentSCN=9"
>
>
>
> So, can you fix the timestamp for these columns?
>
> Steps you may follow(but request you to try first on non production
> cluster as these are not tested by me).
>
> - Take snapshot of your system.catalog( for backup)
>
> - delete such cells from system.catalog at latest timestamp
>
> - set keep_deleted_cells to false in SYSTEM.CATALOG(remember you should
> not have any query run

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

It's weird that the exception is still there.
is it possible for you to share following outputs.

1. scan 'SYSTEM.CATALOG',{RAW => true, STARTROW => "\x00SYSTEM\x00CATALOG\
x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"}
2. scan 'SYSTEM.SEQUENCE',{RAW => true}
3. from phoenix , select * from system.stats
where PHYSICAL_NAME='SYSTEM.CATALOG';


On Sun, May 1, 2016 at 11:19 AM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Makes sense.
>
> Restarting HBase cluster did not seem to fix the issue. Still getting same
> error for Alter command:
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
>
>
> Any other suggestions?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:44 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Explicit "put" to add column is not recommended as it may affect you down
> the line when you start using features which expects some column attribute
> and are missed because of manual error.
>
> It could be just a solution for immediate production recovery.
>
>
>
>
>
> On Sun, May 1, 2016 at 11:07 AM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Ankit,
>
> We will try restarting HBase cluster. Adding explicit “put” in HBase with
> timestamp as 9 for these two columns has any side effects?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:35 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> As you are also not seeing any cell having timestamp greater than 9 (in scan
> 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"})
> then I think restarting your hbase cluster will help as it will bounce the
> stale cache.
>
> Nothing is required to be deleted from phoenix side.
>
>
>
> On Sat, Apr 30, 2016 at 9:04 PM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Hi Ankit,
>
>  We have removed all cells for these two columns and scan with raw =>
> true does not return any results:
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT_\x00"}
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP_\x00"}
>
> We still see the same error.
>
>
>
> Do we need to explicitly delete from phoenix as well?
>
>
>
> Thanks,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Saturday, April 30, 2016 3:00 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Do a raw scan on SYSTEM.CATALOG through hbase shell and confirm that there
> should not be any cell for SYSTEM.CATALOG row having timestamp greater than
> 9.
>
>
>
> On Sat, Apr 30, 2016 at 3:28 AM, Arun Kumaran Sabtharishi <
> arun1...@gmail.com> wrote:
>
> Ankit,
>
>
>
> Follwing the steps, the below exception is thrown. Can you let me know
> what could have gone wrong?
>
>
>
> ALTER TABLE SYSTEM.CATALOG ADD BASE_COLUMN_COUNT INTEGER, IS_ROW_TIMESTAMP
> BOOLEAN;
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1106)
>
> at
> org.apache.phoenix.jdbc.PhoenixSt

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

You have guidePosts collected for SYSTEM.CATALOG table which is actually
causing the problem. It is possible only if your guidePosts width was set
anytime low or you have very big system.catalog table.

can you please delete guideposts from system.catalog table(delete from
SYSTEM.STATS where physical_name='SYSTEM.CATALOG') and try adding a column.

On Sun, May 1, 2016 at 9:02 PM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Hi Ankit,
>
> Please find attached results for steps 1, and 3.
>
> SYSTEM.SEQUENCE is returning results for all the tables. Are you looking
> for any specific data? so that I can filter and send the results?
>
>
>
> Thank you for your time looking into this,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 1:01 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> It's weird that the exception is still there.
>
> is it possible for you to share following outputs.
>
>
>
> 1. scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"}
>
> 2. scan 'SYSTEM.SEQUENCE',{RAW => true}
>
> 3. from phoenix , select * from system.stats
> where PHYSICAL_NAME='SYSTEM.CATALOG';
>
>
>
>
>
> On Sun, May 1, 2016 at 11:19 AM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Makes sense.
>
> Restarting HBase cluster did not seem to fix the issue. Still getting same
> error for Alter command:
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
>
>
> Any other suggestions?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:44 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Explicit "put" to add column is not recommended as it may affect you down
> the line when you start using features which expects some column attribute
> and are missed because of manual error.
>
> It could be just a solution for immediate production recovery.
>
>
>
>
>
> On Sun, May 1, 2016 at 11:07 AM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Ankit,
>
> We will try restarting HBase cluster. Adding explicit “put” in HBase with
> timestamp as 9 for these two columns has any side effects?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:35 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> As you are also not seeing any cell having timestamp greater than 9 (in scan
> 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"})
> then I think restarting your hbase cluster will help as it will bounce the
> stale cache.
>
> Nothing is required to be deleted from phoenix side.
>
>
>
> On Sat, Apr 30, 2016 at 9:04 PM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Hi Ankit,
>
>  We have removed all cells for these two columns and scan with raw =>
> true does not return any results:
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00BASE_COLUMN_COUNT_\x00"}
>
> scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP\x00", STOPROW =>
> "\x00SYSTEM\x00CATALOG\x00IS_ROW_TIMESTAMP_\x00"}
>
> We still see the same error.
>
>
>
> Do we need to explicitly delete from phoenix as well?
>
>
>
> Thanks,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Saturday, April 30, 2016 3:00 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Do a raw scan on SYSTEM.CATALOG through hbase shell and confirm th

Re: Undefined column. columnName=IS_ROW_TIMESTAMP

[Ankit Singhal]

Have you restarted the cluster to refresh the cache? If the rows got appear
again in system.stats then delete the rows again, restart the cluster and
try altering the table.
If this doesn't solves the problem, I think , you should try attaching
debugger and share your findings.


On Mon, May 2, 2016 at 6:20 AM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Ankit,
>
> I deleted SYSTEM.CATALOG entry from SYSTEM.STATS, but still getting same
> error for Alter statement.
>
> I have also tried these below steps:
>
> -Enable KEEP_DELETED_CELLS to false for SYSTEM.STATS
>
> -flush ‘SYSTEM.STATS’
>
> -major_compact ‘SYSTEM.STATS’
>
>
>
> Still I do see one row from hbase raw scan. Please find attached the
> results of this scan:
>
> scan 'SYSTEM.STATS',{ RAW => true, STARTROW => "SYSTEM.CATALOG\x000",
> STOPROW => "SYSTEM.CATALOG\x001"}
>
> Could this row be causing the issue?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 1:36 PM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> You have guidePosts collected for SYSTEM.CATALOG table which is actually
> causing the problem. It is possible only if your guidePosts width was set
> anytime low or you have very big system.catalog table.
>
>
>
> can you please delete guideposts from system.catalog table(delete from
> SYSTEM.STATS where physical_name='SYSTEM.CATALOG') and try adding a column.
>
>
>
> On Sun, May 1, 2016 at 9:02 PM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Hi Ankit,
>
> Please find attached results for steps 1, and 3.
>
> SYSTEM.SEQUENCE is returning results for all the tables. Are you looking
> for any specific data? so that I can filter and send the results?
>
>
>
> Thank you for your time looking into this,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 1:01 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> It's weird that the exception is still there.
>
> is it possible for you to share following outputs.
>
>
>
> 1. scan 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00", STOPROW => "\x00SYSTEM\x00CATALOG\x01"}
>
> 2. scan 'SYSTEM.SEQUENCE',{RAW => true}
>
> 3. from phoenix , select * from system.stats
> where PHYSICAL_NAME='SYSTEM.CATALOG';
>
>
>
>
>
> On Sun, May 1, 2016 at 11:19 AM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Makes sense.
>
> Restarting HBase cluster did not seem to fix the issue. Still getting same
> error for Alter command:
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=SYSTEM.CATALOG
> (state=42M04,code=1013)
>
> org.apache.phoenix.schema.NewerTableAlreadyExistsException: ERROR 1013
> (42M04): Table already exists. tableName=SYSTEM.CATALOG
>
> at
> org.apache.phoenix.schema.MetaDataClient.processMutationResult(MetaDataClient.java:2345)
>
> at
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:2641)
>
>
>
> Any other suggestions?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:44 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> Explicit "put" to add column is not recommended as it may affect you down
> the line when you start using features which expects some column attribute
> and are missed because of manual error.
>
> It could be just a solution for immediate production recovery.
>
>
>
>
>
> On Sun, May 1, 2016 at 11:07 AM, Bavineni, Bharata <
> bharata.bavin...@epsilon.com> wrote:
>
> Ankit,
>
> We will try restarting HBase cluster. Adding explicit “put” in HBase with
> timestamp as 9 for these two columns has any side effects?
>
>
>
> Thank you,
>
> Bharathi.
>
>
>
> *From:* Ankit Singhal [mailto:ankitsingha...@gmail.com]
> *Sent:* Sunday, May 01, 2016 12:35 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Undefined column. columnName=IS_ROW_TIMESTAMP
>
>
>
> As you are also not seeing any cell having timestamp greater than 9 (in scan
> 'SYSTEM.CATALOG',{RAW => true, STARTROW =>
> "\x00SYSTEM\x00CATALOG\x00&q

Re: [while doing select] getting exception - ERROR 1108 (XCL08): Cache of region boundaries are out of date.

[Ankit Singhal]

Yes Vishnu , you may be hitting
https://issues.apache.org/jira/browse/PHOENIX-2249 so you can try deleting
stats for the table "*EVENTS_PROD*'.

On Mon, May 9, 2016 at 10:56 AM, vishnu rao  wrote:

> hi guys need help !
>
> i was getting this exception while doing a select. hbase 1.1 with phoenix
> 4.6.
>
> * SELECT * FROM EVENTS_PROD WHERE id = 100  AND  ts IS NOT NULL AND ts
> BETWEEN 146249280 AND 146253600  ORDER BY ts DESC;*
>
> *--*
>
> *7 days retention*
>
> *150 regions*
>
> *got this error suddenly - no code changes.*
>
> *one solution is to truncate SYSTEM_STATS  ? should i try it*
>
> *--*
>
> *java.lang.RuntimeException:
> org.apache.phoenix.schema.StaleRegionBoundaryCacheException: ERROR 1108
> (XCL08): Cache of region boundaries are out of date.*
>
> *at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)*
>
> *at
> sqlline.SeparatedValuesOutputFormat.print(SeparatedValuesOutputFormat.java:31)*
>
> *at sqlline.SqlLine.print(SqlLine.java:1653)*
>
> *at sqlline.Commands.execute(Commands.java:833)*
>
> *at sqlline.Commands.sql(Commands.java:732)*
>
> *at sqlline.SqlLine.dispatch(SqlLine.java:808)*
>
> *at sqlline.SqlLine.begin(SqlLine.java:681)*
>
> *at sqlline.SqlLine.start(SqlLine.java:398)*
>
> *at sqlline.SqlLine.main(SqlLine.java:292)*
>
> --
> with regards,
> ch Vishnu
> mash213.wordpress.com
> doodle-vishnu.blogspot.in
>

Re: SYSTEM.CATALOG table - VERSIONS attribute

[Ankit Singhal]

Yes, you can but it depends if you don't want to go back in time for schema
before 5 versions.

On Mon, May 9, 2016 at 8:16 AM, Bavineni, Bharata <
bharata.bavin...@epsilon.com> wrote:

> Hi,
>
> SYSTEM.CATALOG table is created with VERSIONS => '1000' by default. Can we
> change this value to 5 or 10? Does this cause any side effects?
>
>
>
> Thank you,
>
> Bharathi.
>
> --
>
> This e-mail and files transmitted with it are confidential, and are
> intended solely for the use of the individual or entity to whom this e-mail
> is addressed. If you are not the intended recipient, or the employee or
> agent responsible to deliver it to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you are not one of the named
> recipient(s) or otherwise have reason to believe that you received this
> message in error, please immediately notify sender by e-mail, and destroy
> the original message. Thank You.
>

Re: [Phoenix 4.4]Rename table Supported ?

[Ankit Singhal]

Hi Divya,

Currently there is no sql construct but you can do it by following below
steps.(It is highly recommended you try these steps in dev environment
before proceeding to production.

1. Take snapshot of the original table from hbase shell and restore it with
another table name.

hbase> disable 'oldtablename'

hbase> snapshot 'oldtablename', 'oldtablename_Snapshot'

hbase> clone_snapshot 'oldtablename_Snapshot', 'newTableName'

2. Open phoenix connection at timestamp 1 day less than the oldest data in
your tables ( by specifying ts in CurrentSCN ./sqlline.py
"localhost;CurrentSCN=") and create table with the exact DDL used for
old table but with the table name changed to new table.

3. confirm that your new table is working fine as expected .
4. Then drop the old table from phoenix and snapshot from hbase shell.

hbase> delete_snapshot 'oldtablename_Snapshot'


Regards,
Ankit Singhal

On Mon, May 9, 2016 at 8:08 AM, Divya Gehlot 
wrote:

> Hi,
> Does Phoenix Support renaming of table ?
> Is yes, Please help me with the syntax.
>
>
>
>
> Thanks,
> Divya
>

Re: [Spark 1.5.2]Check Foreign Key constraint

[Ankit Singhal]

You can use Joins as a substitute to subqueries.

On Wed, May 11, 2016 at 1:27 PM, Divya Gehlot 
wrote:

> Hi,
> I am using Spark 1.5.2  with Apache Phoenix 4.4
> As Spark 1.5.2 doesn't support subquery in where conditions .
> https://issues.apache.org/jira/browse/SPARK-4226
>
> Is there any alternative way to find foreign key constraints.
> Would really appreciate the help.
>
>
>
> Thanks,
> Divya
>

Re: Global Index stuck in BUILDING state

[Ankit Singhal]

Try recreating your index with ASYNC and update index using INDEX tool so
that you don't face issues related to timeout or stuck during the initial
load of huge data.
https://phoenix.apache.org/secondary_indexing.html


On Tue, May 10, 2016 at 7:26 AM, anupama agarwal  wrote:

> Hi All,
>
> I have a query, I created global index on table of 10,000,000 records. My
> global index's state is stuck at "BUILDING". I have tried running this
> query :
>
> *alter index accrual_client_ref_id_type_idx on
> apl_accrual_service.accruals USABLE; *
> which says the same.
>
> The  global index has 10k rows less than main table.  What are my options
> to fix this? Do I have to rebuild the index to fix this?
>

Re: phoenix : timeouts for long queries

[Ankit Singhal]

You can try increasing phoenix.query.timeoutMs (and
hbase.client.scanner.timeout.period) on the client .
https://phoenix.apache.org/tuning.html


On Fri, May 13, 2016 at 1:51 PM, 景涛 <844300...@qq.com> wrote:

> When I query from a very big table
> It get errors as follow:
>
> java.lang.RuntimeException:
> org.apache.phoenix.exception.PhoenixIOException:
> org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36,
> exceptions:
>
> Fri May 13 16:15:29 CST 2016, null, java.net.SocketTimeoutException:
> callTimeout=6, callDuration=60307: row '' on table 'IC_WHOLESALE_PRICE'
> at
> region=IC_WHOLESALE_PRICE,,1463121082822.e7e7cbd63c1831df75aee8842df5c7f6.,
> hostname=hadoop09,60020,1463119404385, seqNum=2
>
> at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
> at sqlline.TableOutputFormat.print(TableOutputFormat.java:33)
> at sqlline.SqlLine.print(SqlLine.java:1653)
> at sqlline.Commands.execute(Commands.java:833)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
>
>
> Is this a client-side timeout, or do I need to change something
> HBase-related on the server and restart the cluster? On master, or all
> region servers?
>

Re: Dropping of Index can still leave some non-replayed writes Phoenix-2915

[Ankit Singhal]

Hi Anupama,

Option 1:-
You can create a ASYNC index so that WAL can be replayed. And once your
regions are up , remember to do the flush of data table before dropping the
index.

Option 2:-
Create a table in hbase with the same name as index table name by using
hbase shell.

Regards,
Ankit Singhal


On Tue, Jun 14, 2016 at 11:19 PM, anupama agarwal  wrote:

> Hi All,
>
> I have hit this error in phoenix, Phoenix-2915. It could be possible ,
> that there are some index writes in WAL which are not replayed and the
> index is dropped.
>
> And, now the table is not there, these writes cannot be replayed which
> result in data table regions also to not come up. My data region is in
> FAILED_TO_OPEN state. I have tried recreating the index, and still region
> is not able to come up. I realise that this has been fixed in new version
> of phoenix, but I am currently on phoenix 4.6 and Hbase 1.0. Can you please
> suggest a solution?
>

Re: Dropping of Index can still leave some non-replayed writes Phoenix-2915

[Ankit Singhal]

Yes, restart your cluster

On Wed, Jun 15, 2016 at 8:17 AM, anupama agarwal  wrote:

> I have created async index with same name. But I am still getting the same
> error. Should I restart my cluster for changes to reflect?
> On Jun 15, 2016 8:38 PM, "Ankit Singhal"  wrote:
>
>> Hi Anupama,
>>
>> Option 1:-
>> You can create a ASYNC index so that WAL can be replayed. And once your
>> regions are up , remember to do the flush of data table before dropping the
>> index.
>>
>> Option 2:-
>> Create a table in hbase with the same name as index table name by using
>> hbase shell.
>>
>> Regards,
>> Ankit Singhal
>>
>>
>> On Tue, Jun 14, 2016 at 11:19 PM, anupama agarwal 
>> wrote:
>>
>>> Hi All,
>>>
>>> I have hit this error in phoenix, Phoenix-2915. It could be possible ,
>>> that there are some index writes in WAL which are not replayed and the
>>> index is dropped.
>>>
>>> And, now the table is not there, these writes cannot be replayed which
>>> result in data table regions also to not come up. My data region is in
>>> FAILED_TO_OPEN state. I have tried recreating the index, and still region
>>> is not able to come up. I realise that this has been fixed in new version
>>> of phoenix, but I am currently on phoenix 4.6 and Hbase 1.0. Can you please
>>> suggest a solution?
>>>
>>
>>

Re: Bulk loading and index

[Ankit Singhal]

HI Tongzhou,

May be you can trying dropping the current index and after your upload is
completed, you can create a async index. Then you can use IndexTool to
rebuild your index from start.

source:- https://phoenix.apache.org/secondary_indexing.html

CREATE INDEX async_index ON my_schema.my_table (v) ASYNC


But if you are only using CSVBulkLoadTool for bulk load, then it will
automatically prepare and bulk load index data also. So Index maintaining
would not be required.

Regards,
Ankit Singhal

On Sat, Jun 25, 2016 at 4:13 PM, Tongzhou Wang (Simon) <
tongzhou.wang.1...@gmail.com> wrote:

> Hi Josh,
>
> First, thanks for the response.
>
> As far as I can tell, a disabled index cannot be directly changed to
> USABLE. It must be rebuilt first. I am aware that I can do ALTER INDEX 
> REBUILD. But, if I understand correctly, this is single thread and slow.
> I'm wondering if I can use the IndexTool map reduce job in this case.
>
> About TTL, I did some experiments. Turns out that Phoenix do not
> automatically remove index entry when the table entry dies from TTL
> setting. However, it is possible to set index table with same TTL so that
> index can be in sync.
>
> Best,
> Tongzhou
>
> > On Jun 25, 2016, at 15:31, Josh Elser  wrote:
> >
> > Hi Tongzhou,
> >
> > Maybe you can try `ALTER INDEX index ON table DISABLE`. And then the
> same command with USABLE after you update the index. Are you attempting to
> do this incrementally? Like, a bulk load of data then a bulk load of index
> data, repeat?
> >
> > Regarding the TTL, I assume so, but I'm not certain.
> >
> > Tongzhou Wang wrote:
> >> Hi all,
> >>
> >> I am writing to ask if there is a way to disable an index, then update
> >> it through the MapReduce job (IndexTool). I want to bulk load a huge
> >> amount of data, but index maintaining makes it very slow. It would be
> >> great if I can disable an index, load data, then use a MapReduce job to
> >> update it to usable state.
> >>
> >> Also, does Phoenix's secondary index maintaining take TTL into account?
> >>
> >> Thanks,
> >> Tongzhou
>

Re: PhoenixFunction

[Ankit Singhal]

Hi Yang,

Actually , I use org.apache.phoenix.expression.ExpressiontType of the
version I'm using to check the build in function available in that version.

I know this is not a good way.

For example , following built in functions will not be available in 4.4 as
they are added in later releases.(Name without Function suffix is usually
the function name but not guaranteed ,to confirm check NAME in Function
class).

*ArrayPrepend*Function(ArrayPrependFunction.class),

*Sqrt*Function(SqrtFunction.class),

*Abs*Function(AbsFunction.class),

*Cbrt*Function(CbrtFunction.class),

LnFunction(LnFunction.class),

LogFunction(LogFunction.class),

ExpFunction(ExpFunction.class),

PowerFunction(PowerFunction.class),

ArrayConcatFunction(ArrayConcatFunction.class),

ArrayFillFunction(ArrayFillFunction.class),

ArrayToStringFunction(ArrayToStringFunction.class),

StringToArrayFunction(StringToArrayFunction.class),

GetByteFunction(GetByteFunction.class),

SetByteFunction(SetByteFunction.class),

GetBitFunction(GetBitFunction.class),

SetBitFunction(SetBitFunction.class),

OctetLengthFunction(OctetLengthFunction.class),

RoundWeekExpression(RoundWeekExpression.class),

RoundMonthExpression(RoundMonthExpression.class),

RoundYearExpression(RoundYearExpression.class),

FloorWeekExpression(FloorWeekExpression.class),

FloorMonthExpression(FloorMonthExpression.class),

FloorYearExpression(FloorYearExpression.class),

CeilWeekExpression(CeilWeekExpression.class),

CeilMonthExpression(CeilMonthExpression.class),

CeilYearExpression(CeilYearExpression.class);

Regards,
Ankit Singhal



On Wed, Jun 29, 2016 at 9:08 AM, Yang Zhang  wrote:

> when I use the functions described on your website.
> some function work well such as count()  and min(),
> But with exp() it returned with Function undefined. functionName=[EXP] not
> found
>
> my phoenix version is phoenix-4.4.0-HBase-0.98-bin.
> I just want to confirm whether my version not support some function,
> and where can i find the functions my version support?
>
> Thanks very much.
> Zhang Yang
>

Re: For multiple local indexes on Phoenix table only one local index table is being created in HBase

[Ankit Singhal]

Hi Vamsi,

Phoenix uses single local Index table for all the local indexes created on
a particular data table.
Rows are differentiated by local index sequence id and filtered when
requested during the query for particular index.

Regards,
Ankit Singhal

Re

On Tue, Jun 28, 2016 at 4:18 AM, Vamsi Krishna 
wrote:

> Team,
>
> I'm using HDP 2.3.2 (HBase : 1.1.2, Phoenix : 4.4.0).
> *Question:* For multiple local indexes on Phoenix table only one local
> index table is being created in HBase. Is this regular behavior? Can
> someone explain why?
>
> Phoenix:
> CREATE TABLE IF NOT EXISTS VAMSI.TABLE_B (COL1 VARCHAR(36) , COL2
> VARCHAR(36) , COL3 VARCHAR(36) CONSTRAINT TABLE_B_PK PRIMARY KEY (COL1))
> COMPRESSION='SNAPPY', SALT_BUCKETS=5;
> CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL2 ON VAMSI.TABLE_A (COL2);
> CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL3 ON VAMSI.TABLE_A (COL3);
>
> hbase(main):012:0> list '_LOCAL.*'
> TABLE
> _LOCAL_IDX_VAMSI.TABLE_A
>
> Thanks,
> Vamsi Attluri
> --
> Vamsi Attluri
>

Re: phoenix explain plan not showing any difference after adding a local index on the table column that is used in query filter

[Ankit Singhal]

Hi Vamsi,

*Explain plan:*
explain select * from vamsi.table_a where col2 = 'abc';
+-+
|PLAN |
+-+
| CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
| SERVER FILTER BY COL2 = 'abc'   |
+-+

You are selecting all the columns of the table, which are not part of local
index (and are also not covered) so instead of scanning index and join back
to data table to get all the columns is costly operation. so optimizer
chooses data table to scan instead of using index to serve query.

below query should use local indexes:-
explain select col2, any_covered_colums from vamsi.table_a where col2 =
'abc';

For covered indexes , you can read
https://phoenix.apache.org/secondary_indexing.html

Regards,
Ankit Singhal




On Tue, Jun 28, 2016 at 4:25 AM, Vamsi Krishna 
wrote:

> Team,
>
> I'm using HDP 2.3.2 (HBase : 1.1.2, Phoenix : 4.4.0).
> *Question: *phoenix explain plan not showing any difference after adding
> a local index on the table column that is used in query filter. Can someone
> please explain why?
>
> *Create table:*
> CREATE TABLE IF NOT EXISTS VAMSI.TABLE_A (COL1 VARCHAR(36) , COL2
> VARCHAR(36) , COL3 VARCHAR(36) CONSTRAINT TABLE_A_PK PRIMARY KEY (COL1))
> COMPRESSION='SNAPPY', SALT_BUCKETS=5;
> *Insert data:*
> upsert into vamsi.table_a values ('abc123','abc','123');
> upsert into vamsi.table_a values ('def456','def','456');
>
> *Explain plan:*
> explain select * from vamsi.table_a where col2 = 'abc';
> +-+
> |PLAN |
> +-+
> | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
> | SERVER FILTER BY COL2 = 'abc'   |
> +-+
>
> *Create local index:*
> CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL2 ON VAMSI.TABLE_A (COL2);
>
> *Explain plan:*
> explain select * from vamsi.table_a where col2 = 'abc';
> +-+
> |PLAN |
> +-+
> | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A  |
> | SERVER FILTER BY COL2 = 'abc'   |
> +-+
>
> Thanks,
> Vamsi Attluri
>
> --
> Vamsi Attluri
>

Re: Errors while launching sqlline

[Ankit Singhal]

Hi Vasanth,

RC for 4.8(with support of hbase-1.2) is just out today, you can try with
the latest build.

Regards,
Ankit Singhal

On Thu, Jul 14, 2016 at 10:06 AM, Vasanth Bhat  wrote:

> Thanks James.
>
>  When are the early builds going to be available for  Phoenix
> 4.8.0?
> also are the two errors below related to  hbase version compatibility?
> There  are also seems to be some java class loader related issue?
>
> How do I enable debug to get more information?
>
> Thanks
> Vasanth
>
>
> On Sun, Jul 3, 2016 at 12:05 PM, James Taylor 
> wrote:
>
> > Phoenix 4.7.0 does not support HBase 1.2.1, but Phoenix 4.8.0 will. We're
> > very close to getting an RC up for this, so stay tuned.
> >
> > Thanks,
> > James
> >
> > On Sun, Jul 3, 2016 at 7:36 AM, Vasanth Bhat  wrote:
> >
> >> Hi ,
> >>
> >>I am stuck not able to progress.  Any suggestions to debug
> the
> >> issue?  Please advice.
> >>
> >> Thanks
> >> Vasanth
> >>
> >>
> >> On Thu, Jun 30, 2016 at 10:32 AM, Vasanth Bhat 
> wrote:
> >>
> >>> Hi,
> >>> We are trying to do a small POC with  Hbase 1.2.1 , Hadoop
> >>> 2.6.4, zookeeper 3.4.8 and phoenix 4.7.0.
> >>>
> >>> We have setup a three node zookeeper quorum, and hbase 1.2.1 setup with
> >>> 3 region servers.
> >>> The Hbase and zk setup seems to be OK, as we are able to do basic
>  CRUD
> >>> operations with hbase.
> >>>
> >>> Now we are trying to setup phoenix and  connect.
> >>>
> >>> When I try to  launch sqlline.py, I get the following exception.
> >>>
> >>>
> >>> had...@client-121.hbasepoc.com:~/phoenix-4.7.0-HBase-1.1-bin/bin$
> *./sqlline.py
> >>> zk1.hbasepoc.com <http://zk1.hbasepoc.com>,zk2.hbasepoc.com
> >>> <http://zk2.hbasepoc.com>,zk3.hbasepoc.com:2181/hbase
> >>> <http://zk3.hbasepoc.com:2181/hbase>*
> >>> phoenix_utils.hbase_conf_dir => /etc/hbase/conf
> >>> phoenix_utils.phoenix_client_jar =>
> >>>
> /u01/hadoop/phoenix-4.7.0-HBase-1.1-bin/bin/../phoenix-4.7.0-HBase-1.1-client.jar
> >>> java_cmd => /usr/jdk/instances/jdk1.8.0/bin/java $PHOENIX_OPTS  -cp
> >>>
> "/etc/hbase/conf:/u01/hadoop/phoenix-4.7.0-HBase-1.1-bin/bin/../phoenix-4.7.0-HBase-1.1-client.jar:::/etc/hadoop/conf:"
> >>>
> -Dlog4j.configuration=file:/u01/hadoop/phoenix-4.7.0-HBase-1.1-bin/bin/log4j.properties
> >>> sqlline.SqlLine -d org.apache.phoenix.jdbc.PhoenixDriver -u
> jdbc:phoenix:
> >>> zk1.hbasepoc.com,zk2.hbasepoc.com,zk3.hbasepoc.com:2181/hbase -n none
> >>> -p none --color=true --fastConnect=false --verbose=true
> --incremental=false
> >>> --isolation=TRANSACTION_READ_COMMITTED
> >>> Setting property: [incremental, false]
> >>> Setting property: [isolation, TRANSACTION_READ_COMMITTED]
> >>> issuing: !connect jdbc:phoenix:zk1.hbasepoc.com,zk2.hbasepoc.com,
> >>> zk3.hbasepoc.com:2181/hbase none none
> >>> org.apache.phoenix.jdbc.PhoenixDriver
> >>> Connecting to jdbc:phoenix:zk1.hbasepoc.com,zk2.hbasepoc.com,
> >>> zk3.hbasepoc.com:2181/hbase
> >>> *java.lang.NoClassDefFoundError: Could not initialize class
> >>>
> org.apache.hadoop.hbase.util.Bytes$LexicographicalComparerHolder$UnsafeComparer*
> >>> at org.apache.hadoop.hbase.util.Bytes.putInt(Bytes.java:899)
> >>> at
> >>> org.apache.hadoop.hbase.KeyValue.createByteArray(KeyValue.java:1082)
> >>> at org.apache.hadoop.hbase.KeyValue.(KeyValue.java:652)
> >>> at org.apache.hadoop.hbase.KeyValue.(KeyValue.java:580)
> >>> at org.apache.hadoop.hbase.KeyValue.(KeyValue.java:483)
> >>> at org.apache.hadoop.hbase.KeyValue.(KeyValue.java:370)
> >>> at org.apache.hadoop.hbase.KeyValue.(KeyValue.java:267)
> >>> at
> >>> org.apache.phoenix.util.KeyValueUtil.newKeyValue(KeyValueUtil.java:48)
> >>> at
> >>> org.apache.phoenix.util.KeyValueUtil.newKeyValue(KeyValueUtil.java:82)
> >>> at
> >>>
> org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.(PhoenixDatabaseMetaData.java:1004)
> >>> at
> >>>
> org.apache.phoenix.exception.SQLExceptionCode.(SQLExceptionCode.java:238)
> >>> at
> >>>
> org.apache.phoenix.query.Con

Re: Java Query timeout

[Ankit Singhal]

bq. 1) From the exceptions, it looks like timeout is set to 1min (6ms)
and actual query takes little more than a minute (60306ms) to finish. Why
does Java client takes longer to finish than sqlline? The query returns
only one record so there shouldn’t be network latency or something

It timed out at 60306ms , it doesn't mean query will take this much time
only, it may take longer too. If java client is running from remote machine
and sqlline is local then network latency can affect RPC too.
This is actually , your scan RPC request is taking time to respond and is
not returning within a timeout period. You need to increase scanner timeout
period along with above properties you mentioned.


hbase.client.scanner.timeout.period
6
  


Regards,
Ankit Singhal

On Mon, Aug 8, 2016 at 6:55 PM,  wrote:

> Thanks Brian. I have added HBASE_CONF_DIR and it’s still timing out.
>
>
>
> Any help?
>
>
>
> *From:* Brian Jeltema [mailto:bdjelt...@gmail.com]
> *Sent:* Thursday, August 04, 2016 15:08
> *To:* user@phoenix.apache.org
> *Subject:* Re: Java Query timeout
>
>
>
> Regarding your second question, try
>
>
>
>export HBASE_CONF_DIR=
>
>
>
> before running the client.
>
>
>
> On Aug 4, 2016, at 3:01 PM, kannan.ramanat...@barclays.com wrote:
>
>
>
> Any pointers?
>
>
>
> *From:* Ramanathan, Kannan: R&A (NYK)
> *Sent:* Wednesday, August 03, 2016 15:08
> *To:* user@phoenix.apache.org
> *Subject:* RE: Java Query timeout
>
>
>
> Can someone please take a look?
>
>
>
> *From:* Ramanathan, Kannan: IT (NYK)
> *Sent:* Tuesday, August 02, 2016 15:59
> *To:* user@phoenix.apache.org
> *Subject:* Java Query timeout
>
>
>
> Hello All,
>
>
>
> We’re using Phoenix 4.7 with CDH 5.7.1. The query from Java client is
> timing out with this error:
>
>
>
> Caused by: java.net.SocketTimeoutException: callTimeout=6,
> callDuration=60306: row '' on table 'PHOENIX_TABLE' at region=
> PHOENIX_TABLE,,1462867135721.5bc05e2b5485f3fd550e9f93a795a100., hostname=
> ABC.com <http://abc.com/>,60020,1469522152474, seqNum=245191
>
> at org.apache.hadoop.hbase.client.RpcRetryingCaller.
> callWithRetries(RpcRetryingCaller.java:159)
>
> at org.apache.hadoop.hbase.client.
> ResultBoundedCompletionService$QueueingFuture.run(
> ResultBoundedCompletionService.java:64)
>
> ... 3 more
>
> Caused by: java.io.IOException: Call to ABC.com/1.1.1.1:60020
> <http://abc.com/1.1.1.1:60020> failed on local exception:
> org.apache.hadoop.hbase.ipc.CallTimeoutException: Call id=60,
> waitTime=60005, operationTimeout=6 expired.
>
> at org.apache.hadoop.hbase.ipc.
> RpcClientImpl.wrapException(RpcClientImpl.java:1255)
>
> at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(
> RpcClientImpl.java:1223)
>
> at org.apache.hadoop.hbase.ipc.AbstractRpcClient.
> callBlockingMethod(AbstractRpcClient.java:216)
>
> at org.apache.hadoop.hbase.ipc.AbstractRpcClient$
> BlockingRpcChannelImplementation.callBlockingMethod(
> AbstractRpcClient.java:300)
>
> at org.apache.hadoop.hbase.protobuf.generated.
> ClientProtos$ClientService$BlockingStub.scan(ClientProtos.java:32651)
>
> at org.apache.hadoop.hbase.client.ScannerCallable.call(
> ScannerCallable.java:213)
>
> at org.apache.hadoop.hbase.client.ScannerCallable.call(
> ScannerCallable.java:62)
>
> at org.apache.hadoop.hbase.client.RpcRetryingCaller.
> callWithoutRetries(RpcRetryingCaller.java:200)
>
> at org.apache.hadoop.hbase.client.
> ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.
> java:371)
>
> at org.apache.hadoop.hbase.client.
> ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.
> java:345)
>
> at org.apache.hadoop.hbase.client.RpcRetryingCaller.
> callWithRetries(RpcRetryingCaller.java:126)
>
> ... 4 more
>
> Caused by: org.apache.hadoop.hbase.ipc.CallTimeoutException: Call id=60,
> waitTime=60005, operationTimeout=6 expired.
>
> at org.apache.hadoop.hbase.ipc.
> Call.checkAndSetTimeout(Call.java:70)
>
> at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(
> RpcClientImpl.java:1197)
>
> ... 13 more
>
>
>
> The same query works fine in sqlline (taking about 30 seconds to finish).
>
>
>
> From various posts/blogs/user forums, it appears that client side
> hbase-site.xml should have these properties set possibly with higher
> n

Re: ERROR 201 (22000) illegal data error, expected length at least 4 but had ...

[Ankit Singhal]

Hi Dong-iL Kim,

As you said , you are inserting data from hbase, so the exception can be
seen if the primary key and columns are not properly represented or encoded
as per Phoenix.

Please see for more details:-
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

If you have a composite key , it is always better to insert data from
phoenix only.

Regards,
Ankit Singhal

On Fri, Aug 5, 2016 at 8:00 PM, Dong-iL, Kim  wrote:

> oh.  phoenix version is 4.7.0 and on EMR.
> Thx.
>
> > On Aug 5, 2016, at 11:27 PM, Dong-iL, Kim  wrote:
> >
> > Hi
> > I’ve create a table in phoenix.
> >
> > CREATE TABLE WINLOSS (
> >dayNumber VARCHAR NOT NULL,
> >type VARCHAR NOT NULL,
> >historyId VARCHAR NOT NULL,
> >seq INTEGER NOT NULL,
> >timestamp BIGINT,
> >name VARCHAR,
> >gameType VARCHAR,
> >playType VARCHAR,
> >gameKind VARCHAR,
> >onRitt BOOLEAN,
> >contributedAmount BIGINT,
> >payAmount BIGINT
> >CONSTRAINT rowid PRIMARY KEY(dayNumber, type, historyId, seq)
> > ) default_column_family = 'winloss';
> >
> > insert data through hbase.
> >
> > and I got an error when querying table.
> > just like this.
> >
> > select * from WINLESS;
> >
> > Error: ERROR 201 (22000): Illegal data. Expected length of at least 4
> bytes, but had 1 (state=22000,code=201)
> > java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length
> of at least 4 bytes, but had 1
> >   at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.
> newException(SQLExceptionCode.java:422)
> >   at org.apache.phoenix.exception.SQLExceptionInfo.buildException(
> SQLExceptionInfo.java:145)
> >   at org.apache.phoenix.schema.types.PDataType.
> checkForSufficientLength(PDataType.java:274)
> >   at org.apache.phoenix.schema.types.PInteger$IntCodec.
> decodeInt(PInteger.java:183)
> >   at org.apache.phoenix.schema.types.PInteger.toObject(
> PInteger.java:81)
> >   at org.apache.phoenix.schema.types.PInteger.toObject(
> PInteger.java:28)
> >   at org.apache.phoenix.schema.types.PDataType.toObject(
> PDataType.java:985)
> >   at org.apache.phoenix.compile.ExpressionProjector.getValue(
> ExpressionProjector.java:75)
> >   at org.apache.phoenix.jdbc.PhoenixResultSet.getObject(
> PhoenixResultSet.java:524)
> >   at sqlline.Rows$Row.(Rows.java:157)
> >   at sqlline.BufferedRows.(BufferedRows.java:38)
> >   at sqlline.SqlLine.print(SqlLine.java:1650)
> >   at sqlline.Commands.execute(Commands.java:833)
> >   at sqlline.Commands.sql(Commands.java:732)
> >   at sqlline.SqlLine.dispatch(SqlLine.java:808)
> >   at sqlline.SqlLine.begin(SqlLine.java:681)
> >   at sqlline.SqlLine.start(SqlLine.java:398)
> >   at sqlline.SqlLine.main(SqlLine.java:292)
> >
> > what shall I do?
> >
> > Thanks.
>
>

Re: Problems with Phoenix bulk loader when using row_timestamp feature

[Ankit Singhal]

Samarth, filed PHOENIX-3176 for the same.



On Wed, Aug 10, 2016 at 11:42 PM, Ryan Templeton  wrote:

> 0: jdbc:phoenix:localhost:2181> explain select count(*) from
> historian.data;
>
> *+--+*
>
> *| * *  PLAN  ** |*
>
> *+--+*
>
> *| * CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER HISTORIAN.DATA* |*
>
> *| * ROW TIMESTAMP FILTER [0, 1470852712807)* |*
>
> *| * SERVER FILTER BY FIRST KEY ONLY * |*
>
> *| * SERVER AGGREGATE INTO SINGLE ROW* |*
>
> *+--+*
>
> 4 rows selected (0.071 seconds)
>
> From: Samarth Jain 
> Reply-To: "user@phoenix.apache.org" 
> Date: Wednesday, August 10, 2016 at 12:05 AM
> To: "user@phoenix.apache.org" 
> Subject: Re: Problems with Phoenix bulk loader when using row_timestamp
> feature
>
> Ryan,
>
> Can you tell us what the explain plan says for the select count(*) query.
>
> - Samarth
>
>
> On Tue, Aug 9, 2016 at 12:58 PM, Ryan Templeton <
> rtemple...@hortonworks.com> wrote:
>
>> I am working on a project that will be consuming sensor data. The “fact”
>> table is defined as:
>>
>> CREATE TABLE historian.data (
>> assetid unsigned_int not null,
>> metricid unsigned_int not null,
>> ts timestamp not null,
>> val double
>> CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp))
>> IMMUTABLE_ROWS=true;
>>
>> I generated a 1million row csv sample dataset and use the Phoenix bulk
>> loader to load this data up. The tool reports that all 1,000,000 rows were
>> loaded successfully which I can confirm via sqlline.
>>
>> I then dropped and recreated the table to take advantage of the
>> row_timestamp feature
>>
>> drop table historian.data;
>> CREATE TABLE historian.data (
>> assetid unsigned_int not null,
>> metricid unsigned_int not null,
>> ts timestamp not null,
>> val double
>> CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp))
>> IMMUTABLE_ROWS=true;
>>
>> I reran the bulk loader utility which says it completed successfully
>>
>> [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t
>> HISTORIAN.DATA /tmp/data.csv
>>
>> SLF4J: Class path contains multiple SLF4J bindings.
>>
>> SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180
>> /phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/im
>> pl/StaticLoggerBinder.class]
>>
>> SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180
>> /hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticL
>> oggerBinder.class]
>>
>> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
>> explanation.
>>
>> 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load
>> native-hadoop library for your platform... using builtin-java classes where
>> applicable
>>
>> 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The
>> short-circuit local reads feature cannot be used because libhadoop cannot
>> be loaded.
>>
>> csv columns from database.
>>
>> CSV Upsert complete. 100 rows upserted
>>
>> Time: 65.985 sec(s)
>>
>> But when I run “select count(*) from historian.data” I see that only the
>> first 572 rows appear in the table. These rows correlate to the the first
>> 572 rows of the input file.
>>
>> 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data;
>>
>> *+--+*
>>
>> *| **COUNT(1)** |*
>>
>> *+--+*
>>
>> *| *572 * |*
>>
>> *+--+*
>>
>> 1 row selected (4.541 seconds)
>>
>> 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from
>> historian.data;
>>
>>
>> *+--+--+*
>>
>> *| **MIN(TS) ** | **
>> MAX(TS) ** |*
>>
>>
>> *+--+--+*
>>
>> *| *2016-08-08 11:05:15.000 * | *2016-08-08
>> 20:36:15.000 * |*
>>
>> *+--+—+*
>>
>>
>>
>>
>> Any ideas?
>>
>>
>> Thanks,
>> Ryan
>>
>
>

[ANNOUNCE] Apache Phoenix 4.8.0 released

[Ankit Singhal]

Apache Phoenix enables OLTP and operational analytics for Hadoop through
SQL support and integration with other projects in the ecosystem such as
Spark, HBase, Pig, Flume, MapReduce and Hive.

We're pleased to announce our 4.8.0 release which includes:
- Local Index improvements[1]
- Integration with hive[2]
- Namespace mapping support[3]
- VIEW enhancements[4]
- Offset support for paged queries[5]
- 130+ Bugs resolved[6]
- HBase v1.2 is also supported ( with continued support for v1.1, v1.0 &
v0.98)
- Many performance enhancements(related to StatsCache, distinct, Serial
query with Stats etc)[6]

The release is available in source or binary form here [7].

Release artifacts are signed with the following key:
*https://people.apache.org/keys/committer/ankit.asc
*

Thanks,
The Apache Phoenix Team

[1] https://issues.apache.org/jira/browse/PHOENIX-1734
[2] https://issues.apache.org/jira/browse/PHOENIX-2743
[3] https://issues.apache.org/jira/browse/PHOENIX-1311
[4] https://issues.apache.org/jira/browse/PHOENIX-1508
[5] https://issues.apache.org/jira/browse/PHOENIX-2722
[6] 
*https://issues.apache.org/jira/secure/ReleaseNote.jspa?version=12334393&projectId=12315120
*
[7] https://phoenix.apache.org/download.html

Re: How to troubleshoot 'Could not find hash cache for joinId' which is failing always for some users and never for others

[Ankit Singhal]

Hi Chabot,

"USE_SORT_MERGE_JOIN" hint is supported from v4.3.0 onwards. As your
version is 4.2.2 ,so it is not getting used in your query.

It seems , your stats are corrupted because of region merge or split,
resulting in some regionserver to not receive a hash join cache. Though
stats collection is improved in later versions to handle such cases but for
now, you can try deleting the guidePosts from SYSTEM.STATS  ,

DELETE FROM SYSTEM.STATS WHERE PHYSICAL_NAME='';

bq. But now, some users can no longer search. Others do not have problems
for a similar search. Granted, the searched data is different.
Yes, it could be possible because some users are hitting certain key range
only depending upon the first column(prefix) of the row key.

Regards,
Ankit Singhal






On Mon, Aug 15, 2016 at 6:29 PM, Chabot, Jerry 
wrote:

> I’ve added the hint to the SELECT. Does anyone see anything wrong with
> this statement?
>
>
>
> select /*+ USE_SORT_MERGE_JOIN 
> */T1.L,T1.C,T1.A,T1.R,T1.W,T1.P,T1.N,T1.E,T1.S,T1.M,
> T1.CW, T1.T,T1.Y,T1.U
>
>FROM "asset_metadata" T1
>
>WHERE R=(SELECT MAX(R) from "asset_metadata" WHERE L=T1.L AND C=T1.C
> AND A=T1.A AND C=? AND L IN (?))
>
>   AND T1.L in (?) AND T1.N ILIKE ? order by T1.N asc nulls first limit
> 100
>
>
>
> I ask because the query still fails. The stack trace in the region server
> is the same as before. It appears to still be using a hash-join.
>
>
>
> Caused by: org.apache.phoenix.exception.PhoenixIOException:
> org.apache.phoenix.exception.PhoenixIOException: org.apache.hadoop.hbase.
> DoNotRetryIOException:
>
> Could not find hash cache for joinId: \^V???0^M^G. The cache might have
> expired and have been removed.
>
> at org.apache.phoenix.coprocessor.HashJoinRegionScanner.(
> HashJoinRegionScanner.java:96)
>
> at org.apache.phoenix.coprocessor.ScanRegionObserver.
> doPostScannerOpen(ScanRegionObserver.java:195)
>
> at org.apache.phoenix.coprocessor.BaseScannerRegionObserver.
> postScannerOpen(BaseScannerRegionObserver.java:144)
>
> at org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.
> postScannerOpen(RegionCoprocessorHost.java:1976)
>
>
>
> How can I confirm SORT_MERGE is being used?
>
>
>
> *Jerry P. Chabot*  | Master Software Designer 7  |  Big Data Solutions,  
> Hewlett
> Packard Enterprise |  508.467.1236  |  jerry.p.cha...@hpe.com
>
>
>
> *From:* Kathiresan S [mailto:kathiresanselva...@gmail.com]
> *Sent:* Thursday, May 05, 2016 11:14 AM
> *To:* user@phoenix.apache.org
> *Subject:* Re: How to troubleshoot 'Could not find hash cache for joinId'
> which is failing always for some users and never for others
>
>
>
> We had a similar problem while using explicit JOIN in the query (we didn't
> have sub-queries). We worked it around by switching to sort merge join from
> hash join (to get it run, though it'll be bit slower than hash join as per
> documentation)
>
>
>
> Try adding the hint */*+ USE_SORT_MERGE_JOIN*/ *in the SELECT query.
>
>
>
> Thanks,
>
> Kathir
>
>
>
> On Thu, May 5, 2016 at 10:42 AM, Chabot, Jerry 
> wrote:
>
> We are running HBase 0.98.5 with Phoenix 4.2.2. All was well for a period
> of time (a year or more). But now, some users can no longer search. Others
> do not have problems for a similar search. Granted, the searched data is
> different.
>
>
>
> The 'asset' table was created and maintained using an HBase client. Search
> uses a Phoenix view on this table. The row key is L,C,A,R.
>
>
>
> A search that is failing is what we call a multiple locality search. A
> single locality search is working. In multi-locality search, there is an IN
> L=(...) clause.
>
>
>
> Note, the L is the first column in the row key. The query is shown below.
>
>
>
> SELECT * from "asset" WHERE R=(SELECT MAX(R) from "asset" WHERE L=T1.L
> and C=T1.C AND A=T1.A AND C=? AND L IN (?))
>
>   AND (T1.L in (?) AND T1.T=? AND T1.N ILIKE ? order by
> T1.N asc nulls first limit 500
>
>
>
> The subquery selects the latest revision of each 'asset'. The additional
> conditions (C=? and L IN (?)) was added to make the subquery more
> efficient. I did notice there is no corresponding C=? in the primary WHERE
> clause.
>
>
>
> The root cause is "Could not find hash cache for joinId: The cache might
> have expired and have been removed." One recommendation on the Phoenix site
> is to increase the phoenix.coprocessor.maxSearchCacheTimeToLiveMs. We use
> the default. This recommendation does not seem applicable because the query

Re: Cannot select data from a system table

[Ankit Singhal]

Aaron,

you can escape check for reserved keyword with double quotes ""

SELECT * FROM SYSTEM."FUNCTION"

Regards,
Ankit Singhal

On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor 
wrote:

> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
> this a known bug?
>
>
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
> ++--+-+---+-
> -+++
> -+--+-+---+-
> --+-++---+
> | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  |
> TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | INDEX_STATE  |
> IMMUTABLE_ROWS  | SALT_BUCKETS  | MULTI_TENANT  | VIEW_STATEMENT  |
> VIEW_TYPE  | INDEX_TYP |
> ++--+-+---+-
> -+++
> -+--+-+---+-
> --+-++---+
> || SYSTEM   | CATALOG | SYSTEM TABLE  |  |
> || |  |
> false   | null  | false | |
> |   |
> || SYSTEM   | FUNCTION| SYSTEM TABLE  |  |
> || |  |
> false   | null  | false | |
> |   |
> || SYSTEM   | SEQUENCE| SYSTEM TABLE  |  |
> || |  |
> false   | null  | false | |
> |   |
> || SYSTEM   | STATS   | SYSTEM TABLE  |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | CUSTOMER| TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | LINEITEM| TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | NATION  | TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | ORDERS  | TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | PART| TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | PARTSUPP| TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | REGION  | TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> || TPCH | SUPPLIER| TABLE |  |
> || |  |
> false   | null  | false | |
> |   |
> ++--+-+---+-
> -+++
> -+--+-+---+-
> --+-++---+
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from
> SYSTEM.FUNCTION;
> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
> "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
> Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1,
> column 22.
> at org.apache.phoenix.exception.PhoenixParserException.
> newException(PhoenixParserException.java:33)
>   

Re: [ANNOUNCE] Apache Phoenix 4.8.0 released

[Ankit Singhal]

Sorry Tom, not sure about the release cycle at Cloudera side, you may need
to check with them directly.

On Tue, Aug 16, 2016 at 4:22 PM, Squires, Tom (ELS-LON) <
tom.squi...@elsevier.com> wrote:

> Hi Ankit,
>
>
> Do you know when the Phoenix 4.8.0 Cloudera parcel will be available @
> http://archive.cloudera.com/cloudera-labs/phoenix/parcels/latest/ ?
>
>
> Many thanks,
>
> Tom
>
>
> ------
> *From:* Ankit Singhal 
> *Sent:* 12 August 2016 18:25
> *To:* d...@phoenix.apache.org; user; annou...@apache.org;
> d...@hbase.apache.org; u...@hbase.apache.org
> *Subject:* [ANNOUNCE] Apache Phoenix 4.8.0 released
>
> Apache Phoenix enables OLTP and operational analytics for Hadoop through
> SQL support and integration with other projects in the ecosystem such as
> Spark, HBase, Pig, Flume, MapReduce and Hive.
>
> We're pleased to announce our 4.8.0 release which includes:
> - Local Index improvements[1]
> - Integration with hive[2]
> - Namespace mapping support[3]
> - VIEW enhancements[4]
> - Offset support for paged queries[5]
> - 130+ Bugs resolved[6]
> - HBase v1.2 is also supported ( with continued support for v1.1, v1.0 &
> v0.98)
> - Many performance enhancements(related to StatsCache, distinct, Serial
> query with Stats etc)[6]
>
> The release is available in source or binary form here [7].
>
> Release artifacts are signed with the following key:
> *https://people.apache.org/keys/committer/ankit.asc
> <https://people.apache.org/keys/committer/ankit.asc>*
>
> Thanks,
> The Apache Phoenix Team
>
> [1] https://issues.apache.org/jira/browse/PHOENIX-1734
> [2] https://issues.apache.org/jira/browse/PHOENIX-2743
> [3] https://issues.apache.org/jira/browse/PHOENIX-1311
> [4] https://issues.apache.org/jira/browse/PHOENIX-1508
> [5] https://issues.apache.org/jira/browse/PHOENIX-2722
> [6] 
> *https://issues.apache.org/jira/secure/ReleaseNote.jspa?version=12334393&projectId=12315120
> <https://issues.apache.org/jira/secure/ReleaseNote.jspa?version=12334393&projectId=12315120>*
> [7] https://phoenix.apache.org/download.html
>
> --
>
> Elsevier Limited. Registered Office: The Boulevard, Langford Lane,
> Kidlington, Oxford, OX5 1GB, United Kingdom, Registration No. 1982084,
> Registered in England and Wales.
>

Re: java.lang.IllegalStateException: Number of bytes to resize to must be greater than zero, but instead is -1984010164

[Ankit Singhal]

can you confirm what values are set for
phoenix.groupby.estimatedDistinctValues(Integer)
and phoenix.groupby.maxCacheSize(long)?

On Wed, Aug 31, 2016 at 12:24 PM, Dong-iL, Kim  wrote:

> Hi.
>
> when I’m using simple groupby query, exception occured as below.
> What shall I do?
>
> Thanks.
>
> Error: org.apache.phoenix.exception.PhoenixIOException:
> org.apache.hadoop.hbase.DoNotRetryIOException: PLAYER_ACTION,\x00\x00B\
> x8D2J7GvczV2U8J8Z2itjcODTGI\x003\x001\x004\x00\x80\x00\x00\x00\x00l\
> xE1e1157988054337976043\x00\x00\x00\x01V\xBA\x8DH~,1472148086609.
> d1b6338ff6820e5469eb78643e4e177d.: Number of bytes to resize to must be
> greater than zero, but instead is -1984010164
> at org.apache.phoenix.util.ServerUtil.createIOException(
> ServerUtil.java:87)
> at org.apache.phoenix.util.ServerUtil.throwIOException(
> ServerUtil.java:53)
> at org.apache.phoenix.coprocessor.BaseScannerRegionObserver.
> postScannerOpen(BaseScannerRegionObserver.java:229)
> at org.apache.hadoop.hbase.regionserver.
> RegionCoprocessorHost$52.call(RegionCoprocessorHost.java:1334)
> 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:1712)
> at org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.
> postScannerOpen(RegionCoprocessorHost.java:1329)
> at org.apache.hadoop.hbase.regionserver.RSRpcServices.
> scan(RSRpcServices.java:2412)
> at org.apache.hadoop.hbase.protobuf.generated.
> ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java:33648)
> at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2178)
> at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:112)
> at org.apache.hadoop.hbase.ipc.RpcExecutor.consumerLoop(
> RpcExecutor.java:133)
> at org.apache.hadoop.hbase.ipc.RpcExecutor$1.run(RpcExecutor.
> java:108)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.lang.IllegalStateException: Number of bytes to resize to
> must be greater than zero, but instead is -1984010164
> at org.apache.phoenix.memory.GlobalMemoryManager$
> GlobalMemoryChunk.resize(GlobalMemoryManager.java:135)
> at org.apache.phoenix.cache.aggcache.SpillableGroupByCache$1.
> removeEldestEntry(SpillableGroupByCache.java:172)
> at java.util.LinkedHashMap.afterNodeInsertion(
> LinkedHashMap.java:299)
> at java.util.HashMap.putVal(HashMap.java:663)
> at java.util.HashMap.put(HashMap.java:611)
> at org.apache.phoenix.cache.aggcache.SpillableGroupByCache.cache(
> SpillableGroupByCache.java:249)
> at org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver
> .scanUnordered(GroupedAggregateRegionObserver.java:420)
> at org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver
> .doPostScannerOpen(GroupedAggregateRegionObserver.java:165)
> at org.apache.phoenix.coprocessor.BaseScannerRegionObserver.
> postScannerOpen(BaseScannerRegionObserver.java:202)
> ... 12 more (state=08000,code=101)

Re: Help w/ table that suddenly keeps timing out

[Ankit Singhal]

Yes, Ted is right , "Error 1102 (XCL02): Cannot get all table regions"
happens when Phoenix is not able to get locations of all regions. Assigning
that offline region should help.

On Mon, Aug 29, 2016 at 10:22 PM, Ted Yu  wrote:

> I searched for "Cannot get all table regions" in hbase repo - no hit.
> Seems to be Phoenix error.
>
> Anyway, the cause could be due to the 1 offline region for this table.
> Can you retrieve the encoded region name and search for it in the master
> log ?
>
> Feel free to pastebin snippets of master / region server logs if needed
> (with proper redaction).
>
> See if the following shell command works:
>
>   hbase> assign 'REGIONNAME'
>   hbase> assign 'ENCODED_REGIONNAME'
>
> Cheers
>
> On Mon, Aug 29, 2016 at 9:41 AM, Riesland, Zack 
> wrote:
>
>> ​Our cluster recently had some issue related to network outages*.
>>
>> When all the dust settled, Hbase eventually "healed" itself, and almost
>> everything is back to working well, with a couple of exceptions.
>>
>> In particular, we have one table where almost every (Phoenix) query times
>> out - which was never the case before. It's very small compared to most of
>> our other tables at around 400 million rows.
>>
>> I have tried with a raw JDBC connection in Java code as well as with Aqua
>> Data Studio, both of which usually work fine.
>>
>> The specific failure is that after 15 minutes (the set timeout),  I get a
>> one-line error that says: “Error 1102 (XCL02): Cannot get all table regions”
>>
>> When I look at the GUI tools (like http://> server>:16010/master-status#storeStats) it shows '1' under "offline
>> regions" for that table (it has 33 total regions). Almost all the other
>> tables show '0'.
>>
>> Can anyone help me troubleshoot this?
>>
>> Are there Phoenix tables I can clear out that may be confused?
>>
>> This isn’t an issue with the schema or skew or anything. The same table
>> with the same data was lightning fast before these hbase issues.
>>
>> I know there is a CLI tool for fixing HBase issues. I'm wondering whether
>> that "offline region" is the cause of these timeouts.
>>
>> If not, how I can I figure it out?
>>
>> Thanks!
>>
>>
>>
>> * FWIW, what happened was that DNS stopped working for a while, so HBase
>> started referring to all the region servers by IP address, which somewhat
>> worked, until the region servers restarted. Then they were hosed until a
>> bit of manual intervention.
>>
>>
>>
>
>

Re: RetriesExhaustedWithDetailsException: Failed 1 action: IOException: 1 time

[Ankit Singhal]

can you check whether your hbase is stable or not (you can use hbck tool to
see any inconsistencies).

On Sat, Aug 27, 2016 at 10:41 PM, Sanooj Padmakumar 
wrote:

> Hi All,
>
> I am getting the same exception , this time when running a Phoenix MR (
> https://phoenix.apache.org/phoenix_mr.html) .. The MR works just fine if
> I am doing a select with limit to some 10 rows.. but when I do the same
> with a lot of data, I start getting the below exception after 66% of
> reducer..
>
> 6/08/27 10:04:35 INFO mapreduce.Job: Task Id :
> attempt_1471862728027_0103_r_36_0, Status : FAILED
> Error: java.lang.RuntimeException: org.apache.phoenix.execute.CommitException:
> org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException:
> Failed 2000 actions: IOException: 2000 times,
> at org.apache.phoenix.mapreduce.PhoenixRecordWriter.close(
> PhoenixRecordWriter.java:62)
> at org.apache.hadoop.mapred.ReduceTask$
> NewTrackingRecordWriter.close(ReduceTask.java:550)
>  
> Caused by: 
> org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException:
> Failed 2000 actions: IOException: 2000 times,
> at org.apache.hadoop.hbase.client.AsyncProcess$
> BatchErrors.makeException(AsyncProcess.java:227)
> at org.apache.hadoop.hbase.client.AsyncProcess$
> BatchErrors.access$1700(AsyncProcess.java:207)
> at org.apache.hadoop.hbase.client.AsyncProcess$
> AsyncRequestFutureImpl.getErrors(AsyncProcess.java:1568)
> at org.apache.hadoop.hbase.client.HTable.batch(HTable.java:1003)
> at org.apache.hadoop.hbase.client.HTable.batch(HTable.java:1017)
> at org.apache.phoenix.execute.MutationState.commit(
> MutationState.java:444)
> ... 13 more
>
> When I look at the failed reducers log I am seeing a lot of logs like
> these..
>
> 2016-08-27 10:07:05,834 ERROR [main] 
> org.apache.hadoop.hbase.client.AsyncProcess: Cannot get replica 0 location 
> for 
> {"totalColumns":25,"families":{"p":[{"timestamp":1472317624237,"tag":[],"qualifier"
>
> But as I said with very less number of rows in the select query, the MR works 
> just fine and data is populated alright ? Any Phoenix/Hbase parameters
>
> that is should look into ?
>
> Thanks
>
> Sanooj Padmakumar
>
>
>
>
>
> On Wed, Aug 24, 2016 at 11:26 AM, Sanooj Padmakumar 
> wrote:
>
>> Hi All,
>>
>> I get this error one one of the nodes where we have an application
>> running. It comes only after a certain duration and once the application is
>> restarted things will start working normally..Any inputs as to why this
>> might be happening will be of great help.
>>
>>
>> org.springframework.jdbc.UncategorizedSQLException:
>> PreparedStatementCallback; uncategorized SQLException for SQL [<> >>> ]; SQL state [null]; error code [0]; org.apache.hadoop.hbase.client
>> .RetriesExhaustedWithDetailsException: Failed 1 action: IOException: 1
>> time, ; nested exception is org.apache.phoenix.execute.CommitException:
>> org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException:
>> Failed 1 action: IOException: 1 time,
>> at org.springframework.jdbc.support.AbstractFallbackSQLExceptio
>> nTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
>> at org.springframework.jdbc.support.AbstractFallbackSQLExceptio
>> nTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>> at org.springframework.jdbc.support.AbstractFallbackSQLExceptio
>> nTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>> at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTempl
>> ate.java:660)
>> at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTempla
>> te.java:909)
>> at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTempla
>> te.java:933)
>> at org.springframework.jdbc.core.namedparam.NamedParameterJdbcT
>> emplate.update(NamedParameterJdbcTemplate.java:313)
>> at Caused by: org.apache.phoenix.execute.CommitException:
>> org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException:
>> Failed 1 action: IOException: 1 time,
>> at org.apache.phoenix.execute.MutationState.commit(MutationStat
>> e.java:473)
>> at org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConn
>> ection.java:472)
>> at org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConn
>> ection.java:469)
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConn
>> ection.java:469)
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixState
>> ment.java:323)
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixState
>> ment.java:312)
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(Pho
>> enixStatement.java:310)
>> at org.apache.phoenix.jdbc.PhoenixPreparedStatement.executeUpda
>>

Re: Cannot select data from a system table

[Ankit Singhal]

bq. Is this documented somewhere ?
not as such, https://phoenix.apache.org/language/index.html#quoted_name is
generally for case sensitive identifier(and to allow some special
characters) and same can be used for keywords.

bq. Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would
give us good idea.
Yes Ted, you are right . Phoenix keywords are the tokens in
phoenix-core/src/main/antlr3/PhoenixSQL.g



On Sun, Aug 21, 2016 at 8:33 PM, Ted Yu  wrote:

> Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would give
> us good idea.
>
> Experts please correct me if I am wrong.
>
> On Sun, Aug 21, 2016 at 7:21 AM, Aaron Molitor  > wrote:
>
>> Thanks, Ankit, that worked.
>>
>> And on the heels of Ted's question... Are the reserved words documented
>> (even if just a list) somewhere, I've been looking at this page:
>> http://phoenix.apache.org/language/index.html  -- it feels like where I
>> should find a list like that, but I don't see it explicitly called out.
>>
>> -Aaron
>>
>> On Aug 21, 2016, at 09:04, Ted Yu  wrote:
>>
>> Ankit:
>> Is this documented somewhere ?
>>
>> Thanks
>>
>> On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal 
>> wrote:
>>
>>> Aaron,
>>>
>>> you can escape check for reserved keyword with double quotes ""
>>>
>>> SELECT * FROM SYSTEM."FUNCTION"
>>>
>>> Regards,
>>> Ankit Singhal
>>>
>>> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <
>>> amoli...@splicemachine.com> wrote:
>>>
>>>> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
>>>> this a known bug?
>>>>
>>>>
>>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
>>>> ++--+-+---+-
>>>> -+++
>>>> -+--+-+---+-
>>>> --+-++---+
>>>> | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  |
>>>> TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | INDEX_STATE  |
>>>> IMMUTABLE_ROWS  | SALT_BUCKETS  | MULTI_TENANT  | VIEW_STATEMENT  |
>>>> VIEW_TYPE  | INDEX_TYP |
>>>> ++--+-+---+-
>>>> -+++
>>>> -+--+-+---+-
>>>> --+-++---+
>>>> || SYSTEM   | CATALOG | SYSTEM TABLE  |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || SYSTEM   | FUNCTION| SYSTEM TABLE  |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || SYSTEM   | SEQUENCE| SYSTEM TABLE  |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || SYSTEM   | STATS   | SYSTEM TABLE  |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || TPCH | CUSTOMER| TABLE |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || TPCH | LINEITEM| TABLE |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>> |   |
>>>> || TPCH | NATION  | TABLE |  |
>>>>   || |  |
>>>> false   | null  | false | |
>>>

Re: error after upgrade to 4.8-hbase-1.1

[Ankit Singhal]

probably , you are hitting the following bug(I just reported today).
https://issues.apache.org/jira/browse/PHOENIX-3237

Because of above bug, data table is trying to get the index maintainers of
disabled index of another table.
You can check it with below query:-
select
TABLE_NAME,DATA_TABLE_NAME,INDEX_TYPE,INDEX_STATE,INDEX_DISABLE_TIMESTAMP
from system.catalog where INDEX_TYPE is not null;

INDEX_DISABLE_TIMESTAMP>0 for more than one data table.

As a workaround , I think for now , if possible, we need to rebuild indexes
of one data table manually by using (Alter INDEX .. REBUILD) command.

Regards,
Ankit Singhal

On Fri, Aug 26, 2016 at 7:52 PM, jinzh...@wacai.com 
wrote:

> after upgraded ,  a lot of WARN logs in hbase-regionserver.log:
>
>
> 2016-08-26 22:12:39,682 WARN  [pool-287-thread-1] coprocessor.
> MetaDataRegionObserver: ScheduledBuildIndexTask failed!
> java.lang.IllegalArgumentException
> at org.apache.phoenix.index.IndexMaintainer.create(
> IndexMaintainer.java:121)
> at org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(
> PTableImpl.java:957)
> at org.apache.phoenix.coprocessor.MetaDataRegionObserver$
> BuildIndexScheduleTask.run(MetaDataRegionObserver.java:318)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
> at java.util.concurrent.ScheduledThreadPoolExecutor$
> ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
> at java.util.concurrent.ScheduledThreadPoolExecutor$
> ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
> 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)
>
> --
> jinzh...@wacai.com
>

Re: CONVERT_TZ for TIMESTAMP column

[Ankit Singhal]

Yes, it seems to be a bug as function signature in docs doesn't seems to
match with the allowedTypes(only date) in the code.
Filed Jira for the same(PHOENIX-3241)

For now , you can use DATE datatype(time part is also maintained with date
in Phoenix).

For eg:-
CREATE TABLE IF NOT EXISTS test (*"iso_8601" TIMESTAMP NOT NULL* PRIMARY
KEY);
upsert into test values(TO_DATE('2016-04-01 22:45:00'));
select * from test;
+--+
| iso_8601 |
+--+
| 2016-04-01 22:45:00.000  |
+------+


Regards,
Ankit Singhal




On Fri, Sep 2, 2016 at 1:38 PM, Craig Roberts 
wrote:

> Hi folks,
>
> I've been tripped up recently by Phoenix auto-converting to GMT (from
> phoenix.query.dateFormatTimeZone), which I've now set to UTC. I'm on
> Phoenix 4.4 in the Ambari/HDP stack.
>
> I'd like to do some retrieval and aggregation based on the client's local
> time zone, so I'm using:
>
> *SELECT CONVERT_TZ("iso_8601", 'UTC', 'Asia/Kuala_Lumpur') FROM TEST LIMIT
> 10;*
>
> But I'm getting:
>
>
> *Error: ERROR 203 (22005): Type mismatch. expected: [DATE] but was:
> TIMESTAMP at CONVERT_TZ argument 1*
>
> in Squirrel.
>
> My schema (cut down) is:
>
> *CREATE TABLE IF NOT EXISTS TEST(*
> *  "iso_8601" TIMESTAMP NOT NULL*
> *)*
>
> Just for reference: the time part is important. I don't just want to
> convert 2016-01-01, I need to convert 2016-01-01 01:00:00 to the local
> equivalent at 2016-01-01 09:00:00.
>
> The docs state timestampTerm is a valid argument, so am I missing
> something obvious?
>
> The alternative is I set Phoenix to store all dates in my local timezone,
> but I'd really rather store in UTC.
>
> Thanks for any help you can offer,
> *Craig Roberts*
> *Senior Developer*
>
> *FrogAsia Sdn Bhd (A YTL Company) *| Unit 9, Level 2, D6 at Sentul East |
> 801, Jalan Sentul, 51000 Kuala Lumpur | 01125618093 | Twitter
> <http://www.twitter.com/FrogAsia> | Facebook
> <http://www.facebook.com/FrogAsia> | Website <http://www.frogasia.com/>
>
> *This message (including any attachments) is for the use of the addressee
> only. It may contain private proprietary or legally privileged statements
> and information. No confidentiality or privilege is waived or lost by any
> mistransmission. If you are not the intended recipient, please immediately
> delete it and all copies of it from your system, destroy any hard copies of
> it and notify the sender. You must not, directly or indirectly, use,
> disclose, distribute, print, copy or rely on any part of the message if you
> are not the intended recipient. Any views expressed in this message
> (including any attachments) are those of the individual sender and not
> those of any member of the YTL Group, except where the message states
> otherwise and the sender is authorized to state them to be the views of any
> such entity.*
>

Re: can I prevent rounding of a/b when a and b are integers

[Ankit Singhal]

Adding some more workaround , if you are working on column:-
select cast(col_int1 as decimal)/col_int2;
select col_int1*1.0/3;



On Wed, Sep 21, 2016 at 8:33 PM, James Taylor 
wrote:

> Hi Noam,
> Please file a JIRA. As a workaround, you can do SELECT 1.0/3.
> Thanks,
> James
>
> On Wed, Sep 21, 2016 at 12:48 AM, Bulvik, Noam 
> wrote:
>
>> Hi,
>>
>>
>>
>> When I do something like select 1/3 from   the result will be
>> integer value (0) and not double or alike(0.33….). Is there some
>> configuration that can force the result to be double
>>
>>
>>
>> BTW – when executing the same query in oracle (select 1/3 from dual ) I
>> get correct result same in impala
>>
>>
>>
>> *Noam Bulvik*
>>
>>
>>
>> --
>>
>> PRIVILEGED AND CONFIDENTIAL
>> PLEASE NOTE: The information contained in this message is privileged and
>> confidential, and is intended only for the use of the individual to whom it
>> is addressed and others who have been specifically authorized to receive
>> it. If you are not the intended recipient, you are hereby notified that any
>> dissemination, distribution or copying of this communication is strictly
>> prohibited. If you have received this communication in error, or if any
>> problems occur with transmission, please contact sender. Thank you.
>>
>
>

Re: Phoenix ResultSet.next() takes a long time for first row

[Ankit Singhal]

Share some more details about the query, DDL and explain plan. In Phoenix,
there are cases where we do some server processing at the time when
rs.next() is called first time but subsequent next() should be faster.

On Thu, Sep 22, 2016 at 9:52 AM, Sasikumar Natarajan 
wrote:

> Hi,
> I'm using Apache Phoenix core 4.4.0-HBase-1.1 library to query the
> data available on Phoenix server.
>
> preparedStatement.executeQuery()  seems to be taking less time. But to
> enter into *while (rs.next()) {} *takes a long time. I would like to know
> what is causing the delay to make the ResultSet ready. Please share your
> thoughts on this.
>
>
> --
> Regards,
> Sasikumar Natarajan
>

Re: Phoenix ResultSet.next() takes a long time for first row

[Ankit Singhal]

Sorry Sasi, missed your last mails.

It seems that you have one region in a table or the query touching one
region because of monotonically increasing key['MK00100','YOU',4]  .
Varying performance is because you may have filter which are aggressive and
skipping lots of rows in between (*0*  (7965 ms), *2041* (7155 ms),
*4126 *(1630
ms)) and that's why server is taking time.

can you try after doing salting on the table.
https://phoenix.apache.org/salted.html




On Wed, Sep 28, 2016 at 10:47 AM, Sasikumar Natarajan 
wrote:

> Any one has suggestions for the performance issue discussed in this
> thread?. Your suggestions would help me resolve this issue.
>
> Infrastructure details:
>
> Azure HDInsight HBase
>
> Type Node SizeCores   Nodes
> Head D3 V2 8 2
> Region D3 V2 16 4
> ZooKeeper D3 V2 12 3
> Thanks,
> Sasikumar Natarajan.
>
>
> On Fri, Sep 23, 2016 at 7:57 AM, Sasikumar Natarajan 
> wrote:
>
>> Also its not only the first time it takes time when we call
>> ResultSet.next().
>>
>> When we iterate over ResultSet, it takes a long time initially and then
>> iterates faster. Again after few iterations, it takes sometime and this
>> goes on.
>>
>>
>>
>> Sample observation:
>>
>>
>>
>> Total Rows available on ResultSet : 5130
>>
>> Statement.executeQuery() has taken : 702 ms
>>
>> ResultSet Indices at which long time has been taken : *0*  (7965 ms),
>> *2041* (7155 ms), *4126 *(1630 ms)
>>
>> On Fri, Sep 23, 2016 at 7:52 AM, Sasikumar Natarajan 
>> wrote:
>>
>>> Hi Ankit,
>>>Where does the server processing happens, on the HBase
>>> cluster or the server where Phoenix core runs.
>>>
>>> PFB the details you have asked for,
>>>
>>> Query:
>>>
>>> SELECT col1, col2, col5, col7, col11, col12 FROM SPL_FINAL where
>>> col1='MK00100' and col2='YOU' and col3=4 and col5 in (?,?,?,?,?) and ((col7
>>> between to_date('2016-08-01 00:00:00.000') and to_date('2016-08-05
>>> 23:59:59.000')) or (col8 between to_date('2016-08-01 00:00:00.000') and
>>> to_date('2016-08-05 23:59:59.000')))
>>>
>>>
>>> Explain plan:
>>>
>>> CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER SPL_FINAL
>>> ['MK00100','YOU',4]
>>> SERVER FILTER BY (COL5 IN ('100','101','105','234','653') AND
>>> ((COL7 >= TIMESTAMP '2016-08-01 00:00:00.000' AND COL7 <= TIMESTAMP
>>> '2016-08-05 23:59:59.000') OR (COL8 >= TIMESTAMP '2016-08-01 00:00:00.000'
>>> AND COL8 <= TIMESTAMP '2016-08-05 23:59:59.000')))
>>> DDL:
>>>
>>> CREATE TABLE IF NOT EXISTS SPL_FINAL
>>> (col1 VARCHAR NOT NULL,
>>> col2 VARCHAR NOT NULL,
>>> col3 INTEGER NOT NULL,
>>> col4 INTEGER NOT NULL,
>>> col5 VARCHAR NOT NULL,
>>> col6 VARCHAR NOT NULL,
>>> col7 TIMESTAMP NOT NULL,
>>> col8 TIMESTAMP NOT NULL,
>>> ext.col9 VARCHAR,
>>> ext.col10 VARCHAR,
>>> pri.col11 VARCHAR[], //this column contains 3600 items in every row
>>> pri.col12 VARCHAR
>>> ext.col13 BOOLEAN
>>> CONSTRAINT SPL_FINAL_PK PRIMARY KEY (col1, col2, col3, col4, col5, col6,
>>> col7, col8)) COMPRESSION='SNAPPY';
>>>
>>> Thanks,
>>> Sasikumar Natarajan.
>>>
>>> On Thu, Sep 22, 2016 at 12:36 PM, Ankit Singhal <
>>> ankitsingha...@gmail.com> wrote:
>>>
>>>> Share some more details about the query, DDL and explain plan. In
>>>> Phoenix, there are cases where we do some server processing at the time
>>>> when rs.next() is called first time but subsequent next() should be faster.
>>>>
>>>> On Thu, Sep 22, 2016 at 9:52 AM, Sasikumar Natarajan >>> > wrote:
>>>>
>>>>> Hi,
>>>>> I'm using Apache Phoenix core 4.4.0-HBase-1.1 library to query the
>>>>> data available on Phoenix server.
>>>>>
>>>>> preparedStatement.executeQuery()  seems to be taking less time. But
>>>>> to enter into *while (rs.next()) {} *takes a long time. I would like
>>>>> to know what is causing the delay to make the ResultSet ready. Please 
>>>>> share
>>>>> your thoughts on this.
>>>>>
>>>>>
>>>>> --
>>>>> Regards,
>>>>> Sasikumar Natarajan
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Regards,
>>> Sasikumar Natarajan
>>>
>>
>>
>>
>> --
>> Regards,
>> Sasikumar Natarajan
>>
>
>
>
> --
> Regards,
> Sasikumar Natarajan
>

Re: Creating view on a phoenix table throws Mismatched input error

[Ankit Singhal]

Currently, Phoenix doesn't support projecting selective columns of table or
expressions in a view. You need to project all the columns with (select *).

Please see the section "Limitations" on this page or PHOENIX-1507.
https://phoenix.apache.org/views.html

On Thu, Oct 6, 2016 at 10:05 PM, Mich Talebzadeh 
wrote:

>
> Hi,
>
> I have a table on top of a Hbase table as follows:
>
> create table "tsco" (PK VARCHAR PRIMARY KEY, "stock_daily"."Date" VARCHAR,
> "stock_daily"."close" VARCHAR, "stock_daily"."high" VARCHAR,
> "stock_daily"."low" VARCHAR, "stock_daily"."open" VARCHAR,
> "stock_daily"."ticker" VARCHAR, "stock_daily"."stock" VARCHAR,
> "stock_daily"."volume" VARCHAR
>
> This works fine.
>
> Now I want to create a view as follows:
>
> create view "tsco_clean" (Date date, close integer, high integer, low
> integer, open integer, ticker integer, stock varchar, volume integer) as
> select to_date("Date",'dd-MMM-yy'), to_number("close"), to_number("high"),
> to_number("low"), to_number("open"), "ticker", "stock", to_number("volume")
> from "tsco" where "close" != '-' and "high" != '-' and "low" != '-' and
> "open" != '-' and to_number("volume") > 0;
>
> However, it falls over with the following error
>
>
> Error: Error -1 (0) : Error while executing SQL "create view
> "tsco_clean" (Date date, close integer, high integer, low integer, open
> integer, ticker integer, stock varchar, volume integer) as select
> to_date("Date",'dd-MMM-yy'), to_number("close"), to_number("high"),
> to_number("low"), to_number("open"), "ticker", "stock", to_number("volume")
> from "tsco" where "close" != '-' and "high" != '-' and "low" != '-' and
> "open" != '-' and to_number("volume") > 0": Remote driver error:
> RuntimeException: org.apache.phoenix.exception.PhoenixParserException:
> ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK",
> got "to_date" at line 1, column 151. -> PhoenixParserException: ERROR 604
> (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got
> "to_date" at line 1, column 151. -> MismatchedTokenException: (null
> exception message) (state=0,code=-1)
>
>
> Appreciate any feedback.
>
>
> Thanks
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>

Re: Ordering of numbers generated by a sequence

[Ankit Singhal]

JFYI, phoenix.query.rowKeyOrderSaltedTable is deprecated and is
not honored from v4.4, so please use phoenix.query.force.rowkeyorder
instead.
I have updated the docs(http://localhost:8000/tuning.html) now accordingly.

On Mon, Oct 17, 2016 at 3:14 AM, Josh Elser  wrote:

> Not 100% sure, but yes, I believe this is correct. One of the servers
> would get 0-99, the other 100-199. The server to use up that batch of 100
> values would then request 200-299, etc. Setting the cache to be 0 would
> likely impact the performance of Phoenix.
>
> Using some external system to perform the global ordering may result in
> better performance. Some testing would probably be needed :)
>
> For the final rowKeyOrderSaltedTable question, yes, I believe that query
> would work as you intend.
>
>
> F21 wrote:
>
>> I am using Phoenix 4.8.1 with HBase 1.2.3 and the Phoenix Query Server.
>>
>> I want to use a sequence to generate a monotonically increasing id for
>> each row. Since the documentation states that 100 sequence numbers are
>> cached by default in the client (in my case, I assume the caching would
>> happen in the query server), what is the behavior if I have 2 query
>> servers (load-balanced)? Does this mean Server A would generate numbers
>> starting from 0, and Server B would generate numbers starting from 100?
>> I need to make sure that the id is in order on a global basis for the
>> whole table. Would setting the CACHE to 0 be the best of achieving this?
>>
>> Also, as the ID is monotonically increasing, I plan to salt the table
>> using something like (no manual split points):
>>
>> CREATE TABLE mytable (id BIGINT NOT NULL PRIMARY KEY,  VARCHAR)
>> SALT_BUCKETS = 20;
>>
>> Without setting phoenix.query.rowKeyOrderSaltedTable to true, would I
>> still be able to get my records in order if I select them using
>> something like this?
>>
>> SELECT * FROM mytable WHERE id > 5 && id < 100 ORDER BY id
>>
>> Thanks,
>>
>> Francis
>>
>>

Re: Analytic functions in Phoenix

[Ankit Singhal]

Hi Mich,

Phoenix doesn't support OVER clause(PHOENIX-154) yet. Analytics function
supported are listed under "Aggregate Functions" on below page.
https://phoenix.apache.org/language/functions.html
For eg:-
https://phoenix.apache.org/language/functions.html#percentile_cont

And, I think query with OVER clause can be re-written by using SELF JOINs
in many cases.

Regards,
Ankit Singhal

On Sun, Oct 23, 2016 at 3:11 PM, Mich Talebzadeh 
wrote:

> Hi,
>
> I was wondering whether analytic functions work in Phoenix. For example
> something equivalent to below in Hive or Spark SQL
>
> select distinct *
> from
> (
> select substr(timecreated,1,10) as Date, ticker as Ticker,
> avg(CAST(price AS FLOAT)) OVER (PARTITION BY (substr(timecreated,1,10)))
> AS AveragePrice
> from marketdatahbase
> ) tmp
> ORDER BY ticker, Date
>
> Thanks
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>

Re: Index in Phoenix view on Hbase is not updated

[Ankit Singhal]

Rebuild is currently a costly operation as it will rebuild the complete
index again and should be used when you think your index is corrupted and
you are not aware about the timestamp when it got out of sync.

Why can't you use Bulk loading tool[1] provided by Phoenix instead of using
importTSV, as this tool will build the data for index table along with data
table?
https://phoenix.apache.org/bulk_dataload.html


On Sat, Oct 22, 2016 at 11:52 PM, Mich Talebzadeh  wrote:

> A workout I deployed was to rebuild the index immediately after bulk load
> of data into Hbase table
>
> ALTER INDEX MARKETDATAHBASE_IDX1 ON "marketDataHbase" REBUILD;
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 22 October 2016 at 18:25, Mich Talebzadeh 
> wrote:
>
>> Sorry Ted,
>>
>> This is the syntax for view
>>
>> create view "marketDataHbase" (PK VARCHAR PRIMARY KEY,
>> “price_info”.”ticker” VARCHAR,"price_info"."timecreated" VARCHAR,
>> "price_info"."price" VARCHAR);
>>
>> Thanks James for clarification.
>>
>> My understanding is that when one creates an index on a Phoenix view on
>> an Hbase table,  a local index file is created in Phoenix with data as is
>> and that Phoenix table has no way of knowing if new data is added to Hbase
>> table.
>>
>> From an operational point of view unless index in Phoenix is immediately
>> updated after any append to Hbase, then it is pretty useless.
>>
>> In my case data is inserted to Hbase table. I am just using Phoenix for
>> data queries (DQ) as opposed to inserts.
>>
>> Regards,
>>
>>
>>
>>
>>
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>> On 22 October 2016 at 17:59, James Taylor  wrote:
>>
>>> Hi Mich,
>>> Phoenix indexes are only updated if you use Phoenix APIs to input the
>>> data.
>>> Thanks,
>>> James
>>>
>>>
>>> On Saturday, October 22, 2016, Ted Yu  wrote:
>>>
 The first statement creates index, not view.

 Can you check ?

 Cheers

 > On Oct 22, 2016, at 1:51 AM, Mich Talebzadeh <
 mich.talebza...@gmail.com> wrote:
 >
 > Hi,
 >
 > I have a Hbase table that is populated via
 > org.apache.hadoop.hbase.mapreduce.ImportTsv
 > through bulk load ever 15 minutes. This works fine.
 >
 > In Phoenix I created a view on this table
 >
 > jdbc:phoenix:rhes564:2181> create index marketDataHbase_idx on
 > "marketDataHbase" ("price_info"."ticker", "price_info"."price",
 > "price_info"."timecreated");
 >
 > This also does what is supposed to do and shows correct count.
 >
 > I then created an index in Phoenix as below
 >
 > create index index_dx1 on "marketDataHbase"
 > ("price_info"."timecreated","price_info"."ticker",
 "price_info"."price");
 >
 > that showed the records OK at that time. I verified this using explain
 >
 >
 > 0: jdbc:phoenix:rhes564:2181> explain select count(1) from
 > "marketDataHbase";
 > +-+
 > |  PLAN   |
 > +-+
 > | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER INDEX_DX1  |
 > | SERVER FILTER BY FIRST KEY ONLY |
 > | SERVER AGGREGATE INTO SINGLE ROW|
 > +-+
 >
 > Now the issue is that the above does not show new data since build in
 Hbase
 > table unless I do the following:
 >
 > 0: jdbc:phoenix:rhes564:2181> alter index INDEX_DX1 on
 "marketDataHbase"
 > rebuild;
 >
 >
 > Which is not what an index should do (The covered index should be
 > maintained automatically).
 > The simple issue is how to overcome this problem?
 >
 > As I understand the index in Phoenix 

Re: Setting default timezone for Phoenix

[Ankit Singhal]

Hi Mich,

phoenix.query.dateFormatTimeZone is honored by functions which are listed
on a page[1] and their description has explicit mention of usage of this
property to override timezone from default GMT(For eg:[2]). so you need to
wrap other function with such function to convert the timezone like you did
in your example.

select CURRENT_DATE(), CONVERT_TZ(CURRENT_DATE());
+-+-+
| DATE '2016-10-23 12:38:02.434'  | DATE '2016-10-23 13:38:02.434'  |
+-+-+
| 2016-10-23 12:38:02.434 | 2016-10-23 13:38:02.434 |
+-+-+



I think we will update this property[3] with proper description to avoid
such confusion.

[1]https://phoenix.apache.org/language/functions.html
[2]https://phoenix.apache.org/language/functions.html#to_date
[3] https://phoenix.apache.org/tuning.html


Regards,
Ankit Singhal


On Sun, Oct 23, 2016 at 6:15 PM, Mich Talebzadeh 
wrote:

> Hi,
>
> My queries in Phoenix pickup GMT timezone as default.
>
> I need them to default to Europe/London by default
>
> 0: jdbc:phoenix:rhes564:2181> select CURRENT_DATE(),
> CONVERT_TZ(CURRENT_DATE(), 'UTC', 'Europe/London');
> +-+-+
> | DATE '2016-10-23 12:38:02.434'  | DATE '2016-10-23 13:38:02.434'  |
> +-+-+
> | 2016-10-23 12:38:02.434 | 2016-10-23 13:38:02.434 |
> +-+-+
>
>
> I want to set this property in hbase-site.xml. This setting does not seem
> to work
>
> 
>  phoenix.query.dateFormatTimeZone
>  Europe/London
> 
>
>
> Thanks
>
>
>

Re: Index in Phoenix view on Hbase is not updated

[Ankit Singhal]

bq. Will bulk load from Phoenix update the underlying Hbase table?
Yes. instead of using importTSV try to use CSV bulkload only.

bq. Do I need to replace Phoenix view on Hbase as with CREATE TABLE?
You can still keep VIEW.

Regards,
Ankit Singhal

On Sun, Oct 23, 2016 at 6:37 PM, Mich Talebzadeh 
wrote:

> Thanks Ankit,
>
> couple of questions.
>
>
>1. Will bulk load from Phoenix update the underlying Hbase table?
>2. Do I need to replace Phoenix view on Hbase as with CREATE TABLE?
>
> regards
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 23 October 2016 at 13:52, Ankit Singhal 
> wrote:
>
>> Rebuild is currently a costly operation as it will rebuild the complete
>> index again and should be used when you think your index is corrupted and
>> you are not aware about the timestamp when it got out of sync.
>>
>> Why can't you use Bulk loading tool[1] provided by Phoenix instead of
>> using importTSV, as this tool will build the data for index table along
>> with data table?
>> https://phoenix.apache.org/bulk_dataload.html
>>
>>
>> On Sat, Oct 22, 2016 at 11:52 PM, Mich Talebzadeh <
>> mich.talebza...@gmail.com> wrote:
>>
>>> A workout I deployed was to rebuild the index immediately after bulk
>>> load of data into Hbase table
>>>
>>> ALTER INDEX MARKETDATAHBASE_IDX1 ON "marketDataHbase" REBUILD;
>>>
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>> On 22 October 2016 at 18:25, Mich Talebzadeh 
>>> wrote:
>>>
>>>> Sorry Ted,
>>>>
>>>> This is the syntax for view
>>>>
>>>> create view "marketDataHbase" (PK VARCHAR PRIMARY KEY,
>>>> “price_info”.”ticker” VARCHAR,"price_info"."timecreated" VARCHAR,
>>>> "price_info"."price" VARCHAR);
>>>>
>>>> Thanks James for clarification.
>>>>
>>>> My understanding is that when one creates an index on a Phoenix view on
>>>> an Hbase table,  a local index file is created in Phoenix with data as is
>>>> and that Phoenix table has no way of knowing if new data is added to Hbase
>>>> table.
>>>>
>>>> From an operational point of view unless index in Phoenix is
>>>> immediately updated after any append to Hbase, then it is pretty useless.
>>>>
>>>> In my case data is inserted to Hbase table. I am just using Phoenix for
>>>> data queries (DQ) as opposed to inserts.
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Dr Mich Talebzadeh
>>>>
>>>>
>>>>
>>>> LinkedIn * 
>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>
>>>>
>>>>
>>>> http://talebzadehmich.wordpress.com
>>>>
>>>>
>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>>> any loss, damage or destruction of data or any other prope

Re: PhoenixIOException: Table 'unionSchemaName.unionTableName' was not found

[Ankit Singhal]

You need to increase phoenix timeout as well(phoenix.query.timeoutMs).

https://phoenix.apache.org/tuning.html

On Sun, Oct 23, 2016 at 3:47 PM, Parveen Jain  wrote:

> hi All,
>
> I just realized that phoneix doesn't provide "group by" and "distinct"
> methods if we use phoenix map reduce. It seems below approach uses phoenix
> map reduce which is not suitable for this type of  queries.
>
> Now I wanted to run below query by any means. My table has more than 70
> million records and I could not run it either using "sqlline.py" and also
> tried to run it using "squirl" as well as using simple phoenix jdbc
> connection from a java program. In all three I was getting connection
> timeout error. I tried to increase various timeouts in Hbase 
> (hbase.rpc.timeout
> -> 366, I even checked my hbase file path using "./phoenix_utils.py
>  | grep hbase_conf_path")
>  but no luck. I am ok if my query takes more time but I wanted to run it
> successfully without any issue.
>
> HBase - 1.1.2.2.4.2.0-258
> Phoenix - phoenix-4.4.0.2.4.2.0-258
>
> Can any one provide any suggestion ?
>
> Regards,
> Parveen Jain
>
> --
> *From:* Parveen Jain 
> *Sent:* Sunday, October 23, 2016 10:18 AM
> *To:* user@phoenix.apache.org
> *Subject:* PhoenixIOException: Table 'unionSchemaName.unionTableName' was
> not found
>
>
> While running this query from spark phoenix connector:
>
>
> select distinct(C_TXN.CUSTMR_ID) from CUS_TXN where
> (CUS_TXN.TXN_TYPE='') and (substr(CUS_TXN.ROW_KEY,0,8)>='20160101')
> group by CUS_TXN.CUSTMR_ID having sum(CUS_TXN.TXN_AMOUNT)>=300 union all
> select distinct(CUS_TXN.CUSTMR_ID) from CUS_TXN where
> (CUS_TXN.TXN_TYPE='') and (substr(CUS_TXN.ROW_KEY,0,8)>='20160101')
> group by CUS_TXN.CUSTMR_ID having sum(CUS_TXN.TXN_AMOUNT)>100
>
>
>
> getting below exception:
>
> *Getting some phoenix exception for below query:*
> java.lang.RuntimeException: org.apache.phoenix.exception.PhoenixIOException:
> Table 'unionSchemaName.unionTableName' was not found, got:
> hbase:namespace.
> at com.google.common.base.Throwables.propagate(Throwables.java:
> 160)
> at org.apache.phoenix.mapreduce.PhoenixRecordReader.initialize(
> Phoen
>
>
> my code for fetching records is:
>
> PhoenixConfigurationUtil.setInputTableName(configuration , TABLE_NAME);
> PhoenixConfigurationUtil.setOutputTableName(configuration ,TABLE_NAME);
> PhoenixConfigurationUtil.setInputQuery(configuration, QueryToRun);
> PhoenixConfigurationUtil.setInputClass(configuration, DataRecord.class);
>
> configuration.setClass(JobContext.OUTPUT_FORMAT_CLASS_ATTR,PhoenixOutputFormat.class,
> OutputFormat.class);
>
> @SuppressWarnings("unchecked")
> JavaPairRDD stocksRDD = jsc.newAPIHadoopRDD(
> configuration,
> PhoenixInputFormat.class,
> NullWritable.class,
> DataRecord.class);
>
>
> Regards,
> Parveen Jain
>
>
>
> Any pointer why this could be happening.
>
>
> Regards,
>
> Parveen Jain
>
>
>

Re: huge query result miss some fields

[Ankit Singhal]

Do you have bigger rows? if yes , it may be similar to
https://issues.apache.org/jira/browse/PHOENIX-3112 and
increasing hbase.client.scanner.max.result.size can help.



On Thu, Nov 24, 2016 at 6:00 PM, 金砖  wrote:

> thanks Abel.
>
>
> I tried update statistics, it did not work.
>
>
> But after some retries, I found something interesting:
>
> I add  'limit 1'  after my sql.
>
> Even actual size of result is the same(since there’s only 10 rows in
> table),  but the missing problem is solved.
>
> 
> *金砖*
> 挖财网络技术有限公司
> 地址：杭州市西湖区古翠路80号浙江科技产业大厦12楼
> 手机：15558015995
>
>  原始邮件
> *发件人:* Abel Fernández
> *收件人:* user
> *发送时间:* 2016年11月24日(周四) 20:14
> *主题:* Re: huge query result miss some fields
>
> Hi Jinzhuan,
>
> Have you tried to update the statistics of your table?
>
> https://phoenix.apache.org/update_statistics.html
>
>
> On Thu, 24 Nov 2016 at 11:46 金砖  wrote:
>
>> hi, all:
>>
>> I’m using phoenix-4.8.0-hbase-1.1 with hbase 1.1.3.
>>
>> When query a lot of rows(ex: 100,000),  some fileds of rows does not
>> exists  in result set.
>>
>>
>> steps
>>
>> 1. I created a table test(pk varchar primary key, id bigint, name
>> varchar, age bigint).
>>
>> 2. then populated with 10 rows, with key prefix 'prefix', and rows
>> will be 'prefix1' - 'prefix10'.
>>
>> 3. then query with select * from test;
>>
>>
>> occasionally some fields with be lost in rows,
>>
>> sometimes 2 rows missing id and age,
>>
>> some times 3 rows missing name.
>>
>>
>> Can anyone be helpful?  Is there some settings should be done ?
>>
>> 
>> *jinzhuan*
>>
> --
> Un saludo - Best Regards.
> Abel
>

Re: Inconsistent null behavior

[Ankit Singhal]

@James, is this similar to
https://issues.apache.org/jira/browse/PHOENIX-3112?
@Mac, can you try if increasing hbase.client.scanner.max.result.size helps?

On Tue, Dec 6, 2016 at 10:53 PM, James Taylor 
wrote:

> Looks like a bug to me. If you can reproduce the issue outside of Python
> phoenixdb, using the Query Server directly, please file a JIRA. If not, it
> sounds like a Phython phoenixdb issue.
>
> Thanks,
> James
>
> On Tue, Dec 6, 2016 at 8:58 AM, Mark Heppner 
> wrote:
>
>> I encountered something interesting and I'm not sure if it's a bug in
>> Phoenix itself, the query server, or just a side effect of using a large
>> binary column. If I create a table like this (in sqlline):
>> create table test1 (
>> id integer not null primary key,
>> image varbinary,
>> height unsigned_int,
>> width unsigned_int
>> )
>>
>> And insert with the Python phoenixdb client (uses the query server):
>> with phoenixdb.connect('http://localhost:8765', autocommit=True) as
>> con:
>> with con.cursor() as cur:
>> cur.execute('upsert into test1 values(?, ?, ?, ?)', [1, img,
>> 900, 900])
>>
>> When I "select * from test1" using sqlline, the width column appears as
>> null:
>> +-+--+-++
>> | ID  |IMAGE | HEIGHT  | WIDTH  |
>> +-+--+-++
>> | 1   | [B@5a2bd7c8  | 900 | null   |
>> +-+--+-++
>>
>> However, using "select width from test1", it returns the value:
>> ++
>> | WIDTH  |
>> ++
>> | 900|
>> ++
>>
>> Filtering by null using "select * from test1 where width is null" gives 0
>> rows, so it's like the value is there but doesn't get recognized when using
>> a "select *".
>>
>> The value does show up when the binary column is not included in the
>> select (select id, height, width). If the binary column is present, it
>> appears as null (select image, height, width).
>>
>> This behavior can be confirmed with phoenixdb too:
>> > cur.execute('select width from test1')
>> > cur.fetchall()
>> [[900]]
>>
>> > cur.execute('select * from test1')
>> > cur.fetchall()
>> [[1, '\x00\x01 ...', 900, None]]
>>
>> I don't think this is an issue with phoenixdb, since I can see the
>> protobuf message serializing both the height and width columns the same
>> way. Is this an issue with Phoenix itself, or just something the query
>> server did on upsert?
>>
>> Is this a side effect of using a large binary column (typical length is
>> 648)? In sqlline, using this works:
>>
>> upsert into test1 values (2, '\x00\x01\x02', 900, 900);
>> select * from test1;
>>
>> +-+--+-++
>> | ID  |IMAGE | HEIGHT  | WIDTH  |
>> +-+--+-++
>> | 1   | [B@1835d3ed  | 900 | null   |
>> | 2   | [B@66213a0d  | 900 | 900|
>> +-+--+-++
>>
>> Also worth mentioning, this never happens to the height column; it always
>> has the correct value. It's only happening on width, the second of the
>> unsigned_int columns.
>>
>> HBase 1.1.2 and Phoenix 4.7 from HDP 2.5.
>>
>
>

Re: slow response on large # of columns

[Ankit Singhal]

Have you checked your query performance without sqlline. As Jonathan also
mentioned, Sqlline has it's own performance issue in terms of reading
metadata.( so probably time spend is actually spent by sqlline in reading
metadata for 3600 columns and printing header)



On Wed, Dec 28, 2016 at 12:04 AM, Mark Heppner 
wrote:

> If you don't need to query any of the 3600 columns, you could even just
> use JSON inside of a VARCHAR field.
>
> On Mon, Dec 26, 2016 at 2:25 AM, Arvind S  wrote:
>
>> Setup ..
>> hbase (1.1.2.2.4) cluster on azure with 1 Region server. (8core 28 gb
>> ram ..~16gb RS heap)
>> phoenix .. 4.4
>>
>> Observation ..
>> created a table with 3 col composite PK and 3600 float type columns (1
>> per sec).
>> loaded with <5000 lines of data (<100 MB compressed snappy & fast diff
>> encoding)
>>
>> On performing "select * " or select with individually naming each of
>> these 3600 columns the query takes around 2+ mins to just return a few
>> lines (limit 2,10 etc).
>>
>> Subsequently on selecting lesser number of columns the performance seems
>> to improve.
>>
>> is it an anti-pattern to have large number of columns in phoenix tables?
>>
>> *Cheers !!*
>> Arvind
>>
>
>
>
> --
> Mark Heppner
>

Re: Phoenix Spark plug in cannot find table with a Namespace prefix

[Ankit Singhal]

Hi Long,


Is the table name provided at the time of creation is without double
quotes? if yes, then you should be running your queries having a table name
without double quotes and a dot between schema and table.

like this: - Select ENDPOINT_ID, CITY from *ACME.ENDPOINT_STATUS* Where
city = 'ACME City'


If above is not the case, then can you share the dump of SYSTEM.CATALOG and
SYSTEM:CATALOG table( by running scan from hbase shell). and confirm if you
have reverted any namespace property.

org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03):
Table undefined. tableName=ACME:ENDPOINT_STATUS
at 
org.apache.phoenix.schema.PMetaDataImpl.getTableRef(PMetaDataImpl.java:265)
at 
org.apache.phoenix.jdbc.PhoenixConnection.getTable(PhoenixConnection.java:449)
at 
org.apache.phoenix.util.PhoenixRuntime.getTable(PhoenixRuntime.java:407)
at 
org.apache.phoenix.util.PhoenixRuntime.generateColumnInfo(PhoenixRuntime.java:433)
at


Regards,

Ankit Singhal.




On Mon, Nov 7, 2016 at 11:38 PM, Long, Xindian 
wrote:

> Hi, Josh:
>
>
>
> Thanks for your reply. I just added a Jira issue, but I am not familiar
> with Scala, so I did not add the unit test.
>
>
>
> https://issues.apache.org/jira/browse/PHOENIX-3460
>
>
>
> Xindian
>
>
>
>
>
> *From:* Josh Mahonin [mailto:jmaho...@gmail.com]
> *Sent:* Friday, November 04, 2016 2:25 PM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Phoenix Spark plug in cannot find table with a Namespace
> prefix
>
>
>
> Hi,
>
>
>
> Thanks for including the query code and the query exception, it's very
> helpful.
>
>
>
> Normally I'd suggest following up with the vendor here, since the
> namespace support was added in Phoenix 4.8.0, but if everything but
> phoenix-spark is working with namespaces, I suspect they backported the
> feature.
>
>
>
> Could you file a JIRA ticket on the Phoenix project about this? I'm not
> sure if it should work out of the box or not. If you'd like to try your
> hand at adding a unit test to verify, you can look at the following places
> (these are for the schema syntax):
>
> https://github.com/apache/phoenix/blob/master/phoenix-
> spark/src/it/resources/setup.sql#L43
> https://github.com/apache/phoenix/blob/master/phoenix-
> spark/src/it/scala/org/apache/phoenix/spark/PhoenixSparkIT.scala#L407
>
>
>
> Thanks,
>
>
>
> Josh
>
>
>
> On Thu, Nov 3, 2016 at 4:52 PM, Long, Xindian 
> wrote:
>
> I am testing some code using Phoenix Spark plug in to read a Phoenix table
> with a namespace  prefix in the table name (the table is created as a
> phoenix table not a hbase table), but it returns an TableNotFoundException
> .
>
>
>
> The table is obviously there because I can query it using plain phoenix
> sql through  Squirrel, and using spark sql to query has no problem at all.
>
>
>
> The error log is in the attached file: tableNoFound.txt
>
>
>
> My testing code is in the attached  file: query.java
>
>
>
> The weird thing is in the attached code, if I run testSpark alone it gives
> the above exception, but if I run the testJdbc first, and followed by
> testSpark, both of them work.
>
>
>
> I am running on the  HDP 2.5  platform, with phoenix 4.7.0.2.5.0.0-1245
>
>
>
> The problem does not exist at all when I was running the same code on HDP
> 2.4 cluster, with phoenix  4.4.
>
>
>
> Neither does the problem occur when I query a table without a namespace
> prefix in the DB table name, on HDP 2.5
>
>
>
> By the way, here is how the HBase looks like when I list it.
>
>
>
> *hbase(main):031:0* list*
>
> *TABLE*
>
> *ACME:ENDPOINT_CONFIG*
>
> *ACME:ENDPOINT_STATUS*
>
> *LONG:ENDPOINTS*
>
> *LONG:RADIOCHANNELS*
>
> *LONG:REGIONINFORMATION*
>
> *LONG:TGBSTATISTICS*
>
> *SENSUS1:ENDPOINTS*
>
> *SENSUS1:RADIOCHANNELS*
>
> *SENSUS1:REGIONINFORMATION*
>
> *SENSUS1:TGBSTATISTICS*
>
> *SENSUS2:ENDPOINTS*
>
> *SENSUS2:RADIOCHANNELS*
>
> *SENSUS2:REGIONINFORMATION*
>
> *SENSUS2:TGBSTATISTICS*
>
> *SENSUS:ENDPOINTS*
>
> *SENSUS:RADIOCHANNELS*
>
> *SENSUS:REGIONINFORMATION*
>
> *SENSUS:TGBSTATISTICS*
>
> *SYSTEM.CATALOG*
>
> *SYSTEM:CATALOG*
>
> *SYSTEM:FUNCTION*
>
> *SYSTEM:SEQUENCE*
>
> *SYSTEM:STATS*
>
> *TENANT*
>
> *24 row(s) in 0.0090 seconds*
>
>
>
> *=> ["ACME:ENDPOINT_CONFIG", "ACME:ENDPOINT_STATUS", "LONG:ENDPOINTS",
> "LONG:RADIOCHANNELS", "LONG:REGIONINFORMATION", "LONG:TGBSTATISTICS",
> "SENSUS1:ENDPOINTS", "SENSUS1:RADIOCHANNELS", "SENSUS1:REGIONINFORMATION",
> "SENSUS1:TGBSTATISTICS", "SENSUS2:ENDPOINTS", "SENSUS2:RADIOCHANNELS",
> "SENSUS2:REGIONINFORMATION", "SENSUS2:TGBSTATISTICS", "SENSUS:ENDPOINTS",
> "SENSUS:RADIOCHANNELS", "SENSUS:REGIONINFORMATION", "SENSUS:TGBSTATISTICS",
> "SYSTEM.CATALOG", "SYSTEM:CATALOG", "SYSTEM:FUNCTION", "SYSTEM:SEQUENCE",
> "SYSTEM:STATS", "TENANT"]*
>
>
>
>
>
>
>

Re: Phoenix tracing did not start

[Ankit Singhal]

Hi Pradheep,

It seems tracing is not distributed as a part of HDP 2.4.3.0, please work
with your vendor for an appropriate solution.

Regards,
Ankit Singhal

On Thu, Jan 19, 2017 at 4:48 AM, Pradheep Shanmugam <
pradheep.shanmu...@infor.com> wrote:

> Hi,
>
> I am using hdp 2.4.3.0-227. I am trying to enable phoenix tracing to
> monitor the queries and to analyze performance. I followed steps outlined
> here - https://phoenix.apache.org/tracing.html
> i placed the hadoop-metrics2-hbase.properties in /etc/hbase/conf
> hadoop-metrics2-phoenix.properties in /usr/hdp/2.4.3.0-227/phoenix/bin in
> all regions servers
>
> Aslo added following properties to hbase.site
> phoenix.trace.statsTableName SYSTEM.TRACING_STATS value>
> phoenix.trace.frequency always. After this
>
> I am not clear where to place the ddl for SYSTEM.TRACING_STATS. Also i
> could not see ./bin/traceserver.py to start
> Please advice.
>
> Thanks,
> Pradheep
>

Re: ROW_TIMESTAMP weird behaviour

[Ankit Singhal]

I think you are also hitting
https://issues.apache.org/jira/browse/PHOENIX-3176.

On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi  wrote:

> Hi Pedro,
>
> Upserted key are different. One key is for July month & other for January
> month.
> 1. '2017-*07*-02T15:02:21.050'
> 2. '2017-*01*-02T15:02:21.050'
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
> On 7 February 2017 at 13:18, Pedro Boado  wrote:
>
>> Hi.
>>
>> I don't think it's weird. That column is PK and you've upserted twice the
>> same key value so first one is inserted and second one is updated.
>>
>> Regards.
>>
>>
>>
>> On 7 Feb 2017 04:59, "Dhaval Modi"  wrote:
>>
>>> Hi All,
>>>
>>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>>
>>> I created table in Phoenix as below:
>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>>> (XXX_TS ROW_TIMESTAMP))
>>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>>
>>> Now, I am trying to add data:
>>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>>
>>> I am only seeing one entry.
>>> *==*
>>> *0: jdbc:phoenix:> select * from DUMMY;*
>>> *+--+*
>>> *|  XXX_TS  |*
>>> *+--+*
>>> *| 2017-01-02 15:02:21.050  |*
>>> *+--+*
>>> *1 row selected (0.039 seconds)*
>>> *==*
>>>
>>>
>>> Additional info:
>>> System date of HBase & Phoenix: mar feb  7 05:57:37 CET 2017
>>>
>>>
>>> Regards,
>>> Dhaval Modi
>>> dhavalmod...@gmail.com
>>>
>>
>

Re: ROW_TIMESTAMP weird behaviour

[Ankit Singhal]

It's actually getting added but may be due to timezone difference, your
values are going in future.
You can set the local time zone by setting phoenix.query.dateFormatTimeZone
https://phoenix.apache.org/tuning.html

On Tue, Feb 7, 2017 at 6:34 PM, Dhaval Modi  wrote:

> Thanks Ankit.
>
> My issue is relevant to PHOENIX-3176.
>
> But additional observation is, any timestamp value after 13:oo hours of
> the same day is not added.
>
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> |  XXX_TS  |
> +--+
> | 2017-01-01 15:02:21.050  |
> | 2017-01-02 15:02:21.050  |
> | 2017-01-13 15:02:21.050  |
> | 2017-02-06 15:02:21.050  |
> | 2017-02-07 11:02:21.050  |
> | 2017-02-07 11:03:21.050  |
> | 2017-02-07 12:02:21.050  |
> +--+
> 7 rows selected (0.044 seconds)
> 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*12:03:21.050'*);
> 1 row affected (0.01 seconds)
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> |  XXX_TS  |
> +--+
> | 2017-01-01 15:02:21.050  |
> | 2017-01-02 15:02:21.050  |
> | 2017-01-13 15:02:21.050  |
> | 2017-02-06 15:02:21.050  |
> | 2017-02-07 11:02:21.050  |
> | 2017-02-07 11:03:21.050  |
> | 2017-02-07 12:02:21.050  |
> *| 2017-02-07 12:03:21.050  |*
> +--+
> 8 rows selected (0.047 seconds)
> 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*13:03:21.050*');
> 1 row affected (0.009 seconds)
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> |  XXX_TS  |
> +--+
> | 2017-01-01 15:02:21.050  |
> | 2017-01-02 15:02:21.050  |
> | 2017-01-13 15:02:21.050  |
> | 2017-02-06 15:02:21.050  |
> | 2017-02-07 11:02:21.050  |
> | 2017-02-07 11:03:21.050  |
> | 2017-02-07 12:02:21.050  |
> | 2017-02-07 12:03:21.050  |
> +--+
> 8 rows selected (0.04 seconds)
>
>
>
>
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
> On 7 February 2017 at 15:28, Ankit Singhal 
> wrote:
>
>> I think you are also hitting https://issues.apache.
>> org/jira/browse/PHOENIX-3176.
>>
>> On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi 
>> wrote:
>>
>>> Hi Pedro,
>>>
>>> Upserted key are different. One key is for July month & other for
>>> January month.
>>> 1. '2017-*07*-02T15:02:21.050'
>>> 2. '2017-*01*-02T15:02:21.050'
>>>
>>>
>>> Regards,
>>> Dhaval Modi
>>> dhavalmod...@gmail.com
>>>
>>> On 7 February 2017 at 13:18, Pedro Boado  wrote:
>>>
>>>> Hi.
>>>>
>>>> I don't think it's weird. That column is PK and you've upserted twice
>>>> the same key value so first one is inserted and second one is updated.
>>>>
>>>> Regards.
>>>>
>>>>
>>>>
>>>> On 7 Feb 2017 04:59, "Dhaval Modi"  wrote:
>>>>
>>>>> Hi All,
>>>>>
>>>>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>>>>
>>>>> I created table in Phoenix as below:
>>>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>>>>> (XXX_TS ROW_TIMESTAMP))
>>>>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>>>>
>>>>> Now, I am trying to add data:
>>>>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>>>>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>>>>
>>>>> I am only seeing one entry.
>>>>> *==*
>>>>> *0: jdbc:phoenix:> select * from DUMMY;*
>>>>> *+--+*
>>>>> *|  XXX_TS  |*
>>>>> *+--+*
>>>>> *| 2017-01-02 15:02:21.050  |*
>>>>> *+--+*
>>>>> *1 row selected (0.039 seconds)*
>>>>> *==*
>>>>>
>>>>>
>>>>> Additional info:
>>>>> System date of HBase & Phoenix: mar feb  7 05:57:37 CET 2017
>>>>>
>>>>>
>>>>> Regards,
>>>>> Dhaval Modi
>>>>> dhavalmod...@gmail.com
>>>>>
>>>>
>>>
>>
>

Re: Missing support for HBase 1.0 in Phoenix 4.9 ?

[Ankit Singhal]

The relevant thread where the decision was made to drop HBase 1.0 support
from v4.9 onwards.
http://search-hadoop.com/m/Phoenix/9UY0h21AGFh2sgGFK?subj=Re+DISCUSS+Drop+HBase+1+0+support+for+4+9



On Tue, Feb 7, 2017 at 8:13 PM, Mark Heppner  wrote:

> Pedro,
> I can't answer your question, but if you look at the blog announcement for
> 4.9, they say it's compatible with HBase 0.98/1.1/1.2:
> https://blogs.apache.org/phoenix/entry/announcing_phoenix_4_9_released
>
> The announcements for 4.8 and lower says HBase 0.98/*1.0*/1.1/1.2:
>https://blogs.apache.org/phoenix/entry/announcing_phoenix_4_8_released
>
> If you look at the Maven repo (I think it's separately maintained),
> there's also no bundle with HBase 1.0:
> https://mvnrepository.com/artifact/org.apache.phoenix/
> phoenix-queryserver
>
> Maybe they silently dropped support for 1.0? Of course there's nothing on
> the actual release notes, so I'm not sure:
> https://phoenix.apache.org/release_notes.html
>
>
>
> On Mon, Feb 6, 2017 at 6:29 PM, Pedro Boado  wrote:
>
>> Hi,
>>
>> I've being looking at Phoenix 4.9 and HBase supported versions and I'm
>> missing support for HBase 1.0  . We are running a previous version of
>> Phoenix on cdh 5.5.4, we regularly recompile Phoenix with our vendor
>> specific dependencies - with no significant issues - but now without this
>> support we are stuck at 4.8.2  until we upgrade our cluster to HBase 1.1/1.2
>>
>> Is there any plan to support HBase 1.0 again on this (or newer) versions?
>>
>> Thanks for a great work!
>>
>> Regards.
>>
>> --
>> Un saludo.
>> Pedro Boado.
>>
>
>
>
> --
> Mark Heppner
>

Re: Accessing existing schema, creating schema

[Ankit Singhal]

can you please share exception stack trace?

On Fri, Mar 10, 2017 at 12:25 PM, mferlay  wrote:

> Hi everybody , I up this message because I think i'm in the same situation.
> I'm using Phoenix 4.9 - Hbase 1.2.
> I have found some stuff like setting
> "phoenix.schema.isNamespaceMappingEnabled" at true in the
> hbase-setting.xml
> and Phoenix' client side. Thus i have also add it in my conf in Eclipse
> like
> that:
> *conf.put("phoenix.schema.isNamespaceMappingEnabled", "true");
> try {
>   rootConnection =
> DriverManager.getConnection("jdbc:phoenix:localhost:2181", conf);*
>
> It's look OK because I have no issue with this line, but when I try to
> create a schema, i get a parsing exception.
>
> Thanks for your help.
>
> Mathieu
>
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/Accessing-existing-schema-
> creating-schema-tp2757p3259.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>

Re: phoenix.schema.isNamespaceMappingEnabled

[Ankit Singhal]

Sudhir,

Relevant JIRA for the same.
https://issues.apache.org/jira/browse/PHOENIX-3288

Let me see if I can crack this for the coming release.



On Fri, Apr 21, 2017 at 8:42 AM, Josh Elser  wrote:

> Sudhir,
>
> Didn't meant to imply that asking the question was a waste of time.
> Instead, I wanted to try to redirect you into coming up with solutions to
> the problem instead of rehashing that this is something that could be
> improved :)
>
> Sudhir Babu Pothineni wrote:
>
>> Thanks Josh, Sorry if you felt like I am wasting community time, it took
>> some time for me to figure out how to handle hbase-site.xml for Zeppelin
>> phoenix connection, may be its a zeppelin question than phoenix, in my
>> case the only extra parameter I am using is
>> phoenix.schema.isNamespaceMappingEnabled, I thought it should default to
>> true. thanks for the explanation why it shouldn't
>>
>> -Sudhir
>>
>> On Thu, Apr 20, 2017 at 11:56 AM, Josh Elser > > wrote:
>>
>> Most likely to avoid breaking existing functionality.
>>
>> As this mapping is a relatively new feature, we wouldn't want to
>> force it upon new users.
>>
>> The need for Phoenix to have the proper core-site, hdfs-site, and
>> hbase-site XML files on the classpath is a fair knock though
>> (although, the lack of ability to provide classpath element
>> continues to boggle my mind as "normal"). I think discussion around
>> how to better handle this would be a much better use of your and the
>> community's time :)
>>
>>
>> Sudhir Babu Pothineni wrote:
>>
>> is there any specific reason default value of
>> phoenix.schema.isNamespaceMappingEnabled set to false?
>>
>> when using multiple clients like Zeppelin, DbVisualizer etc.. we
>> need to
>> maintain hbase-site.xml. Particularly latest version of Zeppelin
>> is not
>> recognizing hbase-site.xml, it worked only when I included into
>> phoenix
>> client jar (jar uf phoenix--client.jar hbase-site.xml).
>>
>> Thanks
>> Sudhir
>>
>>
>>

Re: View timestamp on existing table (potential defect)

[Ankit Singhal]

This is because we cap the scan with the current timestamp so anything
beyond the current time will not be seen. This is needed mainly to avoid
UPSERT SELECT to see its own new writes.

https://issues.apache.org/jira/browse/PHOENIX-3176


On Thu, Apr 20, 2017 at 11:52 PM, Randy  wrote:

> I was trying to map existing HBase table to view based on the instruction:
> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_
> table_to_an_existing_HBase_table
>
> One surprise from query on the view is all values from one column are all
> null although they are clearly populated with data when viewed in Hbase
> shell.
>
> After some investigation, seems the issue is likely the timestamp used for
> query in view is not "The Most Current". It might be the current server
> timestamp, which would filter out any cells with timestamp larger than the
> "current" value.
>
> Here is my test case.
>
> First run follow command in the HBase shell:
>
> tableName = 'TEST'
> create tableName, 'FAM'
> put tableName,'test1','FAM:VAL',"Hello1"
> put tableName,'test2','FAM:VAL',"Hello2",852223752434352130
> scan tableName
>
> and here are two rows been populated, note the second cell has a custom
> timestamp:
>
> ROW COLUMN+CELL
>
>  test1  column=FAM:VAL, timestamp=1492705385114,
> value=Hello1
>  test2  column=FAM:VAL,
> timestamp=852223752434352130, value=Hello2
>
> After that, start "sqlline.py", and map the table as:
> create view TEST (pk varchar primary key, FAM.VAL varchar);
>
> and query the view by:
> select * from TEST;
>
> The result shows only one row with the default timestamp:
>
> PK   VAL
>
>  --
> --
> test1Hello1
>
> Time: 0.101 sec(s)
>
> Apparently, the second row is filtered due to custom timestamp. So the
> question is if there is anyway to specify the timestamp for query in such
> case. This seems an undesired behavior. Ideally, the query should always
> return the latest value as scan or get operation in HBase shell or API.
>

Re: Bad performance of the first resultset.next()

[Ankit Singhal]

+1 for Joanthan comment,
-- Take multiple jstack of the client during the query time and check which
thread is working for long. If you find merge sort is the bottleneck then
removing salting and using SERIAL scan will help in the query given above.
Ensure that your queries are not causing hotspotting. JFYI, merge sort is
optimized in PHOENIX-2377
-- If you are using single Phoenix client for all your concurrent queries
then increase phoenix.query.threadPoolSizefor parallelism and also
increase regionserver and datanode handler if your server capacity allows
you to do so.
-- In the case of parallel scans, first next() call prepares the scanners
and open them and wait till it completes the scan on all the regionserver
as you have specified ORDER BY on a salted table. Try removing ORDER BY and
see the performance.

Below tuning guide should help you with the tips regarding the same too.
https://phoenix.apache.org/tuning_guide.html




On Thu, Apr 20, 2017 at 9:12 PM, ashish tapdiya 
wrote:

> execQuery() is asynchronous and returns immediately.
>
> next() has blocking semantics and that is why it waits for the result set
> to be generated by the server side.
>
>
> On Thu, Apr 20, 2017 at 10:18 AM, Jonathan Leech 
> wrote:
>
>> Client merge sort is just merging already sorted data from the parallel
>> scan. Look into the number of simultaneous queries vs the Phoenix thread
>> pool size and numActiveHandlers in Hbase region servers. Salting might not
>> be helping you. Also try setting the fetch size on the query in JDBC. Make
>> sure your regions for the table are spread around equally on the region
>> servers. Hbase does not do that by default.
>>
>> On Apr 20, 2017, at 5:45 AM, Binh Luong  wrote:
>>
>> Hi Josh,
>> thank you for your answer.
>> Yes, I am using HDP 2.3.4. You're right, with the newer versions it may
>> improve the performance significantly. However, we are going to have a
>> release shortly, so now it's not possible for an upgrade. But yes, it
>> should happen in the upcoming application release.
>>
>> The table has 21 columns:
>> - the first 3 (id,type and timestamp) make up the PK
>> - the following 18 columns are unsigned int.
>>
>> No, there is no secondary indexes defined for the table.
>> An example query:
>> SELECT timestamp,VALUE04,VALUE15
>> FROM T.TABELLE
>> WHERE id='ID1' and type='A' and timestamp>=TO_TIMESTAMP('...')
>> timestamp<=TO_TIMESTAMP('...')
>> ORDER BY id ASC, type ASC, timestamp ASC;
>>
>> Explain plan:
>> | CLIENT 7-CHUNK PARALLEL 7-WAY RANGE SCAN OVER T.TABELLE
>> [0,'ID1','A','2015-12-02 00:00:00.000'] - [0,'ID1','A','2017-01-01
>> 00:00:00.000']
>> | SERVER FILTER BY (A.VALUE04 IS NOT NULL OR A.VALUE15 IS NOT NULL)
>> | CLIENT MERGE SORT
>>
>> It looks like you suspect that phoenix is firstly reading the data and
>> then post-filtering / sorting the data.
>>
>> But why it take sometimes so much time in the first next() call?
>>
>> When I try to send the request sequentially, the 1.next() always takes
>> about less than 200 ms for processing. But when a large number of requests
>> are coming in parallel, the processing time is increasing significantly to
>> even more than 20, 30 secs.
>>
>> Is it something relating to HBase, as the table is minor compacted from
>> time to time and it has impact to the read performance?
>> I am not sure how the next() call is implemented in the phoenix 4.4.0?
>> Which component can be the bottleneck in such concurrent processing
>> scenario?
>>
>> Thanks in advance
>> Lee
>>
>>
>> 
>> I'm guessing that you're using a version of HDP? If you're using those
>> versions from Apache, please update as they're dreadfully out of date.
>>
>> What is the DDL of the table you're reading from? Do you have any
>> secondary indexes on this table (if so, on what columns)? What kind of
>> query are you running? What is the output of `EXPLAIN ` for these
>> queries?
>>
>> For example, this could be easily explained if Phoenix is reading the
>> data table and post-filtering records. It could take significant amounts
>> of time to read data that does not satisfy your query until you get to
>> some data which does...
>>
>> Lee wrote:
>> > Hi all,
>> >
>> > currently I am struggling with a performance issue in my Rest API. The
>> API
>> > receives loads of requests coming from frontend in parallel, makes SQL
>> > queries using Phoenix JDBC driver to fetch data from HBase. For each
>> > request, the api makes only 1 query to phoenix/hbase.
>> >
>> > I find out, that the very first ResultSet.next() always take long time
>> to
>> > get data from hbase. As far as I know, it gets data in batch, stores
>> them in
>> > main memory, enables the following next() to get data directly from main
>> > memory and thus save up the network overload. The following next() takes
>> > usually less than 10 ms to finish.
>> >
>> > Sometimes this first next() takes more than 10 seconds and gets
>> increasing
>> > from time to time to 30 or even 40 secs. For each query 

Re: load kafka to phoenix

[Ankit Singhal]

It seems we don't pack the dependencies in phoenix-kafka jar yet. Try
including flume-ng-configuration-1.3.0.jar in your classpath to resolve the
above issue.

On Thu, Apr 20, 2017 at 9:27 AM, lk_phoenix  wrote:

> hi,all:
>  I try to read data from kafka_2.11-0.10.2.0 , I get error:
>
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/apache/flume/Context
>  at org.apache.phoenix.kafka.consumer.PhoenixConsumer.prepareContext(
> PhoenixConsumer.java:140)
>  at org.apache.phoenix.kafka.consumer.PhoenixConsumer.<
> init>(PhoenixConsumer.java:71)
>  at org.apache.phoenix.kafka.consumer.PhoenixConsumerTool.
> run(PhoenixConsumerTool.java:98)
>  at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>  at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:84)
>  at org.apache.phoenix.kafka.consumer.PhoenixConsumerTool.
> main(PhoenixConsumerTool.java:104)
>  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>  at sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:62)
>  at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:43)
>  at java.lang.reflect.Method.invoke(Method.java:498)
>  at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
>  at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
> Caused by: java.lang.ClassNotFoundException: org.apache.flume.Context
>  at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
>  at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
>  at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
>  ... 12 more
> how to import flume Context ?
>
> I follow this http://phoenix.apache.org/kafka.html tutorials.
>
> 2017-04-20
> --
> lk_phoenix
>

Re: Limit of phoenix connections on client side

[Ankit Singhal]

bq. 1. How many concurrent phoenix connections the application can open?

I don't think there is any limit on this.

bq. 2. Is there any limitations regarding the number of connections I should
consider?

I think as many till your JVM permits.

bq. 3. Is the client side config parameter phoenix.query.threadPoolSize
correlated to the number of connections?
Yes, phoenix.query.threadPoolSize is shared among the connections so the
no. of threads active will be limited by this.

On Sat, Apr 15, 2017 at 3:58 AM, blnr102  wrote:

> Hello community,
>
> afaik the Phoenix Connection object is designed to be a thin object that is
> inexpensive to create and they can also be resued.
>
> However, in case of handling a load of concurrent requests:
> 1. How many concurrent phoenix connections the application can open?
> 2. Is there any limitations regarding the number of connections I should
> consider?
> 3. Is the client side config parameter phoenix.query.threadPoolSize
> correlated to the number of connections?
>
> Best thanks!
> Lee
>
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/Limit-of-phoenix-connections-
> on-client-side-tp3405.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>

Re: Passing arguments (schema name) to .sql file while executing from command line

[Ankit Singhal]

If you are using phoenix 4.8 onwards then you can try giving zookeeper
string appended with a schema like below.

psql.py ;schema= /create_table.sql

psql.py zookeeer1;schema=TEST_SCHEMA /create_table.sql

On Sat, Apr 15, 2017 at 2:25 AM, sid red  wrote:

> Hi,
>
> I am trying to find a solution, where I can pass arguments, such as schema
> name to the .sql file while executing the file from the command line.
>
> Something like this...
>
> psql.py  /create_table.sql TEST_SCHEMA
>
> I have a create table DDL in the script. So, I want to create a table in
> the
> TEST_SCHEMA schema. But want to pass it as an argument.
>
>
> Your help is much appreciated.
>
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/Passing-arguments-schema-name-
> to-sql-file-while-executing-from-command-line-tp3404.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>

Re: Fwd: Apache Phoenix

[Ankit Singhal]

I'll not recommend keeping two similar system(Kafka and kinesis) back to
back, as it will be a waste of servers. If you are worried about network
connectivity for Kinesis then you should also worry about Kafka going down
because of corrupted partitions or anything, It's better to have some
buffering logic(spilling on file or something) in the system injecting data
in these queues(source/producer reading your extract files) and make a
choice to use either Kafka or kinesis.

You can consider using AWS "Direct Connect" to connect your on-prem cluster
to AWS cloud with HA connectivity instead of going from the internet.

Is the staging area needed during the maintenance of production EDW only? I
think for staging, you can go with simple Files(with some optimised data
format for spark) on HDFS rather than deploying Phoenix until you are
planning to access the data using SQL for some other purpose.

Regards,
Ankit Singhal

On Tue, May 2, 2017 at 7:55 PM, Josh Elser  wrote:

> Planning for unexpected outages with HBase is a very good idea. At a
> minimum, there will likely be points in time where you want to change HBase
> configuration, apply some patched jars, etc. A staging area that can buffer
> data for later processing and avoid dropping data on the floor makes this
> process much easier.
>
> Apache Kafka is just one tool that can help with implementing such a
> staging area -- Apache NiFi is another you might want to look at. I'll
> avoid making any suggestions as to how you should do it because I don't
> know your requirements (nor really care to *wink*). There are lots of tools
> here, you'll need to do the research for your requirements and needs to
> evaluate what tools would work best.
>
> Ash N wrote:
>
>>
>> Hello,
>>
>> We are building an Enterprise Datawarehouse on Phoenix(HBase)
>> Please refer the diagram attached.
>>
>> The EDW supports an unified architecture that serves both Streaming and
>> batch use cases.
>>
>> I am recommending a staging area that is source compliant (i.e. that
>> mimics source structure)
>> In the EDW path - data is always loaded into staging and then gets moved
>> to EDW.
>>
>> Folks are not liking the idea due to an additional hop. They are saying
>> the hop is unnecessary and will cause latency issues.
>>
>> I am saying latency can be handled in two ways:
>>
>> 1. The caching layer will take care
>> 2. If designed properly, Latency is a function of hardware
>>
>> What are your thoughts?
>>
>> One other question -  is Kafka required at all???
>> It is introduced in the architecture for replay messages in case kinesis
>> connectivity issues.  So that we can replay messages.
>> Is there a better way to do it?
>>
>> help as always is appreciated.
>>
>>
>> Inline image 1
>>
>>
>>
>> thanks,
>> -ash
>>
>>
>>
>>
>>

Re: Upsert-Select NullPointerException

[Ankit Singhal]

I think you have a salted table and you are hitting a below bug.
https://issues.apache.org/jira/browse/PHOENIX-3800

Do you mind trying out the patch, we will have this fixed in 4.11 at
least(probably 4.10.1 too).

On Fri, May 5, 2017 at 11:06 AM, Bernard Quizon <
bernard.qui...@stellarloyalty.com> wrote:

> Hi,
>
> I have upgraded my phoenix version from phoenix-4.4.0-Hbase-1.1 to
> phoenix-4.10.0-Hbase-1.1, everything's seems to be working fine.
>
> But when I tried to execute a simple upsert select, it resulted to an
> error.
>
> Query:
>
> UPSERT INTO TEST.TABLE1(id, boolean_field) SELECT id, false FROM
> TEST.TABLE1;
>
> Result:
>
> java.lang.NullPointerException: at index 2
>
> at com.google.common.collect.ObjectArrays.checkElementNotNull(
> ObjectArrays.java:191)
>
> at com.google.common.collect.ImmutableList.construct(
> ImmutableList.java:320)
>
> at com.google.common.collect.ImmutableList.copyOf(
> ImmutableList.java:290)
>
> at org.apache.phoenix.schema.PTableImpl.init(PTableImpl.java:534)
>
> at org.apache.phoenix.schema.PTableImpl.(PTableImpl.
> java:408)
>
> at org.apache.phoenix.schema.PTableImpl.makePTable(
> PTableImpl.java:297)
>
> at org.apache.phoenix.compile.UpsertCompiler.compile(
> UpsertCompiler.java:684)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:611)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:597)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(
> PhoenixStatement.java:351)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(
> PhoenixStatement.java:341)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(
> PhoenixStatement.java:339)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(
> PhoenixStatement.java:1511)
>
> at sqlline.Commands.execute(Commands.java:822)
>
> 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)
>
>
> Btw, it works on my older version. Any thoughts?
>
>
> Thanks!
>

Re: Why can Cache of region boundaries are out of date be happening in 4.5.x?

[Ankit Singhal]

It could be because of stale stats due to the merging of region or
something, you can try deleting the stats from SYSTEM.STATS.
http://apache-phoenix-user-list.1124778.n5.nabble.com/Cache-of-region-boundaries-are-out-of-date-during-index-creation-td1213.html

On Sat, May 20, 2017 at 8:29 PM, Pedro Boado  wrote:

> Hi,
>
> we're just having in production an org.apache.phoenix.schema.
> StaleRegionBoundaryCacheException: ERROR 1108 (XCL08): Cache of region
> boundaries are out of date.
>
> and we don't find a lot of information about the error apart of
> https://issues.apache.org/jira/browse/PHOENIX-2599
>
> The error occurred in similar circumstances to the ticket's but what's
> weird is that the table is no longer available for query.
>
> Why does this happen? Can a reboot harm the database? Our ops guys are
> reluctant on rebooting the server just in case it doesn't start again.
>
> Any help is welcome!!
>
> Cheers!
> Pedro
>
>

Re: checking-in on hbase 1.3.1 support

[Ankit Singhal]

Next release of Phoenix(v4.11.0) will be supporting HBase 1.3.1(see
PHOENIX-3603) and there is no timeline yet decided for the release. But you
may expect some updates in next 1-2 months.

On Thu, May 25, 2017 at 3:32 AM, Anirudha Jadhav  wrote:

> hi,
>
> just checking in, any idea what kind of a timeline this will be. If there
> are any issues. we can lend a hand.
>
>
>
> --
> Anirudha
>

Re: Renaming table schema in hbase

[Ankit Singhal]

Hi Michael,

bq. "exact DDL" Does this mean including all qualifiers like
COMPRESSION='SNAPPY', SALT_BUCKETS=64, ... etc...? If we don't use the
exact DDL, will Phoenix not behave correctly?
 yes, Phoenix will not behave well if properties affecting the write path
are not consistent during the read time, SALT_BUCKETS=64 is an important
one.

bq.if we want to add or remove IMMUTABLE_ROWS=true from the CREATE
statement?
Yes, you can ignore IMMUTABLE_ROWS=true or alter table to mutable later.

{code}
ALTER TABLE table SET IMMUTABLE_ROWS=false
{code}

bq. Also, is it correct to assume that the "1 day less" timestamp is just
so we use a timestamp prior to the CurrentSCN?
Using CurrentSCN less than the oldest timestamp of data during DDL will
just avoid adding empty KV for each row. As you are renaming the existing
Phoenix table, these empty KV will already be there and you will just save
time by doing so.

Regards,
Ankit Singhal

On Thu, Jun 8, 2017 at 1:34 PM, Michael Young  wrote:

> I have a doubt about step 2 from Ankit Singhal's response in
> http://apache-phoenix-user-list.1124778.n5.nabble.com/Phoeni
> x-4-4-Rename-table-Supported-td1781.html
>
> He says:
>
> 2. Open phoenix connection at timestamp 1 day less than the oldest data in
> your tables ( by specifying ts in CurrentSCN ./sqlline.py
> "localhost;CurrentSCN=") and create table with the exact DDL used for
> old table but with the table name changed to new table
>
> "exact DDL" Does this mean including all qualifiers like
> COMPRESSION='SNAPPY', SALT_BUCKETS=64, ... etc...?
>
> If we don't use the exact DDL, will Phoenix not behave correctly?  Is it
> possible to add or change from that list, for example if we want to add or
> remove IMMUTABLE_ROWS=true from the CREATE statement?
>
> Also, is it correct to assume that the "1 day less" timestamp is just so
> we use a timestamp prior to the CurrentSCN?
>
>
> On Wed, Jun 7, 2017 at 5:06 PM, Michael Young  wrote:
>
>> ah haafter some googling I found some info from Ankit, copied below
>>
>> Looks like I was missing step 2.  I don't know how to get the CurrentSCN.
>>
>> Can someone help me with this?
>>
>> == From 
>> http://apache-phoenix-user-list.1124778.n5.nabble.com/Phoenix-4-4-Rename-table-Supported-td1781.html
>>  
>>
>> Currently there is no sql construct but you can do it by following below
>> steps.(It is highly recommended you try these steps in dev environment
>> before proceeding to production.
>>
>> 1. Take snapshot of the original table from hbase shell and restore it
>> with another table name.
>>
>> hbase> disable 'oldtablename'
>>
>> hbase> snapshot 'oldtablename', 'oldtablename_Snapshot'
>>
>> hbase> clone_snapshot 'oldtablename_Snapshot', 'newTableName'
>>
>> 2. Open phoenix connection at timestamp 1 day less than the oldest data
>> in your tables ( by specifying ts in CurrentSCN ./sqlline.py
>> "localhost;CurrentSCN=") and create table with the exact DDL used
>> for old table but with the table name changed to new table.
>>
>> 3. confirm that your new table is working fine as expected .
>> 4. Then drop the old table from phoenix and snapshot from hbase shell.
>>
>> hbase> delete_snapshot 'oldtablename_Snapshot'
>>
>>
>>
>> On Wed, Jun 7, 2017 at 4:58 PM, Michael Young 
>> wrote:
>>
>>> It is possible to rename a table in Hbase, but it doesn't appear to be
>>> recognized by Phoenix.
>>>
>>> I use the approach documented for HBase:
>>>
>>> disable 'MySchema.TABLE1'
>>> snapshot 'MySchema.TABLE1', 'MySchema.TABLE1_SNAPSHOT'
>>> clone_snapshot 'MySchema.TABLE1_SNAPSHOT', NewSchema.TABLE1'
>>> delete_snapshot 'MySchema.TABLE1_SNAPSHOT'
>>> #drop 'MySchema.TABLE1' - let's test before dropping
>>>
>>> However, when testing this Phoenix doesn't seem to see the new cloned
>>> table and there is nothing in the SYSTEM.CATALOG for it, even after
>>> restarting the phoenix client.
>>>
>>> Should this mechanism work in Phoenix or is there another way to rename
>>> or clone an existing table?
>>>
>>> Michael
>>>
>>>
>>
>

Re: phoenix query modtime

[Ankit Singhal]

If you have composite columns in your row key of HBase table and they are
not formed through Phoenix then you can't access an individual column of
primary key by Phoenix SQL too.
Try composing the whole PK and use them in a filter or may check if you can
use regex functions[1] or LIKE operator.

[1] https://phoenix.apache.org/language/functions.html#regexp_substr

On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu  wrote:

> I have a phoenix table created on existing hbase table, and want to query
> something like
> select * from mytable where modtime>'2010-01-01',
>
> how do I query phoenix like this? seems it doesn't have a modtime column
> if I don't do the modtime mapping, which I can not do because it has to be
> part of the primary key.
>
> Thanks,
> Nan
>

Re: Getting too many open files during table scan

[Ankit Singhal]

bq. A leading date column is in our schema model:-
Don't you have any other column which is obligatory in queries during
reading but not monotonous with ingestion? As pre-split can help you
avoiding hot-spotting.
For parallelism/performance comparison, have you tried running a query on a
non-salted table after updating the stats and comparing performance with a
salted table?


On Fri, Jun 23, 2017 at 9:49 AM, Michael Young  wrote:

> We started with no salt buckets, but the performance was terrible in our
> testing.
>
> A leading date column is in our schema model.  We don't seem to be getting
> hotspotting after salting.  Date range scans are very common as are slice
> and dice on many dimension columns.
>
> We have tested with a range of SALT values from 0 to 120 for bulk loading,
> upserts, selects at different concurrent load levels on a test cluster
> before moving to production (with some tweaking post-production).  However,
> we had fewer average regions per RS during the testing.  The larger SALT
> numbers definitely gave overall better performance on our predominantly
> read-heavy environment.
>
> I appreciate any insights to identify bottlenecks.
>
> On Thu, Jun 22, 2017 at 6:26 PM, James Taylor 
> wrote:
>
>> My recommendation: don't use salt buckets unless you have a monatomically
>> increasing row key, for example one that leads with the current date/time.
>> Otherwise you'll be putting more load (# of salt buckets more load worst
>> case) for bread-and-butter small-range-scan Phoenix queries.
>>
>> Thanks,
>> James
>>
>> On Fri, Jun 23, 2017 at 10:06 AM Michael Young 
>> wrote:
>>
>>> The ulimit open files was only 1024 for the user executing the query.
>>> After increasing, the queries behaves better.
>>>
>>> How can we tell if we need to reduce/increase the number of salt buckets?
>>>
>>> Our team set this based on read/write performance using data volume and
>>> expected queries to be run by users.
>>>
>>> However, now it seems the performance has degraded.  We can recreate the
>>> schemas using fewer/more buckets and reload the data, but I haven't seen a
>>> hard and fast rule for setting the number of buckets.
>>>
>>> We have 12 data nodes, 4 SSDs per node, 128 GB Ram per node, 24 core w/
>>> hyperthreading (HDP 2.5 running, hbase is primary service).
>>> and 800+ regions per RS (seems high)
>>>
>>> Any orientation on this would be greatly appreciated.
>>>
>>>
>>> On Tue, Jun 20, 2017 at 11:54 AM, Josh Elser 
>>> wrote:
>>>
 I think this is more of an issue of your 78 salt buckets than the width
 of your table. Each chunk, running in parallel, is spilling incremental
 counts to disk.

 I'd check your ulimit settings on the node which you run this query
 from and try to increase the number of open files allowed before going into
 this one in more depth :)


 On 6/16/17 2:31 PM, Michael Young wrote:

>
> We are running a 13-node hbase cluster.  One table uses 78 SALT
> BUCKETS which seems to work reasonable well for both read and write.  This
> table has 130 columns with a PK having 30 columns (fairly wide table).
>
> However, after adding several new tables we are seeing errors about
> too many open files when running a full table scan.
>
>
> Caused by: org.apache.phoenix.exception.PhoenixIOException: Too many
> open files
>  at org.apache.phoenix.util.Server
> Util.parseServerException(ServerUtil.java:111)
>  at org.apache.phoenix.iterate.Spo
> olingResultIterator.(SpoolingResultIterator.java:152)
>  at org.apache.phoenix.iterate.Spo
> olingResultIterator.(SpoolingResultIterator.java:84)
>  at org.apache.phoenix.iterate.Spo
> olingResultIterator.(SpoolingResultIterator.java:63)
>  at org.apache.phoenix.iterate.Spo
> olingResultIterator$SpoolingResultIteratorFactory.newIterato
> r(SpoolingResultIterator.java:79)
>  at org.apache.phoenix.iterate.Par
> allelIterators$1.call(ParallelIterators.java:112)
>  at org.apache.phoenix.iterate.Par
> allelIterators$1.call(ParallelIterators.java:103)
>  at java.util.concurrent.FutureTask.run(FutureTask.java:266)
>  at org.apache.phoenix.job.JobMana
> ger$InstrumentedJobFutureTask.run(JobManager.java:183)
>  at java.util.concurrent.ThreadPoo
> lExecutor.runWorker(ThreadPoolExecutor.java:1142)
>  at java.util.concurrent.ThreadPoo
> lExecutor$Worker.run(ThreadPoolExecutor.java:617)
>  at java.lang.Thread.run(Thread.java:745)
> Caused by: java.io.IOException: Too many open files
>  at java.io.UnixFileSystem.createFileExclusively(Native
> Method)
>  at java.io.File.createTempFile(File.java:2024)
>  at org.apache.phoenix.shaded.org.
> apache.commons.io.output.DeferredFileOutputStream.thresholdR
> eached(DeferredFileOutputStream.java:176

Re: How to create new table as existing table with same structure and data ??

[Ankit Singhal]

You can map an existing table to view or table in Phoenix but we expect the
name of the table should match with Phoenix table name. (However, you can
rename your existing HBase table with snapshot and restore)
The DDLs you are using to map the table is not correct or are not
supported. You can refer FAQ[1] for details.

[1]
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table


On Thu, Jun 22, 2017 at 5:13 PM, Vishal Biradar 
wrote:

> HBase version : 1.2.6
> Phoenix version : 4.10.0-HBase-1.2.6
>
> I am trying to create new table by using the already existing table
> but I don't know how to do it ??
> I have tried like:
>
> CREATE TABLE "+targerSchemaName+"."+targetTableName+" AS SELECT * FROM
> "+schemaName+"."+tableName
>
> and also i tried like:
>
> CREATE TABLE "+targerSchemaName+"."+targetTableName+" Like SELECT *
> FROM "+schemaName+"."+tableName
>
> But getting error.
> So how to create new table based on existing table in same or
> different schema ??
> Please help me.
>

Re: phoenix query modtime

[Ankit Singhal]

Ah, you are trying to use the row_timestamp feature, not sure if there is a
way from SQL if that column is not in pk.

but if you are open to using an unexposed API, here is some snippet (please
use them at your discretion as these APIs are internal and have no
guarantee to be consistent in versions )

 PreparedStatement statement = conn.preparedStatement("SELECT * FROM " +
tableName);
 QueryPlan plan = statement.unwrap(PhoenixStatement.class).getQueryPlan();
 Scan scan = plan.getContext().getScan();
 scan.setTimeRange(minStamp, maxStamp);
 rs = statement.executeQuery();


On Fri, Jun 23, 2017 at 8:05 PM, Nan Xu  wrote:

> sorry, maybe I did not make it clear, I have a hbase table, already
> formatted with phoenix format and has composite key, I can query all the
> columns I want, but I can not query the hbase modtime in phoenix query, any
> way to do this?
>
> Nan
>
> On Fri, Jun 23, 2017 at 1:23 AM, Ankit Singhal 
> wrote:
>
>> If you have composite columns in your row key of HBase table and they are
>> not formed through Phoenix then you can't access an individual column of
>> primary key by Phoenix SQL too.
>> Try composing the whole PK and use them in a filter or may check if you
>> can use regex functions[1] or LIKE operator.
>>
>> [1] https://phoenix.apache.org/language/functions.html#regexp_substr
>>
>> On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu  wrote:
>>
>>> I have a phoenix table created on existing hbase table, and want to
>>> query something like
>>> select * from mytable where modtime>'2010-01-01',
>>>
>>> how do I query phoenix like this? seems it doesn't have a modtime column
>>> if I don't do the modtime mapping, which I can not do because it has to be
>>> part of the primary key.
>>>
>>> Thanks,
>>> Nan
>>>
>>
>>
>

Re: Phoenix UDF jar cache?

[Ankit Singhal]

Yes, this is a limitation[1] of the current implementation of UDF  and
class loader used. It is recommended either to reboot the cluster if
implementation changes or use new jar name.

[1] https://phoenix.apache.org/udf.html


On Wed, May 3, 2017 at 4:41 AM, Randy Hu  wrote:

> Developed and test UDF successfully, but was surprised that the same SELECT
> with UDF still succeeded after the jar was removed. Same behavior after
> restart sqlline.py, even HBase. So is there a permanent cache for UDF jars
> on disk in Phoenix?
>
> So far not causing any problem yet, but a little bit concerned since the
> jar
> is likely rebuilt multiple times during the development. Want to make sure
> Phoenix always pick up the latest version.
>
> Thanks
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/Phoenix-UDF-jar-cache-tp3508.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>

Re: phoenix query modtime

[Ankit Singhal]

Yes, and also to avoid returning an incomplete row for the same primary key
because of different timestamp for the column's cell.

On Sat, Jun 24, 2017 at 4:20 AM, Randy Hu  wrote:

> First HBase does not have a concept of "row timestamp". Timestamp is part
> of each cell. The closest to row timestamp is probably the latest timestamp
> from all cells with same row key.
>
> The reason that timestamp column need to be part of primary key is that
> there could be multiple values with different timestamps but same row key,
> family. and qualifier. The SQL result data model does not support such two
> dimension structure well. The best Phoenix can do in the SQL model is to
> return list of values in array, but then the cell timestamp need to be
> returned in another array with same length under different column name. You
> can imagine the mess and awkwardness it could be if going to that
> direction. It's much straightforward to make time stamp part of primary
> key, so the two dimension structure could be represented by multiple rows
> naturally in SQL result.
>
> Randy
>
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/phoenix-query-modtime-tp3702p3716.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>

Re: Safest migration path for Apache Phoenix 4.5 to 4.9

[Ankit Singhal]

If you don't have secondary indexes, views and immutable table then upgrade
from 4.5 to 4.9 will just add some new columns in SYSTEM.CATALOG and
re-creates STATS table.
but still we have not tested an upgrade from 4.5 to 4.9, it is always
advisable to do stop after every two versions (especially in production).

On Tue, Jun 27, 2017 at 3:58 AM, Pedro Boado  wrote:

> Hi guys
>
> we are planning a migration from our current version of Apache Phoenix (
> 4.5 ) to 4.9. We checked the upgrade guide and we are aware that Phoenix
> maintains two versions-back compatibility but...  is it really needed to do
> a stop in 4.7 to have a safe migration?
>
> We don't have any secondary indexes or views but just regular phoenix
> tables.
>
> Cheers!
> Pedro.
>

Re: Can set default value for column in phoenix ?

[Ankit Singhal]

Phoenix 4.9 onwards you can specify any expression for default column. (I'm
not sure if there is any limitation called out).
For syntax:-
https://phoenix.apache.org/language/index.html#column_def

For examples-
https://github.com/apache/phoenix/blob/2d40241b5c5c0287dca3dd2e2476db328bb7e7de/phoenix-core/src/it/java/org/apache/phoenix/end2end/DefaultColumnValueIT.java

On Fri, Jul 14, 2017 at 1:38 PM, 曾柏棠  wrote:

> Oh ，thanks！
>
>
> -- 原始邮件 --
> *发件人:* "Juvenn Woo";;
> *发送时间:* 2017年7月14日(星期五) 下午3:39
> *收件人:* "user";
> *主题:* Re: Can set default value for column in phoenix ?
>
> Hi Baitang,
>
> Yes, you can, but only constant value at the moment. So literal value such
> as ‘string’, 0, 0.0 would work well, but CURRENT_TIME will not. (As far as
> I know.)
>
> Best,
> --
> Juvenn Woo
> Sent with Sparrow 
>
> On Friday, 14 July 2017 at 3:29 PM, 曾柏棠 wrote:
>
> hi all,
>Can set default value for column in phoenix ?
>for example :
>
> CREATE TABLE api.hahah(
> subscribe_time bigint not null  ,
> *hahha varchar default 'aaa',*
> CONSTRAINT pk PRIMARY KEY (subscribe_time)
> )
>
> Thanks !
>
>
>

Re: RegionNotServingException when using Phoenix

[Ankit Singhal]

Yes, value 1 for "hbase.client.retries.number" is the root cause of above
exception.

General guideline/formulae could be(not official):-
(time taken for region movement in your cluster + timeout of zookeeper) /
hbase.client.pause

Or with intuition, you can set to at least 10.

On Fri, Jul 14, 2017 at 7:59 AM, Tanujit Ghosh 
wrote:

> Thanks Josh for the quick reply
>
> So we are not getting the Exception transiently, but it get thrown out our
> application code wrapped in an SQLException from Phoenix layer.
>
> For now, we have written kind of a backoff retry logic which re-tries the
> query after some time again but looking for a more elegant solution to this
> problem.
>
> Does the hbase.client.retries.number parameter has any effect on this kind
> of failure scenario, in our current cluster, we have set it to 1.
>
>
>
> On Thu, Jul 13, 2017 at 4:45 PM, Josh Elser  wrote:
>
>>
>>
>> On 7/13/17 1:48 PM, Tanujit Ghosh wrote:
>>
>>> Hi All,
>>>
>>> We are facing a problem in our cluster as stated below.
>>>
>>> We have a long running java process which does various select on
>>> underlying Phoenix/HBASE table structure and return data. This process gets
>>> requests from other upstream apps and responds with results from
>>> Phoenix/HBASE.
>>>
>>> We are facing an issue here is that if any one of the HBASE region
>>> servers goes down, then we start getting a RegionNotServingException when
>>> we run a query on a table whose regions were on the region server that went
>>> down. Although now the cluster has reassigned those regions to other region
>>> servers, somehow it does not reflect in the Phoenix query layer.
>>>
>>
>> This expected to a degree. Even after Regions move on the cluster, the
>> client will not re-query a Region's location until it is not where the
>> client thinks it was (invalidates the cache of Region->RS, and re-queries
>> it from hbase:meta).
>>
>> If you see transiently this for a region after it moves, that is
>> expected. You have to do nothing -- the client automatically recovers and
>> is just informing you. However, if your client becomes "stuck" (looping
>> with NotServingRegionExceptions), that's a completely different problem and
>> would likely be an HBase bug.
>>
>> I'm not sure which case you're describing.
>>
>>
>> As per Phoenix documentation, we are creating a new PhoenixConnection
>>> object for each query and running the select statements.
>>>
>>> Has anyone faced a similar issue?
>>> Any suggestions/help in this regards will be appreciated.
>>>
>>> Thanks and Regards,
>>> Tanujit
>>>
>>
>
>
> --
> Regards,
> Tanujit
>