Doubts in phoenix


Hadoop Eco System Forums Hadoop Discussion Forum Doubts in phoenix

Tagged: , ,

This topic contains 10 replies, has 3 voices, and was last updated by Profile photo of Lokesh Kumar Adike Lokesh Kumar Adike 7 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #1852 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    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?

    #1859 Reply
    Profile photo of Siva
    Siva
    Keymaster

    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)

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

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

    Syntax:
    CAST ( string_timestamp AS TIMESTAMP )

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

    #1860 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    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?

    Thanks,
    Subramaniyabharathi

    #1861 Reply
    Profile photo of Siva
    Siva
    Keymaster

    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.

    Syntax:
    REGEX_REPLACE(String, pattern, replacement string)

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

    try both SUBSTR and REGEX_REPLACE to achieve this.

    #1925 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    Hi Siva,
    Thank you so much. It worked.

    #1926 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    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?

    #1934 Reply
    Profile photo of Siva
    Siva
    Keymaster

    Hi Subramaniya,

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

    #1935 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    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.

    Thanks
    Subramaniyabharathi

    #1957 Reply
    Profile photo of Siva
    Siva
    Keymaster

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

    #2262 Reply
    Profile photo of Subramaniyabharathi
    Subramaniyabharathi
    Participant

    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(SqlLine.java:2440)
    at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
    at sqlline.SqlLine.print(SqlLine.java:1735)
    at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
    at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
    at sqlline.SqlLine.dispatch(SqlLine.java:821)
    at sqlline.SqlLine.begin(SqlLine.java:699)
    at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
    at sqlline.SqlLine.main(SqlLine.java:424)

    what does this mean and how to rectify this?

    #11487 Reply
    Profile photo of Lokesh Kumar Adike
    Lokesh Kumar Adike
    Participant

    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
    Lokesh

Viewing 11 posts - 1 through 11 (of 11 total)
Reply To: Doubts in phoenix
Your information:




Review Comments
default gravatar

I am a plsql developer. Intrested to move into bigdata.

Neetika Singh ITA

.