How about using individual data functions on crdatetime (in my case op_time)
Something like
INSERT OVERWRITE TABLE tmp
SELECT rs.op_type, rs.year, rs.Month, rs.Day, rs.Total_Rows
FROM
(
SELECT
op_type
, YEAR(op_time) As Year
, MONTH(op_time) AS Month
, DAY(op_time) AS Day
, count(*) AS Total_Rows
FROM t
GROUP BY
op_type
, YEAR(op_time)
, MONTH(op_time)
, DAY(op_time)
) rs
HTH
Mich Talebzadeh
http://talebzadehmich.wordpress.com
Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15",
ISBN 978-0-9563693-0-7.
co-author "Sybase Transact SQL Guidelines Best Practices", ISBN
978-0-9759693-0-4
Publications due shortly:
Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and
Coherence Cache
Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one
out shortly
NOTE: The information in this email is proprietary and confidential. This
message is for the designated recipient only, if you are not the intended
recipient, you should destroy it immediately. Any information in this message
shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries
or their employees, unless expressly so stated. It is the responsibility of the
recipient to ensure that this email is virus free, therefore neither Peridale
Ltd, its subsidiaries nor their employees accept any responsibility.
From: Ayazur Rehman [mailto:rehman.ayazu...@gmail.com]
Sent: 02 June 2015 15:19
To: user@hive.apache.org
Subject: current_date function in hive
Hi everyone,
I am trying to schedule a hive query using Oozie, to perform aggregation on a
table on data of a particular day and save the results in another table
whenever every 24 hours.
the schema of my table is something like (tablename - currenttable)
id string
cdatetime timestamp
average int
locations array<string>
color string
And currently the query that I perform manually everyday is something like
insert into table lotable select id, lv, cdatetime, color, count(color) from
currenttable lateral view explode(locations) lvtable as lv where
to_date(cdatetime)='2015-06-01' group by cdatetime, color, lv, id;
So, in order to automate the process I want to use a date function that would
let hive aggregate on the data of the previous day.
I tried using current_date function but I can't get the syntax right. I get the
following error
FAILED: SemanticException [Error 10011]: Line 1:47 Invalid
function 'current_date'
Could you please help me with the syntax.
--
Thanking You,
Ayaz
