Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Need the query to get the past 24 hours data from now.

avatar
Contributor

I am looking for the query which brings me the pat 24 hours of data when ever I run a query. And I want store it in the local machine as CSV. Do any one of you have any idea about it. If so please can you please me.

3 REPLIES 3

avatar
Expert Contributor

Below query may help you with your use case?

select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))

Where data column holds data in timestamp format -> YYYY-mm-dd HH:MM:SS

To export using beeline below is command can be used.

beeline --outputformat=csv2 -u "JDBC_CONNECT_STRING" -n USERNAME -e "select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))" > /tmp/output.txt

OR if you want to exclude headers, you can run

beeline --showheader=false --outputformat=csv2 -u "JDBC_CONNECT_STRING" -n USERNAME -e "select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))" > /tmp/output.txt

avatar
Contributor

@Chiran Ravani Is the query what you have here is correct ? I am getting some exceptions when I use it in Hive. In addition to that also I am trying to run select * from dnapm.pm_data where ts >(UNIX_TIMESTAMP() - 24*60*60) and it returns empty rows.

Here is the error that I am getting if I run your query. Might be you have got a typo in your query. I am just guessing/ Correct me if I am wrong.

<small>org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:33 cannot recognize input near 'select' 'FROM_UNIXTIME' '(' in expression specification
    </small>
  </small>

avatar
Expert Contributor

@Sai Krishna Makineni Somehow the query does not work on Hive 1.2.1, Your query looks good, can you check data format in ts column? I had in below format

YYYY-mm-dd HH:MM:SS