Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Highlighted

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

New 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

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

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

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

New 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>

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

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

Don't have an account?
Coming from Hortonworks? Activate your account here