Doubts in phoenix

Hadoop Eco System Forums Hadoop Discussion Forum Doubts in phoenix

Tagged: , ,

This topic contains 11 replies, has 4 voices, and was last updated by  Aditya Raj 1 year, 8 months ago.

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author
  • #1852


    Dear all,
    I started working in Phoenix and have following doubts. I will be thankfull if anyone answer the followin questions.
    How to extract time from a datetime string with phoenix?
    How to convert a string of specified format to datetime format in phoenix?



    Hi Subramaniya,

    1. You can extract time from a datetime string in Phoenix with the help of SUBSTR function in Phoenix.

    Its syntax is:
    SUBSTR(‘String’, Startposition, Length)

    SUBSTR(‘Welcome’, 4, 4) –> come

    2. To convert a string of specified format to datetime format in phoenix use, CAST Operator.

    CAST ( string_timestamp AS TIMESTAMP )

    Please give a try on these and let us know how this goes.



    Hi Siva,
    Thank you so much 🙂 Whenever I am stuck in this world of ‘BIG DATA’, you are helping me to come out of that. I am so much thankful to you.
    Coming to the problem,SUBSTR function worked Perfectly. thanks :).
    But CAST function shows an error. May be I am wrong in the syntax of using CAST function.
    Error: ERROR 203 (22005): Type mismatch. VARCHAR and TIMESTAMP for ’01/09/2013:00:08:34′ (state=22005,code=203)
    Here is what I did.
    select * from utest where cast(datetime as timestamp) between cast(’01/09/2013:00:08:34′ as timestamp) and cast(’01/09/2013:00:11:27′ as timestamp);

    Can you Please explain how to use the function?




    Hi Subramaniya,

    The format of TIMESTAMP in phoenix should be in yyyy-MM-dd hh:mm:ss. but not DD/MM/YYYY:HH:MM:SS try re-framing this field into the above form and use SUBSTR and REGEX_REPLACE for building this field.

    REGEX_REPLACE(String, pattern, replacement string)

    REGEXP_REPLACE(‘abc/ABC’, ‘/’, ‘:’) evaluates to ‘abc:ABC’

    try both SUBSTR and REGEX_REPLACE to achieve this.



    Hi Siva,
    Thank you so much. It worked.



    Hi Siva,
    Can you explain how to get data from multiple tables in phoenix? We use union all in Mysql. is there any similar thing in phoenix?

    Also how to retain the result of a query for further analysis? And how to write complex queries?



    Hi Subramaniya,

    In Apache Phoenix, Unions and Complex queries are not supported yet, we need to think of any other alternatives to these.



    Hi Siva,
    Thanks for the reply.. I will keep searching for achieving these queries. If you get any information regarding these, please reply in this post.




    Definitely…and post your answers in this, if you try anything new and succeed in it, so that every one can know it.



    Hi siva,
    After surfing a lot, I found there is no possible ways to retrieve data from multiple tables. So I created an issue in JIRA of phoenix. and am contributing towards it. For time instance, I have merged the tables from which I have to retrieve the data into one BIG table. when I queried with two subqueries, it throws an error like

    java.lang.RuntimeException: org.apache.phoenix.exception.PhoenixIOException
    at sqlline.SqlLine$IncrementalRows.hasNext(
    at sqlline.SqlLine$TableOutputFormat.print(
    at sqlline.SqlLine.print(
    at sqlline.SqlLine$Commands.execute(
    at sqlline.SqlLine$Commands.sql(
    at sqlline.SqlLine.dispatch(
    at sqlline.SqlLine.begin(
    at sqlline.SqlLine.mainWithInputRedirection(
    at sqlline.SqlLine.main(

    what does this mean and how to rectify this?


    Lokesh Kumar Adike

    Hi Subramaniya,

    I have seen some of topics and subscribed in this forum. Those are really helpful for BigData Developers. I am really glad that to find this Tutorial. Thanks to every one who are contributing their ideas, errors, solutions. And i just joined now. I am really happy for being a part of this forum.

    I am beginner to BigData Hadoop. I have an idea about hadoop eco system. What i wanted to do is now, i need an overall idea about how exactly the real time process will go on any project. Let’s take Phoenix Market City. A separate website is maintaining for this mall right.

    I want to extract log data from phoenix mall website using flume.
    And we parse the log data using pig/Mapreduce to structured format.
    Store it into Hive/Hbase.
    After that visualization team will take care for further process.

    I need a sample peace of code that how this process will go on…. Like..
    1. Sample configurations on flume to access Phoenix log data.
    2. Sample code for converting unstructured data to structured data using Pig.
    3. Sample code for how to store that data into Hive/HBase.

    Can you help me with this please. Why becase i have contacted some of ppl but thet did’t help me so far. I am hoping for your reply
    Thanks in Advance


    Aditya Raj

    I have a table in hbase and i am not able to see that table in phoenix command prompt.Did i missed something while creating table in hbase or it is due to other reason?

    Thanks In Advance

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘Doubts in phoenix’ is closed to new replies.

Review Comments
default image

I have attended Siva’s Spark and Scala training. He is good in presentation skills and explaining technical concepts easily to everyone in the group. He is having excellent real time experience and provided enough use cases to understand each concepts. Duration of the course and time management is awesome. Happy that I found a right person on time to learn Spark. Thanks Siva!!!

Dharmeswaran ETL / Hadoop Developer Spark Nov 2016 September 21, 2017