Support Questions

Find answers, ask questions, and share your expertise

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
Super Collaborator

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
Super Collaborator

@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