Analytic functions in Phoenix

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 AveragePri

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

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

Setting default timezone for Phoenix

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

Re: Analytic functions in Phoenix

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

Re: Index in Phoenix view on Hbase is not updated

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

Re: Index in Phoenix view on Hbase is not updated

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUr

Re: Analytic functions in Phoenix

You are correct Ankit. However, I can use Spark SQL on Hbase table directly or Hive table built on Hbase regards, Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

Re: Setting default timezone for Phoenix

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

Re: Index in Phoenix view on Hbase is not updated

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

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

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 seem

Re: Index in Phoenix view on Hbase is not updated

Hi Ankit, I created a dummy table in Hbase as below create 'dummy', 'price_info' Then in Phoenix I created a table on Hbase table create table "dummy" (PK VARCHAR PRIMARY KEY, "price_info"."ticker" VARCHAR,"price_info"."timecreated" VARCHAR, "price_info"."price" VARCHAR); And then used the fo

Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Hi, My stack Hbase: hbase-1.2.3 Phoenix: apache-phoenix-4.8.1-HBase-1.2-bin As a suggestion I tried to load an Hbase file via org.apache.phoenix.mapreduce.CsvBulkLoadTool So I created a dummy table in Hbase as below create 'dummy', 'price_info' Then in Phoenix I created a table on Hbase tab

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Not sure whether phoenix-4.8.1-HBase-1.2-client.jar is the correct jar file? Thanks Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw * http://tale

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Hi Mich, Apparently, the tables are case sensitive. Since you have enclosed a double quote when creating the table, please pass the same when running the bulk load job. HADOOP_CLASSPATH=/home/hduser/jars/hbase-protocol-1.2.3.jar:/usr/lib/hbase/conf hadoop jar phoenix-4.8.1-HBase-1.2-client.jar

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Sorry, I meant to say table names are case sensitive. On Sun, Oct 23, 2016 at 9:06 AM, Ravi Kiran wrote: > Hi Mich, >Apparently, the tables are case sensitive. Since you have enclosed a > double quote when creating the table, please pass the same when running the > bulk load job. > > HADOOP_

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Hi Mich, Its recommended to use upper case for table and column name so that you dont to explicitly quote table and column names. ~Anil On Sun, Oct 23, 2016 at 9:07 AM, Ravi Kiran wrote: > Sorry, I meant to say table names are case sensitive. > > On Sun, Oct 23, 2016 at 9:06 AM, Ravi Kiran

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

Hi Ankit, Thanks for response. My phoenix.query.timeoutMs is already increased to 366. phoenix.query.timeoutMs 366 Few more questions just to clear my path: 1) Can I use spark to connect phoenix for running queries which have aggregate functions ? I felt I could not b

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Thanks gents I dropped and recreated the table name and columns in UPPERCASE as follows: create table DUMMY (PK VARCHAR PRIMARY KEY, PRICE_INFO.TICKER VARCHAR, PRICE_INFO.TIMECREATED VARCHAR, PRICE_INFO.PRICE VARCHAR); and used this command as below passing table name in UPPERCASE as well HADOO

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

Looks like user experience can be improved (by enriching exception message) if table abc can be found but table ABC cannot be found. Cheers On Sun, Oct 23, 2016 at 9:26 AM, Mich Talebzadeh wrote: > Thanks gents > > I dropped and recreated the table name and columns in UPPERCASE as follows: > >

Re: Load into Phoenix table via CsvBulkLoadTool cannot find table and fails

I am not sure why you are creating underlying HBase tables. If you create table in Phoenix, it will create HBase table automatically with correct name. If you are using Phoenix, i would recommend you to let Phoenix handle the interaction with HBase. On Sun, Oct 23, 2016 at 9:26 AM, Mich Talebzadeh

Re: Index in Phoenix view on Hbase is not updated

Hi MIch, I would recommend you to use Phoenix API/Tools to write data to a Phoenix table so that it can handle secondary index seamlessly. Your approach of **Rebuilding** index after every bulkload will run into scalability problems as your primary table keeps growing. ~Anil On Sat, Oct 22, 2016

How can I contribute clients?

I made three protobuf clients for Phoenix in C++, Python and Erlang. how can I make these become "official" as lalinsky's Phoenix JSON python client? *The story*: I first created my application in Python and used lalinsky's JSON client but later found out Python (and JSON) weren't my best choice

Re: How can I contribute clients?

That's awesome, Cheyenne! Is it packaged as an ODBC client or is the API different? If you send us a link to your Github repo and docs, we'll get links up on the website. Would be great to get an ODBC driver contributed to Phoenix and included in our distro. Thanks, James On Sunday, October 23, 2

Re: How can I contribute clients?

If they're generic to Apache Avatica (Apache Calcite sub-project) and not tied to Apache Phoenix, we'd also love to have you recognized, if not having the code not directly committed to Avatica :). Avatica is the underlying tech to the Phoenix Query Server. Minor clarification with my Phoenix

Re: Creating Covering index on Phoenix

Hi Mich, No, if you update HBase directly, the index will not be maintained. Actually I would suggest to ingest data using Phoenix CSV bulk load. Thanks, Sergey. On Sat, Oct 22, 2016 at 12:49 AM, Mich Talebzadeh wrote: > Thanks Sergey, > > In this case the phoenix view is defined on Hbase table

Re: Creating Covering index on Phoenix

Thanks Sergey, I have modified the design to load data into Hbase through Phoenix table. In that way both the table in Hbase and the index in Hbase are maintained. I assume Phoenix bulkload .CsvBulkLoadTool updates the underlying table in Hbase plus all the indexes there as well. therefore I not

Re: Creating Covering index on Phoenix

Keep in mind that the CsvBulkLoadTool does not handle updating data in-place. It's expected that the data is unique by row and not updating existing data. If your data is write-once/append-only data, then you'll be ok, but otherwise you should stick with using the JDBC APIs. You're free to just us

Re: Creating Covering index on Phoenix

Thanks James. My use case is trade data into HDFS and then put into Hbase via Phoenix. This is the batch layer 1. Every row has a UUID as row-key and immutable (append only) 2. Source (trade data -> Kafka -> Flume > HDFS. Hdfs directories partitioned by DtStamp (daily) 3. cron from H

Re: Creating Covering index on Phoenix

Sorry I forgot you were referring to "multi tenancy"? Can you please elaborate on this? Thanks Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw *

Re: Creating Covering index on Phoenix

See http://phoenix.apache.org/ and the Features menu items. On Sunday, October 23, 2016, Mich Talebzadeh wrote: > Sorry I forgot you were referring to "multi tenancy"? > > Can you please elaborate on this? > > Thanks > > Dr Mich Talebzadeh > > > > LinkedIn * > https://www.linkedin.com/profile/v

data chaned depending on condition

Hello. I’ve queried data as below. select /*+ NO_INDEX */ * from WINLOSS where DAYNUMBER=?; there are fields of null vaue. after that I retrieve it with primary key again there is no null value. select /*+ NO_INDEX */ * from WINLOSS where DAYNUMBER=? and GPID=? and TYPE=?; what is check point