This being the R-help mailing list, not being fluent in random SQL variants is normal. There is a place for discussing the intersection of R and databases where the intersection of R with such knowledge might be more typical. I suggest that Ashta read the Posting Guide to learn about appropriate mailing lists and other useful background. If in fact this question is about R, then confusing the issue with SQL should not be necessary since a sample of the output of a database query can be provided as dput() output. If the question is purely about Hive then it doesn't belong on any R list anyway. -- Sent from my phone. Please excuse my brevity.
On October 9, 2016 9:16:33 AM PDT, David Winsemius <dwinsem...@comcast.net> wrote: > >> On Oct 9, 2016, at 7:56 AM, Ashta <sewa...@gmail.com> wrote: >> >> I am trying to query data from Hive service and create a variable. >> >> >> dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") >> date1, date2, Diif >> 4/5/1999, 6/14/2000 >> 7/2/1999, 6/26/2000 >> 8/14/1999, 8/19/2000 >> 11/10/1999, 9/18/2000 >> 8/25/2000, 6/5/2001 >> 3/14/2012, 3/15/2004 >> >> >> Here is what I wanted to do. While I am querying I want create a >> variable diff= dat1e1-date2. >> I may use this variable "diff" to do some statistics (mean, mode, >> etc) and also in the where clause l like as the following. >> >> test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable >> where diff gt 1000 limit 10") >> >> I would appreciate if you suggest me how to do this. > >Sorry for the blank message earlier. My reading of the use of Hive >queries is that you would need to use the `datediff` function. I >further suspect you need to define a variable name to which then apply >your limits. I also read that hive dates are actually strings types >represented as POSIX style character values and might need a to_date >funciton. This is all guesswork since I don't have a hive cluster to >run this against: > > So perhaps something like one of these: > >try1 <- dbGetQuery(hivecon,"select date1, date2, >datediff(TO_DATE(date1),TO_DATE(date2)) as d12diff from dateTable >where d12diff GT 1000 limit 10") > >try2 <- dbGetQuery(hivecon,"select date1, date2, datediff(dat1,date2) >as d12diff from dateTable where d12diff GT 1000 limit 10") > >Obviously these are just guesses. ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
