I'm trying to use hive to plot simple things.I'm currently working with Hive (0.11) on a small dataset (about 6Gb/6 millions of lines), with tableau 8.0.4.
However I cannot make date aggregation work.
My date is in the format, 16/06/13 18:30:01.240856. Based on numerous post I found here, I understood that I had to create a calculated field to manage it. Here my formula:
DATETIME(LEFT([idx_timestamp],17 ) )
until here everything is fine.
Now if I try to do a simple aggregation plot (get #of event per minute), it seems that the date is not recognized and I always get NULL or the 30th December 1899.... (and the total # of events)
I made a test and imported directly in Tableau a small sample of 100 lines. If I use this sample everything is working perfectly fine. I can plot without any issue a # of event per minute The date is recognized and the time is right. But doing exactly the same things on the hive cluster give me nothing.
Am I facing a performance issue here on tableau side? If a do a simple sql group by request to get what I want I get my result in under 20 seconds from the hive console. Do I have to use custom sql for this kind of request?
That formula does not compensate for date/time representations common in Europe and many other locales. I don't believe that the Hive SQL syntax is locale-aware, so you must transform your date string into one that matches the US format of mm/dd/yyyy.